oracle分页存储过程

在实际的应用中我们可以根据自己的想法用Oracle存储过程做一个页的相关的存储过程，下面的文章主要是和大家分享Oracle存储过程的经验心得，希望你能通此文章对其的实际操作有个更好的了解。

之所以用存储过程，是因为以后需要修改的话不需要修改程序代码，只需要修改Oracle存储过程的代码。但这个例子是在存储过程里动态生成的SQL语句，不知道会不会因此失去存储过程一次编译和快速的特点。代码如下：

1、首先建立一个包，用户创建一个游标类型

1 create or replace package pkg_query as  

2 type cur_query is ref cursor;  

3 end pkg_query;  

2、创建Oracle存储过程

4 create OR REPLACE PROCEDURE prc_query  

5 (p_tableName in varchar2,   

表名

6 p_strwhere in varchar2,  

查询条件 --*

7 p_orderColumn in varchar2,  

排序的列 --*

8 p_orderStyle in varchar2,  

排序方式 --*

9 p_curPage in out Number, 

当前页

10 p_pageSize in out Number,  

每页显示记录条数

11 p_totalRecords out Number,  

总记录数

12 p_totalPages out Number,  

总页数

13 v_cur out pkg_query.cur_query)  

返回的结果集

14 IS  

15 v_sql VARchar2(1000) := ’’;   

sql语句

16 v_startRecord Number(4);  

开始显示的记录条数

17 v_endRecord Number(4); 

结束显示的记录条数

18 BEGIN 

记录中总记录条数

19 v_sql := ’select TO_NUMBER(count(*)) FROM ’ || p_tableName || ’ where 11=1’;  

20 IF p_strwhere IS NOT NULL or p_strwhere <> ’’ THEN  

21 v_sql := v_sql || p_strwhere;  

22 END IF;  

23 execute IMMEDIATE v_sql INTO p_totalRecords;  

验证页面记录大小

24 IF p_pageSize < 0 THEN  

25 p_pageSize := 0;  

26 END IF;  

根据页大小计算总页数

27 IF MOD(p_totalRecords,p_pageSize) = 0 THEN  

28 p_totalPages := p_totalRecords / p_pageSize;  

29 ELSE  

30 p_totalPages := p_totalRecords / p_pageSize + 1;  

31 END IF;   

 

 

 

1.创建测试表

Sql代码

create table Account( 

    cardId char(20) primary key,  --帐号

    name char(20) not null,         --姓名

        money numeric(20,2)         --存款

); 

 2.录入测试数据

Sql代码

insert into Account values('0001','张三',1000); 

insert into Account values('0002','李四',2000); 

insert into Account values('0003','王五',3000); 

insert into Account values('0004','钱六',4000); 

 3，编写一个视图以简化存储过程的编写与调用写法

Sql代码

create or replace view v_page  

as 

select rownum rn,t.* from Account t; 

 4，编写存储过程（省略了查询条件）

Sql代码

create or replace procedure P_Pagination( 

       page  int,--第几页

       perPageCount int ,--每页几条记录

       totalPage out int,--总页数

       pageResultSet out SYS_REFCURSOR --当前页查询出来的结果集  

) 

as 

   totalCount int;--总记录数

   pageSql varchar(2000); --查询某页结果的SQL语句

begin 

  select count(1) into totalCount from Account;  --查询总记录数

  totalPage := ceil( totalCount / perPageCount);   --算出总页数

  pageSql := 'select * from v_page u  

    where rn between '||(page-1)||'*'||perPageCount||'+1 and '||(page*perPageCount); 

   open pageResultSet for pageSql; 

end P_Pagination; 

 5，在PL/SQL中调用该存储过程

Sql代码

declare  

   totalaPage int;  --总页数

   pageResult  SYS_REFCURSOR; --存放结果的变量

   account v_page%rowtype; 

 begin 

    P_Pagination(1,2,totalaPage,pageResult); 

    dbms_output.put_line('总共'||totalaPage||'页'); 

    fetch pageResult into account; 

   while pageResult%found loop 

      dbms_output.put_line(account.cardid||','||account.name||','||account.money); 

      fetch pageResult into account; 

    end loop; 

   close pageResult; 

end; 

 6，使用JDBC调用的代码片段

Java代码

public static void main(String[] args) throws SQLException { 

    Connection conn = ConnectionManage.getConnection(); 

    CallableStatement cs = conn.prepareCall("call P_Pagination(?,?,?,?)"); 

    cs.setInt(1, 1); 

    cs.setInt(2, 3); 

    cs.registerOutParameter(3, Types.INTEGER); 

    cs.registerOutParameter(4, oracle.jdbc.OracleTypes.CURSOR); 

    cs.execute(); 

    int totalPage = cs.getInt(3); 

    System.out.println("共有" + totalPage + "页"); 

    ResultSet rs = (ResultSet) cs.getObject(4); 

    while (rs.next()) { 

        System.out.println(rs.getString("cardid")+","+rs.getString("name") +"," + rs.getString("money")); 

    } 

}   

<!--EndFragment-->