[Procedure]Oracle之分頁儲存過程

關於分頁儲存過程中使用到的rownum陷阱解析：

http://www.blogjava.net/CONANS/ARTICLES/219693.HTML

create or replace procedure pd_page_query

(

in_colnames         varchar2,            --列名

in_tablename        varchar2,            --表名

in_where            varchar2,            --查詢條件

in_order            varchar2,            --排序條件

in_curpage          number,              --當前頁

in_pagesize         number,              --每頁記錄數

out_totalrecords    out number,          --總記錄數

out_totalpages      out number,          --總頁數

out_query_result    out sys_refcursor    --返回的結果集

)

------------------------------------------------------------------------------

---功能描述：透過傳入的查詢條件返回對應的分頁查詢結果

---時間：    2013-07-10

---作者：    sunw

---版本：    1.0 beta

------------------------------------------------------------------------------

is

v_start_record int;                --開始顯示的記錄條數

v_end_record   int;                --結束顯示的記錄條數

v_pagesize     int;                --每頁記錄數

v_curpage      int;                --當前頁

v_colnames     varchar2(2000);     --列名

v_where        varchar2(2000);     --查詢條件

v_order        varchar2(2000);     --排序條件

v_count_sql    varchar2(2000);     --記錄數語句

v_select_sql   varchar2(2000);     --分頁語句

begin

---------------------------------------------------開始判斷條件---------------------------------------------------

  --如果沒有表名,則直接返回異常訊息

  --如果沒有欄位,則表示全部欄位

  if in_colnames is not null then

    v_colnames:=in_colnames;

  else

    v_colnames:='*';

  end if;

  --如果沒有where條件

  if in_where is not null  then

    v_where:=' WHERE '||in_where;

  end if;

  --如果沒有order by條件

  if in_order is null then

    v_order:='';

  else

    v_order:=' ORDER BY '||in_order;

  end if;

  --如果未指定查詢頁,則預設為首頁

  if in_curpage is null or in_curpage<1 then

    v_curpage:=1;

  else

    v_curpage:=in_curpage;

  end if;

  --如果未指定每頁記錄數,則預設為10條記錄

  if in_pagesize is null then

    v_pagesize:=10;

  else

    v_pagesize:=in_pagesize;

  end if;

---------------------------------------------------開始資料處理----------------------------------------------------

  --查詢總條數

  v_count_sql:='select count(*) from '||in_tablename||v_where;

  --執行查詢,得到out_totalrecords輸出結果

  execute immediate v_count_sql into out_totalrecords;

  --輸出查詢語句

  dbms_output.put_line('查詢總記錄數sql=>'||v_count_sql);

  dbms_output.put_line('查詢總記錄數='||out_totalrecords);

  --得到總頁數,並進行處理

  if mod(out_totalrecords,in_pagesize)=0 then

    out_totalpages:=out_totalrecords/in_pagesize;

  else

    out_totalpages:=floor(out_totalrecords/in_pagesize)+1;

  end if;

  --如果傳入的當前頁大於最大頁

  if in_curpage>out_totalpages then

    v_curpage:=out_totalpages;

  end if;

  --設定開始結束的記錄數

  v_start_record:=(v_curpage-1)*v_pagesize+1;

  v_end_record:=v_curpage*v_pagesize;

---------------------------------------------------開始構造分頁查詢語句----------------------------------------------

  --構造核心查詢語句

  v_select_sql:='(select '||v_colnames||' from '||in_tablename||v_where||v_order||') t';

  --進行完整的動態sql語句拼寫

  v_select_sql:='select * from '||

                '('||

                'select t.*,rownum rn '||

                ' from '||

                v_select_sql||

                ' where rownum<='||v_end_record||

                ')'||

                ' where rn>='||v_start_record;

  --列印完整分頁查詢語句

  dbms_output.put_line('查詢sql=>'||v_select_sql);

  open out_query_result for v_select_sql;

end pd_page_query;