Progress
SQL-89
Guide and Reference
Defining a Cursor
The DECLARE CURSOR statement has the following syntax.
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.
Table 3–5 shows the Progress record locks and explains their effects.
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.
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.
NOTE
- According to the SQL standard, a cursor is not updatable if an ORDER BY clause appears in the DECLARE CURSOR statement. Progress/SQL does not enforce this restriction. However, updatable cursors must obey the same restrictions as updatable views. For a list of these restrictions, see the section on updating views in Data Definition Language."
Copyright © 2004 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095 |