oracle15 pl/sql 分页

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

 

posted @ 2015-10-07 16:03  无天666  阅读(251)  评论(0编辑  收藏  举报