Oracle分页查询脚本
create or replace package jssb.pck_page istype T_Page is ref cursor; --定义游标变量用于返回记录集
procedure getpagerecord(
pindex in number, --分页索引
psql in varchar2, --产生dataset的sql语句
psize in number, --页面大小
pcount out number, --返回分页总数
v_cur out T_Page --返回当前页数据记录
);
end pck_page;
view plaincopy
create or replace procedure jssb.p_app_GetCount(
p_sql in varchar2,
p_count out number
)
as
v_sql varchar2(1000);
v_prcount number;
begin
v_sql := 'select count(*) from (' || p_sql || ')';
execute immediate v_sql into v_prcount;
p_count := v_prcount; --返回记录总数
end p_app_GetCount;
view plaincopy
create or replace procedure jssb.p_app_page(
p_pagesql in varchar2,--产生分页的sql语句 sql要求必须包含rownum字段且其别名为rn,如例子:select rownum rn,t.* from test t
p_pagesize in number, --每页显示的条数
p_pageindex in number,--显示页的索引 从0开始
p_totalcount out number,--总条数
p_pagecount out number,--总页数
p_currentpagedata out pck_page.T_Page --返回当前页的数据
)
as
begin
pck_page.getpagerecord(p_pageindex,p_pagesql,p_pagesize,p_pagecount,p_currentpagedata);
p_app_getcount(p_pagesql,p_totalcount);
end;
view plaincopy
create or replace package body jssb.pck_page is
procedure getpagerecord(
pindex in number,
psql in varchar2,
psize in number,
pcount out number,
v_cur out T_Page
)
as
v_sql varchar2(1000);
v_count number;
v_plow number;
v_phei number;
begin
v_sql := 'select count(*)from (' || psql || ')';
execute immediate v_sql into v_count;
pcount := ceil(v_count/psize);
v_phei := pindex * psize + psize;
v_plow := v_phei - psize + 1;
--psql := select rownum rn,t.* from test t ; --要求必须包含rownum字段
v_sql := 'select * from (' || psql || ') where rn between ' || v_plow || ' and ' || v_phei ;
open v_cur for v_sql;
end getpagerecord;
end pck_page;
页:
[1]