If a PL/SQL program doesn't close a cursor, Oracle closes the cursor when the subprogram disconnects from the Oracle database, either by terminating or by performing a DISCONNECT. Closing a cursor is straightforward:

close get_instructors;

Listing 11.20 shows you how to supply a different set of parameter values to a cursor by closing the cursor, changing the parameter values, and reopening the cursor. It uses the patients_with_hypertension cursor to illustrate this process. First, the cursor is opened with age set to 50 and normal_dyastolic set to 80 (line 14). The rows are fetched in a loop (line 16), and the cursor is closed. Next, the cursor is reopened with age equal to 40 and normal_dyastolic set at 70 (line 22). The rows are fetched in a loop with different results.

Listing 11.20. Changing a cursor's arguments by closing and reopening the cursor.

SQL> declare
  2
  2  Patient_ID   Patient.Patient_ID%type;
  3  Age          Patient.Age%type;
  4  Dyastolic    Patient.Dyastolic%type;
  5
  5  cursor patients_with_hypertension
  6         (patient_age number,
  7          normal_dyastolic number) is
  8          select patient_id, age, dyastolic
  9          from patient
 10          where
 11          dyastolic > normal_dyastolic * (age+200)/200;
 12
 12  begin
 13
 13  dbms_output.enable;
 14
 14  open patients_with_hypertension (50, 80);
 15
 15  loop
 16
 16     fetch patients_with_hypertension
 17           into Patient_ID, Age, Dyastolic;
 18     exit when patients_with_hypertension%notfound;
 19
 19     dbms_output.put_line(`With age=50, dyas=80: ` || Patient_ID);
 20
 20  end loop;
 21
 21  close patients_with_hypertension;
 22
 22  open patients_with_hypertension (40, 70);
 23
 23  loop
 

                                                     continues

Listing 11.20. continued

 24
 24     fetch patients_with_hypertension
 25           into Patient_ID, Age, Dyastolic;
 26     exit when patients_with_hypertension%notfound;
 27
 27     dbms_output.put_line(`With age=40, dyas=70: ` || Patient_ID);
 28
 28  end loop;
 29
 29  close patients_with_hypertension;
 30
 30  end;
 31  /

With age=50, dyas=80: N3393
With age=40, dyas=70: A2002
With age=40, dyas=70: N3393
With age=40, dyas=70: E3893
PL/SQL procedure successfully completed.

Working with Cursor FOR Loops

As an alternative to opening, fetching, and closing a cursor, Oracle furnishes another approach—the cursor FOR loop. With the cursor FOR loop, Oracle implicitly declares a variable—the loop index—that is of the same record type as the cursor's record, as shown in Listing 11.21.

Listing 11.21. Using a cursor FOR loop.

SQL> declare
  2
  2  Instructor_ID   Instructor.Instructor_ID%type;
  3  Last_Name       Instructor.Last_Name%type;
  4  First_Name      Instructor.First_Name%type;
  5
  5  cursor Get_Associate_Profs is
  6    select Instructor_ID, Last_Name, First_Name
  7    from Instructor
  8    where Position = `ASSOCIATE PROFESSOR'
  9    order by Instructor_ID;
 10
 10  begin
 11
 11  dbms_output.enable;
 12
 12  for Get_Associate_Profs_Rec in Get_Associate_Profs loop
 13
 13   dbms_output.put_line(`Last name: ` ||
            ÂGet_Associate_Profs_Rec.Last_Name);

 14
 14  end loop;
 15
 15  end;
 16  /

Last name: NILAND
Last name: DANIELS
Last name: RESTON
Last name: JASON
Last name: ANGELO
Last name: CHERNOW
Last name: YOUNG

PL/SQL procedure successfully completed.

The name that follows FOR is the loop index that is implicitly declared. However, Listing 11.22 demonstrates that you can't reference the loop index—Get_Associate_Profs_Rec—outside of the loop statement.

Listing 11.22. Oracle returns an error when the cursor loop index is referenced outside of loop.

SQL> declare
  2
  2  Instructor_ID   Instructor.Instructor_ID%type;
  3  Last_Name       Instructor.Last_Name%type;
  4  First_Name      Instructor.First_Name%type;
  5
  5  cursor Get_Associate_Profs is
  6    select Instructor_ID, Last_Name, First_Name
  7    from Instructor
  8    where Position = `ASSOCIATE PROFESSOR'
  9    order by Instructor_ID;
 10
 10  begin
 11
 11  dbms_output.enable;
 12
 12  for Get_Associate_Profs_Rec in Get_Associate_Profs loop
 13
 13    dbms_output.put_line(`Last name: ` ||
               ÂGet_Associate_Profs_Rec.Last_Name);
 14
 14  end loop;
 15
 15  Last_Name := Get_Associate_Profs_Rec.Last_Name;
 16
 16  end;
 17  /
 

                                                     continues

Listing 11.22. continued

declare
 *
ERROR at line 1:
ORA-06550: line 15, column 14:
PLS-00201: identifier `GET_ASSOCIATE_PROFS_REC.LAST_NAME' must be declared
ORA-06550: line 15, column 1:
PL/SQL: Statement ignored

Was It %FOUND or %NOTFOUND

The previous examples used the %NOTFOUND attribute to determine whether a FETCH statement retrieved a row. When all of the rows in the active set have been fetched and the last FETCH statement fails to retrieve a row, %NOTFOUND evaluates to TRUE.

Getting the Number of Rows with %ROWCOUNT

You don't need a counter to keep track of the number of rows that are fetched from a cursor. Instead, reference the cursor's %ROWCOUNT attribute. As you can see in line 13 in Listing 11.23, %ROWCOUNT returns the running count of the rows that have been fetched.

Listing 11.23. Using %ROWCOUNT to determine the number of rows fetched from a cursor.

SQL> declare
  2
  2  Instructor_ID   Instructor.Instructor_ID%type;
  3  Last_Name       Instructor.Last_Name%type;
  4  First_Name      Instructor.First_Name%type;
  5
  5  cursor Get_Associate_Profs is
  6  select Instructor_ID, Last_Name, First_Name
  7  from Instructor

  8  where Position = `ASSOCIATE PROFESSOR'
  9  order by Instructor_ID;
 10
 10  begin
 11
 11  dbms_output.enable;
 12
 12  for Get_Associate_Profs_Rec in Get_Associate_Profs loop
 13
 13    dbms_output.put_line (`Rowcount: ` ||
               ÂGet_Associate_Profs%rowcount);
 14
 14  end loop;
 15
 15  end;
 16  /

Rowcount: 1
Rowcount: 2
Rowcount: 3
Rowcount: 4
Rowcount: 5
Rowcount: 6
Rowcount: 7

PL/SQL procedure successfully completed.

Instead of exiting a loop when there are no more rows in the cursor, Listing 11.24 demonstrates how you can specify an exit condition when a specified %ROWCOUNT is achieved (see line 13).

Listing 11.24. Exiting a cursor loop after a specified number of rows are fetched.

SQL> declare
  2
  2  Instructor_ID   Instructor.Instructor_ID%type;
  3  Last_Name       Instructor.Last_Name%type;
  4  First_Name      Instructor.First_Name%type;
  5
  5  cursor Get_Associate_Profs is
  6  select Instructor_ID, Last_Name, First_Name
  7  from Instructor
  8  where Position = `ASSOCIATE PROFESSOR'
  9  order by Instructor_ID;
 10
 10  begin
 11
 11  dbms_output.enable;
 12
 12  for Get_Associate_Profs_Rec in Get_Associate_Profs loop
 13
 

                                                     continues