Oracle Cursor

For every SQL statement execution certain area in memory is allocated. PL/SQL allow you to name this area. This private SQL area is called context area or cursor. A cursor acts as a handle or pointer into the context area. A PL/SQL program controls the context area using the cursor. Cursor represents a structure in memory and is different from cursor variable.

When you declare a cursor, you get a pointer variable, which does not point any thing. When the cursor is opened, memory is allocated and the cursor structure is created. The cursor variable now points the cursor. When the cursor is closed the memory allocated for the cursor is released.

Cursors allow the programmer to retrieve data from a table and perform actions on that data one row at a time.

There are two types of cursors implicit cursors and explicit cursors.

Implicit cursors:

For SQL queries returning single row PL/SQL declares implicit cursors. Implicit cursors are simple SELECT statements and are written in the BEGIN block (executable section) of the PL/SQL. Implicit cursors are easy to code, and they retrieve exactly one row. PL/SQL implicitly declares cursors for all DML statements. The most commonly raised exceptions here are NO_DATA_FOUND or TOO_MANY_ROWS.

Syntax:

SELECT ename, sal INTO ena, esa FROM EMP WHERE EMPNO = 7844;

Note: Ename and sal are columns of the table EMP and ena and esa are the variables used to store ename and sal fetched by the query.

Explicit Cursors:

Explicit cursors are used in queries that return multiple rows. The set of rows fetched by a query is called active set. The size of the active set meets the search criteria in the select statement. Explicit cursor is declared in the DECLARE section of PL/SQL program.

Syntax:

CURSOR cursor_name IS select statement

Sample Code:

DECLARE 
CURSOR emp_cur IS SELECT ename FROM EMP;
BEGIN
     ------- 
     -------
END;

A cursor is a mechanism by which you can assign a name to a "select statement" and manipulate the information within that SQL statement.

You can manipulate cursors using the OPEN, FETCH, and CLOSE statements.

When you need to get information about the current status of your cursor, or the result of the last fetch from a cursor, you will access cursor attributes.

PL/SQL offers a total of six attributes:

1. %FOUND- Returns TRUE if record was fetched successfully; FALSE otherwise.
2. %NOTFOUND- Returns TRUE is record was not fetched successfully; FALSE otherwise.
   
3. %ROWCOUNT - Returns number of records fetched from cursor at that point in time.
4. %ISOPEN- Returns TRUE is cursor is open; FALSE otherwise.
5. %BULK_ROWCOUNT - Returns the number of records modified by the FORALL statement for each collection element.
6. %BULK_EXCEPTIONS- Returns exception information for rows modified by the FORALL statement for each collection element.

Examples

1. An explicit cursor that selects data.

SQL> declare
  2    cursor emps
  3    is select *
  4         from employees
  5        where rownum < 6
  6        order by 1;
  7
  8    emp employees%rowtype;
  9    row number := 1;
 10  begin
 11    open emps;
 12    fetch emps into emp;
 13
 14    loop
 15      if emps%FOUND then
 16        dbms_output.put_line('Looping over record '
 17                   ||row|| ' of ' || emps%ROWCOUNT);
 18        fetch emps into emp;
 19        row := row + 1;
 20      elsif emps%NOTFOUND then
 21        exit; -- EXIT statement exits the LOOP
 22      end if;
 23    end loop;
 24
 25    if emps%ISOPEN then
 26      close emps;
 27    end if;
 28  end;
 29  /
Looping over record 1 of 1
Looping over record 2 of 2
Looping over record 3 of 3
Looping over record 4 of 4
Looping over record 5 of 5

PL/SQL procedure successfully completed.

2. An explicit cursor fetch loop.

SQL> DECLARE
  2    myLecturerID    lecturer.id%TYPE;
  3    firstName    lecturer.first_name%TYPE;
  4    v_LastName     lecturer.last_name%TYPE;
  5
  6    v_Major lecturer.major%TYPE := 'Computer Science';
  7
  8    CURSOR cursorValue IS
  9      SELECT id, first_name, last_name
 10        FROM lecturer
 11        WHERE major = v_Major;
 12  BEGIN
 13    OPEN cursorValue;
 14    LOOP
 15      FETCH cursorValue INTO myLecturerID, firstName
 16    , v_LastName;
 17      EXIT WHEN cursorValue%NOTFOUND;
 18    END LOOP;
 19    CLOSE cursorValue;
 20  END;
 21  /

PL/SQL procedure successfully completed.

3. Use cursor to store the row count.

SQL> declare
  2       l_emp_count number;
  3       i number; -- We will use this as our counter
  4
  5       CURSOR cursorValue IS
  6       select count(*)
  7       from lecturer;
  8       begin
  9       OPEN cursorValue;
 10       FETCH cursorValue INTO l_emp_count;
 11
 12       FOR i IN 1 .. l_emp_count LOOP
 13       dbms_output.put_line('Employee ' || i);
 14       END LOOP;
 15
 16       CLOSE cursorValue;
 17  end;
 18  /
Employee 1
Employee 2
Employee 3
Employee 4
Employee 5
Employee 6
Employee 7
Employee 8
Employee 9
Employee 10
Employee 11
Employee 12

PL/SQL procedure successfully completed.

4. Cursor with parameter.

SQL> declare
  2    cursor cursorValue (p_deptid in number)
  3    is select *
  4         from employees
  5        where department_id = p_deptid;
  6
  7    l_emp employees%rowtype;
  8  begin
  9    open cursorValue(30);
 10    loop
 11      fetch cursorValue into l_emp;
 12      exit when cursorValue%notfound;
 13      dbms_output.put('Emp id ' || l_emp.employee_id || ' is ');
 14      dbms_output.put_line(l_emp.last_name);
 15    end loop;
 16    close cursorValue;
 17
 18    dbms_output.put_line('Getting employees for department 90');
 19    open cursorValue(90);
 20    loop
 21      fetch cursorValue into l_emp;
 22      exit when cursorValue%notfound;
 23      dbms_output.put('Emp id ' || l_emp.employee_id || ' is ');
 24      dbms_output.put_line(l_emp.last_name);
 25    end loop;
 26    close cursorValue;
 27  end;
 28  /
Getting employees for department 90

PL/SQL procedure successfully completed.