Oracle存儲過程-用JAVA調用總結

用JAVA調用oracle存儲過程總結

一、無返回值的存儲過程

測試表

-- Create table create table TESTTB ( 
  	ID   VARCHAR2(30),   
	NAME VARCHAR2(30)
 ) 
tablespace BOM  
 	pctfree 10  
 	initrans 1  
 	maxtrans 255 
  storage   ( 
    	initial 64K     
	minextents 1 
   	 maxextents unlimited  
 );

例: 存储过程为(当然了，这就先要求要建张表TESTTB,里面两个字段（I_ID，I_NAME）。 )：

CREATE OR REPLACE PROCEDURE TESTA(PARA1 IN VARCHAR2, PARA2 IN VARCHAR2) 
AS 
	BEGIN 
  		INSERT INTO BOM.TESTTB(ID, NAME) VALUES (PARA1, PARA2); 
	END TESTA; 

在Java里调用时就用下面的代码：

package com.yiming.procedure.test;  
import java.sql.CallableStatement; 
import java.sql.Connection; 
import java.sql.DriverManager; 
import java.sql.ResultSet; 
import java.sql.SQLException;
 import java.sql.Statement;  
public class TestProcedureDemo1 {  
public TestProcedureDemo1() {  } 
  public static void main(String[] args) {  
String driver = "Oracle.jdbc.driver.OracleDriver"; 
  String strUrl = "jdbc:Oracle:thin:@10.20.30.30:1521:vasms";   

Statement stmt = null;   ResultSet rs = null;   

Connection conn = null; 
  CallableStatement proc = null;   

try {    Class.forName(driver); 
    conn = DriverManager.getConnection(strUrl, "bom", "bom");  
  proc = conn.prepareCall("{ call BOM.TESTA(?,?) }");   
  proc.setString(1, "100");   
  proc.setString(2, "TestOne");   
proc.execute(); 
  } catch (SQLException ex2) { 
ex2.printStackTrace(); 
  } catch (Exception ex2) {    
 ex2.printStackTrace();  
} finally { 
   try {    
  if (rs != null) {     
  rs.close(); 
     if (stmt != null) {       
    stmt.close(); 
     } 
     if (conn != null) {       
    conn.close();      
    } 
    } 
   } catch (SQLException ex1) { 
   } 
  } 
 } 
}