--============================
-- PL/SQL --> 包的创建与管理
--============================
包，是一个逻辑集合，是由 PL / SQL 类型以及 PL / SQL 子程序的集合。 PL / SQL 类型包括 table 类型， record 类型。 PL / SQL 项则包括游标，游标
变量。 PL / SQL 子程序包括过程，函数等。可以说包可谓是包罗万象。是所有 PL / SQL 相关资源的汇总。
包的使用可以简化应用程序设计，实现信息掩藏，子程序重载等功能。
包的优点
1. 模块化：将函数，子程序全部融合在一起，使得成为一个有机的整体，封装了相关的结构。
2. 易于维护：整合了子程序，更易于维护 。
3. 简化应用程序设计：包的声明与包体内容相分离。
4. 隐藏信息：私有对象不可访问，所有的包体内代码可以实现隐藏。
5. 节省 I / O ：一次编译，多次使用。
一、包的组成与创建语法
包头：用于定义包的公共组件，如函数头，过程头，游标等以及常量，变量等。包头中定义的公共组件可以在包内引用，也可以被其
它子程序引用。
包体：用于定义包头中定义过的过程和函数。可以单独定义私有组件，包括变量，常量，过程和函数等。私有组件只能在包内使用，而
不能被其它子程序所调用。
一言以蔽之，包头定义包的声明及描述部分，而包体则定义了对应包的具体执行部分。
创建包的语法：
CREATE [OR REPLACE] PACKAGE package_name -- 定义包头
{ AS|IS}
public_variable_declarations |
public_type_declarations |
public_exception_declarations |
public_cursor_declarations |
function_declarations |
procedure_specifications
END [package_name]
CREATE [OR REPLACE] PACKAGE BODY package_name -- 定义包体，包体中的 package_name 应当与包头中的 package_name 相同
{ AS|IS}
private_variable_declarations |
private_type_declarations |
private_exception_declarations |
private_cursor_declarations |
function_declarations |
procedure_specifications
END [package_name]
二、创建包
下面演示包的创建，基于用户 scott 创建，存储过程，函数等依赖于其下的对象
1. 创建包头
CREATE OR REPLACE PACKAGE emp_package IS -- 创建包头，包的名字为 emp_package
g_deptno NUMBER ( 3 ) := 30 ; -- 定义一个公共变量 g_deptno
PROCEDURE add_employee ( eno NUMBER , name VARCHAR2 , salary NUMBER , dno NUMBER DEFAULT g_deptno ); -- 声明过程
PROCEDURE fire_employee ( eno NUMBER ); -- 声明过程
FUNCTION get_sal ( eno NUMBER ) RETURN NUMBER ; -- 声明函数
END emp_package ;
/
2. 创建包体
CREATE OR REPLACE PACKAGE BODY emp_package IS -- 创建包体，注意，包体中包的名字必须与包头的名字相一致
FUNCTION validate_deptno ( v_deptno NUMBER ) RETURN BOOLEAN -- 创建一个私有函数 , 注 , 此私有函数不能该包外子程序调用
IS
v_temp INT ;
BEGIN
SELECT 1 INTO v_temp FROM dept WHERE deptno = v_deptno ;
RETURN TRUE ;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN FALSE ;
END ;
PROCEDURE add_employee -- 创建添加雇员的过程
( eno NUMBER , name VARCHAR2 , salary NUMBER , dno NUMBER DEFAULT g_deptno ) IS
BEGIN
IF validate_deptno ( dno ) THEN -- 该过程调用了包内的一个函数 validate_deptno 来验证 dno 的有效性
INSERT INTO emp ( empno , ename , sal , deptno ) VALUES ( eno , name , salary , dno );
ELSE
RAISE_APPLICATION_ERROR (- 20000 , ' 不存在该部门 ' );
END IF ;
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
RAISE_APPLICATION_ERROR (- 20011 , ' 该雇员已存在 ' );
END ;
PROCEDURE fire_employee ( eno NUMBER ) IS -- 创建解除雇员的过程
BEGIN
DELETE FROM emp WHERE empno = eno ;
IF SQL % NOTFOUND THEN
RAISE_APPLICATION_ERROR (- 20012 , ' 该雇员不存在 ' );
END IF ;
END ;
FUNCTION get_sal ( eno NUMBER ) RETURN NUMBER IS -- 创建函数 get_sal 返回雇员的薪水
v_sal emp . sal % TYPE ;
BEGIN
SELECT sal INTO v_sal FROM emp WHERE empno = eno ;
RETURN v_sal ;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR (- 20012 , ' 该雇员不存在 ' );
END ;
END emp_package ;
/
3. 创建仅包含包头的包 ( 仅包含包头的包也可以被调用，具体参照后面的包的调用 )
CREATE OR REPLACE PACKAGE global_int
IS
g_positive CONSTANT NUMBER := 10 ;
g_negative CONSTANT NUMBER :=- 10 ;
END global_int ;
三、包的调用
对于包的私有对象只能在包内调用。如上面的例子中对包内私有函数 validate_deptno 进行了直接调用
对于包的公共对象，既可以在包内调用，也可以由其他应用程序调用。使用其他应用程序调用时的方法：包名 . 包对象
1. 调用包的公共变量
scott@ORCL > exec emp_package . g_deptno := 10 ;
2. 调用包的公共过程
scott@ORCL > exec emp_package . add_employee ( 2222 , 'Robinson' , 3000 ); -- 此调用未指定部门号，则使用缺省值 , 但前面执行了
--exec emp_package.g_deptno:=10; 故部门号变为
scott@ORCL > exec emp_package . add_employee ( 3333 , 'Jackson' , 4000 , 20 );
scott@ORCL > select * from emp where empno in( 2222 , 3333 );
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
2222 Robinson 3000 10
3333 Jackson 4000 20
3. 调用包的公共函数
scott@ORCL > var sal number
scott@ORCL > exec : sal := emp_package . get_sal ( 7788 );
PL / SQL procedure successfully completed .
scott@ORCL > print sal
SAL
----------
310
4. 以不同用户身份调用包 . 需要使用 schema 名字来调用，即：用户名 . 包名 . 包对象名
scott@ORCL > conn lion / lion -- 注意帐户需要具有执行所调用包的权限
lion@ORCL > exec scott . emp_package . fire_employee ( 2222 );
5. 调用远程数据库包的公共对象。调用方法 : 包名 . 包对象名 @ 数据库链接名
sys@ASMDB > create database link orcl
2 connect to lion identified by lion
3 using 'orcl' ;
Database link created .
sys@ASMDB > exec scott . emp_package . add_employee@orcl ( 4444 , 'Richard' , 4000 );
BEGIN scott . emp_package . add_employee@orcl ( 4444 , 'Richard' , 4000 ); END ;
*
ERROR at line 1 :
ORA - 06550 : line 1 , column 7 : -- 注意远程调用时，对于缺省的参数不适用，需要明确指定参数
PLS - 00424 : RPC defaults cannot include Package State
ORA - 06550 : line 1 , column 7 :
PL / SQL : Statement ignored
sys@ASMDB > exec scott . emp_package . add_employee@orcl ( 4444 , 'Richard' , 4000 , 20 ); -- 下面的调用被成功执行
PL / SQL procedure successfully completed .
6. 无包体包的调用 ( 使用前面创建的包 global_int )
scott@ORCL > BEGIN
2 DBMS_OUTPUT . PUT_LINE ( 'Result is : ' || 2 * global_int . g_positive ); -- 使用包 DBMS_OUTPUT 来调用
3 END ;
4 /
Result is : 20
PL / SQL procedure successfully completed .
scott@ORCL > CREATE OR REPLACE FUNCTION f_negative ( m number ) -- 将包嵌入到函数之中
2 RETURN NUMBER
3 IS
4 BEGIN
5 RETURN ( m * global_int . g_negative );
6 END f_negative ;
7 /
Function created .
scott@ORCL > EXEC DBMS_OUTPUT . PUT_LINE ( f_negative ( 2 ));
- 20
PL / SQL procedure successfully completed .
四、包的管理
1. 查看包
scott@ORCL > select line , text from user_source -- 查看包头
2 where name = 'EMP_PACKAGE' and type = 'PACKAGE' ;
LINE TEXT
---------- ------------------------------------------------------------
1 PACKAGE emp_package IS
2 g_deptno NUMBER ( 3 ) := 30 ;
3 PROCEDURE add_employee ( eno NUMBER , name VARCHAR2 , salary N
UMBER , dno NUMBER DEFAULT g_deptno );
4 PROCEDURE fire_employee ( eno NUMBER );
5 FUNCTION get_sal ( eno NUMBER ) RETURN NUMBER ;
6 END emp_package ;
scott@ORCL > select line , text from user_source -- 查看包体
2 where name = 'EMP_PACKAGE' and type = 'PACKAGE BODY' ;
LINE TEXT
---------- --------------------------------------------------------------------------------
1 PACKAGE BODY emp_package IS
2 FUNCTION validate_deptno ( v_deptno NUMBER ) RETURN BOOLEAN IS
3 v_temp INT ;
4 BEGIN
5 SELECT 1 INTO v_temp FROM dept WHERE deptno = v_deptno ;
6 RETURN TRUE ;
......................
2. 查看包的参数
scott@ORCL > desc emp_package ;
PROCEDURE ADD_EMPLOYEE
Argument Name Type In/ Out Default ?
------------------------------ ----------------------- ------ --------
ENO NUMBER IN
NAME VARCHAR2 IN
SALARY NUMBER IN
DNO NUMBER IN DEFAULT
PROCEDURE FIRE_EMPLOYEE
Argument Name Type In/ Out Default ?
------------------------------ ----------------------- ------ --------
ENO NUMBER IN
FUNCTION GET_SAL RETURNS NUMBER
Argument Name Type In/ Out Default ?
------------------------------ ----------------------- ------ --------
ENO NUMBER IN
3. 包的删除
DROP PACKAGE package_name -- 同时删除包体和包头
scott@ORCL > DROP PACKAGE global_int ;
删除包体，保留包头
DROP PACKAGE BODY package_name -- 删除包体
scott@ORCL > drop package body emp_package ;
Package body dropped .
4.包的编译
重新编译包规范和包体：alter package...compile
重新编译包规范：alter package...compile specification
重新编译包体：alter package...compile body
五、总结
创键包体之前应该先创建包头
包头应当仅仅包含那些希望作为公共对象的部分
包头的声明应包含尽可能少的结构信息
任意包头的变更，需要重新编译该包内的子程序
在包头内定义的任意公共对象可以被任意内部或外部子程序调用
包体内的私有对象仅仅能被该包体内的子程序调用
六、更多参考
有关 SQL 请参考
SQL 基础--> ROLLUP 与CUBE 运算符实现数据汇总
SQL 基础--> 层次化查询(START BY ... CONNECT BY PRIOR)
有关 PL/SQL 请参考
