Oracle分页过程

CREATE OR REPLACE  PROCEDURE "CCUSER"."PRO_PAGETEST" (
tb varchar2,--表名
col varchar2,---按该列来进行分页
collist varchar2,--要查询出的字段列表,*表示全部字段
condition varchar2,--查询条件
orderby integer,--排序 0=升序,1=降序
pagesize integer,--指定每页记录条数
page integer,--指定页
pages out integer--总记录数

)
as
 sqlstr1 varchar2(4000);--分页查询的ＳＱＬ
 sqlstr2 varchar2(1500);--求总页数据的ＳＱＬ
 orderbystr varchar2(150);--排序串
 pageTotal number;--总页数
 tmpCount integer;
 type ref_cur is ref cursor;--定义游标类型
 v_tabCursor ref_cur;--定义游标变量
 --halfpageTotal integer;--半页数

begin

--========求总页数================================
sqlstr2:='SELECT COUNT(*) FROM '||tb||condition;
execute immediate sqlstr2 into tmpCount;
--================================================
pages:=tmpCount;
if(orderby=0) then
orderbystr:=' ORDER BY '||col||' ASC';
else
orderbystr:=' ORDER BY '||col||' DESC';
end if;

--=======求页数===================================
pageTotal:= pages/pagesize;
if(pagesize/2>mod(pages,pagesize)) then
pageTotal:=pageTotal+1;
end if;
--halfpageTotal:=pageTotal/2;
--================================================
sqlstr1:='SELECT '||collist||' FROM (SELECT ROWNUM rownumindex,'||collist||' FROM '||tb||' '||condition||' and  rownumindex<='||(pagesize*page)||orderbystr||') PAGESTBTMP where PAGESTBTMP.rownumindex>'||(pagesize*(page-1))||' and PAGESTBTMP.rownumindex<='||(pagesize*page);

OPEN v_tabCursor FOR sqlstr1;
end;

 

修正

 

CREATE OR REPLACE PACKAGE PACK_PRO_PATINFO   AS   
TYPE PAYINFO_CURSOR IS REF CURSOR;   
end PACK_PRO_PATINFO; 

CREATE OR REPLACE  PROCEDURE PRO_PAYINFO (
tb varchar2,--表名
col varchar2,---按该列进行分页排序
colnewlist varchar2,--子查询临时表列
collist varchar2,--要查询出的字段列表,*表示全部字段
condition varchar2,--查询条件
orderby integer,--排序 0=升序,1=降序
pagesize integer,--指定每页记录条数
page integer,--指定页
pages out integer,--总记录数
p_corsor out PACK_PRO_PATINFO.PAYINFO_CURSOR
)
as
 sqlstr1 varchar2(4000);--分页查询的ＳＱＬ
 sqlstr2 varchar2(1500);--求总页数据的ＳＱＬ
 orderbystr varchar2(150);--排序串
 pageTotal number;--总页数
 tmpCount integer;

begin

--========求总页数================================
sqlstr2:='SELECT COUNT(*) FROM '||tb||condition;
execute immediate sqlstr2 into tmpCount;
--================================================
pages:=tmpCount;
if(orderby=0) then
orderbystr:=' ORDER BY '||col||' ASC';
else
orderbystr:=' ORDER BY '||col||' DESC';
end if;

--=======求页数===================================
--pageTotal:= pages/pagesize;
--if(pagesize/2>mod(pages,pagesize)) then
--pageTotal:=pageTotal+1;
--end if;
--halfpageTotal:=pageTotal/2;
--================================================
sqlstr1:='SELECT '||colnewlist||' FROM (SELECT ROWNUM NO,'||collist||' FROM '||tb||' '||condition||' and  ROWNUM<='||(pagesize*page)||orderbystr||') T where T.NO>'||(pagesize*(page-1))||' and T.NO<='||(pagesize*page);

OPEN p_corsor FOR sqlstr1;
end;