Oracle分页存储过程的实现

1. 描述

这里以Oracle数据库中的SCOTT用户的EMP表为例,编写一个分页存储过程,要求是:
输入表名、每页显示记录数、当前页,返回总记录数、总页数和返回的结果集。

2. 具体步骤

2.1 创建包
由于需要返回查询出来的结果集,需要在PL/SQL中创建一个package,这个包里面定义一个ref cursor类型,用于记录sql语句查询出来的结果集。如下图:
创建包的代码如下:
create or replace package pagingPackage as type paging_cursor is ref cursor; end pagingPackage; 
2.2 分页过程
接下来开始Oracle的分页过程,我们可以用select emp.*,rownum from emp;来表示出每行的行标。然后可以根据行标对内容进行分页,下面这个SQL语句可以作为Oracle分页的模板。
select * from (select t1.*,rownum rn from (select * from emp) t1 where rownum<=12) where rn>=8;
2.3 存储过程
有了上面的ref cursor类型和分页模板,下面开始编写分页的存储过程,如下图:
具体代码如下:
create or replace procedure paging (tableName in varchar2 ,--表名 pageSizes in number,--每页显示记录数 pageNow in number,--当前页 rowNums out number,--总记录数 pageNum out number,--总页数 paging_cursor out pagingPackage.paging_cursor) is --定义部分 --定义sql语句,字符串 v_sql varchar2(1000); --定义两个整数,用于表示每页的开始和结束记录数 v_begin number:=(pageNow-1)*pageSizes+1; v_end number:=pageNow*pageSizes; begin --执行部分 v_sql:='select * from (select t1.*,rownum rn from (select * from '||tableName||') t1 where rownum<='||v_end||') where rn>='||v_begin; --把游标和sql语句关联 open paging_cursor for v_sql; --计算rowNums和pageNum --组织一个sql语句 v_sql:='select count(*) from '||tableName; --执行该sql语句,并赋给rowNums execute immediate v_sql into rowNums; --计算pageNum if mod(rowNums,pageSizes)=0 then pageNum := rowNums/pageSizes; else pageNum := rowNums/pageSizes+1; end if; end;
2.4 测试分页
点击模板>模板数据集或者直接在数据集面板中,点击+号,添加一个存储过程,切换到oracle数据库所在的数据连接,双击所需要添加的存储过程,如下图:
点击预览,需要输入参数,如下图:
输入参数后,点击确定,返回三个数据集,如下图:

数据集可以同时查看多个结果集,如下图所示:
注:在调用存储过程时,需要设置下pageNow这个参数的默认值,否则不会返回数据集。

附件列表


主题:
标签: 已验证

文档内容仅供参考,如果你需要获取更多帮助,付费/准付费客户请咨询帆软技术支持
关于技术问题,您还可以前往帆软社区,点击顶部搜索框旁边的提问按钮
若您还有其他非技术类问题,可以联系帆软传说哥(qq:1745114201