Plsql Procedure returns Record set

Complex Recordsets

It's not always possible to build the required recordset from a single SQL query, especially when trying to produce complex reports. Sometimes several passes through the data are required to acheive the desired data. Oracle allows you to do this using temporary tables or PL/SQL tables. Although the examples in this article could easily be done using SQL alone they do illustrate the methods available:

• Temporary Tables
• PL/SQL Tables
• Test It

Temporary Tables

Using this method we can create a temporary table to hold the data while we process it. Once the processing is complete we can use a REF CURSOR to pass the recordset out to an application.

First we create a temporary table to hold the data during the processing:

CREATE GLOBAL TEMPORARY TABLE my_temp_table
(
empno         NUMBER(4),
ename         VARCHAR2(10),
sal           NUMBER(7,2),
complex       NUMBER(7,2)
) ON COMMIT PRESERVE ROWS;

Next we create a package to hold a REF CURSOR type:

CREATE OR REPLACE PACKAGE Types AS 
  TYPE cursor_type IS REF CURSOR;
END Types; 
/

Next a procedure is defined to use the ref cursor and the temporary table:

CREATE OR REPLACE
PROCEDURE GetComplexEmpRS (p_deptno    IN  emp.deptno%TYPE,
                           p_recordset OUT Types.cursor_type) AS 
BEGIN
  -- Populate temporary table.
  INSERT INTO my_temp_table
    (empno, ename, sal)
  SELECT empno, ename, sal
  FROM   emp
  WHERE  deptno = p_deptno;
  
  -- Do complex processing that can't be done from SQL alone.
  FOR cur_row IN (SELECT * FROM my_temp_table) LOOP
    UPDATE my_temp_table
    SET    complex = cur_row.sal + 1
    WHERE  empno   = cur_row.empno;
  END LOOP;

  -- Open REF CURSOR for outout.
  OPEN p_recordset FOR
    SELECT empno,
           ename,
           sal,
           complex
    FROM   my_temp_table
    ORDER BY ename;
END GetComplexEmpRS;
/

PL/SQL Tables

This method is essentially the same only the temporary table is replaced by a PL/SQL table. First we create the relevant database types:

CREATE TYPE My_Row_Type AS OBJECT
(
empno         NUMBER(4),
ename         VARCHAR2(10),
sal           NUMBER(7,2),
complex       NUMBER(7,2)
);
/

Next a table type is defined using the previous object type as a rowtype:

CREATE TYPE My_Tab_Type IS TABLE OF My_Row_Type;
/

Next we create a package to hold a REF CURSOR type:

CREATE OR REPLACE PACKAGE Types AS 
  TYPE cursor_type IS REF CURSOR;
END Types; 
/

Next a procedure is defined to use the ref cursor and the table type:

CREATE OR REPLACE
PROCEDURE GetComplexEmpRS (p_deptno    IN  emp.deptno%TYPE,
                           p_recordset OUT Types.cursor_type) AS 
  v_tab  My_Tab_Type := My_Tab_Type();
BEGIN
  
  -- Populate PL/SQL table.
  FOR cur_row IN (SELECT * FROM emp WHERE deptno = p_deptno) LOOP
    v_tab.extend;
    v_tab(v_tab.Last) := My_Row_Type(cur_row.empno, cur_row.ename, cur_row.sal, NULL);
  END LOOP;

  -- Do complex processing that can't be done from SQL alone.
  FOR cur_row IN 1 .. v_tab.count LOOP
    v_tab(cur_row).complex := v_tab(cur_row).sal + 1;
  END LOOP;
  
  -- Open REF CURSOR for outout.
  OPEN p_recordset FOR
    SELECT empno,
           ename,
           sal,
           complex
    FROM   Table(Cast(v_tab As My_Tab_Type))
    ORDER BY ename; 
END GetComplexEmpRS;
/

The TABLE and CAST functions allow the PL/SQL table to be referenced from SQL.

Test It

Finally the recordset can be referenced using PL/SQL, Java, ADO etc. The following example uses ADO:

Dim conn, cmd, rs

Set conn = Server.CreateObject("adodb.connection")
conn.Open "DSN=TSH1;UID=scott;PWD=tiger"

Set cmd = Server.CreateObject ("ADODB.Command")
Set cmd.ActiveConnection = conn
cmd.CommandText = "GetComplexEmpRS"
cmd.CommandType = 4 'adCmdStoredProc

Dim param1
Set param1 = cmd.CreateParameter ("deptno", adInteger, adParamInput)
cmd.Parameters.Append param1
param1.Value = 30

Set rs = cmd.Execute

Do Until rs.BOF Or rs.EOF
  -- Do something
  rs.MoveNext
Loop

rs.Close
conn.Close
Set rs     = nothing
Set param1 = nothing
Set cmd    = nothing
Set conn   = nothing