Caché SQL Reference
DECLARE
|
|
Declares a cursor.
Synopsis
DECLARE cursor-name CURSOR FOR query
A
DECLARE statement declares a
cursor used in
cursor-based Embedded SQL. After declaring a cursor, you issue an
OPEN statement to open the cursor and then a series of
FETCH statements to retrieve individual records. The cursor defines the
SELECT query that is used to select records for retrieval by these
FETCH statements. You issue a
CLOSE statement to close (but not delete) the cursor.
As an SQL statement,
DECLARE is only supported from Embedded SQL. For Dynamic SQL, use instead either a simple
SELECT statement (with no
INTO clause), or a combination of Dynamic SQL and Embedded SQL. Equivalent operations are supported through ODBC using the ODBC API.
DECLARE declares a forward-only (non-scrollable) cursor. Fetch operations begin with the first record in the query result set and proceed sequentially through the result set records. A
FETCH can only fetch a record once. The next
FETCH fetches the next sequential record in the result set.
Because
DECLARE is a declaration, not an executed statement, it does not set or kill the SQLCODE variable.
A cursor name must be unique within the routine and the corresponding class. A cursor name may be of any length, but must be unique within the first 29 characters. Cursor names are case-sensitive. Attempting to declare two cursors with the same name results in an error code -52 during compilation.
Cursor names are not namespace-specific. You can
DECLARE a cursor in one namespace, and
OPEN,
FETCH, or
CLOSE this cursor when in another namespace. Note that SQL tables are namespace-specific, so the
FETCH operation must be invoked in the same namespace as the table from which records are being fetched.
The first character of a cursor name must be a letter. The second and subsequent characters of a cursor name must be either a letter or a number. Unlike SQL
identifiers, punctuation characters are not permitted in cursor names.
You can use a delimiter characters (double quotes) to specify an SQL reserved word as a cursor name. A delimited cursor name is
not an SQL delimited identifier; delimited cursor names are still case-sensitive and cannot contain punctuation characters. In most cases, an SQL reserved word should not be used as a cursor name.
Updating through a Cursor
You can perform record updates and deletes through a declared cursor using an
UPDATE or
DELETE statement with the
WHERE CURRENT OF clause. In Caché SQL a cursor can always be used for
UPDATE or
DELETE operations if you have the appropriate privileges on the affected tables and columns; refer to the
GRANT statement for assigning object privileges.
A
DECLARE statement can specify a FOR UPDATE or FOR READ ONLY keyword clause following the query. These clauses are optional and perform no operation. They are provided as a way to document in the code that the process issuing the query has or does not have the needed update and delete object privileges.
The following Embedded SQL example uses
DECLARE to define a cursor for a query that specifies two output host variables. The cursor is then opened, fetched repeatedly, and closed:
SET name="John Doe",state="##"
&sql(DECLARE EmpCursor CURSOR FOR
SELECT Name, Home_State
INTO :name,:state FROM Sample.Person
WHERE Home_State %STARTSWITH 'A'
FOR READ ONLY)
WRITE !,"BEFORE: Name=",name," State=",state
&sql(OPEN EmpCursor)
QUIT:(SQLCODE'=0)
NEW %ROWCOUNT,%ROWID
FOR { &sql(FETCH EmpCursor)
QUIT:SQLCODE
WRITE !,"DURING: Name=",name," State=",state }
WRITE !,"FETCH status SQLCODE=",SQLCODE
WRITE !,"Number of rows fetched=",%ROWCOUNT
&sql(CLOSE EmpCursor)
WRITE !,"AFTER: Name=",name," State=",state
The following Embedded SQL example uses
DECLARE to define a cursor for a query that specifies both output host variables in the INTO clause and input host variables in the WHERE clause. The cursor is then opened, fetched repeatedly, and closed:
NEW SQLCODE,%ROWCOUNT,%ROWID
SET EmpZipLow="10000"
SET EmpZipHigh="19999"
&sql(DECLARE EmpCursor CURSOR FOR
SELECT Name,Home_Zip
INTO :name,:zip
FROM Sample.Employee WHERE Home_Zip BETWEEN :EmpZipLow AND :EmpZipHigh)
&sql(OPEN EmpCursor)
QUIT:(SQLCODE'=0)
FOR { &sql(FETCH EmpCursor)
QUIT:SQLCODE
WRITE !,name," ",zip }
&sql(CLOSE EmpCursor)
QUIT
ZNSPACE "Samples"
&sql(DECLARE EmpCursor CURSOR FOR
SELECT Name INTO :name FROM Sample.SP_Sample_By_Name('A')
FOR READ ONLY)
&sql(OPEN EmpCursor)
QUIT:(SQLCODE'=0)
NEW %ROWCOUNT,%ROWID
FOR { &sql(FETCH EmpCursor)
QUIT:SQLCODE
WRITE "Name=",name,! }
WRITE !,"FETCH status SQLCODE=",SQLCODE
WRITE !,"Number of rows fetched=",%ROWCOUNT
&sql(CLOSE EmpCursor)