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. }