萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

基於 Oracle 的高效能動態 SQL 程式開發實戰

靜態 SQL 與動態 SQL:編譯時機的本質差異

  • 靜態 SQL(Early Binding):編譯期確定 SQL 文本,效能最佳,適用於固定邏輯。
  • 動態 SQL(Late Binding):執行期構建 SQL 字串,靈活支援可變條件、DDL、未知表名,但需額外解析開銷。

動態 SQL 核心語法:EXECUTE IMMEDIATE

EXECUTE IMMEDIATE dynamic_sql_string
  [USING bind_arguments]
  [RETURNING INTO out_arguments];

關鍵點:

  1. 動態 SQL 包含 DDL 及帶參數的 DML。
  2. USING 綁定輸入參數(對應佔位符 :1, :2...)。
  3. RETURNING INTO 捕獲 DML 的 RETURNING 子句結果。
  4. 效能代價:每次執行皆需解析,高頻場景慎用或採用 DBMS_SQL 緩存游標。

實戰範例:EMP 表 CRUD 操作

場景一:動態建表與插入資料(DDL)

CREATE OR REPLACE PROCEDURE create_table AS
BEGIN
  EXECUTE IMMEDIATE 'CREATE TABLE emp (
    id NUMBER, name VARCHAR2(10), salary NUMBER)';
  INSERT INTO emp VALUES (100, 'Jacky', 5600);
  INSERT INTO emp VALUES (101, 'Rose', 3000);
  INSERT INTO emp VALUES (102, 'John', 4500);
END;

場景二:單行查詢綁定變數(動態 SELECT)

CREATE OR REPLACE PROCEDURE find_info(p_id NUMBER) AS
  v_name VARCHAR2(10); v_salary NUMBER;
BEGIN
  EXECUTE IMMEDIATE
    'SELECT name, salary FROM emp WHERE id = :1'
    USING p_id
    RETURNING INTO v_name, v_salary;
  DBMS_OUTPUT.PUT_LINE(v_name || '的收入為:' || v_salary);
EXCEPTION WHEN NO_DATA_FOUND THEN
  DBMS_OUTPUT.PUT_LINE('找不到相應資料');
END;

場景三:多行查詢使用 REF CURSOR(動態游標)

CREATE OR REPLACE PROCEDURE find_emp(p_salary NUMBER) AS
  r_emp emp%ROWTYPE;
  TYPE c_type IS REF CURSOR;
  c1 c_type;
BEGIN
  OPEN c1 FOR 'SELECT * FROM emp WHERE salary > :1' USING p_salary;
  LOOP
    FETCH c1 INTO r_emp; EXIT WHEN c1%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('ID: ' || r_emp.id || ' 姓名: ' || r_emp.name);
  END LOOP;
  CLOSE c1;
END;

進階技巧與最佳實踐

  • 防注入:永遠使用 USING 綁定變數,拒絕字串拼接。
  • 複雜邏輯改用 DBMS_SQL:支援描述、定義、批次綁定,適合未知欄位數量的動態查詢。
  • 緩存游標:重複執行相同動態 SQL 時,DBMS_SQL 可避免重複解析。
  • 除錯利器:DBMS_UTILITY.FORMAT_ERROR_BACKTRACE 定位動態 SQL 錯誤行號。

結語

動態 SQL 是雙面刃:賦予極大靈活性,卻以解析開銷為代價。掌握 EXECUTE IMMEDIATE 與 DBMS_SQL 兩套機制,依場景選擇,並嚴守綁定變數原則,方能開發出安全高效的 PL/SQL 程式。

copyright © 萬盛學電腦網 all rights reserved