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
/
|
