About Cursors
Every SQL statement executed by the Oracle Server has an individual cursor associated with it:
• Implicit cursors: Declared for all DML and PL/SQL SELECTstatements
• Explicit cursors: Declared and named by the programmer
Declaring the Cursor
Syntax:
CURSOR cursor_name IS
select_statement;
• Do not include the INTOclause in the cursor declaration.
• If processing rows in a specific sequence is required, use the ORDER BYclause in the query.
Example:
DECLARE
CURSOR dept_cursor IS
SELECT *
FROM departments
WHERE location_id = 170;
BEGIN
Opening the Cursor
Syntax:
OPEN cursor_name;
• Open the cursor to execute the query and identify the active set.
• If the query returns no rows, no exception is raised.
• Use cursor attributes to test the outcome after a fetch.
Fetching Data from the Cursor
Syntax:
FETCHcursor_name INTO [variable1, variable2, ...]
| record_name];
• Retrieve the current row values into variables.
• Include the same number of variables.
• Match each variable to correspond to the columns positionally.
• Test to see whether the cursor contains rows.
Closing the Cursor
Syntax:
CLOSE cursor_name;
• Close the cursor after completing the processing of the rows.
• Reopen the cursor, if required.
• Do not attempt to fetch data from a cursor after it has been closed.Syntax:
Explicit Cursor Attributes
Obtain status information about a cursor.
Attribute =Type = Description
%ISOPEN = Boolean = Evaluates to TRUE if the cursor is open
%NOTFOUND = Boolean = Evaluates to TRUE if the most recent fetch does not return a row
%FOUND = Boolean = Evaluates to TRUE if the most recent fetch returns a row; complement of %NOTFOUND
%ROWCOUNT = Number = Evaluates to the total number of rows returned so far
Example
DECLARE
v_empno employees.employee_id%TYPE;
v_ename employees.last_name%TYPE;
CURSOR emp_cursor IS
SELECT employee_id, last_name
FROM employees;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO v_empno, v_ename;
EXIT WHEN emp_cursor%ROWCOUNT > 10 OR emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE (TO_CHAR(v_empno) ||’ ‘|| v_ename);
END LOOP;
CLOSE emp_cursor;
END ;
/
Cursor FORLoops
Print a list of the employees who work for the sales department.
DECLARE
CURSOR emp_cursor IS
SELECT last_name, department_id
FROM employees;
BEGIN
FOR emp_record IN emp_cursor LOOP –implicit open and implicit fetch occur
IF emp_record.department_id = 80 THEN
…
END LOOP; –implicit close occurs
END;
/
