一、分页
1、无返回值的存储过程
1)案例:现有一张表book,表结构如下:
- 编写一个过程,可以向book表添加书,要求通过java程序调用该过程。
SQL> create table book( 2 bookId number, 3 bookName varchar2(50), 4 publishHouse varchar2(50)); Table created SQL> --编写过程 SQL> --in:表示输入参数。缺省时默认为in。 SQL> --out:表示输出参数。 SQL> create or replace procedure sp_pro7 2 (spBookId in number,spBookName in varchar2,sppublicHouse varchar2) is 3 begin 4 insert into book values(spBookId,spBookName,sppublicHouse); 5 end; 6 / Procedure created
- //调用一个无返回值的过程
import java.sql.CallableStatement; import java.sql.Connection; import java.sql.DriverManager; public class Test1 { public static void main(String[] args) { // TODO Auto-generated method stub CallableStatement cs = null; Connection ct = null; try { // 加载驱动 Class. forName("oracle.jdbc.driver.OracleDriver"); ct = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl", "scott", "***" ); cs = ct.prepareCall( "{call sp_pro7(?,?,?)}"); cs.setInt(1, 10); cs.setString(2, "笑傲江湖"); cs.setString(3, "人民出版社" ); cs.execute(); } catch (Exception e) { // TODO: handle exception e.printStackTrace(); } finally { try { // 关闭资源 cs.close(); ct.close(); } catch (Exception e1) { // TODO Auto-generated catch block e1.printStackTrace(); } } } }
2、有返回值的存储过程(非列表)
1)案例:编写一个过程,可以输入雇员的编号,返回该雇员的姓名、工资和岗位。
SQL> --有输入和输出的存储过程 SQL> create or replace procedure sp_pro8 2 (spno in number,spName out varchar2,spSal out number,spJob out varchar2) is 3 begin 4 select ename,sal,job into spName,spSal,spJob from emp where empno=spno; 5 end; 6 / Procedure created
//调用有返回值的过程 import java.sql.CallableStatement; import java.sql.Connection; import java.sql.DriverManager; public class Test2 { public static void main(String[] args) { // TODO Auto-generated method stub CallableStatement cs = null; Connection ct = null; try { // 加载驱动 Class. forName("oracle.jdbc.driver.OracleDriver"); ct = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl", "scott", "***" ); cs = ct.prepareCall( "{call sp_pro8(?,?,?,?)}"); // 赋值 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 sal = cs.getString(3); String job = cs.getString(4); System. out.println( "7788 的名字是:" + name + "薪水是:" + sal + ",工作是:" + job ); } catch (Exception e) { // TODO: handle exception e.printStackTrace(); } finally { try { // 关闭资源 cs.close(); ct.close(); } catch (Exception e1) { // TODO Auto-generated catch block e1.printStackTrace(); } } } }
3、有返回值的存储过程(列表【结果集】)
案例:编写一个过程,输入部门号,返回该部门所有雇员的信息。
- 分析:由于oracle存储过程没有返回值,它的所有返回值都是通过out参数来替代的,列表同样也不例外。但由于是集合,所以不能用一般的参数,必须要用package,分为两部分:
- 建一个包
SQL> --返回结果集的过程 SQL> --建立包,在该包中,定义类型test_cursor,是一个游标 SQL> create or replace package testpackage as 2 type test_cursor is ref cursor; 3 end testpackage; 4 / Package created
- 建立存储过程
SQL> --2、创建存储过程 SQL> create or replace procedure sp_pro9 2 (spNo in number,p_cursor out testpackage.test_cursor) is 3 begin 4 open p_cursor for select * from emp where deptno=spNo; 5 end; 6 / Procedure created
import java.sql.CallableStatement; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; public class Test3 { public static void main(String[] args) { // TODO Auto-generated method stub CallableStatement cs = null; Connection ct = null; try { // 加载驱动 Class. forName("oracle.jdbc.driver.OracleDriver"); ct = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl", "scott", "***" ); cs = ct.prepareCall( "{call sp_pro9(?,?)}"); // 赋值 cs. setInt(1, 10); cs.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR ); // 执行 cs.execute(); // 得到结果集 ResultSet rs = (ResultSet) cs.getObject(2); System. out.println( "10号部门:"); while ( rs.next()) { System. out.println( "empno:" + rs.getInt(1) + "empName:" + rs .getString(2)); } } catch (Exception e) { // TODO: handle exception e.printStackTrace(); } finally { try { // 关闭资源 cs.close(); ct.close(); } catch (Exception e1) { // TODO Auto-generated catch block e1.printStackTrace(); } } } }
4、编写分页过程
案例:编写一个存储过程,要求可以输入表名、每页显示记录数、当前页,返回总记录数、总页数和返回的结果集
- 分页查询
- select * from emp;
- select t1.*,rownum rn from (select * from emp) t1;
- select t1.*,rownum rn from (select * from emp) t1 where rownum<=10;
- select * from (select t1.*,rownum rn from (select * from emp) t1 where rownum<=10) where rownum>=6;
SQL> --新建包 SQL> create or replace package testpackage as 2 type test_cursor is ref cursor; 3 end testpackage; 4 / Package created
SQL> --编写分页的存储过程 SQL> create or replace procedure fenye 2 (tableName in varchar2, 3 myPageSize in number, 4 pageNow in number, 5 myRows out number,--总记录数 6 myPageCount out number,--总页数 7 p_cursor out testpackage.test_cursor--返回的结果集 8 ) is 9 --定义部分 10 --定义sql语句 字符串 11 v_sql varchar2(1000); 12 --定义两个整数 13 v_begin number:=(pageNow-1)*myPageSize+1; 14 v_end number:=pageNow*myPageSize; 15 begin 16 v_sql:='select * from (select t1.*,rownum rn from (select * from '||tableName|| 17 ') t1 where rownum<='||v_end||') where rn>='||v_begin; 18 --把游标和sql语句关联 19 open p_cursor for v_sql; 20 --计算myRows 21 v_sql:='select count(*) from '||tableName; 22 execute immediate v_sql into myRows; 23 --计算myPageCount 24 if mod(myRows,myPageSize)=0 then 25 myPageCount:=myRows/myPageSize; 26 else 27 myPageCount:=myRows/myPageSize+1; 28 end if; 29 --关闭游标 30 close p_cursor; 31 end; 32 / Procedure created
import java.sql.CallableStatement; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; public class Test4 { public static void main(String[] args) { // TODO Auto-generated method stub Connection ct = null; CallableStatement cs = null; try { Class. forName("oracle.jdbc.driver.OracleDriver"); ct = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl", "scott", "***" ); cs = ct.prepareCall( "{call fenye(?,?,?,?,?,?)}"); // 赋值 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 rowNum = cs.getInt(4); // 取出总页数 int pageCount = cs.getInt(5); ResultSet rs = (ResultSet) cs.getObject(6); // 显示 System. out.println( "rowNum=" + rowNum); System. out.println( "pageCount=" + pageCount); while ( rs.next()) { System. out.println( "编号:" + rs .getInt(1) + ",姓名:" + rs .getString(2)); } } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { try { // 关闭资源 cs.close(); ct.close(); } catch (Exception e1) { // TODO Auto-generated catch block e1.printStackTrace(); } } } }