ORACLE程序包

程序包

 

包主体/规范名字一样

包主体/规范中的对应参数必须类型及名字一样

只能使用强类型的REF游标

 

创建程序包规范 create or replace package my_pack is        procedure find_emp_proc(eno emp.empno%type);        function find_emp_fun(eno emp.empno%type) return emp.ename%type; end my_pack;   SQL> ed SQL> /   Package created   调用函数 SQL> select my_pack.find_emp_fun(7788) from dual;   select my_pack.find_emp_fun(7788) from dual   ORA-04068: 已丢弃程序包  的当前状态 ORA-04067: 未执行，package body "SCOTT.MY_PACK" 不存在 ORA-06508: PL/SQL: 无法在调用之前找到程序单元 ORA-06512: 在line 1   执行存储过程 SQL> exec my_pack.find_emp_proc(7788);   begin my_pack.find_emp_proc(7788); end;   ORA-04068: 已丢弃程序包  的当前状态 ORA-04067: 未执行，package body "SCOTT.MY_PACK" 不存在 ORA-06508: PL/SQL: 无法在调用之前找到程序单元 ORA-06512: 在line 2   创建程序包主体 create or replace package body my_pack is        empname emp.ename%type;        procedure find_emp_proc(eno emp.empno%type)        is        begin                  select ename into empname from emp where empno=eno;                  dbms_output.put_line(empname);        end;        function find_emp_fun(eno emp.empno%type) return emp.ename%type        is        begin                 select ename into empname from emp where empno=eno;                 return empname;        end; end my_pack;   SQL> ed SQL> /   Package body created   调用函数 SQL> select my_pack.find_emp_fun(7788) from dual;   MY_PACK.FIND_EMP_FUN(7788) -------------------------------------------------------------------------------- SCOTT 执行存储过程 SQL> exec my_pack.find_emp_proc(7839);   KING   PL/SQL procedure successfully completed

 

 

 

 

 

 

 

创建程序包规范 create or replace package emp_pack is        procedure find_emp_proc(eno emp.empno%type);        cursor emp_cur(eno emp.empno%type) return emp%rowtype; end emp_pack;   SQL> ed SQL> /   Package created   创建程序包主体 create or replace package body emp_pack is        --行类型变量        empR emp%rowtype;        --声明强类型REF游标        cursor emp_cur(eno emp.empno%type) return emp%rowtype        is        select * from emp where empno=eno;        --过程        procedure find_emp_proc(eno emp.empno%type)        is        begin                  open emp_cur(eno);                  loop                       fetch emp_cur into empR;                       exit when emp_cur%notfound;                       dbms_output.put_line(empR.ename);                  end loop;                  close emp_cur;        end; end emp_pack;   SQL> ed SQL> /   Package body created   执行存储过程 SQL> exec emp_pack.find_emp_proc(7839);   KING   PL/SQL procedure successfully completed

 

创建程序包规范 create or replace package emp_pack is        procedure find_emp_proc;        cursor emp_cur return emp%rowtype; end emp_pack;   创建程序包主体 create or replace package body emp_pack is        --行类型变量        empR emp%rowtype;        --声明强类型REF游标        cursor emp_cur return emp%rowtype        is        select * from emp;        --过程        procedure find_emp_proc        is        begin                  open emp_cur;                  loop                       fetch emp_cur into empR;                       exit when emp_cur%notfound;                       dbms_output.put_line(empR.ename);                  end loop;                  close emp_cur;        end; end emp_pack;   执行存储过程 SQL> exec emp_pack.find_emp_proc;   SMITH ALLEN WARD JONES MARTIN BLAKE CLARK SCOTT KING TURNER ADAMS JAMES FORD MILLER   PL/SQL procedure successfully completed

 

查询包   SQL> select OBJECT_NAME from user_objects where object_type='PACKAGE';   OBJECT_NAME -------------------------------------------------------------------------------- EMP_PACK MY_PACK   查询程序包代码 SQL> select line,text from user_source where name='my_pack';         LINE TEXT ---------- --------------------------------------------------------------------------------   SQL> select line,text from user_source where name='MY_PACK';         LINE TEXT ---------- --------------------------------------------------------------------------------          1 package my_pack          2 is          3        procedure find_emp_proc(eno emp.empno%type);          4        function find_emp_fun(eno emp.empno%type) return emp.ename%type;          5 end my_pack;          1 package body my_pack          2 is          3        empname emp.ename%type;          4        procedure find_emp_proc(eno emp.empno%type)          5        is          6        begin          7                  select ename into empname from emp where empno=eno;          8                  dbms_output.put_line(empname);          9        end;         10        function find_emp_fun(eno emp.empno%type) return emp.ename%type         11        is         12        begin         13                 select ename into empname from emp where empno=eno;         14                 return empname;         15        end;         LINE TEXT ---------- --------------------------------------------------------------------------------         16 end my_pack;   21 rows selected   SQL> select line,text from user_source where name='EMP_PACK';         LINE TEXT ---------- --------------------------------------------------------------------------------          1 package emp_pack          2 is          3        procedure find_emp_proc(eno emp.empno%type);          4        cursor emp_cur(eno emp.empno%type) return emp%rowtype;          5 end emp_pack;          1 package body emp_pack          2 is          3        --行类型变量          4        empR emp%rowtype;          5        --声明强类型REF游标          6        cursor emp_cur(eno emp.empno%type) return emp%rowtype          7        is          8        select * from emp where empno=eno;          9        --过程         10        procedure find_emp_proc(eno emp.empno%type)         11        is         12        begin         13                  open emp_cur(eno);         14                  loop         15                       fetch emp_cur into empR;         LINE TEXT ---------- --------------------------------------------------------------------------------         16                       exit when emp_cur%notfound;         17                       dbms_output.put_line(empR.ename);         18                  end loop;         19                  close emp_cur;         20        end;         21 end emp_pack;   26 rows selected