DB / Oracle / ACE 精选

Oracle Connor PL/SQL 技巧集

2026.09.06

Oracle Connor PL/SQL 技巧集

来源：Connor McDonald / asktom.oracle.com 适用版本：Oracle Database 8i+ 文档版本：v1.0 / 2026-07-22

1. 关于 Connor McDonald

Connor McDonald，Oracle ACE Director，AskTOM 成员[1]。

  • 著有《Mastering Oracle PL/SQL》
  • 擅长 PL/SQL、新特性
  • 博客：oracledisruption.blogspot.com

2. PL/SQL 哲学

2.1 Connor 观点

- SQL 优先
- PL/SQL 补充
- 不要逐行
- 集合思维

2.2 原则

- 能 SQL 就 SQL
- PL/SQL 适合复杂逻辑
- 批量优于循环
- 性能优先

3. BULK COLLECT

3.1 优势

- 批量获取
- 减少上下文切换
- 性能提升

3.2 示例

DECLARE
  TYPE emp_tab IS TABLE OF emp%ROWTYPE;
  v_emps emp_tab;
BEGIN
  SELECT * BULK COLLECT INTO v_emps FROM emp;
  
  FOR i IN 1..v_emps.COUNT LOOP
    -- 处理
    NULL;
  END LOOP;
END;
/

3.3 LIMIT

DECLARE
  CURSOR c IS SELECT * FROM emp;
  TYPE emp_tab IS TABLE OF emp%ROWTYPE;
  v_emps emp_tab;
BEGIN
  OPEN c;
  LOOP
    FETCH c BULK COLLECT INTO v_emps LIMIT 100;
    EXIT WHEN v_emps.COUNT = 0;
    -- 处理
  END LOOP;
  CLOSE c;
END;
/

3.4 Connor 建议

- LIMIT 100-1000
- 平衡内存与性能
- 评估

4. FORALL

4.1 优势

- 批量 DML
- 减少上下文切换
- 性能提升

4.2 示例

DECLARE
  TYPE id_tab IS TABLE OF NUMBER;
  v_ids id_tab := id_tab(1, 2, 3, 4, 5);
BEGIN
  FORALL i IN 1..v_ids.COUNT
    DELETE FROM emp WHERE id = v_ids(i);
END;
/

4.3 SAVE EXCEPTIONS

DECLARE
  TYPE id_tab IS TABLE OF NUMBER;
  v_ids id_tab := id_tab(1, 2, 3, 4, 5);
BEGIN
  FORALL i IN 1..v_ids.COUNT SAVE EXCEPTIONS
    DELETE FROM emp WHERE id = v_ids(i);
EXCEPTION
  WHEN OTHERS THEN
    FOR i IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
      DBMS_OUTPUT.PUT_LINE('Error ' || SQL%BULK_EXCEPTIONS(i).ERROR_CODE);
    END LOOP;
END;
/

4.4 Connor 强调

- FORALL 必用
- 批量 DML
- 性能 10-100 倍

5. 集合操作

5.1 反例

-- slow-by-slow
FOR rec IN (SELECT * FROM emp) LOOP
  UPDATE emp SET salary = salary * 1.1 WHERE id = rec.id;
END LOOP;

5.2 Connor 重写

UPDATE emp SET salary = salary * 1.1;

5.3 性能

- 反例：100 秒
- 重写：1 秒
- 差距 100 倍

6. PIPELINED

6.1 优势

- 流式处理
- 内存少
- 实时返回

6.2 示例

CREATE OR REPLACE FUNCTION get_emp(p_deptno NUMBER) 
  RETURN sys.odcivarchar2list PIPELINED
IS
  CURSOR c IS SELECT ename FROM emp WHERE deptno = p_deptno;
BEGIN
  FOR rec IN c LOOP
    PIPE ROW(rec.ename);
  END LOOP;
  RETURN;
END;
/

-- 查询
SELECT * FROM TABLE(get_emp(10));

6.3 Connor 应用

- 流式
- 内存优化
- 灵活

7. 动态 SQL

7.1 EXECUTE IMMEDIATE

EXECUTE IMMEDIATE 'SELECT * FROM emp WHERE id=:1' USING v_id;

7.2 DBMS_SQL

-- 复杂动态
DECLARE
  v_cur INTEGER;
  v_cnt NUMBER;
BEGIN
  v_cur := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(v_cur, 'SELECT COUNT(*) FROM emp', DBMS_SQL.NATIVE);
  -- ...
  DBMS_SQL.CLOSE_CURSOR(v_cur);
END;
/

7.3 Connor 建议

- EXECUTE IMMEDIATE 优先
- DBMS_SQL 复杂场景
- 绑定变量

8. RESULT CACHE

8.1 11g+

CREATE OR REPLACE FUNCTION get_salary(p_id NUMBER) 
  RETURN NUMBER RESULT_CACHE
IS
  v_sal NUMBER;
BEGIN
  SELECT salary INTO v_sal FROM emp WHERE id = p_id;
  RETURN v_sal;
END;
/

8.2 优势

- 结果缓存
- 性能提升
- 自动失效

8.3 Connor 应用

- 读多写少
- 函数缓存
- 性能

9. UTL_FILE

9.1 文件操作

DECLARE
  v_file UTL_FILE.file_type;
BEGIN
  v_file := UTL_FILE.fopen('DIR', 'test.txt', 'W');
  UTL_FILE.put_line(v_file, 'Hello');
  UTL_FILE.fclose(v_file);
END;
/

9.2 Connor 建议

- 目录对象
- 权限
- 评估

10. DBMS_OUTPUT

10.1 基础

SET SERVEROUTPUT ON;
EXEC DBMS_OUTPUT.PUT_LINE('Hello');

10.2 性能

- 缓冲
- 限制
- 评估

10.3 Connor 警告

- 大量输出慢
- 评估
- 限制

11. AUTONOMOUS_TRANSACTION

11.1 用途

- 独立事务
- 日志
- 不影响主事务

11.2 示例

CREATE OR REPLACE PROCEDURE log_msg(p_msg VARCHAR2) AS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO log_table VALUES (p_msg, SYSDATE);
  COMMIT;
END;
/

11.3 Connor 警告

- 慎用
- 潜在问题
- 评估

12. 异常处理

12.1 命名异常

DECLARE
  e_no_data EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_no_data, -1400);
BEGIN
  -- ...
EXCEPTION
  WHEN e_no_data THEN
    -- 处理
END;
/

12.2 RAISE_APPLICATION_ERROR

RAISE_APPLICATION_ERROR(-20001, 'Custom error');

12.3 Connor 建议

- 精确处理
- 不要 OTHERS 吞掉
- 日志

13. 性能技巧

13.1 索引

- 索引使用
- 监控
- 优化

13.2 绑定变量

- 必用
- 减少硬解析
- 性能

13.3 批量

- BULK COLLECT
- FORALL
- 性能

14. Connor 经典案例

14.1 案例：慢循环

- slow-by-slow
- 改集合
- 100 倍

14.2 案例：解析高

- 字面量
- 绑定变量
- 优化

14.3 案例：内存高

- BULK COLLECT 无 LIMIT
- 加 LIMIT
- 优化

15. Connor 名言

"If you can do it in SQL, do it in SQL"
"Bind variables are mandatory"
"Don't go slow-by-slow"

16. 最佳实践

  1. SQL 优先：能用 SQL
  2. 绑定变量：必用
  3. BULK COLLECT：批量
  4. FORALL：DML
  5. LIMIT：控制
  6. PIPELINED：流式
  7. RESULT CACHE：缓存
  8. 异常：精确
  9. 测试：性能
  10. 原理：理解

17. 参考资料

[1] Connor McDonald, https://asktom.oracle.com [2] Connor McDonald, “Mastering Oracle PL/SQL”, Apress [3] Connor McDonald, https://oracledisruption.blogspot.com