Chapter 26 SQL command DECLARE

Chapter 26 SQL command DECLARE

declare cursor

outline

DECLARE cursor-name CURSOR FOR query

parameter

  • Cursor name - the name of the cursor, which must start with a letter and contain only letters and numbers. (the cursor name does not follow the SQL identifier Convention). Cursor names are case sensitive. They are subject to other naming restrictions, as described below.
  • Query - a standard SELECT statement that defines the cursor result set. This selection can include the% NOFPLAN keyword to specify the freezing plan, if any, that this query should be ignored. This SELECT can include an ORDER BY clause, with or without a TOP clause. This SELECT specifies a table valued function in the FROM clause.

describe

DECLARE StatementDeclares cursors used in cursor based embedded SQL. After declaring the cursor, you can issue an OPEN statement to OPEN the cursor, and then issue a series of FETCH statements to retrieve each record. The cursor defines a SELECT query that selects the records to be retrieved by these FETCH statements. You can issue a CLOSE statement to CLOSE (but not delete) the cursor.

As an SQL statement, DECLARE is supported only for embedded SQL. For dynamic SQL, you can use a simple SELECT statement (without an INTO clause) or a combination of dynamic SQL and embedded SQL. Use ODBC API to support equivalent operations through ODBC.

DECLARE declares a forward only (non scrollable) cursor. The extraction operation starts from the first record in the query result set and traverses the result set records in order. Only one record can be extracted at a time. The next extraction will get the next continuous record in the result set.

Because DECLARE is a declaration, not an executed statement, it does not set or terminate the SQLCODE variable.

Cursor name

Cursor names are case sensitive.

Cursor names must be unique in routines and corresponding classes. The cursor name can be any length, but must be unique within the first 29 characters. Cursor names are case sensitive. If the specified cursor has been declared, the compilation will fail with SQLCODE-52 error, and the cursor name has been declared.

Cursor names are not namespace specific. You can declare a cursor in one namespace and open, get, or close the cursor in another namespace. Compile embedded SQL when executing the open command. SQL tables and local variables are namespaces, so you must invoke OPEN operations in the same namespace specified in the query, or you can access tables in namespaces.

The first character of the cursor name must be a letter. The second and subsequent characters of the cursor name must be letters or numbers. Unlike SQL identifiers, punctuation is not allowed in cursor names.

SQL reserved words can be specified as cursor names using separator characters (double quotes). Delimited cursor name is not a SQL delimited identifier; Delimited cursor names are still case sensitive and cannot contain punctuation. In most cases, SQL reserved words should not be used as cursor names.

Update by cursor

You can use UPDATE or DELETE statements with a WHERE CURRENT OF clause to UPDATE and DELETE records through declared cursors. In SQL, cursors can always be used for UPDATE or DELETE operations if they have appropriate permissions on the affected tables and columns.

DECLARE statementyou can specify the FOR UPDATE or FOR READ ONLY keyword clause after a query. These clauses are optional and do nothing. They are provided as a way to record in code whether the process issuing the query has the required update and delete object permissions.

Example

The following embedded SQL example uses DECLARE to define cursors for queries that specify two output host variables. Then, the cursor is opened, repeatedly read and closed:

ClassMethod Declare()
{
	s 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
	)
	w !,"BEFORE: Name = ",name," State = ",state 
	&sql(
		OPEN EmpCursor
	)
	if SQLCODE < 0 {
		w "SQL Open cursor error:",SQLCODE," ",%msg  
		q
	}
	n %ROWCOUNT,%ROWID
	for { 
		&sql(
			FETCH EmpCursor
		)
		q:SQLCODE  
		w !,"DURING: Name = ",name," State = ",state 
	}
	w !,"Get status SQLCODE = ",SQLCODE
	w !,"Number of rows read = ",%ROWCOUNT
	&sql(
		CLOSE EmpCursor
	)
	if SQLCODE < 0 {
		w "SQL Close cursor error:",SQLCODE," ",%msg  
		q
	} 
	w !,"AFTER: Name = ",name," State = ",state
}

The following embedded SQL example uses DECLARE to define a cursor for a query that specifies the OUTPUT host variable in the INTO clause and the INPUT host variable in the WHERE clause. Then, the cursor is opened, repeatedly read and closed:

ClassMethod Declare1()
{
	n SQLCODE,%ROWCOUNT,%ROWID
	s EmpZipLow = "10000"
	s EmpZipHigh = "19999"
	&sql(
		DECLARE EmpCursor1 CURSOR FOR
			SELECT Name,Home_Zip
			INTO :name,:zip
			FROM Sample.Employee 
			WHERE Home_Zip BETWEEN :EmpZipLow AND :EmpZipHigh)
	&sql(
		OPEN EmpCursor1
	)
	if SQLCODE < 0 {
		w "SQL Open cursor error:",SQLCODE," ",%msg  
		q
	}
	for { 
		&sql(
			FETCH EmpCursor1
		)
		q:SQLCODE  
		w !,name," ",zip 
	}
	&sql(
		CLOSE EmpCursor1
	)
	if SQLCODE < 0 {
		w "SQL Close cursor error:",SQLCODE," ",%msg  
		q
	}
}

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

ClassMethod Declare2()
{
	s $NAMESPACE="Samples"
	&sql(DECLARE EmpCursor2 CURSOR FOR 
			SELECT Name INTO :name FROM Sample.SP_Sample_By_Name('A')
			FOR READ ONLY
	)
	&sql(
		OPEN EmpCursor2
	)
	if SQLCODE < 0 {
		w "SQL Open cursor error:",SQLCODE," ",%msg  
		q
	}
	n %ROWCOUNT,%ROWID
	for { 
		&sql(
			FETCH EmpCursor2
		)
		q:SQLCODE  
		w "Name=",name,! 
	}
	w !,"Get status SQLCODE = ",SQLCODE
	w !,"Number of rows read = ",%ROWCOUNT
	&sql(
		CLOSE EmpCursor2
	)
	if SQLCODE < 0 {
		w "SQL Close cursor error:",SQLCODE," ",%msg  
		q
	}
}

Tags: Database Oracle SQL

Posted on Sun, 26 Sep 2021 06:31:11 -0400 by Chips