JAVA預存程序調用

建立需要的測試表:create table Test(tid varchar2(10),tname varchar2(10));

  第一種情況：無傳回值.

      create or replace procedure test_a(param1 in varchar2,param2 in varchar2) 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("{ call test_a(?,?) }");
          proc.setString(1, "1001");
          proc.setString(2, "TestA");
          proc.execute();
        }catch(Exception e){
     e.printStackTrace();
 }finally{
           try{
       if(null!=rs){
                 rs.close();

          if(null!=proc){
                    proc.close();
          }

          if(null!=conn){
                    conn.close();
          } 
       }  
           }catch(Exception ex){

           }
        }
   }

}

第二種情況：有傳回值的預存程序(傳回值非列表).

預存程序為:
create or replace procedure test_b(param1 in varchar2,param2 out varchar2)
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("{ call test_b(?,?) }");
          proc.setString(1, "1001");
          proc.registerOutParameter(2, Types.VARCHAR);
          proc.execute();
   System.out.println("Output is:"+proc.getString(2));
        }catch(Exception e){
     e.printStackTrace();
 }finally{
           try{

          if(null!=proc){
                    proc.close();
          }

          if(null!=conn){
                    conn.close();
          }
 
           }catch(Exception ex){

           }
        }
   }

}

第三種情況：返回列表.

由於oracle預存程序沒有傳回值，它的所有傳回值都是通過out參數來替代的，列表同樣也不例外，但由於是集合，所以不能用一般的參數，必須要用pagkage了.要分兩部分來寫：
create or replace package tpackage as
type t_cursor is ref cursor;
procedure test_c(c_ref out t_cursor);
end ;

create or replace package body tpackage as
procedure test_c(c_ref out t_cursor) is
   begin
      open c_ref for select * from test;
  end test_c;
end tpackage;

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;
        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("{? = call tpackage.test_b(?) }");
          
          proc.registerOutParameter(1,OracleTypes.CURSOR);
          proc.execute();
          while(rs.next()){
              System.out.println(rs.getObject(1)+"/t"+rs.getObject(2));
          }
        }catch(Exception e){
     e.printStackTrace();
 }finally{
           try{
          if(null!=rs){
              rs.close();
             if(null!=proc){
                    proc.close();
             }

             if(null!=conn){
                    conn.close();
             }
          }
          }catch(Exception ex){

           }
        }
   }

}

 

Hibernate調用oracle儲存過程

　this.pnumberManager.getHibernateTemplate().execute(

　　new HibernateCallback() ...{

　　 public Object doInHibernate(Session session)

　　 throws HibernateException, SQLException ...{

　　 CallableStatement cs = session.connection().prepareCall("{call modifyapppnumber_remain(?)}");

　　 cs.setString(1, foundationid);

　　 cs.execute();

　　 return null;

　　}

　});