21.Oracle的程序包(Package)

一、Package的概述

1、什么是Oracle11g的Package

Oracle11g的package是一种封装了一组相关的过程、函数、变量和常量的数据库对象。它是一种可重用的程序模块，可以在不同的应用程序中使用。Package是Oracle数据库中的一种高级编程技术，它可以让程序员将相关的代码封装在一起，提高代码的可重用性、可维护性和安全性。

• 程序包是对相关过程、函数、变量、游标和异常等对象的封装
• 程序包由规范和主体两部分组成

2、Package的作用是什么

Package的作用是将相关的过程、函数、变量和常量封装在一起，提供一种组织代码的方式。通过使用Package，可以将代码分组，使得代码更加易于管理和维护。Package还可以提供一些公共的接口，使得其他程序可以调用其中的过程和函数，从而实现代码的复用。

• 模块化
• 更轻松的应用程序设计
• 信息隐藏
• 新增功能
• 性能更佳

3、常见的系统内置Package

Oracle数据库中有很多常见的系统Package，包括：

这个表格展示了常见的系统内置Package的作用、常用函数以及这些函数的作用。这些包提供了丰富的功能，可以帮助开发人员处理各种不同的任务，如向控制台输出信息、动态执行SQL语句、管理作业、加密数据、读写文件等。

这些系统Package提供了很多常用的功能，可以帮助程序员更加方便地开发和管理数据库应用程序。同时，Oracle数据库还支持自定义Package，程序员可以根据自己的需求编写自己的Package，以实现更加灵活的功能。

二、创建Package的相关语法

1、Package的创建语法

在Oracle 11g中，创建Package的语法如下：

-- 创建Package的规范部分（包头）
CREATE OR REPLACE PACKAGE package_name
IS
    -- 声明变量
    variable_name datatype;

    -- 声明常量
    constant_name CONSTANT datatype := value;

    -- 声明过程
    PROCEDURE procedure_name (parameter_list);

    -- 声明函数
    FUNCTION function_name (parameter_list) RETURN return_datatype;
END package_name;
/
-- 创建Package的体部分（包体）
CREATE OR REPLACE PACKAGE BODY package_name
IS
    -- 实现过程
    PROCEDURE procedure_name (parameter_list) IS
    BEGIN
        -- 过程实现代码
    END procedure_name;

    -- 实现函数
    FUNCTION function_name (parameter_list) RETURN return_datatype IS
    BEGIN
        -- 函数实现代码
    END function_name;
END package_name;
/

在上面的语法中，首先使用CREATE OR REPLACE PACKAGE语句创建包的规范部分，其中可以包括变量、常量、过程和函数的声明。然后使用CREATE OR REPLACE PACKAGE BODY语句创建包的体部分，其中包含实际的过程和函数的实现。

2、Package的删除

在Oracle中，要删除一个Package，可以使用DROP PACKAGE语句。下面是删除一个Package的示例代码：

-- 删除Package的规范部分
DROP PACKAGE package_name;

-- 删除Package的体部分
DROP PACKAGE BODY package_name;

在上面的示例中，package_name是要删除的Package的名称。如果要删除Package的规范部分，可以使用DROP PACKAGE语句；如果要删除Package的体部分，可以使用DROP PACKAGE BODY语句。

需要注意的是，删除Package会同时删除其规范部分和体部分，因此在删除之前请确保不再需要该Package中的任何对象。此外，删除Package需要有相应的权限，通常需要有DROP ANY PACKAGE的系统权限或者对应Package的所有者权限。

3、具体案例

下面是一个具体的案例，创建一个包来计算圆的面积和周长：

CREATE OR REPLACE PACKAGE circle_package
IS
    -- 声明常量
    pi CONSTANT NUMBER := 3.14159;

    -- 声明函数
    FUNCTION calculate_area (radius NUMBER) RETURN NUMBER;
    FUNCTION calculate_circumference (radius NUMBER) RETURN NUMBER;
END circle_package;
/

CREATE OR REPLACE PACKAGE BODY circle_package
IS
    -- 实现函数
    FUNCTION calculate_area (radius NUMBER) RETURN NUMBER IS
    BEGIN
        RETURN pi * radius * radius;
    END calculate_area;

    FUNCTION calculate_circumference (radius NUMBER) RETURN NUMBER IS
    BEGIN
        RETURN 2 * pi * radius;
    END calculate_circumference;
END circle_package;
/

在上面的例子中，我们创建了一个名为circle_package的包，其中包含了常量pi和两个函数calculate_area和calculate_circumference，分别用于计算圆的面积和周长。在包的体部分中，我们实现了这两个函数的具体计算逻辑。

4、Package的使用

在Oracle 11g中，使用已经创建好的Package可以通过以下步骤进行：

1. 调用Package中的函数或过程
2. 使用Package中的常量

下面是一个示例代码，展示了如何使用已经创建好的Package：

-- 调用Package中的函数
DECLARE
    radius NUMBER := 5;
    area NUMBER;
    circumference NUMBER;
BEGIN
    -- 调用Package中的函数来计算圆的面积和周长
    area := math_operations.calculate_area(radius);
    circumference := math_operations.calculate_circumference(radius);
    
    -- 输出计算结果
    DBMS_OUTPUT.PUT_LINE('The area of the circle is: ' || area);
    DBMS_OUTPUT.PUT_LINE('The circumference of the circle is: ' || circumference);
END;
/

-- 使用Package中的常量
DECLARE
    radius NUMBER := 5;
    area NUMBER;
BEGIN
    -- 使用Package中的常量pi来计算圆的面积
    area := math_operations.pi * radius * radius;
    
    -- 输出计算结果
    DBMS_OUTPUT.PUT_LINE('The area of the circle is: ' || area);
END;
/

在上面的示例中，我们首先在PL/SQL块中调用了math_operations包中的函数来计算圆的面积和周长，并输出计算结果。然后，在另一个PL/SQL块中，我们使用了math_operations包中的常量pi来计算圆的面积，并输出计算结果。

通过这样的方式，我们可以方便地使用已经创建好的Package中的函数、过程和常量，从而实现代码的复用和模块化。

5、与Package相关的其他语法

除了创建和删除包之外，还可以对包进行其他操作，例如修改包中的内容、重新编译包等。下面是一些对包的其他操作以及相应的代码示例：

1. 修改包中的内容
   可以使用CREATE OR REPLACE PACKAGE和CREATE OR REPLACE PACKAGE BODY语句来修改包中的内容。例如，如果要修改包中的函数或过程的实现，可以使用CREATE OR REPLACE PACKAGE BODY语句重新定义函数或过程的实现。

   -- 修改包中的函数实现
   CREATE OR REPLACE PACKAGE BODY package_name
   IS
       FUNCTION new_function RETURN NUMBER IS
       BEGIN
           -- 新的函数实现
           RETURN 0;
       END new_function;
   END package_name;
   

2. 重新编译包
   如果修改了包中的内容，可以使用ALTER PACKAGE语句重新编译包，以确保修改生效。

   -- 重新编译包
   ALTER PACKAGE package_name COMPILE;
   

3. 查看包的内容
   可以使用DESCRIBE语句或者查询USER_OBJECTS和USER_SOURCE视图来查看包的结构和内容。

   -- 查看包的结构
   DESC package_name;
   
   -- 查看包的源代码
   SELECT text FROM USER_SOURCE WHERE name = 'PACKAGE_NAME';
   

4. 查询当前用户已经创建的包

   要查询当前用户已经创建的包，可以使用以下的SQL查询：

   SELECT object_name
   FROM user_objects
   WHERE object_type = 'PACKAGE';
   

   这条SQL查询会返回当前用户已经创建的所有包的名称。user_objects是Oracle系统表，包含了当前用户拥有的所有对象的信息，包括表、视图、索引、存储过程、函数和包等。我们在查询中加入了条件object_type = 'PACKAGE'，以便只返回包的信息。

   另外，如果你想查看其他用户创建的包，可以使用all_objects或dba_objects视图，前者包含了当前用户对其有访问权限的所有对象的信息，而后者包含了数据库中所有对象的信息。

通过这些操作，可以方便地对包进行修改、重新编译和查看，以满足不同的需求。

三、常见内置程序包的使用

下面是一些常见内置程序包的使用以及这些包中的一些常用的函数的示例代码：

1. DBMS_OUTPUT

   • 作用：向控制台输出信息
   • 常用函数：PUT_LINE，PUT

   -- 使用DBMS_OUTPUT包向控制台输出信息
   BEGIN
       DBMS_OUTPUT.PUT_LINE('Hello, world!');
   END;
   

2. DBMS_SQL

   • 作用：动态执行SQL语句
   • 常用函数：OPEN_CURSOR，PARSE，BIND_VARIABLE，EXECUTE，FETCH_ROWS

   -- 使用DBMS_SQL包动态执行SQL语句
   DECLARE
       l_cursor INTEGER;
       l_status INTEGER;
   BEGIN
       l_cursor := DBMS_SQL.OPEN_CURSOR;
       DBMS_SQL.PARSE(l_cursor, 'SELECT * FROM employees', DBMS_SQL.NATIVE);
       l_status := DBMS_SQL.EXECUTE(l_cursor);
       -- 其他操作...
       DBMS_SQL.CLOSE_CURSOR(l_cursor);
   END;
   

3. DBMS_JOB

   • 作用：创建和管理作业
   • 常用函数：SUBMIT，REMOVE，NEXT_DATE

   -- 使用DBMS_JOB包创建作业
   DECLARE
       l_job NUMBER;
   BEGIN
       DBMS_JOB.SUBMIT(l_job, 'my_procedure;', SYSDATE, 'SYSDATE + 1');
   END;
   

4. DBMS_LOCK

   • 作用：管理锁
   • 常用函数：REQUEST，RELEASE

   -- 使用DBMS_LOCK包管理锁
   DECLARE
       l_lockhandle VARCHAR2(128);
   BEGIN
       l_lockhandle := DBMS_LOCK.REQUEST(DBMS_LOCK.X_MODE, 'LOCK_NAME', 10, TRUE);
       -- 其他操作...
       DBMS_LOCK.RELEASE(l_lockhandle);
   END;
   

5. DBMS_CRYPTO

   • 作用：加密和解密数据
   • 常用函数：ENCRYPT，DECRYPT

   -- 使用DBMS_CRYPTO包加密数据
   DECLARE
       l_encrypted_data RAW(2000);
   BEGIN
       l_encrypted_data := DBMS_CRYPTO.ENCRYPT('my_data', DBMS_CRYPTO.DES_CBC_PKCS5);
       -- 其他操作...
   END;
   

6. UTL_FILE

   • 作用：读写文件
   • 常用函数：FOPEN，FCLOSE，PUT_LINE

   -- 使用UTL_FILE包读写文件
   DECLARE
       l_file UTL_FILE.FILE_TYPE;
   BEGIN
       l_file := UTL_FILE.FOPEN('MY_DIR', 'my_file.txt', 'W');
       UTL_FILE.PUT_LINE(l_file, 'Hello, world!');
       UTL_FILE.FCLOSE(l_file);
   END;
   

7. DBMS_METADATA

   • 作用：获取数据库对象的元数据信息
   • 常用函数：GET_DDL，GET_DEPENDENT_DDL

   -- 使用DBMS_METADATA包获取对象的DDL
   DECLARE
       l_ddl CLOB;
   BEGIN
       l_ddl := DBMS_METADATA.GET_DDL('TABLE', 'EMPLOYEES');
       DBMS_OUTPUT.PUT_LINE(l_ddl);
   END;
   

8. DBMS_APPLICATION_INFO

   • 作用：设置应用程序的信息
   • 常用函数：SET_MODULE，SET_ACTION

   -- 使用DBMS_APPLICATION_INFO包设置应用程序信息
   BEGIN
       DBMS_APPLICATION_INFO.SET_MODULE('HR', 'Data Import');
       DBMS_APPLICATION_INFO.SET_ACTION('Importing data from file...');
   END;
   

这些示例代码展示了常见内置程序包的使用以及这些包中的一些常用的函数。这些包提供了丰富的功能，可以帮助开发人员处理各种不同的任务，包括输出信息、动态执行SQL语句、管理作业、加密数据等。

1、DBMS_OUTPUT包

当我们需要在存储过程或触发器中输出信息到控制台时，可以使用DBMS_OUTPUT包。下面是一个示例，演示了如何使用DBMS_OUTPUT包输出信息到控制台：

-- 创建一个存储过程，使用DBMS_OUTPUT输出信息
CREATE OR REPLACE PROCEDURE display_employee_info (employee_id NUMBER) AS
    l_employee_name employees.first_name%TYPE;
    l_employee_salary employees.salary%TYPE;
BEGIN
    -- 查询员工姓名和工资
    SELECT first_name, salary INTO l_employee_name, l_employee_salary
    FROM employees
    WHERE employee_id = employee_id;

    -- 使用DBMS_OUTPUT输出信息
    DBMS_OUTPUT.PUT_LINE('Employee Name: ' || l_employee_name);
    DBMS_OUTPUT.PUT_LINE('Employee Salary: ' || l_employee_salary);
END;
/

在这个示例中，我们创建了一个存储过程display_employee_info，该存储过程接受一个员工ID作为参数，并使用DBMS_OUTPUT包输出员工的姓名和工资信息到控制台。

接下来，我们可以调用这个存储过程，并查看输出的信息：

-- 调用存储过程，并查看输出信息
SET SERVEROUTPUT ON;
BEGIN
    display_employee_info(100);
END;
/

在执行这段代码后，我们会在控制台上看到输出的员工姓名和工资信息：

Employee Name: Steven
Employee Salary: 24000

2、DBMS_XMLQUERY包

通过这个示例，我们展示了如何使用DBMS_OUTPUT包在存储过程中输出信息到控制台，这对于调试和日志记录非常有用。

DBMS_XMLQUERY包用于执行XML查询和转换操作。它提供了一些函数，可以将XML文档转换为关系数据或将关系数据转换为XML文档。下面是一个示例，演示了如何使用DBMS_XMLQUERY包将XML文档转换为关系数据：

-- 创建一个XML类型的表
CREATE TABLE xml_data (xml_content XMLTYPE);

-- 插入一条XML数据
INSERT INTO xml_data VALUES ('<employees>
  <employee>
    <id>100</id>
    <name>Steven</name>
    <salary>24000</salary>
  </employee>
  <employee>
    <id>101</id>
    <name>David</name>
    <salary>20000</salary>
  </employee>
</employees>');

-- 使用DBMS_XMLQUERY将XML数据转换为关系数据
DECLARE
  l_ctx DBMS_XMLQUERY.ctxType;
  l_rows DBMS_XMLQUERY.resultsType;
BEGIN
  -- 初始化上下文
  l_ctx := DBMS_XMLQUERY.newContext('SELECT * FROM xml_data');

  -- 执行查询
  DBMS_XMLQUERY.getRows(l_ctx, l_rows);

  -- 输出结果
  FOR i IN 1..l_rows.count LOOP
    DBMS_OUTPUT.PUT_LINE(l_rows(i).id || ' ' || l_rows(i).name || ' ' || l_rows(i).salary);
  END LOOP;

  -- 清理上下文
  DBMS_XMLQUERY.closeContext(l_ctx);
END;
/

在这个示例中，我们创建了一个XML类型的表xml_data，并向其中插入了一条XML数据。接下来，我们使用DBMS_XMLQUERY包将XML数据转换为关系数据，并输出结果到控制台。

在代码中，我们首先使用DBMS_XMLQUERY.newContext函数初始化一个查询上下文，然后使用DBMS_XMLQUERY.getRows函数执行查询并获取结果。最后，我们遍历结果集并使用DBMS_OUTPUT.PUT_LINE函数输出每一行的数据。

执行这段代码后，我们会在控制台上看到输出的关系数据：

100 Steven 24000
101 David 20000

通过这个示例，我们展示了如何使用DBMS_XMLQUERY包将XML文档转换为关系数据，这对于处理XML数据非常有用。

3、DBMS_RANDOM包

DBMS_RANDOM包是Oracle数据库中用于生成随机数的包。它提供了一系列函数，可以用来生成不同类型的随机数。下面是一个示例，演示了如何使用DBMS_RANDOM包生成随机数：

-- 使用DBMS_RANDOM包生成随机数
DECLARE
  l_random_number NUMBER;
BEGIN
  -- 生成一个介于1和10之间的随机整数
  l_random_number := DBMS_RANDOM.value(low => 1, high => 10);
  DBMS_OUTPUT.PUT_LINE('Random Number: ' || l_random_number);

  -- 生成一个0到1之间的随机浮点数
  l_random_number := DBMS_RANDOM.value;
  DBMS_OUTPUT.PUT_LINE('Random Float Number: ' || l_random_number);
END;
/

在这个示例中，我们使用了DBMS_RANDOM包的value函数来生成随机数。首先，我们使用DBMS_RANDOM.value函数生成一个介于1和10之间的随机整数，并将结果输出到控制台。然后，我们使用相同的函数生成一个0到1之间的随机浮点数，并同样将结果输出到控制台。

执行这段代码后，我们会在控制台上看到生成的随机数。例如：

Random Number: 7
Random Float Number: 0.832741

通过这个示例，我们展示了如何使用DBMS_RANDOM包生成随机数，这对于需要在数据库中进行随机化操作的场景非常有用。

4、UTL_FILE包

UTL_FILE包是Oracle数据库中用于读写操作系统文件的包。它提供了一系列的过程和函数，可以让数据库程序访问操作系统文件系统。下面是一个示例，演示了如何使用UTL_FILE包读取和写入文件：

-- 创建一个目录对象，指向数据库服务器上的一个目录
CREATE OR REPLACE DIRECTORY data_files AS '/u01/data_files';

-- 创建一个表，用于存储文件内容
CREATE TABLE file_content (file_name VARCHAR2(100), file_data CLOB);

-- 创建一个存储过程，使用UTL_FILE包读取文件内容并存储到表中
CREATE OR REPLACE PROCEDURE read_and_store_file(file_name IN VARCHAR2) IS
  file_handle UTL_FILE.FILE_TYPE;
  file_buffer VARCHAR2(32767);
  file_data CLOB;
BEGIN
  -- 打开文件
  file_handle := UTL_FILE.FOPEN('DATA_FILES', file_name, 'R');

  -- 读取文件内容
  LOOP
    BEGIN
      UTL_FILE.GET_LINE(file_handle, file_buffer);
      file_data := file_data || file_buffer;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        EXIT;
    END;
  END LOOP;

  -- 关闭文件
  UTL_FILE.FCLOSE(file_handle);

  -- 将文件内容存储到表中
  INSERT INTO file_content (file_name, file_data) VALUES (file_name, file_data);
  COMMIT;
END;
/

-- 调用存储过程，读取文件内容并存储到表中
BEGIN
  read_and_store_file('example.txt');
END;
/

在这个示例中，我们首先创建了一个目录对象data_files，并指向了数据库服务器上的一个目录/u01/data_files。然后，我们创建了一个表file_content，用于存储文件内容。接下来，我们创建了一个存储过程read_and_store_file，该存储过程使用UTL_FILE包打开、读取和关闭文件，并将文件内容存储到表中。

在存储过程中，我们使用UTL_FILE.FOPEN函数打开文件，然后使用UTL_FILE.GET_LINE函数逐行读取文件内容，并使用CLOB类型的变量file_data存储文件内容。最后，我们使用UTL_FILE.FCLOSE函数关闭文件，并将文件内容插入到表中。

通过这个示例，我们展示了如何使用UTL_FILE包读取文件内容并存储到数据库表中，这对于需要在数据库中处理文件数据的场景非常有用。

5、DBMS_JOB包

在Oracle数据库中，DBMS_JOB包用于管理和调度作业（jobs）。作业是在后台运行的一系列数据库操作，可以周期性地执行或者在特定时间执行。DBMS_JOB包提供了一系列的过程和函数，用于创建、调度、修改和删除作业。下面是一个示例，演示了如何使用DBMS_JOB包创建和调度一个作业：

-- 创建一个存储过程，用于作为作业的执行内容
CREATE OR REPLACE PROCEDURE my_job_procedure IS
BEGIN
  -- 在这里定义作业需要执行的数据库操作
  -- 例如：INSERT INTO my_table VALUES (1, 'Hello, World!');
  NULL;
END;
/

-- 使用DBMS_JOB包创建一个作业，并调度作业的执行
DECLARE
  l_job_number NUMBER;
BEGIN
  -- 创建一个作业，每天凌晨1点执行
  DBMS_JOB.SUBMIT(
    job       => l_job_number,
    what      => 'BEGIN my_job_procedure; END;',
    next_date => TRUNC(SYSDATE) + 1,
    interval  => 'TRUNC(SYSDATE) + 1'
  );
  COMMIT;
END;
/

在这个示例中，我们首先创建了一个存储过程my_job_procedure，该存储过程定义了作业需要执行的数据库操作。然后，我们使用DBMS_JOB包的SUBMIT过程创建了一个作业，并调度了作业的执行时间。在SUBMIT过程中，我们指定了作业的执行内容（即调用my_job_procedure存储过程），作业的下次执行时间（每天凌晨1点），以及作业的执行间隔（每天执行一次）。

通过这个示例，我们展示了如何使用DBMS_JOB包创建和调度一个作业，这对于需要在数据库中定期执行特定操作的场景非常有用。

6、DBMS_LOB包

DBMS_LOB包是Oracle数据库中用于管理大型对象（LOB，Large Objects）的包，提供了一系列的存储、读取、修改和删除LOB数据的功能。LOB数据类型包括CLOB（Character Large Object）、BLOB（Binary Large Object）和BFILE（Binary File）。下面是一个具体的代码案例，演示了DBMS_LOB包的一些常见用法：

-- 创建一个包含CLOB字段的表
CREATE TABLE my_table (
  id NUMBER,
  clob_data CLOB
);

-- 插入LOB数据
DECLARE
  l_clob CLOB;
BEGIN
  -- 创建一个CLOB对象
  DBMS_LOB.CREATETEMPORARY(l_clob, TRUE);
  -- 向CLOB对象写入数据
  DBMS_LOB.WRITEAPPEND(l_clob, 10, 'Hello, ');
  DBMS_LOB.WRITEAPPEND(l_clob, 5, 'World');
  -- 将CLOB对象插入到表中
  INSERT INTO my_table (id, clob_data) VALUES (1, l_clob);
  -- 释放CLOB对象
  DBMS_LOB.FREETEMPORARY(l_clob);
END;
/

-- 读取LOB数据
DECLARE
  l_clob_data CLOB;
BEGIN
  -- 从表中读取CLOB数据
  SELECT clob_data INTO l_clob_data FROM my_table WHERE id = 1;
  -- 输出CLOB数据
  DBMS_OUTPUT.PUT_LINE(l_clob_data);
END;
/

-- 更新LOB数据
DECLARE
  l_clob_data CLOB;
BEGIN
  -- 从表中读取CLOB数据
  SELECT clob_data INTO l_clob_data FROM my_table WHERE id = 1;
  -- 修改CLOB数据
  l_clob_data := l_clob_data || '!';
  -- 更新表中的CLOB数据
  UPDATE my_table SET clob_data = l_clob_data WHERE id = 1;
END;
/

-- 删除LOB数据
DECLARE
  l_clob_data CLOB;
BEGIN
  -- 从表中读取CLOB数据
  SELECT clob_data INTO l_clob_data FROM my_table WHERE id = 1;
  -- 删除表中的CLOB数据
  DELETE FROM my_table WHERE id = 1;
  -- 释放CLOB对象
  DBMS_LOB.FREETEMPORARY(l_clob_data);
END;
/

在这个代码案例中，我们首先创建了一个包含CLOB字段的表my_table，然后演示了DBMS_LOB包的几个常见用法：

1. 插入LOB数据：使用DBMS_LOB.CREATETEMPORARY创建一个临时的CLOB对象，然后使用DBMS_LOB.WRITEAPPEND向CLOB对象中写入数据，最后将CLOB对象插入到表中。
2. 读取LOB数据：使用SELECT语句从表中读取CLOB数据，并使用DBMS_OUTPUT.PUT_LINE输出到控制台。
3. 更新LOB数据：使用UPDATE语句修改表中的CLOB数据。
4. 删除LOB数据：使用DELETE语句删除表中的CLOB数据，并使用DBMS_LOB.FREETEMPORARY释放临时的CLOB对象。

通过这个代码案例，我们演示了DBMS_LOB包的一些常见用法，包括插入、读取、更新和删除LOB数据，展示了DBMS_LOB包在管理大型对象时的作用。