3.6.3　程序包

程序包是一种Oracle数据库对象，它是一组逻辑上相关的数据类型、变量、过程、函数和游标等的集合。程序包被创建后，存储在数据库中，用户可以直接使用包中的数据类型和变量，也可以直接调用包中的过程和函数。

程序包有两种形式，一种是用户根据需要创建的程序包，一种是系统预定义的程序包。这里介绍自定义程序包的创建、使用、删除等操作，以及预定义程序包的使用方法。

用户可以根据需要创建自己的程序包。在程序包中可以定义数据类型、变量、过程、函数、异常和游标等元素，这些元素具有全局的特性，可以在程序包中使用，也可以在程序包之外使用。

一个程序包由两部分组成：程序包的头部和包体。其中头部用来定义类型、变量、异常、声明游标、过程和函数，它的作用相当于程序包的接口。在包体中可以利用头部的类型定义变量，定义过程、游标和函数的代码。

在创建程序包时，头部和包体是分别创建的，并且头部必须在包体之前创建。程序包创建之后，如果要对其功能进行修改，这时只需修改包体的代码即可，不用修改头部，仅当需要改变参数类型、参数个数等信息时，才需要修改程序包的头部。

创建程序包头部的命令是CREATE PACKAGE，这条命令的语法格式为：

CREATE[OR REPLACE]PACKAGE包名

AUTHID CURRENT_USER|DEFINER

AS

类型的定义；

变量的定义；

子程序的声明；

游标的声明；

异常的声明；

END；

其中OR REPLACE选项的作用是当指定的包已经存在时重新创建它。AUTHID选项用来规定程序包以哪个用户的身份执行。这个选项有两个可选值，即CURRENT_USER和DEFINER，二者只能选择其中一个。

子程序的声明就是定义过程和函数的原型，即子程序的名称、参数和返回值，不包含它的代码部分。类型定义部分允许用户根据需要创建自己的数据类型。

例如，要对部门员工的总收入和所得税进行统计，为此需要编写一个程序包。在程序包中首先定义了一个记录类型total，然后声明了一个函数tax_per_depart，用来统计某个部门的所得税，过程total_per_depart用来统计各个部门的员工总收入。最后还定义了一个游标c1。需要注意的是，在程序包的头部定义游标时需要指定它的返回类型。以下是创建程序包employee头部的代码：

CREATE OR REPLACE PACKAGE employee

AS

type total is record（

dno emp.deptno%type，

total_income number

）；

function tax_per_depart（dno integer）RETURN number；

procedure total_per_depart；

cursor c1 RETURN total；

END；

程序包的包体是对头部的实现，主要用来定义过程和函数的可执行代码。创建包体的命令是CREATE PACKAGE BODY，这条命令的语法格式为：

CREATE[OR REPLACE]PACKAGE BODY包名AS

游标的实现；

子程序的实现；

END；

其中包名与创建头部时使用的名字完全相同。游标的实现是指定游标中所使用的SELECT语句。子程序的实现是写出过程和函数的代码，过程和函数的编写方法与以前介绍的方法完全相同。以下是创建程序包employee的包体的代码。

CREATE OR REPLACE PACKAGE BODY employee

AS

CURSOR c1 RETURN total is—定义游标

SELECT deptno, sum（sal）FROM emp GROUP BY deptno；

function tax_per_depart（dno integer）—定义函数tax_per_depart

RETURN number

as

result number；

BEGIN

SELECT sum（sal）*0.03 INTO result FROM emp

WHERE deptno=dno

GROUP BY deptno；

RETURN result；

END；—函数tax_per_depart结束

procedure total_per_depart—定义过程total_per_depart

as

depart total；

BEGIN

open c1；

fetch c1 INTO depart；—利用取出游标中的数据

while c1%found loop

dbms_output.put_line（'部门'||depart.dno||'总收入：'||depart.total_income）；

fetch c1 INTO depart；

END loop；

close c1；

END；—过程total_per_depart结束

END；—包体结束

定义了程序包employee后，用户就可以在PL/SQL块或者SQL*Plus中使用这个包中的类型、游标、变量、过程和函数了，使用的方法为：

包名.元素名

例如，要利用程序包employee中的过程total_per_depart统计各个部门员工的总收入，可以在SQL*Plus中调用这个过程：

SQL＞EXEC employee.total_per_depart

部门10 总收入：8750

部门20 总收入：6775

部门30 总收入：9400

再比如，在一个匿名块中调用程序包employee中的函数tax_per_depart，计算部门20的所得税，这个匿名块的代码为：

DECLARE

dno integer；

total_tax number；

t1 employee.total—利用程序包employee中的类型total定义一个变量，

BEGIN

t1.dno：=10；

t1.total_income：=1000；

dno：=20；

total_tax：=employee.tax_per_depart（dno）；

dbms_output.put_line（'Total tax of department'||dno||'is：'||total_tax）；

END；

如果一个程序包不再需要，我们可以将其从数据库中删除。删除程序包时，可以选择只删除包体，或者删除整个包。删除整个程序包的命令是DROP PACKAGE，它的格式为：

DROP PACKAGE包的名字；

这样，程序包的头部和包体都将从数据库中被删除。

如果只删除包体，相应的命令为DROP PACKAGE BODY，它的格式为：

DROP PACKAGE BODY包的名字；