PL/SQL之包的创建和应用

PL/SQL之包的创建和应用

一 引言

包是一组相关**过程、函数、变量、常量和游标**等 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) 创建包定义

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

（2）创建包主体
语法

CREATE [OR REPLACE ] PACKAGE BODY package_name
	{
   IS | AS}
	[私有数据类型定义 私 有数据类型定义]
	[私有变量、常量声明 私有变量、常量声明]
	[私有子程序声明和定义 私有子程序声明和定义]
	[公有游标定义 公有游标定义]
	[公有子程序定义 公有子程序定义]
BEGIN
	PL/SQL语句
END [package_name]
注意：其中：在包主体定义公有程序时，它们必须与**包定义中所声明子程序的格式完全一致** 。

三 包的开发步骤

与开发存储过程类似，包的开发需要几个步骤
1． 将每个存储过程调式正确；
2． 用文本编辑软件将各个存储过程和函数集成在一起；
3． 按照包的定义 要求将集成的文本的前面加上包定义；
4． 按照包的定义要求将集成的文本的前面加上包主体；
5． 使用 SQLPLUS 或开发工具进行调式。

四 案例

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

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;

包主体的创建方法，它实现上面所声明的包定义

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 是违反唯一约束条件的错误代码*/
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_ 的部门')
	WHEN TOO_MANY_ROWS THEN
		DBMS_OUTPUT.PUT_LINE(