CREATE OR REPLACE Procedure Soa_Getpager(p_Pagesize       Int, --每頁(yè)記錄數(shù)p_Pageno         Int, --當(dāng)前頁(yè)碼,從 1 開(kāi)始p_Sqlcols        Varchar2, --選擇列p_Sqlfrom        Varchar2, --表名OR連接查詢p_Sqlwhere       Varchar2, --查詢子句p_Sqlorderby     Varchar2, --排序p_Outrecordcount Out Int, --返回總記錄數(shù)p_Outpagecount   Out Int, --返回總頁(yè)數(shù)Result           Out Sys_Refcursor) Asv_Sql       Varchar2(3000);p_Sqlselect Varchar2(3000);v_Count     Int;v_Heirownum Int;v_Lowrownum Int;
Begin----拼接SQL查詢語(yǔ)句p_Sqlselect := 'select ' || p_Sqlcols || ' from ' || p_Sqlfrom || ' ' ||p_Sqlwhere || ' ' || p_Sqlorderby;Dbms_Output.Put_Line(p_Sqlselect);----取記錄總數(shù)v_Sql := 'select count(1) from (' || p_Sqlselect || ')';Execute Immediate v_SqlInto v_Count;p_Outrecordcount := v_Count;----取總頁(yè)數(shù)p_Outpagecount := p_Outrecordcount / p_Pagesize;If p_Outpagecount = 0 Thenp_Outpagecount := 1;End If;----執(zhí)行分頁(yè)查詢v_Heirownum := p_Pageno * p_Pagesize;v_Lowrownum := v_Heirownum - p_Pagesize + 1;v_Sql := 'SELECT *FROM (SELECT A.*, rownum rnFROM  ( ' || p_Sqlselect || ') AWHERE rownum <= ' || To_Char(v_Heirownum) || ') BWHERE rn >= ' || To_Char(v_Lowrownum);--注意對(duì)rownum別名的使用,第一次直接用rownum,第二次一定要用別名rn
Dbms_Output.Put_Line(v_Sql);Open Result For v_Sql;End Soa_Getpager;

?

轉(zhuǎn)載于:https://www.cnblogs.com/huhangfei/p/5000771.html

總結(jié)

以上是生活随笔為你收集整理的Oracle分页存储过程的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。