PL/SQL分页 编写分页过程 无返回值的存储过程 古人云：欲速则不达，为了让大家伙比较容易接受分页过程编写，我还是从简单到复杂，循序渐进的给大家讲解。首先是掌握最简单的存储过程，无返回值的存储过程： 案例：现有一张表book，表结构如下：书号 书名 出版社 请写一个过程，可以向book表添加书，要求通过java程序调用该过程。 --in:表示这是一个输入参数，默认为in(不写in则默认就是in) --out:表示一个输出参数 Sql代码 1.create or replace procedure sp_pro7(spBookId in number,spbookName in varchar2,sppublishHouse in varchar2) is 2.begin 3. insert into book values(spBookId,spbookName,sppublishHouse); 4.end; 5./
--在java中调用 Java代码 1.//调用一个无返回值的过程 2.import java.sql.*; 3.public class Test2{ 4. public static 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"); 11. 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.} 执行，记录被加进去了
有返回值的存储过程（非列表） 再看如何处理有返回值的存储过程： 案例：编写一个过程，可以输入雇员的编号，返回该雇员的姓名。 案例扩张：编写一个过程，可以输入雇员的编号，返回该雇员的姓名、工资和岗位。 Sql代码 1.--有输入和输出的存储过程 2.create or replace procedure sp_pro8 3.(spno in number, spName out varchar2) is 4.begin 5. select ename into spName from emp where empno=spno; 6.end; 7./ Java代码 1.import java.sql.*; 2.public class Test2{ 3. public static void main(String[] args){ 4. 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"); 10. //看看如何调用有返回值的过程 11. //创建CallableStatement 12. CallableStatement cs = ct.prepareCall("{call sp_pro8(?,?)}"); 13. 14. //给第一个？赋值 15. cs.setInt(1,7788); 16. //给第二个？赋值 17. cs.registerOutParameter(2,oracle.jdbc.OracleTypes.VARCHAR); 18. 19. //5.执行 20. cs.execute(); 21. //取出返回值,要注意？的顺序 22. String name=cs.getString(2); 23. System.out.println("7788的名字"+name); 24. } catch(Exception e){ 25. e.printStackTrace(); 26. } finally{ 27. //6.关闭各个打开的资源 28. cs.close(); 29. ct.close(); 30. } 31. } 32.} 运行，成功得出结果。。
案例扩张：编写一个过程，可以输入雇员的编号，返回该雇员的姓名、工资和岗位。 Sql代码 1.--有输入和输出的存储过程 2.create or replace procedure sp_pro8 3.(spno in number, spName out varchar2,spSal out number,spJob out varchar2) is 4.begin 5. select ename,sal,job into spName,spSal,spJob from emp where empno=spno; 6.end; 7./ Java代码 1.import java.sql.*; 2.public class Test2{ 3. public static void main(String[] args){ 4. 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"); 10. 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,"人民出版社");*/ 17. 18. //看看如何调用有返回值的过程 19. //创建CallableStatement 20. CallableStatement cs = ct.prepareCall("{call sp_pro8(?,?,?,?)}"); 21. 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); 30. 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.} 运行，成功找出记录
有返回值的存储过程（列表[结果集]） 案例：编写一个过程，输入部门号，返回该部门所有雇员信息。 对该题分析如下： 由于oracle存储过程没有返回值，它的所有返回值都是通过out参数来替代的，列表同样也不例外，但由于是集合，所以不能用一般的参数，必须要用pagkage了。所以要分两部分： 返回结果集的过程 1.建立一个包，在该包中，我定义类型test_cursor,是个游标。 如下： Sql代码 1.create or replace package testpackage as 2. type test_cursor is ref cursor; 3.end testpackage; 2.建立存储过程。如下： Sql代码 1.create or replace procedure sp_pro9(spNo in 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; 3.如何在java程序中调用该过程 Java代码 1.import java.sql.*; 2.public class Test2{ 3. public static void main(String[] args){ 4. 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"); 10. 11. //看看如何调用有返回值的过程 12. //3.创建CallableStatement 13. CallableStatement cs = ct.prepareCall("{call sp_pro9(?,?)}"); 14. 15. //4.给第？赋值 16. cs.setInt(1,10); 17. //给第二个？赋值 18. cs.registerOutParameter(2,oracle.jdbc.OracleTypes.CURSOR); 19. 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.} 运行，成功得出部门号是10的所有用户
编写分页过程 有了上面的基础，相信大家可以完成分页存储过程了。 要求，请大家编写一个存储过程，要求可以输入表名、每页显示记录数、当前页。返回总记录数，总页数，和返回的结果集。 如果大家忘了oracle中如何分页，请参考第三天的内容。 先自己完成，老师在后面给出答案，并讲解。 --oracle的分页 Sql代码 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;
--开发一个包 --建立一个包，在该包中，我定义类型test_cursor,是个游标。 如下： Sql代码 1.create or replace package testpackage as 2. TYPE test_cursor is ref 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; 39./
--使用java测试 //测试分页 Java代码 1.import java.sql.*; 2.public class FenYe{ 3. public static void main(String[] args){ 4. 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"); 10. 11. //3.创建CallableStatement 12. CallableStatement cs = ct.prepareCall("{call fenye(?,?,?,?,?,?)}"); 13. 14. //4.给第？赋值 15. cs.seString(1,"emp"); 16. cs.setInt(2,5); 17. cs.setInt(3,2); 18. 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); 25. 26. //5.执行 27. cs.execute(); 28. 29. //取出总记录数 /这里要注意，getInt(4)中4，是由该参数的位置决定的 30. int rowNum=cs.getInt(4); 31. 32. int pageCount = cs.getInt(5); 33. ResultSet rs=(ResultSet)cs.getObject(6); 34. 35. //显示一下，看看对不对 36. System.out.println("rowNum="+rowNum); 37. System.out.println("总页数="+pageCount); 38. 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.} 运行，控制台输出： 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 过程的执行部分做下改动，如下： Sql代码 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; 重新执行一次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