Oracle基础（五）pl/sql进阶(分页过程)
编写分页过程
通过pl/sql实现分页过程，再该过程中由简单到难一步步深入，目的在于通过该案例熟悉pl/sql的各种存储过程，包，游标，如何在java中调用等内容的学习。1、无返回值的存储过程
例如 1、可以向book表添加书，
---建表
Create table book(bookId number,bookName varchar2（50）,publishHouse varchar2（50））;
--编写过程 in 表示变量为输入值，如果不写默认为输入值，而不是输出变量,out为输出值
Create or replace procedure sp_pro7(spBookId in number,spbookName in varchar2,sppublishHouse in varchar2)is
Begin
Insert into book valuse(spBookId ,spbookName,sppublishHouse );
End
---java 中如何调用
Package com.sp;
Importjava.sql.*;
Publicclass Test1{
public static void main(string[] args){
try{
//1加载驱动
Class.forName("Oracle.jdbc.driver.OracleDriver");
Connetionct=DriverManger.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:myora1","scott","123");
//2创建callablestatement
Callablestatement cs=ct.preparecall(“{call sp_pro7(?,?,?)}”)；
//给？赋值
Cs.setInt(1,10);
Cs.setstring(2，“笑傲江湖”)；
Cs.setstring(3,"人民出版社")；
//执行
Cs.execute();;
}
Catch(EXCEPTION E){
e.printstacktrace();
}
Finally{
关闭各个链接
}
}
}
2、有返回值的存储过程
例如输入书编号返回书姓名
Create or replace procedure sp_pro8(spno in number,spkName out varchar2，spsal out varchar2) is
Begin
select ename ，spsal,into spName， spsal form emp where empno=spno;
End
在java中如何调用
Package com.sp;
Importjava.sql.*;
Publicclass Test1{
public static void main(string[] args){
try{
//1加载驱动
Class.forName("Oracle.jdbc.driver.OracleDriver");
Connetionct=DriverManger.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:myora1","scott","123");
//2创建callablestatement
Callablestatement cs=ct.preparecall(“{call sp_pro8(?,?)}”)；
//给？赋值
Cs.setInt(1,7788);
Cs.registerOutParemeter（2，oracle.jdbc.oracleTyps.varchar）//执行
Cs.registerOutParemeter（3，oracle.jdbc.oracleTyps.double）//执行 //oracle.jdbc.oracleTyps.varchar 表示执行的类型
Cs.execute();
//取出返回值,要注意？顺序
String name=sc.getstring(2);
String job=cs.getstring(3);
System.out.println(“7788的名字”+name +“7788的工资”+sal);
}
Catch(EXCEPTION E){
e.printstacktrace();
}
Finally{
关闭各个链接
}
}
}
3、有返回值的存储过程，以列表结果集的形式返回
oracle存储过程本身没有返回时用out参数替代的，集合需要用package
--建立包，定义类型test_cursor
Create or replace packagetestpackage as
Typetest_cursur is ref cursor;
End testpackage
建立存储过程
Create or repalce procedure sp_pro9(spNo in number,P_cursor out tespackage,test_cursor) is
begin
Open p_cursor for select *from emp where depto-spNo;
End;
--如何在java中调用
Package com.sp;
Importjava.sql.*;
Publicclass Test1{
public static void main(string[] args){
try{
//1加载驱动
Class.forName("Oracle.jdbc.driver.OracleDriver");
Connetionct=DriverManger.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:myora1","scott","123");
//2创建callablestatement
Callablestatement cs=ct.preparecall(“{call sp_pro9(?,?)}”)；
//给？赋值
Cs.setInt(1,10);
Cs.registerOutParemeter（2，oracle.jdbc.oracleTypes.cursor）
//执行
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{
关闭各个链接
}
}
}
4、----oracle的分页规律介绍
Sql>select t1.*， rownum rn from(select * from emp) t1;
Sql>select t1.*， rownum rn from(select * from emp) t1 where rownum<=10;
--在分页的时候，可以
Select *from(
Sql>select t1.*， rownum rn from(select * from emp) t1where rownum<=10;) where rn>=6;
--开发一个包
Create or replace package testpackage as
Typetest_cursur is ref cursor;
End testpackage
5、--编写分写的过程 分页并按工资排序
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_endnumber:=Pagenow *Pagesize ;
Begin
--执行部分
v_sql:='Select * from(
Sql>select t1.*， rownum rn from(select * from '||tableName||' order by sal) 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;
Executeimmediate 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;
--使用java测试
Package com.sp;
Importjava.sql.*;
Public class Test1{
public static void main(string[] args){
try{
//1加载驱动
Class.forName("Oracle.jdbc.driver.OracleDriver");
Connetionct=DriverManger.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:myora1","scott","123");
//2创建callablestatement
Callablestatement cs=ct.preparecall(“{call fenye(?,?,?,?,?,?)}”)；
//给？赋值
Cs.setString(1,"emp"); 表
Cs.setInt(2,5);每页大小
Cs.setInt(3,2);第2页
//接受总记录数
Cs.registerOutParemeter（4，oracle.jdbc.oracleTypes.integer）//执行
//注册总页数
Cs.registerOutParemeter（5，oracle.jdbc.oracleTypes.integer）//执行
//注册返回的结果集
Cs.registerOutParemeter（6，oracle.jdbc.oracleTypes.cursor）//执行
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(20)+"薪水"；
}
}
}
Catch(EXCEPTION E){
e.printstacktrace();
}
Finally{
关闭各个链接
}
}
}
看似很复杂一个分页过程，通过一步步由简到繁的分解，由无返回值的存储过程，到有返回值，进而到以集合形式作为返回值的存储过程，加上简单的分页算法，基于pl/sql中包，游标，mod语句，if分支语句，变量的定义，oder by子句组成。学习的过程就是这样，一口一个胖子是不可能的，同样这也是人类认知的一个规律，由简到繁，在遇到复杂的问题要考虑如何将它拆分成简单的，自己熟知的问题，一步步了解。