Oracle-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));
}