Creating compound statements

Creating and executing compound statements is a task that you would perform when you need to run a script consisting of SQL statements.

Before you begin

Before you create a compound statement:
  • Read: Compound statements
  • Ensure that you have the privileges required to execute the Compound statement.

Procedure

  1. Define a compound SQL statement.
  2. Execute the compound SQL statement from a supported interface.

Results

If executed dynamically, the SQL statement should execute successfully.

Example

The following is an example of an inlined compound SQL statement that contains SQL PL: 
 BEGIN ATOMIC      
  FOR row AS        
    SELECT pk, c1, discretize(c1) AS v FROM source      
  DO
    IF row.v is NULL THEN          
      INSERT INTO except VALUES(row.pk, row.c1);        
    ELSE          
      INSERT INTO target VALUES(row.pk, row.d); 
    END IF;      
  END FOR;    
 END
The compound statement is bounded by the keywords BEGIN and END. For an inline compound statement, the keyword ATOMIC follows the keyword BEGIN. A compound statement includes use of both the FOR and IF/ELSE control-statements that are part of SQL PL. The FOR statement is used to iterate through a defined set of rows. For each row a column's value is checked and conditionally, based on the value, a set of values is inserted into another table.