Oracle---PL/SQL

PL/SQL以语句块为单位：
declare
  --私有变量声明
begin
  --代码主体
exception
  --异常处理
end;
/

declare
  --私有变量声明
begin
  --代码主体
end;
/

declare
  v_message varchar2(30);
begin
  v_message := 'Hello World!';
end;
/

PL/SQL procedure successfully completed.
打开sqlplus环境变量
set serverout on
declare
  v_message varchar2(30);
begin
  v_message := 'Hello World!';
  /*调用标准输出包*/
  dbms_output.put_line(v_message);
end;
/

declare
  v_message varchar2(30);
begin
  v_message := 'Hello World! ';
  dbms_output.put(v_message);
  v_message := 'My first plsql block ';
  dbms_output.put(v_message);
  dbms_output.put_line('start print!');
end;
/

声明变量时直接初始化：
declare
  v_message varchar2(30) := upper('Hello World! ');
begin
  dbms_output.put_line(chr(10));
  dbms_output.put(v_message);
  v_message := 'My first plsql block';
  dbms_output.new_line;
  dbms_output.put(v_message);
  dbms_output.put_line('');
end;
/

声明常量：必须被初始化，初值不能被改变
declare
  v_tax constant number := 0.05;
begin
  dbms_output.put_line(v_tax);
end;
/

声明非空类型变量： 必须被初始化，变量不接受null
declare
  v_ename varchar2(20) not null := 'King';
begin
  dbms_output.put_line(v_ename);
  v_ename:='scott';
  dbms_output.put_line(v_ename);
end;
/

常用标量类型：
char
varchar2
date
number
long
clob
rowid
boolean (true & false & null)

条件判断：
正确输出中文
SQL> select userenv('language') from dual;
USERENV('LANGUAGE')
------------------------------
AMERICAN_AMERICA.WE8MSWIN1252

$ export NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252

declare
  v_sal number;
begin
  select sal
  into v_sal
  from emp where empno=&p_empno;
  if v_sal<1000 then
    dbms_output.put_line('工资太低！');
  elsif v_sal>=1000 and v_sal<3000 then
    dbms_output.put_line('工资中等！');
  else
    dbms_output.put_line('工资足够高！');
  end if;
end;
/

分枝选择：
declare
  v_grade varchar2(1):=upper('&p');
  v_mess varchar2(21);
begin
  v_mess := case v_grade when 'A' then 'excellent!'
                         when 'B' then 'very good！'
                         when 'C' then 'good！'
            else 'No such grade!'
            end;
  dbms_output.put_line(v_mess);
end;
/

'没有'||v_flag||'这样的等级'

declare
  v_flag number:=&p;
  v_grade varchar2(50);
begin
  v_grade := case  when v_flag=1 then '极好的！'
                   when v_flag=2 then '很好！'
            else 5
            end;
  dbms_output.put_line(v_grade);
end;
/

1级工资不缴税
2级工资缴税3%
3级工资缴税5%
4级工资缴税7%
5级工资缴税10%

循环遍历：
1.基本loop循环
declare
  x number:=1;
begin
  loop
    dbms_output.put_line(x);
    x:=x+1;
    exit when x>10;
  end loop;
end;
/

declare
  x number:=1;
begin
  loop
    dbms_output.put(x||' ');
    x:=x+1;
    exit when x>10;
  end loop;
  dbms_output.put_line('');
end;
/

2.while循环
declare
  x number:=1;
begin
  while x<=10 loop
    dbms_output.put_line(x);
    x:=x+1;
  end loop;
end;
/

3.for循环
begin
  for x in 1..10 loop
    dbms_output.put_line(x);
  end loop;
end;
/

begin
  for x in reverse 1..10 loop
    dbms_output.put_line(x);
  end loop;
end;
/

打印1～10之间的偶数
begin
  for x in 1..10 loop
   if mod(x,2)!=0 then
    dbms_output.put_line(x);
   end if;
  end loop;
end;
/

打印乘法口诀表
begin
  for x in 1..9 loop
    for y in 1..x loop
     dbms_output.put(y||'*'||x||'='||y*x||' ');
    end loop;
    dbms_output.put_line('');
  end loop;
end;
/

goto与标签：
begin
  for x in 1..10 loop
    dbms_output.put_line(x);
    if x=6 then
      goto end_loop;
    end if;
  end loop;
  <<end_loop>>
  null;
end;
/

自定义数据类型（复合变量）：
数组：
declare
  type num_list is varray (10) of number;
  v_num num_list;
  v_sal varchar2(10);
begin
  v_num:=num_list();
  v_num.extend;
  v_num(1):=1990;
  v_num.extend(2);
  v_num(2):=1985;
  select sal into v_num(3) from emp where empno=7839;  
  dbms_output.put_line(v_num(1));
  dbms_output.put_line(v_num(2));
  dbms_output.put_line(v_num(3));
end;
/

declare
  type loc_list is varray (10) of varchar2(20);
  v_loc loc_list;
  v number;
begin
  select loc bulk collect into v_loc from dept;
  /*数组容量*/
  dbms_output.put_line(v_loc.limit);
  /*数组中元素数量*/
  dbms_output.put_line(v_loc.count);
  /*删除数组中最后n个元素*/
  v_loc.trim(2);
    /*删除数组中所有元素*/
--  v_loc.trim(v_loc.count);
--  v_loc.delete();
  /*取数组中指定下标的前一个下标值*/
  v:=v_loc.prior(v_loc.count);
  /*取数组中指定下标的下一个下标值*/
  v:=v_loc.next(1);
  v:=v_loc.last();
  v:=v_loc.first();
  dbms_output.put_line(v_loc(v_loc.last()));
  dbms_output.put_line(v);
  for i in 1..v_loc.count loop
    dbms_output.put_line(v_loc(i));
  end loop;
end;
/

将dept的loc列保存到varray
将emp的ename列保存到varray

记录(record):
declare
  type dept_record is record
  (deptno number,
   dname varchar2(10),
   loc varchar2(13));
  r_dept dept_record;
begin
  select * into r_dept from dept where deptno=10;
  dbms_output.put_line(r_dept.deptno||' '||r_dept.dname);
end;
/

动态声明变量和记录的数据类型：
declare
  v_ename emp.ename%type;
  r_dept dept%rowtype;
  r_emp emp%rowtype;
begin
  select ename into v_ename from emp where empno=7788;
  select * into r_dept from dept where deptno=10;
end;
/

PL/SQL表（index by table）:
declare
  type dept_type is table of varchar2(10) index by binary_integer;
  v_dept dept_type;
begin
  v_dept(2):='Hello';
  v_dept(-1):='World';
  v_dept(0):='tom';
  dbms_output.put_line(v_dept(-1));
  dbms_output.put_line(v_dept.first);
  dbms_output.put_line(v_dept.last);
  dbms_output.put_line(v_dept.prior(0));
  dbms_output.put_line(v_dept.next(0));
end;
/

declare
  type dept_type is table of dept%rowtype index by binary_integer;
  v_dept dept_type;
begin
  for i in 1..5 loop
    select * into v_dept(i) from dept where deptno=i*10;
  end loop;
  dbms_output.put_line(v_dept.count);
  v_dept.delete(2);
  dbms_output.put_line(v_dept.count);
  dbms_output.put_line(v_dept(3).dname);
end;
/

非PL/SQL变量：
主机变量 & 宿主变量
var g_ename varchar2(10)

begin
  select ename into :g_ename from emp where empno=7499;
end;
/

select :g_ename from dual;
print g_ename

游标变量：声明 --> 打开 --> 获取 --> 关闭
declare
  cursor c1 is select ename,sal,deptno from emp where deptno=10;
  r1 c1%rowtype;
begin
  open c1;
  fetch c1 into r1;
  dbms_output.put_line(r1.ename||' '||r1.sal||' '||r1.deptno);
  fetch c1 into r1;
  dbms_output.put_line(r1.ename||' '||r1.sal||' '||r1.deptno);
  close c1;
end;
/

游标属性：
游标名字%rowcount 从游标中获取的行的数量
游标名字%found    从游标中得到了行返回true
游标名字%notfound 从游标中得不到行返回true
游标名字%isopen   游标打开返回true

declare
  cursor c1 is select ename,sal,deptno from emp where deptno=10;
  r1 c1%rowtype;
begin
  open c1;
  fetch c1 into r1;
  dbms_output.put_line(r1.ename||' '||r1.sal||' '||r1.deptno);
  dbms_output.put_line(c1%rowcount);
  if c1%isopen then
    dbms_output.put_line('true');
  end if;
  close c1;
end;
/

declare
  cursor c1 is select ename,sal,deptno from emp where deptno=10;
  r1 c1%rowtype;
begin
  open c1;
  loop
    fetch c1 into r1;
    exit when c1%notfound;
    dbms_output.put_line(r1.ename||' '||r1.sal||' '||r1.deptno);
  end loop;
  close c1;
end;
/

CLARK 2795 10
KING 1 10
MILLER 1530 10

游标for循环:
declare
  cursor c1 is select ename,sal,deptno from emp where deptno=10;
begin
  for r1 in c1 loop
    dbms_output.put_line(r1.ename||' '||r1.sal||' '||r1.deptno);
    exit when c1%rowcount>1;
  end loop;
end;
/

带参数的游标：
declare
  cursor c2 is select deptno from dept;b
  cursor c1 (p_deptno number) is
         select ename,sal,deptno from emp where deptno=p_deptno order by sal desc;
begin
  for r2 in c2 loop
    for r1 in c1(r2.deptno) loop
      exit when c1%rowcount>2;
      dbms_output.put_line(r1.ename||' '||r1.sal||' '||r1.deptno);
    end loop;
  end loop;
end;
/

参照游标：返回结果集
弱类型参照游标：没有规定返回值的参照游标
强类型参照游标：规定返回值

declare
  type test_ref_type is ref cursor; --弱类型
  c1 test_ref_type;
  r1 dept%rowtype;
  type emp_record is record
  (ename varchar2(10),
  sal number,
  job varchar2(10));
  r2 emp_record;
  type emp_ref_type is ref cursor RETURN emp%rowtype; --强类型
  c2 emp_ref_type;
  r3 emp%rowtype;
begin
  open c1 for select * from dept;
  fetch c1 into r1;
  dbms_output.put_line(r1.dname);
  close c1;
  open c1 for select ename,sal,job from emp where deptno=10;
  fetch c1 into r2;
  dbms_output.put_line(r2.ename);
  CLOSE C1;
  open c2 for select * from emp;
  fetch c2 into r3;
  dbms_output.put_line(r3.ename);
  close c2;
end;
/

var c1 refcursor

begin
  open :c1 for select * from dept;
end;
/

print c1
====================================================================
异常处理：
什么是异常？oracle报错信息的别名！

1.处理系统预定义异常
TOO_MANY_ROWS : SELECT INTO返回多行
INVALID_CURSOR :非法指针操作(关闭已经关闭的游标)
ZERO_DIVIDE :除数等于零
DUP_VAL_ON_INDEX :违反唯一性约束
ACCESS_INTO_NULL: 未定义对象
CASE_NOT_FOUND: CASE 中若未包含相应的 WHEN ，并且没有设置 ELSE 时
COLLECTION_IS_NULL: 集合元素未初始化
CURSER_ALREADY_OPEN: 游标已经打开
DUP_VAL_ON_INDEX: 唯一索引对应的列上有重复的值
INVALID_NUMBER: 内嵌的 SQL 语句不能将字符转换为数字
NO_DATA_FOUND: 使用 select into 未返回行，或应用索引表未初始化的元素时
SUBSCRIPT_BEYOND_COUNT:元素下标超过嵌套表或 VARRAY 的最大值
SUBSCRIPT_OUTSIDE_LIMIT: 使用嵌套表或 VARRAY 时，将下标指定为负数
VALUE_ERROR: 赋值时，变量长度不足以容纳实际数据
LOGIN_DENIED: PL/SQL 应用程序连接到 oracle 数据库时，提供了不正确的用户名或密码
NOT_LOGGED_ON: PL/SQL 应用程序在没有连接 oralce 数据库的情况下访问数据
PROGRAM_ERROR: PL/SQL 内部问题，可能需要重装数据字典＆ pl./SQL 系统包
ROWTYPE_MISMATCH: 宿主游标变量与 PL/SQL 游标变量的返回类型不兼容
SELF_IS_NULL: 使用对象类型时，在 null 对象上调用对象方法
STORAGE_ERROR: 运行 PL/SQL 时，超出内存空间
SYS_INVALID_ID: 无效的 ROWID 字符串
TIMEOUT_ON_RESOURCE: Oracle 在等待资源时超时
Transaction-backed-out : 由于发生死锁事务被撤消

一个报错的代码：
declare
  v_ename varchar2(10);
begin
  select ename into v_ename from emp where deptno=10;
end;
/

一个报错的代码被作为子程序：
declare
begin
declare
  v_ename varchar2(10);
begin
  select ename into v_ename from emp where deptno=10;
end;
declare
  v_empno number:=7839;
begin
  update emp set sal=sal+100 where empno=v_empno;
end;
end;
/


ORA-01422: exact fetch returns more than requested number of rows --> TOO_MANY_ROWS

declare
  v_ename varchar2(10);
begin
  select ename into v_ename from emp where deptno=10;
exception
  when too_many_rows then
    dbms_output.put_line('向标量填充太多元素！');
end;
/

declare
begin
declare
  v_ename varchar2(10);
begin
  select ename into v_ename from emp where deptno=10;
exception
  when too_many_rows then
    dbms_output.put_line('向标量填充太多元素！');
end;
declare
  v_empno number:=7839;
begin
  update emp set sal=sal+100 where empno=v_empno;
end;
end;
/

修改指定雇员的工资，如果工资<1000涨10%
declare
  v_empno number:=&p_empno;
  v_sal number;
begin
  select sal into v_sal from emp where empno=v_empno;
  if v_sal<1000 then
    update emp set sal=sal*1.1 where empno=v_empno;
  end if;
exception
  when no_data_found then
    dbms_output.put_line('雇员不存在！');
end;
/

2.处理非预定义异常
declare
  my_error exception; --用户声明
  pragma exception_init(my_error,-2292); --用户关联
  v_deptno number := &p_deptno;
begin
  delete dept where deptno=v_deptno; --oracle传播
exception
  when my_error then --用户捕获
    dbms_output.put_line('被参照的逐渐不可以删除！');
end;
/

向雇员表插入新雇员，部门不存在的异常做处理

3.处理没有考虑到的异常
declare
  v_empno number:=&p_empno;
begin
  update emp set sal=100000 where empno=v_empno;
exception
  when no_data_found then
    null;
  when others then
    dbms_output.put_line(sqlcode||' ; '||sqlerrm);
end;
/

使用函数sqlerrm打印oracle的后台消息
declare
  ora_mess varchar2(1000);
begin
  for i in 20000..20999 loop
    ora_mess:=sqlerrm(-i);
    dbms_output.put_line(ora_mess);
  end loop;
end;
/

4.用户自定义异常
declare
  my_err exception; --用户声明
  pragma exception_init(my_err,-20000);--用户关联
  v_empno number:=&p_empno;
  v_sal number:=&p_sal;
begin
  if v_sal<1000 then
    raise_application_error(-20000,'ok'); --用户传播
  else
   update emp set sal=v_sal where empno=v_empno;
  end if;
exception
  when my_err then --用户捕获
    dbms_output.put_line('工资太少！');
end;
/

在部门表，查询部门编号，如果存在返回 1 否则返回 0
declare
  v number;
begin
  select 1 into v from dept where deptno=&p_deptno;
  dbms_output.put_line('1');
exception
  when others then
    dbms_output.put_line(sqlcode||' ; '||sqlerrm);
--  when no_data_found then
--    dbms_output.put_line('0');
end;
/

匿名块：
====================================================================
命名块：
1.procedure 过程
2.function  函数
3.package   包
4.trigger   触发器

1.procedure
导入型形式参数：查询
create or replace procedure get_ename
(p_empno in number)
is
  v_ename emp.ename%type;
begin
  select ename into v_ename from emp where empno=p_empno;
  dbms_output.put_line(v_ename);
exception
  when no_data_found then
    dbms_output.put_line('查无此人！');
end;
/

调用命名块：
begin
  get_ename(7566);
end;
/

exec get_ename(7566);
-----------------------------------------------------------
导出型形式参数：查询
create or replace procedure get_ename
(p_empno in number,
 p_ename out varchar2)
is
begin
  select ename into p_ename from emp where empno=p_empno;
exception
  when no_data_found then
    dbms_output.put_line('查无此人！');
end;
/

declare
  v_ename varchar2(10);
begin
  get_ename(7839,v_ename);
  dbms_output.put_line(v_ename);
end;
/

var g_ename varchar2(10)
exec get_ename(7934,:g_ename);
-----------------------------------------------------------
创建过程 raise_sal 通过雇员编号涨工资10%,如果雇员不存在返回 “员工不存在”
create or replace procedure raise_sal
(p_empno number)
is
begin
  update emp set sal=sal*1.1 where empno=p_empno;
  if sql%notfound then
    dbms_output.put_line('员工不存在');
  end if;
end;
/
-----------------------------------------------------------
创建过程 fire_emp 通过雇员编号删除雇员,如果雇员不存在返回 “员工不存在”
create or replace procedure fire_emp
(p_empno number)
is
begin
  delete emp where empno=p_empno;
  if sql%notfound then
    dbms_output.put_line('员工不存在');
  end if;
end;
/
-----------------------------------------------------------
创建过程 add_emp 向emp表增加新雇员
create sequence seq_empno start with 7935 maxvalue 9999;

create or replace procedure add_emp
(p_ename varchar2,
 p_job varchar2 default 'CLERK',
 p_mgr number default 7698,
 p_hiredate date default sysdate,
 p_sal number default 1000,
 p_comm number default null,
 p_deptno number default 30)
is
begin
insert into emp values
(seq_empno.nextval,
 p_ename,
 p_job,
 p_mgr,
 p_hiredate,
 p_sal,
 p_comm,
 p_deptno);
end;
/
查看编译错误
show error
查看程序的源代码：
select text from user_source where name='ADD_EMP';

形式参数赋值的三种手段：
1.位置表示法
ename--> tom;job -->salesman
exec add_emp('TOM','SALESMAN');
2.混合表示法
ename--> tom; sal-->1500
exec add_emp('TOM',p_sal=>1500);
3.名称表示法
exec add_emp(p_ename=>'TOM',p_sal=>1500);
-----------------------------------------------------------
导入/导出型形式参数
根据雇员编号返回雇员工资
create or replace procedure get_sal
(p_no in out number)
is
begin
  select sal into p_no from emp where empno=p_no;
end;
/

var g_empno number
exec :g_empno:=7839;
exec get_sal(:g_empno);
-----------------------------------------------------------
事务控制:
create table t01 (x int);
向表中循环插入1111行数据，每100行提交一次
declare
  v_count number:=0;
begin
  for i in 1..1111 loop
    insert into t01 values (i);
    v_count:=v_count+1;
    if v_count=100 then
      commit;
      v_count:=0;
    end if;
  end loop;
  commit;
end;
/
-----------------------------------------------------------
自治事务:
create or replace procedure raise_sal
(p_empno number)
is
  pragma autonomous_transaction;
begin
  update emp set sal=sal*1.1 where empno=p_empno;
  if sql%notfound then
    dbms_output.put_line('员工不存在');
  end if;
  commit;
end;
/
-----------------------------------------------------------
动态sql语句 : 解析时是文本，执行时是命令
create or replace procedure test_cre
is
  sql_str varchar2(500);
begin
  sql_str:='create table tmp (x int)';
  execute immediate sql_str;
end;
/

*动态sql语句创建表的权限不能继承于角色
#############################################################
2.function : 一定要有返回值
create or replace function tax
(p_sal number)
return number
is
begin
  return p_sal*0.03;
end;
/

按照工资等级返回税
1级工资不缴税
2级工资缴税0.03
3级工资缴税0.05
4级工资缴税0.07
5级工资缴税0.1

create table d as select * from dept;
创建一个函数 valid_deptno，判断一个deptno在D表中存在否，如果存在返回true，负责返回false

create or replace function valid_deptno
(p_deptno number)
return boolean
is
  v number;
begin
  select 1 into v from d where deptno=p_deptno;
  return true;
exception
  when no_data_found then
    return false;
end;
/

declare
  v_deptno number:=&deptno;
begin
if valid_deptno(v_deptno) then
  dbms_output.put_line(v_deptno||' 部门存在');
else
  dbms_output.put_line(v_deptno||' 部门不存在');
end if;
end;
/

SYS@ orcl> drop user scott cascade;
SYS@ orcl> @?/rdbms/admin/utlsampl

创建一个函数根据雇员的编号返回雇员的姓名和工资
create or replace function get_ename
(p_empno number,
p_sal out number)
return varchar2
is
  v_ename varchar2(10);
begin
  select ename,sal into v_ename,p_sal from emp where empno=p_empno;
  return v_ename;
end;
/

var g_ename varchar2(10)
var g_sal number
exec :g_ename:=get_ename(7788,:g_sal);

create or replace function get_ename
(p_empno number)
return varchar2
is
  v_ename varchar2(20);
begin
  select ename||' '||sal into v_ename from emp where empno=p_empno;
  return v_ename;
end;
/

确定性函数（确定返回值函数）：
create or replace function test
(p_chr varchar2)
return varchar2
is
begin
  dbms_lock.sleep(1);
  return p_chr;
end;
/

begin
  for i in 1..10 loop
    insert into t01 values ('A');
  end loop;
end;
/

select x from t01;
select test(x) from t01;

create or replace function test
(p_chr varchar2)
return varchar2 deterministic
is
begin
  dbms_lock.sleep(1);
  return p_chr;
end;
/

grant connect,resource to tom identified by tom;

定义者：
create or replace function tax
(p_empno number)
return number
is
  v_sal number;
begin
  select sal into v_sal from emp where empno=p_empno;
  return v_sal*0.03;
end;
/

调用者：
create or replace function tax
(p_empno number)
return number
authid current_user
is
  v_sal number;
begin
  select sal into v_sal from emp where empno=p_empno;
  return v_sal*0.03;
end;
/

通过名称查找源代码：
select text from user_source where name='TAX';

第3种命名块：package(header+body)
package header:程序的声明
--------------------------------------------------
3.package : procedure & function 的集合

create or replace package pk_emp
is
procedure get_emp
(p_empno number,p_ename out varchar2);
procedure fire_emp
(p_empno number);
procedure fire_emp
(p_ename varchar2);
procedure add_emp (p_ename varchar2,p_deptno number);
function tax
(p_empno number)
return number;
function tax2
(p_sal number)
return number;
end;
/
--------------------------------------------------
package body:程序的代码主体
--------------------------------------------------
create or replace package body pk_emp
is
function valid_deptno (p_deptno number) return boolean
is
  v number;
begin
  select 1 into v from dept where deptno=p_deptno;
  return true;
exception
  when no_data_found then
    return false;
end valid_deptno;
procedure get_emp
(p_empno number,p_ename out varchar2)
is
begin
  select ename into p_ename from emp where empno=p_empno;
end get_emp;
procedure fire_emp
(p_empno number)
is
begin
  delete emp where empno=p_empno;
end fire_emp;
procedure fire_emp
(p_ename varchar2)
is
begin
  delete emp where ename=p_ename;
end fire_emp;
procedure add_emp (p_ename varchar2,p_deptno number)
is
begin
  if valid_deptno(p_deptno) then
    insert into emp (empno,ename,deptno) values
    (seq_empno.nextval,p_ename,p_deptno);
  else
    dbms_output.put_line('specified deptno not exists!');
  end if;
end add_emp;
function tax
(p_empno number)
return number
is
  v_sal number;
begin
  select sal into v_sal from emp where empno=p_empno;
  return v_sal*0.03;
end tax;
function tax2
(p_sal number)
return number
is
begin
  return p_sal*0.03;
end tax2;
end;
/
--------------------------------------------------
包体加密:
wrap iname=1.sql
@1.plb

包中子程序的重载：包中允许存在同名的子程序，一定得能通过形参区分子程序
create or replace package emp_pack
is
procedure get_sal
(p_empno in number,
 p_sal out number);
procedure get_sal
(p_ename in varchar2,
 p_sal out number);
end;
/
create or replace package body emp_pack
is
procedure get_sal
(p_empno in number,
 p_sal out number)
is
begin
  select sal into p_sal from emp where empno=p_empno;
exception
  when no_data_found then
    dbms_output.put_line('查无此人！');
end;
procedure get_sal
(p_ename in varchar2,
 p_sal out number)
is
begin
  select sal into p_sal from emp where ename=p_ename;
exception
  when no_data_found then
    dbms_output.put_line('查无此人！');
end;
end;
/

包的私有函数：
create or replace package emp_pack
is
procedure add_emp
(p_ename varchar2,
 p_job varchar2 default 'CLERK',
 p_mgr number default 7698,
 p_hiredate date default sysdate,
 p_sal number default 1000,
 p_comm number default null,
 p_deptno number default 30);
end;
/
create or replace package body emp_pack
is
function valid_deptno --私有函数，声明部分不存在于package header
(p_deptno number)
return boolean
is
  v number;
begin
  select 1 into v from dept where deptno=p_deptno;
  return true;
exception
  when no_data_found then
    return false;
end;
procedure add_emp
(p_ename varchar2,
 p_job varchar2 default 'CLERK',
 p_mgr number default 7698,
 p_hiredate date default sysdate,
 p_sal number default 1000,
 p_comm number default null,
 p_deptno number default 30)
is
begin
if valid_deptno(p_deptno) then
insert into emp values
(seq_empno.nextval,
 p_ename,
 p_job,
 p_mgr,
 p_hiredate,
 p_sal,
 p_comm,
 p_deptno);
else
  dbms_output.put_line(p_deptno||' 部门不存在，请重试！');
end if;
end;
end;
/
##################################################################################
第4种命名块：trigger (触发器)
一张表最多允许放12个触发器
触发器中不允许使用commit & rollback,除非使用自治事务

表级别的触发器:
create or replace trigger sec_emp
before update on emp
begin
  dbms_output.put_line('emp updated!');
end;
/

create or replace trigger sec_emp
after update on emp
begin
  dbms_output.put_line('emp updated!');
end;
/

列级别触发器（只针对update）
create or replace trigger sec_emp
before update of sal on emp
begin
  dbms_output.put_line('emp updated!');
end;
/

在触发器内部判断dml操作的种类使用进行时
create or replace trigger sec_emp
before update of sal or delete or insert on emp
begin
  if inserting then
    dbms_output.put_line('emp insert!');
  elsif updating then
    dbms_output.put_line('emp updated!');
  elsif deleting then
    dbms_output.put_line('emp delete!');
  end if;
end;
/

行级别触发器:
create or replace trigger sec_emp
before update on emp for each row
begin
  dbms_output.put_line('emp updated!');
end;
/

行级别触发器有能力保留新老数据状态:
create or replace trigger sec_emp
before update on emp for each row
declare
  --私有变量
begin
  dbms_output.put_line('Old value: '||:old.sal||' New value: '||:new.sal);
end;
/

创建测试表
create table e as select * from emp;
create table d as select * from dept;

在EMP表上放置触发器，截获update操作，如果EMP表的工资被修改，在触发器中对E表工资做同步修改！
用触发器实现E表与EMP表工资的同步！
create or replace trigger up_emp
after update of sal on emp for each row
declare
  pragma autonomous_transaction;
begin
  if :new.sal<>:old.sal then
    update e set sal=:new.sal where empno=:old.empno;
  end if;
  commit;
end;
/

如果在D表中删除一个部门，将E表中对应的部门员工解雇！
--------------------------------------------------------------
如果在D表中删除一个部门，将E表中对应的部门编号修改为null！
如果在D表中修改一个部门编号，将E表中对应的部门编号做相同修改
create or replace trigger del_d
after delete on d for each row
begin
  if :new.deptno is null then
    update e set deptno=:new.deptno where deptno=:old.deptno;
  end if;
end;
/

-----------------------------------------------------------------------------
创建一个触发器，只允许周一到周五每天8点和9点两个小时修改emp表数据
create or replace trigger tr_emp_update
before update on emp for each row
begin
  if to_char(sysdate,'dy') in ('sat','sun') or to_char(sysdate,'hh24') not in ('08','09') then
    raise_application_error(-20000,'非工作时间段不能修改数据！');
  end if;
end;
/


drop table t01 purge;
create table t01 (x int);
insert into t01 values (1);
commit;

create or replace trigger tr_t01
before update on t01 for each row
begin
  dbms_output.put_line('Old value: '||:old.x||' New value: '||:new.x);
end;
/

session 1: update t01 set x=x+1;
session 2: update t01 set x=x+1;
session 1: commit;
session 2: 看结果

丢失更新：
create table t2 (id int,b char);
insert into t2 values (10,'Y');
insert into t2 values (1,'N');
insert into t2 values (5,'Y');
insert into t2 values (6,'N');
insert into t2 values (9,'Y');
insert into t2 values (4,'N');
insert into t2 values (7,'Y');
insert into t2 values (3,'Y');
insert into t2 values (2,'Y');
insert into t2 values (8,'N');

session 1:
update t2 set b='N' where id=(select min(id) from (select id from t2 where b='Y'));
session 2:
update t2 set b='N' where id=(select min(id) from (select id from t2 where b='Y'));
session 1: commit;

避免丢失更新：
declare
cursor c1 is
select id from t2 where id=
(select id from
  (select id from t2 where b='Y' order by id)
 where rownum<2)
for update of b;
begin
  for r in c1 loop
    exit when c1%rowcount>2;
    update t2 set b='N' where current of c1;
  end loop;
end;
/

DDL触发器：截获ddl操作
create table log_ddl
(LOGON_FROM VARCHAR2(30),
LOGON_TIME TIMESTAMP,
action varchar2(10),
OBOWNER VARCHAR2(30),
OBTYPE VARCHAR2(30),
OBNAME VARCHAR2(30));

CREATE OR REPLACE TRIGGER tr_drop
before drop
ON schema
begin
  insert into scott.log_ddl values
(SYS.LOGIN_USER,
CURRENT_TIMESTAMP,
'DROP',
SYS.DICTIONARY_OBJ_OWNER,
SYS.DICTIONARY_OBJ_TYPE,
SYS.DICTIONARY_OBJ_NAME);
end;
/

系统触发器：
conn / as sysdba
CREATE OR REPLACE TRIGGER TRIGGER_RESTRICT_LOGON
AFTER LOGON ON DATABASE
DECLARE
 RESTRICTED_USER VARCHAR2(32) := 'SCOTT';
 ALLOWED_IP      VARCHAR2(16) := '172.25.0.10';
 LOGON_USER      VARCHAR2(32);
 CLIENT_IP       VARCHAR2(16);
BEGIN
 LOGON_USER := SYS_CONTEXT('USERENV','SESSION_USER');
 CLIENT_IP  := NVL(SYS_CONTEXT('USERENV','IP_ADDRESS'), 'NULL');
  IF LOGON_USER = RESTRICTED_USER AND CLIENT_IP <> ALLOWED_IP THEN
   RAISE_APPLICATION_ERROR(-20001, RESTRICTED_USER || ' is not allowed to connect from ' || CLIENT_IP);
 END IF;
END;
/