|
|
Cursors are a way of representing a result set within SQL code, and they allow you to loop through a set of rows, one row at a time. In general, if it's possible to avoid using cursors, then you should use the alternative because the performance of cursors is generally poor. However, situations do arise where you need to be able to loop through every row of a result set individually. More important, though, you need to use cursors in Oracle and DB2 if you want to create a stored procedure that returns a result set.
You'll look quickly at the syntax for declaring and using cursors before you move on to look at an actual example, which should make everything much clearer!
As mentioned previously, you can declare cursor variables using this syntax:
DECLARE CursorName CURSOR FOR CursorSpec;
Here the CursorSpec section is the SQL query with which the cursor will be used. For example:
DECLARE cur_students CURSOR FOR SELECT StudentID, Name FROM Student;
Oracle uses IS instead of FOR:
If you want to update the data in the database through the cursor, you need to add the FOR UPDATE clause:
DECLARE cur_students CURSOR IS SELECT StudentID, Name FROM Student FOR UPDATE;
This locks any tables queried so that no other user can update the data in the table; if the query affects more than one table, you can lock only a specific table using FOR UPDATE OF ColumnList, where ColumnList is a comma-delimited list of column names. In this case, a table will only be locked if one or more of its columns appears in the list of column names.
You'll look at locking in Chapter 10, "Transactions."
SQL Server also allows you to specify a FOR UPDATE [OF ColumnList] clause to indicate which columns you want to be updateable (alternatively, you can specify FOR READ ONLY if you don't want the cursor to be updateable). You can also specify that you want the cursor to be insensitive to changes made by other uses by placing the word INSENSITIVE after the cursor name; and you can specify whether you want the cursor to be scrollable (rather than forward only) by inserting the keyword SCROLL before CURSOR :
DECLARE cur_students INSENSITIVE SCROLL CURSOR FOR SELECT StudentID, Name FROM Student FOR UPDATE OF Student;
Notice that, unlike other variables, SQL Server cursor names don't begin with the @ character.
As well as this SQL-92 syntax, Transact-SQL has an extended syntax:
DECLARE CursorName CURSOR [Scope] [Scrollability] [CursorType] [LockType] [TYPE_WARNING] FOR CursorSpec [FOR UPDATE [OF ColumnList]]
Table 9-1 describes the options available using this syntax.
|
Option |
Description |
|---|---|
|
Scope |
This can be LOCAL or GLOBAL. Local cursors can only be accessed from within the current stored procedure or batch of SQL statements; global cursors are available to any procedures using the same connection. |
|
Scrollability |
FORWARD_ONLY or SCROLL. Indicates whether the cursor is scrollable, so you can move backward and forward through it or forward only (in which case you can only move forwards, one row at a time). |
|
CursorType |
One of STATIC, KEYSET, DYNAMIC, or FAST_FORWARD. Static cursors make a local copy of the data, so they don't reflect changes made by other users or allow modifications to the underlying data. |
|
Keyset cursors fix the order and number of the rows when the cursor is opened; changes to nonkey values by other users will be visible, but rows inserted by other users won't be accessible. If another user deletes a row, then it won't be possible to fetch that row. If a key value for a row is changed, the keyset cursor will treat that as the deletion of the existing row and an insertion of a new row. |
|
|
Dynamic cursors reflect any changes made by other users. Fast-forward cursors are a special type of forward-only, read-only cursor, which is optimized for performance. |
|
|
LockType |
This can be READ_ONLY, SCROLL_LOCKS, or OPTIMISTIC. Read-only cursors aren't updateable at all, optimistic cursors allow rows to be updated only if the underlying data hasn't changed since the cursor was opened, and cursors with scroll locks lock each row as it's fed into the cursor, so updates are always possible. |
|
TYPE_WARNING |
If this option is specified, a warning will be generated if the cursor is implicitly converted to another type because the CursorSpec is incompatible with the requested options. |
DB2 has two extra options in the DECLARE CURSOR statement (Table 9-2 describes these options):
DECLARE CURSOR CursorName [WITH HOLD] [WITH RETURN [TO CALLER | TO CLIENT]] FOR CursorSpec;
|
Option |
Description |
|---|---|
|
WITH HOLD |
If the WITH HOLD option is specified, the cursor can be kept open over multiple transactions. If the transaction is aborted, the cursor will be closed. |
|
WITH RETURN [TO CALLER | TO CLIENT] |
Indicates whether the cursor can be returned to a client application from a stored procedure. If WITH RETURN TO CALLER is specified, an open cursor will return a result set to any caller, such as a client application or another stored procedure. The WITH RETURN TO CLIENT option only allows the procedure to return a result set directly to the client application—the result set will be invisible to any intermediate stored procedure or UDF. |
Before you can use a cursor, you need to open it:
OPEN CursorName;
You can now retrieve rows from this cursor into local variables. To do this, you use the FETCH keyword (but the syntax varies somewhat for the different RDBMSs). The simplest is DB2:
FETCH CursorName INTO var1, var2, ...varn;
The variables that you're storing the row values in must obviously match the data types of the columns. For example, to fetch the values from the cur_students cursor into variables called StudID and StudName, you use this statement:
SQL Server uses the following syntax:
FETCH [NEXT] FROM CursorName INTO @var1, @var2, ...@varn;
So, the previous statement appears in SQL Server as follows:
FETCH NEXT FROM cur_students INTO @StudID, @StudName;
Depending on the cursor type, you can also fetch the first, previous, or last row from the cursor by specifying FETCH FIRST, FETCH PRIOR, or FETCH LAST, instead of FETCH NEXT. Alternatively, you can specify a particular row to retrieve using FETCH ABSOLUTE n, which retrieves the nth row in the cursor, or FETCH RELATIVE n, which retrieves the row n rows after the current row (for a positive number) or before it (if n is negative).
The syntax for Oracle is a bit different, in that you read the row from the cursor into a single variable. This variable must, of course, be of the same type as a row in the cursor or CursorName%ROWTYPE. You can retrieve a row into this variable using the syntax:
FETCH CursorName INTO RowTypeVariable;
You can then access an individual column value using RowTypeVariable.ColumnName.
Each system provides its own mechanism for looping through the rows in the cursor, so you'll examine these using an example. Once you've finished with the cursor, you need to close it:
CLOSE CursorName;
Finally, if you're using SQL Server, you also need to release the resources used by the cursor with the DEALLOCATE keyword:
DEALLOCATE CursorName;
This step isn't necessary in Oracle and DB2, and they don't support DEALLOCATE.
Before you look at a concrete example of cursors in action, you need to look briefly at implicit cursors in Oracle and DB2. Implicit cursors provide a shortcut syntax for creating, opening, looping through, and finally closing a cursor. This syntax uses a FOR loop and resembles somewhat the foreach construct available in some programming languages.
The syntax for Oracle is as follows:
FOR CursorName IN (CursorSpec) LOOP ...statements that use the cursor... END LOOP;
And for DB2:
FOR CursorName AS CursorSpec DO ...statements that use the cursor... END FOR;
This opens a cursor, loops through each row in turn, and then closes the cursor. As you can see, this approach is much easier to code and also more readable. The following DB2 and Oracle examples use both the implicit and explicit syntaxes.
For this example, you'll print a list of each professor in the database and all the students in each class they take. Although there are some significant differences between the SQL Server, Oracle, and DB2 versions of this procedure, the overall structure is similar. You start by opening a cursor containing the ProfessorID and Name columns of the Professor table and looping through each row in this cursor, printing the name of the professor. For each row, you open a nested cursor containing the names of all the students taught by that professor and loop through this, printing each name.
To loop through the rows in a cursor in SQL Server, you use the WHILE loop you saw previously. SQL Server has a system variable called @@FETCH_STATUS, which returns zero as long as the last attempt to fetch a row from the cursor was successful.
 |
Enter the following code into Query Analyzer and execute it:
DECLARE @ProfName VARCHAR(50);
DECLARE @StudentName VARCHAR(50);
DECLARE @ProfID INT;
DECLARE cur_profs CURSOR FOR
SELECT ProfessorID, Name FROM Professor;
OPEN cur_profs;
FETCH NEXT FROM cur_profs INTO @ProfID, @ProfName;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @ProfName;
DECLARE cur_students CURSOR FOR
SELECT DISTINCT Name FROM Student s
INNER JOIN Enrollment e
ON s.StudentID = e.StudentID
INNER JOIN Class c
ON e.ClassID = c.ClassID
WHERE c.ProfessorID = @ProfID;
OPEN cur_students;
FETCH NEXT FROM cur_students INTO @StudentName;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @StudentName;
FETCH NEXT FROM cur_students INTO @StudentName;
END
CLOSE cur_students;
DEALLOCATE cur_students;
PRINT '---------------';
FETCH NEXT FROM cur_profs INTO @ProfID, @ProfName;
END
CLOSE cur_profs;
DEALLOCATE cur_profs;
To see what's going on here more clearly, let's look at the outer loop separated from the inner loop:
DECLARE cur_profs CURSOR FOR SELECT ProfessorID, Name FROM Professor; OPEN cur_profs; FETCH NEXT FROM cur_profs INTO @ProfID, @ProfName; WHILE @@FETCH_STATUS = 0 BEGIN PRINT @ProfName; -- Use cursor PRINT '---------------'; FETCH NEXT FROM cur_profs INTO @ProfID, @ProfName; END CLOSE cur_profs; DEALLOCATE cur_profs;
You declare and open the cursor as normal and fetch the first row into two variables, @ProfID and @ProfName. You then loop for as long as the @@FETCH_STATUS variable remains at zero, printing out the professor's name at the start of the loop and fetching the next row at the end (after you've finished using the current row). When the end of the cursor is reached, @@FETCH_STATUS will be set to -1, so the loop will end. You then close and deallocate the cursor.
Now let's look at the inner loop:
DECLARE cur_students CURSOR FOR
SELECT DISTINCT Name FROM Student s
INNER JOIN Enrollment e
ON s.StudentID = e.StudentID
INNER JOIN Class c
ON e.ClassID = c.ClassID
WHERE c.ProfessorID = @ProfID;
OPEN cur_students;
FETCH NEXT FROM cur_students INTO @StudentName;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @StudentName;
FETCH NEXT FROM cur_students INTO @StudentName;
END
CLOSE cur_students;
DEALLOCATE cur_students;
The structure of this loop is basically the same as that for the outer one: You declare and open the cursor containing the students' names and fetch the first row into a variable. You then loop through each row, printing out the student's name and fetching the next row from the cursor. When @@FETCH_STATUS is set to zero, the loop ends, and you close and deallocate the cursor. At this point, you fetch the next row for the professor cursor, so @@FETCH_STATUS will be reset, and the outer loop will continue.
The output from this example is as follows:
Prof. Dawson Anna Wolff John Jones Julia Picard --------------- Prof. Williams Bruce Lee ...
|
The chief difference between the examples for SQL Server and Oracle is that you're using an implicit cursor for the inner loop. The way that you loop through the rows of the explicit cursor is similar. The cursor has a FOUND attribute (CursorName%FOUND), which evaluates to true if the last FETCH returned a row or false otherwise. You therefore just need to loop while this remains true.
|
Enter the following code into SQL*Plus:
SET SERVEROUT ON
DECLARE
CURSOR cur_profs IS
SELECT ProfessorID, Name FROM Professor;
prof cur_profs%ROWTYPE;
BEGIN
OPEN cur_profs;
FETCH cur_profs INTO prof;
WHILE cur_profs%FOUND
LOOP
dbms_output.put_line(prof.Name);
FOR c1 IN (SELECT DISTINCT Name FROM Student s
INNER JOIN Enrollment e
ON s.StudentID = e.StudentID
INNER JOIN Class c
ON e.ClassID = c.ClassID
WHERE c.ProfessorID = prof.ProfessorID)
LOOP
dbms_output.put_line(c1.Name);
END LOOP;
FETCH cur_profs INTO prof;
dbms_output.put_line('----------------');
END LOOP;
CLOSE cur_profs;
END;
/
Again, let's look at the outer loop separated from the inner loop to see more clearly what's happening:
OPEN cur_profs;
FETCH cur_profs INTO prof;
WHILE cur_profs%FOUND
LOOP
dbms_output.put_line(prof.Name);
... Use cursor
FETCH cur_profs INTO prof;
dbms_output.put_line('----------------');
END LOOP;
CLOSE cur_profs;
You start by opening the professor cursor and reading the first row into the prof variable, which you've declared as cur_profs%ROWTYPE. You then loop while cur_profs%FOUND remains true. For each row, you print the professor's name using dbms_output.put_line(), and (after you've looped through the inner cursor) fetch the next row. When the end of the cursor is reached, cur_profs%FOUND will evaluate to false, the loop will end, and you close the cursor.
The inner loop is much simpler because it uses an implicit cursor:
FOR c1 IN (SELECT DISTINCT Name FROM Student s
INNER JOIN Enrollment e
ON s.StudentID = e.StudentID
INNER JOIN Class c
ON e.ClassID = c.ClassID
WHERE c.ProfessorID = prof.ProfessorID)
LOOP
dbms_output.put_line(c1.Name);
END LOOP;
Notice that you access the ProfessorID column of the current row in the cur_profs cursor through the prof rowtype variable as prof.ProfessorID. You can access the columns in the implicit cursor using the same syntax (but without the need for a rowtype variable):
c1.Name
The output from this example is as follows:
Prof. Dawson Anna Wolff John Jones Julia Picard ---------------- Prof. Williams Bruce Lee ...
|
This example is a little trickier in DB2 for a number of reasons. The first is purely practical—DB2 doesn't provide an easy way of writing directly to the Command Center output. In DB2, DECLARE CURSOR isn't valid outside stored procedures, so you need to define a stored procedure for the example. So, what you'll do is construct a long string that contains the names of the professors and students and then pass this as an output parameter. True, this does seem a bit contrived, but it demonstrates the cursor syntax just as well as if you were printing to Command Center directly!
The next problem is that DB2 doesn't provide a convenient attribute of the cursor that tells you when the end of the cursor has been reached. To get around this, you need to define a "continue handler" that will execute when a particular SQL state occurs (in this case, the SQL state 02000):
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET eof = 1;
When the end of the cursor is reached, SQL state 02000 will be signaled, your continue handler will execute, and the stored procedure will continue to execute. Within the continue handler, you just set the value of a flag to 1.
You'll look at SQL states in more detail shortly, when you learn about error handling in stored procedures.
|
Create and build this procedure using Development Center:
CREATE PROCEDURE GetClassAttendees (
OUT AllClassAttendees VARCHAR(1000))
P1: BEGIN
DECLARE ProfID INT;
DECLARE ProfName VARCHAR(50);
DECLARE tmp_msg VARCHAR(1000) DEFAULT '';
DECLARE eof SMALLINT DEFAULT 0;
DECLARE cur_profs CURSOR FOR
SELECT ProfessorID, Name FROM Professor;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000'
SET eof = 1;
OPEN cur_profs;
WHILE eof = 0
DO
FETCH cur_profs INTO ProfID, ProfName;
SET tmp_msg = tmp_msg || ProfName || ': ';
FOR cur_students AS SELECT DISTINCT Name FROM Student s
INNER JOIN Enrollment e
ON s.StudentID = e.StudentID
INNER JOIN Class c
ON e.ClassID = c.ClassID
WHERE c.ProfessorID = ProfID
DO
SET tmp_msg = tmp_msg || Name || ', ';
END FOR;
END WHILE;
CLOSE cur_profs;
SET AllClassAttendees = LEFT(tmp_msg, LENGTH(tmp_msg) – 2);
END P1
Again, let's look at the outer loop first:
DECLARE cur_profs CURSOR FOR SELECT ProfessorID, Name FROM Professor; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET eof = 1; OPEN cur_profs; WHILE eof = 0 DO FETCH cur_profs INTO ProfID, ProfName; SET tmp_msg = tmp_msg || ProfName || ': '; END WHILE; CLOSE cur_profs;
Here you declare the cursor and the continue handler. You then open the cursor and continue fetching rows into it and adding the professor names to a string variable that you use to build the output message until no more rows are found. At this point, the SQL state 02000 is signaled, the continue handler runs and sets the eof flag to 1, and the WHILE loop terminates.
The inner loop uses an implicit cursor and looks like this:
FOR cur_students AS SELECT DISTINCT Name FROM Student s
INNER JOIN Enrollment e
ON s.StudentID = e.StudentID
INNER JOIN Class c
ON e.ClassID = c.ClassID
WHERE c.ProfessorID = ProfID
DO
SET tmp_msg = tmp_msg || Name || ', ';
END FOR;
This simply loops through each row in the Student table and adds the Name for any students taught by the current professor to your tmp_msg variable.
You can run the procedure in Command Center using this statement:
CALL GetClassAttendees(?);
The output from this is as follows:
Value of output parameters -------------------------- Parameter Name : ALLCLASSATTENDEES Parameter Value : Prof. Dawson: Anna Wolff, John Jones, Julia Picard, Prof. Williams: Bruce Lee ... Return Status = 0
|
|
|