cursors
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:
- Declare the cursor.
- Open the cursor for a query.
- Fetch the result into PL/SQL variable.
- 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:
- The values of the bind PL/SQL variables are examined.
- Based on the values and the content of the table(s) referenced in the query, the active set is determined.
- The active set pointer is set to the first row.
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:
- %found is a boolean attribute that returns true if the previous fetch returned a row and false if it didn't.
- %notfound behaves opposite to %found.
- %isopen is a boolean attribute that returns true if the cursor open and false otherwise.
- %rowcount is a numeric attribute returns the number of rows fetched by the cursor row so far.
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:
- First, the record v_row is not declared in the declarative section. This variable is implicitly declared by the PL/SQL compiler, similar to the index for a numeric loop. The type of this variable is c_all_rows&rowtype, and the scope is only the for loop itself.
- Second, the cursor c_all_rows is implicitly opened, fetched from, and closed by the loop. The cursor is opened before the loop starts. Before each iteration, the %found attribute is checked. When the active set is completely fetched, the cursor is closed as the loop ends.
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:
- the for update clause in the cursor declaration
- and the where current of clause in an update or delete statement.
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;
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
- Cursor variables cannot be defined in a package. The type itself can be, but the variable cannot.
- Remote subprograms cannot return the value of a cursor variable.
- PL/SQL collections cannot store cursor variables.
- The query associated with a cursor variable in the open ... for statement cannot be for update.
Comments
Post a Comment