Oracle PL/SQL 基础与块结构

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

---

1. 概述

PL/SQL（Procedural Language/SQL） 是 Oracle 的过程化语言[1]：

核心特性：

• 过程化扩展
• 集成 SQL
• 模块化
• 异常处理
• 性能优化

---

2. 块结构

[DECLARE
  -- 声明部分
  variable_name datatype [:= initial_value];
BEGIN
  -- 执行部分（必需）
  -- SQL 和 PL/SQL 语句
EXCEPTION
  -- 异常处理
  WHEN exception_name THEN
    -- 处理逻辑
END;
/

2.1 简单块

BEGIN
  DBMS_OUTPUT.PUT_LINE('Hello, PL/SQL!');
END;
/

2.2 完整块

DECLARE
  v_name VARCHAR2(100);
  v_salary NUMBER := 5000;
BEGIN
  SELECT last_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;
/

---

3. 变量与常量

3.1 变量声明

DECLARE
  v_count NUMBER;
  v_name VARCHAR2(100) := 'Unknown';
  v_hire_date DATE DEFAULT SYSDATE;
  v_active BOOLEAN := TRUE;
  v_id employees.employee_id%TYPE := 100;  -- 引用列类型
  v_emp employees%ROWTYPE;  -- 引用表行类型
BEGIN
  ...
END;

3.2 常量

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

3.3 %TYPE

-- 引用列类型
v_salary employees.salary%TYPE;

-- 优势：跟随列类型变化

3.4 %ROWTYPE

-- 引用整行
v_emp employees%ROWTYPE;

-- 使用
SELECT * INTO v_emp FROM employees WHERE employee_id = 100;
DBMS_OUTPUT.PUT_LINE(v_emp.last_name);

---

4. 数据类型

4.1 标量类型

类型	说明
NUMBER(p,s)	数值
VARCHAR2(n)	字符串
CHAR(n)	定长字符串
DATE	日期
TIMESTAMP	时间戳
BOOLEAN	布尔
INTEGER	整数
PLS_INTEGER	整数（高效）
BINARY_INTEGER	整数

4.2 复合类型

-- 记录类型
DECLARE
  TYPE emp_record IS RECORD (
    id NUMBER,
    name VARCHAR2(100),
    salary NUMBER
  );
  v_emp emp_record;
BEGIN
  v_emp.id := 100;
  v_emp.name := 'Smith';
END;

4.3 集合类型

-- 索引表
DECLARE
  TYPE name_table IS TABLE OF VARCHAR2(100) INDEX BY PLS_INTEGER;
  v_names name_table;
BEGIN
  v_names(1) := 'Alice';
  v_names(2) := 'Bob';
  DBMS_OUTPUT.PUT_LINE(v_names(1));
END;

-- 嵌套表
DECLARE
  TYPE num_list IS TABLE OF NUMBER;
  v_nums num_list := num_list(1, 2, 3, 4, 5);
BEGIN
  FOR i IN 1..v_nums.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(v_nums(i));
  END LOOP;
END;

-- VARRAY
DECLARE
  TYPE num_array IS VARRAY(10) OF NUMBER;
  v_arr num_array := num_array(1, 2, 3);
BEGIN
  ...
END;

---

5. 控制结构

5.1 IF 语句

IF v_score >= 90 THEN
  v_grade := 'A';
ELSIF v_score >= 80 THEN
  v_grade := 'B';
ELSIF v_score >= 70 THEN
  v_grade := 'C';
ELSE
  v_grade := 'F';
END IF;

5.2 CASE 语句

-- 简单 CASE
CASE v_grade
  WHEN 'A' THEN v_gpa := 4.0;
  WHEN 'B' THEN v_gpa := 3.0;
  WHEN 'C' THEN v_gpa := 2.0;
  ELSE v_gpa := 0.0;
END CASE;

-- 搜索 CASE
CASE
  WHEN v_score >= 90 THEN v_grade := 'A';
  WHEN v_score >= 80 THEN v_grade := 'B';
  ELSE v_grade := 'F';
END CASE;

5.3 LOOP 循环

-- 基本 LOOP
LOOP
  EXIT WHEN v_count > 10;
  v_count := v_count + 1;
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;

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

5.4 EXIT / CONTINUE

-- EXIT
LOOP
  EXIT WHEN condition;
END LOOP;

-- CONTINUE（11g+）
FOR i IN 1..10 LOOP
  CONTINUE WHEN i = 5;
  DBMS_OUTPUT.PUT_LINE(i);
END LOOP;

---

6. SQL 集成

6.1 SELECT INTO

DECLARE
  v_name VARCHAR2(100);
  v_salary NUMBER;
BEGIN
  SELECT last_name, salary 
  INTO v_name, v_salary
  FROM employees
  WHERE employee_id = 100;
END;

6.2 DML

BEGIN
  INSERT INTO log_table VALUES (SYSDATE, 'Test');
  
  UPDATE employees SET salary = salary * 1.1 WHERE employee_id = 100;
  
  DELETE FROM log_table WHERE log_date < SYSDATE - 30;
END;

6.3 游标

DECLARE
  CURSOR c_emp IS
    SELECT employee_id, last_name, salary FROM employees;
  
  v_emp c_emp%ROWTYPE;
BEGIN
  OPEN c_emp;
  LOOP
    FETCH c_emp INTO v_emp;
    EXIT WHEN c_emp%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_emp.last_name || ': ' || v_emp.salary);
  END LOOP;
  CLOSE c_emp;
END;

-- FOR 循环游标（自动）
BEGIN
  FOR v_emp IN (SELECT * FROM employees) LOOP
    DBMS_OUTPUT.PUT_LINE(v_emp.last_name);
  END LOOP;
END;

---

7. 异常处理

7.1 预定义异常

BEGIN
  SELECT last_name INTO v_name FROM employees WHERE employee_id = 999;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Not found');
  WHEN TOO_MANY_ROWS THEN
    DBMS_OUTPUT.PUT_LINE('Too many rows');
  WHEN ZERO_DIVIDE THEN
    DBMS_OUTPUT.PUT_LINE('Divide by zero');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;

7.2 自定义异常

DECLARE
  e_invalid_salary EXCEPTION;
  v_salary NUMBER := -100;
BEGIN
  IF v_salary < 0 THEN
    RAISE e_invalid_salary;
  END IF;
EXCEPTION
  WHEN e_invalid_salary THEN
    DBMS_OUTPUT.PUT_LINE('Invalid salary');
END;

7.3 RAISE_APPLICATION_ERROR

CREATE OR REPLACE PROCEDURE update_salary(
  p_emp_id NUMBER, 
  p_salary NUMBER
) AS
BEGIN
  IF p_salary < 0 THEN
    RAISE_APPLICATION_ERROR(-20001, 'Salary must be positive');
  END IF;
  
  UPDATE employees SET salary = p_salary WHERE employee_id = p_emp_id;
END;
/

---

8. 存储过程

CREATE OR REPLACE PROCEDURE update_salary(
  p_emp_id IN NUMBER,
  p_increase IN NUMBER
) AS
  v_old_salary NUMBER;
  v_new_salary NUMBER;
BEGIN
  SELECT salary INTO v_old_salary
  FROM employees WHERE employee_id = p_emp_id;
  
  v_new_salary := v_old_salary + p_increase;
  
  UPDATE employees SET salary = v_new_salary
  WHERE employee_id = p_emp_id;
  
  DBMS_OUTPUT.PUT_LINE('Updated: ' || v_old_salary || ' -> ' || v_new_salary);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Employee not found');
END;
/

-- 调用
EXEC update_salary(100, 1000);

---

9. 函数

CREATE OR REPLACE FUNCTION get_total_salary(
  p_dept_id NUMBER
) RETURN NUMBER AS
  v_total NUMBER;
BEGIN
  SELECT SUM(salary) INTO v_total
  FROM employees WHERE dept_id = p_dept_id;
  
  RETURN v_total;
END;
/

-- 调用
SELECT get_total_salary(10) FROM dual;

---

10. 包

-- 包规范
CREATE OR REPLACE PACKAGE emp_pkg AS
  PROCEDURE update_salary(p_emp_id NUMBER, p_increase NUMBER);
  FUNCTION get_total_salary(p_dept_id NUMBER) RETURN NUMBER;
END emp_pkg;
/

-- 包体
CREATE OR REPLACE PACKAGE BODY emp_pkg AS
  PROCEDURE update_salary(p_emp_id NUMBER, p_increase NUMBER) AS
  BEGIN
    UPDATE employees 
    SET salary = salary + p_increase 
    WHERE employee_id = p_emp_id;
  END;
  
  FUNCTION get_total_salary(p_dept_id NUMBER) RETURN NUMBER AS
    v_total NUMBER;
  BEGIN
    SELECT SUM(salary) INTO v_total
    FROM employees WHERE dept_id = p_dept_id;
    RETURN v_total;
  END;
END emp_pkg;
/

-- 调用
EXEC emp_pkg.update_salary(100, 1000);
SELECT emp_pkg.get_total_salary(10) FROM dual;

---

11. 触发器

CREATE OR REPLACE TRIGGER trg_audit_salary
BEFORE UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
  INSERT INTO salary_audit (emp_id, old_sal, new_sal, change_date)
  VALUES (:OLD.employee_id, :OLD.salary, :NEW.salary, SYSDATE);
END;
/

---

12. 常见坑与排错

12.1 NO_DATA_FOUND

-- SELECT INTO 必须返回一行
-- 使用聚合函数避免（SUM 返回 NULL）
SELECT SUM(salary) INTO v_total FROM ...;  -- 不抛异常
SELECT salary INTO v_sal FROM ...;  -- 可能抛异常

12.2 TOO_MANY_ROWS

-- SELECT INTO 返回多行
-- 添加 ROWNUM = 1 或 WHERE 条件

12.3 变量未初始化

DECLARE
  v_count NUMBER;  -- 默认 NULL
BEGIN
  v_count := v_count + 1;  -- NULL + 1 = NULL
END;

-- 修复
v_count NUMBER := 0;

---

13. 最佳实践

1. 使用 %TYPE/%ROWTYPE：跟随列变化
2. 异常处理：避免程序崩溃
3. 使用包：模块化
4. 绑定变量：减少硬解析
5. BULK COLLECT：批量操作
6. **避免 SELECT * **：明确列
7. 使用 FOR 循环游标：简洁
8. 权限检查：最小权限
9. 注释：清晰说明
10. 测试：单元测试

---

14. 参考资料

[1] Oracle Database PL/SQL Language Reference 19c https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/