Oracle Database ：玩转Oracle学习笔记之（17）：PLSQL编写分页过程

Ⅰ、介绍：分页是任何一个网站都会使用到的技术，因此学习pl/sql一定要掌握分页技术；

Ⅱ、简单的存储过程：无返回值的存储过程：

--现有异常表book：书号，书名，出版社；
--编写一个过程，可以向表中添加书籍，
--通过Java调用该过程 ；


SQL> --建表book；
SQL> create table book(
  2  bookId number ,
  3  bookName varchar2(50),
  4  publishHous varchar2(50));

Table created.



SQL> --编写存储过程；
SQL> --in代表的是，往存储过程输入的一个变量参数，
SQL> --如果不写in，则默认就是一个in；
SQL> --后面还会有out，表是输出参数；
SQL> create or replace procedure mypro(
  2  spBookId in number ,
  3  spBookName in varchar2 ,
  4  spPublishHouse varchar2) is
  5  begin
  6  insert into book values(spBookId , spBookName , spPublishHouse);
  7  end;
  8  /

Procedure created.

㈢、有返回值的存储过程：（非列表）；

①、

--编写一个过程；
--可以输入雇员编号，
--返回该雇员的姓名；
--有输入和输出的存储过程；
--out代表的是，从存储过程中输出的变量参数；
--如果不写out，则默认就是一个in；
create or replace  procedure mypro(
    spNo in number,
    spName out varchar2) is
begin
    select ename into spName from emp where empno=spno;
end;

在java中调用有返回值的存储过程核心代码：

//创建CallableStatment;

CallableStatment cs = connection.prepareCall("{ call mypro(?,?)}");

//给第一个问号赋值；给输入参数；
cs.setInt(1,7788);

//给第二个问号赋值：输出参数；
cs.registerOutParameter(2,oracle.jdbc.OracleTypes.VARCHAR);

//执行；
cs.execute();

//取出返回值；要注意问号的顺序；
String name = cs.getString(2);

System.out.println("7788 的名字是： “ + name);

②、扩展，返回多个值：工资，岗位，姓名；

SQL> --编写一个过程；
SQL> --可以输入雇员编号，
SQL> --扩展，返回多个值：工资，岗位，姓名；
SQL> --有输入和输出的存储过程；
SQL> --out代表的是，从存储过程中输出的变量参数；
SQL> --如果不写out，则默认就是一个in；
SQL> create or replace  procedure mypro(
  2  spNo in number,
  3  spName out varchar2 ,
  4  spSal out number ,
  5  spJob out varchar2 ) is
  6  begin
  7  select ename ,sal , job into spName ,spSal , spJob
  8  from emp where empno=spno;
  9  end;
 10  /

Procedure created.

//创建CallableStatment;

CallableStatment cs = connection.prepareCall("{ call mypro(?,?,?,?)}");

//给第一个问号赋值；给输入参数；
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);


//执行；
cs.execute();

//取出返回值；要注意问号的顺序；
String name = cs.getString(2);

String job = cs.getString(4);

System.out.println("7788 的名字是： “ + name +" 工作岗位是 ： " + job);

㈣、有返回值的存储过程，而且返回的是列表（结果集）；

--案例：编写一个过程，输入部门号，
--返回该部门所有雇员的信息；

--分析：由于Oracle存储过程没有返回值，
--所以他的返回值都是通过out参数来替换的，
--列表同样也不例外，但是由于是集合，
--所以不能用一般的参数，
--必须要用package了；
--所以要分为两部分了；

①、建立一个包：如下：

create or replace package testpackage as
    TYPE test_cursor is ref cursor;
end testpackage;

②、建立存储过程：

create or replace procedure mypro(
    spNo in number ,
    p_cursor out testpackage.test_cursor) is
begin
    open p_cursor for select * from emp where deptno=spNo;
end mypro;

③、如何在Java中调用

//创建CallableStatment;

CallableStatment cs = connection.prepareCall("{ call mypro(?,?)}");

//给第一个问号赋值；给输入参数；
cs.setInt(1,10);

//这里要注意：在关联注册输出参数的时候，每个输出参数都要关联，否则会出错；
//给第二个问号赋值：输出参数；
cs.registerOutParameter(2,oracle.jdbc.OracleTypes.CURSOR);


//执行；
cs.execute();

//取出返回值；
ResultSet rs = (ReslutSet)cs.getObject(2);

while(rs.next()){
    System.out.println(rs.getInt(1)+ "  "+rs.getString(2));
}

案例二：

--编写一个存储过程，
--输入表名称，每页显示的记录数，当前页；
--返回总记录数，总页数，返回的结果集；


--oracle分页：

select t1.* ,rownum rn from (select * from emp) t1

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>=3;


--开始编写分页的过程；

--编写游标包；
create or replace package mypackage as
    TYPE my_cursor is ref cursor;
end mypackage;

--创建存储过程；
create or replace procedure mypro(
    tableName in varchar2 ,
    --一页显示的记录数；
    pageSize in number,
    --当前的页；
    pageNow in number,
    --总记录数；
    myRows out number ,
    --总的页数；
    myPageCount out number,
    --结果集游标；
    p_cursor out mypackage.my_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  ||'order by sal) t1) where rownum<='|| v_end ||') where rn>='|| v_begin;

    --打开游标；
    open p_cursor for v_sql;

    --计算myRows和myPageCount；
    --组织了一个sql语句；
    v_sql:='select count(*) from '||tablename;
    --执行了一个sql语句，并不返回的值赋值给myRows；
    execute immediate v_sql into myRows;

    if mod(myRows , pageSize)=0 then
        myPageCount:= myRows/pageSize;
    else
        myPageCount:=1+myRows/pageSize;
    end if;

    --关闭游标；
    --close p_cursor;

end mypro;

使用Java测试分页：

//创建CallableStatment;

CallableStatment cs = connection.prepareCall("{ call mypro(?,?,?,?,?,?)}");

//给第问号赋值；给输入参数；
cs.setString(1,"EMP");
cs.setInt(2,5);
cs.setInt(3,1);

//给第问号赋值：输出参数；
//注册总记录数；
cs.registerOutParameter(4,oracle.jdbc.OracleTypes.INTEGER);
//注册总页数；
cs.registerOutParameter(5,oracle.jdbc.OracleTypes.INTEGER);
//注册返回的结果集；
cs.registerOutParameter(6,oracle.jdbc.OracleTypes.CURSOR);


//执行；
cs.execute();

//取出返回值；
//取出总的记录数；
int totalNum = cs.getInt(4);
//取出总的页数；
int totalPage = cs.getInt(5);
ResultSet rs = (ReslutSet)cs.getObject(6);

while(rs.next()){
    System.out.println(rs.getInt(1)+ "  "+rs.getString(2));
}