PL/SQL

PL/SQL（Procedural Language/Structured Query Language） 是 Oracle 数据库 的专有过程化扩展语言，将 SQL 的数据操作能力与过程化逻辑（变量、循环、异常处理）结合。它用于编写高效的数据库端程序，是 Oracle 生态中存储业务逻辑的核心工具。

---

核心特性与设计目标

1. 紧密集成 SQL

   • 直接在代码中嵌入 SELECT/INSERT/UPDATE/DELETE 语句，无转换开销。

   • 支持批量 SQL 操作（BULK COLLECT, FORALL），大幅提升性能。

2. 服务器端执行

   • 代码在数据库服务器运行（存储过程、函数、触发器等），减少网络传输。

   • 自动事务控制（通过 COMMIT/ROLLBACK）。

3. 模块化与复用

   • 封装为命名程序单元（过程、函数、包、触发器）。

   • 包（PACKAGE）分离规范与实现，支持代码组织和权限控制。

---

关键语法与组件

1. 基础结构

sql

 

Copy

 

Download

DECLARE
  -- 变量声明
  v_emp_name  employees.last_name%TYPE; -- 使用表字段类型
  v_salary    NUMBER(10,2) := 0;        -- 显式类型+初始化
BEGIN
  -- 执行逻辑
  SELECT last_name, salary 
  INTO v_emp_name, v_salary   -- 查询结果存入变量
  FROM employees 
  WHERE employee_id = 100;

  -- 条件判断
  IF v_salary > 10000 THEN
    DBMS_OUTPUT.PUT_LINE(v_emp_name || ' 是高薪员工');
  ELSE
    DBMS_OUTPUT.PUT_LINE(v_emp_name || ' 需调薪');
  END IF;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('员工不存在'); -- 异常处理
END;

2. 核心程序单元

类型	作用	示例片段
存储过程	执行操作（无返回值）	CREATE PROCEDURE raise_salary(...)
函数	计算并返回单值	CREATE FUNCTION calc_bonus(...) RETURN NUMBER
包	组合相关过程/函数/变量	CREATE PACKAGE emp_utils AS ... END;
触发器	响应表事件自动执行	CREATE TRIGGER audit_emp BEFORE DELETE ON employees

3. 高效数据处理

sql

 

Copy

 

Download

-- 批量提取数据到集合
DECLARE
  TYPE t_emp_tab IS TABLE OF employees%ROWTYPE;
  v_emps t_emp_tab;
BEGIN
  SELECT * BULK COLLECT INTO v_emps  -- 一次性加载多行
  FROM employees 
  WHERE department_id = 50;

  -- 批量更新（跳过逐行提交）
  FORALL i IN 1..v_emps.COUNT
    UPDATE employees 
    SET salary = v_emps(i).salary * 1.1
    WHERE employee_id = v_emps(i).employee_id;
  COMMIT;
END;

4. 错误处理

sql

 

Copy

 

Download

BEGIN
  INSERT INTO orders VALUES (...);
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN  -- 捕获主键冲突
    log_error('订单号重复');  -- 自定义日志
  WHEN OTHERS THEN            -- 捕获所有其他错误
    DBMS_OUTPUT.PUT_LINE('错误代码: ' || SQLCODE);
    RAISE;  -- 重新抛出异常
END;

---

应用场景

1. 数据密集型操作

   • ETL 数据迁移、复杂报表生成。

   sql

    

   Copy

    

   Download

   CREATE PROCEDURE generate_monthly_report AS
   BEGIN
     -- 1. 清理临时表
     -- 2. 多表关联计算指标
     -- 3. 汇总结果写入报表表
   END;

2. 业务规则强制

   • 通过触发器实施审计、数据校验。

   sql

    

   Copy

    

   Download

   CREATE TRIGGER prevent_salary_decrease
   BEFORE UPDATE ON employees
   FOR EACH ROW
   BEGIN
     IF :NEW.salary < :OLD.salary THEN
       RAISE_APPLICATION_ERROR(-20001, '薪资不可降低');
     END IF;
   END;

3. API 封装

   • 用包暴露安全数据访问接口。

   sql

    

   Copy

    

   Download

   CREATE PACKAGE employee_api AS
     FUNCTION get_salary(emp_id NUMBER) RETURN NUMBER;
     PROCEDURE update_contact(emp_id NUMBER, phone VARCHAR2);
   END;

---

性能优化技巧

1. 避免逐行处理

   • 用 BULK COLLECT + FORALL 替代循环内单行 DML。

2. 绑定变量防硬解析

   sql

    

   Copy

    

   Download

   -- 反例（硬解析）
   EXECUTE IMMEDIATE 'SELECT * FROM emp WHERE id = ' || v_id;
   
   -- 正例（绑定变量）
   EXECUTE IMMEDIATE 'SELECT * FROM emp WHERE id = :1' INTO v_emp USING v_id;

3. 显式定义游标

   • 复杂查询用 CURSOR 提高可读性与控制力。

4. 使用 NOCOPY 参数模式

   • 减少大型集合参数传递的开销。

---

与 T-SQL 对比

特性	PL/SQL (Oracle)	T-SQL (SQL Server)
变量前缀	v_ 或无名（推荐）	@
包支持	有 (PACKAGE)	无（用模块化存储过程替代）
错误码	命名异常 (NO_DATA_FOUND)	数字错误码 (@@ERROR)
集合操作	强大（嵌套表、VARRAY）	较弱（临时表、表变量）

---

开发工具

1. SQL Developer：Oracle 官方免费 IDE（调试、性能分析）。

2. Toad for Oracle：第三方工具（企业级功能）。

3. PL/SQL Developer：轻量级高效客户端。

学习资源：

• Oracle PL/SQL 文档

• 书籍：《Oracle PL/SQL 编程》（Steven Feuerstein）

• 练习：LiveSQL（在线沙盒）

总结：PL/SQL 是 Oracle 生态中处理复杂数据库逻辑的高性能解决方案，尤其适合：

• 减少应用层与数据库的交互次数

• 强制实施数据一致性规则

• 封装敏感数据操作
  尽管云时代部分逻辑向应用层迁移，其在核心事务系统中的价值不可替代。