第二章 PL/SQL架构

一、PL/SQL引擎

PL/SQL 的编码和运行时系统是一项技术，而不是一个独立的产品。可以把这项技术想象成一个能够编译并运行PL/SQL块和子程序的引擎。这个引擎可以安装在Oracle服务器上或安装在Oracle Forms，Oracle Reports这样的开发工具中。所以，PL/SQL可以在两种环境中存在：

• Oracle数据库服务器
• Oracle开发工具这两种环境是独立的

PL/SQL虽被绑定到Oracle服务器上，但在某些工具中是无法使用的。在这两种环境下，PL/SQL引擎都能接受有效的 PL/SQL 块或子程序。下图是PL/SQL引擎处理匿名块的过程，引擎会处理过程化语句，而把SQL语句发送给Oracle服务器端的SQL语句执行程序(SQL Statement Executor)来处理。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

-- PL/SQL匿名块示例
DECLARE
    -- 声明变量（过程化部分，由PL/SQL引擎处理）
    v_emp_count NUMBER;
    v_dept_name VARCHAR2(100) := '研发部';
BEGIN
    -- SQL语句（发送到Oracle服务器的SQL执行程序处理）
    SELECT COUNT(*) INTO v_emp_count 
    FROM employees 
    WHERE department = v_dept_name;
    
    -- 过程化逻辑（由PL/SQL引擎处理）
    IF v_emp_count > 50 THEN
        DBMS_OUTPUT.PUT_LINE('部门人数过多，当前人数: ' || v_emp_count);
    ELSE
        DBMS_OUTPUT.PUT_LINE('部门人数正常，当前人数: ' || v_emp_count);
    END IF;
    
    -- 另一SQL语句（再次发送到SQL执行程序）
    UPDATE departments 
    SET last_check_date = SYSDATE 
    WHERE department_name = v_dept_name;
    
EXCEPTION
    -- 异常处理（过程化部分，由PL/SQL引擎处理）
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('未找到指定部门');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('发生错误: ' || SQLERRM);
END;

1. 在Oracle数据库中

缺乏本地PL/SQL引擎的应用开发工具就必须依赖于Oracle来处理PL/SQL块和子程序。Oracle服务器除了能够处理SQL语句外，还会处理PL/SQL块和子程序，它会将块与子程序传给它本地的PL/SQL引擎。

1.1 匿名块

匿名PL/SQL块能被嵌到Oracle预编译程序(Oracle Precompiler)或是OCI程序中（Oracle Call Interface Oracle 调用接口）。运行的时候，不含PL/SQL引擎的程序会把这些块发送到Oracle服务器编译并执行。

1.2 存储子程序

子程序可以独立编译并存储在Oracle数据库。使用CREATE语句显式创建的子程序就是一个"存储"子程序。一旦编译并保存到数据词典中（Oracle 记录所有对象信息的系统表），它就成了一个模式对象(schema object)，和表、索引、视图是同一类 “数据库里的东西”，可以被许多连到数据库的应用程序调用，不是临时代码 —— 就像把工具存进工具箱，下次用直接拿，不用重新写。存储子程序的本质是 “能独立编译、并存储在 Oracle 数据库里的 PL/SQL 程序单元”。存储子程序包含哪些核心类型？存储过程（Procedure）只是其中一种，另一种核心类型是存储函数（Function），二者是存储子程序的两大主要分支，此外还包括打包子程序、独立子程序等。

通过代码片段，能更清楚存储过程和存储函数的区别（也能看到它们都是 “存储子程序”）：

例子 1：存储过程（Procedure）—— 执行逻辑，无返回值

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

-- 创建一个“新增部门”的独立存储过程（属于存储子程序）
CREATE OR REPLACE PROCEDURE create_dept(
  p_dept_name VARCHAR2,  -- 输入参数：部门名称
  p_location  VARCHAR2   -- 输入参数：部门位置
) AS
BEGIN
  -- 核心逻辑：插入一条部门数据
  INSERT INTO dept (deptno, dname, loc) 
  VALUES (dept_seq.NEXTVAL, p_dept_name, p_location);
  
  COMMIT;
  DBMS_OUTPUT.PUT_LINE('部门创建成功！');
END create_dept;
/

例子 2：存储函数（Function）—— 计算结果，有返回值

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

-- 创建一个“查询部门人数”的独立存储函数（也属于存储子程序）
CREATE OR REPLACE FUNCTION get_dept_emp_count(
  p_deptno NUMBER  -- 输入参数：部门编号
) RETURN NUMBER AS  -- 必须声明返回值类型（这里返回“人数”，是NUMBER）
  v_count NUMBER;  -- 局部变量，存计算结果
BEGIN
  -- 核心逻辑：查询该部门的员工数量
  SELECT COUNT(*) INTO v_count 
  FROM emp 
  WHERE deptno = p_deptno;
  
  RETURN v_count;  -- 必须返回结果
END get_dept_emp_count;
/

存储子程序 = 存储过程 + 存储函数 + 其他符合 “存储在数据库、可调用” 的 PL/SQL 单元，存储过程只是存储子程序的一种，二者是 “大类” 和 “具体类型” 的关系。

存储子程序根据“定义位置”可划分为3类，核心差异体现在是否能被外部程序调用，具体分类及特征如下：

打包子程序（Packaged Subprogram）

• 定义位置：在PL/SQL包（Package）内部（包规范中声明，包体中实现）。
• 核心特征（可调用性）：支持外部调用，需通过“包名.子程序名”的格式指定调用路径（明确归属）。
• 示例：若在包hr_utils中定义子程序add_emp()，外部程序需通过hr_utils.add_emp()调用（如其他子程序、匿名块均可调用）。

独立子程序（Standalone Subprogram）

• 定义位置：不依赖任何包或其他子程序，单独在数据库中创建（直接通过CREATE PROCEDURE/CREATE FUNCTION语句定义）。
• 核心特征（可调用性）：支持外部调用，调用时无需额外指定路径，直接使用“子程序名”即可（只要调用者有执行权限）。
• 示例：单独创建的子程序create_dept()，外部程序可直接通过create_dept()调用（无需关联其他对象）。

本地子程序（Local Subprogram）

• 定义位置：嵌套在“另一个子程序”（如过程/函数）或“PL/SQL匿名块”内部，作为内部逻辑的一部分。
• 核心特征（可调用性）：仅支持本地调用，不支持外部调用——作用域仅限于“定义它的父程序/父块”，外部程序无法感知或调用。
• 示例：在“计算薪资”的子程序（如calc_salary()）内部，定义一个“计算个税”的小子程序（如calc_tax()）：
  • calc_tax()仅能在calc_salary()内部被调用（用于辅助计算薪资）；
  • 外部程序（如其他子程序、匿名块）无法直接调用calc_tax()（因超出其作用域）。

存储过程执行效率高，耗内存少，应用集成，安全性好。例如，我们设计出一套存储过程和函数时，应用程序就可以调用这些函数和方法，这样就能避免大量的冗余代码提高效能。

我们可以从数据库触发器、其他存储子程序、Oracle预编译程序、OCI程序或是SQLPlus等，调用存储子程序。例如，我们可以像下面这样从SQLPlus中调用独立子程序create_dept：

1

SQL> CALL create_dept('FINANCE', 'NEW YORK');

子程序以分析、编译过的形式保存在数据库中。所以，在被调用时，它们会被立即加载并传递到PL/SQL引擎。并且，它们还会利用共享内存，这样，每次只要有一个子程序需要被加载到内存，就能被多个用户调用执行。

1.3 数据库触发器

数据库触发器是与数据库中某个数据表、视图或事件相关联的存储子程序。举一个例子，我们可以让Oracle数据库在INSERT、UPDATE或 DELETE表达式影响一个表之前或之后来自动激活一个触发器。触发器的用途之一就是审核数据修改。例如，下面的表级(table-level)触发器会在emp表的salaries字段更新后被激活。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

-- 创建触发器：当emp表的sal（薪资）字段被更新后，自动记录审计日志到emp_audit表
CREATE OR REPLACE TRIGGER audit_sal  -- "OR REPLACE"避免重复创建时报错，建议添加
AFTER UPDATE OF sal                  -- 触发时机（更新后）+ 触发条件（仅当sal字段被修改时）
ON emp                               -- 触发器所属的表（监控emp表的sal字段更新）
FOR EACH ROW                         -- 行级触发器：每更新一行emp表数据，就触发一次
BEGIN
    -- 向审计表emp_audit插入更新前后的关键信息（需确保emp_audit表已存在）
    INSERT INTO emp_audit (
        audit_id,        -- 审计记录ID（唯一标识）
        empno,           -- 被更新员工的编号（关联emp表的主键）
        old_sal,         -- 更新前的薪资
        new_sal,         -- 更新后的薪资
        update_time,     -- 薪资更新时间
        update_user      -- 执行更新操作的数据库用户
    ) VALUES (
        emp_audit_seq.NEXTVAL,  -- 通过序列生成唯一ID（需提前创建序列）
        :OLD.empno,             -- ":OLD"表示更新前的行数据，取原员工编号
        :OLD.sal,               -- 取更新前的薪资
        :NEW.sal,               -- ":NEW"表示更新后的行数据，取新薪资
        SYSDATE,                -- 系统当前时间（自动记录更新时间）
        USER                    -- 自动获取当前执行更新操作的数据库用户名
    );
END audit_sal;  -- 触发器名称（需与CREATE TRIGGER后的名称一致）
/  -- 执行触发器创建（Oracle中需用"/"结束PL/SQL块）

触发器执行部分可以包括过程语句和SQL数据操作语句。除了表级触发器以外，还包含替代触发器(instead-of triggers for views)和系统触发器(system-event trigger)。

2. 在Oracle工具中

在包含PL/SQL引擎的条件下，应用开发工具就能够处理PL/SQL块和子程序。开发工具会把块传给它的本地PL/SQL引擎。引擎会在应用程序段执行所有的过程语句，只把SQL语句发送给Oracle。因此，大多部分工作会在应用程序端完成，而不是在服务器端。进一步说，如果块中不包含任何 SQL语句的话，这个引擎会在应用程序端执行全部的代码。

二、PL/SQL的优势

PL/SQL是一个可移植、高效的事务处理语言：

• 支持SQL
• 支持面向对象编程
• 良好的性能
• 效率高
• 可移植
• 与Oracle集成

1. 高度安全

1.1 SQL的支持

SQL因为它的灵活、强大和易学，已经成为标准的数据库语言。只要几个像SELECT、INSERT、UPDATE和DELETE这样简单的命令就能轻松地操作关系数据库中的数据。SQL是非过程化的，这就是说我们可以决定做我们想做的，但不能决定如何做。Oracle会决定处理我们请求的最佳方案。

PL/SQL能让我们使用所有的SQL数据操作，游标控制和事务控制命令；也可以使用所有的SQL函数，操作符和伪列。所以，我们可以灵活安全地操作Oracle数据。PL/SQL完全支持SQL数据类型，这就减少了我们的应用程序和数据库间数据传递时的类型转换。

PL/SQL也支持动态SQL语句，这样能够让我们的应用程序更加灵活通用。程序可以在运行时处理SQL数据定义、数据控制和会话控制语句。

1.2 面向对象支持

对象类型是理想的面向对象建模工具，它能帮助我们创建复杂的应用程序。除了能创建模块化，易维护和重用性高的软件组件外，对象类型还可以让不同开发组的程序员并发地开发组件。

对象类型通过对数据操作的封装，把数据维护代码从SQL脚本和PL/SQL块中提取出来，放到独立的方法中去。同样，对象类型也可以隐藏实现，这样我们就在不影响客户端程序的情况下改变实现细节。

此外，真实世界的复杂实体和关系也能够映射到对象类型中去。这样我们的程序就能更好将模拟的内容反映出来。

1.3 良好的性能

如果没有PL/SQL的话，Oracle就必须每次接收一条SQL语句，然后处理。每条SQL语句都会调用一次Oracle，这就造成很大的运行开销。在网络环境中，这种现象就更加明显。如下图所示，如果应用程序与数据库之间交互频繁，那么就可以在向Oracle发送SQL语句之前使用 PL/SQL 块和子程序把SQL语句组合起来。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

-- 应用程序需执行3次独立INSERT，每次都要与Oracle建立交互
-- 第1次调用：新增研发部员工
INSERT INTO emp (empno, ename, deptno, sal) 
VALUES (1001, '张三', 10, 8000);

-- 第2次调用：新增财务部员工
INSERT INTO emp (empno, ename, deptno, sal) 
VALUES (1002, '李四', 20, 7500);

-- 第3次调用：新增人事部员工
INSERT INTO emp (empno, ename, deptno, sal) 
VALUES (1003, '王五', 30, 7000);

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

-- 1. 先在Oracle中创建存储子程序（仅需创建1次）
CREATE OR REPLACE PROCEDURE batch_add_emp(
    p_empno1 NUMBER, p_ename1 VARCHAR2, p_deptno1 NUMBER, p_sal1 NUMBER,
    p_empno2 NUMBER, p_ename2 VARCHAR2, p_deptno2 NUMBER, p_sal2 NUMBER,
    p_empno3 NUMBER, p_ename3 VARCHAR2, p_deptno3 NUMBER, p_sal3 NUMBER
) AS
BEGIN
    -- 内部组合3条SQL，逻辑与匿名块一致
    INSERT INTO emp (empno, ename, deptno, sal) VALUES (p_empno1, p_ename1, p_deptno1, p_sal1);
    INSERT INTO emp (empno, ename, deptno, sal) VALUES (p_empno2, p_ename2, p_deptno2, p_sal2);
    INSERT INTO emp (empno, ename, deptno, sal) VALUES (p_empno3, p_ename3, p_deptno3, p_sal3);
    
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE; -- 抛出异常，让应用感知失败
END batch_add_emp;
/

-- 2. 应用程序调用时，仅需1次交互（传参数即可）
CALL batch_add_emp(1001, '张三', 10, 8000, 
                  1002, '李四', 20, 7500, 
                  1003, '王五', 30, 7000);

有了PL/SQL，整块的语句就可以一次传递给Oracle，这样就能减少应用程序和Oracle的通信，减少网络开销，如上图所示，如果我们的应用程序与数据库的交互操作较多，那么就可以用PL/SQL块和子程序将SQL语句组织起来一次性地发送给Oracle执行。

PL/SQL块和子程序能够在编译成可执行的形式存放起来，所以调用存储过程是快速和高效的。而且，存储过程是在服务器端执行的，减少网络流量改善响应时间。可执行的代码会被自动地放到缓存然后在多个用户间共享。

我们也可以为Oracle工具提供PL/SQL的过程处理功能来改善性能。这样我们就可以直接利用工具来进行计算而不用调用Oracle服务器。这样会节省大量的时间和网络开销。

1.4 高效性

对于像Oracle Forms和Oracle Reports这样的非过程化工具，在加入了PL/SQL功能后，我们也可以使用熟悉的过程语句来建立应用程序。例如，我们可以在Oracle Form触发器中使用整块整块的PL/SQL块，而不必多次调用触发器，宏等。所有环境中的PL/SQL都是一样的。一旦我们掌握了PL/SQL，那么我们就可以在任何支持PL/SQL的工具中使用它。

1.5 可移植性

用PL/SQL编写的应用程序都可移植到Oracle运行的操作系统和平台。换句话说，PL/SQL程序可以在任何Oracle能够运行的地方而运行，因此，我们不必为每一个新环境定制一套新的PL/SQL程序。

1.6 与SQL紧密结合

PL/SQL和SQL语言是紧密结合的。PL/SQL支持所有的SQL数据类型和NULL。这样一来，我们操作Oracle数据就变得方便快捷。% TYPE和%ROWTYPE属性进一步的拉近PL/SQL和SQL的关系。例如，我们可以通过%TYPE属性，在已定义的数据库字段的基础上声明同类型的变量。如果数据库定义发生改变的话，在下一次编译或运行时，变量的声明类型也会随之变化。这就减少了代码维护的开销，使程序自动地适应于新的业务需求。

1.7 高度安全

PL/SQL存储过程能使客户端和服务器端的逻辑分离，避免让客户端操作敏感的Oracle数据。用PL/SQL编写的触发器可以有选择性的允许应用程序更新数据，并可以根据已有的内容来审核用户的插入操作。

另外，我们还可以让用户只通过调用定义者权限的存储过程，来严格控制用户对Oracle数据的访问。例如，我们可以授权用户来调用更新数据表的存储过程，但不授权他们直接访问数据表的权限。

小结

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