oracle万能分页代码,oracle高效分页存储过程代码

oracle高效分页存储过程代码

create or replace package p_page is

-- author : pharaohs

-- created : 2006-4-30 14:14:14

-- purpose : 分页过程

type type_cur is ref cursor --定义游标变量用于返回记录集

procedure pagination(

pindex in number --分页索引

psql in varchar2 --产生dataset的sql语句

psize in number --页面大小

pcount out number --返回分页总数

v_cur out type_cur --返回当前页数据记录

)

procedure pagerecordscount(

psqlcount in varchar2 --产生dataset的sql语句

prcount out number --返回记录总数

)

end p_page

/

create or replace package body p_page is

procedure pagination(

pindex in number

psql in varchar2

psize in number

pcount out number

v_cur out type_cur

)

as

v_sql varchar2(1000)

v_count number

v_plow number

v_phei number

begin

------------------------------------------------------------取分页总数

v_sql := ' select count() from (' || psql || ' )'

ute 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 zzda t' --要求必须包含rownum字段

v_sql := ' select from (' || psql || ' ) where rn between ' || v_plow || ' and ' || v_phei

open v_cur for v_sql

end pagination

--

procedure pagerecordscount(

psqlcount in varchar2

prcount out number

)

as

v_sql varchar2(1000)

v_prcount number

begin

v_sql := ' select count() from (' || psqlcount || ' )'

ute immediate v_sql into v_prcount

prcount := v_prcount --返回记录总数

end pagerecordscount

--

end p_page

/