Java中间层调用数据库耗时,在Linux上使用JDBC从Java中间层调用Oracle PL / SQL存储过程？...-CSDN博客

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)?

UPDATE 1

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

QUESTION 1: Does the above look OK?

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

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

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.

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)?

解决方案

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

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).

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.)