DB2® for Linux, UNIX, and Windows stored procedures can have OUT parameters of the cursor type. To retrieve data from those parameters in SQLJ applications, you use iterators or ResultSet objects.
To retrieve data from cursor variables, follow these steps:
CREATE TYPE myRowType AS ROW (name VARCHAR(128))
CREATE TYPE myCursorType AS myRowType CURSOR
CREATE PROCEDURE MYPROC(IN pempNo VARCHAR(6), OUT pcv1 myCursorType)
RESULT SETS 0
LANGUAGE SQL
BEGIN
SET pcv1 = CURSOR FOR SELECT name FROM employee WHERE empNo = pempNo;
OPEN pcv1;
END
The following code calls stored procedure MYPROC
and uses a positioned iterator to retrieve data from cursor pcv1.
The numbers to the right of selected statements correspond to the
previously-described steps.#sql iterator Iter (String); // Declare a positioned iterator
…
Iter iter = null; // Output parameter 1
String hvPempNo="000500"; // Input parameter 2
#sql [ctx] {CALL MYPROC (:IN hvPempNo, :OUT iter)}; 3
// Call the stored procedure
String hvEmpName = null;
while (true) { // Retrieve rows from the result set
#sql { FETCH :iter into :hvName }; 4
if (iter.endFetch()) break;
System.out.println("Employee name for " + hvPempNo
+ ": " + hvEmpName);
}
The following code calls stored procedure MYPROC and uses
a ResultSet object to retrieve data from cursor
pcv1. The numbers to the right of selected statements correspond to
the previously-described steps.…
ResultSet rs = null; // Output parameter 1
String hvPempNo="000500"; // Input parameter 2
#sql [ctx] {CALL MYPROC (:IN hvPempNo, :OUT rs)}; 3
// Call the stored procedure
String hvEmpName = null;
while (rs.next()) { // Retrieve result set rows 4
hvEmpName=rs.getString(1);
System.out.println("Employee name for " + hvPempNo
+ ": " + hvEmpName);
}