Sybase 12.4.2 Manuale Utente

CHAPTER 6    Using Procedures and Batches

A command delimiter is required after every statement in a statement list 
except for the last, where it is optional. 

Local declarations in a compound statement immediately follow the BEGIN 
keyword. These local declarations exist only within the compound statement. 
The following may be declared within a compound statement:

•

Variables

•

Cursors

•

Temporary tables

•

Exceptions (error identifiers)

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 called from the compound statement.

The following user-defined function illustrates local declarations of variables.

The 

customer

 table includes some Canadian customers sprinkled among those 

from the USA, but there is no 

country

 column. The user-defined function 

nationality

 uses the fact that the US zip code is numeric while the Canadian 

postal code begins with a letter to distinguish Canadian and US customers.

CREATE FUNCTION nationality( cust_id INT )

RETURNS CHAR( 20 )

BEGIN

DECLARE natl CHAR(20);

IF cust_id IN ( SELECT id FROM customer

WHERE LEFT(zip,1) > ’9’) THEN

SET natl = ’CDN’;

ELSE

SET natl = ’USA’;

END IF;

RETURN ( natl );

END

This example declares a variable 

natl

 to hold the nationality string, uses a 

SET 

statement to set a value for the variable, and returns the value of the 

natl

 string 

to the calling environment.

The following query lists all Canadian customers in the 

customer

 table:

SELECT *