PL/SQL的进阶----编写分页过程
介绍
分页是任何一个网站（bbs，网上商城， blog）都会使用到的技术，因此学习 pl/sql 编程开发就一定要掌握该技术。
①无返回值的存储过程
古人云：欲速则不达。首先是掌握最简单的存储过程，无返回值的存储过程：
案例：现在有一张表，字段有“书号，书名，出版社”。请编写一个存储过程，可以向book表添加书，要求通过JAVA程序调用该过程。
----book表
SQL>create table book (boodId number, bookName varchar2(50), publishHouse varchar2(50));
----编写存储过程
----in:表示这是一个输入参数！不写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中调用
package com.sp;
//调用一个无返回值的过程
import java.sql.*;
public class Test1 {
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.关闭各种打开的资源
}
}
}
②.1>有返回值的存储过程（非列表）
案例：编写一个过程，可以输入雇员的编号，返回该雇员的姓名。
案例扩张：编写一个过程，可以输入雇员的编号，返回该雇员的姓名，工资和岗位。
-----有输入和输出的存储过程：
create or replace procedure sp_pro8
(spNo in varchar2, spName out varchar2) is
begin
select ename into spName from emp where empno=spNo;
end;
在JAVA中调用：
package com.sp;
//调用一个有返回值的过程
import java.sql.*;
public class Test1 {
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_pro8(?,?)}");
//4.1给第一个？赋值
cs.setInt(1,7788);
//4.2给第二个？赋值
cs.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);
//5.执行
cs.execute();
//6.取出返回值,要注意？的顺序
String name=cs.getString(2);
System.out.println("7788的名字是:"+name);
}catch (Exception e){
e.printStackTrace();
}finally{
//7.关闭各种打开的资源
}
}
}
若有多个返回值！
create or replace procedure sp_pro8
(spNo in varchar2, 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;
package com.sp;
//调用一个有多个返回值的过程
import java.sql.*;
public class Test1 {
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_pro8(?,?,?,?)}");
//4.1给第一个？赋值
cs.setInt(1,7788);
//4.2给第二个？赋值
cs.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);
//4.3给第三个？赋值
cs.registerOutParameter(3, oracle.jdbc.OracleTypes.DOUBLE);
//4.4给第四个？赋值
cs.registerOutParameter(4, oracle.jdbc.OracleTypes.VARCHAR);
//5.执行
cs.execute();
//6.取出返回值,要注意？的顺序
String name=cs.getString(2);
String job=cs.getString(4);
System.out.println("7788的名字是:"+name+" 工作:"+job);
}catch (Exception e){
e.printStackTrace();
}finally{
//7.关闭各种打开的资源
}
}
}
②.2>有返回值的存储过程（列表[结果集]）
案例：编写一个过程，输入部门号，返回该部门所有雇员信息。
分析：由于ORACLE存储过程没有返回值，它的所有返回值都是通过out参数来替代的，列表同样也不例外，但是由于是集合，所以不能用一般的参数，必须要用package了。所以要分两部分：
<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;
<3>在JAVA中调用
package com.sp;
//调用一个返回结果集的过程
import java.sql.*;
public class Test1 {
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.1给第一个？赋值
cs.setInt(1,10);
//4.2给第二个？赋值(注册一个问号)
cs.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR);
//5.执行
cs.execute();
//6.得到结果集
ResultSet rs=(ResultSet)cs.getObject(2);
while(rs.next()){
System.out.println(rs.getInt(1)+" "+rs.getString(2));
}
}catch (Exception e){
e.printStackTrace();
}finally{
//7.关闭各种打开的资源
}
}
}
③：编写分页过程：
要求，编写一个存储过程：要求可以输入表名，每页显示记录数，当前页。返回总记录数，总页数，和返回的结果集。
ORACLE的分页
SQL>select t1.*, rownum rn from (select * from emp) t1 //select * from emp=>内嵌视图
SQL>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;
[在分页的时候，可以将上面的SQL语句当做一个模板使用]
分页过程
---a>开发一个包
create or replace package testpackage as
type test_cursor is ref cursor;
end testpackage;
---b>开始编写分页过程
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 imediate v_sql into myrows;
---计算myPageCount
if mod(myrows,pageSize)=0 then
myPageCount:=myrows/pageSize;
else
myPageCount:=myrows/pageSize+1;
end if;
---关闭游标
close p_cursor;
end;
--c> 在JAVA中测试!
package com.sp;
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.1.给前三个输入参数的？赋值
cs.setString(1,"emp");
cs.setInt(2,5);
cs.setInt(3,1);
//4.2.给后面三个输出参数的？赋值
//4.2.1：注册总记录数
cs.registerOutParameter(4, oracle.jdbc.OracleTypes.INTEGER);
//4.2.2: 注册总页数
cs.registerOutParameter(5, oracle.jdbc.OracleTypes.INTEGER);
//4.2.3: 注册返回的结果集
cs.registerOutParameter(6, oracle.jdbc.OracleTypes.CURSOR);
//5.执行
cs.execute();
//6.取出总记录数/这儿要注意，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));
//这里面的"1"是结果集的第一列!
}
}catch (Exception e){
e.printStackTrace();
}finally{
//关闭资源
}
}
}
-----新的需求，要求按薪水的从低到高排序!
结果：修改一下存储过程即可，添加一个 order by sal ///默认是 asc 升序！从低到高!
