一、任务提出
JAVA操作oracle11g存储过程实验需要完成以下几个实例：
1.调用没有返回参数的过程（插入记录、更新记录）
2.有返回参数的过程
3.返回列表的过程
4.返回带分页的列表的过程。
二、建立表和相应的存储过程
create table student (sno int ,sname varchar2(20),sage int);
--创建存储过程testa1 create or replace procedure testa1(para1 in int,para2 in varchar2,para3 in int) is begin insert into student(sno,sname,sage) values(para1,para2,para3); commit; end; / --创建存储过程testa2 create or replace procedure testa2(para1 in int,para2 in int) is begin update student set sage=para2 where sno=para1; commit; end; / --创建有返回参数的存储过程testb create or replace procedure testb(para1 in int ,para2 out varchar2,para3 out int) is begin select sname,sage into para2,para3 from student where sno=para1; end; --创建返回集合的存储过程： --在oracle中，如果要返回集合必须是返回游标，不能是一张二维表。所以，要先建立包。 create or replace package testpack is type test_cursor is ref cursor; end testpack; / create or replace procedure testc(p_cursor out testpack.test_cursor) is begin open p_cursor for select * from student; end; /
|
1
2
3
4
5
6
7
8
9
|
--实现分页的存储过程---ps 每页几个记录，cs第几页create or replace
procedure testd(ps int
,cs int
,p_cursor out
testpack.test_cursor)
isbegin open
p_cursor for
select
* from (select student.*,rownum rn from
student) where
rn>ps*(cs-1) and
rn<=ps*cs;
end;
/ |
三、java操作调用上述存储过程
package com.oaj; import java.sql.*; import com.sun.org.apache.xalan.internal.xsltc.compiler.util.Type; public class Test { String driver="oracle.jdbc.driver.OracleDriver"; String strUrl="jdbc:oracle:thin:@localhost:1521:orcl"; ResultSet rs=null; Connection conn=null; CallableStatement cstmt=null; public static void main(String[] args) { new Test().testPageSet(3,1); } public void testPageSet(int recordPerPage,int currentPage) { try { Class.forName(driver); conn=DriverManager.getConnection(strUrl,"scott","scott"); cstmt=conn.prepareCall("{ call scott.testd(?,?,?)}"); cstmt.registerOutParameter(3, oracle.jdbc.OracleTypes.CURSOR);//指定是oracle里规定的类型 cstmt.setInt(1,recordPerPage); cstmt.setInt(2, currentPage); cstmt.execute(); rs=(ResultSet)cstmt.getObject(3); while(rs.next()) { System.out.print("学号是："+rs.getInt(1)+"的学生的名字是："+rs.getString(2)+",年龄是："+rs.getInt(3)+"\r\n"); } } catch(SQLException ex) { ex.printStackTrace(); } catch(Exception ex) { ex.printStackTrace(); } finally { try { if(cstmt!=null) cstmt.close(); if(conn!=null) { conn.close(); conn=null; } } catch(SQLException ex) { ex.printStackTrace(); } } } public void testOutResult() { try { Class.forName(driver); conn=DriverManager.getConnection(strUrl,"scott","scott"); cstmt=conn.prepareCall("{ call scott.testc(?)}"); cstmt.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);//指定是oracle里规定的类型 cstmt.execute(); rs=(ResultSet)cstmt.getObject(1); while(rs.next()) { System.out.print("学号是："+rs.getInt(1)+"的学生的名字是："+rs.getString(2)+",年龄是："+rs.getInt(3)+"\r\n"); } } catch(SQLException ex) { ex.printStackTrace(); } catch(Exception ex) { ex.printStackTrace(); } finally { try { if(cstmt!=null) cstmt.close(); if(conn!=null) { conn.close(); conn=null; } } catch(SQLException ex) { ex.printStackTrace(); } } } public void testOutParameter(int inputSno) { try { Class.forName(driver); conn=DriverManager.getConnection(strUrl,"scott","scott"); cstmt=conn.prepareCall("{ call scott.testb(?,?,?)}"); cstmt.setInt(1, inputSno); cstmt.registerOutParameter(2, Types.VARCHAR); cstmt.registerOutParameter(3, Types.INTEGER); cstmt.execute(); String name=cstmt.getString(2); int age=cstmt.getInt(3); System.out.print("学号是："+inputSno+"的学生的名字是："+name+",年龄是："+age); } catch(SQLException ex) { ex.printStackTrace(); } catch(Exception ex) { ex.printStackTrace(); } finally { try { if(cstmt!=null) cstmt.close(); if(conn!=null) { conn.close(); conn=null; } } catch(SQLException ex) { ex.printStackTrace(); } } } public void testNoOutParameterUpdate(int inputeSno,int inputSage) { try { Class.forName(driver); conn=DriverManager.getConnection(strUrl,"scott","scott"); cstmt=conn.prepareCall("{ call scott.testa2(?,?)}"); cstmt.setInt(1, inputeSno); cstmt.setInt(2, inputSage); cstmt.execute(); System.out.println("执行成功！"); } catch(SQLException ex) { ex.printStackTrace(); } catch(Exception ex) { ex.printStackTrace(); } finally { try { if(cstmt!=null) cstmt.close(); if(conn!=null) { conn.close(); conn=null; } } catch(SQLException ex) { ex.printStackTrace(); } } } public void testNoOutParameterInsert(int a,String b,int c) { try { Class.forName(driver); conn=DriverManager.getConnection(strUrl,"scott","scott"); cstmt=conn.prepareCall("{ call scott.testa1(?,?,?)}"); cstmt.setInt(1, a); cstmt.setString(2, b); cstmt.setInt(3, c); cstmt.execute(); } catch(SQLException ex) { ex.printStackTrace(); } catch(Exception ex) { ex.printStackTrace(); } finally { try { if(cstmt!=null) cstmt.close(); if(conn!=null) { conn.close(); conn=null; } } catch(SQLException ex) { ex.printStackTrace(); } } } }