本文節選之《劍破冰山-Oracle開發藝術》一書，喜歡的朋友可以在本站下載。由於動態SQL 特有的靈活性，我們很容易的按照某種共性去構造通用和重用功能的代碼，例如基於某個表的動態字段查詢；
但凡事有利有弊；首先動態SQL 語句無法在編譯前期檢查SQL 是否正確，必須等到運行期才會發現問題；其次靜態SQL 是一次解析，多次執行，雖然動態SQL 也可以使用綁定變量的方式，但是也會帶來一些意想不到的性能問題，例如綁定變量在SQL 要訪問的表存在數據傾斜時會提供錯誤的執行計劃；最後動態SQL 語句可讀性較差，比較難以維護。
下面我們就以比較經典的分頁功能為例：
CREATE OR REPLACE Procedure sp_exec_dynamic_page
(
i_tablename VARCHAR2, -- 表名 employees e,departments d
i_tablecolumn VARCHAR2, -- 查詢列 a.employee_id,b.department_name
i_where VARCHAR2, -- 查詢條件 b.department_name like 'S%'
i_ordercolumn VARCHAR2, -- 排序 b.department_name desc
i_pagesize NUMBER, -- 每頁大小 20
i_curpage NUMBER, -- 當前頁 6
o_rowcount OUT NUMBER, -- 返回總條數
o_pagecount OUT NUMBER, -- 返回總頁數
o_cursor OUT ref_cursor.t_RetDataSet -- 返回分頁結果集
)
IS
v_startrecord INT;
v_endrecord INT;
v_pagesize INT;
v_curpage INT;
v_tablecolumn VARCHAR2(2000);
v_where VARCHAR2(2000);
v_ordercolumn VARCHAR2(200);
v_count_sql VARCHAR2(2000);
v_select_sql VARCHAR2(2000);
BEGIN
-- 如果沒有表名稱，則直接返回異常消息
-- 如果沒有字段，則表示全部字段
IF i_tablecolumn IS NOT NULL THEN
v_tablecolumn:=i_tablecolumn;
ELSE
v_tablecolumn:=' * ';
END IF;
-- 可以沒有 WHERE 條件
IF i_where IS NOT NULL THEN
v_where:=' WHERE 1=1 AND '||i_where||' ';
ELSE
v_where:=' WHERE 1=1 ';
END IF;
-- 可以沒有ORDER BY 條件
IF i_ordercolumn IS NULL THEN
v_ordercolumn:=' ';
ELSE
v_ordercolumn:=' ORDER BY '||i_ordercolumn;
END IF;
-- 如果未指定查詢頁，則默認為首頁
IF i_curpage IS NULL OR i_curpage<1 THEN
v_curpage:=1;
ELSE
v_curpage:=i_curpage;
END IF;
-- 如果未指定每頁記錄數，則默認為10 條記錄
IF i_pagesize IS NULL THEN
v_pagesize:=10;
ELSE
v_pagesize:=i_pagesize;
END IF;
-- 查詢總條數
v_count_sql:='SELECT COUNT(*) FROM '||i_tablename||v_where;
-- 構造最核心的查詢語句
v_select_sql:='(SELECT '||v_tablecolumn||' FROM '||i_tablename||v_where||v_ordercolumn||') e';
-- 執行查詢, 查詢總條數
EXECUTE IMMEDIATE v_count_sql INTO o_rowcount;
DBMS_OUTPUT.PUT_LINE(' 查詢總條數SQL=>'||v_count_sql);
DBMS_OUTPUT.PUT_LINE(' 查詢總條數Count='||o_rowcount);
-- 得到總頁數，並進行處理
IF MOD(o_rowcount,i_pagesize)=0 THEN
o_pagecount:=o_rowcount/i_pagesize;
ELSE
o_pagecount:=FLOOR(o_rowcount/i_pagesize)+1;
END IF;
-- 如果當前頁大於最大頁數，則取最大頁數
IF i_curpage>o_pagecount THEN
v_curpage:=o_pagecount;
END IF;
-- 設置開始結束的記錄數
v_startRecord := (v_curpage - 1) * v_pagesize + 1;
v_endRecord := v_curpage * v_pagesize;
-- 進行完整的動態SQL 語句拼寫
v_select_sql:='SELECT * FROM '||
'( '||
' SELECT e.*,ROWNUM rn '||
' FROM '||
v_select_sql||
' WHERE ROWNUM<='||v_endRecord||
') '||
' WHERE rn>='||v_startRecord;
DBMS_OUTPUT.PUT_LINE(' 查詢SQL=>'||v_select_sql);
OPEN o_cursor FOR v_select_sql;
END;
