1.程序包
程序包是对相关过程、函数、变量、游标和异常等对象的封装,由规范(包头)和主体(包体)两部分组成。规范中可以声明程序包中公共对象,包括类型、变量、常量、异常、游标规范和子程序规范等。主体中可以声明程序包私有对象和实现在包规范中声明的子程序和游标。
--程序包规范语法 CREATE [OR REPLACE] PACKAGE 名字 IS|AS [Public item declarations] [Subprogram specification] END [package_name]; package_name:包的名称 Public item declarations:公共声明部分 Subprogram specification:声明PL/SQL子程序 --程序包主体 CREATE [OR REPLACE] PACKAGE BODY package_name IS|AS [Private item declarations] [Subprogram bodies] [BEGIN Initialization] END [package_name]; package_name是包的名称 public type and item declarations::私有声明部分 subprogram specificatons:子程序体 --定义包头 create or replace package pack1 is aa number := 9; procedure insert_dept(v_dept in dept%rowtype); function fun(v1 number,v2 number) return number; end; --包体实现 create or replace package body pack1 is bb number := 10; procedure insert_dept(v_dept in dept%rowtype) is begin insert into dept values(v_dept.deptno,v_dept.dname,v_dept.loc); end; function fun(v1 number,v2 number) return number is begin return v1 + v2; end; end;
程序包中游标的定义分为游标规范和游标主体两部分,在包规范中声明游标规范时必须使用 RETURN 子句指定游标的返回类型 return 数据类型;RETURN子句指定的数据类型可以是:%ROWTYPE 属性引用表定义的记录类型,程序员定义的记录类型,例如 TYPE EMPRECTYP IS RECORD(emp_id INTEGER,salary REAL) 来定义的,不可以是number, varchar2, %TYPE等类型。
--显示游标 create or replace package pack2 is cursor mycursor return emp%rowtype; procedure mycursor_use; end; create or replace package body pack2 is cursor mycursor return emp%rowtype is select * from emp procedure mycursor_use is begin for i in mycursor loop dbms_output.put_line(i.ename) end loop; end; end; --REF游标 create or replace package pack3 is type refcur is ref cursor; procedure mycursor_use; end; create or replace package body pack3 is procedure mycursor_use is mycursor refcur; v_emp emp%rowtype; begin open mycursor for select * from emp; fetch mycursor into v_emp; while mycursor%found loop dbms_output.put_line(v_emp.ename); fetch mycursor into v_emp; end loop; close mycursor; end; end;