DB / Oracle / SQL 开发

Oracle PL/SQL 高级编程技巧

2026.09.06

Oracle PL/SQL 高级编程技巧

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

1. 概述

PL/SQL 高级编程技巧汇总[1]：

详细见：Oracle 数据库高级 SQL 技巧、Oracle PL/SQL 性能优化详解。

2. 动态 SQL 高级

2.1 DBMS_SQL

CREATE OR REPLACE PROCEDURE dynamic_query(p_table VARCHAR2) IS
  v_cur INTEGER;
  v_count INTEGER;
  v_desc DBMS_SQL.DESC_TAB;
  v_cols INTEGER;
  v_val VARCHAR2(4000);
BEGIN
  v_cur := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(v_cur, 'SELECT * FROM ' || DBMS_ASSERT.QUALIFIED_SQL_NAME(p_table), DBMS_SQL.NATIVE);
  
  DBMS_SQL.DESCRIBE_COLUMNS(v_cur, v_cols, v_desc);
  
  FOR i IN 1..v_cols LOOP
    DBMS_SQL.DEFINE_COLUMN(v_cur, i, v_val, 4000);
  END LOOP;
  
  v_count := DBMS_SQL.EXECUTE(v_cur);
  
  WHILE DBMS_SQL.FETCH_ROWS(v_cur) > 0 LOOP
    FOR i IN 1..v_cols LOOP
      DBMS_SQL.COLUMN_VALUE(v_cur, i, v_val);
      DBMS_OUTPUT.PUT_LINE(v_desc(i).col_name || ': ' || v_val);
    END LOOP;
  END LOOP;
  
  DBMS_SQL.CLOSE_CURSOR(v_cur);
END;
/

详细见：Oracle PL/SQL 动态 SQL 详解。

2.2 任意结果集

CREATE OR REPLACE FUNCTION any_query(p_sql VARCHAR2) 
  RETURN SYS_REFCURSOR IS
  v_cur SYS_REFCURSOR;
BEGIN
  OPEN v_cur FOR p_sql;
  RETURN v_cur;
END;
/

DECLARE
  v_cur SYS_REFCURSOR;
  v_id NUMBER;
  v_name VARCHAR2(100);
BEGIN
  v_cur := any_query('SELECT id, name FROM employees');
  LOOP
    FETCH v_cur INTO v_id, v_name;
    EXIT WHEN v_cur%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_id || ': ' || v_name);
  END LOOP;
  CLOSE v_cur;
END;
/

3. 反射 / 元数据

3.1 表结构

CREATE OR REPLACE PROCEDURE describe_table(p_table VARCHAR2) IS
  v_cur INTEGER;
  v_desc DBMS_SQL.DESC_TAB;
  v_cols INTEGER;
BEGIN
  v_cur := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(v_cur, 'SELECT * FROM ' || DBMS_ASSERT.QUALIFIED_SQL_NAME(p_table) || ' WHERE 1=0', DBMS_SQL.NATIVE);
  DBMS_SQL.DESCRIBE_COLUMNS(v_cur, v_cols, v_desc);
  
  FOR i IN 1..v_cols LOOP
    DBMS_OUTPUT.PUT_LINE(
      v_desc(i).col_name || ' | ' || 
      v_desc(i).col_type || ' | ' ||
      v_desc(i).col_max_len
    );
  END LOOP;
  
  DBMS_SQL.CLOSE_CURSOR(v_cur);
END;
/

3.2 数据字典

SELECT column_name, data_type, data_length, nullable
FROM user_tab_columns
WHERE table_name = 'EMPLOYEES';

4. 泛型

4.1 ANYDATA

CREATE OR REPLACE PROCEDURE process_any(p_data SYS.ANYDATA) IS
  v_type VARCHAR2(30);
  v_number NUMBER;
  v_string VARCHAR2(4000);
  v_date DATE;
BEGIN
  v_type := p_data.GetTypeName;
  
  CASE v_type
    WHEN 'SYS.NUMBER' THEN
      IF p_data.GetNumber(v_number) = DBMS_TYPES.SUCCESS THEN
        DBMS_OUTPUT.PUT_LINE('Number: ' || v_number);
      END IF;
    WHEN 'SYS.VARCHAR2' THEN
      IF p_data.GetString(v_string) = DBMS_TYPES.SUCCESS THEN
        DBMS_OUTPUT.PUT_LINE('String: ' || v_string);
      END IF;
    WHEN 'SYS.DATE' THEN
      IF p_data.GetDate(v_date) = DBMS_TYPES.SUCCESS THEN
        DBMS_OUTPUT.PUT_LINE('Date: ' || v_date);
      END IF;
  END CASE;
END;
/

4.2 ANYDATASET

-- 任意集合
-- ANYDATASET 类型

5. 重用 / 模板

5.1 模板过程

CREATE OR REPLACE PROCEDURE template_proc(
  p_table VARCHAR2,
  p_action VARCHAR2
) IS
BEGIN
  -- 通用模板
  CASE p_action
    WHEN 'INSERT' THEN
      EXECUTE IMMEDIATE 'INSERT INTO ' || p_table || ' SELECT * FROM source';
    WHEN 'UPDATE' THEN
      ...
    WHEN 'DELETE' THEN
      EXECUTE IMMEDIATE 'DELETE FROM ' || p_table;
  END CASE;
END;
/

5.2 回调

CREATE OR REPLACE PROCEDURE process_with_callback(
  p_data VARCHAR2,
  p_callback VARCHAR2  -- 过程名
) IS
BEGIN
  -- 处理
  -- 调用回调
  EXECUTE IMMEDIATE 'BEGIN ' || p_callback || '(:1); END;' USING p_data;
END;
/

6. 元编程

6.1 生成代码

CREATE OR REPLACE PROCEDURE generate_audit_trigger(p_table VARCHAR2) IS
  v_sql CLOB;
BEGIN
  v_sql := 'CREATE OR REPLACE TRIGGER trg_audit_' || p_table || CHAR(10) ||
           'AFTER INSERT OR UPDATE OR DELETE ON ' || p_table || CHAR(10) ||
           'FOR EACH ROW' || CHAR(10) ||
           'BEGIN' || CHAR(10) ||
           '  INSERT INTO ' || p_table || '_audit VALUES (...);' || CHAR(10) ||
           'END;';
  
  EXECUTE IMMEDIATE v_sql;
END;
/

6.2 动态 PL/SQL

DECLARE
  v_proc VARCHAR2(4000);
BEGIN
  v_proc := 'BEGIN my_pkg.process_' || p_type || '(:1); END;';
  EXECUTE IMMEDIATE v_proc USING p_id;
END;
/

7. 缓存

7.1 RESULT CACHE

CREATE OR REPLACE FUNCTION get_dept_name(p_id NUMBER) RETURN VARCHAR2
  RESULT_CACHE RELIES_ON (departments)
IS
  v_name VARCHAR2(100);
BEGIN
  SELECT name INTO v_name FROM departments WHERE id = p_id;
  RETURN v_name;
END;
/

7.2 包级缓存

CREATE OR REPLACE PACKAGE cache_pkg AS
  TYPE dept_cache IS TABLE OF departments%ROWTYPE INDEX BY PLS_INTEGER;
  v_dept_cache dept_cache;
  
  FUNCTION get_dept(p_id NUMBER) RETURN departments%ROWTYPE;
END;
/

CREATE OR REPLACE PACKAGE BODY cache_pkg AS
  FUNCTION get_dept(p_id NUMBER) RETURN departments%ROWTYPE IS
  BEGIN
    IF NOT v_dept_cache.EXISTS(p_id) THEN
      SELECT * INTO v_dept_cache(p_id) FROM departments WHERE id = p_id;
    END IF;
    RETURN v_dept_cache(p_id);
  END;
END;
/

8. 闭包 / 回调

CREATE OR REPLACE PACKAGE callback_pkg AS
  PROCEDURE register(p_name VARCHAR2, p_proc VARCHAR2);
  PROCEDURE call(p_name VARCHAR2, p_arg VARCHAR2);
END;
/

CREATE OR REPLACE PACKAGE BODY callback_pkg AS
  TYPE proc_tab IS TABLE OF VARCHAR2(4000) INDEX BY VARCHAR2(50);
  v_procs proc_tab;
  
  PROCEDURE register(p_name VARCHAR2, p_proc VARCHAR2) IS
  BEGIN
    v_procs(p_name) := p_proc;
  END;
  
  PROCEDURE call(p_name VARCHAR2, p_arg VARCHAR2) IS
  BEGIN
    IF v_procs.EXISTS(p_name) THEN
      EXECUTE IMMEDIATE 'BEGIN ' || v_procs(p_name) || '(:1); END;' USING p_arg;
    END IF;
  END;
END;
/

9. 错误处理

9.1 完整异常

CREATE OR REPLACE PROCEDURE safe_proc IS
  v_err VARCHAR2(4000);
BEGIN
  -- 业务
  ...
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    log_error('NO_DATA', SQLERRM, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
    RAISE_APPLICATION_ERROR(-20001, 'Data not found');
    
  WHEN TOO_MANY_ROWS THEN
    log_error('TOO_MANY', SQLERRM, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
    RAISE_APPLICATION_ERROR(-20002, 'Too many rows');
    
  WHEN OTHERS THEN
    v_err := SQLERRM || CHR(10) || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;
    log_error('OTHERS', v_err, NULL);
    RAISE;
END;
/

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

9.2 异常链

CREATE OR REPLACE PROCEDURE inner IS
BEGIN
  RAISE_APPLICATION_ERROR(-20001, 'Inner error');
END;
/

CREATE OR REPLACE PROCEDURE outer IS
BEGIN
  inner;
EXCEPTION
  WHEN OTHERS THEN
    RAISE_APPLICATION_ERROR(-20002, 'Outer: ' || SQLERRM, TRUE);  -- 保留栈
END;
/

10. 事务

10.1 自治

CREATE OR REPLACE PROCEDURE log_msg(p_msg VARCHAR2) IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO log VALUES (p_msg, SYSTIMESTAMP);
  COMMIT;
END;
/

10.2 嵌套

CREATE OR REPLACE PROCEDURE parent IS
  PROCEDURE child IS
  BEGIN
    SAVEPOINT sp1;
    -- 子操作
  END;
BEGIN
  child;
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK TO sp1;
    RAISE;
END;
/

11. 性能

11.1 BULK

DECLARE
  TYPE emp_tab IS TABLE OF employees%ROWTYPE;
  v_emp emp_tab;
BEGIN
  SELECT * BULK COLLECT INTO v_emp FROM employees LIMIT 10000;
  
  FORALL i IN 1..v_emp.COUNT
    INSERT INTO emp_copy VALUES v_emp(i);
END;
/

详细见：Oracle BULK COLLECT 与 FORALL 详解。

11.2 Native

ALTER SESSION SET PLSQL_CODE_TYPE = NATIVE;

11.3 INLINE

CREATE OR REPLACE PROCEDURE caller IS
  PRAGMA INLINE(small_proc, 'YES');
BEGIN
  small_proc;
END;
/

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

12. 安全

12.1 DBMS_ASSERT

-- 防 SQL 注入
EXECUTE IMMEDIATE 'SELECT * FROM ' || DBMS_ASSERT.QUALIFIED_SQL_NAME(p_table);
EXECUTE IMMEDIATE 'SELECT * FROM t WHERE name = ' || DBMS_ASSERT.ENQUOTE_LITERAL(p_name);

详细见：Oracle PL/SQL 安全编程详解。

12.2 AUTHID

CREATE PROCEDURE p AUTHID CURRENT_USER IS ...
-- 调用者权限

13. 应用场景

13.1 框架

- 通用 CRUD
- 反射
- 元编程

13.2 动态

- 任意表
- 任意查询
- 任意操作

13.3 集成

- 回调
- 事件
- 队列

13.4 工具

- 代码生成
- 自动化
- 模板

14. 最佳实践

  1. 动态 SQL 谨慎：安全
  2. DBMS_ASSERT：防注入
  3. RESULT_CACHE：性能
  4. BULK：批量
  5. 异常完整：日志
  6. 自治事务：独立
  7. Native：计算
  8. 模块化：清晰
  9. 测试：覆盖
  10. 文档：说明

15. 参考资料

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