Oracle实践--PL/SQL综合之分页存储过程

oracle，分页，存储过程三个词结合起来，来个综合点的小练习，运用之前的pl/sql创建一个分页的存储过程，只需要简单几步即可。

1.声明一个引用游标

[sql] view plaincopyprint?01.create or replace package cur_ref_type 
02.as-- is or as can be used  
03.type cur_ref is ref cursor; 
04.end; 
create or replace package cur_ref_type
as-- is or as can be used
type cur_ref is ref cursor;
end;2.编写存储过程

[sql] view plaincopyprint?01.create or replace procedure pidebypge 
02.         (tablename varchar2,pagesize number,currentpage number, 
03.                 totalpage out number,totalrecord out number,results out cur_ref_type.cur_ref)--要带包名  
04.is 
05. v_sql varchar2(200);--用于写sql语句的  
06. v_begin number;--开始位置  
07. v_end number;--结束位置  
08.begin 
09. v_begin := (currentpage -1) * pagesize; 
10. v_end := currentpage * pagesize; 
11. v_sql :='select count(*) from '||tablename;--将表名字符串拼接sql语句  
12. execute immediate v_sql into totalrecord;--查询所有记录  
13. totalpage :=ceil(totalrecord/pagesize);--计算总页数  
14. v_sql :='select * from (select rownum rn,'||tablename||'.* from '||tablename||' where rownum <= '||v_end||') where rn > '||v_begin; 
15. open  results for v_sql; 
16.end; 
create or replace procedure pidebypge
         (tablename varchar2,pagesize number,currentpage number,
                 totalpage out number,totalrecord out number,results out cur_ref_type.cur_ref)--要带包名
is
 v_sql varchar2(200);--用于写sql语句的
 v_begin number;--开始位置
 v_end number;--结束位置
begin
 v_begin := (currentpage -1) * pagesize;
 v_end := currentpage * pagesize;
 v_sql :='select count(*) from '||tablename;--将表名字符串拼接sql语句
 execute immediate v_sql into totalrecord;--查询所有记录
 totalpage :=ceil(totalrecord/pagesize);--计算总页数
 v_sql :='select * from (select rownum rn,'||tablename||'.* from '||tablename||' where rownum <= '||v_end||') where rn > '||v_begin;
 open  results for v_sql;
end;3.java客户端调用

[java] view plaincopyprint?01.string driver ="oracle.jdbc.driver.oracledriver"; 
02.        string url  = "jdbc:oracle:thin:@localhost:1521:orcl"; 
03.        string username = "scott"; 
04.        string password = "ysjian"; 
05.        try { 
06.           class.forname(driver); 
07.           connection conn = 
08.                        drivermanager.getconnection(url,username,password); 
09.           callablestatement cs = conn.preparecall("{call 
10.                                         pidebypge(?,?,?,?,?,?)}"); 
11.           cs.setstring(1,"emp");//表名  
12.           cs.setint(2, 3);//分页单位  
13.           cs.setint(3, 1);//当前页  
14.           cs.registeroutparameter(4, oracletypes.integer);//注册输出参数  
15.           cs.registeroutparameter(5, oracletypes.integer); 
16.           cs.registeroutparameter(6, oracletypes.cursor);//注册输出参数游标  
17.           cs.execute();//执行  
18.           resultset rs = (resultset)cs.getobject(6);//获得游标  
19.           while(rs.next()){ 
20.               int empno = rs.getint("empno"); 
21.               string ename = rs.getstring("ename"); 
22.               system.out.println(empno+"-->"+ename); 
23.           } 
24.        } catch (classnotfoundexception | sqlexception e) { 
25.           e.printstacktrace(); 
26.        }