Declares a SQL variable or an exception within a compound statement (BEGIN...END).
DECLARE variable-name [, ... ] data-type [ { = | DEFAULT } initial-value ]
initial-value : special-value | string | [ - ] number | ( constant-expression ) | built-in-function ( constant-expression ) | NULL
special-value : CURRENT { DATABASE | DATE | PUBLISHER | TIME | TIMESTAMP | USER | UTC TIMESTAMP } | USER
DECLARE exception-name EXCEPTION FOR SQLSTATE [ VALUE ] string
DECLARE variable-name: Variables used in the body of a procedure, trigger, or batch can be declared using the DECLARE statement using Syntax 1. The variable persists for the duration of the compound statement in which it is declared. If you specify initial-value, the variable is set to that value. If you do not specify an initial-value, the variable contains the NULL value until a different value is assigned by the SET statement.
The body of a Watcom SQL procedure or trigger is a compound statement, and variables must be declared with other declarations, such as a cursor declaration (DECLARE CURSOR), immediately following the BEGIN keyword. In a Transact-SQL procedure or trigger, there is no such restriction.
If you specify initial-value, the data type must match the type defined by data-type.
DECLARE exception-name EXCEPTION: Use this syntax to declare variables for SQL language exceptions within a compound statement (BEGIN...END). The variables can be used, for example, for comparison with the SQLSTATEs obtained during execution, with the SIGNAL statement, or as part of the exception case within the exception handler.
SQL/2008 Syntax 1 (declaring variables) - Persistent Stored Module feature. Syntax 2 (declaring exceptions) - The form of exception declaration supported by SQL Anywhere, namely the DECLARE EXCEPTION statement, is a vendor extension; in SQL/2008, exceptions are specified using a handler declaration using the keywords DECLARE HANDLER. The DECLARE...EXCEPTION syntax is not allowed in T-SQL procedures.
Transact-SQL Syntax 2 (declaring exceptions) cannot be used in Transact-SQL compound statements and procedures.
The following batch illustrates the use of the DECLARE statement and prints a message in the database server messages window:
BEGIN DECLARE varname CHAR(61); SET varname = 'Test name'; MESSAGE varname; END |
This example declares the following variables:
BEGIN DECLARE v1 INT = 5; DECLARE v2, v3 CHAR(10) = 'abc'; // ... END |
The following procedure declares an exception for use with the SQLSTATE comparison:
CREATE PROCEDURE HighSales (IN cutoff INT, OUT HighValues INT) BEGIN DECLARE err_notfound EXCEPTION FOR SQLSTATE '02000'; DECLARE curThisCust CURSOR FOR SELECT CAST( sum( SalesOrderItems.Quantity * Products.UnitPrice ) AS INTEGER) VALUE FROM Customers LEFT OUTER JOIN SalesOrders LEFT OUTER JOIN SalesOrderItems LEFT OUTER JOIN Products GROUP BY CompanyName; DECLARE ThisValue INT; SET HighValues = 0; OPEN curThisCust; CustomerLoop: LOOP FETCH NEXT curThisCust INTO ThisValue; IF SQLSTATE = err_notfound THEN LEAVE CustomerLoop; END IF; IF ThisValue > cutoff THEN SET HighValues = HighValues + ThisValue; END IF; END LOOP CustomerLoop; CLOSE curThisCust; END; |
The following compound statement declares an exception for use with SIGNAL and an exception handler:
BEGIN DECLARE err_div_by_0 EXCEPTION FOR SQLSTATE '22012'; DECLARE curQuantity CURSOR FOR SELECT Quantity FROM SalesOrderItems WHERE ProductID = 300; DECLARE Quantities INT; DECLARE altogether INT; SET Quantities = 0; SET altogether = 0; OPEN curQuantity; LOOP FETCH NEXT curQuantity INTO Quantities; IF SQLSTATE = '02000' THEN SIGNAL err_div_by_0; END IF; SET altogether = altogether + Quantities; END LOOP; EXCEPTION WHEN err_div_by_0 THEN CLOSE curQuantity; SELECT altogether; return; WHEN OTHERS THEN RESIGNAL; END; |
Discuss this page in DocCommentXchange.
|
Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1 |