==========我的版本==================================
create or replace package CutPage is
type ref_cur is ref cursor ;
procedure CutPage(
table_name in varchar2 , --表名
query_partition in varchar2 , --查询条件
order_partition in varchar2 , --排序条件
pageSize in Integer , --每页显示记录数
pageNo in Integer , --第几页
real_records out Integer , --返回的总记录数
real_pageNo out integer , --真实的页数
all_cur out ref_cur --记录集
) ;
procedure CutPage2(
p_sql in varchar2 , --查询集合
p_query_partition in varchar2 , --查询条件
p_order_partition in varchar2 , --排序条件
p_page_size in Integer , --每页显示记录数
p_page_no in Integer , --第几页
p_all_records out Integer , --返回的总记录数
p_all_pageNo out integer , --真实的页数
r_cur out ref_cur --记录集
) ;
end CutPage ;
create or replace package body CutPage is
procedure CutPage(
table_name in varchar2 , --表名
query_partition in varchar2 , --查询条件
order_partition in varchar2 , --排序条件
pageSize in Integer , --每页显示记录数
pageNo in Integer , --第几页
real_records out Integer , --总记录数
real_pageNo out integer , --真实的页数
all_cur out ref_cur --记录集
) is
rownum_begin Integer ; -- 起始ROWNUM
rownum_end Integer ; -- 结束ROWNUM
all_records Integer ; -- 全部条数
all_pageno Integer ; -- 全部页数
sql_str1 varchar2(2000) ;--SQL查询语句
sql_str2 varchar2(2000) ;--SQL查询语句
ErrorParaments Exception ; --参数异常
begin
if table_name is null then
raise ErrorParaments ;
end if ;
rownum_begin := pageSize * (pageNo - 1) ;
rownum_end := pageSize * (pageNo - 1) + pageSize ;
--统计总条数 动态执行SQL
sql_str1 := 'select count(rownum) from ' || table_name;
execute immediate sql_str1 into all_records ;
--统计总页数 算法
if all_records = 0 then
all_pageno := 0 ;
elsif all_records <= pageSize then
all_pageno := 1 ;
elsif all_records mod pageSize = 0 then
all_pageno := all_records / pageSize ;
else
all_pageno := (all_records - (all_records mod pageSize ))/ pageSize + 1 ;
end if ;
--赋值打印分页信息
dbms_output.put_line('全部 :' || all_records);
dbms_output.put_line('全部页数:' || all_pageno);
real_records := all_records ;
real_pageNo := all_pageno ;
--组装分页SQL 语句
sql_str2 := 'select info.* from '||
'(select '||table_name||'.* , rownum as r_num from '|| table_name ||' where '|| query_partition ||
' and rownum <= ' || rownum_end || ' order by '|| order_partition ||')info' ||
' where info.r_num > '|| rownum_begin ;
dbms_output.put_line(sql_str2);
open all_cur for sql_str2 ;
exception
when ErrorParaments then
dbms_output.put_line('参数输入格式不对 或者 格式异常');
end CutPage ;
procedure CutPage2(
p_sql in varchar2 , --查询集合
p_query_partition in varchar2 , --查询条件
p_order_partition in varchar2 , --排序条件
p_page_size in Integer , --每页显示记录数
p_page_no in Integer , --第几页
p_all_records out Integer , --返回的总记录数
p_all_pageNo out integer , --真实的页数
r_cur out ref_cur --记录集
) is
v_sql varchar2(1000) ; --查询总记录数
ErrorParaments Exception ; --参数异常
rownum_begin Integer ; -- 起始ROWNUM
rownum_end Integer ; -- 结束ROWNUM
str_query_partition varchar2(1000) ;
str_order_partition varchar2(1000) ;
begin
rownum_begin := p_page_size * (p_page_no - 1) ;
rownum_end := p_page_size * (p_page_no - 1) + p_page_size ;
v_sql := 'select count(rownum) from '|| p_sql;
execute immediate v_sql into p_all_records ;
p_all_pageNo := ceil(p_all_records / p_page_size) ;
dbms_output.put_line('全部 :' || p_all_records);
dbms_output.put_line('全部页数:' || p_all_pageNo);
if p_query_partition is null then
str_query_partition := '1 = 1';
else
str_query_partition := p_query_partition ;
end if ;
if p_order_partition is null then
str_order_partition := ' ';
else
str_order_partition := p_order_partition ;
end if ;
v_sql := 'select * from '||
'(select t.* , rownum as r_num from '|| p_sql ||
't where '||str_query_partition ||' and ' ||
' rownum < '||rownum_end ||str_order_partition||
')info where info.r_num >= '|| rownum_begin ;
dbms_output.put_line(v_sql);
open r_cur for v_sql ;
exception
when ErrorParaments then
dbms_output.put_line('参数输入格式不对 或者 格式异常');
end CutPage2 ;
end Cutpage ;
==========老版本================================
create or replace procedure prc_query(
p_tablename in varchar2,--表明
p_strwhere in varchar2, --条件
p_ordercolumn in varchar2, --排序的列
p_orderstyle in varchar2, --排序方式
p_pageSize in out number ,--每页显示的记录条数
p_curPage in out number, --当前页
p_totalRecords out number ,--总记录数
p_totalpages out number, --总页数
v_cur out find.cur)--返回的结果集
is
v_sql varchar2(1000) :=''; --sql语句
v_startrecord number(4);--开始显示的记录条数
v_endrecord number(4);--结束显示的记录条数
begin
v_sql := 'select to_number(count(*)) from '||p_tablename || ' where 1=1 ';
if p_strwhere is not null or p_strwhere <> '' then
v_sql := v_sql || p_strwhere;
end if;
execute immediate v_sql into p_totalRecords;
--验证页面记录大小
if p_pageSize <0 then
p_pageSize:=0;
end if;
--根据页面的大小计算总页数
/* if mod(p_totalRecords,p_pageSize)=0 then
p_totalpages:=p_totalRecords/p_pageSize;
else
p_totalpages:=p_totalRecords/p_pageSize+1;
end if;*/
p_totalRecords:= ceil(p_totalRecords/p_pageSize);
--验证页号
if p_curPage <1 then
p_curPage :=1;
end if;
if p_curPage > p_totalpages then
p_curPage:=p_totalpages;
end if;
--实现分页查询
v_startrecord := (p_curPage-1)*p_pageSize+1;
v_endrecord := p_curPage * p_pageSize;
v_sql:='select * from (select A.*,rownum r from ' ||
'(select * from '|| p_tablename;
if p_strwhere is not null or p_strwhere <> '' then
v_sql := v_sql || ' where 1=1 ' || p_strwhere;
end if;
if p_ordercolumn is not null or p_ordercolumn <> '' then
v_sql := v_sql || ' order by '|| p_ordercolumn || ' ' || p_orderstyle;
end if;
v_sql := v_sql || ')A where rownum <= ' || v_endrecord || ') B where r>= '
|| v_startrecord;
dbms_output.put_line(v_sql);
open v_cur for v_sql;
end;
------------兄弟连----------------
/*分页存储过程：
1、参数说明：
tablename：表名
strwhere：查询条件
pagesize：每页显示记录数
pageno： 第几页
ordercol：需要排序的列
totalrecords：总记录数(输出)
totalpage：总页数(输出)
v_cur out 引用游标(输出参数)*/
create or replace package devidpage
is
type cur is ref cursor;
procedure devidpages
(
tablename varchar2,
strwhere varchar2,
pagesize number,
pageno number,
ordercol varchar2,
totalrecords out number,
totalpage out int,
v_cur out cur
);
end devidpage;
create or replace package body devidpage
is
procedure devidpages
(
tablename varchar2,
strwhere varchar2,
pagesize number,
pageno number,
ordercol varchar2,
totalrecords out number,
totalpage out int,
v_cur out cur
)
is
sqlstr varchar2(2000);
sqlstr1 varchar2(2000);
c_cur cur;
begin
if strwhere is null and ordercol is null then
sqlstr1:='select count(*) from '||tablename||'';
sqlstr:='select * from(select t.*,rownum ru from'||' '|| tablename ||' '||'t )where ru>('||pageno||'-1)*'||pagesize ||'and ru<='||pagesize||'*'||pageno||'';
elsif strwhere is not null and ordercol is null then
sqlstr1:='select count(*) from '||tablename||' where '||strwhere||'';
sqlstr:='select * from(select t.*,rownum ru from'||' '|| tablename ||' '||'t where '||strwhere||')where ru>('||pageno||'-1)*'||pagesize ||'and ru<='||pagesize||'*'||pageno||'';
elsif strwhere is null and ordercol is not null then
sqlstr1:='select count(*) from '||tablename||'';
sqlstr:='select * from(select t.*,rownum ru from'||' '|| tablename ||' '||'t )where ru>('||pageno||'-1)*'||pagesize ||'and ru<='||pagesize||'*'||pageno||' order by '||ordercol;
else
sqlstr1:='select count(*) from '||tablename||' where '||strwhere||'';
sqlstr:='select * from(select t.*,rownum ru from'||' '|| tablename ||' '||'t where '||strwhere||' order by '||ordercol||')where ru>('||pageno||'-1)*'||pagesize ||'and ru<='||pagesize||'*'||pageno||'';
end if;
open v_cur for sqlstr;
open c_cur for sqlstr1;
begin
loop
fetch c_cur into totalrecords;
exit when c_cur%notfound;
end loop;
end;
select ceil(totalrecords/pagesize) into totalpage from dual;
end devidpages;
end devidpage;
感谢观看兄弟连的分享
