Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Table of Contents


Overview

A stored procedure can pass a result set, resulting from execution of a SQL statement, to a client
using a cursor variable (REF CURSOR).

Like a cursor, a cursor variable points to the current row in the result set of a multi-row
query.

Cursor variables are used to pass query result sets between PL/SQL stored
subprograms and client programs. Neither PL/SQL nor any client program owns a
result set.

They share a pointer to the work area where the result set is stored.

The order of processing a result set

 The followings are descriptions about a procedure for processing a result set in ALTIBASE HDB.

To see details, refer to the following examples(Passing a REF CURSOR as a parameter).

  • Create a typeset which containing a corresponding type. And create user-defined type which is a REF CURSOR.
  • Create the stored procedure, which having OUT parameters.
  • Execute a procedure.

On the other hand, in case of Oracle, processing a result set can be done as follows

  • Declaring REF CURSOR Types and Cursor Variables, passing Cursor Variables As Parameters can be executed in a procedure.

Passing a REF CURSOR as a parameter

The example described below shows how to pass a ref cursor as parameter to a client program in ORACLE.

Code Block
DECLARE
TYPE empcurtyp IS REF CURSOR;
TYPE namelist IS TABLE OF employees.e_lastname%TYPE;
TYPE sallist IS TABLE OF employees.salary%TYPE;
emp_cv empcurtyp;
names namelist;
sals sallist;
BEGIN
OPEN emp_cv FOR SELECT e_lastname, salary FROM employees
WHERE emp_job = 'manager';
FETCH emp_cv BULK COLLECT INTO names, sals;
CLOSE emp_cv;
-- loop through the names and sals collections
FOR i IN names.FIRST .. names.LAST

LOOP
DBMS_OUTPUT.PUT_LINE('Name = ' || names(i) || ', salary = ' || sals(i));
END LOOP;
END;
/

An example below arouses same result as above in ALTIBASE HDB.

Code Block
CREATE OR REPLACE PROCEDURE fetch_employee(v_emp_job varchar(20))
AS
TYPE empcurtyp IS REF CURSOR;
TYPE namelist IS TABLE OF CHAR(20) INDEX BY INTEGER;
TYPE sallist IS TABLE OF NUMBER(38) INDEX BY INTEGER;
emp_cv empcurtyp;
names namelist;
sals sallist;

BEGIN
OPEN emp_cv FOR 'SELECT e_lastname, salary FROM employees WHERE emp_job = ?' USING v_emp_job;
FETCH emp_cv INTO names, sals;
CLOSE emp_cv;

-- loop through the names and sals collections
FOR i IN names.FIRST() .. names.LAST()

LOOP
SYSTEM_.PRINTLN('Name = ' || names[i] || ', salary = ' || sals[i]);
END LOOP;
END;
/