执行匿名 pl/sql 块并在 java 中获取结果集

java resultset jdbc plsql

2022-09-01 16:46:08

我想执行匿名PL / SQL，需要获取结果集对象。我得到了可以通过在PL / SQL块内使用游标来完成的代码。

但是PL / SQL块本身将作为文本来自数据库。所以我不能编辑PL / SQL块。它将仅返回两个列名始终相同的值。它将返回 2 列组合值的列表。

在这里，我给出了PL / SQL的示例。

BEGIN

RETURN 'select distinct fundname d, fundname r from <table> where condition order by 1';

EXCEPTION
   WHEN OTHERS THEN
    RETURN 'SELECT ''Not Available'' d, ''Not Available'' r FROM dual';
END;

任何回复都将非常有帮助。

答案 1

下面是如何“执行匿名 PL/SQL 并获取结果集对象”的独立示例

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Types;

import oracle.jdbc.OracleTypes;

public class CallPLSQLBlockWithOneInputStringAndOneOutputStringParameterAndOneOutputCursorParameter {

    public static void main(String[] args) throws Exception {

        DriverManager.registerDriver(new oracle.jdbc.OracleDriver());

        // Warning: this is a simple example program : In a long running application,
        // error handlers MUST clean up connections statements and result sets.

        final Connection c = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE", "system", "manager");
        String plsql = "" +
        " declare " +  
        "    p_id varchar2(20) := null; " +
        "    l_rc sys_refcursor;" +
        " begin " +
        "    p_id := ?; " +
        "    ? := 'input parameter was = ' || p_id;" +
        "    open l_rc for " +
        "        select 1 id, 'hello' name from dual " +
        "        union " +
        "        select 2, 'peter' from dual; " +
        "    ? := l_rc;" +
        " end;";

        CallableStatement cs = c.prepareCall(plsql);
        cs.setString(1, "12345");
        cs.registerOutParameter(2, Types.VARCHAR);
        cs.registerOutParameter(3, OracleTypes.CURSOR);

        cs.execute();

        System.out.println("Result = " + cs.getObject(2));

        ResultSet cursorResultSet = (ResultSet) cs.getObject(3);
        while (cursorResultSet.next ())
        {
            System.out.println (cursorResultSet.getInt(1) + " " + cursorResultSet.getString(2));
        } 
        cs.close();
        c.close();
    }
}

上面的示例查询“选择 1 个 id，'hello' 名称从双并集中选择 2，'peter' 从双;”可以替换为任何查询。

答案 2

首先，您发布的代码无效。匿名 PL/SQL 块不能返回表达式。没有PL / SQL块可以返回这样的查询结果。您需要执行一些操作，例如声明 REF CURSOR 并使用各种 SQL 语句打开该游标。

由于匿名PL / SQL块无法向调用方返回任何内容，因此您描述的体系结构存在问题。至少，您需要修改匿名块，以便您的JDBC代码可以注册一个绑定变量。类似的东西（改编自Menon的Easter Oracle JDBC编程中的一个例子（请注意，我可能引入了一些小的语法错误）

CallableStatement stmt := null;
ResultSet         rset := null;
String            query := 'DECLARE 
                              FUNCTION get_result
                                RETURN SYS_REFCURSOR
                              AS
                                l_rc SYS_REFCURSOR;
                              BEGIN
                                OPEN l_rc 
                                 FOR SELECT DISTINCT fundname d, fundname r
                                       FROM some_table
                                      WHERE some_condition
                                      ORDER BY 1;
                                RETURN l_rc;
                              EXCEPTION
                                WHEN others THEN
                                  OPEN l_rc 
                                   FOR SELECT 'Not Available' d, 'Not Available' r
                                         FROM dual;
                                  RETURN l_rc;
                              END get_result;
                            BEGIN
                              ? := get_result;
                            END;';
try {
  cstmt := conn.prepareCall( query );
  cstmt.registerOutParameter( 1, OracleTypes.CURSOR );
  cstmt.execute();
  rset := (ResultSet) cstmt.getObject( 1 );
}
finally {
  <<close cstmt & rset>>
}