在java中执行匿名pl/sql块并获取结果集 [英] 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' name from dual union select 2, 'peter' from dual;"可以被任何查询替换.
The above example query "select 1 id, 'hello' name from dual union select 2, 'peter' from dual;" can be replaced by any query.
这篇关于在java中执行匿名pl/sql块并获取结果集的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!