Stored function that returns a set of records

Home page

1. Purpose

2. Function that returns an INDEX BY table

3. Function that returns a NESTED table

4. Function that returns a cursor

5. Another function that return a cursor

6. Pipelined function

1. Purpose

How, in a client side application, to use a record set provided by a stored function ?

A record set can be returned by a function (or by an OUT procedure parameter) in two ways :

Ø A REF CURSOR

Ø A collection (NESTED or INDEX BY table)

Here is some basic examples of stored functions that return a record set to a client side application.

2. Function that returns an INDEX BY table

q The database package:

CREATE OR REPLACE PACKAGE PKG_CUR

IS

  -- Collection of EMP raws --

  TYPE TYP_REC_EMP IS TABLE OF EMP%ROWTYPE INDEX BY BINARY_INTEGER ;

  -- Function that returns a collection --

  FUNCTION Get_Raws RETURN TYP_REC_EMP ;

END  PKG_CUR ;

CREATE OR REPLACE PACKAGE BODY PKG_CUR

IS

  FUNCTION Get_Raws RETURN TYP_REC_EMP

IS

     tab TYP_REC_EMP ;

  Begin

     -- Populate the collection --

     Select * BULK COLLECT Into tab From EMP ;

     -- Return the collection --

     RETURN tab ;

  End ;

END  PKG_CUR ;

q The client side request:

SQL> set serveroutput on

SQL> set pagesize 100

SQL> Declare

  2    tab PKG_CUR.TYP_REC_EMP ;

  3  Begin

  5    tab := PKG_CUR.Get_Raws ;

  7    For i IN tab.first .. tab.Last Loop

  8       Dbms_Output.Put_Line( tab(i).ename ) ;

  9    End loop ;

 10  End ;

 11  /

SMITH

ALLEN

WARD

JONES

MARTIN

BLAKE

CLARK

SCOTT

KING

TURNER

ADAMS

JAMES

FORD

MILLER

PL/SQL procedure successfully completed.

SQL>

3. Function that returns a NESTED table

q The database types:

drop type TYP_TAB_REC_EMP
/
drop type TYP_REC_EMP
/
create or replace type TYP_REC_EMP as object
(
  EMPNO NUMBER(4),
  ENAME VARCHAR2(10),
  JOB VARCHAR2(10),
  MGR NUMBER(4),
  HIREDATE DATE,
  SAL NUMBER(7,2),
  COMM NUMBER(7,2),
  DEPTNO NUMBER(2)
)
/
create or replace type TYP_TAB_REC_EMP is table of TYP_REC_EMP

q The function:

CREATE OR REPLACE function ret_cur return TYP_TAB_REC_EMP

tab TYP_TAB_REC_EMP := TYP_TAB_REC_EMP(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);

Cursor C_EMP is

Select *

From EMP ;

i pls_integer := 0 ;

Begin

For CEMP in C_EMP Loop

tab.extend ;

i := i + 1 ;

tab(i) := TYP_REC_EMP(CEMP.EMPNO,CEMP.ENAME,CEMP.JOB,CEMP.MGR,CEMP.HIREDATE,CEMP.SAL,CEMP.COMM,CEMP.DEPTNO);

End loop ;

return tab ;

End;

q The client side request:

Sql*Plus, Forms

SQL> set linesize 200
SQL> select * from table(ret_cur) where deptno=10 ;

   EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- ---------- ---------- -------- ---------- --------------------
   7782 CLARK MANAGER 7839 09/06/81 2450   10
   7839 KING PRESIDENT 17/12/80 5000   10
   7934 MILLER CLERK 7782 09/12/82 1301   10

SQL>

Java

// Select the ENAME column from the EMP table

ResultSet rset = stmt.executeQuery ("select * from table(ret_cur) where deptno=10");

// Iterate through the result and print the employee names

while (rset.next ())

System.out.println (rset.getString (2));

4. Function that returns a cursor

q The database function:

CREATE OR REPLACE FUNCTION Return_Cursor

(

PC$Select IN VARCHAR2

) RETURN SYS_REFCURSOR

cc SYS_REFCURSOR ;

BEGIN

OPEN cc FOR PC$Select ;

RETURN cc ;

END ;

q The client side request:

Sql*Plus, Forms

SQL> set serveroutput on

SQL> DECLARE

2 cur SYS_REFCURSOR ;

3 rec EMP%ROWTYPE ;

4 BEGIN

5 cur := Return_Cursor( 'SELECT * FROM EMP WHERE DEPTNO=10' ) ;

6 LOOP

7 FETCH cur INTO rec ;

8 EXIT WHEN cur%NOTFOUND ;

9 dbms_output.put_line( rec.ENAME ) ;

10 END LOOP ;

11 CLOSE cur ;

12 END;

14 /

CLARK

KING

MILLER

Procédure PL/SQL terminée avec succès.

SQL>

Java

CallableStatement call = conn.prepareCall ("{ ? = call Return_Cursor (?)}");

call.registerOutParameter (1, OracleTypes.CURSOR);

call.setString (2, "SELECT * FROM EMP WHERE DEPTNO=10");

call.execute ();

ResultSet rset2 = (ResultSet)call.getObject (1);

while (rset2.next ())

{

System.out.println( "Ename = " + rset2.getString(2) ) ;

}

5. Another function that returns a cursor

q The database package:

CREATE OR REPLACE PACKAGE Pkg_Cur

……

-- EMP Record --

TYPE R_EMP IS RECORD

(

EMPNO EMP.EMPNO%TYPE,

ENAME EMP.ENAME%TYPE,

JOB EMP.JOB%TYPE,

MGR EMP.MGR%TYPE,

HIREDATE EMP.HIREDATE%TYPE,

SAL EMP.SAL%TYPE,

   COMM     EMP.COMM%TYPE,

DEPTNO EMP.DEPTNO%TYPE

);

-- Cursor returning EMP record --

TYPE TP_CUR IS REF CURSOR RETURN R_EMP ;

……

FUNCTION Get_Raws3 RETURN TP_CUR ;

END Pkg_Cur ;

CREATE OR REPLACE PACKAGE BODY Pkg_Cur

… …

FUNCTION Get_Raws3

RETURN TP_CUR

Cur TP_CUR ;

BEGIN

OPEN Cur FOR SELECT * FROM EMP ;

         RETURN Cur ;

END ;

END Pkg_Cur ;

q The client side request:

Sql*Plus, Forms

SQL> DECLARE

2 cur Pkg_Cur.TP_CUR ;

3 rec EMP%ROWTYPE ;

4 BEGIN

5 cur := Pkg_Cur.Get_Raws3 ;

6 LOOP

7 FETCH cur INTO rec ;

8 EXIT WHEN cur%NOTFOUND ;

9 dbms_output.put_line( rec.EMPNO || ' ' ||rec.ENAME ) ;

10 END LOOP ;

11 CLOSE cur ;

12 END;

13 /

7369 SMITH

7499 ALLEN

7521 WARD

7566 JONES

7654 MARTIN

7698 BLAKE

7782 CLARK

7788 SCOTT

7839 KING

7844 TURNER

7876 ADAMS

7900 JAMES

7902 FORD

7934 MILLER

Procédure PL/SQL terminée avec succès.

SQL>

Java

CallableStatement call = conn.prepareCall ("{ ? = call Pkg_Cur.Get_Raws3 ()}");

call.registerOutParameter (1, OracleTypes.CURSOR);

call.execute ();

ResultSet rset = (ResultSet)call.getObject (1);

while (rset.next ())

{

System.out.println( “Ename = “ + rset.getString(2) ) ;

}

6. Pipelined function

q The database types:

CREATE OR REPLACE TYPE TYP_REC_EMP AS OBJECT

(

EMPNO NUMBER(4),

ENAME VARCHAR2(10),

JOB VARCHAR2(9),

  MGR      NUMBER(4),

HIREDATE DATE,

SAL NUMBER(7,2),

COMM NUMBER(7,2),

DEPTNO NUMBER(2)

)

CREATE OR REPLACE TYPE TYP_TAB_REC_EMP AS TABLE OF TYP_REC_EMP

q The database package:

CREATE OR REPLACE PACKAGE Pkg_Cur

TYPE REC_EMP IS TABLE OF EMP%ROWTYPE INDEX BY BINARY_INTEGER ;

FUNCTION Get_Raws RETURN REC_EMP ;

FUNCTION Get_Raws2 ( cur_lig IN SYS_REFCURSOR )

  RETURN TYP_TAB_REC_EMP PIPELINED ;

END Pkg_Cur ;

CREATE OR REPLACE PACKAGE BODY Pkg_Cur

FUNCTION Get_Raws RETURN REC_EMP

TAB REC_EMP ;

  BEGIN

SELECT * BULK COLLECT INTO TAB FROM EMP ;

RETURN TAB ;

END ;

FUNCTION Get_Raws2 ( cur_lig IN SYS_REFCURSOR )

RETURN TYP_TAB_REC_EMP PIPELINED

Trec TYP_REC_EMP := TYP_REC_EMP(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL ) ;

Remp EMP%ROWTYPE ;

BEGIN

LOOP

FETCH cur_lig INTO Remp ;

EXIT WHEN cur_lig%NOTFOUND ;

-- Handling datas --

Trec.EMPNO := Remp.EMPNO ;

Trec.ENAME := Remp.ENAME ;

Trec.JOB := Remp.JOB ;

Trec.MGR := Remp.MGR ;

Trec.HIREDATE := Remp.HIREDATE ;

Trec.SAL := Remp.SAL * 1.1 ;

Trec.COMM := Remp.COMM ;

Trec.DEPTNO := Remp.DEPTNO ;

-- Return value --

PIPE ROW( Trec ) ;

END LOOP ;

RETURN ;

END ;

END Pkg_Cur ;

q The client side request:

Sql*Plus, Forms

SQL> set linesize 200

SQL>

SQL> SELECT * FROM

2 TABLE(Pkg_Cur.Get_Raws2(CURSOR(SELECT * FROM EMP WHERE DEPTNO = 10)))

3 /

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- -------- ---------- ---------- ----------

7782 CLARK MANAGER 7839 09/06/81 2695 10

7839 KING PRESIDENT 17/12/80 5500 10

7934 MILLER CLERK 7782 09/12/82 1430 10

SQL>

Java

// Select columns from the EMP table

ResultSet rset = stmt.executeQuery (

"SELECT * FROM TABLE(Pkg_Cur.Get_Raws2(CURSOR(SELECT * FROM EMP WHERE DEPTNO = 10)))");

// Iterate through the result and print the employee names

while (rset.next ())

System.out.println (rset.getString (2));