pl/sql之编写分页过程

--开发一个包 
--建立一个包，在该包中，我定义类型test_cursor,是个游标。 如下： 

Sql代码

1. create or replace package testpackage as  
2.   TYPE test_cursor is ref cursor;   
3. end testpackage;   

--开始编写分页过程
create or replace procedure fenye
(table_name in varchar2,
page_size in number,        --每页显示的记录数
page_now in number,          --当前页
order_by in varchar2,                --附加--排序条件

total_rows out number,       --总记录数
total_pages out number,      --总页数
p_cursor out testpackage.test_cursor  --返回的记录集
) is
--定义部分
--定义SQL语句的字符串
v_sql varchar2(500);
v_start number := (page_now-1)*page_size+1;
v_end number := page_now*page_size;
begin
--执行部分
v_sql:='select * from (select t1.*, rownum rn from (select * from '||table_name||' order by '||order_by||') t1 where rownum<='||v_end||') where rn>='||v_start;
--把游标和SQL关联
open p_cursor for v_sql;
--计算总记录数和总页数
--组织另一个sql
v_sql:='select count(*) from '||table_name;
--执行上面的sql语句，并将返回值付给total_rows;
execute immediate v_sql into total_rows;
--计算total_pages（--总页数）
if mod(total_rows, page_size)=0 then
  total_pages:=total_rows/page_size;
else 
  total_pages:=total_rows/page_size+1;
end if;
--此处不能关闭游标，否则在JAVA中调用时，会出现 游标已经关闭exception，导致取不到结果集
---close p_cursor;
end;

/

----------------------------------------------------------------------------------------------

--使用java测试 
//测试分页 

package oracle.plsql.test.fenye;


import java.sql.*;


/**
 * //调用一个分页的存储过程
create or replace procedure fenye
(table_name in varchar2, --表名
page_size in number,         --每页显示的记录数
page_now in number,          --当前页
order_by in varchar2, --排序条件


total_rows out number,       --总记录数
total_pages out number,      --总页数
p_cursor out testpackage.test_cursor  --返回的记录集
)
 */
public class TestPlSqlFenye {
public static void main(String[] args){  
Connection ct = null;
        CallableStatement cs = null;
        try{  
            //1.加载驱动  
            Class.forName("oracle.jdbc.driver.OracleDriver");  
            //2.得到连接  
            ct = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:BJSXT","scott","tiger");  
            //看看如何调用有返回值的过程   
            //3.创建CallableStatement   
            cs = ct.prepareCall("{call fenye(?,?,?,?, ?,?,?)}");   
  
            //4.给前4个？赋值   
            cs.setString(1, "emp");   
            cs.setInt(2, 6); //每页显示6条  
            cs.setInt(3, 1); //显示第1页
            cs.setString(4,"sal");
            //给后3个？注册参数   
            cs.registerOutParameter(5, oracle.jdbc.OracleTypes.INTEGER);   //注意这里的这个类型
            cs.registerOutParameter(6, oracle.jdbc.OracleTypes.INTEGER);   //注意这里的这个类型
            cs.registerOutParameter(7, oracle.jdbc.OracleTypes.CURSOR);   //注意这里的这个类型
  
            //5.执行   
            cs.execute();      
            System.out.println(cs.getInt(5));
            System.out.println(cs.getInt(6));
            //得到结果集 ,在这里取出来的时候就是结果集了
            ResultSet rs=(ResultSet)cs.getObject(7);        
            
            while(rs.next()){   
                System.out.println(rs.getInt(1)+" "+rs.getString(2)+" "+rs.getFloat("SAL"));   
            }    
            
            
        } catch(Exception e){  
            e.printStackTrace();  
        } finally{  
            //6.关闭各个打开的资源  
            try {
cs.close();
ct.close();  
} catch (SQLException e) {
e.printStackTrace();
}  
        }
    }
}

-------------------------------------------------------------------------------------------

console显示结果如下：

14
3
7369 SMITH 671.17
7900 JAMES 950.71
7876 ADAMS 1100.0
7521 WARD 1250.0
7654 MARTIN 1250.0
7934 MILLER 1300.0