PL\SQL用户指南与参考9.1 转载

第九章 PL/SQL包

一、什么是PL/SQL包

包就是一个把各种逻辑相关的类型、常量、变量、异常和子程序组合在一起的模式对象。包通常由两个部分组成：包说明和包体，但有时包体是不需要的。说明(简写为spec)是应用程序接口；它声明了可用的类型、变量、常量、异常、游标和子程序，包体部分完全定义游标和子程序，并对说明中的内容加以实现。

如下例所示，我们可以认为说明部分是一个可选接口，而包体是一个"黑盒"。我们可以调试、增强或替换一个包体而不同改变接口(包说明)。

我们可以从SQL*Plus中使用CREATE PACKAGE语句来创建一个包。语法如下：

CREATE  [OR  REPLACE] PACKAGE  package_name
  [AUTHID  {CURRENT_USER | DEFINER}]
  {IS  | AS }
  [PRAGMA  SERIALLY_REUSABLE;]
  [collection_type_definition ...]
  [record_type_definition ...]
  [subtype_definition ...]
  [collection_declaration ...]
  [constant_declaration ...]
  [exception_declaration ...]
  [object_declaration ...]
  [record_declaration ...]
  [variable_declaration ...]
  [cursor_spec ...]
  [function_spec ...]
  [procedure_spec ...]
  [call_spec ...]
  [PRAGMA  RESTRICT_REFERENCES(assertions) ...]
END  [package_name];

[CREATE  [OR  REPLACE] PACKAGE  BODY  package_name {IS  | AS }
  [PRAGMA  SERIALLY_REUSABLE;]
  [collection_type_definition ...]
  [record_type_definition ...]
  [subtype_definition ...]
  [collection_declaration ...]
  [constant_declaration ...]
  [exception_declaration ...]
  [object_declaration ...]
  [record_declaration ...]
  [variable_declaration ...]
  [cursor_body ...]
  [function_spec ...]
  [procedure_spec ...]
  [call_spec ...]
[BEGIN
  sequence_of_statements]
END  [package_name];]

在说明部分声明的内容都是公有的，对应用程序是可见的。我们必须在所有的其他内容(除了用于为一个特殊的函数命名的编译指示；这样的编译指示必须跟在函数说明之后)声明之后才可以声明子程序。

包体中的内容有私有的，它实现了说明部分定义的细节内容，并且对应用程序是不可见的。紧跟着包体声明部分的是一个可选的初始化部分，它用于初始化包中的变量等。

AUTHID语句决定了是否是所有的打包子程序都按定义者权限(默认)或调用者权限执行，其中涉及到的模式对象是在定义者的模式中解析还是在调用者的模式中解析。

一个调用说明能让我们在Oracle数据词典中发布一个Java方法或外部C函数。调用说明靠把程序的名称、参数类型和返回类型映射到它们的SQL副本(SQL counterpart)中来发布程序。

1、PL/SQL包举例

在下面的例子中，我们把一个记录类型、游标和两个employment过程进行打包。要注意，过程hire_employee使用数据库序列empno_seq和函数SYSDATE分别插入到字段雇员编号和雇佣日期。

CREATE  OR  REPLACE PACKAGE  emp_actions AS    -- spec
  TYPE  emprectyp IS  RECORD (
    emp_id   INT,
    salary   REAL
  );

  CURSOR  desc_salary RETURN  emprectyp;

  PROCEDURE  hire_employee(
    ename    VARCHAR2 ,
    job      VARCHAR2 ,
    mgr      NUMBER ,
    sal      NUMBER ,
    comm     NUMBER ,
    deptno   NUMBER
  );

  PROCEDURE  fire_employee(emp_id NUMBER );
END  emp_actions;

CREATE  OR  REPLACE PACKAGE  BODY  emp_actions AS    -- body
  CURSOR  desc_salary RETURN  emprectyp IS
    SELECT    empno, sal
        FROM  emp
    ORDER  BY  sal DESC ;

  PROCEDURE  hire_employee(
    ename    VARCHAR2 ,
    job      VARCHAR2 ,
    mgr      NUMBER ,
    sal      NUMBER ,
    comm     NUMBER ,
    deptno   NUMBER
  ) IS
  BEGIN
    INSERT  INTO  emp
         VALUES  (empno_seq.NEXTVAL ,
                 ename,
                 job,
                 mgr,
                 SYSDATE ,
                 sal,
                 comm,
                 deptno);
  END  hire_employee;

  PROCEDURE  fire_employee(emp_id NUMBER ) IS
  BEGIN
    DELETE  FROM  emp
          WHERE  empno = emp_id;
  END  fire_employee;
END  emp_actions;

只有在包说明部分的声明内容对应用程序才是可见可访问的；包体的详细实现是不可见不可访问的。所以，我们可以在不重新编译调用程序的前提下修改包体(实现)。

二、PL/SQL包的优点

包提供了几个优点：模块化、方便应用程序设计、信息隐藏、附加功能和良好的性能。

• 模块化

包能让我们把逻辑相关的类型、常量、变量、异常和子程序等放到一个命名的PL/SQL模块中。每一个包都容易理解，包与包之间接口简单、清晰。这将有助于程序开发。

• 轻松的程序设计

设计应用程序时，我们首先要确定的是包说明中的接口信息。我们可以在没有包体的条件下编写并编译说明部分。然后引用该包的存储子程序也会被编译。在完成整个应用程序之前，我们是不需要完全实现包体部分的。

• 信息隐藏

有了包，我们就可以指定哪些类型、常量、变量、异常和子程序等是公有(可见和可访问)或私有(隐藏和不可访问)。例如，如果一个包里包含了四个子程序，其中三个是公有的一个是私有的。包就会隐藏私有子程序的实现，这样的话，如果实现内容发生改变，受到影响的只有包本身(不是我们的应用程序)。同样，对用户隐藏实现细节也能保证包的完整性。

• 附加功能

打包公有变量和游标在一个会话期会一直存在。所以，它们可以被当前环境下的所有子程序共享。并且它们允许我们跨事务来维护数据而不用把它保存在数据库中。

• 良好的性能

在我们首次调用打包子程序时，整个包就会被加载到内存中。所以，以后调用包中的相关子程序时，就不需要再次读取磁盘了。包能阻塞级联依赖，这样就能避免不必要的编译。例如，如果我们改变打包函数的实现，Oracle不需要重新编译调用子程序，因为它们并不依赖于包体。

三、理解包说明

包说明包括了公有声明。这些声明的作用于对于数据库模式来说是本地的，对于包来说是全局的。所以，被声明的内容可以从应用程序中和包的任何地方访问。下图演示了包的作用范围：

说明中列出了包中对应用程序所有可用的资源。例如，下面的声明演示了一个接受INTEGER类型的参数并返回一个INTEGER结果的函数fac：

FUNCTION  fac (n INTEGER ) RETURN  INTEGER ; -- returns n!

这些就是我们要调用的函数的所有信息。我们并不需要考虑它的实现细节(如，是使用迭代还是递归)。

只有子程序和游标有实现部分。所以，如果一个说明只有类型、常量、变量、异常的声明和调用说明，那么包体就没有必要的了。下面就是一个没有包体的包：

CREATE  PACKAGE  trans_data AS    -- bodiless package
  TYPE  timerec IS  RECORD (
    minutes   SMALLINT ,
    hours     SMALLINT
  );

  TYPE  transrec IS  RECORD (
    CATEGORY   VARCHAR2 ,
    ACCOUNT    INT,
    amount     REAL ,
    time_of    timerec
  );

  minimum_balance   CONSTANT  REAL       := 10.00;
  number_processed           INT;
  insufficient_funds         EXCEPTION ;
END  trans_data;

包trans_data不需要包体，因为类型、常量、变量和异常并没有实现部分。这样的包能让我们定义全局变量，可供子程序和数据库触发器使用。

1、引用包的内容

如果要引用包内声明的类型、常量、变量、异常和子程序等，就得使用点标识：

package_name.type_name
package_name.item_name
package_name.subprogram_name
package_name.call_spec_name

我们可以从数据库触发器、存储子程序、3GL(第三代程序语言)应用程序和各种Oracle工具中引用包中的内容。例如，我们可以从SQL*Plus中调用过程hire_employee：

SQL > CALL emp_actions.hire_employee('TATE' , 'CLERK' , ...);

下例中，我们可以从一个嵌入到Pro*C的匿名PL/SQL块调用同样的过程。实参emp_name和job_title是主变量(即声明在主环境中的变量)。

EXEC SQL  EXECUTE
BEGIN
  emp_actions.hire_employee(:emp_name, :job_title, ...);

• 约束

我们不能直接或间接地引用远程打包变量。例如，我们不能远程调用下面的过程，因为它在参数初始化子句中引用了打包变量：

CREATE  PACKAGE  random AS
  seed NUMBER ;
  PROCEDURE  initialize (starter IN  NUMBER  := seed, ...);

同样，我们也不能在包的内部引用主变量。

四、理解包体

包体是对包说明的实现。也就是说包体中包含了包说明中声明的每一个游标和子程序的实现。一定要记住，包体内实现的内容只有在包说明中声明之后才能在包外引用。为了匹配包说明和包体，PL/SQL做了一个token-by-token的子程序头比较。所以，除了空白内容，头部内容必须完全一致。否则， PL/SQL就会抛出异常，如下例所示：

CREATE  PACKAGE  emp_actions AS
  ...
  PROCEDURE  calc_bonus(date_hired emp.hiredate%TYPE , ...);
END  emp_actions;

CREATE  PACKAGE  BODY  emp_actions AS
  ...
  PROCEDURE  calc_bonus(date_hired DATE , ...) IS
  -- parameter declaration raises an exception because 'DATE'
  -- does not match 'emp.hiredate%TYPE' word for word
  BEGIN  ... END ;
END  emp_actions;

包体能包含私有声明，可以定义类型和其它所需的内容。这些声明的内容对于包体来说是本地的。因此，声明的内容在包体之外是无法访问的。与包说明部分不同的是，包体的声明部分可以包含子程序体。

在包体的声明部分之后是一个可选的初始化部分，一般是用于初始化包中变量。

包初始化部分起到的作用并不大，与子程序不同，它不能被调用或是接受参数。因此，包初始化部分只能在我们首次引用包的时候运行一次。

请记住，如果一个包说明只声明了类型、常量、变量、异常和调用说明，那么，包体部分就不是必需的了。但是，包体可以用于初始化包说明中声明的内容。

五、包特性的例子

下面是一个名为emp_actions的包。包说明声明了类型、游标、异常和子程序：

1. 类型EmpRecTyp和DeptRecTyp
2. 游标desc_salary
3. 异常invalid_salary
4. 函数hire_employee和raise_salary
5. 过程fire_empire和raise_salary

在编写包之后，我们就可以开发引用它声明的类型，调用它的子程序、游标和异常的应用程序。创建包时，它就会被存放在Oracle数据库中供广泛地调用。

CREATE  PACKAGE  emp_actions AS
  /* Declare externally visible types, cursor, exception. */
  TYPE  emprectyp IS  RECORD (
    emp_id   INT,
    salary   REAL
  );

  TYPE  deptrectyp IS  RECORD (
    dept_id    INT,
    LOCATION   VARCHAR2
  );

  CURSOR  desc_salary RETURN  emprectyp;

  invalid_salary   EXCEPTION ;

  /* Declare externally callable subprograms. */
  FUNCTION  hire_employee(
    ename    VARCHAR2 ,
    job      VARCHAR2 ,
    mgr      REAL ,
    sal      REAL ,
    comm     REAL ,
    deptno   REAL
  )
    RETURN  INT;

  PROCEDURE  fire_employee(emp_id INT);

  PROCEDURE  raise_salary(emp_id INT, grade INT, amount REAL );

  FUNCTION  nth_highest_salary(n INT)
    RETURN  emprectyp;
END  emp_actions;

CREATE  PACKAGE  BODY  emp_actions AS
  number_hired   INT;   -- visible only in this package

  /* Fully define cursor specified in package. */
  CURSOR  desc_salary RETURN  emprectyp IS
    SELECT    empno, sal
        FROM  emp
    ORDER  BY  sal DESC ;

  /* Fully define subprograms specified in package. */
  FUNCTION  hire_employee(
    ename    VARCHAR2 ,
    job      VARCHAR2 ,
    mgr      REAL ,
    sal      REAL ,
    comm     REAL ,
    deptno   REAL
  )
    RETURN  INT IS
    new_empno   INT;
  BEGIN
    SELECT  empno_seq.NEXTVAL
      INTO  new_empno
      FROM  DUAL;

    INSERT  INTO  emp
         VALUES  (new_empno, ename, job, mgr, SYSDATE , sal, comm, deptno);

    number_hired    := number_hired + 1;
    RETURN  new_empno;
  END  hire_employee;

  PROCEDURE  fire_employee(emp_id INT) IS
  BEGIN
    DELETE  FROM  emp
          WHERE  empno = emp_id;
  END  fire_employee;

  /* Define local function, available only inside package. */
  FUNCTION  sal_ok(RANK INT, salary REAL )
    RETURN  BOOLEAN  IS
    min_sal   REAL ;
    max_sal   REAL ;
  BEGIN
    SELECT  losal, hisal
      INTO  min_sal, max_sal
      FROM  salgrade
     WHERE  grade = RANK;

    RETURN  (salary >= min_sal) AND (salary <= max_sal);
  END  sal_ok;

  PROCEDURE  raise_salary(emp_id INT, grade INT, amount REAL ) IS
    salary   REAL ;
  BEGIN
    SELECT  sal
      INTO  salary
      FROM  emp
     WHERE  empno = emp_id;

    IF  sal_ok(grade, salary + amount) THEN
      UPDATE  emp
         SET  sal = sal + amount
       WHERE  empno = emp_id;
    ELSE
      RAISE  invalid_salary;
    END  IF ;
  END  raise_salary;

  FUNCTION  nth_highest_salary(n INT)
    RETURN  emprectyp IS
    emp_rec   emprectyp;
  BEGIN
    OPEN  desc_salary;

    FOR  i IN  1 .. n LOOP
      FETCH  desc_salary
       INTO  emp_rec;
    END  LOOP ;

    CLOSE  desc_salary;

    RETURN  emp_rec;
  END  nth_highest_salary;
BEGIN    -- initialization part starts here
  INSERT  INTO  emp_audit
       VALUES  (SYSDATE , USER , 'emp_actions' );

  number_hired    := 0;
END  emp_actions;

请记住，包初始化部分只是在我们首次引用包的时候执行一次。所以，在上面的例子中，只有一行数据被插入数据表emp_audit。同样，变量number_hired也只被初始化一次。

每次hire_employee被调用的时候，变量nubmer_hired就会被更新。但是，number_hired所记录的数字是与特定的会话相关的。也就是说，计数的结果是与一个用户所处理的新雇员的个数，而不是所有会话处理过的雇员个数。

在下面的例子中，我们把一些典型的银行事务进行打包。假设借款与贷款事务都是在营业时间之后通过自动出纳机处理，然后在第二天早上应用到账户中去。

CREATE  PACKAGE  bank_transactions AS
  /* Declare externally visible constant. */
  minimum_balance   CONSTANT  REAL  := 100.00;

  /* Declare externally callable procedures. */
  PROCEDURE  apply_transactions;

  PROCEDURE  enter_transaction(acct INT, kind CHAR , amount REAL );
END  bank_transactions;

CREATE  PACKAGE  BODY  bank_transactions AS
  /* Declare global variable to hold transaction status. */
  new_status   VARCHAR2 (70) := 'Unknown' ;

  /* Use forward declarations because apply_transactions
  calls credit_account and debit_account, which are not
  yet declared when the calls are made. */
  PROCEDURE  credit_account(acct INT, credit REAL );

  PROCEDURE  debit_account(acct INT, debit REAL );

  /* Fully define procedures specified in package. */
  PROCEDURE  apply_transactions IS
    /* Apply pending transactions in transactions table
    to accounts table. Use cursor to fetch rows. */
    CURSOR  trans_cursor IS
      SELECT         acct_id, kind, amount
               FROM  transactions
              WHERE  status = 'Pending'
           ORDER  BY  time_tag
      FOR  UPDATE  OF  status;   -- to lock rows
  BEGIN
    FOR  trans IN  trans_cursor LOOP
      IF  trans.kind = 'D'  THEN
        debit_account(trans.acct_id, trans.amount);
      ELSIF  trans.kind = 'C'  THEN
        credit_account(trans.acct_id, trans.amount);
      ELSE
        new_status    := 'Rejected' ;
      END  IF ;

      UPDATE  transactions
         SET  status = new_status
       WHERE  CURRENT  OF  trans_cursor;
    END  LOOP ;
  END  apply_transactions;

  PROCEDURE  enter_transaction(
                              /* Add a transaction to transactions table. */
                              acct INT, kind CHAR , amount REAL ) IS
  BEGIN
    INSERT  INTO  transactions
         VALUES  (acct, kind, amount, 'Pending' , SYSDATE );
  END  enter_transaction;

  /* Define local procedures, available only in package. */
  PROCEDURE  do_journal_entry(
                             /* Record transaction in journal. */
                             acct INT, kind CHAR , new_bal REAL ) IS
  BEGIN
    INSERT  INTO  journal
         VALUES  (acct, kind, new_bal, SYSDATE );

    IF  kind = 'D'  THEN
      new_status    := 'Debit applied' ;
    ELSE
      new_status    := 'Credit applied' ;
    END  IF ;
  END  do_journal_entry;

  PROCEDURE  credit_account(acct INT, credit REAL ) IS
    /* Credit account unless account number is bad. */
    old_balance   REAL ;
    new_balance   REAL ;
  BEGIN
    SELECT         balance
             INTO  old_balance
             FROM  accounts
            WHERE  acct_id = acct
    FOR  UPDATE  OF  balance;   -- to lock the row

    new_balance    := old_balance + credit;

    UPDATE  accounts
       SET  balance = new_balance
     WHERE  acct_id = acct;

    do_journal_entry(acct, 'C' , new_balance);
  EXCEPTION
    WHEN  NO_DATA_FOUND THEN
      new_status    := 'Bad account number' ;
    WHEN  OTHERS  THEN
      new_status    := SUBSTR(SQLERRM , 1, 70);
  END  credit_account;

  PROCEDURE  debit_account(acct INT, debit REAL ) IS
    /* Debit account unless account number is bad or
    account has insufficient funds. */
    old_balance          REAL ;
    new_balance          REAL ;
    insufficient_funds   EXCEPTION ;
  BEGIN
    SELECT         balance
             INTO  old_balance
             FROM  accounts
            WHERE  acct_id = acct
    FOR  UPDATE  OF  balance;   -- to lock the row

    new_balance    := old_balance - debit;

    IF  new_balance >= minimum_balance THEN
      UPDATE  accounts
         SET  balance = new_balance
       WHERE  acct_id = acct;

      do_journal_entry(acct, 'D' , new_balance);
    ELSE
      RAISE  insufficient_funds;
    END  IF ;
  EXCEPTION
    WHEN  NO_DATA_FOUND THEN
      new_status    := 'Bad account number' ;
    WHEN  insufficient_funds THEN
      new_status    := 'Insufficient funds' ;
    WHEN  OTHERS  THEN
      new_status    := SUBSTR(SQLERRM , 1, 70);
  END  debit_account;
END  bank_transactions;

在这个包中，我们没有使用初始化部分。

六、包中私有项和公有项

再看一下包emp_actions。包体声明了一个名为number_hired的变量，它被初始化为零。与在包说明中声明的内容不同，包体中的内容只局限于在当前包内使用。因此，在包外的PL/SQL代码就不能引用变量number_hired，这样的内容就是私有的。

但是，在emp_actions的说明中声明的内容，如异常invalid_salary，它对包外的程序来说就是可见的。因此，任何PL/SQL代码都可以访问它。这样的内容就是公有的。

当我们必须通过一个会话或跨事务来维护一些内容时，就需要把它们放在包体的声明部分。例如，number_hired的值在同一个会话中会被不断的更新。当会话结束时，这个值就会丢失。

如果我们必须把某些内容声明为公有的，就要把它们放在包的说明中。例如，声明在包bank_transactions中的常量minimum_balance就可以被广泛地使用。

七、重载包级子程序

PL/SQL允许两个或多个包级子程序拥有相同的名称。当我们想让一个子程序接受类型不同的相似的参数集合时，这个功能就很有效。例如，下面的包就定义了两个名为journalize过程：

CREATE  PACKAGE  journal_entries AS
  ...
  PROCEDURE  journalize(amount REAL , trans_date VARCHAR2 );

  PROCEDURE  journalize(amount REAL , trans_date INT);
END  journal_entries;

CREATE  PACKAGE  BODY  journal_entries AS
  ...
  PROCEDURE  journalize(amount REAL , trans_date VARCHAR2 ) IS
  BEGIN
    INSERT  INTO  journal
         VALUES  (amount, TO_DATE(trans_date, 'DD-MON-YYYY' ));
  END  journalize;

  PROCEDURE  journalize(amount REAL , trans_date INT) IS
  BEGIN
    INSERT  INTO  journal
         VALUES  (amount, TO_DATE(trans_date, 'J' ));
  END  journalize;
END  journal_entries;

第一个过程接受一个字符串trans_date，而第二个过程接受一个数字类型trans_date。每个过程都对数据进行了适当的处理操作。

八、包STANDARD是如何定义PL/SQL环境的

STANDARD包定义了PL/SQL环境。这个包的说明部分定义了全局类型、异常和子程序，这些内容对PL/SQL程序来说都是可用的。例如，STANDARD包声明了能返回一个数字的绝对值的ABS函数：

FUNCTION  ABS (n NUMBER ) RETURN  NUMBER ;

包STANDARD的内容对应用程序是可见的。我们不需要在引用这些内容的前面加限定修饰词。例如，我们可以从数据库触发器、存储子程序、Oracle工具或3GL应用程序中直接调用ABS：

abs_diff := ABS(x - y);

如果我们在PL/SQL中重新声明了ABS，我们的本地声明就会覆盖掉全局声明。但是，我们仍然可以通过添加限定修饰词来引用内部函数ABS：

abs_diff := STANDARD.ABS(x - y);

大多数的内部函数都被重载了。例如，包STANDARD包含了下面几种声明：

FUNCTION  TO_CHAR (right DATE ) RETURN  VARCHAR2 ;
FUNCTION  TO_CHAR (left NUMBER ) RETURN  VARCHAR2 ;
FUNCTION  TO_CHAR (left DATE , right VARCHAR2 ) RETURN  VARCHAR2 ;
FUNCTION  TO_CHAR (left NUMBER , right VARCHAR2 ) RETURN  VARCHAR2 ;

PL/SQL会按照形参与实参的个数和类型来解析对函数TO_CHAR的调用。