Oracle分頁預存程序

標籤：

CREATE OR REPLACE Procedure Soa_Getpager(p_Pagesize       Int, --每頁記錄數                                         p_Pageno         Int, --當前頁碼,從 1 開始                                         p_Sqlcols        Varchar2, --選擇列                                         p_Sqlfrom        Varchar2, --表名OR串連查詢                                         p_Sqlwhere       Varchar2, --查詢子句                                         p_Sqlorderby     Varchar2, --排序                                         p_Outrecordcount Out Int, --返回總記錄數                                         p_Outpagecount   Out Int, --返回總頁數                                         Result           Out Sys_Refcursor) As  v_Sql       Varchar2(3000);  p_Sqlselect Varchar2(3000);  v_Count     Int;  v_Heirownum Int;  v_Lowrownum Int;Begin  ----拼接SQL查詢語句  p_Sqlselect := ‘select ‘ || p_Sqlcols || ‘ from ‘ || p_Sqlfrom || ‘ ‘ ||                 p_Sqlwhere || ‘ ‘ || p_Sqlorderby;  Dbms_Output.Put_Line(p_Sqlselect);  ----取記錄總數  v_Sql := ‘select count(1) from (‘ || p_Sqlselect || ‘)‘;  Execute Immediate v_Sql    Into v_Count;  p_Outrecordcount := v_Count;  ----取總頁數  p_Outpagecount := p_Outrecordcount / p_Pagesize;  If p_Outpagecount = 0 Then    p_Outpagecount := 1;  End If;  ----執行分頁查詢  v_Heirownum := p_Pageno * p_Pagesize;  v_Lowrownum := v_Heirownum - p_Pagesize + 1;  v_Sql := ‘SELECT *            FROM (                  SELECT A.*, rownum rn                  FROM  ( ‘ || p_Sqlselect || ‘) A                  WHERE rownum <= ‘ || To_Char(v_Heirownum) || ‘                 ) B            WHERE rn >= ‘ || To_Char(v_Lowrownum);  --注意對rownum別名的使用,第一次直接用rownum,第二次一定要用別名rn  Dbms_Output.Put_Line(v_Sql);  Open Result For v_Sql;End Soa_Getpager;

 

Oracle分頁預存程序