pl/sql之分页

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

 无返回值的存储过程
古人云：欲速则不达，为了让大家伙比较容易接受分页过程编写，我还是从简单到复杂，循序渐进的给大家讲解。首先是掌握最简单的存储过程，无返回值的存储过程：
案例：现有一张表book，表结构如下：看图：

书号 书名 出版社

请写一个过程，可以向book表添加书，要求通过java程序调用该过程

--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中调用

Java代码:

//调用一个无返回值的过程
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();
        }
    }
}

执行，记录被加进去了
有返回值的存储过程（非列表）
再看如何处理有返回值的存储过程：
案例：编写一个过程，可以输入雇员的编号，返回该雇员的姓名。
案例扩张：编写一个过程，可以输入雇员的编号，返回该雇员的姓名、工资和岗位。

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

运行，成功得出结果。。

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

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.   
32.             //5.执行   
33.             cs.execute();      
34.             //取出返回值,要注意？的顺序   
35.             String name=cs.getString(2);    
36.             String job=cs.getString(4);   
37.             System.out.println("7788的名字"+name+" 工作："+job);           
38.         } catch(Exception e){   
39.             e.printStackTrace();   
40.         } finally{   
41.             //6.关闭各个打开的资源   
42.             cs.close();   
43.             ct.close();   
44.         }   
45.     }   
46. }  

运行，成功找出记录

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

Sql代码:

1. create or replace package testpackage as  //注意这里是as而不是is
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代码 
       
       
      
      
      
      
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;   
/  
     
     
--使用java测试 
//测试分页 

      
      

       
       

        
        

         
         Java代码 
        
        
       
       
       
       
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
过程的执行部分做下改动，如下：

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