Oracle PL/SQL 基础与块结构

适用版本：Oracle Database 10g / 11g / 12c / 19c / 23ai 文档版本：v1.0 / 2026-07

1. 概述

PL/SQL 是 Oracle 过程化 SQL 扩展[1]：

2. 块结构

2.1 完整

DECLARE
  -- 声明
  v_name VARCHAR2(100) := 'Alice';
  v_count NUMBER;
BEGIN
  -- 执行
  SELECT COUNT(*) INTO v_count FROM employees;
  DBMS_OUTPUT.PUT_LINE('Count: ' || v_count);
EXCEPTION
  -- 异常
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('No data');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/

2.2 匿名块

BEGIN
  DBMS_OUTPUT.PUT_LINE('Hello');
END;
/

2.3 命名块

过程（PROCEDURE）
函数（FUNCTION）
包（PACKAGE）
触发器（TRIGGER）

详细见：Oracle 存储过程与函数详解、Oracle PL/SQL 包设计与最佳实践、Oracle PL/SQL 触发器详解。

3. 变量声明

3.1 标量

DECLARE
  v_id NUMBER;
  v_name VARCHAR2(100);
  v_salary NUMBER(10, 2);
  v_hire_date DATE;
  v_active BOOLEAN := TRUE;
  v_count NUMBER DEFAULT 0;
BEGIN
  ...
END;

3.2 锚定

DECLARE
  v_name employees.name%TYPE;        -- 列类型
  v_emp employees%ROWTYPE;            -- 行类型
BEGIN
  SELECT * INTO v_emp FROM employees WHERE id = 1;
  v_name := v_emp.name;
END;

3.3 复合

DECLARE
  TYPE emp_rec IS RECORD (
    id NUMBER,
    name VARCHAR2(100),
    salary NUMBER
  );
  
  TYPE emp_tab IS TABLE OF employees%ROWTYPE INDEX BY VARCHAR2(50);
  
  v_emp emp_rec;
  v_emps emp_tab;
BEGIN
  ...
END;

详细见：Oracle PL/SQL 集合详解。

3.4 常量

DECLARE
  c_max_salary CONSTANT NUMBER := 100000;
  c_company_name CONSTANT VARCHAR2(50) := 'ACME';
BEGIN
  ...
END;

4. 赋值

4.1 直接

v_name := 'Alice';
v_count := 10;
v_active := TRUE;

4.2 SELECT INTO

SELECT name, salary INTO v_name, v_salary
FROM employees WHERE id = 1;

4.3 表达式

v_total := v_salary + v_bonus;
v_full_name := v_first || ' ' || v_last;
v_age := MONTHS_BETWEEN(SYSDATE, v_birth) / 12;

5. 控制流

5.1 IF

IF v_salary > 10000 THEN
  v_grade := 'A';
ELSIF v_salary > 5000 THEN
  v_grade := 'B';
ELSE
  v_grade := 'C';
END IF;

5.2 CASE

-- 简单
CASE v_dept_id
  WHEN 10 THEN v_dept := 'IT';
  WHEN 20 THEN v_dept := 'Sales';
  ELSE v_dept := 'Other';
END CASE;

-- 搜索
CASE 
  WHEN v_salary > 10000 THEN v_level := 'High';
  WHEN v_salary > 5000 THEN v_level := 'Mid';
  ELSE v_level := 'Low';
END CASE;

5.3 LOOP

-- 基本
LOOP
  v_count := v_count + 1;
  EXIT WHEN v_count > 10;
END LOOP;

-- WHILE
WHILE v_count < 10 LOOP
  v_count := v_count + 1;
END LOOP;

-- FOR
FOR i IN 1..10 LOOP
  DBMS_OUTPUT.PUT_LINE(i);
END LOOP;

-- REVERSE
FOR i IN REVERSE 1..10 LOOP
  DBMS_OUTPUT.PUT_LINE(i);
END LOOP;

6. SQL in PL/SQL

6.1 SELECT

SELECT name, salary INTO v_name, v_salary
FROM employees WHERE id = 1;

6.2 DML

INSERT INTO employees (id, name) VALUES (1, 'Alice');
UPDATE employees SET salary = 5000 WHERE id = 1;
DELETE FROM employees WHERE id = 1;

6.3 事务

COMMIT;
ROLLBACK;
SAVEPOINT sp1;
ROLLBACK TO sp1;

详细见：Oracle 事务与并发控制详解。

7. NULL 处理

-- NULL 比较
IF v_value IS NULL THEN ...
IF v_value IS NOT NULL THEN ...

-- 函数
v_name := NVL(v_value, 'default');
v_status := NVL2(v_value, 'has', 'no');
v_first := COALESCE(v_a, v_b, v_c);

8. 注释

-- 单行

/* 
  多行
*/

-- 标题
PROCEDURE my_proc IS
  -- TODO: 待实现
BEGIN
  NULL;  -- 占位
END;

9. PRAGMA

9.1 AUTONOMOUS_TRANSACTION

PROCEDURE log_msg IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  ...
END;

9.2 EXCEPTION_INIT

DECLARE
  e_fk EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_fk, -02292);
BEGIN
  ...
EXCEPTION
  WHEN e_fk THEN ...
END;

9.3 SERIALLY_REUSABLE

CREATE OR REPLACE PACKAGE pkg AS
  PRAGMA SERIALLY_REUSABLE;
END;

9.4 INLINE

PROCEDURE caller IS
  PRAGMA INLINE(small_proc, 'YES');
BEGIN
  small_proc;
END;

10. DBMS_OUTPUT

BEGIN
  DBMS_OUTPUT.ENABLE;
  DBMS_OUTPUT.PUT_LINE('Hello');
  DBMS_OUTPUT.PUT('No newline');
  DBMS_OUTPUT.NEW_LINE;
END;
/

SET SERVEROUTPUT ON;

11. 子程序

11.1 局部

DECLARE
  v_count NUMBER;
  
  PROCEDURE local_proc IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE('Local');
  END;
  
  FUNCTION local_fn RETURN NUMBER IS
  BEGIN
    RETURN 1;
  END;
BEGIN
  local_proc;
  v_count := local_fn;
END;
/

11.2 嵌套

PROCEDURE outer IS
  PROCEDURE inner IS
  BEGIN
    ...
  END;
BEGIN
  inner;
END;

12. 数据类型

类型	示例
标量	NUMBER, VARCHAR2, DATE, BOOLEAN
锚定	%TYPE, %ROWTYPE
复合	RECORD, TABLE, VARRAY
引用	REF CURSOR
LOB	CLOB, BLOB
对象	OBJECT

详细见：Oracle 数据类型详解、Oracle PL/SQL 集合详解。

13. 异常

13.1 预定义

EXCEPTION
  WHEN NO_DATA_FOUND THEN ...
  WHEN TOO_MANY_ROWS THEN ...
  WHEN ZERO_DIVIDE THEN ...
  WHEN OTHERS THEN ...

13.2 自定义

DECLARE
  e_custom EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_custom, -20001);
BEGIN
  IF condition THEN
    RAISE e_custom;
  END IF;
EXCEPTION
  WHEN e_custom THEN ...
END;

详细见：Oracle PL/SQL 异常处理详解。

14. 命名规范

- 变量：v_xxx
- 常量：c_xxx
- 类型：t_xxx（type）/ xxx_rec / xxx_tab
- 异常：e_xxx
- 游标：c_xxx
- 参数：p_xxx
- 过程：sp_xxx / proc_xxx
- 函数：fn_xxx

15. 性能

15.1 绑定变量

-- 自动（静态 SQL）
EXECUTE IMMEDIATE 'SELECT * FROM t WHERE id = :id' USING v_id;

15.2 BULK

SELECT * BULK COLLECT INTO v_emp FROM employees;
FORALL i IN 1..v_emp.COUNT
  INSERT INTO log VALUES (v_emp(i).id);

详细见：Oracle PL/SQL 性能优化详解。

16. 常见坑与排错

16.1 ORA-06550

- 编译错误
- 检查语法

16.2 ORA-06502

- 值错误
- 类型/长度

16.3 ORA-06512

- 错误栈
- 行号

16.4 NO_DATA_FOUND

- SELECT INTO 无行
- 处理

17. 最佳实践

命名规范：清晰
%TYPE/%ROWTYPE：锚定
常量：定义
异常处理：完整
绑定变量：性能
BULK：批量
注释：说明
模块化：包
测试：验证
文档化：维护

18. 参考资料

[1] Oracle Database PL/SQL Language Reference 19c, “PL/SQL Language Elements” https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/plsql-language-elements.html