首页 > 代码库 > plsql分页

plsql分页

2024-07-04 23:03:17   230人阅读

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

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

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

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

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

 

[c-sharp] view plaincopy
  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;  

 

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

 

[java] view plaincopy
  1. //调用一个无返回值的过程  
  2. import java.sql.*;  
  3. public class Test2{  
  4.     public static void main(String[] args){  
  5.           
  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. }  

 

     执行，记录被加进去了。

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

存储过程代码如下：

[c-sharp] view plaincopy
  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;  

 

 

java调用过程代码如下：

[java] view plaincopy
  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.             //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. }  

   运行，成功得出结果。

 

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

[c-sharp] view plaincopy
  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;  

 

java调用过程代码如下：

[java] view plaincopy
  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. }  

 

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

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

[c-sharp] view plaincopy
  1. create or replace package testpackage as  
  2.   TYPE test_cursor is ref cursor;  
  3. end testpackage;  

 

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

[c-sharp] view plaincopy
  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] view plaincopy
  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的所有用户。

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

[c-sharp] view plaincopy
  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;  

 

(2). 开发一个包

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

[c-sharp] view plaincopy
  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;  

 

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

[java] view plaincopy
  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。

代码如下：

[c-sharp] view plaincopy
  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


联系
我们