java下实现调用oracle的存储过程和函数

在oracle下创建一个test的账户，然后

1.创建表：STOCK_PRICES
[cpp] view plaincopy
--创建表格 
CREATE TABLE STOCK_PRICES( 
    RIC VARCHAR(6) PRIMARY KEY, 
    PRICE NUMBER(7,2), 
    UPDATED DATE ); 
2.插入测试数据：
[sql] view plaincopy
--插入数据 
INSERT INTO stock_prices values('1111',1.0,SYSDATE); 
INSERT INTO stock_prices values('1112',2.0,SYSDATE); 
INSERT INTO stock_prices values('1113',3.0,SYSDATE); 
INSERT INTO stock_prices values('1114',4.0,SYSDATE); 
3.建立一个返回游标：
PKG_PUB_UTILS

[cpp] view plaincopy
--建立一个返回游标 
CREATE OR REPLACE PACKAGE PKG_PUB_UTILS IS 
    --动态游标 
    TYPE REFCURSOR IS REF CURSOR; 
END PKG_PUB_UTILS; 
4.创建和存储过程：P_GET_PRICE
[sql] view plaincopy
--创建存储过程 
CREATE OR REPLACE PROCEDURE P_GET_PRICE 
( 
  AN_O_RET_CODE OUT NUMBER, 
  AC_O_RET_MSG  OUT VARCHAR2, 
  CUR_RET OUT PKG_PUB_UTILS.REFCURSOR, 
  AN_I_PRICE IN NUMBER 
)  
IS 
BEGIN 
    AN_O_RET_CODE := 0; 
    AC_O_RET_MSG  := '操作成功'; 
     
    OPEN CUR_RET FOR 
        SELECT * FROM STOCK_PRICES WHERE PRICE<AN_I_PRICE; 
EXCEPTION 
    WHEN OTHERS THEN 
        AN_O_RET_CODE := -1; 
        AC_O_RET_MSG  := '错误代码:' || SQLCODE || CHR(13) || '错误信息:' || SQLERRM; 
END P_GET_PRICE; 
5.创建函数：
[cpp] view plaincopy
--创建函数：F_GET_PRICE 
CREATE OR REPLACE FUNCTION F_GET_PRICE(v_price IN NUMBER) 
    RETURN PKG_PUB_UTILS.REFCURSOR 
AS 
    stock_cursor PKG_PUB_UTILS.REFCURSOR; 
BEGIN 
    OPEN stock_cursor FOR 
    SELECT * FROM stock_prices WHERE price < v_price; 
    RETURN stock_cursor; 
END; 
6.JAVA调用存储过程返回结果集
JDBCoracle10G_INVOKEPROCEDURE.java

[java] view plaincopy
import java.sql.*; 
import oracle.jdbc.OracleCallableStatement; 
import oracle.jdbc.OracleTypes; 
 
/* 本例是通过调用oracle的存储过程来返回结果集:
* oracle 9i、10G 的jdbc由1个jar包组成：classes12.zip
*/ 
public class JDBCoracle10G_INVOKEPROCEDURE { 
    Connection conn = null; 
    Statement statement = null; 
    ResultSet rs = null; 
    CallableStatement stmt = null; 
 
    String driver; 
    String url; 
    String user; 
    String pwd; 
    String sql; 
    String in_price; 
 
    public JDBCoracle10G_INVOKEPROCEDURE()  
    { 
        driver = "oracle.jdbc.driver.OracleDriver"; 
        url = "jdbc:oracle:thin:@localhost:1521:ORCL"; 
        // oracle 用户 
        user = "test"; 
        // oracle 密码 
        pwd = "test"; 
        init(); 
        // mysid：必须为要连接机器的sid名称，否则会包以下错： 
        // java.sql.SQLException: Io 异常: Connection 
        // refused(DESCRIPTION=(TMP=)(VSNNUM=169870080)(ERR=12505)(ERROR_STACK=(ERROR=(CODE=12505)(EMFI=4)))) 
        // 参考连接方式: 
        // Class.forName( "oracle.jdbc.driver.OracleDriver" ); 
        // cn = DriverManager.getConnection( 
        // "jdbc:oracle:thin:@MyDbComputerNameOrIP:1521:ORCL", sUsr, sPwd ); 
 
    } 
 
    public void init() { 
        System.out.println("oracle jdbc test"); 
        try { 
            Class.forName(driver); 
            System.out.println("driver is ok"); 
            conn = DriverManager.getConnection(url, user, pwd); 
            System.out.println("conection is ok"); 
            statement = conn.createStatement(); 
            // conn.setAutoCommit(false); 
            // 输入参数 
            in_price = "3.0"; 
            // 调用函数 
            stmt = conn.prepareCall("call P_GET_PRICE(?,?,?,?)"); 
            stmt.registerOutParameter(1, java.sql.Types.FLOAT); 
            stmt.registerOutParameter(2, java.sql.Types.CHAR); 
            stmt.registerOutParameter(3, oracle.jdbc.OracleTypes.CURSOR); 
            stmt.setString(4, in_price); 
            stmt.executeUpdate(); 
            int retCode = stmt.getInt(1); 
            String retMsg = stmt.getString(2); 
            if (retCode == -1) { // 如果出错时，返回错误信息 
                System.out.println("报错！"); 
            } else { 
                // 取的结果集的方式一： 
                rs = ((OracleCallableStatement) stmt).getCursor(3); 
                // 取的结果集的方式二： 
                // rs = (ResultSet) stmt.getObject(3); 
                String ric; 
                String price; 
                String updated; 
                // 对结果进行输出 
                while (rs.next()) { 
                    ric = rs.getString(1); 
                    price = rs.getString(2); 
                    updated = rs.getString(3); 
                    System.out.println("ric:" + ric + ";-- price:" + price 
                            + "; --" + updated + "; "); 
                } 
            } 
 
        } catch (Exception e) { 
            e.printStackTrace(); 
        } finally { 
            System.out.println("close "); 
        } 
    } 
 
    public static void main(String args[])// 自己替换［］ 
    { 
        new JDBCoracle10G_INVOKEPROCEDURE(); 
    } 
} 
7.开发JAVA调用函数返回结果集
JDBCoracle10G_INVOKEFUNCTION.java

[java] view plaincopy
import java.sql.*; 
import oracle.jdbc.OracleCallableStatement; 
import oracle.jdbc.OracleTypes; 
 
/*
/* 本例是通过调用oracle的函数来返回结果集:
* oracle 9i、10G 的jdbc由1个jar包组成：classes12.zip  
*/ 
public class JDBCoracle10G_INVOKEFUNCTION { 
    Connection conn = null; 
    Statement statement = null; 
    ResultSet rs = null; 
    CallableStatement stmt = null; 
 
    String driver; 
    String url; 
    String user; 
    String pwd; 
    String sql; 
    String in_price; 
 
    public JDBCoracle10G_INVOKEFUNCTION() 
    { 
        driver = "oracle.jdbc.driver.OracleDriver"; 
        url = "jdbc:oracle:thin:@localhost:1521:ORCL"; 
        // oracle 用户 
        user = "test"; 
        // oracle 密码 
        pwd = "test"; 
        init(); 
        // mysid：必须为要连接机器的sid名称，否则会包以下错： 
        // java.sql.SQLException: Io 异常: Connection 
        // refused(DESCRIPTION=(TMP=)(VSNNUM=169870080)(ERR=12505)(ERROR_STACK=(ERROR=(CODE=12505)(EMFI=4)))) 
        // 参考连接方式: 
        // Class.forName( "oracle.jdbc.driver.OracleDriver" ); 
        // cn = DriverManager.getConnection( 
        // "jdbc:oracle:thin:@MyDbComputerNameOrIP:1521:ORCL", sUsr, sPwd ); 
    } 
 
    public void init() { 
        System.out.println("oracle jdbc test"); 
        try { 
            Class.forName(driver); 
            System.out.println("driver is ok"); 
            conn = DriverManager.getConnection(url, user, pwd); 
            System.out.println("conection is ok"); 
            statement = conn.createStatement(); 
            // conn.setAutoCommit(false); 
            // 输入参数 
            in_price = "5.0"; 
            // 调用函数 
            stmt = conn.prepareCall("{? = call F_GET_PRICE(?)}"); 
            // stmt.registerOutParameter(1, java.sql.Types.FLOAT); 
            // stmt.registerOutParameter(2, java.sql.Types.CHAR); 
            stmt.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR); 
            stmt.setString(2, in_price); 
            stmt.executeUpdate(); 
            // 取的结果集的方式一： 
            rs = ((OracleCallableStatement) stmt).getCursor(1); 
            // 取的结果集的方式二： 
            // rs = (ResultSet) stmt.getObject(1); 
            String ric; 
            String price; 
            String updated; 
 
            while (rs.next()) { 
                ric = rs.getString(1); 
                price = rs.getString(2); 
                updated = rs.getString(3); 
                System.out.println("ric:" + ric + ";-- price:" + price + "; --" 
                        + updated + "; "); 
            } 
 
        } catch (Exception e) { 
            e.printStackTrace(); 
        } finally { 
            System.out.println("close "); 
        } 
    } 
 
    public static void main(String args[])// 自己替换［］ 
    { 
        new JDBCoracle10G_INVOKEFUNCTION(); 
    } 
} 
   http://blog.csdn.net/xw13106209/article/details/6905259