oracle 11g 的程序包

1. 概念

程序包是对相关过程、函数、变量、游标和异常等对象的封装。程序包具有模块化、更轻松的应用程序设计、信息隐藏、新增功能和性能更佳等优点。程序包由规范（包头）和主体（包体）两部分组成，及其作用如下图所示：

需要说明的是，包头中声明的变量为公共变量，程序包可以进行访问。而包体中的为私有变量，只能在包体中进行访问。

2. 创建

2.1 创建语法

程序包包头创建语法如下：

CREATE [OR REPLACE] PACKAGE package_name -- 使用到了package
IS|AS
[Public item declarations] -- 公共对象声明
[Subprogram specification] -- 子程序规范
END [package_name];

程序包包体创建语法如下：

CREATE [OR REPLACE] PACKAGE BODY package_name -- 使用到了package 和 body
IS|AS
[Private item declarations] -- 私有对象声明
[Subprogram bodies] -- 子程序主体
[BEGIN Initialization]
END [package_name];

2.2 示例

假设现在有如下 student 表，具体内容如下所示：

SQL> select * from student;

                                    SNO SNAME                                         SAGE
--------------------------------------- ---------- ---------------------------------------
                                      5 lisi                                            -6
                                      1 Tom                                             31
                                      2 Kite                                            32
                                      3 Bob                                             33
                                      4 Mike                                            34

首先创建如下程序包包头：

create or replace package pack1
is
    aa int := -9; -- 公共变量
    procedure insert_student(a1 in student%rowtype); -- 声明一个存储过程
    procedure update_student(a2 in student%rowtype); -- 声明一个存储过程
end pack1;
/

接着创建对应的包体：

create or replace package body pack1 -- 增加一个 body 且名字相对应
is
    bb int := 5; -- 声明一个私有变量
    -- 定义第一个存储过程
    procedure insert_student(a1 in student%rowtype)
    is
    begin
    	insert into student(sno, sname, sage) values (a1.sno, a1.sname, a1.sage);
    	commit;
    	-- 验证一下私有变量
    	dbms_output.put_line(pack1.bb);
    end insert_student;
    -- 定义第二个存储过程
    procedure update_student(a2 in student%rowtype)
    is
    begin
    	update student set sname = a2.sname, sage = a2.sage where sno = a2.sno;
    	commit;
    end update_student;
end pack1;
/

我们来验证一下公有变量和私有变量得访问限制，查看下面输出结果，包体中的公共变量可以在程序包访问。而包体中的私有变量则不可以：

SQL> execute dbms_output.put_line(pack1.aa);

-9

------------------------------------------

SQL> execute dbms_output.put_line(pack1.bb);
begin dbms_output.put_line(pack1.bb); end;

ORA-06550: 第 1 行, 第 34 列: 
PLS-00302: 必须声明 'BB' 组件
ORA-06550: 第 1 行, 第 7 列: 
PL/SQL: Statement ignored

同样也可以调用程序包中定义的存储过程，具体如下所示：

declare
    stu student%rowtype;
begin
    stu.sno := 7;
    stu.sname := '张章';
    stu.sage := 42;
    -- 调用程序包
    pack1.insert_student(stu);
end;
/
-- 执行上述 PL/SQL 块之后，student 多了一条相应的记录
SQL> select * from student;

                                    SNO SNAME                                         SAGE
--------------------------------------- ---------- ---------------------------------------
                                      7 张章                                            42
                                      5 lisi                                            -6
                                      1 Tom                                             31
                                      2 Kite                                            32
                                      3 Bob                                             33
                                      4 Mike                                            34

declare
    stu student%rowtype;
begin
    stu.sno := 5;
    stu.sname := 'zhangsan';
    stu.sage := 12;
    -- 调用程序包
    pack1.update_student(stu);
end;
/
-- 同理如下
SQL> select * from student;

                                    SNO SNAME                                         SAGE
--------------------------------------- ---------- ---------------------------------------
                                      7 张章                                            42
                                      5 zhangsan                                        12
                                      1 Tom                                             31
                                      2 Kite                                            32
                                      3 Bob                                             33
                                      4 Mike                                            34

3. 程序包中的游标

程序包中使用游标与我们平常在 PL/SQL 块中游标的方式不太一样。在程序包中，游标的定义分为游标规范和游标主体两部分。在包规范中声明游标规范时必须使用 RETURN 子句指定游标的返回类型，RETURN子句指定的数据类型可以是：

用 %ROWTYPE 属性引用表定义的记录类型
自定义的记录类型，例如 TYPE EMPRECTYP IS RECORD(emp_id INTEGER,salary REAL)来定义的。

但是不可以是 number，varchar2， %TYPE等类型。

下面我们介绍在程序包中使用显式游标，示例如下所示：

-- 创建包头
CREATE OR REPLACE PACKAGE cur_pack
IS
    CURSOR stu_cur(stu_num number) RETURN student%rowtype; -- 游标规范部分，需要带上 return 子句
    PROCEDURE print_stu(stu_num number);
end cur_pack;
/

-- 创建包体
CREATE OR REPLACE PACKAGE BODY cur_pack
AS
    -- 记得参数名称需要与包头的对应上
    CURSOR stu_cur(stu_num number) RETURN student%ROWTYPE IS SELECT * FROM student WHERE sno = stu_num; -- 游标主体部分
    -- 存储过程主体部分
 	PROCEDURE print_stu(stu_num number) 
 	IS
 		stu student%ROWTYPE; -- 声明变量
 		BEGIN
  		OPEN stu_cur(stu_num);  -- 打开游标
  		LOOP
    		FETCH stu_cur INTO stu; -- 游标记录当前行记录
    		EXIT WHEN stu_cur%NOTFOUND;
    		DBMS_OUTPUT.PUT_LIne('当前学生信息：学号=' || stu.sno || ',姓名=' || stu.sname || ', 年龄=' || stu.sage);
  		END LOOP;
  		CLOSE stu_cur; -- 关闭游标
 	END print_stu;
END cur_pack;
/

使用如下语句调用如上的程序包，结果如下所示：

SQL> exec cur_pack.print_stu(2);

当前学生信息：学号=2,姓名=Kite, 年龄=32

PL/SQL procedure successfully completed

下面我们介绍在程序包中使用 REF 游标，示例如下所示：

-- 创建包头
create or replace package pack_ref is
    type refcur is ref cursor; -- 此处只声明类型为参照游标的类型变量
    procedure mycursor_use; -- 声明存储过程
end pack_ref;
/

-- 创建包体
create or replace package body pack_ref is
procedure mycursor_use is
    mycursor refcur; -- 此处声明类型为 refcur 的变量
    stu_rec student%rowtype;
begin
    open mycursor for select * from student;
    LOOP
        fetch mycursor into stu_rec;
        EXIT WHEN mycursor%NOTFOUND;
        DBMS_OUTPUT.put_line('学号：' || stu_rec.sno || '，姓名：' || stu_rec.sname);
    END LOOP;
    close mycursor;
end mycursor_use;
end pack_ref;
/

使用如下语句调用如上的程序包，结果如下所示：

SQL> exec pack_ref.mycursor_use();

学号：7，姓名：张章
学号：5，姓名：zhangsan
学号：1，姓名：Tom
学号：2，姓名：Kite
学号：3，姓名：Bob
学号：4，姓名：Mike

PL/SQL procedure successfully completed

4. 子程序和程序包的信息

USER_OBJECTS 视图包含用户创建的子程序和程序包的信息，通过如下语句查询：

1
2
3

SELECT object_name, object_type
FROM USER_OBJECTS
WHERE object_type IN ('PROCEDURE', 'FUNCTION', 'PACKAGE', 'PACKAGE BODY');

查询结果如下所示：

OBJECT_NAME                                                                      OBJECT_TYPE
-------------------------------------------------------------------------------- -------------------
PROC_OUT                                                                         PROCEDURE
FIND_EMP                                                                         PROCEDURE
PROC_EXCHANGE                                                                    PROCEDURE
FUN_HELLO                                                                        FUNCTION
STUD_RANKING                                                                     FUNCTION
PACK1                                                                            PACKAGE BODY
PACK1                                                                            PACKAGE
CUR_PACK                                                                         PACKAGE BODY
CUR_PACK                                                                         PACKAGE
PACK_REF                                                                         PACKAGE
PACK_REF                                                                         PACKAGE BODY

USER_SOURCE 视图存储子程序和程序包的源代码，通过如下语句查询：

1	SELECT text FROM USER_SOURCE WHERE NAME='PACK_REF'; ---注意大写

查询结果如下所示：

SQL> SELECT text FROM USER_SOURCE WHERE NAME='PACK_REF';     ---注意大写

TEXT
--------------------------------------------------------------------------------
package pack_ref is
    type refcur is ref cursor; -- 此处只声明类型为参照游标的类型变量
    procedure mycursor_use; -- 声明存储过程
end pack_ref;
package body pack_ref is
procedure mycursor_use is
    mycursor refcur; -- 此处声明类型为 refcur 的变量
    stu_rec student%rowtype;
begin
    open mycursor for select * from student;
    LOOP
        fetch mycursor into stu_rec;
        EXIT WHEN mycursor%NOTFOUND;
        DBMS_OUTPUT.put_line('学号：' || stu_rec.sno || '，姓名：' || stu_rec.sname);
    END LOOP;
    close mycursor;
end mycursor_use;
end pack_ref;

5. 内置程序包

oracle 内置有一些内置程序包，常用如下：

程序包名称	说明
STANDARD和DBMS_STANDARD	定义和扩展PL/SQL语言环境
DBMS_LOB	提供对 LOB数据类型进行操作的功能
DBMS_OUTPUT	处理PL/SQL块和子程序输出调试信息
DBMS_RANDOM	提供随机数生成器
DBMS_SQL	允许用户使用动态 SQL
DBMS_XMLDOM	用DOM模型读写XML类型的数据
DBMS_XMLPARSER	XML解析，处理XML文档内容和结构
DBMS_XMLQUERY	提供将数据转换为 XML 类型的功能
DBMS_XSLPROCESSOR	提供XSLT功能，转换XML文档
UTL_FILE	用 PL/SQL 程序来读写操作系统文本文件

5.1 DBMS_OUTPUT

该程序可以直接向控制台打印输出调试信息，类似于 Java 中的 System.out.println("xxxx");语句，这里不再过多说明。

5.2 DBMS_RANDOM

DBMS_RANDOM 包可用来生成随机整数。

示例1：

产生随机数

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
   l_num   NUMBER;
   counter NUMBER;
BEGIN
   counter:=1;
   WHILE counter <= 10
   LOOP
     l_num := DBMS_RANDOM.RANDOM;
     DBMS_OUTPUT.PUT_LINE(l_num);
     counter:=counter+1;
   END LOOP;
END;
/
-- 输出结果
-1844779820
2090599204
46142221
1419758385
-1286235225
-2065024678
-1259274836
-57157868
1933458772
-1037520283

示例2：

产生一个100以内的随机正整数

1	SELECT ABS(MOD(DBMS_RANDOM.RANDOM,100)) FROM DUAL;

示例3：

使用VALUE函数返回一个大于等于0但是小于1的数

1	SELECT DBMS_RANDOM.VALUE FROM DUAL;

示例4：

返回 [0, 100) 之间的值

对于指定范围内的整数，要加入参数low_value和high_value，并从结果中截取小数（最大值不能被作为可能的值）。所以对于0到99之间的小数，可以使用下面的代码：

1
2
3

SELECT  DBMS_RANDOM.VALUE(0, 100)  FROM  DUAL;  -- 返回区间内的小数

SELECT TRUNC(DBMS_RANDOM.VALUE(0, 100)) FROM  DUAL; -- 返回区间内的整数

示例5：

产生随机序列

STRING函数生成随机文本字符串，可以指定字符串的类型和所希望的长度：

1	SELECT DBMS_RANDOM.STRING('A', 20) rand_seq FROM DUAL; -- 生成长度为 20 的大小写混合的字符序列

第一个参数存在如下类型的代码：

‘U’用来生成大写字符，‘L’用来生成小写字符，‘A’用来生成大小写混合的字符，’P’ 表示字符串由任意可打印字符构成， ’X’ 表示字符串由大写字符和数字构成。

输出结果示例如下：

SQL> SELECT DBMS_RANDOM.STRING('A', 20) rand_seq FROM DUAL;  -- 生成长度为 20 的大小写混合的字符序列

RAND_SEQ
--------------------------------------------------------------------------------
iFvSXfHZMfNYIfffXDlo

5.3 UTL_FILE

UTL_FILE 包可用于读写操作系统文本文件，操作文件的一般过程是打开、读或写、关闭。UTL_FILE 包指定文件路径依赖于 DIRECTORY 对象。

首先需要登录管理员用户创建 DIRECTORY 对象，然后将该对象的读写权限授予指定用户，示例如下：

1 2	SQL> CREATE DIRECTORY TEST_DIR AS 'F:\TEST'; SQL> GRANT READ, WRITE ON DIRECTORY TEST_DIR TO SCOTT;

接下来我们就可以使用 SCOTT 用户来使用 UTL_FILE 来操作文件，使用示例如下：

DECLARE
  input_file   UTL_FILE.FILE_TYPE; -- 声明文件类型
  input_buffer VARCHAR2(4000);
BEGIN
  input_file := UTL_FILE.FOPEN('TEST_DIR', 'demo.txt', 'r'); -- 以读的方式打开 TEST_DIR 目录对象下的 demo.txt 文件
  LOOP
    UTL_FILE.GET_LINE(input_file, input_buffer); -- 每次读取一行
    DBMS_OUTPUT.PUT_LINE(input_buffer);
  END LOOP;
  UTL_FILE.FCLOSE(input_file); -- 关闭文件
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('------------------'); -- 读取到没有数据时打印
END;
/

5.4 DBMS_JOB

DBMS_JOB 可以创建定时任务调度程序，下面我们就简单的来使用 DBMS_JOB 程序包，关于更多的使用方法可以查阅官方文档。

首先创建测试表：

1	create table a(a date);

创建一个自定义过程：

create or replace procedure test as
begin
    insert into a values(sysdate);
end;
/

创建一个定时任务 JOB：

variable job1 number;
begin
    -- test; 表示调用的存储过程（需要分号），并将调用的结果返回给 job1 变量, : 指明 job1 是一个变量
    -- sysdate 参数是表示什么时候提交这个 job，这里我们使用当前时间
    -- 最后一个参数表示每天1440分钟，即一分钟运行test过程一次
    dbms_job.submit(:job1,'test;', sysdate, 'sysdate + 1 / 1440');
end;
/

运行 job：

begin
    dbms_job.run(:job1);
end;
/

检查结果，每隔一分钟 a 表就会插入一条记录：

SQL> select to_char(a,'yyyy/mm/dd hh24:mi:ss') 时间 from a;

时间
-------------------
2020/11/07 11:37:45
2020/11/07 11:38:45

删除JOB：

begin
    dbms_job.remove(:job1);
end;
/