子程序和程序包

 

七、子程序和程序包
  总结：
    子程序是命名的PL/SQL块，可带参数并可在需要时随时调用。
    PL/SQL有两种类型的子程序，即过程和函数。
    过程用于执行特定的任务，函数用于执行任务并返回值。
    程序包是对相关类型、变量、常量、游标、异常、过程和函数的封装。
    程序包由包规范和包主体两部分组成。
    包规范是包的接口，包含公用对象及其类型。
    包主体实现包规范中的游标和子程序，包主体中的声明仅限于在包内使用。
    程序包中游标的定义分为游标规范和游标主体两部分。

  语法及示例：
  1、存储过程
  创建存储过程的语法：
  CREATE [OR REPLACE] PROCEDURE procedure_name
    [(parameter_list)]
    {IS|AS}
     [local_declarations]
    BEGIN
      executable_statements
    [EXCEPTION
      exception_handlers]
    END [procedure_name];
  其中：procedure_name是过程的名称。
        parameter_list是参数列表。
        local_declarations是局部声明。
        executable_statements是可执行语句。
        exception_handlers是异常处理程序。
  示例1：演示创建过程(参数列表中为IN参数赋予一个默认值，不能为OUT、IN OUT参数赋予默认值)
  create or replace procedure find_emp(emp_no in number:=7900)
  as
    empname varchar2(20);
  begin
    select ename into empname from emp where empno=emp_no;
    dbms_output.put_line('雇员姓名是 '||empname);
  exception
    when no_data_found then
      dbms_output.put_line('雇员编号未找到');
  end find_emp;
 
  调用过程：EXECUTE procudure_name(parameters_list);
  也可以在过程里面调用，直接写上procudure_name而不必写EXECUTE。

  示例2：演示创建带OUT参数的过程
  create or replace procedure test(value1 varchar2,value2 out number)
  is
    identity number;
  begin
    select sal into identity from emp where empno=value1;
    if identity<2000 then
       value2:=1000;
    else
       value2:=500;
    end if;
  end;

  调用带OUT参数的过程：
  declare
    value2 number;
  begin
    test('7900',value2);
    dbms_output.put_line(value2);
  end;

  示例3：演示创建带IN OUT参数的过程
  create or replace procedure swap(p1 in out number,p2 in out number)
  is
    v_temp number;
  begin
    v_temp:=p1;
    p1:=p2;
    p2:=v_temp;
  end;

  调用带IN OUT参数的过程：
  declare
    num1 number:=100;
    num2 number:=200;
  begin
    swap(num1,num2);
    dbms_output.put_line('num1= '||num1);
    dbms_output.put_line('num2= '||num2);
  end;

  示例4：将过程的执行权限授予其他用户
  GRANT EXECUTE ON find_emp TO scott;
  GRANT EXECUTE ON swap TO PUBLIC;
  将find_emp过程的执行权限授予给用户scott，将执行swap过程的权限授予所有数据库用户。
 
  删除过程语法：DROP PROCEDURE procudure_name;

  2、函数
  定义函数的语法如下：
  CREATE [OR REPLACE] FUNCTION function_name
    [(parameter_list)]
    RETURN datatype
    {IS|AS}
     [local_declarations]
    BEGIN
      executable_statements
    [EXCEPTION
      exception_handlers]
    END [function_name];
  其中：function_name是函数的名称。
        parameter_list是参数列表。
        local_declarations是局部声明。
        executable_statements是可执行语句。
        exception_handlers是异常处理程序。
  使用函数时注意：形式参数必须只使用数据库类型，不得使用PL/SQL类型。函数的返回类型也必须是数据库类型。
  函数不能单独执行，只能通过SQL语句或PL/SQL程序块来调用。
  示例5：演示如何创建函数
  create or replace function fun_hello
  return varchar2 is
  begin
    return '朋友，您好';
  end;
  调用函数：select fun_hello from dual;

  函数的授权：同过和的授权一样具体请看示例4。
  删除函数：DROP FUNCTION function_name

                                         过程和函数的差异
             过程                                                           函数
   作为PL/SQL语句执行                                             作为表达式的一部分调用
   在规范中不包含RETURN子句                                       必须在规范中包含RETURN子句
   不返回任何值                                                   必须返回单个值
   可以包含RETURN语句，但是与函数不同，它不能用于返回值           必须包含至少一条RETURN语句

  3、程序包
  创建包规范的语法：
  CREATE [OR REPLACE] PACKAGE package_name
  IS|AS
  [Public type and item declarations]
  [Subprogram specifications]
  END [package_name];
  其中：package_name是包的名称。
        Public type and item declarations是声明类型、常量、变量、异常和游标等。
        Subprogram specifications声明PL/SQL子程序。
 
  示例6：演示创建程序包规范
  create or replace package pack_op is
    procedure pro_print_ename(id number);
    procedure pro_print_sal(id number);
    function fun_re_date(id number) return date;
  end;

  创建包主体的语法：
  CREATE [OR REPLACE] PACKAGE BODY package_name
  IS|AS
  [Public type and item declarations]
  [Subprogram bodies]
  [BEGIN
      Initialization_statements]
  END [package_name];
  其中：package_name是包的名称。
        Public type and item declarations是声明类型、常量、变量、异常和游标等。
        Subprogram bodies是定义公共和私有PL/SQL子程序。

  示例7：演示创建程序包主体
  create or replace package body pack_op is
    procedure pro_print_ename(id number) is
    name emp.ename%type;
  begin
    select ename into name from emp where empno=id;
    dbms_output.put_line('职员姓名：'||name);
  end pro_print_ename;
  procedure pro_print_sal(id number) is
    salary emp.sal%type;
  begin
    select sal into salary from emp where empno=id;
    dbms_output.put_line('职员工资：'||salary);
  end pro_print_sal;
    function fun_re_date(id number) return date is
    bedate emp.hiredate%type;
  begin
    select hiredate into bedate from emp where empno=id;
    return bedate;
  end fun_re_date;
  end pack_op;

  示例8：调用程序包中创建的过程和函数
  exec pack_op.pro_print_ename(7900);
  exec pack_op.pro_print_sal(7900);
  select pack_op.fun_re_date(7900) from dual;

  示例9：演示程序包中的游标
    创建包规范
    create or replace package pack_emp is
      cursor cur_emp return emp%rowtype;
      procedure pro_cur;
    end pack_emp;

    创建包主体
    create or replace package body pack_emp is
      cursor cur_emp return emp%rowtype is
        select * from emp;
      procedure pro_cur is
      rec_emp emp%rowtype;
    begin
      open cur_emp;
      loop
        fetch cur_emp into rec_emp;
        exit when cur_emp%notfound;
        if rec_emp.sal<1000 then
          dbms_output.put_line('员工工资：'||rec_emp.sal||'，需加倍努力争取提高工资');
        elsif rec_emp.sal>=1000 and rec_emp.sal<2000 then
          dbms_output.put_line('员工工资：'||rec_emp.sal||'，工资一般，争取搞个部门经理做做');
        else
          dbms_output.put_line('员工工资：'||rec_emp.sal||'，工资不错，争取搞个总经理做做');
        end if;
      end loop;
   end pro_cur;
   end pack_emp;

   调用程序包中的过程以调用程序包中的游标
   exec pack_emp.pro_cur;

   示例10：存储过程返回游标的子程序包(此程序包返回r_cur游标)
   CREATE OR REPLACE package SCOTT.pk_wt
   is
    type mytype is ref cursor;
    procedure p_wt(mycs out mytype);
   end;

   CREATE OR REPLACE package BODY SCOTT.pk_wt
   is
    procedure p_wt(mycs out mytype)
    is
    r_cur mytype;
   begin
     open r_cur for select * from emp;
     mycs:=r_cur;
     end p_wt;
   end pk_wt;

  查询有关过程、函数和程序包的信息：USER_OBJECTS数据字典视图
  column object_name format a18
  select object_name,object_type from user_objects where object_type in ('PROCEDURE','FUNCTION','PACKAGE','PACKAGE BODY');