oracle分页查询别名,Oracle分页查询脚本

create or replace package jssb.pck_page is

type 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;

[sql] 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;

[sql] 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;

[sql] 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;