Oracle分页过程
?
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);--分页查询的SQL sqlstr2 varchar2(1500);--求总页数据的SQL orderbystr varchar2(150);--排序串 pageTotal number;--总页数 tmpCount integer;begin--========求总页数================================sqlstr2:='SELECT COUNT(*) FROM '||tb||condition;execute immediate sqlstr2 into tmpCount;--================================================pages:=tmpCount;if(orderby=0) thenorderbystr:=' ORDER BY '||col||' ASC';elseorderbystr:=' 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;
?