转载:http://developer.51cto.com/art/200906/130448.htm
创建需要的测试表:create table Test(tidvarchar2(10),tnamevarchar2(10));
第一种情况:无返回值.
- create or replace proceduretest_a(param1invarchar2,param2invarchar2)as
- begin
- insert into test value(param1,param2);
- end;
Java调用代码:
- package com.test;
- import java.sql.*;
- import java.io.*;
- import java.sql.*;
- public class TestProcA
- {
- public TestProcA(){
- }
- public static void main(String[]args)
- {
- ResultSet rs=null;
- Connection conn=null;
- CallableStatement proc=null;
- try{
- Class.forName("oracle.jdbc.driver.OracleDriver");
- conn=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:test","test","test");
- proc=conn.prepareCall("{calltest_a(?,?)}");
- proc.setString(1,"1001");
- proc.setString(2,"TestA");
- proc.execute();
- }catch(Exceptione){
- e.printStackTrace();
- }finally{
- try{
- if(null!=rs){
- rs.close();
- if(null!=proc){
- proc.close();
- }
- if(null!=conn){
- conn.close();
- }
- }
- }catch(Exceptionex){
- }
- }
- }
- }
第二种情况:有返回值的存储过程(返回值非列表).
存储过程为:
- create or replace procedur etest_b(param1invarchar2,param2outvarchar2)
- as
- begin
- select tname into param2 from test where tid=param1;
- end;
Java调用代码:
- package com.test;
- import java.sql.*;
- import java.io.*;
- import java.sql.*;
- public class TestProcB
- {
- public TestProcB(){
- }
- public static void main(String[]args)
- {
- Connection conn=null;
- CallableStatement proc=null;
- try{
- Class.forName("oracle.jdbc.driver.OracleDriver");
- conn=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:test","test","test");
- proc=conn.prepareCall("{calltest_b(?,?)}");
- proc.setString(1,"1001");
- proc.registerOutParameter(2,Types.VARCHAR);
- proc.execute();
- System.out.println("Outputis:"+proc.getString(2));
- }catch(Exceptione){
- e.printStackTrace();
- }finally{
- try{
- if(null!=proc){
- proc.close();
- }
- if(null!=conn){
- conn.close();
- }
- }catch(Exceptionex){
- }
- }
- }
- }
第三种情况:返回列表.
由于oracle存储过程没有返回值,它的所有返回值都是通过out参数来替代的,列表同样也不例外,但由于是集合,所以不能用一般的参数,必须要用pagkage了.要分两部分来写:
- create or replace packaget package as
- typet_cursorisrefcursor;
- proceduretest_c(c_refoutt_cursor);
- end;
- createorreplacepackagebodytpackageas
- proceduretest_c(c_refoutt_cursor)is
- begin
- openc_refforselect*fromtest;
- endtest_c;
- endtpackage;
Java调用代码:
- package com.test;
- import java.sql.*;
- import java.io.*;
- import java.sql.*;
- public class TestProcB
- {
- public TestProcB(){
- }
- publics tatic void main(String[]args)
- {
- Connection conn=null;
- CallableStatement proc=null;
- ResultSet rs=null;
- try{
- Class.forName("oracle.jdbc.driver.OracleDriver");
- conn=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:test","test","test");
- proc=conn.prepareCall("{?=calltpackage.test_b(?)}");
- proc.registerOutParameter(1,OracleTypes.CURSOR);
- proc.execute();
- while(rs.next()){
- System.out.println(rs.getObject(1)+"\t"+rs.getObject(2));
- }
- }catch(Exceptione){
- e.printStackTrace();
- }finally{
- try{
- if(null!=rs){
- rs.close();
- if(null!=proc){
- proc.close();
- }
- if(null!=conn){
- conn.close();
- }
- }
- }catch(Exceptionex){
- }
- }
- }
- }
Hibernate调用oracle存储过程
- this.pnumberManager.getHibernateTemplate().execute(
- newHibernateCallback()...{
- public Object doInHibernate(Sessionsession)
- throws HibernateException,SQLException...{
- CallableStatementcs=session.connection().prepareCall("{callmodifyapppnumber_remain(?)}");
- cs.setString(1,foundationid);
- cs.execute();
- returnnull;
- }
- });