PL/SQL Reference I - Cursors

===========================================
Cursors Syntax
===========================================
Explicit Cursor Syntax
Implicit Cursor Syntax
Implicit Cursor with Parameter Syntax
WHERE CURRENT OF Syntax
REF Cursors
REF Cursor types
REF Cursors Example
Using REF Cursor as argument in a procedure Example
Autonomous Transaction

===========================================
Explicit Cursor Syntax
===========================================
DECLARE
  CURSOR emp_cursor IS
  SELECT employee_id, last_name FROM EMPLOYEES
  WHERE department_id = 20;

  v_emp_id employees.employee_id%TYPE;
  v_lst_name employees.last_name%TYPE;
BEGIN
  OPEN emp_cursor;
    LOOP;
      FETCH emp_cursor INTO v_emp_id, v_lst_name;
      //do something
      EXIT WHEN emp_cursor%NOTFOUND;
    END LOOP;
  CLOSE emp_cursor;
END;
/

===========================================
Implicit Cursor Syntax
===========================================
DECLARE
  CURSOR emp_cursor IS
  SELECT employee_id, last_name FROM EMPLOYEES
  WHERE department_id = 20;

  v_emp_id employees.employee_id%TYPE;
  v_lst_name employees.last_name%TYPE;
BEGIN
  FOR emp_record IN emp_cursor
    LOOP
      //do something
    END LOOP;
  CLOSE emp_cursor;
END;
/

===========================================

Implicit Cursor with Parameter Syntax
===========================================
DECLARE
  CURSOR emp_cursor (p_dept_id DEPARTMENTS.dept_id%TYPE) IS
  SELECT employee_id, last_name FROM EMPLOYEES
  WHERE department_id = p_dept_id;

  v_emp_id employees.employee_id%TYPE;
  v_lst_name employees.last_name%TYPE;
BEGIN
  FOR emp_record IN emp_cursor
    LOOP
      //do something
    END LOOP;
  CLOSE emp_cursor;
END;
/

===========================================
WHERE CURRENT OF Syntax
===========================================
DECLARE
  CURSOR emp_cursor (p_dept_id DEPARTMENTS.dept_id%TYPE) IS
  SELECT employee_id, last_name FROM EMPLOYEES
  WHERE department_id = p_dept_id;

  v_emp_id employees.employee_id%TYPE;
  v_lst_name employees.last_name%TYPE;
BEGIN
  FOR emp_record IN emp_cursor
    LOOP
      //do something

      UPDATE EMPLOYEES
         SET salary=...
       WHERE CURRENT OF emp_cursor
    END LOOP;
  CLOSE emp_cursor;
END;
/

===========================================

REF Cursors
===========================================

Ref Cursor is a reference to a result set.

It is a better alternative to working with in memory Oracle tables.
With Ref Cursors, no need to load the whole result set into  a table variable, and have the Client wait until all the rows are returned.

Instead, the Client has only a reference to result set, and can access rows immediately.

REF Cursors Type

Weak vs Strong Cursors

With Weak Cursor, no return type is defined. 

TYPE ref_type IS REF_CURSOR

With Strong Cursor, return type is defined. This method is less error prone, and is more restrictive.

TYPE emp_cur_type IS REF_CURSOR RETURN employees%ROWTYPE

Limitations with REF Cursors

- Cannot use with remote subprograms.

- Cannot use with FOR UPDATE

- Cannot be assigned to NULL

- more...

REF Cursors Syntax

1. Declare cursor type

2. Declare cursor variable.

3. Open cursor

  

===========================================

REF Cursors General Example
===========================================

1. Declare cursor type

CREATE OR REPLACE PACKAGE GEN_PKG

  TYPE emp_refcur_type IS REF_CURSOR RETURN employees%ROWTYPE;

END;

2. Declare cursor variable.

emp_cur emp_refcur_type;

3. Open cursor

OPEN emp_cur FOR 'SELECT statement';

4. Fetch results

FETCH emp_cur INTO v_emp_rec

5. Close cursor

CLOSE emp_cur

===========================================

Using Weak REF Cursor Example
===========================================

CREATE OR REPLACE PACKAGE EMP_PKG

  TYPE emp_refcur_type IS REF_CURSOR;

END;

CREATE OR REPLACE PACKAGE_BODY EMP_PKG AS 

PROCEDURE get_emp_by_dept_id_proc 

             (p_dept_id IN employees.dept_id%TYPE,

              p_emp_cur OUT EMP_PKG.emp_refcur) IS

  stmt VARCHAR2(2000) := 'SELECT * FROM EMPLOYEE';

BEGIN

  IF p_dept_id IS NOT NULL THEN

    stmt := stmt || ' WHERE dept_id='||p_dept_id;

  END IF;

  OPEN p_emp_cur FOR stmt;

  LOOP 

    FETCH p_emp_cur INTO v_emp_rec;

    //do something with v_emp_rec

    EXIT WHEN v_ref%NOTFOUND;

  END LOOP;

  CLOSE p_emp_cur;

END get_emp_by_dept_id_proc;

END EMP_PKG;

/

===========================================

Using REF Cursor as argument in a procedure Example
===========================================

A. Procedure definition
CREATE OR REPLACE PROCEDURE get_emp_by_dept_id_proc 

             (p_dept_id IN employees.dept_id%TYPE,

              p_emp_cur OUT GEN_PKG.emp_refcur) IS

  stmt VARCHAR2(2000) := 'SELECT * FROM EMPLOYEE';

BEGIN

  IF p_dept_id IS NOT NULL THEN

    stmt := stmt_str || ' WHERE dept_id='||p_dept_id;

  END IF;

  OPEN p_emp_cur FOR stmt;

END;

B. Calling the Procedure

DECLARE

  v_ref GEN_PKG.emp_refcur_type;

  v_emp_rec employee%ROWTYPE;

BEGIN

  get_emp_by_dept_id_proc(11,v_ref);

  LOOP

    FETCH v_ref INTO v_emp_rec;

    EXIT WHEN v_ref%NOTFOUND;

    //do something with v_emp_rec

  END LOOP;

  CLOSE v_ref;

END;

/

===========================================
Using Ref Cursor in PL/SQL Example 
===========================================
PL/SQL code is having a cursor with REF Cursor.

CREATE OR REPLACE PROCEDURE emp_report_proc IS

  TYPE t_refcursor IS REF CURSOR;

  v_empcur t_refcursor;
  
  CURSOR p_dept_cur IS
         SELECT department_name,
         CURSOR
              (SELECT EMPLOYEES.last_name
                FROM EMPLOYEES
                WHERE EMPLOYEES.dept_id = DEPARTMENTS.department_id
              )
       )EMPLOYEES_LIST
       FROM DEPARTMENTS;

  v_dept_name DEPARTMENTS.department_name%TYPE;
  v_emp_last_name EMPLOYEES.last_name%TYPE;

BEGIN
  OPEN p_dept_cur 

  LOOP
    FETCH p_dept_cur INTO v_dept_name, v_empcur;
    EXIT WHEN p_dept_cur%NOTFOUND;
      LOOP
        FETCH v_empcur INTO v_emp_last_name;
        EXIT WHEN v_empcur%NOTFOUND; 
        //do something with v_empcur data
        --No OPEN or CLOSE on the v_empcur REF Cursor
      END LOOP;
  END LOOP;
  CLOSE p_dept_cur;
END;
/

===========================================
Using Ref Cursor from SQL Example 
===========================================
A function receives REF Cursor as a parameter, and concatenates its values.
Function:

CREATE OR REPLACE FUNCTION concatenate_list_func 

             (p_refcursor IN SYS_REFCURSOR) RETURN VARCHAR2 IS

  v_return VARCHAR2(32000)='';
  v_temp VARCHAR2(32000);

BEGIN
  LOOP
    FETCH p_refcursor INTO v_temp;
    EXIT WHEN p_refcursor%NOTFOUND;    v_return := v_return||','||v_temp;
  END LOOP;
  IF v_return IS NULL THEN
    v_return='Empty';
  ELSE
    v_return=LTRIM(v_return,',');
  END IF;

  RETURN v_return;
END;

Function Usage:

SELECT department_id,
       department_name,
       concatenate_list_func 
       (CURSOR
              (SELECT last_name
                FROM EMPLOYEES
                WHERE EMPLOYEES.dept_id = DEPARTMENTS.department_id
              )
       )EMPLOYEES_LIST
  FROM DEPARTMENTS
 WHERE DEPARTMENTS.department_id IN (10,30,50)
ORDER BY department_id;

The output should be something like:

DEPARTMENT_ID DEPARTMENT_NAME EMPLOYEES_LIST
------------- --------------- -----------------------
10            Purchasing      King,Lewis, Davis
30            Travel          Koon, Racewell, Edwards
50            Payroll         Empty

===========================================
Autonomous Transaction 
===========================================
Autonomous Transaction Example

PROCEDURE write_to_log (line IN VARCHAR2) IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  UPDATE...
  COMMIT;
END;


===========================================
EXECUTE IMMEDIATE Syntax
===========================================
Example:
sql_string VARCHAR2(1000);
v_result   TABLE_A.field_a%TYPE;
v_param_b  TABLE_A.field_b%TYPE;

sql_string := 'SELECT field_a ';
sql_string := sql_string||'  FROM TABLE_A';
sql_string := sql_string||' WHERE field_b=:b';

EXEC IMMEDIATE sql_string INTO v_result USING v_param_b;