Java调用Oracle存储过程详解编程语言

步骤：

1、编写Oracle存储过程

2、编写数据库获取连接工具类

3、编写简单应用调用存储过程

实现：

1、Oracle存储过程：

/*测试表*/ 
create table test( 
    id varchar2(32), 
    name varchar2(32) 
); 
 
/*存储过程 插入数据*/     
CREATE OR REPLACE PROCEDURE insert_procedure( 
    PARA1 IN VARCHAR2, 
    PARA2 IN VARCHAR2 
) AS 
BEGIN 
  INSERT INTO test (id, name) VALUES (PARA1, PARA2); 
END insert_procedure; 
 
/*存储过程 返回结果集*/ 
CREATE OR REPLACE PROCEDURE select_procedure( 
    para_id IN VARCHAR2, 
    name OUT sys_refcursor /* 这个sys_refcursor类型在SYS.STANDARD包中 */ 
) AS 
BEGIN 
  OPEN name FOR 
    SELECT * FROM test WHERE id = para_id; 
END;

2、JDBC工具类

import java.sql.Connection; 
import java.sql.DriverManager; 
import java.sql.ResultSet; 
import java.sql.SQLException; 
import java.sql.Statement; 
 
public class DBUtil { 
    public static final String DRIVER = "oracle.jdbc.driver.OracleDriver"; 
    public static final String URL = "jdbc:oracle:thin:@localhost:1521/orcl"; 
    public static final String USERNAME = "pfm"; 
    public static final String PASSWORD = "pfm"; 
 
    /** 
     * 通过静态代码块 注册数据库驱动 
     */ 
    static { 
        try { 
            Class.forName(DRIVER); 
        } catch (ClassNotFoundException e) { 
            e.printStackTrace(); 
        } 
    } 
 
    /** 
     * 获得Connection 
     *  
     * @return 
     */ 
    public static Connection getConnection() { 
        Connection conn = null; 
        try { 
            conn = DriverManager.getConnection(URL, USERNAME, PASSWORD); 
        } catch (SQLException e) { 
            e.printStackTrace(); 
        } 
        return conn; 
    } 
 
    /** 
     * 获得Statement 
     *  
     * @return 
     */ 
    public static Statement getStatement() { 
        Statement st = null; 
        try { 
            st = getConnection().createStatement(); 
        } catch (SQLException e) { 
            e.printStackTrace(); 
        } 
        return st; 
    } 
 
    /** 
     * 关闭ResultSet 
     *  
     * @param rs 
     */ 
    public static void closeResultSet(ResultSet rs) { 
        if (rs != null) { 
            try { 
                rs.close(); 
            } catch (SQLException e) { 
                e.printStackTrace(); 
            } 
        } 
    } 
 
    /** 
     * 关闭Statement 
     *  
     * @param st 
     */ 
    public static void closeStatement(Statement st) { 
        if (st != null) { 
            try { 
                st.close(); 
            } catch (SQLException e) { 
                e.printStackTrace(); 
            } 
        } 
    } 
 
    /** 
     * 关闭Connection 
     *  
     * @param conn 
     */ 
    public static void closeConnection(Connection conn) { 
        if (conn != null) { 
            try { 
                conn.close(); 
            } catch (SQLException e) { 
                e.printStackTrace(); 
            } 
        } 
    } 
 
    /** 
     * 关闭全部 
     *  
     * @param rs 
     * @param sta 
     * @param conn 
     */ 
    public static void closeAll(ResultSet rs, Statement sta, Connection conn) { 
        closeResultSet(rs); 
        closeStatement(sta); 
        closeConnection(conn); 
    } 
 
}

3、调用存储过程：

import java.sql.CallableStatement; 
import java.sql.Connection; 
import java.sql.PreparedStatement; 
import java.sql.ResultSet; 
import java.sql.SQLException; 
import oracle.jdbc.driver.OracleTypes; 
 
/** 
 * 测试调用存储过程 
 *  
 */ 
public class StoredTest { 
    public static void main(String[] args) { 
        insert_call(); 
        //select_call(); 
    } 
 
    /** 
     * 执行存储过程 插入数据 
     */ 
    public static void insert_call() { 
        Connection conn = DBUtil.getConnection(); 
        PreparedStatement pst = null; 
 
        CallableStatement proc = null; // 创建执行存储过程的对象 
        try { 
            proc = conn.prepareCall("{ call insert_procedure(?,?) }"); 
            proc.setString(1, "1"); // 设置第一个输入参数 
            proc.setString(2, "hello call"); // 设置第一个输入参数 
            proc.execute();// 执行 
 
        } catch (SQLException e) { 
            e.printStackTrace(); 
        } finally { 
            try { 
                // 关闭IO流 
                proc.close(); 
                DBUtil.closeAll(null, pst, conn); 
            } catch (Exception e) { 
                e.printStackTrace(); 
            } 
        } 
    } 
 
    /** 
     * 执行存储过程 查询数据 
     */ 
    public static void select_call() { 
        Connection conn = DBUtil.getConnection(); 
 
        CallableStatement stmt; 
        try { 
            stmt = conn.prepareCall("{ call select_procedure(?, ?) }"); // 用此调用方法不能实现多行语法 
            stmt.setString(1, "1"); 
            stmt.registerOutParameter(2, OracleTypes.CURSOR); 
            stmt.execute(); 
            ResultSet rs = (ResultSet) stmt.getObject(2); 
            while (rs.next()) { 
                System.out.println(rs.getString("name")); 
            } 
        } catch (SQLException e) { 
            e.printStackTrace(); 
        } finally { 
            DBUtil.closeConnection(conn); 
        } 
    } 
}

作者：blog.ytso.com