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