14.7. Static Cursors

14.7.1. Declaring a Cursor

In order to use a cursor, it must first be declared in the declaration section of the SPL program. A cursor declaration appears as follows:

CURSOR name IS query;

name is an identifier that will be used to reference the cursor and its result set later in the program. query is a SQL SELECT command that determines the result set retrievable by the cursor.

The following are some examples of cursor declarations:

CREATE OR REPLACE PROCEDURE cursor_example
IS
    CURSOR emp_cur_1 IS SELECT * FROM emp;
    CURSOR emp_cur_2 IS SELECT empno, ename FROM emp;
    CURSOR emp_cur_3 IS SELECT empno, ename FROM emp WHERE deptno = 10 ORDER BY empno;
BEGIN
    ...
END;

14.7.2. Opening a Cursor

Before a cursor can be used to retrieve rows, it must first be opened. This is accomplished with the OPEN statement.

OPEN name;

name is the identifier of a cursor that has been previously declared in the declaration section of the SPL program. The OPEN statement must not be executed on a cursor that has already been, and still is open.

The following shows an OPEN statement with its corresponding cursor declaration.

CREATE OR REPLACE PROCEDURE cursor_example
IS
    CURSOR emp_cur_3 IS SELECT empno, ename FROM emp WHERE deptno = 10 ORDER BY empno;
BEGIN
    OPEN emp_cur_3;
        ...
END;

14.7.3. Fetching Rows From a Cursor

Once a cursor has been opened, rows can be retrieved from the cursor's result set by using the FETCH statement.

FETCH name INTO {record | 
variable [, variable_2 ]...};

name is the identifier of a previously opened cursor. record is the identifier of a previously defined record (for example, using table%ROWTYPE). variable, variable_2... are SPL variables that will receive the field data from the fetched row. The fields in record or variable, variable_2... must match in number and order, the fields returned in the SELECT list of the query given in the cursor declaration. The data types of the fields in the SELECT list must match, or be implicitly convertible to the data types of the fields in record or the data types of variable, variable_2...

The following shows the FETCH statement.

CREATE OR REPLACE PROCEDURE cursor_example
IS
    v_empno         NUMBER(4);
    v_ename         VARCHAR2(10);
    CURSOR emp_cur_3 IS SELECT empno, ename FROM emp WHERE deptno = 10 ORDER BY empno;
BEGIN
    OPEN emp_cur_3;
    FETCH emp_cur_3 INTO v_empno, v_ename;
        ...
END;

Instead of explicitly declaring the data type of a target variable, %TYPE can be used instead. In this way, if the data type of the database column is changed, the target variable declaration in the SPL program does not have to be changed. %TYPE will automatically pick up the new data type of the specified column.

CREATE OR REPLACE PROCEDURE cursor_example
IS
    v_empno         emp.empno%TYPE;
    v_ename         emp.ename%TYPE;
    CURSOR emp_cur_3 IS SELECT empno, ename FROM emp WHERE deptno = 10 ORDER BY empno;
BEGIN
    OPEN emp_cur_3;
    FETCH emp_cur_3 INTO v_empno, v_ename;
        ...
END;

If all the columns in a table are retrieved in the order defined in the table, %ROWTYPE can be used to define a record into which the FETCH statement will place the retrieved data. Each field within the record can then be accessed using dot notation.

CREATE OR REPLACE PROCEDURE cursor_example
IS
    v_emp_rec       emp%ROWTYPE;
    CURSOR emp_cur_1 IS SELECT * FROM emp;
BEGIN
    OPEN emp_cur_1;
    FETCH emp_cur_1 INTO v_emp_rec;
    DBMS_OUTPUT.PUT_LINE('Employee Number: ' || v_emp_rec.empno);
    DBMS_OUTPUT.PUT_LINE('Employee Name  : ' || v_emp_rec.ename);
        ...
END;

14.7.4. Cursor FOR Loop

Cursor FOR loops are used to fetch and process every record in a cursor with minimal code.

The cursor FOR loop automatically opens the cursor, fetches all rows identified by the cursor, and then closes the cursor. You can embed SQL statements directly in the cursor FOR loop.

FOR record_index IN name_of_cursor
LOOP
   statements...
END LOOP;

CREATE OR REPLACE FUNCTION total_sal_by_dept(p_DeptNo IN NUMBER) 
RETURN NUMBER AS v_total NUMBER;

CURSOR emp_check IS

SELECT	e.sal
FROM	emp e, dept d
WHERE	e.deptno=d.deptno
AND	d.deptno=p_DeptNo;
  
   BEGIN
      v_total :=0;
      FOR i IN emp_check
      LOOP
         v_total := v_total+i.sal;
      END LOOP;
   RETURN v_total;
END;

SELECT total_sal_by_dept(10);
 
 total_sal_by_dept
-------------------
           8750.00
(1 row)

14.7.5. Closing a Cursor

Once all the desired rows have been retrieved from the cursor result set, the cursor must be closed. Once closed, the result set is no longer accessible. The CLOSE statement appears as follows:

CLOSE name;

name is the identifier of a cursor that is currently open. Once a cursor is closed, it must not be closed again. However, once the cursor is closed, the OPEN statement can be issued again on the closed cursor and the query result set will be rebuilt after which the FETCH statement can then be used to retrieve the rows of the new result set.

The following example illustrates the use of the CLOSE statement:

CREATE OR REPLACE PROCEDURE cursor_example
IS
    v_emp_rec       emp%ROWTYPE;
    CURSOR emp_cur_1 IS SELECT * FROM emp;
BEGIN
    OPEN emp_cur_1;
    FETCH emp_cur_1 INTO v_emp_rec;
    DBMS_OUTPUT.PUT_LINE('Employee Number: ' || v_emp_rec.empno);
    DBMS_OUTPUT.PUT_LINE('Employee Name  : ' || v_emp_rec.ename);
    CLOSE emp_cur_1;
END;

This procedure produces the following output when invoked. Employee number 7369, SMITH is the first row of the result set.

EXEC cursor_example;

Employee Number: 7369
Employee Name  : SMITH

14.7.6. Using %ROWTYPE With Cursors

Using the %ROWTYPE attribute, a record can be defined that contains fields corresponding to all columns fetched from a cursor or cursor variable. Each field takes on the data type of its corresponding column. The %ROWTYPE attribute is prefixed by a cursor name or cursor variable name.

	record cursor%ROWTYPE;
	

record is an identifier assigned to the record. cursor is an explicitly declared cursor within the current scope

The following example shows how you can use a cursor with %ROWTYPE to get information about which employee works in which department.

CREATE OR REPLACE PROCEDURE emp_info IS
   CURSOR empcur IS SELECT ename,deptno FROM emp;
   myvar empcur%ROWTYPE;

BEGIN
   OPEN empcur;
   LOOP
      FETCH empcur INTO myvar;
      EXIT WHEN empcur%NOTFOUND;

      DBMS_OUTPUT.PUT_LINE( myvar.ename || ' works in department ' || myvar.deptno );

   END LOOP;
   close empcur;
END;    
     

To see this example in work, please type the following:

     EXEC emp_info;
     

14.7.7. Cursor Attributes

Each cursor has a set of attributes associated with it that allows the program to test the state of the cursor. These attributes are %ISOPEN, %FOUND, %NOTFOUND, and %ROWCOUNT. These attributes are described in the following sections.

cursor_name%ISOPEN

CREATE OR REPLACE PROCEDURE cursor_example
IS
        ...
    CURSOR emp_cur_1 IS SELECT * FROM emp;
        ...
BEGIN
        ...
    IF emp_cur_1%ISOPEN THEN
        NULL;
    ELSE
        OPEN emp_cur_1;
    END IF;
    FETCH emp_cur_1 INTO ...
        ...
END;

cursor_name%FOUND

CREATE OR REPLACE PROCEDURE cursor_example
IS
    v_emp_rec       emp%ROWTYPE;
    CURSOR emp_cur_1 IS SELECT * FROM emp;
BEGIN
    OPEN emp_cur_1;
    DBMS_OUTPUT.PUT_LINE('EMPNO    ENAME');
    DBMS_OUTPUT.PUT_LINE('-----    -------');
    FETCH emp_cur_1 INTO v_emp_rec;
    WHILE emp_cur_1%FOUND LOOP
        DBMS_OUTPUT.PUT_LINE(v_emp_rec.empno || '     ' || v_emp_rec.ename);
        FETCH emp_cur_1 INTO v_emp_rec;
    END LOOP;
    CLOSE emp_cur_1;
END;

EXEC cursor_example;

EMPNO    ENAME
-----    ------
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

cursor_name%NOTFOUND

CREATE OR REPLACE PROCEDURE cursor_example
IS
    v_emp_rec       emp%ROWTYPE;
    CURSOR emp_cur_1 IS SELECT * FROM emp;
BEGIN
    OPEN emp_cur_1;
    DBMS_OUTPUT.PUT_LINE('EMPNO    ENAME');
    DBMS_OUTPUT.PUT_LINE('-----    -------');
    LOOP
        FETCH emp_cur_1 INTO v_emp_rec;
        EXIT WHEN emp_cur_1%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_emp_rec.empno || '     ' || v_emp_rec.ename);
    END LOOP;
    CLOSE emp_cur_1;
END;

EXEC cursor_example;

EMPNO    ENAME
-----    ------
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

cursor_name%ROWCOUNT

CREATE OR REPLACE PROCEDURE cursor_example
IS
    v_emp_rec       emp%ROWTYPE;
    CURSOR emp_cur_1 IS SELECT * FROM emp;
BEGIN
    OPEN emp_cur_1;
    DBMS_OUTPUT.PUT_LINE('EMPNO    ENAME');
    DBMS_OUTPUT.PUT_LINE('-----    -------');
    LOOP
        FETCH emp_cur_1 INTO v_emp_rec;
        EXIT WHEN emp_cur_1%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_emp_rec.empno || '     ' || v_emp_rec.ename);
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('**********************');
    DBMS_OUTPUT.PUT_LINE(emp_cur_1%ROWCOUNT || ' rows were retrieved');
    CLOSE emp_cur_1;
END;

EXEC cursor_example;

EMPNO    ENAME
-----    -------
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
**********************
14 rows were retrieved

14.7.8. Parameterized Cursors

A user can also declare a static cursor that accepts parameters, and can pass values for those parameters when opening that cursor. In the following example we have created a parameterized cursor which will display the name and salary of all employees from the emp table that have a salary less then a specified value which is passed as a parameter.

    DECLARE
       my_record emp%ROWTYPE;
       CURSOR c1 (max_wage NUMBER) IS
    SELECT * FROM emp WHERE sal < max_wage;
       BEGIN
          OPEN c1(2000);
          LOOP
             FETCH c1 INTO my_record;
             EXIT WHEN c1%NOTFOUND;
             DBMS_OUTPUT.PUT_LINE('Name = ' || my_record.ename || ', salary = ' || my_record.sal);
          END LOOP;
        END;
    

So for example if we pass the value 2000 as max_wage, then we will only be shown the name and salary of all employees that have a salary less then 2000. The result of the above query is following:

    INFO:  Name = SMITH,  salary = 800.00
    INFO:  Name = ALLEN,  salary = 1600.00
    INFO:  Name = WARD,   salary = 1250.00
    INFO:  Name = MARTIN, salary = 1250.00
    INFO:  Name = TURNER, salary = 1500.00
    INFO:  Name = ADAMS,  salary = 1100.00
    INFO:  Name = JAMES,  salary = 950.00
    INFO:  Name = MILLER, salary = 1300.00