Oracle PL/SQL 入门教程：从基础语法到工程实践

PL/SQL（Procedural Language/Structured Query Language）是 Oracle 数据库对 SQL 的过程化扩展。它将 SQL 的数据操作能力与过程式编程语言的控制逻辑（变量、条件、循环、子程序等）无缝结合，是 Oracle 后端开发、数据处理、报表与业务逻辑封装的核心工具。本文带你从零入门，覆盖 基础语法 → 常用结构 → 子程序 → 工程实践，并配大量可直接运行的示例。

---

TL;DR（先给结论）

• 变量声明：用 DECLARE 块，类型推荐 %TYPE（引用列类型）和 %ROWTYPE（引用行类型），避免硬编码。
• 控制流：IF-ELSIF-ELSE 做多分支，LOOP / WHILE / FOR 做循环，CASE 做表达式级分支。
• 游标（Cursor）：显式游标适合遍历查询结果集，FOR 循环游标最省心（自动打开/关闭）。
• 异常处理：EXCEPTION 块必写，区分 预定义异常（如 NO_DATA_FOUND）与 自定义异常。
• 存储过程/函数：过程无返回值用 CREATE PROCEDURE，函数有返回值用 CREATE FUNCTION，参数模式分 IN / OUT / IN OUT。
• 触发器（Trigger）：BEFORE / AFTER + INSERT / UPDATE / DELETE，行级用 FOR EACH ROW，通过 :NEW / :OLD 访问新旧值。
• 包（Package）：规范 = 包规范（声明） + 包体（实现），用于模块化与权限控制。

---

1) PL/SQL 基本结构：匿名块

PL/SQL 程序的最小单元是 块（Block），最常见的是「匿名块」，结构如下：

DECLARE
  -- 声明区（可选）：变量、常量、游标、自定义类型、局部子程序
  v_name   VARCHAR2(50);
  v_salary NUMBER(10,2) := 8000;
BEGIN
  -- 执行区（必填）：SQL 语句和过程化语句
  SELECT first_name INTO v_name
  FROM employees
  WHERE employee_id = 100;

  DBMS_OUTPUT.PUT_LINE('Name: ' || v_name || ', Salary: ' || v_salary);
EXCEPTION
  -- 异常处理区（可选）：捕获并处理错误
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Employee not found.');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/

提示：在 SQL*Plus / SQL Developer 中执行 PL/SQL 块，结尾用 / 作为块分隔符。开启输出需先执行 SET SERVEROUTPUT ON;。

---

2) 变量与常量声明

2.1 基本类型与赋值

DECLARE
  v_emp_id    NUMBER(6);          -- 数字
  v_name      VARCHAR2(50);       -- 字符串
  v_hire_date DATE;               -- 日期
  v_active    BOOLEAN := TRUE;    -- 布尔（PL/SQL 专有，SQL 表字段不能直接用）
  c_tax_rate  CONSTANT NUMBER := 0.13;  -- 常量，必须初始化且不能改
BEGIN
  v_emp_id := 100;
  v_name   := 'Steven';
  DBMS_OUTPUT.PUT_LINE(v_name || ' | ID=' || v_emp_id || ' | TaxRate=' || c_tax_rate);
END;
/

2.2 %TYPE：引用表列类型（推荐）

直接绑定字段类型，表结构变更时无需改代码：

DECLARE
  v_emp_id  employees.employee_id%TYPE;
  v_name    employees.first_name%TYPE;
  v_salary  employees.salary%TYPE;
BEGIN
  SELECT employee_id, first_name, salary
    INTO v_emp_id, v_name, v_salary
    FROM employees
   WHERE employee_id = 100;

  DBMS_OUTPUT.PUT_LINE(v_emp_id || ' - ' || v_name || ' - ' || v_salary);
END;
/

2.3 %ROWTYPE：引用整行类型

DECLARE
  r_emp employees%ROWTYPE;   -- 一行记录，字段通过 . 访问
BEGIN
  SELECT * INTO r_emp FROM employees WHERE employee_id = 100;
  DBMS_OUTPUT.PUT_LINE(r_emp.employee_id || ' | ' || r_emp.last_name);
END;
/

---

3) 控制流：条件与循环

3.1 条件分支：IF / CASE

DECLARE
  v_score NUMBER := 85;
  v_grade VARCHAR2(2);
BEGIN
  -- IF-ELSIF-ELSE
  IF v_score >= 90 THEN
    v_grade := 'A';
  ELSIF v_score >= 80 THEN
    v_grade := 'B';
  ELSIF v_score >= 60 THEN
    v_grade := 'C';
  ELSE
    v_grade := 'D';
  END IF;
  DBMS_OUTPUT.PUT_LINE('Grade(IF): ' || v_grade);

  -- CASE 表达式（更简洁）
  v_grade := CASE
               WHEN v_score >= 90 THEN 'A'
               WHEN v_score >= 80 THEN 'B'
               WHEN v_score >= 60 THEN 'C'
               ELSE 'D'
             END;
  DBMS_OUTPUT.PUT_LINE('Grade(CASE): ' || v_grade);
END;
/

3.2 三种循环

DECLARE
  i NUMBER := 1;
BEGIN
  -- 1) 基础 LOOP + EXIT WHEN
  DBMS_OUTPUT.PUT_LINE('--- LOOP ---');
  LOOP
    DBMS_OUTPUT.PUT_LINE('i=' || i);
    EXIT WHEN i >= 3;
    i := i + 1;
  END LOOP;

  -- 2) WHILE 循环
  DBMS_OUTPUT.PUT_LINE('--- WHILE ---');
  i := 1;
  WHILE i <= 3 LOOP
    DBMS_OUTPUT.PUT_LINE('i=' || i);
    i := i + 1;
  END LOOP;

  -- 3) FOR 循环（最常用，无需手动声明/自增）
  DBMS_OUTPUT.PUT_LINE('--- FOR ---');
  FOR j IN 1..3 LOOP
    DBMS_OUTPUT.PUT_LINE('j=' || j);
  END LOOP;

  -- 反向遍历：REVERSE
  FOR j IN REVERSE 1..3 LOOP
    DBMS_OUTPUT.PUT_LINE('reverse j=' || j);
  END LOOP;
END;
/

---

4) 游标（Cursor）：遍历结果集

4.1 显式游标（完整控制）

DECLARE
  CURSOR c_emps IS           -- 1) 声明游标
    SELECT employee_id, first_name, salary
      FROM employees
     WHERE department_id = 60;
  r c_emps%ROWTYPE;          -- 行变量
BEGIN
  OPEN c_emps;               -- 2) 打开
  LOOP
    FETCH c_emps INTO r;     -- 3) 抓取
    EXIT WHEN c_emps%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(r.employee_id || ' | ' || r.first_name || ' | ' || r.salary);
  END LOOP;
  CLOSE c_emps;              -- 4) 关闭
END;
/

4.2 FOR 循环游标（推荐，自动开关）

BEGIN
  FOR r IN (SELECT employee_id, first_name, salary
              FROM employees
             WHERE department_id = 60)
  LOOP
    DBMS_OUTPUT.PUT_LINE(r.employee_id || ' | ' || r.first_name || ' | ' || r.salary);
  END LOOP;
END;
/

4.3 带参数的游标

DECLARE
  CURSOR c_dept_emps(p_dept_id NUMBER) IS
    SELECT employee_id, first_name FROM employees WHERE department_id = p_dept_id;
BEGIN
  FOR r IN c_dept_emps(60) LOOP
    DBMS_OUTPUT.PUT_LINE(r.employee_id || ' - ' || r.first_name);
  END LOOP;
END;
/

---

5) 异常处理

5.1 预定义异常

异常名	触发场景
NO_DATA_FOUND	SELECT ... INTO 没查到数据
TOO_MANY_ROWS	SELECT ... INTO 返回多行
ZERO_DIVIDE	除以零
DUP_VAL_ON_INDEX	唯一约束冲突
OTHERS	兜底，匹配所有其它异常

DECLARE
  v_sal employees.salary%TYPE;
BEGIN
  SELECT salary INTO v_sal FROM employees WHERE employee_id = -1;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('No employee with id=-1, defaulting to 0.');
    v_sal := 0;
  WHEN TOO_MANY_ROWS THEN
    DBMS_OUTPUT.PUT_LINE('Unexpected: query returned multiple rows.');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error code: ' || SQLCODE || ' Msg: ' || SQLERRM);
    RAISE;   -- 重新抛出
END;
/

5.2 自定义异常 + PRAGMA EXCEPTION_INIT

DECLARE
  e_sal_too_low EXCEPTION;
  -- 把自定义异常绑定到 -20001 错误码
  PRAGMA EXCEPTION_INIT(e_sal_too_low, -20001);
  v_sal NUMBER := 500;
BEGIN
  IF v_sal < 1000 THEN
    RAISE_APPLICATION_ERROR(-20001, 'Salary ' || v_sal || ' below minimum 1000.');
  END IF;
EXCEPTION
  WHEN e_sal_too_low THEN
    DBMS_OUTPUT.PUT_LINE('Handled: ' || SQLERRM);
END;
/

RAISE_APPLICATION_ERROR(-20000 ~ -20999, 'msg') 是返回业务错误给调用方的标准方式。

---

6) 存储过程（Procedure）与函数（Function）

6.1 存储过程：封装业务动作

CREATE OR REPLACE PROCEDURE raise_salary(
  p_emp_id    IN  employees.employee_id%TYPE,
  p_pct       IN  NUMBER DEFAULT 0.05,   -- 默认参数
  p_new_sal   OUT employees.salary%TYPE
)
IS
  v_cur_sal employees.salary%TYPE;
BEGIN
  SELECT salary INTO v_cur_sal
    FROM employees
   WHERE employee_id = p_emp_id
   FOR UPDATE;   -- 行锁，避免并发更新

  v_cur_sal := v_cur_sal * (1 + p_pct);

  UPDATE employees
     SET salary = v_cur_sal
   WHERE employee_id = p_emp_id;

  p_new_sal := v_cur_sal;
  COMMIT;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RAISE_APPLICATION_ERROR(-20001, 'Employee ' || p_emp_id || ' not found.');
  WHEN OTHERS THEN
    ROLLBACK;
    RAISE;
END raise_salary;
/

调用过程：

DECLARE
  v_new NUMBER;
BEGIN
  raise_salary(100, 0.10, v_new);
  DBMS_OUTPUT.PUT_LINE('New salary: ' || v_new);
END;
/

6.2 函数：有返回值，可用于 SQL

CREATE OR REPLACE FUNCTION get_annual_sal(
  p_emp_id employees.employee_id%TYPE
) RETURN NUMBER
IS
  v_sal  employees.salary%TYPE;
  v_comm employees.commission_pct%TYPE;
BEGIN
  SELECT salary, NVL(commission_pct, 0)
    INTO v_sal, v_comm
    FROM employees
   WHERE employee_id = p_emp_id;

  RETURN v_sal * 12 * (1 + v_comm);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RETURN NULL;
END get_annual_sal;
/

在 SQL 中直接用：

SELECT employee_id, first_name, get_annual_sal(employee_id) AS annual_pay
FROM employees
WHERE department_id = 60;

---

7) 触发器（Trigger）：自动响应 DML/DDL 事件

7.1 行级触发器：BEFORE INSERT/UPDATE 自动填充字段

CREATE OR REPLACE TRIGGER trg_employees_biu
BEFORE INSERT OR UPDATE ON employees
FOR EACH ROW   -- 行级：每一行触发一次
BEGIN
  -- 新员工自动设置入职日
  IF INSERTING THEN
    :NEW.hire_date := COALESCE(:NEW.hire_date, SYSDATE);
  END IF;

  -- 薪资校验（防降薪超 20%）
  IF UPDATING('salary') AND :OLD.salary IS NOT NULL THEN
    IF :NEW.salary < :OLD.salary * 0.8 THEN
      RAISE_APPLICATION_ERROR(-20002,
        'Salary cannot drop more than 20%: ' || :OLD.salary || ' -> ' || :NEW.salary);
    END IF;
  END IF;
END trg_employees_biu;
/

关键字说明：

• :NEW / :OLD：分别指向修改前后的行（INSERT 只有 :NEW，DELETE 只有 :OLD）。
• INSERTING / UPDATING / DELETING：谓词，用于判断当前触发的是哪种 DML。

7.2 语句级触发器 + 审计日志

-- 审计表
CREATE TABLE emp_audit_log (
  log_id      NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  action      VARCHAR2(10),
  changed_by  VARCHAR2(100),
  changed_at  TIMESTAMP,
  row_count   NUMBER
);

CREATE OR REPLACE TRIGGER trg_emps_audit
AFTER INSERT OR UPDATE OR DELETE ON employees
DECLARE
  v_action VARCHAR2(10);
BEGIN
  v_action := CASE
                WHEN INSERTING THEN 'INSERT'
                WHEN UPDATING  THEN 'UPDATE'
                WHEN DELETING  THEN 'DELETE'
              END;
  INSERT INTO emp_audit_log(action, changed_by, changed_at, row_count)
  VALUES (v_action, USER, SYSTIMESTAMP, SQL%ROWCOUNT);
END trg_emps_audit;
/

---

8) 包（Package）：模块化的最佳实践

把一组相关的过程、函数、类型、常量按「规范 + 实现」分离：

8.1 包规范（声明对外接口）

CREATE OR REPLACE PACKAGE pkg_emp_mgmt AS
  -- 常量
  c_min_sal CONSTANT NUMBER := 1000;

  -- 自定义类型（引用游标 / 集合等）
  TYPE t_emp_list IS TABLE OF employees%ROWTYPE;

  -- 过程 / 函数声明
  FUNCTION  get_emp(p_id employees.employee_id%TYPE) RETURN employees%ROWTYPE;
  PROCEDURE list_by_dept(p_dept_id employees.department_id%TYPE,
                         p_list    OUT t_emp_list);
END pkg_emp_mgmt;
/

8.2 包体（具体实现）

CREATE OR REPLACE PACKAGE BODY pkg_emp_mgmt AS

  FUNCTION get_emp(p_id employees.employee_id%TYPE) RETURN employees%ROWTYPE IS
    r employees%ROWTYPE;
  BEGIN
    SELECT * INTO r FROM employees WHERE employee_id = p_id;
    RETURN r;
  END get_emp;

  PROCEDURE list_by_dept(p_dept_id employees.department_id%TYPE,
                         p_list    OUT t_emp_list) IS
  BEGIN
    SELECT *
      BULK COLLECT INTO p_list   -- 批量绑定到集合，性能远好于逐行 FETCH
      FROM employees
     WHERE department_id = p_dept_id
     ORDER BY employee_id;
  END list_by_dept;

END pkg_emp_mgmt;
/

调用包子程序：

DECLARE
  v_list pkg_emp_mgmt.t_emp_list;
BEGIN
  pkg_emp_mgmt.list_by_dept(60, v_list);
  FOR i IN 1..v_list.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(v_list(i).employee_id || ' - ' || v_list(i).first_name);
  END LOOP;
END;
/

---

9) 集合与批量操作（BULK COLLECT / FORALL）

当需要处理大量数据时，逐行处理极慢。Oracle 提供集合与批量 DML，性能通常提升 1~2 个数量级。

9.1 批量查询：BULK COLLECT

DECLARE
  TYPE t_ids IS TABLE OF employees.employee_id%TYPE;
  v_ids t_ids;
BEGIN
  SELECT employee_id
    BULK COLLECT INTO v_ids
    FROM employees
   WHERE salary > 10000;

  DBMS_OUTPUT.PUT_LINE('Fetched ' || v_ids.COUNT || ' rows.');
END;
/

9.2 批量 DML：FORALL（注意：不是循环）

DECLARE
  TYPE t_emp_rec IS RECORD (id NUMBER, sal NUMBER);
  TYPE t_emp_tab IS TABLE OF t_emp_rec;
  v_data t_emp_tab;
BEGIN
  SELECT employee_id, salary * 1.1
    BULK COLLECT INTO v_data
    FROM employees
   WHERE department_id = 60;

  -- 一次性把整个集合绑定给 UPDATE，只发一次 SQL
  FORALL i IN 1..v_data.COUNT
    UPDATE employees
       SET salary   = v_data(i).sal,
           last_updated_by = USER,
           last_updated_at = SYSDATE
     WHERE employee_id = v_data(i).id;

  DBMS_OUTPUT.PUT_LINE('Updated ' || SQL%BULK_ROWCOUNT.COUNT || ' groups.');
  COMMIT;
END;
/

---

10) 动态 SQL：运行时拼 SQL

当表名、列名、筛选条件不能写死时（例如报表框架），用 EXECUTE IMMEDIATE：

DECLARE
  v_table_name VARCHAR2(30) := 'employees';
  v_col_name   VARCHAR2(30) := 'salary';
  v_dept_id    NUMBER       := 60;
  v_total      NUMBER;
  v_sql        VARCHAR2(1000);
BEGIN
  v_sql := 'SELECT SUM(' || DBMS_ASSERT.ENQUOTE_NAME(v_col_name) || ') ' ||
           'FROM '  || DBMS_ASSERT.QUALIFIED_SQL_NAME(v_table_name) || ' ' ||
           'WHERE department_id = :p_dept';

  EXECUTE IMMEDIATE v_sql INTO v_total USING v_dept_id;

  DBMS_OUTPUT.PUT_LINE('Total salary in dept ' || v_dept_id || ': ' || v_total);
END;
/

安全提醒：动态 SQL 的标识符（表/列名）不能用绑定变量，务必用 DBMS_ASSERT 包做校验，避免 SQL 注入。值参数一律用 :1 / :2 绑定 + USING 传入。

---

11) 工程实践小贴士

• 命名规范：变量 v_ / 常量 c_ / 过程 p_ / 游标 c_ / 自定义类型 t_ / 记录 r_，一眼分清。
• 事务控制：存储过程中保持「短事务」，只在最外层过程统一 COMMIT/ROLLBACK，避免嵌套提交。
• 权限：CREATE PROCEDURE/FUNCTION/TRIGGER/PACKAGE 需要对应的系统权限；执行权限用 GRANT EXECUTE ON ... TO ...。
• 调试：SQL Developer / DataGrip 支持 PL/SQL 断点调试；简单场景 DBMS_OUTPUT.PUT_LINE 也够用。
• 性能定位：用 DBMS_PROFILER / DBMS_HPROF 做 PL/SQL 级行级耗时分析。
• 可维护性：复杂业务逻辑建议落到 包 中；单体匿名块/触发器越「薄」越容易调优。

---

参考与延伸阅读

• Oracle PL/SQL Language Reference（官方文档）
• Oracle Database PL/SQL Packages and Types Reference（DBMS_OUTPUT / DBMS_ASSERT / DBMS_SQL 等内置包大全）
• Ask TOM（https://asktom.oracle.com）：Oracle 官方问答社区，覆盖大量 PL/SQL 最佳实践