Java调用Oracle存储过程应该加上花括号

来源：互联网发布：java中文api 编辑：程序博客网时间：2024/06/05 16:42

Java调用Oracle存储过程，应该要有花括号“{}”,这样SP内部执行有异常抛出时，Java端才可以在execute时马上catch到异常，而不用要等到获取游标时才抛出游标关闭异常。代码：

SP：

CREATE OR REPLACE PROCEDURE SP_EXCEPTION_TEST(V_PARAM IN VARCHAR2,                                              CUR     OUT SYS_REFCURSOR) AS  V_A VARCHAR2(10);BEGIN  --make exception occur  SELECT A.B INTO V_A FROM ZENG_TEST_T A WHERE A.A = V_PARAM AND ROWNUM = 1;  OPEN CUR FOR    SELECT * FROM ZENG_TEST_T A;END;

Java:

package spTest;import java.sql.CallableStatement;import java.sql.Connection;import java.sql.ResultSet;import oracle.jdbc.OracleTypes;import spMigration.EnumDBType;import spMigration.ODS_OracleJdbc_V2;/** * 测试过程发现SP内部报错，Java端catch不到异常，此类写例子查找原因 *  * @author zengxh *  */public class SPExceptionJavaCatch {ODS_OracleJdbc_V2 jdbc = new ODS_OracleJdbc_V2();public static void main(String[] args) throws Exception {SPExceptionJavaCatch a = new SPExceptionJavaCatch();a.testSPException_wrong("abc");a.testSPException_right("abc");}private void testSPException_right(String param) throws Exception {Connection conn = null;CallableStatement stmt = null;ResultSet rs = null;// 调用SP应该要有花括号,这样SP内部执行有异常抛出时，Java端才可以在execute时马上catch到异常String sql = "{call SP_EXCEPTION_TEST(?,?)}";try {conn = jdbc.getOConn(EnumDBType.ODS_PP);stmt = conn.prepareCall(sql);stmt.setString(1, param);stmt.registerOutParameter(2, OracleTypes.CURSOR);stmt.execute();rs = (ResultSet) stmt.getObject(2);// rs = ((OracleCallableStatement) stmt).getCursor(2);while (rs.next()) {System.out.println(rs.getBoolean(1));}} catch (Exception e) {e.printStackTrace();} finally {conn.close();}}private void testSPException_wrong(String param) throws Exception {Connection conn = null;CallableStatement stmt = null;ResultSet rs = null;// 调用SP没有花括号,SP内部执行有异常抛出时，Java端不可以在execute时马上catch到异常，要到获取游标时才抛出游标关闭异常String sql = "call SP_EXCEPTION_TEST(?,?)";try {conn = jdbc.getOConn(EnumDBType.ODS_PP);stmt = conn.prepareCall(sql);stmt.setString(1, param);stmt.registerOutParameter(2, OracleTypes.CURSOR);stmt.execute();rs = (ResultSet) stmt.getObject(2);// rs = ((OracleCallableStatement) stmt).getCursor(2);while (rs.next()) {System.out.println(rs.getBoolean(1));}} catch (Exception e) {e.printStackTrace();} finally {conn.close();}}}

执行Java，Console信息：

<span style="color:#ff0000;"><span style="background-color: rgb(255, 255, 255);">java.sql.SQLException: Cursor is closed.</span>at oracle.jdbc.driver.T4CResultSetAccessor.getCursor(T4CResultSetAccessor.java:332)at oracle.jdbc.driver.ResultSetAccessor.getObject(ResultSetAccessor.java:97)at oracle.jdbc.driver.OracleCallableStatement.getObject(OracleCallableStatement.java:1579)at oracle.jdbc.driver.OracleCallableStatementWrapper.getObject(OracleCallableStatementWrapper.java:815)at spTest.SPExceptionJavaCatch.testSPException_wrong(SPExceptionJavaCatch.java:69)at spTest.SPExceptionJavaCatch.main(SPExceptionJavaCatch.java:22)</span>java.sql.SQLException: ORA-01403: 未找到任何数据ORA-06512: 在 "EDI_MAINT.SP_EXCEPTION_TEST", line 9ORA-06512: 在 line 1at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:206)at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1034)at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:191)at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:950)at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1222)at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3387)at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3488)at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:3857)at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1374)at spTest.SPExceptionJavaCatch.testSPException_right(SPExceptionJavaCatch.java:39)at spTest.SPExceptionJavaCatch.main(SPExceptionJavaCatch.java:23)

红字是testSPException_wrong方法的信息，直到执行到

rs = (ResultSet) stmt.getObject(2);

这条语句时才抛出异常，而且异常信息不准确。

下面的是testSPException_right方法的信息，执行到

stmt.execute();

时，就及时抛出异常，而且异常信息准确。

所以，写调用Oracle 存储过程的sql时应该加上花括号：

String sql = "<span style="color:#ff0000;">{</span>call SP_EXCEPTION_TEST(?,?)<span style="color:#ff0000;">}</span>";

0 0