Writing Code

 Top  Previous  Next

Each scripting code block has the same structure. There are required and optional parts.

 

Separate statements are terminated by the semicolon characters ";".

 

Syntax

The definition of the structure of a scripting block is as follows:

 

EXECUTE UDSBLOCK [block_name] [(<input_parameter_clause>)]

[RETURNS (<output_parameter_clause>)]

AS

[<declare_clause>]

BEGIN

  [<compound_statement>]

  [<when_do>]

END

 

<input_parameter_clause> ::= <input_parameter_spec> [, <input_parameter_spec>]

 

<input_parameter_spec> ::= parameter_name <data_type> [NOT NULL] [{DEFAULT | = } value]

 

<output_parameter_clause> ::= <output_parameter_spec> [, <output_parameter_spec>]

 

<output_parameter_spec> ::= parameter_name <data_type>

 

<declare_clause> ::= { <declare_variable> | <declare_cursor> | <declare_subroutine> } [<declare_clause>]

 

<declare_subroutine> ::== { <declare_function> | <declare_procedure> }

 

<compound_statement> ::= { <block> | <statement> } [{ <block> | <statement> }]

 

Declare Clause

The <declare_clause> is optional and allows for 3 different types of declaration:

1.DECLARE VARIABLE
to declare additional strong typed local variables accessible in this routine.
2.DECLARE FUNCTION | DECLARE PROCEDURE
to declare local subroutines that can be used in the code block that follows
3.DECLARE CURSOR
to declare a cursor that can be used to loop while etch data

 

The clause can include multiple entries of these declarations.

 

Scripting Code Block Body

The <compound_statement> block consists of one or more statements. This can include additional BEGIN ... END blocks, structural or data manipulations statements, and exception handling statements.

 

Some of these statements are forwarded directly to the database system, like SELECT, and cannot use functions from the library.

 

General and Structural Statements

The following statements are available with regard to structure, looping and code decision making.

 

•BEGIN ... END - structural block with its own exception handling or set of statements
•IF ... THEN ... ELSE - code branching depending on a condition
•WHILE ... DO - loop code based on a condition
•REPEAT ... UNTIL - loop code based on a condition
•FOR ... DO - loop code for a certain amount of times
•FOREACH - loop code for each item in an array
•LEAVE - exit the current loop
•CONTINUE - continuing with the next iteration of a loop
•EXIT - terminate the current block
•SUSPEND - pass values to the output parameters and wait for the caller to fetch these values
•RETURN - return a value from a stored function
•FOR EXECUTE STATEMENT - loop over a result set for a dynamic SQL statement
•FOR SELECT - loop over a result set for an SQL statement

 

Data Manipulation Statements

Data can be retrieved from the database and modified if required. You can loop over data or get single row values.

 

•SELECT - to retrieve data from tables and views
•INSERT - to insert data into tables
•UPDATE - to modify existing data in tables
•UPDATE OR INSERT - to update existing data or insert it if it doesn't exist yet
•DELETE - to remove data from tables
•EXECUTE PROCEDURE - to call another local subroutine or a database stored procedure
•EXECUTE STATEMENT - to execute a dynamically created statement

 

Exception Handling

Script behaviour can be different depending on the outcome of statements. You can use exception handling statements to execute code after specific exceptions, or make sure code is executed always, despite of exceptions.

 

•TRY ... EXCEPT - execute code after any exception occurred
•TRY ... FINALLY - make sure some pieces of codes are execute despite of exceptions occurring
•WHEN ... DO - execute code when any, one or more exceptions occur