java - 将匿名 block 中的 plsql 变量值打印到 Java

Question

我正在尝试用 Java 打印 plsql 变量值 (l_console_message)。然而，这种方法似乎并不奏效。我相信 ResultSet 位出了问题。我在这里缺少一些东西。知道出了什么问题吗？

     PreparedStatement statement = null;
     try {
        statement = connection.prepareStatement("\n" +
            " declare " + "\n" +
            " p_schema_name varchar2(400):= upper('" + schema + "'); " + "\n" + 
            " p_temp_table_name varchar2(400) := upper('temp_table_jobs_name'); " + "\n" +
            " l_result varchar2(400); " + "\n" +
            " l_owner varchar2(200); " + "\n" +
            " l_job_name varchar2(200); " + "\n" +
            " l_enabled varchar2(200); " + "\n" +
            " l_console_message varchar2(4000); " + "\n" +
            " cursor c1_temp_table_name is " + "\n" +
                " select " + "\n" +
                    " table_name " + "\n" +  
                " from all_tables " + "\n" +
                " where table_name = p_temp_table_name; " + "\n" +
            " begin " + "\n" +
                " open c1_temp_table_name; " + "\n" +
                " fetch c1_temp_table_name into l_result; " + "\n" +
                " if c1_temp_table_name %notfound then " + "\n" +
                    " execute immediate ' " + "\n" +
                        " create table '||p_schema_name||'.'||p_temp_table_name||' ( " + "\n" +
                            " schema_name varchar2 (1000), " + "\n" +
                            " job_name varchar2(1000), " + "\n" +
                            " status varchar2(100) " + "\n" +
                        " )'; " + "\n" +
                    " l_console_message := p_temp_table_name||' created.'; " + "\n" +
                    " dbms_output.put_line (l_console_message); " + "\n" +
                " end if; " + "\n" +
                " close c1_temp_table_name; " + "\n" +
            " exception when others then " + "\n" +
                " null; " + "\n" +
            " end;");
    ResultSet rs = statement.execute(); 
    while (rs.next()){
        System.out.println(rs.getString(l_console_message));
    }       
} 
catch (SQLException e) {
    System.out.println("ERROR: Unable to run SQL statements for schema " + schema + " in beforeMigrate: " + e.getMessage());
} 
finally {
    if (null != statement) {
        try {
            statement.close();
        } 
        catch (SQLException se) {
            System.out.println("ERROR: Unable to close statement in beforeMigrate: " + se.getMessage());
        }
    }
}

提前致谢:-)

Answer 1

评论中链接的问题显示了您需要做什么的示例，但您似乎很难将其转化为您的情况。

您的匿名 block 不会(也不能)返回结果集，因此它不应该作为查询执行，也不应该是准备好的语句；您需要有一个可调用语句:

 CallableStatement statement = null;
 try {
    statement = connection.prepareStatement("\n" +
 ...

然后您需要将 PL/SQL 变量的值分配给绑定(bind)变量占位符:

 ...
                " l_console_message := p_temp_table_name||' created.'; " + "\n" +
                " ? := l_console_message; " + "\n" +
            " end if; " + "\n" +
 ...

或者根本没有l_console_message(因此甚至不需要声明)，只需将字符串直接分配给绑定(bind)变量占位符即可:

 ...
                " ? := p_temp_table_name||' created.'; " + "\n" +
            " end if; " + "\n" +
 ...

无论如何，dbms_output 调用在这里都没用。 (实际上您可能从 Java 检索 dbms_output 缓冲区，但这需要更多工作)。

然后您需要声明一个 OUT 绑定(bind)变量来接收字符串，并使用 execute() 而不是 executeQuery() 调用语句:

    statement.registerOutParameter(1, Types.VARCHAR);
    statement.execute();

然后您可以检索已放入绑定(bind)变量的字符串值；例如将其直接打印到控制台:

    System.out.println(statement.getString(1));

ResultSet、rs 和循环已完全消失。