java - 使用 JDBC 时 Oracle 临时表计数错误

Question

我有以下数据库对象和Java程序，当我尝试从 Java 调用 Oracle 的存储过程时，无法获取临时表计数。缺少一些内容，但我不知道出了什么问题。请帮助我找到代码中的错误。当我使用 Oracle 调用过程时直接在 Oracle SQL Developer 中的语句能够获取临时表的计数。每次调用插入过程后，它都会在 Oracle 语句调用和 Java 中正确插入并获取计数。但是当我使用 JDBC 尝试相同的操作时，仅插入过程返回计数但获取计数临时表缺少计数并返回零而不是一。

临时表:

CREATE GLOBAL TEMPORARY TABLE TABLE1_TMP(NAME VARCHAR2(10)) ON COMMIT DELETE ROWS;

程序:

CREATE OR REPLACE PROCEDURE INSERT_TABLE1_TMP(IN_NAME IN VARCHAR2,TEMP_COUNT OUT NUMBER)
IS
BEGIN
DBMS_OUTPUT.PUT_LINE('IN_NAME:'||IN_NAME);
 INSERT INTO TABLE1_TMP VALUES(IN_NAME);
 SELECT COUNT(*) INTO TEMP_COUNT FROM TABLE1_TMP;
END INSERT_TABLE1_TMP;

CREATE OR REPLACE PROCEDURE GETCNT_TABLE1_TMP(TEMP_COUNT OUT NUMBER)
IS
BEGIN    
 SELECT COUNT(*) INTO TEMP_COUNT FROM TABLE1_TMP;
 DBMS_OUTPUT.PUT_LINE('COUNT:'||TEMP_COUNT);
END GETCNT_TABLE1_TMP;

执行:

DECLARE
  IN_NAME VARCHAR2(200);
  TEMP_COUNT NUMBER;
BEGIN
  IN_NAME := 'name1';

  INSERT_TABLE1_TMP(
    IN_NAME => IN_NAME,
    TEMP_COUNT => TEMP_COUNT
  );
DBMS_OUTPUT.PUT_LINE('insert TEMP_COUNT1 = ' || TEMP_COUNT);
  GETCNT_TABLE1_TMP(
    TEMP_COUNT => TEMP_COUNT
  );

DBMS_OUTPUT.PUT_LINE('TEMP_COUNT2 = ' || TEMP_COUNT);
END;

输出:

IN_NAME:name1
insert TEMP_COUNT1 = 1
COUNT:1
TEMP_COUNT2 = 1

Java 程序:

Connection dbConnection = null;
CallableStatement callableStatement = null;
CallableStatement callableStatement1 = null;

try {
    dbConnection = getDBConnection();
    callableStatement = dbConnection.prepareCall("{call insert_table1_tmp(?,?)}");

    callableStatement.setString(1, "name1");        
    callableStatement.registerOutParameter(2, OracleTypes.NUMBER); 
    callableStatement.executeUpdate();          
    System.out.println("Insert into temp table count :"+callableStatement.getInt(2));

    callableStatement1 = dbConnection.prepareCall("{call  getCnt_table1_tmp(?)}");           
    callableStatement1.registerOutParameter(1, OracleTypes.NUMBER); 
    callableStatement1.execute();           
    System.out.println("Temp table count :"+callableStatement1.getInt(1));

} catch (SQLException e) {

    System.out.println(e.getMessage());

} finally {             

    if (callableStatement != null) {
        callableStatement.close();
    }
    if (callableStatement1 != null) {
        callableStatement1.close();
    }

    if (dbConnection != null) {
        dbConnection.commit();
        dbConnection.close();
    }

}

Java 输出:

Insert into temp table count :1
Temp table count :0

Answer 1

CREATE GLOBAL TEMPORARY TABLE TABLE1_TMP(NAME VARCHAR2(10)) ON COMMIT DELETE ROWS;

根据我们的讨论，您的临时表会在提交时删除行。如果 JDBC 驱动程序自动提交设置为 true，那么稍后查询该表时将得到零行。