Progress
SQL-89
Guide and Reference


Defining a Cursor

The DECLARE CURSOR statement has the following syntax.

SYNTAX
DECLARE cursor-name CURSOR FOR 
  { SELECT-statement | UNION-statement }
  [ FOR { READ ONLY | UPDATE } ] 

The cursor-name can be any valid SQL identifier. The SELECT-statement is any valid SQL SELECT statement, including WHERE, GROUP BY, HAVING, and ORDER BY clauses, as well as subqueries, aggregates, joins, and all other valid SELECT statement syntax. The SELECT-statement specifies a retrieval set of rows that is accessible when the cursor is opened.

NOTE

FOR READ ONLY allows you to read the selected rows but not update or delete them. FOR READ ONLY is performed with NO-LOCK to prevent lock table overflow. To coordinate concurrent access to records in multi-user applications, SQL statements use a default record- locking scheme based on the Progress record-locking phrases. You do not insert Progress record-locking phrases directly into ESQL programs. Progress applies the lock types shown in Table 3–4 implicitly.

Table 3–4: SQL Statements and Associated Record Locks
Embedded SQL Statement
Associated Record Lock
Non-cursor SELECT statements
(SELECT INTO)
NO-LOCK only. You can see any uncommitted changes to the database.
OPEN cursor statements1
SHARE-LOCK by default. Records retrieved through a cursor use SHARE-LOCK while being read and are converted to EXCLUSIVE-LOCK if updated or deleted.
To read records with the NO-LOCK option, specify FOR READ ONLY in the associated DECLARE CURSOR statement.
Searched UPDATE statements
(UPDATE WHERE condition)
Positioned UPDATE statements
(UPDATE WHERE CURRENT)
Searched DELETE statements
(DELETE WHERE condition)
Positioned DELETE statements
(DELETE WHERE CURRENT)
EXCLUSIVE-LOCK only.
  1. Cursor SELECT statements do not obtain locks until the cursor is opened.

Table 3–5 shows the Progress record locks and explains their effects.

Table 3–5: Progress Record-locking Phrases  
Lock Type
Effect
NO-LOCK
Rows are not locked. A row is read even if another application holds an EXCLUSIVE-LOCK on it. You cannot update rows read with NO-LOCK.
SHARE-LOCK
Rows are read in anticipation of a possible update. A row is not read if another application holds an EXCLUSIVE-LOCK on it.
EXCLUSIVE-LOCK
Rows are locked until the end of the transaction. Another application cannot read rows using SHARE-LOCK or EXCLUSIVE-LOCK.

For more information on Progress record locking, see the Progress Programming Handbook. To guarantee serializable SQL transactions in ESQL, you can specify the ANSI SQL Client (-Q2) startup parameter. If you use this parameter, Progress/SQL conforms to the ANSI rules.

To define a cursor, you must have the SELECT privilege on all tables referred to in the DECLARE CURSOR statement (see the section on access privileges in "Data Definition Language," for more information). The DECLARE CURSOR statement associates the cursor with the SELECT statement and assigns a Progress correlation name with the same name as cursor-name. Later, Progress/SQL fetches rows into the correlation table. If the cursor definition involves a join, Progress/SQL defines a correlation name for each table in the join. The first correlation name is called cursor-name. The other correlation names start with cursor-name followed by a hyphen and a letter, such as cursor-name-a and cursor-name-b.

You must use the DECLARE CURSOR statement in your Progress/SQL procedure before you open or refer to the cursor. You declare a cursor only once, even if you intend to close and reopen it several times in a procedure.

In the following example, the DECLARE CURSOR statement uses a cursor named c1 to retrieve the names and customer numbers for all Massachusetts customers.

DECLARE c1 CURSOR FOR 
  SELECT Name, Cust-Num 
    FROM Customer
    WHERE State = ’MA’. 

The SELECT statement can refer to a procedure variable in its WHERE clause. The variable is evaluated when the cursor is opened, not when it is declared. The following example illustrates how a procedure variable is used in the WHERE clause.

DEFINE VARIABLE state-var AS CHARACTER INITIAL "MA".

DECLARE c1 CURSOR FOR 
    SELECT Name, Address, City, State 
      FROM Customer
      WHERE State = state-var ORDER BY State, City. 

NOTE


Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095