PL/SQL编程整理7 -- 包

包是一组相关过程、函数、变量、常量和游标等PL/SQL程序设计元素的组合，它具有面向对象程序设计语言的特点，是对这些PL/SQL 程序设计元素的封装。包类似于C++和JAVA语言中的类，其中变量相当于类中的成员变量，过程和函数相当于类方法。把相关的模块归类成为包，可使开发人员利用面向对象的方法进行存储过程的开发，从而提高系统性能。

    与类相同，包中的程序元素也分为公用元素和私用元素两种，这两种元素的区别是他们允许访问的程序范围不同，即它们的作用域不同。公用元素不仅可以被包中的函数、过程所调用，也可以被包外的PL/SQL程序访问，而私有元素只能被包内的函数和过程序所访问。

    在PL/SQL程序设计中，使用包不仅可以使程序设计模块化，对外隐藏包内所使用的信息（通过使用私用变量），而写可以提高程序的执行效率。因为，当程序首次调用包内函数或过程时，ORACLE将整个包调入内存，当再次访问包内元素时，ORACLE直接从内存中读取，而不需要进行磁盘I/O操作，从而使程序执行效率得到提高。

    一个包由两个分开的部分组成：

    包定义（PACKAGE）：包定义部分声明包内数据类型、变量、常量、游标、子程序和异常错误处理等元素，这些元素为包的公有元素。

    包主体（PACKAGE BODY）：包主体则是包定义部分的具体实现，它定义了包定义部分所声明的游标和子程序，在包主体中还可以声明包的私有元素。

    包定义和包主体分开编译，并作为两部分分开的对象存放在数据库字典中，详见数据字典user_source, all_source, dba_source.

 

1.1.1   包的创建

包定义的语法如下：

创建包定义:

CREATE [OR REPLACE] PACKAGE package_name [AUTHID {CURRENT_USER | DEFINER}] {IS | AS} [公有数据类型定义[公有数据类型定义]…] [公有游标声明[公有游标声明]…] [公有变量、常量声明[公有变量、常量声明]…] [公有子程序声明[公有子程序声明]…] END [package_name];

其中：AUTHID CURRENT_USER和AUTHID DEFINER选项说明应用程序在调用函数时所使用的权限模式，它们与CREATE FUNCTION语句中invoker_right_clause子句的作用相同。

 

创建包主体:

CREATE [OR REPLACE] PACKAGE BODY package_name

{IS | AS}

[私有数据类型定义[私有数据类型定义]…]

[私有变量、常量声明[私有变量、常量声明]…]

[私有子程序声明和定义[私有子程序声明和定义]…]

[公有游标定义[公有游标定义]…]

[公有子程序定义[公有子程序定义]…]

BEGIN

PL/SQL 语句

END [package_name];

其中：在包主体定义公有程序时，它们必须与包定义中所声明子程序的格式完全一致。

 

例1:创建的包为demo_pack, 该包中包含一个记录变量DeptRec、两个函数和一个过程。

CREATE OR REPLACE PACKAGE demo_pack IS     DeptRec dept%ROWTYPE;     V_sqlcode NUMBER;     V_sqlerr VARCHAR2(2048);     FUNCTION add_dept( dept_no NUMBER, dept_name VARCHAR2, location VARCHAR2)     RETURN NUMBER;     FUNCTION remove_dept(dept_no NUMBER)         RETURN NUMBER;     PROCEDURE query_dept(dept_no IN NUMBER); END demo_pack;

 

包主体的创建方法，它实现上面所声明的包定义，并在包主体中声明一个私有变量flag和一个私有函数check_dept，由于在add_dept和remove_dept等函数中需要调用check_dpet函数，所以，在定义check_dept 函数之前首先对该函数进行声明，这种声明方法称作前向声明。

CREATE OR REPLACE PACKAGE BODY demo_pack     IS     Flag INTEGER; FUNCTION check_dept(dept_no NUMBER)         RETURN INTEGER;   FUNCTION add_dept(dept_no NUMBER, dept_name VARCHAR2, location VARCHAR2)     RETURN NUMBER     IS BEGIN     IF check_dept(dept_no)=0 THEN         INSERT INTO dept VALUES(dept_no, dept_name, location);         RETURN 1;     ELSE         RETURN 0;     END IF; EXCEPTION     WHEN OTHERS THEN         V_sqlcode := SQLCODE;         V_sqlerr := SQLERRM;         RETURN -1; END add_dept;   FUNCTION remove_dept(dept_no NUMBER)     RETURN NUMBER     IS BEGIN     V_sqlcode := 0;     V_sqlerr := NULL;     IF check_dept(dept_no) = 1 THEN         DELETE FROM dept WHERE deptno=dept_no;         RETURN 1;     ELSE         RETURN 0;     END IF; EXCEPTION     WHEN OTHERS THEN         V_sqlcode := SQLCODE;         V_sqlerr := SQLERRM;         RETURN -1; END remove_dept;   PROCEDURE query_dept(dept_no IN NUMBER)     IS BEGIN     IF check_dept(dept_no) =1 THEN         SELECT * INTO DeptRec FROM dept WHERE deptno=dept_no;     END IF; END query_dept;   FUNCTION check_dept(dept_no NUMBER)     RETURN INTEGER     IS BEGIN     SELECT COUNT(*) INTO flag FROM dept WHERE deptno=dept_no;     IF flag > 0 THEN         Flag := 1;     END IF;     RETURN flag; END check_dept;   BEGIN     V_sqlcode := NULL;     V_sqlerr := NULL; END demo_pack;

 

对包内共有元素的调用格式为：包名.元素名称

调用demo_pack包内函数对dept表进行插入、查询和修改操作，并通过demo_pack包中的记录变量DeptRec 显示所查询到的数据库信息：

DECLARE     Var NUMBER; BEGIN     Var := demo_pack.add_dept(90,’Administration’, ‘Beijing’);     IF var =-1 THEN         DBMS_OUTPUT.PUT_LINE(demo_pack.v_sqlerr);     ELSIF var =0 THEN         DBMS_OUTPUT.PUT_LINE(‘该部门记录已经存在！’); ELSE         DBMS_OUTPUT.PUT_LINE(‘添加记录成功！’);         Demo_pack.query_dept(90);         DBMS_OUTPUT.PUT_LINE(demo_pack.DeptRec.deptno||’---‘|| demo_pack.DeptRec.dname||’---‘||demo_pack.DeptRec.loc);        var := demo_pack.remove_dept(90);         IF var =-1 THEN             DBMS_OUTPUT.PUT_LINE(demo_pack.v_sqlerr);         ELSE             DBMS_OUTPUT.PUT_LINE(‘删除记录成功！’);         END IF;     END IF; END;

 

例2: 创建包emp_package

CREATE OR REPLACE PACKAGE emp_package     IS     TYPE emp_table_type IS TABLE OF emp%ROWTYPE INDEX BY BINARY_INTEGER;     PROCEDURE read_emp_table (p_emp_table OUT emp_table_type); END emp_package;   CREATE OR REPLACE PACKAGE BODY emp_package     IS     PROCEDURE read_emp_table (p_emp_table OUT emp_table_type)     IS     I BINARY_INTEGER := 0;     BEGIN         FOR emp_record IN ( SELECT * FROM emp ) LOOP             P_emp_table(i) := emp_record;             I := I + 1;         END LOOP;     END read_emp_table; END emp_package;   DECLARE     E_table emp_package.emp_table_type; BEGIN     Emp_package.read_emp_table(e_table);     FOR I IN e_table.FIRST .. e_table.LAST LOOP         DBMS_OUTPUT.PUT_LINE(e_table(i).empno||’  ‘||e_table(i).ename);     END LOOP; END;

 

例3:

Create or replace package p_test is Procedure update_sal(name varchar2,newSal number); Function annual_inconme(name varchar2) return nuber; End; /

注意：包规范只包含了过程和函数的说明，但是没有过程和函数的实现代码。

包体用于实现包规范中的过程和函数。

实例：

Create or replace package body p_test is Procedure update_sal(name varchar2,newSal number) Is Begin     Update emp set sal=newsal where ename=name; End; Function annual_inconme(name varchar2) return nuber is Annual_salary number; Begin      Select sal*12 + nvl(comm.,o)*12 into Annual_salary from emp where ename=spName;      Return Annual_salary; End; End; /

 

调用包的过程或是函数

Exec p_test .update_sal(‘SCOTT’,120);