default1 is an optional default value for parameter1.

parameterN is the name of the last parameter to be supplied to the cursor.

parameterN-datatype is the datatype for parameterN.

defaultN is an optional default value for parameterN.

select-stmt is a valid SELECT statement that will be associated with the declared cursor.

Listing 11.15 illustrates two different cursors—the first cursor has three parameters without default values and the second cursor has three parameters with specified default values.

Listing 11.15. Cursors with and without default values for parameters.

cursor patients_with_hypertension
       (patient_age number,
        normal_dyastolic,
        normal_systolic) is
        select patient_id, age, dyastolic, systolic
        from patient
        where
        dyastolic > normal_dyastolic * (age+200)/200 and
        systolic > normal_systolic * (age+200)/200;

cursor patients_with_hypertension
         (patient_age number default 55,
          normal_dyastolic number default 70,
          normal_systolic  number default 130) is
          select patient_id, age, dyastolic, systolic
          from patient
          where
          dyastolic > normal_dyastolic * (age+200)/200 and
          systolic > normal_systolic * (age+200)/200;

Opening a Cursor

Before you can fetch rows from a cursor, you must open the cursor. When the cursor is opened, its SELECT statement is executed and Oracle constructs a list of the qualified rows.

These rows are referred to as the active set. If the cursor was declared without any parameters, the syntax is very simple:

open my_cursor;

If the cursor was declared with parameters, you must supply a PL/SQL variable or a literal value for each parameter when you open the cursor, as shown in Listing 11.16.

Listing 11.16. Declaring a cursor with parameters.

SQL> declare
  2
  2  cursor patients_with_hypertension
  3         (patient_age number,
  4          normal_dyastolic number) is
  5          select patient_id
  6          from patient
  7          where
  8          dyastolic > normal_dyastolic * (age+200)/200 and
  9          systolic > 180;
 10
 10  Patient_ID Patient.Patient_ID%type;
 11
 11  begin
 12
 12  open patients_with_hypertension (45, 80);
 13
 13  end;
 14  /
PL/SQL procedure successfully completed.

If the cursor was declared with parameters—and default values were specified for those parameters—you aren't required to furnish a PL/SQL variable or a literal value for each parameter, as illustrated in Listing 11.17.

Listing 11.17. Default values for cursor parameters used.

SQL> declare
  2
  2  cursor patients_with_hypertension
  3         (patient_age number default 55,
  4          normal_dyastolic number default 70,
  5          normal_systolic  number default 130) is
  6          select patient_id
  7          from patient
  8          where
  9          dyastolic > normal_dyastolic * (age+200)/200 and
 10          systolic > normal_systolic * (age+200)/200;
 11
 

                                                     continues

Listing 11.17. continued

 11  Patient_ID Patient.Patient_ID%type;
 12
 12  begin
 13
 13  dbms_output.enable;
 14
 14  open patients_with_hypertension;
 15
 15  loop
 16
 16     fetch patients_with_hypertension
 17           into Patient_ID;
 18     exit when patients_with_hypertension%notfound;
 19
 19     dbms_output.put_line(patient_record.patient_id);
 20
 20  end loop;
 21
 21  end;
 22  /

N3393
PL/SQL procedure successfully completed.

If the cursor was declared with parameters—but no default values were specified for those parameters—you must supply a PL/SQL variable or a literal value for each parameter. Listing 11.18 illustrates how Oracle will reject the open cursor statement if the required arguments are not supplied.

Listing 11.18. Oracle returns an error when arguments are not supplied for the cursor.

SQL> declare
  2
  2  cursor patients_with_hypertension
  3         (patient_age number,
  4          normal_dyastolic number,
  5          normal_systolic number) is
  6          select patient_id
  7          from patient
  8          where
  9          dyastolic > normal_dyastolic * (age+200)/200 and
 10          systolic > 180;
 11
 11  Patient_ID Patient.Patient_ID%type;
 12
 12  begin
 13
 13  open patients_with_hypertension;

 14
 14  end;
 15  /
declare
 *
ERROR at line 1:
ORA-06550: line 13, column 1:
PLS-00306: wrong number or types of arguments
           in call to `PATIENTS_WITH_HYPERTENSION'
ORA-06550: line 13, column 1:
PL/SQL: SQL Statement ignored

Fetching Rows from a Cursor

Once the cursor has been opened, the query has been executed and the qualified rows have been identified. To retrieve the rows, you must execute the FETCH statement, which retrieves the value of each column specified in the cursor's SELECT statement and places it in a PL/SQL variable. In general, you'll want to fetch rows within a loop. To illustrate, Listing 11.19 contains an anonymous PL/SQL block (starting with the first line) that fetches rows from the Instructor table.

Listing 11.19. Fetching rows 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_instructors is
  6    select Instructor_ID, Last_Name, First_Name
  7    from Instructor
  8    order by Instructor_ID;
  9
  9  begin
 10
 10  dbms_output.enable;
 11
 11  open get_instructors;
 12
 12  loop
 13
 13    fetch get_instructors into
 14      Instructor_ID, Last_Name, First_Name;
 15    exit when get_instructors%notfound;
 16
 16    dbms_output.put_line(Instructor_ID);
 17
 17  end loop;
 

                                                     continues

Listing 11.19. continued

 18
 18  end;
 19  /

A612
B331
B391
D201
D944
E301
E405
E491
G331
J505
L391
M101
P331
R983
S131
T149
W490
Y561

PL/SQL procedure successfully completed.

You should use the EXIT statement to exit the loop when all rows have been fetched from the cursor.

The syntax to use is as follows:

EXIT [label] [WHEN condition]

The variables are defined as follows:

label is an optional label name that specifies which loop should be exited.

condition is a PL/SQL condition that returns a Boolean value.

Four specific attributes are associated with declared cursors: %ROWCOUNT, %FOUND, %NOTFOUND, and %ISOPEN. These attributes are referenced by placing them after a cursor's name. To terminate a loop with the EXIT statement, reference a cursor's %NOTFOUND attribute in the following way:

exit when get_instructors%notfound;

Closing a Cursor

You must close a cursor for two reasons:

• To reopen it with a different set of parameter values
• To release the resources consumed by thecursor