User's Guide

Part VI. SQL Anywhere Reference

Chapter 43. Watcom-SQL Statements

Function

To specify a statement that groups other statements together.

Syntax

     [ statement-label : ]
          ... BEGIN [ [ NOT ] ATOMIC ]
          ...    [ local-declaration ; ... ]
          ...    statement-list
          ...    [ EXCEPTION [ exception-case ... ] ]
          ... END [ statement-label ]

Parameters

     local-declaration:
          variable-declaration
          | cursor-declaration
          | exception-declaration
          | temporary-table-declaration

     variable-declaration:
          DECLARE variable-name data-type

     exception-declaration:
          DECLARE exception-name EXCEPTION
          FOR SQLSTATE [ VALUE ] string

     exception-case:
          WHEN exception-name [ ,... ] THEN statement-list
          | WHEN OTHERS THEN statement-list

Usage

Procedures, triggers, and batches.

Permissions

None.

Side effects

None.

See also

• DECLARE CURSOR statement
• DECLARE TEMPORARY TABLE statement
• LEAVE statement
• SIGNAL statement
• RESIGNAL statement
• The chapter "Using Procedures, Triggers, and Batches"

Description

The body of a procedure or trigger is a compound statement. Compound statements can also be used in control statements within a procedure or trigger.

A compound statement allows one or more SQL statements to be grouped together and treated as a unit. A compound statement starts with the keyword BEGIN and ends with the keyword END. Immediately following the BEGIN, a compound statement can have local declarations that only exist within the compound statement. A compound statement can have a local declaration for a variable, a cursor, a temporary table, or an exception. Local declarations can be referenced by any statement in that compound statement, or in any compound statement nested within it. Local declarations are not visible to other procedures that are called from within a compound statement.

If the ending statement-label is specified, it must match the beginning statement-label. The LEAVE statement can be used to resume execution at the first statement after the compound statement. The compound statement that is the body of a procedure or triggers has an implicit label that is the same as the name of the procedure or trigger.

  For a complete description of compound statements and exception handling, see the chapter "Using Procedures, Triggers, and Batches".

Example

The body of a procedure or trigger is a compound statement.

     CREATE PROCEDURE TopCustomer (OUT TopCompany CHAR(35), OUT TopValue INT)
     BEGIN
         DECLARE err_notfound EXCEPTION FOR
             SQLSTATE '02000' ;
         DECLARE curThisCust CURSOR FOR
             SELECT company_name, CAST(
                     sum(sales_order_items.quantity *
                     product.unit_price) AS INTEGER) VALUE
             FROM customer
                     LEFT OUTER JOIN sales_order
                     LEFT OUTER JOIN sales_order_items
                     LEFT OUTER JOIN product
             GROUP BY company_name ;
         DECLARE ThisValue INT ;
         DECLARE ThisCompany CHAR(35) ;
         SET TopValue = 0 ;
         OPEN curThisCust ;
     
         CustomerLoop:
         LOOP
             FETCH NEXT curThisCust
                 INTO ThisCompany, ThisValue ;
             IF SQLSTATE = err_notfound THEN
                 LEAVE CustomerLoop ;
             END IF ;
             IF ThisValue > TopValue THEN
                 SET TopValue = ThisValue ;
                 SET TopCompany = ThisCompany ;
             END IF ;
         END LOOP CustomerLoop ;
     
     CLOSE curThisCust ;
     END