java调用存储过程

首先先做有返回值的存储过程
1， 建一个程序包。如下：

CREATE OR REPLACE PACKAGE USERSCOREPAGE AS

TYPE Test_CURSOR IS REF CURSOR;

end USERSCOREPAGE;

2，建立存储过程，存储过程为：

CREATE OR REPLACE PROCEDURE TESTC(p_CURSOR out userscorepage.Test_CURSOR) IS

BEGIN

OPEN p_CURSOR FOR SELECT * FROM web_shopping_score_user;

END TESTC;


public static void main(String[] args) throws SQLException {
// TODO Auto-generated method stub
DataManage db = new DataManage(); //装载数据库驱动
ResultSet rs = null;
Connection conn = null;
try {
conn = DBManager.getRunner().getDataSource().getConnection();
CallableStatement proc = null;
proc = conn.prepareCall("{ call wap.testc(?) }");//wap表示数据库的用户名
proc.registerOutParameter(1,oracle.jdbc.OracleTypes.CURSOR);
proc.execute();
rs = (ResultSet)proc.getObject(1);
while(rs.next())
{
System.out.println(rs.getString(1));
System.out.println(rs.getString(2));

}
conn.commit();
rs.close();
proc.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
try {
conn.rollback();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
e.printStackTrace();
}
finally{
conn.setAutoCommit(true);
conn.close();
}
}

下面讲没有返回值的调用存储过程
创建无返回值的存储过程
CREATE OR REPLACE PROCEDURE TESTA(PARA1 IN varchar2,PARA2 IN varchar2)  AS
seq number;
BEGIN
   select web_shopping_score_user_seq.nextval into seq from dual;
   INSERT INTO web_shopping_score_user (uuid,score,PHONENUM) VALUES (seq,PARA1,PARA2);
END TESTA;
下面做测试
public static void main(String[] args) throws SQLException  {
        // TODO Auto-generated method stub
        DataManage db = new DataManage();
        ResultSet rs = null;
            Connection conn = null;
            try {
                conn = DBManager.getRunner().getDataSource().getConnection();
                 CallableStatement proc = null;
                  proc = conn.prepareCall("{ call wap.TESTA(?,?) }");
                  proc.setInt(1, 444);
                  proc.setString(2, "999");
                  proc.execute();
                  conn.commit();
                  proc.close();
                  System.out.println("ok");
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                try {
                    conn.rollback();
                } catch (SQLException e1) {
                    // TODO Auto-generated catch block
                    e1.printStackTrace();
                }
                e.printStackTrace();
            }
        finally{
            conn.setAutoCommit(true);
               conn.close();
        }
    }
好了