Cursors in PL/SQL

A cursor is a pointer to the private memory area allocated by Oracle server. Oracle server uses work areas to execute SQL statements and to store processing information. You can use explicit cursors to name a private SQL area and to access its stored information. Oracle server implicitly opens a cursor to process each SQL statement that is not associated with an explicitly declared cursor.

Two Types: 1) Implicit - created and managed by Oracle server

2) Explicit - Explicitly by programmer.

Explicit cursors

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

1) Declare cursor

Syntax: CURSOR cursor_name IS select_statement;

Example: CURSOR emp_details IS select emp_id, emp_name from employee;

2) Open the cursor

Open statement executes the query associated with the cursor and positions the cursor pointer at first row.

Syntax: Open cursor_name;

Example: Open emp_details;

3) Fetch data

Fetch statement retrieves the rows from the cursor one at a time. After each fetch cursor advances to next row. Usually we will have fetch statement inside a loop.

Syntax: Fetch cursor_name into var1,var2;

Note : var1,var2 are the variables declared in Declare section

Example: Fetch emp_details into eno, ename;

4) Close the cursor

Syntax: Close emp_details;

If you attempt to fetch data from cursor after closing it, then an INVALID_CURSOR Exception will be raised.

DECLARE    

  CURSOR emp_details IS SELECT emp_num, emp_name FROM employee; 

  eno employee.emp_id%TYPE

  ename employee.emp_name%TYPE 

  BEGIN 

  OPEN emp_details; 

  LOOP

     FETCH emp_details into eno,ename;

     EXIT when emp_details%NOTFOUND

  END LOOP 

  CLOSE emp_details; 

END;

ename employee.emp_name%TYPE - Declaring a variable ename with same datatype as column emp_name in employee table.

Explicit Cursor Attributes

cursor%ROWCOUNT - integer - number of rows fetched so far

cursor%FOUND - Boolean - TRUE if >1 row returned

cursor%NOTFOUND – Boolean - TRUE if 0 rows returned

cursor%ISOPEN - Boolean - TRUE if cursor still open

Cursor with a for loop

When using FOR LOOP you need not declare a record or variables to store the cursor values, need not open, fetch and close the cursor. These functions are accomplished by the FOR LOOP automatically.

General Syntax for using FOR LOOP:

FOR record_name IN cusror_name

LOOP

process the row...

END LOOP;

Example:

DECLARE

CURSOR emp_details IS SELECT emp_num,emp_name FROM employee;

emp_rec emp_cur%rowtype;

BEGIN

FOR emp_rec in emp_details

LOOP

dbms_output.put_line(emp_rec.emp_num || ' ' ||emp_rec.emp_name);

END LOOP;

END;

CURSOR with parameters

We can pass parameters for cursors.

DECLARE

CURSOR emp_details (deptno NUMBER) IS

SELECT emp_num, emp_name from employee

Where dept_id = deptno;

BEGIN

OPEN emp_details (10);

CLOSE emp_details;

END