Oracle分页存储过程的实现

1. 描述

这里以 Oracle 数据库中的 SCOT T用户的 EMP 表为例，编写一个分页存储过程，要求是：

输入表名、每页显示记录数、当前页，返回总记录数、总页数和返回的结果集。

2. 具体步骤2.1 创建包

由于需要返回查询出来的结果集，需要在 PL/SQL 中创建一个 Package，这个包里面定义一个 ref cursor 类型，用于记录 SQL 语句查询出来的结果集。如下图：

创建包的代码如下：

1. create or replace package pagingPackage as
   
2. type paging_cursor is ref cursor;
   
3. end pagingPackage;

复制代码

2.2 分页过程

接下来开始 Oracle 的分页过程，我们可以用 select emp.*,rownum from emp; 来表示出每行的行标。然后可以根据行标对内容进行分页，下面这个 SQL 语句可以作为 Oracle 分页的模板。

1. select * from   
   
2.     (select t1.*,rownum rn from (select * from emp) t1 where rownum<=12)  
   
3.     where rn>=8;

复制代码

2.3 存储过程

有了上面的 ref cursor 类型和分页模板，下面开始编写分页的存储过程，如下图：

具体代码如下：

1. create or replace procedure paging
   
2.     (tableName in varchar2 ,--表名
   
3.     pageSizes in number,--每页显示记录数
   
4.     pageNow in number,--当前页
   
5.     rowNums out number,--总记录数
   
6.     pageNum out number,--总页数
   
7.     paging_cursor out pagingPackage.paging_cursor) is
   
8.     --定义部分
   
9.     --定义sql语句，字符串
   
10.     v_sql varchar2(1000);
    
11.     --定义两个整数，用于表示每页的开始和结束记录数
    
12.     v_begin number:=(pageNow-1)*pageSizes+1;
    
13.     v_end number:=pageNow*pageSizes;
    
14.     begin
    
15.       --执行部分
    
16.       v_sql:='select * from (select t1.*,rownum rn from (select * from '||tableName||') t1 where rownum<='||v_end||') where rn>='||v_begin;
    
17.       --把游标和sql语句关联
    
18.       open paging_cursor for v_sql;
    
19.       --计算rowNums和pageNum
    
20.       --组织一个sql语句
    
21.       v_sql:='select count(*) from '||tableName;
    
22.       --执行该sql语句,并赋给rowNums
    
23.       execute immediate v_sql into rowNums;
    
24.       --计算pageNum
    
25.       if mod(rowNums,pageSizes)=0 then
    
26.         pageNum := rowNums/pageSizes;
    
27.         else
    
28.           pageNum := rowNums/pageSizes+1;
    
29.           end if;
    
30.       end;

复制代码

2.4 测试分页

点击模板>模板数据集或者直接在数据集面板中，点击+号，添加一个存储过程，切换到 Oracle 数据库所在的数据连接，双击所需要添加的存储过程，如下图所示：

点击预览，需要输入参数，如下图：

输入参数后，点击确定，返回三个数据集，如下图：

数据集可以同时查看多个结果集，如下图所示：

注：在调用存储过程时，需要设置下 pageNow 这个参数的默认值，否则不会返回数据集。