How to Open a SQL Cursor

Although the DECLARE CURSOR SQL statement specifies which rows to include in the cursor, it doesn’t actually cause anything to happen because DECLARE is just a declaration and not an executable statement. The OPEN statement brings the cursor into existence. It has the following form:

OPEN cursor-name ;

To open the cursor in the ORDER BY clause, use the following:

DECLARE revenue CURSOR FOR
 SELECT Model, Units, Price,
   Units * Price AS ExtPrice
  FROM TRANSDETAIL
 ORDER BY Model, ExtPrice DESC ;
OPEN revenue ;

You can’t fetch rows from a cursor until you open the cursor. When you open a cursor, the values of variables referenced in the DECLARE CURSOR statement become fixed, as do all current date-time functions. Consider the following example of SQL embedded in a host language program:

EXEC SQL DECLARE C1 CURSOR FOR SELECT * FROM ORDERS
 WHERE ORDERS.Customer = :NAME
  AND DueDate < CURRENT_DATE ;
NAME :='Acme Co'; //A host language statement
EXEC SQL OPEN C1;
NAME :='Omega Inc.'; //Another host statement
...
EXEC SQL UPDATE ORDERS SET DueDate = CURRENT_DATE;

The OPEN statement fixes the value of all variables referenced in the declare cursor and also fixes a value for all current date-time functions. As a result, the second assignment to the name variable (NAME := ‘Omega Inc.’) has no effect on the rows that the cursor fetches. (That value of NAME is used the next time you open C1.)

And even if the OPEN statement is executed a minute before midnight and the UPDATE statement is executed a minute after midnight, the value of CURRENT_DATE in the UPDATE statement is the value of that function at the time the OPEN statement executed — even if DECLARE CURSOR doesn’t reference the date-time function.

  • Add a Comment
  • Print
  • Share
blog comments powered by Disqus
Advertisement

Inside Dummies.com