動態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;