oracle高效分頁存儲(chǔ)過程代碼
create or replace package p_page is
-- author : pharaohs
-- created : 2006-4-30 14:14:14
-- purpose : 分頁過程
type type_cur is ref cursor --定義游標(biāo)變量用于返回記錄集
procedure pagination(
pindex in number --分頁索引
psql in varchar2 --產(chǎn)生dataset的sql語句
psize in number --頁面大小
pcount out number --返回分頁總數(shù)
v_cur out type_cur --返回當(dāng)前頁數(shù)據(jù)記錄
)
procedure pagerecordscount(
psqlcount in varchar2 --產(chǎn)生dataset的sql語句
prcount out number --返回記錄總數(shù)
)
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
------------------------------------------------------------取分頁總數(shù)
v_sql := ' select count() from (' || psql || ' )'
ute immediate v_sql into v_count
pcount := ceil(v_count/psize)
------------------------------------------------------------顯示任意頁內(nèi)容
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 --返回記錄總數(shù)
end pagerecordscount
--
end p_page
/
|
