PL/SQL分页(存储过程操作)

20.PL/SQL分页(存储过程操作)
编写分页过程  介绍 分页是任何一个网站(bbs,网上商城，blog)都会使用到的技术，因此学习pl/sql编程开发就一定要掌握该技术。看图：  无返回值的存储过程 古人云：欲速则不达，为了让大家伙比较容易接受分页过程编写，我还是从简单到复杂，循序渐进的给大家讲解。首先是掌握最简单的存储过程，无返回值的存储过程：
案例：现有一张表book，表结构如下：看图： 书号 书名 出版社 请写一个过程，可以向book表添加书，要求通过java程序调用该过程。 --in:表示这是一个输入参数，默认为in --out:表示一个输出参数
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中调用
//调用一个无返回值的过程
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(); Oracle 笔记 49
ct.close();
}
}
}
执行，记录被加进去了  有返回值的存储过程（非列表） 再看如何处理有返回值的存储过程：
案例：编写一个过程，可以输入雇员的编号，返回该雇员的姓名。 案例扩张：编写一个过程，可以输入雇员的编号，返回该雇员的姓名、工资和岗位。
--有输入和输出的存储过程
create or replace procedure sp_pro8
(spno in number, spName out varchar2) is
begin
select ename into spName from emp where empno=spno;
end;
/
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(); Oracle 笔记 50
} finally{
//6.关闭各个打开的资源
cs.close();
ct.close();
}
}
}
运行，成功得出结果。。
案例扩张：编写一个过程，可以输入雇员的编号，返回该雇员的姓名、工资和岗位。
--有输入和输出的存储过程
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;
/
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();
//取出返回值,要注意？的顺序 Oracle 笔记 51
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();
}
}
}
运行，成功找出记录 
有返回值的存储过程（列表[结果集]）
案例：编写一个过程，输入部门号，返回该部门所有雇员信息。 对该题分析如下： 由于oracle存储过程没有返回值，它的所有返回值都是通过out参数来替代的，列表同样也不例外，但由于是集合，所以不能用一般的参数，必须要用pagkage了。所以要分两部分： 返回结果集的过程 1.建立一个包，在该包中，我定义类型test_cursor,是个游标。 如下：
create or replace package testpackage as
TYPE test_cursor is ref cursor;
end testpackage; 2.建立存储过程。如下：
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程序中调用该过程
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);
//给第二个？赋值 Oracle 笔记 52
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的所有用户
 编写分页过程 有了上面的基础，相信大家可以完成分页存储过程了。 要求，请大家编写一个存储过程，要求可以输入表名、每页显示记录数、当前页。返回总记录数，总页数，和返回的结果集。 如果大家忘了oracle中如何分页，请参考第三天的内容。 先自己完成，老师在后面给出答案，并讲解。 --oracle的分页
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; --开发一个包 --建立一个包，在该包中，我定义类型test_cursor,是个游标。 如下：
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语句 字符串 Oracle 笔记 53
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测试 //测试分页
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);
//注册总记录数 Oracle 笔记 54
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 过程的执行部分做下改动，如下：
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 Oracle 笔记 55
编号：7521 名字：WARD 工资：1250.0 编号：7654 名字：MARTIN 工资：1250.0 编号：7934 名字：MILLER 工资：1300.0