数据包

---

定义:
   定义包的规范
      CREATE [OR REPLACE] PACKAGE <数据包名> AS
               --公共类型和对象声明
               --子程序说明
      END;
    定义包的主体
      CREATE [OR REPLACE] PACKAGE BODY <数据包名> AS
               --公共类型和对象声明
               --子程序主体
      BEGIN
            -初始化语句
      END;


使用:
   示例:
创建数据包规范:
create or replace package pack_1 as
      n number;
      procedure p_1;
      FUNCTION f_1 RETURN number;
end;

创建数据包主体:
create or replace package body pack_1 as
  procedure p_1 is
       r emp%rowtype;
  begin
       select * into r from emp where empno=7788;
       dbms_output.put_line(r.empno||' '||r.ename||' '||r.sal);
   end;

   FUNCTION f_1 RETURN number is
       r emp%rowtype;
   begin
        select * into r from emp where empno=7788;
       return r.sal;
   end;
end;

使用包:
declare
      n number;
begin
      n:=&请输入员工号;
      pack_1.n:=n;
      pack_1.p_1;
      n:=pack_1.f_1;
      dbms_output.put_line('薪水为 '||n);
end;

在包中使用REF游标
示例:
创建数据包规范:
create or replace package pack_2 as
     TYPE c_type is REF CURSOR; --建立一个ref游标类型
     PROCEDURE p_1(c1 in out c_type); --过程的参数为ref游标类型;
end;

创建数据包主体:
create or replace package body pack_2 as
  PROCEDURE p_1(c1 in out c_type) is
  begin
       open c1 for select * from emp;
   end;
end;

使用包:
var c_1 refcursor;
set autoprint on;
execute pack_2.p_1(:c_1);



删除包:
    DROP PACKAGE <包名>;