[color=blue]
由于本人水平有限,参考前人成果(基本上没有任何变动),网上多数存储过程写的都一样,
故不知真正的作者是谁,因而没有注明出处,望海涵:
建立游标:
create or replace package pkg_cursor as
type cur is ref cursor;
end pkg_cursor;
建立存储过程:
create or replace procedure splitPage(tableName in varchar2,--表名
condition in varchar2,--查询条件
orderColumn in varchar2,--排序的列
orderStyle in varchar2,--排序的方式
currentPage in out number,--当前第几页
pageSize in out number,--每页显示的记录数
recordCount out number,--记录总数
pageCount out number,--页数
c out pkg_cursor.cur --结果集
)
as
sqls varchar2(1000);--sql
startRecord number(4);--开始记录数
endRecord number(4);--结束记录数
begin
--查询总记录数
sqls:='select to_number(count(*)) from '||tableName;
execute immediate sqls into recordCount;
--dbms_output.put_line(recordCount);
--验证一共多少页
if mod(recordCount,pageSize)=0 then
pageCount:=recordCount/pageSize;
else
pageCount:=floor(recordCount/pageSize)+1;
end if;
--dbms_output.put_line(pageCount);
--获得当前第几页
if currentPage < 1 then
currentPage:=1;
end if;
if currentPage >pageCount then
currentPage:=pageCount;
end if;
--dbms_output.put_line(currentPage);
--实现分页查询
startRecord:=(currentPage-1)*pageSize +1;
endRecord:=currentPage*pageSize;
--dbms_output.put_line(startRecord);
--dbms_output.put_line(endRecord);
sqls:='select * from ( select rownum r,a.* from (select * from ' || tableName ;
--加入条件字符串,例如 ' and createTime=''2010-03-31'' '
if condition is not null then
sqls:=sqls || ' where 1=1 ' || condition;
end if;
--加入排序
if orderColumn is not null then
sqls:=sqls|| ' order by ' || orderColumn || ' ' || orderStyle;
end if;
--加入 分页
sqls:=sqls || ')a where rownum >='|| startRecord || ' and rownum <= '|| endRecord || ' ) ';
--dbms_output.put_line(sqls);
open c for sqls;
end splitPage;
pl/sql调试:
declare
tableName varchar2(100) :='news_object';
condition varchar2(50):=' and id >100 and createTime > ''2011-03-31'' ';
orderColumn varchar2(50):= ' createTime ';
orderStyle varchar2(50):= ' desc';
currentPage number(4) :=1;
pageSize number(4) :=10;
recordCount number(4);
pageCount number(4);
c pkg_cursor.cur;
begin
splitPage(tableName, condition , orderColumn ,orderStyle ,currentPage , pageSize ,
recordCount , pageCount , c);
end;
[/color]
由于本人水平有限,参考前人成果(基本上没有任何变动),网上多数存储过程写的都一样,
故不知真正的作者是谁,因而没有注明出处,望海涵:
建立游标:
create or replace package pkg_cursor as
type cur is ref cursor;
end pkg_cursor;
建立存储过程:
create or replace procedure splitPage(tableName in varchar2,--表名
condition in varchar2,--查询条件
orderColumn in varchar2,--排序的列
orderStyle in varchar2,--排序的方式
currentPage in out number,--当前第几页
pageSize in out number,--每页显示的记录数
recordCount out number,--记录总数
pageCount out number,--页数
c out pkg_cursor.cur --结果集
)
as
sqls varchar2(1000);--sql
startRecord number(4);--开始记录数
endRecord number(4);--结束记录数
begin
--查询总记录数
sqls:='select to_number(count(*)) from '||tableName;
execute immediate sqls into recordCount;
--dbms_output.put_line(recordCount);
--验证一共多少页
if mod(recordCount,pageSize)=0 then
pageCount:=recordCount/pageSize;
else
pageCount:=floor(recordCount/pageSize)+1;
end if;
--dbms_output.put_line(pageCount);
--获得当前第几页
if currentPage < 1 then
currentPage:=1;
end if;
if currentPage >pageCount then
currentPage:=pageCount;
end if;
--dbms_output.put_line(currentPage);
--实现分页查询
startRecord:=(currentPage-1)*pageSize +1;
endRecord:=currentPage*pageSize;
--dbms_output.put_line(startRecord);
--dbms_output.put_line(endRecord);
sqls:='select * from ( select rownum r,a.* from (select * from ' || tableName ;
--加入条件字符串,例如 ' and createTime=''2010-03-31'' '
if condition is not null then
sqls:=sqls || ' where 1=1 ' || condition;
end if;
--加入排序
if orderColumn is not null then
sqls:=sqls|| ' order by ' || orderColumn || ' ' || orderStyle;
end if;
--加入 分页
sqls:=sqls || ')a where rownum >='|| startRecord || ' and rownum <= '|| endRecord || ' ) ';
--dbms_output.put_line(sqls);
open c for sqls;
end splitPage;
pl/sql调试:
declare
tableName varchar2(100) :='news_object';
condition varchar2(50):=' and id >100 and createTime > ''2011-03-31'' ';
orderColumn varchar2(50):= ' createTime ';
orderStyle varchar2(50):= ' desc';
currentPage number(4) :=1;
pageSize number(4) :=10;
recordCount number(4);
pageCount number(4);
c pkg_cursor.cur;
begin
splitPage(tableName, condition , orderColumn ,orderStyle ,currentPage , pageSize ,
recordCount , pageCount , c);
end;
[/color]