782.txt
=====================================================================
CURSOR QUESTIONS AND ANSWERS
=====================================================================
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
update.
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
SELECT...INTO.
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.