Cursors

In a previous lecture we saw how we can use SQL statements in PL/SQL. As you remember this way has a disadvantage, namely, all our statements should return only one row. This functionality is enhanced through the use of cursors, which allow a program to take explicit control of SQL statement processing.

In order to process a SQL statement, Oracle will allocate an area of memory known as the context area. The context area contains information necessary to complete the processing (including the number of rows processed, a pointer to the parsed representation of the statement, set or rows returned by the query). A cursor is a handle, or pointer, to the context area. Through the cursor, a PL/SQL program can control the context area and what happens to it as a statement is processed.

Processing explicit cursors

The four steps necessary for explicit cursor processing are as follows:
  1. Declare the cursor.
  2. Open the cursor for a query.
  3. Fetch the result into PL/SQL variable.
  4. Close the cursor.

Declaring a cursor

Declaring a cursor defines a name of the cursor and associates it with a select statement: 
cursor cursor_name is select_statement;
where select_statement is a usual SQL select statement with no into clause. 
declare
  cursor c_stdents is
    select SID 
      from Register
     where CID = 'IST467';
begin
A cursor declaration can reference PL/SQL variables as well: 
declare
  v_CID Course.CID%type;
  cursor c_stdents is
    select SID 
      from Register
     where CID = v_CID;
begin
If you use PL/SQL variables in a cursor declaration you need to make sure you define these variables before declaring the cursor. To ensure that all variables referenced in a cursor declaration are declared before the reference, you can declare all cursors at the end of a declarative section. The only exception to this is when the cursor name itself is used in a reference, such as the %rowtype attribute.

Opening a cursor

The syntax for opening a cursor is 
open cursor_name;
When the cursor is opened, the following things happen: Note that bind variables are examined at cursor open time, and only at cursor open time. For example, the cursor in the following code will work with IST467 course not IST163: 
declare
  v_CID Course.CID%type;
  cursor c_SID is
    select SID
      from Registration
     where CID = v_CID;
begin
  v_CID := 'IST467';
  open c_SID;
  v_CID := 'IST163';
end;
The active set, or the set of rows that match the query, is determined at cursor open time. The where clause is evaluated against the table or tables referenced in the from clause of the query, and any row for which the condition is TRUE are added to the active set. A pointer into the set is also established at cursor open time. This pointer indicates which row is to be fetched next by the cursor. Once a cursor has been opened, it cannot be reopened unless it is first closed.

Fetching from a cursor

To get the next row from the active set, we need to use the fetch statement. The fetch statement has two forms: 
fetch cursor_name into list_of_variables;
and 
fetch cursor_name into PL/SQL_record;
where list_of_variables is a comma-separated list of previously declared PL/SQL variables, and PL/SQK_record is a previously declared PL/SQL record. 
declare
  cursor c_row is select * from pl_test;
  v_row c_row%rowtype;
begin
  open c_row;
  fetch c_row into v_row;
  close c_row;
  dbms_output.put_line('name: ' || v_row.name || '  id: ' || v_row.id);
end;
Note: fetch statements will retrieve a single row at a time. In Oracle 8i and higher, we can fetch more than one row at a time into a collection, using the bulk collect clause.

Closing a cursor

When all of the active set has been retrieved, the cursor should be closed. This tells PL/SQL that the program is finished with the cursor, and the resources associated with it can be freed. The syntax for closing a cursor is 
close cursor_name;
Once a cursor is closed, it is illegal to fetch from it. Similarly, it is illegal to close an already closed cursor.

Cursor attributes

There are four attributes available in PL/SQL that can be applied to a cursor. Cursor attributes are appended to a cursor name in a PL/SQL block. These attributes are:

Parameterized cursors

There is an additional way of using bind variables in a cursor. A parameterized cursor takes arguments, similar to a procedure: 
declare
  cursor c_SID (v_CID Courses.CID%type) is
    select SID
      from Registration
     where CID = v_CID;
begin
  open c_SID('IST467');
  ...
  close c_SID;
  open c_SID('IST163');
  ...
  close c_SID;
end;

Cursor fetch loops

As we mentioned before the fetch operation brings only one row from the active set. That's why this operation is usually done in a fetch loop. In this section we discuss several kinds of cursor fetch loops.

Simple loops

We can use the simplest PL/SQL loop (loop ... end loop) to fetch all rows from the active set: 
declare
  v_row pl_test%rowtype;
  cursor c_all_rows is 
    select * from pl_test;
begin
  open c_all_rows;
  if c_all_rows%isopen then
    loop
      fetch c_all_rows into v_row;
      exit when c_all_rows%notfound;
      dbms_output.put_line('name: ' || v_row.name ||  '   id: ' || v_row.id);
    end loop;
    close c_all_rows;
  else
    dbms_output.put_line('Error: cannot open cursor');
  end if;
end;
Note that we placed the exit when statement immediately after the fetch operation. After the last row has been fetched, the next attempt to use fetch sets the %notfound attribute to true an exits from the loop. If we put the exit when statement at the end of the loop, then the last row of the table will be printed twice because the last fetch will not change the value of the v_row variable. Please run similar code to check this out.

while loops

A cursor fetch loop can also be constructed using the while ... end loop syntax. We can rewrite the previous example like this: 
declare
  v_row pl_test%rowtype;
  cursor c_all_rows is 
    select * from pl_test;
begin
  open c_all_rows;
  fetch c_all_rows into v_row;
  while c_all_rows%found loop
    dbms_output.put_line('name: ' || v_row.name ||  '   id: ' || v_row.id);
    fetch c_all_rows into v_row;
  end loop;
  close c_all_rows;
end;
Note that the fetch statement appears twice: before the loop starts and in the body of the loop. This is done to to make the property %found available.

Cursor for loops

Both previously discussed fetch loops required explicit cursor processing (open, fetch, close). PL/SQL provides a simpler type of loop, which implicitly handles the cursor processing. This is known as cursor for ... in loop. Using this loop we can rewrite the same example shorter: 
declare
  cursor c_all_rows is 
    select * from pl_test;
begin
  for v_row in c_all_rows loop
    dbms_output.put_line('name: ' || v_row.name ||  '   id: ' || v_row.id);
  end loop;
end;
There are two important things to note about this example:

Implicit for loops

The syntax for a for ... in loop can be shortened even more, in addition to the record, the cursor itself can be implicitly declared: 
begin
  for v_row in (select * from pl_test) loop
    dbms_output.put_line('name: ' || v_row.name ||  '   id: ' || v_row.id);
  end loop;
end;
Both the cursor and the record are implicitly declared. The cursor has no name, however.

select for update cursors

Very often we need not only retrieve some rows by the cursor, but also modify these rows. PL/SQL has special syntax to do this. This method includes two parts:

for update

The for update clause is legal as the last clause of the statement. The syntax is: 
select ...
  from ...
  ...
  for update [of column_reference] [nowait];
where column_reference is a column in the table against which the query is performed. We can also use a list of columns. Here is a small example: 
declare
  cursor c_StudentGrade is
    select *
      from Transcript
     where SID = 'IS2345'
       for update of grade;
begin
  ...
Normally, a select operation will not take any locks on the rows being accessed. This allows other sessions connected to the database to change the data being selected. At open time, when the active set is determined, Oracle takes a snapshot of the table. Any changes that have been committed prior to this point are reflected in the active set. Any changes made after this point, even if they are committed, are not reflected unless the cursor is reopened, which will evaluate the active set again. However, if the for update clause is present, exclusive row locks are taken on the rows in the active set before the open returns. These locks prevent other sessions from changing the rows in the active set until the transaction is committed or rolled back.

If another session already has locks on the rows in the active set, then the select ... for update operation will wait for these locks to be released by the other session. There is no time-out for this waiting period; the select ... for update will hang until the other session releases the lock. To handle this situation, the nowait clause is available. If the rows are locked by another session, the open will return immediately with the Oracle error: 

ORA-54: resource busy and acquire with NOWAIT specified
In Oracle 9i, we can use the syntax: 
select ... from ... for update [of column_reference] [wait n];
where n is the number of seconds to wait. If the rows are not unlocked within n seconds, then the ORA-54 error will be returned.

where current of

If the cursor is declared with the for update clause, the where current of clause can be used in an update 
update table_name set ... where current of cursor_name;
or delete statement 
delete from table_name where current of cursor_name;
The where current of clause evaluates to the row that just retrieved by the cursor. The following example shows how to compute the number of credits for a student: 
declare
  cursor c_StudInfo is
    select * from Student where SID='IS2345'
    for update of credit_hours;
  v_Info c_StudInfo%type;
  v_credit Transcript.credit%type;
begin
  open c_StudInfo;
  fetch c_StudIndo into V_Ivfo;
  /* count credit hours for the current semester */
  select sum(credit)
    from Transcript
   where year=2003 and term='Spring' and SID='IS2345'
    into v_credit;
  /* update student information by increasing the number of credit hours taken */
  update Student 
     set credit_hours=credit_hours+v_credit 
   where current of c_StudInfo;
  close c_StudInfo;
end;
Here you can find more difficult example from Scott Urman's PL/SQL Programming.

Fetching across commit

Please remember that commit releases all locks held by s session. That's why we need to use commit statement (if we need to) only after we are done with the cursor opened with for update clause. Because the for update cursor acquires locks, these will be released by the commit. When this happens, the cursor is invalidated. Any subsequent fetches will return the Oracle error: 
ORA-1002: fetch out of sequence

Cursor variables

So far we have seen only static cursor examples - the cursor is associated with a one SQL query, and this query is known when the block is compiled. A cursor variable, on the other hand, can be associated with different queries at runtime. Cursor variables are analogous to PL/SQL variables, which can hold different values at runtime. Static cursors are analogous to PL/SQL constants because they can only be associated with one runtime query.

In order to use a cursor variable, it must be declared. Storage for it must then be allocated at a runtime. It is then opened, fetched, and closed similar to a static cursor.

Declaring a cursor variable

A cursor variable is a reference type. In order to use a reference type, first the variable has to be declared, and then the storage has to be allocated. Reference types in PL/SQL are declared using the syntax: 
ref type
where type is a previously defined type. The ref keyword indicates that the new type will be a pointer to the defined type. The type of the cursor variable is therefore ref cursor. The complete syntax for defining a cursor variable type is 
type type_name is ref cursor [return return_type];
where return_type is a record type indicating the types of the select list that will eventually be returned by the cursor variables. The cursor variables defined with return clause are called constrained variables - they are declared for a specific return type only. When a variable is later opened, it must be opened for a query that returns type of the cursor. If not, the predefined exception ROWTYPE_MISMATCH is raised.

PL/SQL also allows the declaration of unconstrained cursor variables. An unconstrained cursor variables does not have a return clause. Such a cursor variable can be later opened for any query. The following section illustrates how to declare cursor variables 

declare
  -- declaring a cursor variable type (cvt) Student
  type cvt_Student is ref cursor return Student%rowtype;
  cv_Student cvt_Student;
  -- declaring an unconstrained CVT Generic
  type cvt_Generic is ref cursor;
  cv_Gen cvt_Generic;
begin

Opening and closing a cursor variable

In order to associate a cursor variable with a particular select statement, the open syntax is extended to allow the query to be specified. The extended open ... for syntax is 
open cursor_variable for select_statement;
For example: 
declare
  type cvt_Atlas is ref cursor return Atlas%rowtype;
  cv_Atlas cvt_Atlas; 
begin
  open cv_Atlas for
    select * from Atlas;
  ...
end;

To close a cursor variable, we need to use the same close statement we use for static cursors. This frees the resources used for the query. It's illegal to close a cursor variable that is already closed.

Let us illustrate how cursor variable works in the following example. We will create a function that takes a student ID and returns the GPA of the student. The problem is the student can be currently enrolled or an alumni. Depends on this we need to select either from the Transcript table, or from the AlumniTranscript table: 

create or replace function GPA(p_sid in Student.SID%type) 
return number as
  /* cursor variable to find out which table to look in */
  type TCV_Student is ref cursor return Student%rowtype;
  cv_Student TCV_Student;   
  v_Student Student%rowtype;
  /* cursor variable to figure out the GPA */
  type TNumber is record ( value number );
  type TCV_GPA is ref cursor return TNumber;
  cv_GPA TCV_GPA;
  v_GPA  TNumber;
begin
  open cv_Student for 
    select * from Student where sid=p_sid;
  fetch cv_Student into v_Student;
  if cv_Student%found then
    /* this student is currently enrolled */
    open cv_GPA for 
      select avg(grade) from Transcript where SID=p_SID;
  else
    /* this is probably an alumni */
    open cv_GPA for 
      select avg(grade) from AlumniTranscript where SID=p_SID;
  end if; 
  fetch cv_GPA into v_GPA;
  close cv_Student;  
  close cv_GPA;
  return v_GPA.value;
end;

Restrictions on using cursor variables