Rather than executing a whole query at once, it is possible to set up a cursor that encapsulates the query, and then read the query result set one row at a time. This allows the creation of EDB-SPL program logic that retrieves a row from the result set, does some processing on the data in that row, and then retrieves the next row and repeats the process.
Cursors are most often used in the context of a FOR or WHILE loop. A conditional test should be included in the EDB-SPL logic that detects when the end of the result set has been reached so the program can exit the loop.
In order to use a cursor, it must first be declared in the declaration section of the EDB-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;
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 EDB-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;
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 EDB-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 EDB-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;
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
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.
The %ISOPEN attribute is used to test whether or not a cursor is open.
cursor_name%ISOPEN
cursor_name is the name of the cursor for which a boolean data type of "true" will be returned if the cursor is open, "false" otherwise.
The following is an example of using %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;
The %FOUND attribute is used to test whether or not a row is retrieved from the result set of the specfied cursor after a FETCH on the cursor.
cursor_name%FOUND
cursor_name is the name of the cursor for which a boolean data type of "true" will be returned if a row is retrieved from the result set of the cursor after a FETCH.
After the last row of the result set has been FETCHed the next FETCH results in %FOUND returning "false". "false" is also returned after the first FETCH if there are no rows in the result set to begin with.
Referencing %FOUND on a cursor before it is opened or after it is closed, results in an INVALID_CURSOR exception being thrown.
%FOUND returns null if it is referenced when the cursor is open, but before the first FETCH.
The following example uses %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;
When the previous procedure is invoked, the output appears as follows:
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
The %NOTFOUND attribute is the logical opposite of %FOUND.
cursor_name%NOTFOUND
cursor_name is the name of the cursor for which a boolean data type of "false" will be returned if a row is retrieved from the result set of the cursor after a FETCH.
After the last row of the result set has been FETCHed the next FETCH results in %NOTFOUND returning "true". "true" is also returned after the first FETCH if there are no rows in the result set to begin with.
Referencing %NOTFOUND on a cursor before it is opened or after it is closed, results in an INVALID_CURSOR exception being thrown.
%NOTFOUND returns null if it is referenced when the cursor is open, but before the first FETCH.
The following example uses %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;
Similar to the prior example, this procedure produces the same output when invoked.
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
The %ROWCOUNT attribute returns an integer showing the number of rows FETCHed so far from the specified cursor.
cursor_name%ROWCOUNT
cursor_name is the name of the cursor for which %ROWCOUNT returns the number of rows retrieved thus far. After the last row has been retrieved, %ROWCOUNT remains set to the total number of rows returned until the cursor is closed at which point %ROWCOUNT will throw an INVALID_CURSOR exception if referenced.
Referencing %ROWCOUNT on a cursor before it is opened or after it is closed, results in an INVALID_CURSOR exception being thrown.
%ROWCOUNT returns 0 if it is referenced when the cursor is open, but before the first FETCH. %ROWCOUNT also returns 0 after the first FETCH when there are no rows in the result set to begin with.
The following example uses %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;
This procedure prints the total number of rows retrieved at the end of the employee list as follows:
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
The following table summarizes the possible cursor states and the values returned by the cursor attributes.
Table 13-2. Cursor Attributes
| Cursor State | %ISOPEN | %FOUND | %NOTFOUND | %ROWCOUNT |
|---|---|---|---|---|
| Before OPEN | False | INVALID_CURSOR Exception | INVALID_CURSOR Exception | INVALID_CURSOR Exception |
| After OPEN & Before 1st FETCH | True | Null | Null | 0 |
| After 1st Successful FETCH | True | True | False | 1 |
| After nth Successful FETCH (last row) | True | True | False | n |
| After n+1st FETCH (after last row) | True | False | True | n |
| After CLOSE | False | INVALID_CURSOR Exception | INVALID_CURSOR Exception | INVALID_CURSOR Exception |