Listing 11.12. continued

 13    when Fever_Out_of_Range then
 14      raise_application_error (-20000,
              `Fever is out of the range 65 Deg. F to 115 Deg. F');
 15
 15  end;
 16  /
declare
 *
ERROR at line 1:
ORA-20000: Fever is out of the range 65 Deg. F to 115 Deg. F
ORA-06512: at line 14

Retrieving Data with a Cursor

When you're using SQL*Plus or SQL Worksheet, you can submit a query without being concerned with the number of rows that are returned. It doesn't matter whether the query retrieves zero, one, or a thousand rows. However, this situation isn't true for PL/SQL subprograms. You cannot use an ordinary SELECT statement to retrieve more than one row. If a SELECT statement in a PL/SQL subprogram—whether it's an anonymous block, a stored procedure, or a trigger—retrieves more than one row, Oracle returns an error message. Obviously, the capability to retrieve more than one row is essential, and some mechanism to do so must be available. The resource that Oracle provides to accomplish this job is the cursor. Cursors are automatically created and used by Oracle utilities such as SQL*Plus.

NEW TERM
A cursor is a mechanism for programmatically retrieving an arbitrary number of rows with a SELECT statement.

Listing 11.13 illustrates how Oracle returns an error because the SELECT statement returns more than one instructor.

Listing 11.13. Oracle error resulting from SELECT statement that returns more than one row.

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  begin
  6
  6  select Instructor_ID, Last_Name, First_Name
  7  into   Instructor_ID, Last_Name, First_Name
  8  from Instructor
  9  order by Instructor_ID;
 

 10
 10  end;
 11  /
declare
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 6

You can think of a cursor as a window into the result set of a query. (See Figure 11.1.) You will generally perform four steps when you use a cursor:

1. Declare the cursor. The cursor is assigned a name and associated with a SELECT statement, which is parsed.
2. Open the cursor. The Oracle RDBMS executes the query associated with the cursor and determines the qualified rows (active set).
3. Fetch rows from the cursor. The values for each row are returned to the PL/SQL subprogram environment. Rows are returned one at a time.
4. Close the cursor. All resources consumed by Oracle related to the cursor are released.

Here is a stored function that determines if a specified class conflicts with a student's schedule. The function—Schedule_Conflict—has two arguments: Student_ID and Class_ID. It

determines if there is a conflict by looking for any other classes that are part of the student's current schedule that have the same schedule as the proposed class. A cursor is used to perform this query. Listing 11.14 contains the full text of the function.

Listing 11.14. Using a cursor in a stored function.

SQL> create or replace function schedule_conflict (arg_student_ID IN varchar2,
  2                              arg_class_ID   IN varchar2)
  3    return number is
  4
  4  conflicting_classes  number := -1;
  5  normal               number := 0;
  6
  6  cursor get_other_classes is
  7         select SS.Class_ID
  8         from Student_Schedule SS, Class C
  9         where
 10         SS.Class_ID = C.Class_ID and
 11         (C.Semester, C.School_Year, C.Schedule_ID) =
 12         (select Semester, School_Year, Schedule_ID
 13          from Class
 14          where
 15          Class_ID = arg_class_ID);
 16
 16  Conflicting_Class_ID  Class.Class_ID%type;
 17  status  number;
 18
 18  begin
 19
 19  -- Need to look at the other classes in the student's schedule
 20  -- for the same semester and school year.
 21
 21  open get_other_classes;
 22
 22  loop
 23
 23    fetch get_other_classes into Conflicting_Class_ID;
 24    exit when get_other_classes%notfound;
 25
 25  end loop;
 26
 26  if get_other_classes%rowcount > 0 then
 27    status := conflicting_classes;
 28  else
 29    status := normal;
 30  end if;
 31
 31  close get_other_classes;
 32
 32  return status;
 33
 33  end;
 34  /

Function created.

SQL> select Student_ID, Class_ID
  2  from Student_Schedule
  3  where
  4  Student_ID = `10231311';

STUDENT_ID           CLASS_ID
-------------------- --------------------
10231311             104200
10231311             104500

SQL> select schedule_conflict(`10231311','104200') from dual;

SCHEDULE_CONFLICT(`10231311','104200')
--------------------------------------
                                    -1

ANALYSIS
Here's a look at each step of this cursor in detail. First, beginning at line 6, the cursor—Get_Other_Classes—is declared as a join between two tables—Student_Schedule and Class. Second, the cursor is opened within the executable section of the stored function (in line 21). Third, a loop statement fetches rows from the cursor until no more rows are retrieved (in line 23).

As you can see at the end of Listing 11.14, a query of the Student_Schedule table shows that Student ID 10231311 is registered for two classes: 104200 and 104500. The function is invoked in the final SELECT statement; it checks to see if class 104200 for student 10231311 will result in a conflict. Of course it will, because the student is already enrolled in the class. The function returns _1, which indicates that the status is conflicting classes.

Declaring a Cursor

Every cursor must be declared before it can be used. Declaring a cursor means giving it a name and specifying the SELECT statement with which the cursor is associated.

The basic syntax used in PL/SQL to declare a cursor is as follows:

CURSOR cursor-name
[(parameter1 parameter1-datatype [:= default1],
...
 parameterN parameterN-datatype [:= defaultN])]
IS select-stmt;

The variables are defined in this way:

cursor-name is the name of the cursor and subject to Oracle object-naming

requirements.

parameter1 is the name of the first parameter to be supplied to the cursor.

parameter1-datatype is the datatype for parameter1.