执行匿名pl / sql块并在java中获取结果集 [英] Execute anonymous pl/sql block and get resultset in java

问题描述

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

I would like to execute the anonymous PL/SQL and need to get the resultset object. I got the code which can be done by using cursors inside the PL/SQL block.

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

But the PL/SQL block itself will come from the database as text. So I can't edit that PL/SQL block. And it will return only two values whose column names will be same always. It will return list of 2 column combination values.

这里我给出样本PL / SQL。

Here I am giving sample 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;

任何回复都会非常有用。

Any reply will be so helpful.

推荐答案

这是一个如何执行匿名PL / SQL并获取结果集对象的自包含示例

Here is a self contained example of how to "execute the anonymous PL/SQL and get the resultset object"

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();
    }
}

上面的示例查询select 1 id，'hello '来自双联盟的名字选择2，'peter'来自双联盟;可以被任何查询替换。

The above example query "select 1 id, 'hello' name from dual union select 2, 'peter' from dual;" can be replaced by any query.

这篇关于执行匿名pl / sql块并在java中获取结果集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！