CREATE OR REPLACE PROCEDURE pro_Pagination(
Pindex in number,
Psql in string,
Psize in number,
Pcount out number,
outCursor out SYS_REFCURSOR
)
AS
v_sql string(32000);
v_count number;
v_Plow number;
v_Phei number;
-- v_string varchar2(1000);
-- v_col string(8000);
-- v_col_mid string(40);
-- v_col_count int;
Begin
------------------------------------------------------------取分页总数
v_sql := 'select count(*) from (' || replace(Psql,chr(38),'') || ')';
execute immediate v_sql into v_count;
--解析表名
/* select substr(''|| Psql ||'',instr(''|| Psql ||'','from')+4,
length(''|| Psql ||'')-instr(''|| Psql ||'','from')-5) into v_string from dual;
select count(*) into v_col_count from user_col_comments where table_name=''||upper(trim(v_string))||'';
for i in 1..v_col_count loop
select col into v_col_mid from (
select t.column_name||',' col,rownum rm from user_col_comments t where table_name=''||upper(trim(v_string))||'')
where rm=i;
v_col:=v_col||v_col_mid;
end loop;
v_col:=substr(v_col,1,length(v_col)-1);*/
------------------------------------------------------------显示任意页内容
Pcount := ceil(v_count/Psize);
v_Phei := Pindex * Psize + Psize;
v_Plow := v_Phei - Psize + 1;
--Psql := 'select rownum rm,t.* from gridh_pole t' ; --要求必须包含rownum字段
v_sql := 'select * from (' || Psql || ') where rm between ' || v_Plow || ' and ' || v_Phei ;
--'||v_col||'
open outCursor for v_sql;
End pro_Pagination;
--**************************************************************************************