在Linux上使用JDBC从Java中间层调用Oracle PL/SQL存储过程? [英] Calling Oracle PL/SQL stored procedure from java middle tier using JDBC on Linux?

问题描述

新手问题...任何人都可以提供有关在PL/SQL存储过程(驻留在数据库Linux服务器中)将数据发送到Java程序(在应用程序服务器中)需要做什么的高级描述. ?

Newbie question... Can anyone provide a high level description of what things need to be done in a PL/SQL stored procedure (residing in a database Linux server) to send data to a Java program (in an application server)?

更新1

下面的elrado的回答使我前进(谢谢！).我可以看到PL/SQL存储过程只需要将OUT参数设置为REF CURSOR(例如SYS_REFCURSOR).然后，调用Java例程可以使用如下代码:

elrado's answer below moved me forward (thanks!). I can see that the PL/SQL stored procedure simply needs to set an OUT parameter as REF CURSOR (e.g. SYS_REFCURSOR). Then, the calling Java routine can use something like this:

import oracle.jdbc.*;
...
// call stored procedure using SQL92 syntax
CallableStatement cs = conn.prepareCall( "{call myStoredProc (?,?,?,?,?)}" );

// set IN parameters
cs.setString(1, in1var);
cs.setString(2, in2var);
cs.setString(3, in3var);

// register OUT parameters
cs.registerOutParameter(4, Types.VARCHAR);
cs.registerOutParameter(5, OracleTypes.CURSOR);

// execute and retrieve Oracle "ref cursor" as a Java "ResultSet"
cs.execute();
rs = (ResultSet) cs.getObject(5);

// process result
while (rs.next()) {
  ...
}

// always retrieve ResultSet before OUT parameters
out1var = cs.getInt(4);

问题1:以上内容看起来还可以吗?

QUESTION 1: Does the above look OK?

我在Oracle文档中看到了(请参阅第4-14至4-15页 http://isu.ifmo.ru/docs/doc112/java.112/e10589.pdf )，我应该使用OracleCallableStatement而不是CallableStatement，这样它应该看起来像这样:

I see in Oracle's documentation (see pages 4-14 to 4-15 http://isu.ifmo.ru/docs/doc112/java.112/e10589.pdf) that I should use an OracleCallableStatement instead of a CallableStatement so that it should look like this:

// execute and retrieve Oracle "ref cursor" as a Java "ResultSet"
cs.execute();
rs = {(OracleCallableStatement)cs}.getCursor(5);

问题2:两种方法都可以吗?如果是这样，那么使用一个相对于另一个的优点和缺点是什么?

QUESTION 2: Are both methods fine? If so, what are the pro's and con's for using one over the other?

问题3:我不理解关于Oracle 11G数据库的第4-15页的Important注释:

QUESTION 3: I didn't understand the Important note on page 4-15 regarding Oracle 11G database:

Unlike in past releases, the cursor associated with a REF CURSOR is not closed when the result set object in which the REF CURSOR was materialized is closed.

这是否意味着在cs关闭时关闭了ref cursor(与以前的数据库版本相反，在rs关闭时关闭了ref cursor)?

Does this mean that the ref cursor is closed when cs is closed (as opposed to previous database releases, which closed ref cursor when rs is closed)?

推荐答案

如果要从Oralce PL/SQL过程返回结果集，请使用ref游标:

If you want to return resultset from Oralce PL/SQL procedure use ref cursor:

http://www.oracle-base.com/articles/misc/UsingRefCursorsToReturnRecordsets.php

问题1:是的，看起来不错，但我尚未对其进行测试. 第二季度: 我相信OracleCallableStatement只是扩展了CallableStatement.两者都很好，但是只要有可能，我都在使用Oracle驱动程序来连接和使用Oracle db(而不是某些通用驱动程序).

Q1: Yes it LOOKS ok but I had not tested it. Q2: I belive OracleCallableStatement just extends CallableStatement. Both are fine but when ever possible I am using Oracle driver to connect and work with Oracle db (and not some generic driver).

问题3:我相信您是正确的，并且在关闭结果集时光标保持打开状态.(自从过程中读取结果集以来已经有一段时间了，所以我不记得关闭光标时的抱歉.现在，我在家，无法访问Oracle数据库和我的工作计算机.)

Q3:I belive you're right and cursor stays open when you close resultset. (It has been some time since I read resultset from procedure so I don't remember when I closed cursor sorry. And right now I am at home and don't have access to Oracle db and my work computer.)

这篇关于在Linux上使用JDBC从Java中间层调用Oracle PL/SQL存储过程?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！