PL/SQL 包的概念及创建使用

包
1：概念
包是一组过程、函数、变量、常量和游标等元素的组合。是对这些PL/SQL 程序设计元素的封装。其中变量相当于类中的成员变量，过程和函数相当于类方法。把相关的模块归类成包，可使开发人员利用面向对象的方法进行存储过程的开发。
2：包的组成部分：
（1）包定义（PACKAGE）：包定义部分声明包内数据类型、变量、常量、游标、子程序和异常错误处理等元素，这些元素为包的公有元素。
（2）包主体（PACKAGE BODY）：包主体则是包定义部分的具体实现，它定义了包定义部分所声明的游标和子程序，在包主体中还可以声明包的私有元素。
包定义和包主体分开编译，并作为两部分分开的对象存放在数据库字典中。
3：包的定义（PACKAGE）：
包定义的语法：
CREATE [OR REPLACE] PACKAGE package_name
{IS | AS}
[公有数据类型定义[公有数据类型定义]…]
[公有游标声明[公有游标声明]…]
[公有变量、常量声明[公有变量、常量声明]…]
[公有子程序声明[公有子程序声明]…]END [package_name];
4：包体定义（PACKAGE BODY）：
包体定义语法：
CREATE [OR REPLACE] PACKAGE BODY package_name{IS | AS}
[私有数据类型定义[私有数据类型定义]…]
[私有变量、常量声明[私有变量、常量声明]…]
[私有子程序声明和定义[私有子程序声明和定义]…]
[公有子程序定义[公有子程序定义]…]
BEGIN
PL/SQL 语句END [package_name];
其中：在包主体定义公有程序时，它们必须与包定义中所声明子程序的格式完全一致

5：创建包举例
例：--1：包定义
CREATE OR REPLACE PACKAGE demo_pack
IS
DeptRec dept%ROWTYPE;
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;
--2：定义包体
CREATE OR REPLACE PACKAGE BODY demo_pack
IS
FUNCTION add_dept (dept_no NUMBER, dept_name VARCHAR2, location VARCHAR2)
RETURN NUMBER
IS
empno_remaining EXCEPTION;
PRAGMA EXCEPTION_INIT(empno_remaining, -1); --/* -1 是违反唯一约束条件的错误代码 */
--3：程序执行部分
BEGIN
INSERT INTO dept VALUES(dept_no, dept_name, location);
IF SQL%FOUND THEN
RETURN 1;
END IF;
EXCEPTION
WHEN empno_remaining THEN
RETURN 0;
WHEN OTHERS THEN
RETURN -1;
END add_dept;
FUNCTION remove_dept(dept_no NUMBER)
RETURN NUMBER IS
BEGIN
DELETE FROM dept WHERE deptno=dept_no;
IF SQL%FOUND THEN
RETURN 1;
ELSE
RETURN 0;
END IF;
EXCEPTION
WHEN OTHERS THEN RETURN -1;
END remove_dept;

PROCEDURE query_dept (dept_no IN NUMBER)
IS
BEGIN
SELECT * INTO DeptRec FROM dept WHERE deptno=dept_no;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('数据库中没有编码为'||dept_no||'的部门');
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('程序运行错误!请使用游标');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE||'----'||SQLERRM);
END query_dept;END demo_pack;

--4：调用包应用举例
DECLARE
Var NUMBER;BEGIN
Var := demo_pack.add_dept(90,'Administration', 'Beijing');
IF var =-1 THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE||'----'||SQLERRM);
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(SQLCODE||'----'||SQLERRM);
ELSIF var=0 THEN
DBMS_OUTPUT.PUT_LINE('该部门记录不存在！');
ELSE
DBMS_OUTPUT.PUT_LINE('删除记录成功！');
END IF;
END IF;
END;

6：子程序重载
子程序重载是指：在包内有两个或多个子程序同名，不同参，包括参数变量，参数顺序，数据类型不同。
例：
FUNCTION query_dept(dept_no IN NUMBER)
RETURN INTEGER
IS
BEGIN
IF dept_no =10 THEN
SELECT * INTO DeptRec FROM dept WHERE deptno=dept_no;
RETURN 1;
ELSE
RETURN 0;
END IF;
END query_dept;

--重载
FUNCTION query_dept(dept_no IN VARCHAR2)
RETURN INTEGER
IS
BEGIN
IF dept_no =10 THEN
SELECT * INTO DeptRec FROM dept WHERE deptno=dept_no;
RETURN 1;
ELSE
RETURN 0;
END IF;
END query_dept;
END demo_pack1;