PL/SQL的进阶----编写分页过程

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 升序！从低到高!