Oracle 分页存储过程

最新推荐文章于 2025-09-06 22:56:13 发布

转载最新推荐文章于 2025-09-06 22:56:13 发布 · 43 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/sunyj/archive/2012/05/06/2486450.html

文章标签：

#数据库

本文详细介绍了如何使用PL/SQL实现SQL分页查询，并提供了具体示例代码，帮助开发者理解并掌握分页查询技巧。

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

转载于:https://www.cnblogs.com/sunyj/archive/2012/05/06/2486450.html