Using REF CURSORS with Java v42.7.3.2
A REF CURSOR
is a cursor variable that contains a pointer to a query result set returned by an OPEN
statement. Unlike a static cursor, a REF CURSOR
isn't tied to a particular query. You can open the same REF CURSOR
variable any number of times with the OPEN
statement containing different queries. Each time, a new result set is created for that query and made available by way of the cursor variable. A REF CURSOR
can also pass a result set from one procedure to another.
EDB Postgres Advanced Server supports the declaration of both strongly typed and weakly typed REF CURSOR
variables. A strongly typed cursor must declare the shape
(the type of each column) of the expected result set. You can use only a strongly typed cursor with a query that returns the declared columns. Opening the cursor with a query that returns a result set with a different shape causes the server to return an exception. On the other hand, a weakly typed cursor can work with a result set of any shape.
To declare a strongly typed REF CURSOR
:
TYPE <cursor_type_name> IS REF CURSOR RETURN <return_type>;
To declare a weakly typed REF_CURSOR
:
name SYS_REFCURSOR;
Using a REF CURSOR to retrieve a ResultSet
The stored procedure shown in the following (getEmpNames
) builds two REF CURSOR
variabes on the server. The first REF CURSOR
contains a list of commissioned employees in the emp
table. The second REF CURSOR
contains a list of salaried employees in the emp
table:
CREATE OR REPLACE PROCEDURE getEmpNames ( commissioned OUT SYS_REFCURSOR, salaried OUT SYS_REFCURSOR ) IS BEGIN OPEN commissioned FOR SELECT ename FROM emp WHERE comm is NOT NULL; OPEN salaried FOR SELECT ename FROM emp WHERE comm is NULL; END;
The RefCursorSample()
method shown in the following invokes the getEmpName()
stored procedure and displays the names returned in each of the two REF CURSOR
variables:
public void RefCursorSample(Connection con) { try { con.setAutoCommit(false); String commandText = "{call getEmpNames(?,?)}"; CallableStatement stmt = con.prepareCall(commandText); stmt.registerOutParameter(1, Types.REF); stmt.registerOutParameter(2, Types.REF); stmt.execute(); ResultSet commissioned = (ResultSet)stmt.getObject(1); System.out.println("Commissioned employees:"); while(commissioned.next()) { System.out.println(commissioned.getString(1)); } ResultSet salaried = (ResultSet)stmt.getObject(2); System.out.println("Salaried employees:"); while(salaried.next()) { System.out.println(salaried.getString(1)); } } catch(Exception err) { System.out.println("An error has occurred."); System.out.println("See full details below."); err.printStackTrace(); } }
A CallableStatement
prepares each REF CURSOR
(commissioned
and salaried
). Each cursor is returned as an OUT
parameter of the stored procedure, getEmpNames()
:
String commandText = "{call getEmpNames(?,?)}"; CallableStatement stmt = con.prepareCall(commandText);
The call to registerOutParameter()
registers the parameter type (Types.REF
) of the first REF CURSOR
(commissioned
) :
stmt.registerOutParameter(1, Types.REF);
Another call to registerOutParameter()
registers the second parameter type (Types.REF
) of the second REF CURSOR
(salaried
) :
stmt.registerOutParameter(2, Types.REF);
A call to stmt.execute()
executes the statement:
stmt.execute();
The getObject()
method retrieves the values from the first parameter and casts the result to a ResultSet
. Then, RefCursorSample
iterates through the cursor and prints the name of each commissioned employee:
ResultSet commissioned = (ResultSet)stmt.getObject(1); while(commissioned.next()) { System.out.println(commissioned.getString(1)); }
The same getter method retrieves the ResultSet
from the second parameter, and RefCursorExample
iterates through that cursor, printing the name of each salaried employee:
ResultSet salaried = (ResultSet)stmt.getObject(2); while(salaried.next()) { System.out.println(salaried.getString(1)); }
- On this page
- Using a REF CURSOR to retrieve a ResultSet