Oracle — PL-SQL

介绍

     Oracle PL/SQL是专为Oracle数据库设计的过程化编程语言，深度融合SQL语句与结构化编程逻辑，旨在高效处理复杂数据操作与业务规则。其核心特征为“块结构”，程序由声明、执行、异常处理三部分组成，支持模块化开发，显著提升代码复用性和可维护性。PL/SQL通过预编译机制将代码块整体发送至数据库执行，大幅减少网络交互频次，尤其擅长批量数据处理，可借助FORALL、BULK COLLECT等特性优化事务性能。开发者可创建存储过程、函数、触发器及程序包，将业务逻辑封装于数据库层，实现数据计算下沉，保障事务一致性与安全性。异常处理框架支持自定义错误捕获与响应，增强程序健壮性。游标机制提供灵活的数据逐行处理能力，动态SQL则支持运行时语句构造，适应复杂逻辑场景。随着版本迭代，PL/SQL持续集成JSON解析、面向对象编程等现代特性，并与Java、Python等语言深度互通，巩固其在企业级应用开发中的地位，成为Oracle生态中处理高并发事务、构建金融级系统的关键技术栈。

---

PL/SQL 块结构

Oracle PL/SQL 块是程序基本单元，包含声明（DECLARE）、执行（BEGIN-END）、异常处理（EXCEPTION）三部分，支持变量定义、逻辑控制及错误处理，用于封装数据库操作和业务逻辑。匿名块可直接执行，存储过程等具名块可重复调用。

PL/SQL采用块结构，分为：

1. 声明部分（DECLARE）：定义变量、游标、异常等（可选）。
2. 执行部分（BEGIN ... END）：包含主要逻辑代码。
3. 异常处理（EXCEPTION）：处理运行时错误（可选）。

DECLARE
  v_name VARCHAR2(50) := 'Alice';
BEGIN
  DBMS_OUTPUT.PUT_LINE('Hello, ' || v_name);
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error occurred');
END;

---

变量与数据类型

Oracle PL/SQL变量用于存储数据，需声明数据类型，包括标量（如NUMBER、VARCHAR2）、复合（记录、集合）、引用及LOB类型，支持%TYPE继承字段类型，确保数据一致性与灵活性。

• 标量类型：NUMBER, VARCHAR2, DATE, BOOLEAN等。
• 复合类型：
  • 记录（RECORD）：结构体类型。

TYPE t_emp IS RECORD (id NUMBER, name VARCHAR2(100));

• 集合：包括关联数组（INDEX BY）、嵌套表（TABLE）、可变数组（VARRAY）。
• 引用类型：%TYPE（字段类型）和%ROWTYPE（整行类型）。

  v_emp_id employees.employee_id%TYPE; -- 引用表字段类型
  v_emp employees%ROWTYPE; -- 引用整行结构

---

流程控制

Oracle PL/SQL流程控制通过条件语句（IF/CASE）、循环（FOR/WHILE/LOOP）及顺序控制（GOTO/NULL）管理代码执行逻辑，实现灵活业务处理与逻辑分支。

条件语句：

  IF condition THEN ... ELSIF ... ELSE ... END IF;
  CASE WHEN ... THEN ... ELSE ... END CASE;

循环：

1. 基本循环：LOOP ... EXIT WHEN ... END LOOP;
2. WHILE循环：WHILE condition LOOP ... END LOOP;
3. FOR循环：FOR i IN 1..10 LOOP ... END LOOP;

---

游标（Cursors）

Oracle PL/SQL游标用于逐行处理查询结果集，分显式（手动声明、打开、提取、关闭）和隐式（自动管理）两种，支持循环遍历数据，实现多行记录的精确操作与复杂业务逻辑处理。

• 显式游标：

DECLARE
    CURSOR c_emp IS SELECT * FROM employees;
    v_emp employees%ROWTYPE;
  BEGIN
    OPEN c_emp;
    LOOP
      FETCH c_emp INTO v_emp;
      EXIT WHEN c_emp%NOTFOUND;
      -- 处理数据
    END LOOP;
    CLOSE c_emp;
  END;

• 隐式游标：自动处理SELECT INTO或DML语句。
• FOR循环游标：

  FOR emp_rec IN (SELECT * FROM employees) LOOP
    DBMS_OUTPUT.PUT_LINE(emp_rec.name);
  END LOOP;

---

异常处理

1. 预定义异常：如NO_DATA_FOUND, TOO_MANY_ROWS。
2. 自定义异常：

  DECLARE
    e_custom EXCEPTION;
    PRAGMA EXCEPTION_INIT(e_custom, -20001);
  BEGIN
    RAISE e_custom;
  EXCEPTION
    WHEN e_custom THEN
      DBMS_OUTPUT.PUT_LINE('自定义错误');
  END;

---

存储过程与函数

Oracle PL/SQL存储过程（PROCEDURE）封装数据库操作，无返回值；函数（FUNCTION）返回计算结果，可在SQL中调用。两者均支持参数传递，提升代码复用性、模块化及执行效率。

• 存储过程：

  CREATE OR REPLACE PROCEDURE proc_name (p_param IN NUMBER) IS
  BEGIN
    -- 逻辑代码
  END;

• 函数（必须返回一个值）：

CREATE OR REPLACE FUNCTION func_name RETURN NUMBER IS
  BEGIN
    RETURN 100;
  END;

• 参数模式：IN（输入，默认）、OUT（输出）、IN OUT（双向）。

---

动态SQL

• EXECUTE IMMEDIATE：

  EXECUTE IMMEDIATE 'UPDATE employees SET salary = :1 WHERE id = :2' USING 5000, 101;

• DBMS_SQL包：处理复杂动态SQL。

---

事务控制

1. 显式提交：COMMIT;
2. 回滚：ROLLBACK;或回滚到保存点：ROLLBACK TO sp1;
3. 保存点：SAVEPOINT sp1;

---

集合类型

• 关联数组：

  TYPE t_dict IS TABLE OF VARCHAR2(20) INDEX BY PLS_INTEGER;

• 嵌套表：

TYPE t_list IS TABLE OF NUMBER;

• 可变数组（VARRAY）：固定大小的数组。

---

触发器（Triggers）

1. 行级触发器（FOR EACH ROW）可访问:NEW和:OLD。
2. 事件：BEFORE/AFTER INSERT/UPDATE/DELETE。

CREATE TRIGGER trg_audit
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
  INSERT INTO audit_table VALUES (:OLD.salary, :NEW.salary);
END;

---

注意事项

1. 异常处理中避免过度使用WHEN OTHERS，应捕获具体异常。
2. 显式游标使用后需及时关闭。
3. 动态SQL需防范SQL注入，优先使用绑定变量（USING子句）。
4. 事务控制需谨慎，存储过程内通常不自动提交，由调用者决定。

---

总结

     Oracle PL/SQL是Oracle数据库专用的过程化编程语言，深度融合SQL的数据处理能力与结构化编程特性。它以块（BLOCK）为基本单元，每个块由声明部分（DECLARE）、执行部分（BEGIN-END）和异常处理（EXCEPTION）构成，支持模块化开发，提升代码可读性与复用性。PL/SQL通过变量、条件分支（IF/CASE）、循环（LOOP/WHILE/FOR）等语法实现复杂逻辑控制，并允许开发者创建存储过程、函数、包（Package）及触发器（Trigger），将业务逻辑封装在数据库层，减少网络交互，提高执行效率。

     其异常处理机制通过预定义和自定义异常捕获错误，确保程序健壮性。游标（显式/隐式）支持逐行处理查询结果集，而集合类型（关联数组、嵌套表等）可高效操作批量数据。动态SQL技术（如EXECUTE IMMEDIATE）赋予SQL语句运行时动态构建的能力，适应灵活场景需求。PL/SQL与SQL无缝集成，支持直接在代码中嵌入DML、事务控制语句，同时通过BULK COLLECT、FORALL等特性优化批量操作性能。随着版本迭代，PL/SQL持续增强对JSON、云计算的支持，并强化调试工具（如DBMS_OUTPUT、UTL_FILE），成为企业级数据处理、事务管理和自动化任务的核心工具，广泛应用于金融、电信等领域的高性能数据库系统中。