Using Cursors To Retrieve Data

A cursor is a name for a private SQL area in which information for processing the specific statement is kept. PL/SQL uses both implicit and explicit cursors. PL/SQL implicitly creates a cursor for all SQL data manipulation statements on a set of rows, including queries that return only one row.

You can explicitly declare a cursor for one row or multiple rows. For queries that return multiple rows, you can process the rows individually.

Cursor attributes return useful information about the status of cursors in the execution of SQL statements. See "Cursor Attributes".

Example: Fetching a Single Row With a Cursor in PL/SQL is an example of explicit cursor used to process one row of a table.You should explicitly open and close a cursor before and after use.

Fetching a Single Row With a Cursor in PL/SQL

DECLARE
-- declare variables for first_name and last_name fetched from the employees table
  firstname  employees.first_name%TYPE;   -- variable for first_name
  lastname   employees.last_name%TYPE;   -- variable for last_name

-- declare a cursor to fetch data from a row (employee 120) in the employees table
  CURSOR cursor1 IS
    SELECT first_name, last_name FROM employees WHERE employee_id = 120;

BEGIN
  OPEN cursor1; -- open the cursor
  FETCH cursor1 INTO firstname, lastname; -- fetch data into local variables
  DBMS_OUTPUT.PUT_LINE('Employee name: ' || firstname || ' ' || lastname);
  CLOSE cursor1; -- close the cursor
END;
/

Example: Fetching Multiple Rows With a Cursor in PL/SQL shows examples of the use of a cursor to process multiple rows in a table. The FETCH statement retrieves the rows in the result set one at a time. Each fetch retrieves the current row and advances the cursor to the next row in the result set. Note the use of the cursor attributes %ROWCOUNT and %NOTFOUND. For information on cursor attributes, see "Cursor Attributes".

Fetching Multiple Rows With a Cursor in PL/SQL

DECLARE  
-- declare variables for data fetched from cursors
  empid      employees.employee_id%TYPE; -- variable for employee_id
  jobid      employees.job_id%TYPE;      -- variable for job_id
  lastname   employees.last_name%TYPE;   -- variable for last_name
  rowcount   NUMBER;
-- declare the cursors
  CURSOR cursor1 IS SELECT last_name, job_id FROM employees 
                 WHERE job_id LIKE '%CLERK';
  CURSOR cursor2 is SELECT employee_id, last_name, job_id FROM employees 
                 WHERE job_id LIKE '%MAN' OR job_id LIKE '%MGR';
BEGIN
-- start the processing with cursor1
  OPEN cursor1; -- open cursor1 before fetching
  DBMS_OUTPUT.PUT_LINE( '---------- cursor 1-----------------' );
  LOOP
    FETCH cursor1 INTO lastname, jobid; -- fetches 2 columns into variables
-- check the cursor attribute NOTFOUND for the end of data
    EXIT WHEN cursor1%NOTFOUND;
-- display the last name and job ID for each record (row) fetched
    DBMS_OUTPUT.PUT_LINE( RPAD(lastname, 25, ' ') || jobid );
  END LOOP;
  rowcount := cursor1%ROWCOUNT;
  DBMS_OUTPUT.PUT_LINE('The number of rows fetched is ' || rowcount );
  CLOSE cursor1;

-- start the processing with cursor2
  OPEN cursor2;
  DBMS_OUTPUT.PUT_LINE( '---------- cursor 2-----------------' );
  LOOP
-- fetch 3 columns into the variables
    FETCH cursor2 INTO empid, lastname, jobid; 
    EXIT WHEN cursor2%NOTFOUND;
-- display the employee ID, last name, and job ID for each record (row) fetched
    DBMS_OUTPUT.PUT_LINE( empid || ': ' || RPAD(lastname, 25, ' ') || jobid );
  END LOOP;
  rowcount := cursor2%ROWCOUNT;
  DBMS_OUTPUT.PUT_LINE('The number of rows fetched is ' || rowcount );
  CLOSE cursor2;
END;
/

In Example: Fetching Multiple Rows With a Cursor in PL/SQL, the LIKE condition operator is used to specify the records to return with the query. For information about LIKE, see "Restricting Data Using the WHERE Clause".

Example: Passing Parameters to a Cursor in PL/SQL shows how to pass a parameter to an explicit cursor. In the example, the current month value is passed to the cursor to specify that only those employees hired during this month are displayed. This provides a list of employees that have their yearly anniversary dates and their bonus amount.

Passing Parameters to a Cursor in PL/SQL

DECLARE 
-- declare variables for data fetched from cursor
  empid       employees.employee_id%TYPE; -- variable for employee_id
  hiredate    employees.hire_date%TYPE;   -- variable for hire_date
  firstname   employees.first_name%TYPE;  -- variable for first_name
  lastname    employees.last_name%TYPE;   -- variable for last_name
  rowcount    NUMBER;
  bonusamount NUMBER;
  yearsworked NUMBER;
-- declare the cursor with a parameter,
  CURSOR cursor1 (thismonth NUMBER)IS 
    SELECT employee_id, first_name, last_name, hire_date FROM employees 
       WHERE EXTRACT(MONTH FROM hire_date) = thismonth;
BEGIN
-- open and pass a parameter to cursor1, select employees hired on this month
  OPEN cursor1(EXTRACT(MONTH FROM SYSDATE));
  DBMS_OUTPUT.PUT_LINE('----- Today is ' || TO_CHAR(SYSDATE, 'DL') || ' -----');
  DBMS_OUTPUT.PUT_LINE('Employees with yearly bonus amounts:');
  LOOP
-- fetches 4 columns into variables
    FETCH cursor1 INTO empid, firstname, lastname, hiredate; 
-- check the cursor attribute NOTFOUND for the end of data
    EXIT WHEN cursor1%NOTFOUND;
-- calculate the yearly bonus amount based on months (years) worked
  yearsworked := ROUND( (MONTHS_BETWEEN(SYSDATE, hiredate)/12) );
  IF yearsworked > 10   THEN bonusamount := 2000;
  ELSIF yearsworked > 8 THEN bonusamount := 1600;
  ELSIF yearsworked > 6 THEN bonusamount := 1200;
  ELSIF yearsworked > 4 THEN bonusamount := 800;
  ELSIF yearsworked > 2 THEN bonusamount := 400;
  ELSIF yearsworked > 0 THEN bonusamount := 100;
  END IF;
-- display the employee Id, first name, last name, hire date, and bonus 
-- for each record (row) fetched
    DBMS_OUTPUT.PUT_LINE( empid || ' ' || RPAD(firstname, 21, ' ') ||
      RPAD(lastname, 26, ' ') || hiredate || TO_CHAR(bonusamount, '$9,999'));
  END LOOP;
  rowcount := cursor1%ROWCOUNT;
  DBMS_OUTPUT.PUT_LINE('The number of rows fetched is ' || rowcount );
  CLOSE cursor1;
END;
/

Cursor Attributes

Cursor attributes return information about the execution of DML and DDL statements, such INSERT, UPDATE, DELETE, SELECT INTO, COMMIT, or ROLLBACK statements. The cursor attributes are %FOUND, %ISOPEN, %NOTFOUND, and %ROWCOUNT. These attributes return useful information about the most recently executed SQL statement. When using an explicit cursor, add the explicit cursor or cursor variable name to the beginning of the attribute, such as cursor1%FOUND, to return information for the most recently executed SQL statement for that cursor.

The attributes provide the following information:

%FOUND Attribute: Has a Row Been Fetched?

After a cursor or cursor variable is opened but before the first fetch, %FOUND returns NULL. After any fetches, it returns TRUE if the last fetch returned a row, or FALSE if the last fetch did not return a row.
%ISOPEN Attribute: Is the Cursor Open?

If a cursor or cursor variable is open, then %ISOPEN returns TRUE ; otherwise, %ISOPEN returns FALSE.

Note that implicit cursors are automatically opened before and closed after executing the associated SQL statement so %ISOPEN always returns FALSE.
%NOTFOUND Attribute: Has a Fetch Failed?

If the last fetch returned a row, then %NOTFOUND returns FALSE. If the last fetch failed to return a row, then %NOTFOUND returns TRUE. %NOTFOUND is the logical opposite of %FOUND.
%ROWCOUNT Attribute: How Many Rows Fetched So Far?

After a cursor or cursor variable is opened, %ROWCOUNT returns 0 before the first fetch. Thereafter, it returns the number of rows fetched so far. The number is incremented if the last fetch returned a row.