Oracle 分页存储过程

 

CREATE OR REPLACE PROCEDURE JrscPage(
  Pindex in number,
  Psql in varchar2,
  Psize in number,
  Prows out number,
  Pcount out number,
  v_cur out sys_refcursor
 )
 AS
  v_sql VARCHAR2(3000);
  v_count number;
  v_Plow number;
  v_Phei number;
 Begin
  ------------------------------------------------------------取分页总数
  v_sql := 'select count(*) from (' || Psql || ')';
  execute immediate v_sql into v_count;
  Prows := v_count;
  Pcount := ceil(v_count/Psize);  ---取大于的最小的整数
  ------------------------------------------------------------显示任意页内容
  v_Phei := Pindex * Psize + Psize;
  v_Plow := v_Phei - Psize + 1;
  ---Psql是在.net程序中拼成的形如：'select rownum  rn2005,* from cd_ssxl ' ;
  ---的语句，要求必须包含rownum字段，而且替代名必须为rn2005.
  ---如果想改成其他名字，需要在程序中和下面这个语句中都把rn2005 改成另外某个名字
---  v_sql := 'select * from (' || Psql || ') where  rn2005 between ' || v_Plow || ' and ' || v_Phei ;
 -- v_sql := 'SELECT * FROM ( SELECT rownum  rn2005,A.*  FROM ('||Psql||') A  WHERE rownum <= '||v_Phei||' ) B WHERE rn2005 >= '||v_Plow;
  -- v_sql := 'SELECT * FROM ( SELECT rownum  rn2005,A.*  FROM ('||Psql||') A ) WHERE rn2005 <= '||v_Phei||' and rn2005 >= '||v_Plow;
     v_sql := 'SELECT * FROM ( SELECT rownum  rn2005,A.*  FROM ('||Psql||') A ) WHERE rn2005 between '|| v_Plow ||' and '||v_Phei ;
  open v_cur for v_sql;

 End JrscPage;

 另外：SQl语句

 

select * from (select row_number()over(order by id) rn,a.* from 表a where sex='男') where rn between 1 and 10