PLSQL实现分页

分页是任何一个网站(bbs,网上商城，blog)都会使用到的技术，因此学习pl/sql编程开发就一定要掌握该技术。

1. 编写无返回值的存储过程

首先是掌握最简单的存储过程，无返回值的存储过程。

案例：现有一张表book，表结构如下：

请写一个过程，可以向book表添加书，要求通过java程序调用该过程。
--in:表示这是一个输入参数，默认为in
--out:表示一个输出参数
存储过程代码如下：

[c-sharp] view plain copy print ?

1. create or replace procedure sp_pro7(spBookIdin number,spbookName in varchar2,sppublishHousein varchar2) is
2. begin
3. insert into book values(spBookId,spbookName,sppublishHouse);
4. end;

create or replace procedure sp_pro7(spBookId in number,spbookName in varchar2,sppublishHouse in varchar2) is begin insert into book values(spBookId,spbookName,sppublishHouse); end;

在java中调用中调用该过程代码如下：

[java] view plain copy print ?

1. //调用一个无返回值的过程
2. import java.sql.*;
3. publicclass Test2{
4. publicstatic void main(String[] args){
6. try{
7. //1.加载驱动
8. Class.forName("oracle.jdbc.driver.OracleDriver");
9. //2.得到连接
10. Connection ct = DriverManager.getConnection("jdbc:oracle:thin@127.0.0.1:1521:MYORA1","scott","m123");
12. //3.创建CallableStatement
13. CallableStatement cs = ct.prepareCall("{call sp_pro7(?,?,?)}");
14. //4.给?赋值
15. cs.setInt(1,10);
16. cs.setString(2,"笑傲江湖");
17. cs.setString(3,"人民出版社");
18. //5.执行
19. cs.execute();
20. } catch(Exception e){
21. e.printStackTrace();
22. } finally{
23. //6.关闭各个打开的资源
24. cs.close();
25. ct.close();
26. }
27. }
28. }

//调用一个无返回值的过程 import java.sql.*; public class Test2{ public static void main(String[] args){ try{ //1.加载驱动 Class.forName("oracle.jdbc.driver.OracleDriver"); //2.得到连接 Connection ct = DriverManager.getConnection("jdbc:oracle:thin@127.0.0.1:1521:MYORA1","scott","m123"); //3.创建CallableStatement CallableStatement cs = ct.prepareCall("{call sp_pro7(?,?,?)}"); //4.给?赋值 cs.setInt(1,10); cs.setString(2,"笑傲江湖"); cs.setString(3,"人民出版社"); //5.执行 cs.execute(); } catch(Exception e){ e.printStackTrace(); } finally{ //6.关闭各个打开的资源 cs.close(); ct.close(); } } }

执行，记录被加进去了。

2. 有返回值的存储过程（非列表）
案例：编写一个过程，可以输入雇员的编号，返回该雇员的姓名。
案例扩张：编写一个过程，可以输入雇员的编号，返回该雇员的姓名、工资和岗位。

存储过程代码如下：

[c-sharp] view plain copy print ?

1. --有输入和输出的存储过程
2. create or replace procedure sp_pro8
3. (spno in number, spNameout varchar2) is
4. begin
5. select ename into spName from emp where empno=spno;
6. end;

--有输入和输出的存储过程 create or replace procedure sp_pro8 (spno in number, spName out varchar2) is begin select ename into spName from emp where empno=spno; end;

java调用过程代码如下：

[java] view plain copy print ?

1. import java.sql.*;
2. publicclass Test2{
3. publicstatic void main(String[] args){
5. try{
6. //1.加载驱动
7. Class.forName("oracle.jdbc.driver.OracleDriver");
8. //2.得到连接
9. Connection ct = DriverManager.getConnection("jdbc:oracle:thin@127.0.0.1:1521:MYORA1","scott","m123");
11. //3.创建CallableStatement
12. /*CallableStatement cs = ct.prepareCall("{call sp_pro7(?,?,?)}");
13. //4.给?赋值
14. cs.setInt(1,10);
15. cs.setString(2,"笑傲江湖");
16. cs.setString(3,"人民出版社");*/
18. //看看如何调用有返回值的过程
19. //创建CallableStatement
20. /*CallableStatement cs = ct.prepareCall("{call sp_pro8(?,?)}");
22. //给第一个？赋值
23. cs.setInt(1,7788);
24. //给第二个？赋值
25. cs.registerOutParameter(2,oracle.jdbc.OracleTypes.VARCHAR);
27. //5.执行
28. cs.execute();
29. //取出返回值,要注意？的顺序
30. String name=cs.getString(2);
31. System.out.println("7788的名字"+name);
32. } catch(Exception e){
33. e.printStackTrace();
34. } finally{
35. //6.关闭各个打开的资源
36. cs.close();
37. ct.close();
38. }
39. }
40. }

import java.sql.*; public class Test2{ public static void main(String[] args){ try{ //1.加载驱动 Class.forName("oracle.jdbc.driver.OracleDriver"); //2.得到连接 Connection ct = DriverManager.getConnection("jdbc:oracle:thin@127.0.0.1:1521:MYORA1","scott","m123"); //3.创建CallableStatement /*CallableStatement cs = ct.prepareCall("{call sp_pro7(?,?,?)}"); //4.给?赋值 cs.setInt(1,10); cs.setString(2,"笑傲江湖"); cs.setString(3,"人民出版社");*/ //看看如何调用有返回值的过程 //创建CallableStatement /*CallableStatement cs = ct.prepareCall("{call sp_pro8(?,?)}"); //给第一个？赋值 cs.setInt(1,7788); //给第二个？赋值 cs.registerOutParameter(2,oracle.jdbc.OracleTypes.VARCHAR); //5.执行 cs.execute(); //取出返回值,要注意？的顺序 String name=cs.getString(2); System.out.println("7788的名字"+name); } catch(Exception e){ e.printStackTrace(); } finally{ //6.关闭各个打开的资源 cs.close(); ct.close(); } } }
运行，成功得出结果。

案例扩张：编写一个过程，可以输入雇员的编号，返回该雇员的姓名、工资和岗位。

[c-sharp] view plain copy print ?

1. --有输入和输出的存储过程
2. create or replace procedure sp_pro8
3. (spno in number, spNameout varchar2,spSal out number,spJobout varchar2) is
4. begin
5. select ename,sal,job into spName,spSal,spJob from emp where empno=spno;
6. end;

--有输入和输出的存储过程 create or replace procedure sp_pro8 (spno in number, spName out varchar2,spSal out number,spJob out varchar2) is begin select ename,sal,job into spName,spSal,spJob from emp where empno=spno; end;

java调用过程代码如下：

[java] view plain copy print ?

1. import java.sql.*;
2. publicclass Test2{
3. publicstatic void main(String[] args){
5. try{
6. //1.加载驱动
7. Class.forName("oracle.jdbc.driver.OracleDriver");
8. //2.得到连接
9. Connection ct = DriverManager.getConnection("jdbc:oracle:thin@127.0.0.1:1521:MYORA1","scott","m123");
11. //3.创建CallableStatement
12. /*CallableStatement cs = ct.prepareCall("{call sp_pro7(?,?,?)}");
13. //4.给?赋值
14. cs.setInt(1,10);
15. cs.setString(2,"笑傲江湖");
16. cs.setString(3,"人民出版社");*/
18. //看看如何调用有返回值的过程
19. //创建CallableStatement
20. /*CallableStatement cs = ct.prepareCall("{call sp_pro8(?,?,?,?)}");
22. //给第一个？赋值
23. cs.setInt(1,7788);
24. //给第二个？赋值
25. cs.registerOutParameter(2,oracle.jdbc.OracleTypes.VARCHAR);
26. //给第三个？赋值
27. cs.registerOutParameter(3,oracle.jdbc.OracleTypes.DOUBLE);
28. //给第四个？赋值
29. cs.registerOutParameter(4,oracle.jdbc.OracleTypes.VARCHAR);
31. //5.执行
32. cs.execute();
33. //取出返回值,要注意？的顺序
34. String name=cs.getString(2);
35. String job=cs.getString(4);
36. System.out.println("7788的名字"+name+" 工作："+job);
37. } catch(Exception e){
38. e.printStackTrace();
39. } finally{
40. //6.关闭各个打开的资源
41. cs.close();
42. ct.close();
43. }
44. }
45. }

import java.sql.*; public class Test2{ public static void main(String[] args){ try{ //1.加载驱动 Class.forName("oracle.jdbc.driver.OracleDriver"); //2.得到连接 Connection ct = DriverManager.getConnection("jdbc:oracle:thin@127.0.0.1:1521:MYORA1","scott","m123"); //3.创建CallableStatement /*CallableStatement cs = ct.prepareCall("{call sp_pro7(?,?,?)}"); //4.给?赋值 cs.setInt(1,10); cs.setString(2,"笑傲江湖"); cs.setString(3,"人民出版社");*/ //看看如何调用有返回值的过程 //创建CallableStatement /*CallableStatement cs = ct.prepareCall("{call sp_pro8(?,?,?,?)}"); //给第一个？赋值 cs.setInt(1,7788); //给第二个？赋值 cs.registerOutParameter(2,oracle.jdbc.OracleTypes.VARCHAR); //给第三个？赋值 cs.registerOutParameter(3,oracle.jdbc.OracleTypes.DOUBLE); //给第四个？赋值 cs.registerOutParameter(4,oracle.jdbc.OracleTypes.VARCHAR); //5.执行 cs.execute(); //取出返回值,要注意？的顺序 String name=cs.getString(2); String job=cs.getString(4); System.out.println("7788的名字"+name+" 工作："+job); } catch(Exception e){ e.printStackTrace(); } finally{ //6.关闭各个打开的资源 cs.close(); ct.close(); } } }

运行，成功找出记录。
3. 有返回值的存储过程（列表[结果集])

案例：编写一个过程，输入部门号，返回该部门所有雇员信息。
对该题分析如下：
由于oracle存储过程没有返回值，它的所有返回值都是通过out参数来替代的，列表同样也不例外，但由于是集合，所以不能用一般的参数，必须要用pagkage了。所以要分两部分：
(1). 建立一个包，在该包中，我定义类型test_cursor,是个游标。代码如下：

[c-sharp] view plain copy print ?

1. create or replace package testpackageas
2. TYPE test_cursor isref cursor;
3. end testpackage;

create or replace package testpackage as TYPE test_cursor is ref cursor; end testpackage;

(2). 建立存储过程，代码如下：

[c-sharp] view plain copy print ?

1. create or replace procedure sp_pro9(spNoin number,p_cursor out testpackage.test_cursor)is
2. begin
3. open p_cursor for
4. select * from emp where deptno = spNo;
5. end sp_pro9;

create or replace procedure sp_pro9(spNo in number,p_cursor out testpackage.test_cursor) is begin open p_cursor for select * from emp where deptno = spNo; end sp_pro9;

(3). 在java中调用该过程，代码如下：

[java] view plain copy print ?

1. import java.sql.*;
2. publicclass Test2{
3. publicstatic void main(String[] args){
5. try{
6. //1.加载驱动
7. Class.forName("oracle.jdbc.driver.OracleDriver");
8. //2.得到连接
9. Connection ct = DriverManager.getConnection("jdbc:oracle:thin@127.0.0.1:1521:MYORA1","scott","m123");
11. //看看如何调用有返回值的过程
12. //3.创建CallableStatement
13. /*CallableStatement cs = ct.prepareCall("{call sp_pro9(?,?)}");
15. //4.给第？赋值
16. cs.setInt(1,10);
17. //给第二个？赋值
18. cs.registerOutParameter(2,oracle.jdbc.OracleTypes.CURSOR);
20. //5.执行
21. cs.execute();
22. //得到结果集
23. ResultSet rs=(ResultSet)cs.getObject(2);
24. while(rs.next()){
25. System.out.println(rs.getInt(1)+" "+rs.getString(2));
26. }
27. } catch(Exception e){
28. e.printStackTrace();
29. } finally{
30. //6.关闭各个打开的资源
31. cs.close();
32. ct.close();
33. }
34. }
35. }

import java.sql.*; public class Test2{ public static void main(String[] args){ try{ //1.加载驱动 Class.forName("oracle.jdbc.driver.OracleDriver"); //2.得到连接 Connection ct = DriverManager.getConnection("jdbc:oracle:thin@127.0.0.1:1521:MYORA1","scott","m123"); //看看如何调用有返回值的过程 //3.创建CallableStatement /*CallableStatement cs = ct.prepareCall("{call sp_pro9(?,?)}"); //4.给第？赋值 cs.setInt(1,10); //给第二个？赋值 cs.registerOutParameter(2,oracle.jdbc.OracleTypes.CURSOR); //5.执行 cs.execute(); //得到结果集 ResultSet rs=(ResultSet)cs.getObject(2); while(rs.next()){ System.out.println(rs.getInt(1)+" "+rs.getString(2)); } } catch(Exception e){ e.printStackTrace(); } finally{ //6.关闭各个打开的资源 cs.close(); ct.close(); } } }

运行，成功得出部门号是10的所有用户。

4. 编写分页过程
要求，请大家编写一个存储过程，要求可以输入表名、每页显示记录数、当前页。返回总记录数，总页数，和返回的结果集。
(1). oracle中的分页实现：

[c-sharp] view plain copy print ?

1. select t1.*, rownum rn from (select * from emp) t1 where rownum<=10;
2. --在分页时，大家可以把下面的sql语句当做一个模板使用
3. select * from
4. (select t1.*, rownum rn from (select * from emp) t1 where rownum<=10)
5. where rn>=6;

select t1.*, rownum rn from (select * from emp) t1 where rownum<=10; --在分页时，大家可以把下面的sql语句当做一个模板使用 select * from (select t1.*, rownum rn from (select * from emp) t1 where rownum<=10) where rn>=6;

(2). 开发一个包

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

[c-sharp] view plain copy print ?

1. create or replace package testpackageas
2. TYPE test_cursor isref cursor;
3. end testpackage;
4. --开始编写分页的过程
5. create or replace procedure fenye
6. (tableName in varchar2,
7. Pagesize in number,--一页显示记录数
8. pageNow in number,
9. myrows out number,--总记录数
10. myPageCount out number,--总页数
11. p_cursor out testpackage.test_cursor--返回的记录集
12. ) is
13. --定义部分
14. --定义sql语句 字符串
15. v_sql varchar2(1000);
16. --定义两个整数
17. v_begin number:=(pageNow-1)*Pagesize+1;
18. v_end number:=pageNow*Pagesize;
19. begin
20. --执行部分
21. v_sql:='select * from (select t1.*, rownum rn from (select * from '||tableName||') t1 where rownum<='||v_end||') where rn>='||v_begin;
22. --把游标和sql关联
23. open p_cursor for v_sql;
24. --计算myrows和myPageCount
25. --组织一个sql语句
26. v_sql:='select count(*) from '||tableName;
27. --执行sql,并把返回的值，赋给myrows；
28. execute inmediate v_sql into myrows;
29. --计算myPageCount
30. --if myrows%Pagesize=0 then这样写是错的
31. if mod(myrows,Pagesize)=0 then
32. myPageCount:=myrows/Pagesize;
33. else
34. myPageCount:=myrows/Pagesize+1
35. end if;
36. --关闭游标
37. close p_cursor;
38. end;

create or replace package testpackage as TYPE test_cursor is ref cursor; end testpackage; --开始编写分页的过程 create or replace procedure fenye (tableName in varchar2, Pagesize in number,--一页显示记录数 pageNow in number, myrows out number,--总记录数 myPageCount out number,--总页数 p_cursor out testpackage.test_cursor--返回的记录集 ) is --定义部分 --定义sql语句 字符串 v_sql varchar2(1000); --定义两个整数 v_begin number:=(pageNow-1)*Pagesize+1; v_end number:=pageNow*Pagesize; begin --执行部分 v_sql:='select * from (select t1.*, rownum rn from (select * from '||tableName||') t1 where rownum<='||v_end||') where rn>='||v_begin; --把游标和sql关联 open p_cursor for v_sql; --计算myrows和myPageCount --组织一个sql语句 v_sql:='select count(*) from '||tableName; --执行sql,并把返回的值，赋给myrows； execute inmediate v_sql into myrows; --计算myPageCount --if myrows%Pagesize=0 then这样写是错的 if mod(myrows,Pagesize)=0 then myPageCount:=myrows/Pagesize; else myPageCount:=myrows/Pagesize+1 end if; --关闭游标 close p_cursor; end;

(3). 使用java测试该分页过程，代码如下：

[java] view plain copy print ?

1. import java.sql.*;
2. publicclass FenYe{
3. publicstatic void main(String[] args){
5. try{
6. //1.加载驱动
7. Class.forName("oracle.jdbc.driver.OracleDriver");
8. //2.得到连接
9. Connection ct = DriverManager.getConnection("jdbc:oracle:thin@127.0.0.1:1521:MYORA1","scott","m123");
11. //3.创建CallableStatement
12. CallableStatement cs = ct.prepareCall("{call fenye(?,?,?,?,?,?)}");
14. //4.给第？赋值
15. cs.seString(1,"emp");
16. cs.setInt(2,5);
17. cs.setInt(3,2);
19. //注册总记录数
20. cs.registerOutParameter(4,oracle.jdbc.OracleTypes.INTEGER);
21. //注册总页数
22. cs.registerOutParameter(5,oracle.jdbc.OracleTypes.INTEGER);
23. //注册返回的结果集
24. cs.registerOutParameter(6,oracle.jdbc.OracleTypes.CURSOR);
26. //5.执行
27. cs.execute();
29. //取出总记录数 /这里要注意，getInt(4)中4，是由该参数的位置决定的
30. int rowNum=cs.getInt(4);
32. int pageCount = cs.getInt(5);
33. ResultSet rs=(ResultSet)cs.getObject(6);
35. //显示一下，看看对不对
36. System.out.println("rowNum="+rowNum);
37. System.out.println("总页数="+pageCount);
39. while(rs.next()){
40. System.out.println("编号："+rs.getInt(1)+" 名字："+rs.getString(2)+" 工资："+rs.getFloat(6));
41. }
42. } catch(Exception e){
43. e.printStackTrace();
44. } finally{
45. //6.关闭各个打开的资源
46. cs.close();
47. ct.close();
48. }
49. }
50. }

import java.sql.*; public class FenYe{ public static void main(String[] args){ try{ //1.加载驱动 Class.forName("oracle.jdbc.driver.OracleDriver"); //2.得到连接 Connection ct = DriverManager.getConnection("jdbc:oracle:thin@127.0.0.1:1521:MYORA1","scott","m123"); //3.创建CallableStatement CallableStatement cs = ct.prepareCall("{call fenye(?,?,?,?,?,?)}"); //4.给第？赋值 cs.seString(1,"emp"); cs.setInt(2,5); cs.setInt(3,2); //注册总记录数 cs.registerOutParameter(4,oracle.jdbc.OracleTypes.INTEGER); //注册总页数 cs.registerOutParameter(5,oracle.jdbc.OracleTypes.INTEGER); //注册返回的结果集 cs.registerOutParameter(6,oracle.jdbc.OracleTypes.CURSOR); //5.执行 cs.execute(); //取出总记录数 /这里要注意，getInt(4)中4，是由该参数的位置决定的 int rowNum=cs.getInt(4); int pageCount = cs.getInt(5); ResultSet rs=(ResultSet)cs.getObject(6); //显示一下，看看对不对 System.out.println("rowNum="+rowNum); System.out.println("总页数="+pageCount); while(rs.next()){ System.out.println("编号："+rs.getInt(1)+" 名字："+rs.getString(2)+" 工资："+rs.getFloat(6)); } } catch(Exception e){ e.printStackTrace(); } finally{ //6.关闭各个打开的资源 cs.close(); ct.close(); } } }

运行，控制台输出：
rowNum=19
总页数：4
编号：7369 名字：SMITH 工资：2850.0
编号：7499 名字：ALLEN 工资：2450.0
编号：7521 名字：WARD 工资：1562.0
编号：7566 名字：JONES 工资：7200.0
编号：7654 名字：MARTIN 工资：1500.0

新的需要，要求按照薪水从低到高排序，然后取出6-10。

代码如下：

[c-sharp] view plain copy print ?

1. begin
2. --执行部分
3. v_sql:='select * from (select t1.*, rownum rn from (select * from '||tableName||' order by sal) t1 where rownum<='||v_end||') where rn>='||v_begin;

begin --执行部分 v_sql:='select * from (select t1.*, rownum rn from (select * from '||tableName||' order by sal) t1 where rownum<='||v_end||') where rn>='||v_begin;

重新执行一次procedure,java不用改变，运行，控制台输出：
rowNum=19
总页数：4
编号：7900 名字：JAMES 工资：950.0
编号：7876 名字：ADAMS 工资：1100.0
编号：7521 名字：WARD 工资：1250.0
编号：7654 名字：MARTIN 工资：1250.0
编号：7934 名字：MILLER 工资：1300.0

分享到：