(oracle数据库基础)第七章 PL/SQL子程序设计

--1. 子程序

   PL/SQL程序块可以是一个匿名程序块也可以是一个命名的程序块。

   Oracle提供了四种类型的子程序：过程、函数、包和触发器。

 

--2. 过程

   过程是存储在数据库中的一段存储程序，有时也定义为存储过程。当创建过程时，系统会对其进行编译，并将执行代码存储到数据库中。

   定义过程的语法：

  create [orreplace ] procedure <procedure_name>

  (<arg1 [mode] ,datatype>,……)--mode有三种形式：in、out和in out。

  is |as

  [local declaration]

  begin

  executable statements

  [exceptionexception handler]

  end [procedurename]

 

  删除存储过程的命令：dropprocedure <procedure_name>;

 

--2.1 参数模式in

--例子1：定义过程，该过程通过输入员工号查找员工姓名

createorreplaceprocedure

emp_empno(v_empno emp.empno%type)

as

 v_emp emp%rowtype;--定义隐式记录v_emp

 empno_out_of_rangeexception;--用户自定义异常

begin

 if v_empno<=7000or v_empno>=8000then

  raise empno_out_of_range;

 endif;

 select *into v_empfrom empwhere empno=v_empno;

 ifSQL%foundthen

  dbms_output.put_line(v_emp.ename);

 endif;

 exception

  when empno_out_of_rangethen

    raise_application_error(-20010,'empno is out ofrange');

  when no_data_foundthen--在提取时没有找到数据

    dbms_output.put_line('not found record');

  when too_many_rowsthen--在提取时，返回行数超过一行

    dbms_output.put_line('too many record');

endemp_empno;

 

--例子2：动态SQL执行DML语句，将用户输入的值插入表sem_108

createorreplaceprocedure sem_108_insert

(

  v_id in sem_108.id%type,

  v_name in sem_108.name%type

)

as

  str_sql varchar2(500);

begin

  str_sql := 'insert into sem_108values(:v_id,:v_name)';--注意变量在str_sql字符串中的写法

  --动态sql：excute immediate 动态SQL语句 using绑定参数列表 returning into输出参数列表

  --using子句按顺序将输入的值绑定到变量

  executeimmediate str_sqlusing v_id, v_name;

  ifSQL%foundthen

    dbms_output.put_line('inserted OK!');--奇怪这里没有输出来？？？？？是因为在common窗口吗？

  endif;

endsem_108_insert;

 

--体验动态SQL中的returning子句

declare

  v_id sem_108.id%type;

  v_name sem_108.name%type:='xk';

  sql_str varchar2(500);

begin

  sql_str :='update sem_108 set id=2 wherename=:v_name returning id into :v_id';

  executeimmediate sql_strusing v_namereturninginto v_id;

  dbms_output.put_line(v_id);--输出上一个return子句的参数 2

end;

 

--2.2 参数模式out

--例子：定义过程，根据输入员工号返回员工姓名

createorreplaceprocedure get_ename

(

 v_empno in emp.empno%type,

 v_ename out emp.ename%type

)

as

 cursor c_empisselect empno,enamefrom empwhere empno=v_empno;

 v_emp c_emp%rowtype;

begin

  open c_emp;

  fetch c_empinto v_emp;

  v_ename:=v_emp.ename;

  close c_emp;

endget_ename;

--测试

declare

   name emp.ename%type;

begin

  get_ename(7369,name);

  dbms_output.put_line(name);

end;

 

2.3参数模式inout

--例子：定义过程，将所有员工工资提高5%输出，此例子一共用了三个循环...

--说明，过程的定义放在了一个匿名PL/SQL块中；参数sal_list的数据类型是index_by表；参数sal_list的模式是in out 类型，

      --因此在调用过程set_sal()之前，sal_list存储了原来所有员工工资信息，在调用之后，sal_list值发生变化，并返回。

declare

  cursor c_empisselect salfrom emporderby empno;--定义游标

  type sal_typeistableofnumber(7,2)indexbybinary_integer;--定义集合

  sal_list sal_type;--定义一个以上集合类型的变量

  v_count binary_integerdefault1;

  --定义过程开始

  procedure set_sal(sal_listinout sal_type)as

  begin

   for iin1..sal_list.countloop

      sal_list(i):=sal_list(i)*1.05;--遍历集合，将集合中的元素值提高5%

    endloop; 

  end set_sal;

--定义过程结束

begin

  open c_emp;

  loop

   fetch c_empinto sal_list(v_count);--fetch游标到集合中，集合中此时遍历计算

   exitwhen c_emp%notfound;

   v_count:=v_count+1;--累加

  endloop;

  set_sal(sal_list);

  for iin1..sal_list.countloop

   dbms_output.put_line(sal_list(i));

  endloop;

end;

 

--3. 函数：过程和函数的区别在于他们的调用方式。调用过程没有返回值，而调用函数需将函数返回值赋给某一变量。

create [orreplace ] function<function_name>

(<arg1 [mode],datatype>,…)

return<datatype>is |as

[local declaration]

begin

executable statements

[exceptionexception handler]

end [functionname]

 

--例1：定义函数，根据不同职务，显示在工资总额中的百分比

createorreplacefunction get_sal_percent

(

 v_job emp.job%type

)

returnnumber

as

 cursor c_empisselect salfrom empwhere job=v_job;

 v_emp c_emp%rowtype;

 v_sal emp.sal%type:=0;

 v_sal_sum emp.sal%type;

begin

  open c_emp;

  loop

    fetch c_empinto v_emp;

    exitwhen c_emp%notfound;

    v_sal:=v_sal+v_emp.sal;

  endloop;

  close c_emp;

  selectsum(sal)into v_sal_sumfrom emp;

  v_sal:=v_sal/v_sal_sum;

  return v_sal;

endget_sal_percent;

--在命令窗口中调用函数分三步：

1.定义变量

SQL>variablepernumber;

2.调用函数(但是oracle11g中第二部就直接输出变量了)

SQL>execute :per:=get_sal_percent('CLERK');

3.显示变量

SQL>printper

 

--例2：调用函数get_sal_percent，显示所有职务在工资总额中的百分比

declare

  cursor c_empisselectdistinct jobfrom emporderby job;

  v_job emp.job%type;

  v_sal_per emp.sal%type;

begin

  open c_emp;

  loop

    fetch c_empinto v_job;

    exitwhen c_emp%notfound;

    v_sal_per :=get_sal_percent(v_job);--调用函数get_sal_percent

    dbms_output.put_line('The percent salaryof '||rpad(v_job,12,'')||' is '||v_sal_per||' ! ');

  endloop;

  close c_emp;

end;

 

--4. 包

  包是一组相关过程、函数、变量、常量和游标等PL/SQL程序设计元素的组合，它具有面向对象程序设计语言的特点，

  是对PL/SQL程序设计元素的封装。

  一个包由两个分开的部分组成，包定义和包主体分开编译，并作为两部分分开的对象存放在数据库字典中。

  --包定义：包定义部分声明包含包内变量、常量、游标、子程序和异常错误处理等元素，这些是包的共有元素。

  create [orreplace]package package_name{as|is}

  public_variable_declarations |

  public_type_declarations |

  public_exception_declarations |

  public_cursor_declarations |

  function_declarations |

  procedure_specifications

  end [package_name]

  --包主体：定义了包定义部分所声明的游标和子程序等，在包主体中还可以声明包的私有元素。

  create [orreplace]packagebody package_name{as|is}

  private_variable_declarations |

  private_type_declarations |

  private_exception_declarations |

  private_cursor_declarations |

  function_declarations |

  procedure_specifications

  end [package_name]

 

  说明：用户签名使用的dbms_output.put_line()，实际上就是调用包dbms_output中的put_line函数。

 

--例1：创建包，包括两个功能，一是根据输入的部门号查询该部门所有员工信息，二是列出当天生日员工号和员工姓名，以及所在部门名称。

  --包定义

createorreplacepackage p_emp as

    versionvarchar2(11):='version 1.0';

    author varchar2(15):='buaa sem 108 xk';

    procedure emp_info(v_deptno emp.deptno%type);--定义过程emp_info

    function birthday(v_empno emp.empno%type)returnbinary_integer;--定义函数birthday

end p_emp;

 

  --包主体

createorreplacepackagebody p_emp as

  --过程emp_info开始

  procedure emp_info(v_deptno emp.deptno%type)ascursor c_empisselect *from empwhere deptno=v_deptno;

  v_emp c_emp%rowtype;

  begin

      open c_emp;

        loop

          fetch c_empinto v_emp;

          exitwhen c_emp%notfound;

         dbms_output.put_line(v_emp.empno||','||v_emp.ename||','||v_emp.job||','||

                              to_char(v_emp.hiredate,'YYYY-MM-DD')||','||v_emp.sal||','||v_emp.comm);

        endloop;

      close c_emp;

  end;

  --过程emp_info结束

  --函数birthday开始

  function birthday(v_empno emp.empno%type)returnbinary_integeras sys_datevarchar2(5);

      emp_date varchar2(5);

      temp date;

      lenbinary_integer;

  begin

      select hiredateinto tempfrom empwhere empno=v_empno;   

      emp_date:=substr(to_char(temp,'YYYY-MM-DD'),6,5);

      sys_date:=substr(to_char(SYSDATE,'YYYY-MM-DD'),6,5);

      for iin1..5loop

        ifsubstr(emp_date,i,1)<>substr(sys_date,i,1)then

          return -1;

        endif;

      endloop;

      return1;

 end birthday;

 --函数birthday结束

end p_emp;

 

--执行

1.调用过程

SQL>execute p_emp.emp_info(10);

2.调用函数

SQL>variable marknumber;

SQL>execute:mark:=p_emp.birthday(7369);

SQL>print mark;--oracle11g不需要这一步，上一步已经打印出来了

 

--编写匿名函数，完成第二个功能需求

declare

  cursor c_empisselect e.empno, e.ename, d.dnamefrom emp e, dept dwhere e.deptno=d.deptno;

  v_emp c_emp%rowtype;--定义一个游标类型的变量

begin

  dbms_output.put_line('Birthdat List');

  open c_emp;

  loop

    fetch c_empinto v_emp;

    exitwhen c_emp%notfound;

    --dbms_output.put_line(v_emp.empno);

    if p_emp.birthday(v_emp.empno)=1then

     dbms_output.put_line(v_emp.ename||', '|| v_emp.ename||', '||v_emp.dname);

    endif;

  endloop;

  close c_emp;

end;

 

--使用DBMS_SQL包

先将要执行的SQL语句或一个语句块放到一个字符串变量中。

使用DBMS_SQL包的parse过程来分析该字符串。

使用DBMS_SQL包的bind_variable过程来绑定变量。

使用DBMS_SQL包的execute函数来执行语句。

--例1：使用DBMS_SQL包执行DDL语句，根据用户名输入的表名、字段名及字段类型建表。

createorreplaceprocedure proc_dbms_sql

(

    table_name varchar2,       --表名

    field_name1varchar2,     --字段名

    datatype1 varchar2,          --字段类型

    field_name2varchar2,     --字段名

    datatype2 varchar2            --字段类型

)

as

    v_cursor number;               --定义光标

    v_string varchar2(200);     --定义字符串变量

    v_row number;                    --行数

begin

   v_cursor:=dbms_sql.open_cursor;    --为处理打开光标

    v_string:='create table '||table_name||'('||field_name1||' '||datatype1||','||field_name2||' '||datatype2||')';

    dbms_sql.parse(v_cursor,v_string,dbms_sql.native); --分析语句

   v_row:=dbms_sql.execute(v_cursor);  --执行语句

   dbms_sql.close_cursor(v_cursor);    --关闭光标

end;

--用system用户登录，给scott用户授创建表的权限

grantcreatetableto scott;

SQL>execute proc_dbms_sql('wanghui','id','number(2)','name','varchar2(3)');

 

--5. 触发器

/*

触发器是一种特殊类型的存储过程，它不同于前面介绍过的存储过程。

触发器主要是通过事件进行触发而被执行的，而存储过程可以通过存储过程名字而被直接调用。

当对某一表进行诸如insert、update和delete操作时，Oracle就会自动执行触发器所定义的SQL语句，从而确保对数据的处理符合由这些SQL语句所定义的规则。

*/

create [orreplace]trigger触发器名触发时间触发事件--触发事件如update等

on {table_or_view_name|database}--触发器所在的表或视图名，数据库名暂不考虑

[referencing [old [as] <old>] [new [as] <new> ]--old代表原来的值，new代表新值

[foreachrow[when condition]]--for each row表示对触发语句所影响的每一行，触发器都触发一次。缺少for each row则代表只触发一次。

trigger_body

 

--禁用和启用触发器

alter triger触发器名enable;

alter triger触发其名disable;

 

--禁用或启用表的所有触发器

alter triger表名enableall triger;

alter triger表名disableall triger;

 

--删除触发器

drop tirger触发其名;

 

--例1：创建触发器，该触发器记录了每次对表emp1的insert操作状态

--(1)赋值表emp但不复制数据

createtable emp1asselect *from empwhere1=2;

 

createsequence seq_emp1

startwith1

incrementby1

maxvalue9999999

cycle;

--(2)创建emp1_log记录每次insert操作状态

createtable emp1_log

(

  act_id number(7),

  act_time date,

  act_descptionvarchar2(20) 

);

--(3)创建触发器t_emp1

createorreplacetrigger t_emp1

afterinserton emp1

foreachrow

begin

 insertinto emp1_logvalues

 (seq_emp1.nextval,SYSDATE,'Inseted OK!');

end;

--测试

insertinto emp1(empno)values(7369);

insertinto emp1(empno)values(7370);

insertinto emp1(empno)values(7371);

insertinto emp1(empno)values(7372);

insertinto emp1(empno)values(7373);

select *from emp1_log

 

--5.1 reference选项

--例2：创建触发器，在对员工工资修改时，保留原来工资记录。

--(1)复制表emp为表emp1

createtable emp1asselect *from emp;

--(2)创建old_emp1记录更改时的前后工资。

createtable old_emp1

(

  empno number(4),

  old_sal number(9,2),

  new_sal number(9,2) 

);

--(3)创建触发器t_emp1

createorreplacetrigger t_emp1

beforeupdateof salon emp1

referencingoldas onewas n

foreachrow

begin

  insertinto old_emp1values(:o.empno, :o.sal, :n.sal);--采用" :<别名>.<列名> "的形式

end;

--(4)更改表emp1中数据，并查看表old_emp1中数据

update emp1set sal=900where empno=7369;

select *from old_emp1;

 

--5.2 for each row选项

--例3：创建触发器，在对员工工资修改时，保留原来的记录

--(1)复制表emp为表emp1

droptable emp1;

createtable emp1asselect *from emp;

--(2)创建触发器t_emp1

createorreplacetrigger t_emp1

beforedeleteon emp1

foreachrow

begin

  dbms_output.put_line('trigger executed!');

end;

--侧送

delete emp1where deptno=10;

--输出结果

triggerexecuted!

triggerexecuted!

triggerexecuted!

 

--5.3 when选项

--例4：创建触发器，在新员工入职时，只有销售部门才判断新入职员工的工资水平和该不么平均工资水平的大小关系。

--(此例子没看到输出语句，难道是11g的原因？)

--(1)复制表emp为表emp1

droptable emp1;

createtable emp1asselect *from empwhere1=2;

--(2)创建过程set_sal。

createorreplaceprocedure check_sal

(

   v_sal in emp1.sal%type

)

as

avg_sal emp1.sal%type;

begin

  selectavg(sal)into avg_salfrom empwhere job='SALESMAN';

  if v_sal>=avg_salthen

    dbms_output.put_line('the salary of theemployee is higer than the average salaty of sales department!');

  else

    dbms_output.put_line('the salary of theemployee is lower than the average salaty of sales department!');

  endif;

endcheck_sal;

--(3)创建触发器t_emp1

createorreplacetrigger t_emp1

afterinserton emp1

foreachrow

  when(new.job='SALESMAN')

  call check_sal(:new.sal)

--(4)在表emp1中插入数据，并查看表emp1中数据

SQL>insertinto emp1(empno, ename,job, sal)values (7369,'xk','SALESMAN',1000);

1row inserted

SQL>insertinto emp1(empno, ename,job, sal)values (7370,'wl','CLERK',1000);

1row inserted

 

--5.4 instead of 选项(主要是使不可被修改的视图能够支持修改)

--例5为例4.3创建触发器

--(1)执行下列语句，由于违反可更新视图的原则，系统提示：无法修改与非键值对应表保存的列

insertinto emp_dept(empno,ename, sal, deptno, dname, loc)

values (7000,'xk',1000,50,'sem108','buaa');

--(2)创建触发器t_emp_dept

createorreplacetrigger t_emp_dept

insteadofinserton emp_dept

foreachrow

begin

  insertinto emp1(empno,ename,sal)values (:new.empno,:new.ename, :new.sal);

  insertinto dept1(deptno,dname,loc)values (:new.deptno,:new.dname, :new.loc);

end;

--(3)测试

insertinto emp_dept(empno,ename, sal, deptno, dname, loc)

values (7000,'xk',1000,50,'sem108','buaa');

 

select *from emp1;

select *from dept1;

 

--5.5 条件谓语 inserting、updating和deleting

--例6判断DML操作类型

--(1)创建触发器t_emp1

createorreplacetrigger t_emp1

afterinsertorupdateordeleteon emp1

foreachrow

declare

  action_type varchar2(20);

begin

  if insertingthen

    action_type :='inserted OK!';

  endif;

  if updatingthen

    action_type :='updated OK!';

  endif;

  if deletingthen

    action_type :='deleted OK!';

  endif;

  insertinto emp1_logvalues (seq_emp1.nextval,sysdate,action_type);

end;

--(2)一次执行insert、update和delete语句，并查看表emp1_log中的数据

insertinto emp1(empno, ename,job, sal)values (7369,'xk','SALESMAN',1000);

update emp1set ename='w1'where empno=7369;

delete emp1where empno=7369;

select *from emp1_log;

 

--6. 数据字典

视图名称：user_source   

属性：type

属性值：procedure,function,package,trigger

--例1：查找scott用户创建的过程

select textfrom user_sourcewherename=upper('set_sal')andtype=upper('procedure');

--例2：查找scott用户创建的函数

select textfrom user_sourcewherename=upper('get_sal_percent')andtype=upper('function');

--例3：查找scott用户创建的包

select textfrom user_sourcewherename=upper('p_emp')andtype=upper('package');

--例4：查找scott用户创建的触发器

select textfrom user_sourcewherename=upper('t_emp_dept')andtype=upper('trigger');