第一章 PL/SQL概览

一、理解PL/SQL的主要特性

1. PL/SQL简介

理解PL/SQL的主要特性，了解PL/SQL最好的方法就是从简单的实例入手。下面的程序是用于处理一个网球拍订单的。首先声明一个NUMBER类型的变量来存放现有的球拍数量。然后从数据表inventory中把球拍的数量检索出来。如果数量大于零，程序就会更新inventory表，并向purchase_record表插入一条购买记录，如果数量不大于零，程序会向purchase_record表插入一条脱销 (out-of-stock) 记录。

DECLARE
 qty_on_hand   NUMBER (5) ;
BEGIN
 SELECT        quantity
          INTO qty_on_hand
          FROM inventory
         WHERE product = 'TENNIS RACKET'
 FOR UPDATE OF quantity;

 IF qty_on_hand > 0 THEN   -- check quantity
   UPDATE inventory
      SET quantity = quantity - 1
    WHERE product = 'TENNIS RACKET';

   INSERT INTO purchase_record
        VALUES  ('Tennis racket purchased', SYSDATE) ;
 ELSE
   INSERT INTO purchase_record
        VALUES  ('Out of tennis rackets', SYSDATE) ;
 END IF;

 COMMIT;
END;

在PL/SQL中，可以使用SQL语句来操作Oracle中的数据，并使用流程控制语句来处理数据。我们还可以声明常量和变量，定义函数和过程并捕获运行时错误。因此，PL/SQL是一种把SQL对数据操作的优势和过程化语言数据处理优势结合起来的语言。

2. PLSQL结构

2.1 PLSQL完整结构

PL/SQL 存储过程的整体结构本质上是一个命名的 PL/SQL 块，但比匿名块多了声明部分的参数定义和存储特性。其完整结构由以下 4 个核心部分组成，遵循 “声明 - 执行 - 异常处理” 的块结构逻辑：

CREATE [OR REPLACE] PROCEDURE 存储过程名 (
  -- 1. 参数声明部分（可选）
  参数1 [IN/OUT/IN OUT] 数据类型 [DEFAULT 默认值],
  参数2 [IN/OUT/IN OUT] 数据类型 [DEFAULT 默认值],
  ...
) 
[AUTHID DEFINER/CURRENT_USER]  -- 权限选项（可选）
[DETERMINISTIC]  -- 确定性选项（可选）
IS/AS -- 这里的IS = 匿名块中的DECLARE，用于声明内部元素
  -- 2. 内部变量/类型声明部分（可选）
  变量名 数据类型 [:= 初始值];
  类型名 类型定义;
  游标名 游标定义;
  异常名 EXCEPTION;
BEGIN
  -- 3. 执行部分（必需）
  业务逻辑代码;  -- 包含SQL操作、流程控制、函数调用等
  [COMMIT/ROLLBACK;]  -- 事务控制（可选）
EXCEPTION
  -- 4. 异常处理部分（可选）
  WHEN 异常类型1 THEN
    异常处理逻辑1;
  WHEN 异常类型2 THEN
    异常处理逻辑2;
  WHEN OTHERS THEN
    其他异常处理逻辑;
END 存储过程名;

2.2 块结构

PL/SQL是一种块结构的语言，它的基本组成单元是一些逻辑块，而这些块又能嵌套任意数量子块。通常，每一个逻辑块都承担一部分工作任务， PL/SQL这种将问题分而治之 (divide-and-conquer) 的方法称为逐步求精 (stepwise refinement) 。块能够让我们把逻辑相关的声明和语句组织起来，声明的内容对于块来说是本地的，在块结构退出时它们会自动销毁。如下图所示，一个块分为三个部分：声明，处理，异常控制。其中，只有处理部分是必需的。首先程序处理声明部分，然后被声明的内容就可以在执行部分使用，当异常发生时，就可以在异常控制部分中对抛出的异常进行捕捉、处理。

-- 匿名块：声明部分用DECLARE：定义变量、游标、类型等
DECLARE
  -- 变量声明
  v_num NUMBER := 10; -- 声明内部变量
  v_name VARCHAR2(50); 
  
  -- 游标声明
  CURSOR c_emp IS 
    SELECT ename, sal FROM emp WHERE deptno = 10;

-- 处理部分（BEGIN-END）：执行具体操作，这是必需的部分
BEGIN
  -- 使用声明部分定义的变量和游标
  SELECT dname INTO v_name FROM dept WHERE deptno = 10;
  
  DBMS_OUTPUT.PUT_LINE('部门名称: ' || v_name);
  
  -- 循环游标
  FOR emp_rec IN c_emp LOOP
    DBMS_OUTPUT.PUT_LINE('员工: ' || emp_rec.ename || ', 工资: ' || emp_rec.sal);
  END LOOP;

-- 异常控制部分（EXCEPTION）：处理执行过程中出现的异常
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('未找到对应数据');
  WHEN TOO_MANY_ROWS THEN
    DBMS_OUTPUT.PUT_LINE('返回多行数据，不符合预期');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('发生未知错误: ' || SQLERRM);
END;

我们还可以在处理部分和异常控制部分嵌套子块，但声明部分中不可以嵌套子块。不过我们仍可以在声明部分定义本地的子程序，但这样的子程序只能由定义它们的块来调用。

3.变量与常量

PL/SQL允许我们声明常量和变量，但是常量和变量必须是在声明后才可以使用，向前引用 (forward reference) 是不允许的。

3.1 变量声明

变量可以是任何SQL类型，如CHAR，DATE或NUMBER等，也可以是PL/SQL类型，BOOLEAN或BINARY_INTEGER等。声明方法如下：

1
2

part_no NUMBER (4);
in_stock BOOLEAN;

我们还可以用TABLE、VARRAY和RECORD这些复合类型来声明嵌套表、变长数组 (缩写为varray) 和记录。

3.2 变量赋值

我们可以用三种方式为变量赋值，

第一种，直接使用赋值操作符":="：

tax         := price * tax_rate;
valid_id    := FALSE;
bonus       := current_salary * 0.10;
wages       := gross_pay (emp_id,
                        st_hrs,
                        ot_hrs
                       )  - deductions;

第二种，利用数据库中查询的结果为变量赋值：

SELECT sal * 0.10
 INTO bonus
 FROM emp
WHERE empno = emp_id;

第三种，把变量作为一个OUT或IN OUT模式的参数传递给子程序，然后由子程序为其赋值。如下例所示，IN OUT参数可以为被调用的子程序传递初始值然后子程序将更新后的新值返回给调用程序：

DECLARE
 my_sal REAL (7,2) ;
 PROCEDURE adjust_salary  (emp_id INT, salary IN OUT REAL)  IS ...
BEGIN
 SELECT AVG (sal) 
   INTO my_sal
   FROM emp;
 adjust_salary (7788, my_sal) ; -- assigns a new value to my_sal

3.3 声明常量

声明常量跟声明变量类似，但是要加一个CONSTANT关键字，并在声明时为其赋上初始值。下例中，我们声明一个名为credit_limit的常量：

`1`	`credit_limit CONSTANT REAL := 5000.00;`

4. 游标

Oracle 使用工作区 (work area) 来执行SQL语句，工作区是内存中的 “临时操作区”，包含 SQL 执行的所有临时信息（结果集只是其中一部分）。PL/SQL可以让我们使用游标来为工作区命名，并访问存储的信息，既游标是工作区的 “标识和操作接口”。游标的类型有两种：隐式和显式。 PL/SQL会为所有的SQL数据操作声明一个隐式的游标，包括只返回一条记录的查询操作。对于返回多条记录的查询，我们可以显式地声明一个游标来处理每一条记录。如下例：

DECLARE
 CURSOR c1 IS
   SELECT empno, ename, job
     FROM emp
    WHERE deptno = 20;

由多行查询返回的行集合称为结果集 (result set) 。它的大小就是满足我们查询条件的行的个数。如下图所示，显式游标"指向"当前行的记录，这可以让我们的程序每次处理一条记录。

DECLARE
  -- 声明显式游标c1，定义要查询的结果集
  CURSOR c1 IS
    SELECT empno, ename, job
      FROM emp
     WHERE deptno = 20; -- 查询20号部门的员工信息
  
  -- 声明变量用于存储游标提取的每行数据（变量类型需与游标结果集字段匹配）
  v_empno  emp.empno%TYPE;  -- 员工编号，声明变量v_empno，其数据类型与emp表中empno字段的类型完全一致
  v_ename  emp.ename%TYPE;  -- 员工姓名，声明变量v_ename，其数据类型与emp表中ename字段的类型完全一致
  v_job    emp.job%TYPE;    -- 员工职位，声明变量v_sal，其数据类型与emp表中sal字段的类型完全一致
BEGIN
  -- 打开游标，执行查询并生成结果集
  OPEN c1;
  
  -- 循环提取游标中的每行数据，直到所有行处理完毕
  LOOP
    -- 从游标中提取当前行数据到变量中
    FETCH c1 INTO v_empno, v_ename, v_job;
    
    -- 当游标没有更多数据时退出循环（游标属性%NOTFOUND表示未找到数据）
    EXIT WHEN c1%NOTFOUND;
    
    -- 处理当前行数据（此处为示例操作，可根据需求修改）
    DBMS_OUTPUT.PUT_LINE('员工编号: ' || v_empno || 
                       ', 姓名: ' || v_ename || 
                       ', 职位: ' || v_job);
  END LOOP;
  
  -- 关闭游标，释放资源
  CLOSE c1;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('处理出错: ' || SQLERRM);
    -- 若游标处于打开状态，关闭游标
    IF c1%ISOPEN THEN
      CLOSE c1;
    END IF;
END;

多行查询处理有些像文件处理。例如，一个COBOL程序打开一个文件，处理记录，然后关闭文件。同样，一个PL/SQL程序打开一个游标，处理查询出来的行，然后关闭游标。就像文件指针能标记打开文件中的当前位置一样，游标能标记出结构集的当前位置。

我们可以使用OPEN，FETCH和CLOSE语句来控制游标，OPEN用于打开游标并使游标指向结果集的第一行，FETCH会检索当前行的信息并把游标指移向下一行，当最后一行也被处理完后，CLOSE就会关闭游标。

4.1 游标循环结构

在大多需要使用显式游标的情况下，我们都可以用一个简单的游标FOR循环来代替OPEN，FETCH和CLOSE语句。首先，游标FOR循环会隐式地声明一个代表当前行的循环索引 (loop index) 变量。下一步，它会打开游标，反复从结果集中取得数据并放到循环索引的各个域 (field) 中。当所有行都被处理过以后，它就会关闭游标。下面的例子中，游标FOR循环隐式地声明了一个emp_rec记录：

-- 声明部分：定义程序中需要使用的变量、游标等
DECLARE
  -- 声明显式游标c1，定义要查询的结果集
  -- 该游标查询emp表中的员工姓名、工资、雇佣日期和部门编号
  CURSOR c1 IS
    SELECT ename, sal, hiredate, deptno
      FROM emp;
  
  -- 此处省略了可能的其他变量声明（例如salary_total变量的声明）
  -- 示例：salary_total NUMBER := 0;  -- 用于累计总工资的变量
  ...
  
-- 执行部分：程序的核心逻辑，必须存在
BEGIN
  -- 使用FOR循环遍历游标c1的结果集
  -- 循环会自动处理游标打开、提取数据、关闭的过程，无需手动编写OPEN/FETCH/CLOSE
  -- emp_rec是一个记录类型变量，自动与游标c1的字段结构匹配
  FOR emp_rec IN c1 LOOP
    -- 此处省略了对当前行数据的其他处理逻辑
    ...
    
    -- 累加计算所有员工的工资总和
    -- emp_rec.sal表示当前行的工资字段（通过游标提取的当前记录的sal列值）
    salary_total    := salary_total + emp_rec.sal;
  END LOOP;  -- 循环结束，当游标遍历完所有行后自动退出

END;  -- 程序块结束

为了使用每一个查询到的行中的每一个字段，我们可以使用点标志 (dot notation) ，它的作用就像一个域选择器。

4.2 游标变量

游标变量的使用方式和游标类似，但更加灵活，因为它不绑定于某个特定的查询，所以可用于打开任何返回类型相兼容的查询语句。游标变量的结构兼容（结构兼容）指的是不同查询返回的结果集字段数量、字段数据类型、字段顺序必须完全一致，只有满足这一点，同一个游标变量才能先后指向这些查询的结果集并正确处理数据。游标变量是真正的 PL/SQL 变量，我们可以为它赋值，把它传递给子程序。如下例，我们把游标变量作为存储过程 open_cv 的一个参数传进去，程序执行时，可以根据 choice 值的不同，灵活地打开不同的查询内容。

-- 定义一个名为genericcurtyp的游标变量类型
TYPE genericcurtyp IS REF CURSOR; 
-- 声明generic_cv为游标变量（类型是genericcurtyp）
generic_cv genericcurtyp; 
-- 定义存储过程open_cv，参数为游标变量generic_cv和选择条件choice
PROCEDURE open_cv (generic_cv IN OUT genericcurtyp, choice NUMBER)  IS
BEGIN
  -- 根据choice的值，让游标变量指向不同的查询
  IF choice = 1 THEN
    -- 当choice=1时，打开游标变量，指向emp表的所有数据
    OPEN generic_cv FOR
      SELECT * FROM emp;
  ELSIF choice = 2 THEN
    -- 当choice=2时，打开游标变量，指向dept表的所有数据
    OPEN generic_cv FOR
      SELECT * FROM dept;
  ELSIF choice = 3 THEN
    -- 当choice=3时，打开游标变量，指向salgrade表的所有数据
    OPEN generic_cv FOR
      SELECT * FROM salgrade;
  END IF;
  ... -- 省略后续处理逻辑
END;

5. 属性

PL/SQL的变量和游标都有着让我们能够直接引用它们各自的数据类型或结构的属性。数据库字段和表也有类似的属性。"%“是一个属性的指示符。

5.1 %TYPE

TYPE 可以提供一个变量或数据库字段的数据类型，这在声明存放数据库值的变量时是非常有用的。假设我们要声明一个存放表books中的title字段的名称为my_title的变量时，就可以这样使用%TYPE属性：

`1`	`my_title books.title%TYPE;`

这样声明my_title有两个优点，

第一，我们不必知道title具体类型；

第二，如果我们改变了数据库中对该字段的数据类型定义的话，my_title的数据类型会在运行时做出相应的改变。

5.2 %ROWTYPE

在PL/SQL中，记录（一行）用于将逻辑相关数据组织起来。一个记录是由许多相关域的组合。%ROWTYPE属性返回一个记录类型，其数据类型和数据表的数据结构相一致。这样的记录类型可以完全保存从数据表中查询 (SELECT) 或从游标/游标变量取出 (FETCH) 的行记录。行中的字段和记录中的域对应的名称和数据类型都完全一致。下面的例子中，我们声明一个dept_rec的记录。它的域名称和数据类型与表dept中的字段名称和数据类型就完全一样。

1
2

DECLARE
dept_rec   dept%ROWTYPE;   -- declare record variable

我们可以使用”.“来引用记录中的域。

`1`	`my_deptno := dept_rec.deptno;`

假设我们声明了一个用于检索雇员的名字、工资、雇用日期和职称的游标，我们就可以使用%ROWTYPE来声明一个类型相同的记录，如下例：

-- 声明部分：定义游标和变量
DECLARE
  -- 1. 声明显式游标c1
  -- 该游标用于查询emp表中的员工姓名、工资、雇佣日期和职位信息
  CURSOR c1 IS
    SELECT ename,       -- 员工姓名
           sal,         -- 工资
           hiredate,    -- 雇佣日期
           job          -- 职位
      FROM emp;         -- 从员工表emp中查询
  
  -- 2. 声明记录类型变量emp_rec
  -- 使用游标c1的%ROWTYPE属性，使emp_rec的结构与游标c1返回的一行数据完全匹配
  -- 即emp_rec包含4个字段：ename、sal、hiredate、job，类型与游标查询结果一致
  emp_rec  c1%ROWTYPE;

当我们执行语句 FETCH c1 INTO emp_rec; 表 emp 中 ename 字段的值就会赋给 emp_rec 的 ename 域，sal 字段值赋给 sal 域，依此类推。

6. 控制结构

流程控制是PL/SQL对SQL的最重要的扩展。PL/SQL不仅能让我们操作Oracle数据，还能让我们使用条件、循环和顺序控制语句来处理数据，如IF-THEN-ELSE，CASE，FOR-LOOP，WHILE-LOOP，EXIT-WHEN 和 GOTO 等。

6.1 条件控制

我们经常需要根据环境来采取可选择的行动。IF-THEN-ELSE 语句能让我们按照条件来执行一系列语句。IF用于检查条件；THEN决定在条件值为true的情况下执行；ELSE在条件值为false或null的情况才执行。

看一下下面的例子，这个程序用于处理银行事务。在我们从账户3取走$500元之前，它会先确认是否有足够的资金供我们支配。如果余额足够，程序就会更新账户 (accounts) 表的信息，否则的话，程序会向审核 (audit) 表插入一条余额不足的提示信息。

DECLARE
  -- 声明变量：账户余额余额（带两位小数的数字类型）
  acct_balance  NUMBER(11, 2);
  
  -- 声明常量：账户ID（4位数字），值为3
  acct          CONSTANT NUMBER(4)    := 3;
  
  -- 声明常量：扣款金额（带两位小数的数字类型），值为500.00
  debit_amt     CONSTANT NUMBER(5, 2) := 500.00;
BEGIN
  -- 查询指定账户的余额，并对该记录加行锁（FOR UPDATE）
  SELECT bal
    INTO acct_balance
    FROM accounts
   WHERE account_id = acct
     FOR UPDATE OF bal;  -- 锁定bal字段所在行，防止其他事务修改
  
  -- 判断账户余额是否足够扣款
  IF acct_balance >= debit_amt THEN
    -- 余额充足：执行扣款操作
    UPDATE accounts
       SET bal = bal - debit_amt
     WHERE account_id = acct;
  ELSE
    -- 余额不足：插入记录到临时表，记录账户、当前余额和提示信息
    INSERT INTO temp
         VALUES (acct, acct_balance, 'Insufficient funds');
  END IF;
  
  -- 提交事务，确认所有操作
  COMMIT;
END;

要从多个条件值中选出对应的操作时，我们可以使用CASE结构。CASE表达式会计算条件值，然后相应的操作(这个操作有可能是一个完整的PL/SQL块)。

CASE
  WHEN shape = 'square' THEN
    -- 计算正方形面积：边长 × 边长
    area := side * side;
    
  WHEN shape = 'circle' THEN
    -- 计算圆形面积：π × 半径²（包含多行操作）
    BEGIN
      area := pi * (radius * radius);
      DBMS_OUTPUT.put_line('Value is not exact because pi is irrational.');
    END;
    
  WHEN shape = 'rectangle' THEN
    -- 计算长方形面积：长 × 宽
    area := LENGTH * width;
    
  ELSE
    -- 处理未知形状：输出提示并抛出异常
    BEGIN
      DBMS_OUTPUT.put_line('No formula to calculate area of a ' || shape);
      RAISE PROGRAM_ERROR;  -- 抛出程序错误异常
    END;
END CASE;

6.2 循环控制

LOOP 语句能让我们多次执行一系列语句。LOOP循环以关键字LOOP开头，END LOOP结尾。下面语句就是最简单的LOOP循环：

1
2
3

LOOP
-- sequence of statements
END LOOP;

FOR-LOOP 语句可以指定整数的范围，然后范围内每一个数字执行一次。例如，下面的循环将向数据库表插入500个数字和它们的平方根：

FOR num IN 1 .. 500 LOOP
INSERT INTO roots
    VALUES (num, SQRT(num));
END LOOP;

WHILE-LOOP 语句会按照某个条件值执行。每次循环条件值都会被计算一次，如果条件值为true，循环内的语句就会被执行，然后再次回到循环顶部，重新计算条件值。如果条件值为false或是null的话，循环就会停止，控制权被移交给下一语句。下面的例子中，我们要找出第一个工资高于$2500的且行政级别高于雇员编号7499雇员的员工：

DECLARE
  -- 声明变量，类型与emp表对应字段一致
  salary           emp.sal%TYPE     := 0;  -- 存储工资，初始值为0
  mgr_num          emp.mgr%TYPE;          -- 存储经理编号
  last_name        emp.ename%TYPE;        -- 存储员工姓氏
  starting_empno   emp.empno%TYPE   := 7499;  -- 起始员工编号，初始值7499
BEGIN
  -- 查询起始员工的经理编号
  SELECT mgr
    INTO mgr_num
    FROM emp
   WHERE empno = starting_empno;

  -- 循环：当工资小于等于2500时继续执行
  WHILE salary <= 2500 LOOP
    -- 查询当前经理的工资、其经理编号和姓氏
    SELECT sal, mgr, ename
      INTO salary, mgr_num, last_name
      FROM emp
     WHERE empno = mgr_num;
  END LOOP;

  -- 将最终获取的工资和姓氏插入temp表
  INSERT INTO temp
       VALUES (NULL, salary, last_name);
  COMMIT;  -- 提交事务

EXCEPTION
  -- 处理未找到数据的异常
  WHEN NO_DATA_FOUND THEN
    INSERT INTO temp
         VALUES (NULL, NULL, 'Not found');
    COMMIT;  -- 提交事务
END;

EXIT-WHEN 语句可以在需要继续执行循环的情况下退出循环。当遇到EXIT语句时，WHEN子句中的条件值就会被计算。如果条件为true，循环就会结束，控制权交给下一条语句。下面的例子中，在total的值超过2500时，循环就会结束：

LOOP
...
total    := total + salary;
EXIT WHEN total > 25000;   -- exit loop if condition is true
END LOOP;
-- control resumes here

6.3 顺序控制

GOTO语句能让我们无条件地跳转到一个标签。标签就是一个用双尖括号夹起来的未声明标示符，它必须在一个可执行语句或是PL/SQL块之前。执行时，GOTO语句将控制权交给用标签作了标记的语句或块，如下例所示：

-- 判断评分是否大于90
IF rating > 90 THEN
  -- 如果条件成立，跳转到calc_raise标签处执行
  GOTO calc_raise;
END IF;

-- 定义标签calc_raise（跳转目标位置）
<<calc_raise>>
-- 程序执行到此处（无论是否经过跳转）
-- 根据职位计算加薪金额
IF job_title = 'SALESMAN' THEN
  -- 销售人员：按佣金的25%计算加薪
  amount := commission * 0.25;
ELSE
  -- 其他职位：按工资的10%计算加薪
  amount := salary * 0.10;
END IF;

7. 模块化

模块化可以让我们把程序分成多个部分，这样可以把复杂的问题划分开来，更好的解决问题。PL/SQL为我们提供了块、子程序和包三个程序单元来用于模块化处理。

7.1 子程序

子程序有两种，分别是函数和过程。子程序很像一段小型的程序，由可选的声明部分，执行控制部分和可选的异常控制部分组成。

-- 定义存储过程award_bonus，接收员工编号作为输入参数
PROCEDURE award_bonus(emp_id NUMBER) IS
  -- 声明变量：存储计算出的奖金金额（实数类型）
  bonus          REAL;
  
  -- 声明自定义异常：用于处理“佣金缺失（为NULL）”的情况
  comm_missing   EXCEPTION;

BEGIN  -- 执行部分开始
  -- 查询指定员工的佣金，按15%计算奖金并赋值给bonus
  SELECT comm * 0.15
    INTO bonus
    FROM emp
   WHERE empno = emp_id;  -- 按输入的员工编号筛选

  -- 判断计算出的奖金是否为NULL（即原佣金为NULL）
  IF bonus IS NULL THEN
    -- 若奖金为NULL，主动抛出自定义异常comm_missing
    RAISE comm_missing;
  ELSE
    -- 若奖金有效，更新payroll表：给该员工工资增加奖金金额
    UPDATE payroll
       SET pay = pay + bonus
     WHERE empno = emp_id;
  END IF;

EXCEPTION  -- 异常处理部分开始
  -- 捕获自定义异常comm_missing，处理佣金缺失的情况
  WHEN comm_missing THEN
    -- 此处省略异常处理逻辑（如记录日志、提示信息等）
    ...

END award_bonus;  -- 存储过程结束

调用时，这个过程接受一个雇员编号。它使用编号来查询雇员的commission，同时计算出15%的奖金。然后，它检查bonus总值。如果奖金为空，就会抛出异常，否则雇员的工资记录就会被更新。

7.2 包

PL/SQL可以让我们把逻辑相关的类型、变量、游标和子程序放在一个包内，这样更加清楚易理解。包通常有两部分组成：包说明部分和包体部分。包说明部分是应用程序的接口，它"声明"了类型、常量、变量、异常、游标和可以使用的子程序。包体用于实现包说明部分声明的子程序和游标。下面的例子是把两个雇用相关的过程进行打包：

-- 包规范（Package Specification）：定义对外公开的程序单元
CREATE PACKAGE emp_actions AS
  -- 声明"雇佣员工"存储过程（仅定义接口，不包含实现）
  PROCEDURE hire_employee(
    empno  NUMBER,    -- 员工编号
    ename  CHAR,      -- 员工姓名
    ...               -- 其他参数（如职位、工资等）
  );
  
  -- 声明"解雇员工"存储过程（仅定义接口）
  PROCEDURE fire_employee(
    emp_id  NUMBER    -- 员工编号
  );
END emp_actions;
/

-- 包体（Package Body）：实现包规范中声明的程序单元
CREATE PACKAGE BODY emp_actions AS
  -- 实现"雇佣员工"存储过程
  PROCEDURE hire_employee(
    empno  NUMBER, 
    ename  CHAR, 
    ...
  ) IS
  BEGIN
    -- 向emp表插入新员工记录
    INSERT INTO emp
         VALUES (empno, ename, ...);  -- 完整参数列表
  END hire_employee;  -- 结束存储过程实现
  
  -- 实现"解雇员工"存储过程
  PROCEDURE fire_employee(
    emp_id  NUMBER
  ) IS
  BEGIN
    -- 从emp表删除指定员工记录
    DELETE FROM emp
     WHERE empno = emp_id;
  END fire_employee;  -- 结束存储过程实现
END emp_actions;

只有在包说明中声明的内容对应用程序是可见的，而包体中的内容是不可见，无法访问的。包被编译后会存放到数据库中，当我们首次调用包中的子程序时，整个包就会被加载到内存当中，所以，后续的调用不再涉及到磁盘的读取问题。因此，包可以提高效率改善性能。

8. 数据抽象

数据抽象可以让我们把必要的属性提取出来，忽略那些非必须的细节问题，有助于我们更好地解决问题。一旦我们设计好一个数据结构，就可以不再考虑细节内容，而专注于操作这个数据结构的算法问题的研究。在 PL/SQL 中，通过TABLE和VARRAY这两种 “集合类型” 的定义语法，可以创建出三种具体的集合结构 ——索引表（Index-by Table）、嵌套表（Nested Table）、变长数组（VARRAY）。

8.1 集合

是类型相同的元素有序组合。在集合中，每个元素都有唯一一个能够确定该元素在集合中位置的下标索引。下面是嵌套表的一个例子：

DECLARE
  -- 声明一个集合类型staff，元素类型为employee（假设是一个记录类型或表%ROWTYPE）
  -- 这是一个嵌套表类型（无INDEX BY子句），用于存储多个employee类型的数据
  TYPE staff IS TABLE OF employee;
  
  -- 声明一个employee类型的变量staffer，用于存储单个员工信息
  staffer  employee;
  
  -- 声明一个函数new_hires，接收雇佣日期参数，返回staff类型的集合
  FUNCTION new_hires(hiredate DATE) RETURN staff IS
  BEGIN
    -- 函数实现逻辑（省略）：
    -- 通常会查询指定日期之后雇佣的员工，返回包含这些员工信息的staff集合
    ...
  END new_hires; -- 结束函数定义

BEGIN
  -- 调用new_hires函数获取1998年11月10日之后雇佣的员工集合，
  -- 并通过下标(5)获取集合中的第5个元素，赋值给staffer变量
  staffer := new_hires('10-NOV-98')(5);
  
  -- 后续处理逻辑（省略）
  ...

END; -- 结束匿名块

集合有些像三代语言中的数组，并且可以作为参数进行传递。

8.2 记录

我们知道，可以使用%ROWTYPE属性获取数据表中一行的记录类型，其实我们还可以定义自己的记录类型。记录包含名称不可重复的域，域可以有不同的数据类型。假设我们设计了一个雇员记录类型，其中有名字、工资和雇用日期，这些项虽然类型不同，但逻辑上都是相关联的。看一下下面的例子：

-- 1. 定义时间记录类型：用于存储小时和分钟
TYPE timerec IS RECORD(
  hours    SMALLINT,  -- 小时（短整数类型）
  minutes  SMALLINT   -- 分钟（短整数类型）
);

-- 2. 定义会议记录类型：包含日期、时长、地点和目的
TYPE meetingtyp IS RECORD(
  date_held  DATE,               -- 会议举行日期
  duration   timerec,            -- 会议时长（嵌套timerec类型的记录）
  location   VARCHAR2(20),       -- 会议地点（最大20字符）
  purpose    VARCHAR2(50)        -- 会议目的（最大50字符）
);

这里要注意的是，记录里可以嵌套记录类型。也就是说，记录本身也可以作为另一个记录的组成部分。

8.3 对象类型

PL/SQL中的面向对象编程是基于对象类型的。对象类型把数据和用于数据操作的函数和过程封装起来。其中，对象类型中的变量称为属性，函数和过程称为方法。对象类型是把大系统划分成多个逻辑实体来降低问题的复杂度，这就能使我们创建模块化、可维护和重用性好的组件了。我们在用CREATE TABLE定义对象类型的时候，常常是创建一个对真实世界对象的抽象的模板。如下面的银行账户例子中显示，模板只指定了应用程序的环境中会使用到的属性和方法：

-- 创建一个名为bank_account的对象类型
-- 包含账户属性和操作账户的成员方法
CREATE TYPE bank_account AS OBJECT(
  -- 账户属性（数据字段）
  acct_number   INTEGER(5),        -- 账户编号（5位整数）
  balance       REAL,              -- 账户余额（实数类型）
  status        VARCHAR2(10),      -- 账户状态（如'ACTIVE'、'CLOSED'等，最大10字符）
  
  -- 成员方法声明（操作账户的行为）
  MEMBER PROCEDURE OPEN(amount IN REAL),                  -- 开户（初始存款）
  MEMBER PROCEDURE verify_acct(num IN INTEGER),           -- 验证账户有效性
  MEMBER PROCEDURE CLOSE(num IN INTEGER, amount OUT REAL),-- 关闭账户（返回余额）
  MEMBER PROCEDURE deposit(num IN INTEGER, amount IN REAL),-- 存款
  MEMBER PROCEDURE withdraw(num IN INTEGER, amount IN REAL),-- 取款
  MEMBER FUNCTION curr_bal(num IN INTEGER) RETURN REAL    -- 查询当前余额
);

在 PL/SQL 中，当已经创建了bank_account对象类型后，可以通过以下步骤创建对象实例并使用其成员方法：

步骤 1：创建对象类型体（实现成员方法）

首先需要为bank_account对象类型创建类型体（Type Body），实现所有声明的成员方法（类似类的方法实现）：

-- 创建对象类型体，实现bank_account的所有成员方法
CREATE TYPE BODY bank_account AS
  -- 实现开户方法：初始化账户余额和状态
  MEMBER PROCEDURE OPEN(amount IN REAL) IS
  BEGIN
    self.balance := amount;  -- self代表当前对象实例
    self.status := 'ACTIVE'; -- 标记账户为"活跃"
  END OPEN;

  -- 实现账户验证方法：检查账户编号是否匹配
  MEMBER PROCEDURE verify_acct(num IN INTEGER) IS
  BEGIN
    IF self.acct_number != num THEN
      RAISE_APPLICATION_ERROR(-20001, '账户编号不匹配');
    END IF;
  END verify_acct;

  -- 实现关闭账户方法：返回余额并标记状态为关闭
  MEMBER PROCEDURE CLOSE(num IN INTEGER, amount OUT REAL) IS
  BEGIN
    self.verify_acct(num);  -- 先验证账户
    amount := self.balance; -- 输出当前余额
    self.balance := 0;
    self.status := 'CLOSED';-- 标记账户为"关闭"
  END CLOSE;

  -- 实现存款方法：增加账户余额
  MEMBER PROCEDURE deposit(num IN INTEGER, amount IN REAL) IS
  BEGIN
    self.verify_acct(num);
    IF amount <= 0 THEN
      RAISE_APPLICATION_ERROR(-20002, '存款金额必须为正数');
    END IF;
    self.balance := self.balance + amount;
  END deposit;

  -- 实现取款方法：减少账户余额
  MEMBER PROCEDURE withdraw(num IN INTEGER, amount IN REAL) IS
  BEGIN
    self.verify_acct(num);
    IF amount <= 0 THEN
      RAISE_APPLICATION_ERROR(-20003, '取款金额必须为正数');
    ELSIF self.balance < amount THEN
      RAISE_APPLICATION_ERROR(-20004, '余额不足');
    END IF;
    self.balance := self.balance - amount;
  END withdraw;

  -- 实现查询当前余额方法
  MEMBER FUNCTION curr_bal(num IN INTEGER) RETURN REAL IS
  BEGIN
    self.verify_acct(num);
    RETURN self.balance;
  END curr_bal;
END;

步骤 2：创建对象实例（实例化对象）

通过对象类型名(属性值)的方式创建对象实例，然后可以调用其成员方法：

DECLARE
  -- 声明对象变量（类似其他语言的"对象引用"）
  my_acct  bank_account;  -- 账户1
  your_acct bank_account; -- 账户2
  close_balance REAL;     -- 用于接收关闭账户时的余额
BEGIN
  -- 1. 创建对象实例（初始化属性值）
  my_acct := bank_account(
    acct_number => 10001,  -- 账户编号,明确将10001传给acct_number属性
    balance => 0,          -- 初始余额（后续通过OPEN方法设置）
    status => 'INACTIVE'   -- 初始状态（未激活）
  );

  your_acct := bank_account(
    acct_number => 10002,
    balance => 0,
    status => 'INACTIVE'
  );

  -- 2. 调用对象的成员方法（使用"对象.方法名"语法）
  -- 开户（存入初始金额）
  my_acct.OPEN(1000);  -- 给my_acct存入1000元，状态变为ACTIVE
  your_acct.OPEN(500); -- 给your_acct存入500元

  -- 存款
  my_acct.deposit(10001, 500);  -- 给10001号账户存500元（当前余额1500）

  -- 取款
  my_acct.withdraw(10001, 300); -- 从10001号账户取300元（当前余额1200）

  -- 查询余额
  DBMS_OUTPUT.PUT_LINE('我的账户余额：' || my_acct.curr_bal(10001));  -- 输出1200

  -- 关闭账户
  my_acct.CLOSE(10001, close_balance);
  DBMS_OUTPUT.PUT_LINE('关闭账户时的余额：' || close_balance);  -- 输出1200
END;

运行时，当数据结构被赋值之后，我们就可以创建抽象的银行账户了。我们可以按照需求创建任意个实例(称为对象)。每个对象都有账号，余额和状态。

9. 信息隐藏

有了信息隐藏，我们就可以只关心给定的设计级别的算法和数据结构设计。信息隐藏能把高阶的设计决定从频繁改变的低阶设计细节分离出来。

9.1 算法

我们可以通过自顶而下 (top-down) 的设计来实现算法隐藏。一旦我们明确了低阶过程的实现目的并定义好相应的接口说明，就可以忽略实现细节部分。例如，我们只需要知道将一个雇员的工资金额传递给过程 raise_salary 就可以提高该雇员的工资。任何对 raise_salary 方法的变动，对于应用程序来说，都是透明的。

9.2 数据结构

我们可以通过数据封装来实现信息隐藏。开发一组操作数据结构的工具子程序，就可以让使用它的用户和开发人员分离。这样一来，开发人员只需了解如何使用这些子程序来操作数据，并不需要知道数据真正的含义。使用PL/SQL包，我们就可以指定哪些子程序是公有哪些是私有，更好的提供封装，简化维护。

10. 错误控制

PL/SQL能够轻松的发现并处理预定义和用户定义的错误条件(即异常)。错误发生时，异常就会被抛出。也就是说，正常的执行会终止，程序控制权将交给PL/SQL块或子程序的异常处理部分。为控制被抛出的异常，我们需要单独编写异常控制句柄(即异常控制程序)。

预定义异常会被系统隐式地抛出，例如，用一个数字除以零，PL/SQL就会自动抛出预定义异常ZERO_DIVIDE。对于用户自定义异常，必须由我们显式地使用RAISE语句抛出。

我们可以在任何PL/SQL块或子程序的声明部分定义自己的异常。在执行部分，我们检查那些需要特别对待的条件，如果错误条件满足，就可以使用 RAISE抛出异常。在下面的例子中，我们要计算售货员的奖金。奖金的多少取决于他的工资(salary)和佣金(commission)。所以，如果佣金为空的话，我们就要抛出异常comm_missing。

DECLARE
 ...
 comm_missing   EXCEPTION;   -- declare exception
BEGIN
 ...
 IF commission IS NULL THEN
   RAISE comm_missing;   -- raise exception
 END IF; bonus    := (salary * 0.10) +(commission * 0.15);
EXCEPTION
 WHEN comm_missing THEN   -- process the exception
   ...
END;

小结

在数据库时代，特别是Oracle数据库时代，PL/SQL已经成为了数据库应用程序开发的标准语言。