How to use REF CURSOR to process Oracle result sets

Oracle provides ref CURSOR, which enables the ability to pass result sets between programs, and can also implement bulk SQL using ref CURSOR to improve SQL performance.

Use the Scott User's EMP table to implement the following test cases:

Sql> desc EMP

Name Null? Type

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

EMPNO not NULL number (4)

Ename VARCHAR2 (10)

JOB VARCHAR2 (9)

MGR Number (4)

HireDate DATE

SAL Number (7,2)

COMM Number (7,2)

DEPTNO Number (2)

Obtain result set output using REF CURSOR:

Sql> set Serveroutput on

Sql> DECLARE

2 TYPE mytable is TABLE of Emp%rowtype;

3 L_data mytable;

4 L_REFC Sys_refcursor;

5 BEGIN

6 OPEN L_REFC for

7 SELECT empno, ename, Job, Mgr, HireDate, Sal, Comm, deptno from EMP;

8

9 FETCH L_REFC BULK COLLECT into L_data;

10

One close L_REFC;

12

The I in 1. L_data. COUNT

LOOP

Dbms_output.put_line (L_data (i) ename

16 | | ' was hired since '

17 | | L_data (i) hiredate

18);

End LOOP;

End;

21/

SMITH was hired since 17-dec-80

ALLEN was hired since 20-feb-81

WARD was hired since 22-feb-81

JONES was hired since 02-apr-81

MARTIN was hired since 28-sep-81

BLAKE was hired since 01-may-81

CLARK was hired since 09-jun-81

SCOTT was hired since 19-apr-87

KING was hired since 17-nov-81

TURNER was hired since 08-sep-81

ADAMS was hired since 23-may-87

JAMES was hired since 03-dec-81

FORD was hired since 03-dec-81

MILLER was hired since 23-jan-82

Pl/sql procedure successfully completed.

Author: 51cto Blog Oracle Little Bastard

Back to the column page: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/