DECLARE - Caché SQL Reference - Caché & Ensemble 2018.1

docs.intersystems.com

Caché SQL Reference

DECLARE

[Home] [Back] [Next]

InterSystems: The power behind what matters

Caché Development References > Caché SQL Reference > SQL Commands > DECLARE

Search:

Declares a cursor.

Synopsis

DECLARE cursor-name CURSOR FOR query

Arguments

cursor-name	The name of the cursor, which must begin with a letter and contain only letters and numbers. (Cursor names do not follow SQL identifier conventions). Cursor names are case-sensitive. They are subject to additional naming restrictions, as described below.
query	A standard SELECT statement that defines the result set of the cursor. This SELECT can include the %NOFPLAN keyword to specify that Caché should ignore the frozen plan (if any) for this query. This SELECT can include an ORDER BY clause, with or without a TOP clause. This SELECT can specify a table-valued function in the FROM clause.

Description

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.

DECLARE does not support the #SQLCompile Mode=Deferred preprocessor directive. Attempting to use Deferred mode with a DECLARE, OPEN, FETCH, or CLOSE cursor statement generates a #5663 compilation error.

Cursor Names

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.

Examples

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

The following Embedded SQL example uses a table-valued function as the FROM clause of the query:

    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)