PRODUCT: R:BASE                  VERSION: 4.5+ or Higher
     CATALOG: Programming in R:BASE   AREA   :  Logic & Data Manipulation
     When should I use a cursor?
     Use a cursor when it seems like the best way to get a task done. There 
     are no rules or standards to say when you should use a cursor and when 
     you shouldn't. Often the logic behind a cursor is easier to understand 
     than the logic behind a complex SELECT or UPDATE command that works 
     across a group of rows. Many programmers have replaced DECLARE CURSOR
     routines with a single INSERT, UPDATE or DELETE command, most often for 
     performance reasons, but not all cursors can be replaced with a single 
     SQL command.
     Deciding to use a cursor will depend on your level of programming 
     expertise and understanding of the task to be accomplished. First get 
     the program to work; once it works, look at ways to make the program 
     run more efficiently and faster.
     How do I make a cursor faster?
     Using a DECLARE CURSOR is slower than using just a single SQL command 
     working across a group of rows, but some tasks just can't be done 
     without using a cursor. You can use certain techniques to maximize the 
     performance of DECLARE CURSOR routines. However, just like deciding 
     when to use a cursor, there are no rules or standards about improving 
     the performance of a cursor.
     One of the best ways to make a cursor faster is to move as much of the 
     work as possible into the DECLARE CURSOR command itself. Let the cursor 
     select as many columns as possible. If you are doing calculations for 
     each row, see if you can use one of the SELECT functions with the GROUP 
     BY option.
     For additional suggestions to improve cursor performance, see the 
     article "Optimizing Cursors" in this Exchange.
     Should I use WHERE CURRENT OF or an explicit WHERE clause? 
     In terms of performance, there is very little difference between the 
     two options. Not all cursors can be used with the WHERE CURRENT OF 
     syntax. Getting the most out of your DECLARE CURSOR statement is 
     more important in terms of performance than making your cursor an 
     updatable cursor.
     I'm trying to UPDATE data using WHERE CURRENT OF and I get a syntax 
     error. I have checked and double checked the syntax, and it is fine.
     You get this error when you have a non-updatable cursor. A 
     non-updatable cursor does not support use of WHERE CURRENT OF. Use an 
     explicit WHERE clause to update the table instead of WHERE CURRENT OF.
     What is a non-updatable cursor?
     A cursor knows what data to retrieve based on the SELECT statement 
     that is part of the DECLARE CURSOR command. Like a regular SELECT 
     command, the SELECT that is part of the DECLARE CURSOR can retrieve 
     data from multiple tables or use a GROUP BY. It has all the features 
     of the regular SELECT. However, only a single table SELECT with no 
     GROUP BY is updatable; this option is the only one that guarantees the
     cursor is pointing to a single row in a table. If the cursor can't 
     point back to and identify a single row, it doesn't know what to 
     Is it faster to retrieve data inside my WHILE loop using the SET VAR 
     command or the SELECT...INTO command? It's just a little bit faster 
     to retrieve additional data using a SET VAR command instead of the 
     SELECT...INTO command. The SELECT has more overhead. The fastest way 
     to retrieve column data into variables, however, is to retrieve
     whatever columns possible through the DECLARE CURSOR command. That 
     method can be almost twice as fast as using either SET VAR or 
     My WHILE loop never ends. It just keeps repeating the last row. FETCH, 
     which sets SQLCODE, should be the last command in the WHILE loop. 
     When no more data is available, SQLCODE is set to 100. If FETCH is the 
     last command in the WHILE loop, the next command executed is the WHILE 
     statement, which tests the current value of SQLCODE. Other SQL 
     commands placed after the FETCH and before the ENDWHILE might reset 
     SQLCODE to a value other than 100.
     Also, if your WHILE condition is not SQLCODE <> 100, make sure you are 
     checking the condition correctly. If the WHILE loop doesn't exit, the 
     WHILE condition is never false. Use TRACE and set up watch variables 
     to see what is happening with your variable values.
     Why won't WHENEVER work with DECLARE CURSOR? WHENEVER is an SQL error 
     trap command that executes a GOTO whenever the data not found 
     situation (SQLCODE = 100) occurs. At first glance, WHENEVER seems 
     ideal for use with a DECLARE CURSOR. However, if your DECLARE CURSOR 
     routine uses any other SQL commands that can return a "data not found"
     error, such as SELECT, INSERT or UPDATE, the WHENEVER immediately 
     exits the DECLARE CURSOR WHILE loop even though all the data has not 
     been processed. The R:BASE error "No rows exist or satisfy the WHERE 
     clause" is a "data not found" error and sets SQLCODE to 100.
     I use DECLARE CURSOR to find out if a row exists in a table. Is there 
     a way to do this check faster? If you only want to see if a row exists 
     in a table, don't use DECLARE CURSOR. The DECLARE CURSOR command by 
     itself doesn't check this. You need to OPEN the cursor and FETCH
     before you know if a row has been found. Instead use the SELECT 
     command; SELECT INTO a variable and test the variable value, or test 
     SQLCODE immediately after the SELECT command. If no row is found, 
     SQLCODE is set to 100. Using just the SELECT command is much faster 
     than using the DECLARE CURSOR.
     My DECLARE CURSOR command is giving me a syntax error. Is there an 
     easy way to check the syntax? First make sure the cursor name is in 
     the correct place in the command. A common error is to use DECLARE 
     CURSOR c1 instead of DECLARE c1 CURSOR. The SELECT part of the 
     DECLARE CURSOR command can get quite complex, particularly when more
     than one table is involved. Test the SELECT part of the DECLARE 
     CURSOR command at the R> prompt, which executes just like a regular 
     SELECT command. You can test and debug the SELECT part of your 
     DECLARE CURSOR before putting it into the DECLARE CURSOR structure.