Oracle SQL 学习笔记23 - 包2

子程序重载

通过重载，可以重建多个重名的子程序。这些同名的子程序的参数数量或个数或类型有所不同。重载有利于不同情况下灵活调用子程序，可以在不丢失已有代码的情况下扩展程序功能。可用于本地子程序但不可用于独立的子程序。

• 重载实例1

CREATE  OR  REPLACE  PACKAGE  dept_pkg  IS
  PROCEDURE  add_department(deptno  NUMBER,  name  VARCHAR2  :=  'unknown',  loc  NUMBER  :=  1700);
  PROCEDURE  add_department(name  VARCHAR2  :=  'unknown',  loc  NUMBER  :=  1700);
END  dept_pkg;
/

• 重载实例2

CREATE  OR  REPLACE  PACKAGE  BODY  dept_pkg  IS
  PROCEDURE  add_department(deptno  NUMBER,  name  VARCHAR2  :=  'unknown',  loc  NUMBER  :=  1700)  IS
  BEGIN
    INSERT  INTO  departments(department_id,  department_name,  location_id)
    VALUES  (deptno,  name,  loc);
  END  add_department;
  PROCEDURE  add_department(name  VARCHAR2  :=  'unknown',  loc  NUMBER  :=  1700)  IS
  BEGIN
    INSERT  INTO  departments(department_id,  department_name,  location_id)
    VALUES  (departments_seq.NEXTVAL,  name,  loc);
  END  add_department;
END  dept_pkg;

STANDARD 的重载

STANDATRD 定义了PL/SQL环境和预定义函数。大多数预定义函数都是重载的，例如TO_CHAR：

FUNCTION  TO_CHAR(p1  DATE)  RETURN  VARCHAR2;

FUNCTION  TO_CHAR(p1  DATE,  p2  VARCHAR2)  RETURN  VARCHAR2;

FUNCTION  TO_CHAR(p1  NUMBER)  RETURN  VARCHAR2;

FUNCTION  TO_CHAR(p1  NUMBER,  p2  VARCHAR2)  RETURN  VARCHAR2;

如果自定义了一个子程序和预定义的函数重名，那么就需要用STANDARD.function_name的方式引用预定义函数了。

前置声明

前置声明的必须要性

块结构语言（例如PL/SQL）必须先定义后引用，例如下面的代码就有问题：

CREATE  OR  REPLACE  PACKAGE  BODY  forward_pkg  IS
  PROCEDURE  award_bonus(...)  IS
  BEGIN
    calc_rating(..)  -- illegal reference
  END;
  PROCEDURE  calc_rating(...)  IS
  BEGIN
    ...
  END;
END  forward_pkg;
/

包的初始化块

package 变量初始化部分

package首次被调用的时候，置于package body最后位置的程序块会被执行一次，可将初始化变量的代码放在这里：

CREATE  OR  REPLACE  PACKAGE  taxes  IS
  tax  NUMBER;
  ...  -- declare  all public procedures/functions
END  taxes;
/
CREATE  OR  REPLACE  PACKAGE  BODY  taxes  IS
  ...  --declare  all private variables
  ...  --define public/private  procedures/functions
BEGIN
  SELECT  rate_value  INTO  tax
  FROM  tax_rates
  WHERE  rate_name  =  'TAX';
END  taxes;
/

包状态的持久化

包的状态

包所定义的一些列变量的值决定了包的状态。包的状态包括：

1. 包第一次被引用的时候初始化
2. 默认的生命周期是整个会话

• 存于UGA
• 对每个session唯一
• 当子程序调用或者公有变量被修改则发生变化

3. 如果在定义时加上了 PRAGMA SERIALLY_REUSABLE，则包状态的生命周期就是子程序调用周期而非整个会话周期（包头和包体需要同时定义）。

   
   pragma.JPG

pacakge Cursor的持久化状态

• 定义 CURS_PKG

CREATE  OR  REPLACE  PACKAGE  BODY  curs_pkg  IS
  CURSOR  c  IS  SELECT  employee_id  FROM  employees;
  PROCEDURE  open  IS
  BEGIN
    IF  NOT  c%ISOPEN  THEN  OPNE  c;  END  IF;
  END  open;
  FUNCTION  next(n  NUMBER  :=  1)  RETURN  BOOLEAN  IS
    emp_id  employees.employee_id%TYPE
    FOR  count  IN  1  ..  n  LOOP
      FETCH  c  INTO  emp_id;
      EXIT  WHEN  c%NOTFOUND
      DBMS_OUTPUT.PUT_LINE('Id:  '  ||  (emp_id));
    END  LOOP
    RETURN  c%FOUND;
  END  next;
  PROCEDURE  close  IS  BEGIN
    IF  c%ISOPEN  THEN  CLOSE  c;  END  IF;
  END  close;
END  curs_pkg;

• 执行 CURS_PKG

SET  SERVEROUTPUT  ON
EXECUTE  curs_pkg.open
DECLARE
  more  BOOLEAN  :=  curs_pkg.next(3);
BEGIN
  IF  NOT  more  THEN
    curs_pkg.close
  END  IF;
END
/
RUN  -- repeats  execution on the anonymous block
EXECUTE  curs_pkg.close

在package中使用plsql table

CREATE  OR  REPLACE  PACKAGE  emp_pkg  IS
  TYPE  emp_table_type  IS  TABLE  OF  employees%ROWTYPE
    INDEX  BY  BINARY_INTEGER
  PROCEDURE  get_employees(emps  OUT  emp_table_type);
END  emp_pkg;

CREATE  OR  REPLACE  PACKAGE  BODY  emp_pkg  IS
  PROCEDURE  get_employees(emps  OUT  emp_table_type)  IS
    i  BIANRY_INTEGER  :=  0;
  BEGIN
    FOR  emp_record  IN  (SELECT  *  FROM  employees)
    LOOP
      emps(i)  :=  emp_record;
      i  :=  i+1;
    END  LOOP;
  END  get_employees;
END  emp_pkg;
/

使用PL/SQL wrapper

PL/SQL wrapper 是一个封装plsql代码的独立工具，具有如下功能：

• 平台无关性
• 动态装载
• 动态绑定
• 自动检查依赖关系
• 支持 exp/imp

运行Warpper

命令行语法是

WRAP  INAME  =  input_file_name  [ONAME=output_file_name]

其中，INAME参数是必须的，input file 的默认扩展名是.sql；ONAME参数是可选的，其默认的扩展名是.plb。

• 实例

WARP  INAME=demo_o4_hello.sql
WARP  INAME=demo_o4_hello
WARP  INAME=demo_o4_hello.sql  ONAME=demo_04_hello.plb

封装指导

• 封装package body不要封装package specification
• 检查语法错误但是不会检查语义错误（如表不存在）
• 输出文件不可编辑
• 源代码需要编程者自行维护