Inline SQL functions, triggers, and compound SQL statements

Inline SQL PL statements can be executed in compound SQL (compiled) statements, compound SQL (inlined) statements, SQL functions, and triggers.

A compound SQL (inlined) statements is one that allows you to group multiple SQL statements into an optionally atomic block in which you can declare variables, and condition handling elements. These statements are compiled by Db2® as a single SQL statement and can contain inline SQL PL statements.

The bodies of SQL functions and triggers can contain compound SQL (inlined) statements and can also include some inline SQL PL statements.

On their own, compound SQL (inlined) statements are useful for creating short scripts that perform small units of logical work with minimal control flow, but that have significant data flow. Within functions and triggers, they allow for more complex logic to be executed when those objects are used.

As an example of a compound SQL (inlined) statement that contains SQL PL, consider the following:

   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 SQL statement is bounded by the keywords BEGIN and END. For an inline compound statement, the keyword ATOMIC follows the keyword BEGIN. A compound SQL 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.

As an example of a trigger that contains SQL PL, consider the following:

  CREATE TRIGGER validate_sched
  NO CASCADE BEFORE INSERT ON c1_sched
  FOR EACH ROW
  MODE DB2SQL
  Vs: BEGIN ATOMIC

    IF (n.ending IS NULL) THEN
      SET n.ending = n.starting + 1 HOUR;
    END IF;

    IF (n.ending > '21:00') THEN
      SIGNAL SQLSTATE '80000'  SET MESSAGE_TEXT = 
             'Class ending time is after 9 PM';
    ELSE IF (n.DAY=1 or n.DAY-7) THEN
      SIGNAL SQLSTATE '80001' SET MESSAGE_TEXT = 
             'Class cannot be scheduled on a weekend';
    END IF;
  END vs;

This trigger is activated upon an insert to a table named c1_sched and uses SQL PL to check for and provide a class end time if one has not been provided and to raise an error if the class end time is after 9 pm or if the class is scheduled on a weekend. As an example of a scalar SQL function that contains SQL PL, consider the following:


  CREATE FUNCTION GetPrice (Vendor CHAR(20), Pid INT)  
  RETURNS  DECIMAL(10,3) 
  LANGUAGE SQL 
  MODIFIES SQL DATA
  BEGIN ATOMIC 
    DECLARE price DECIMAL(10,3); 

    IF Vendor = 'Vendor 1' 
      THEN SET price = (SELECT ProdPrice FROM V1Table WHERE Id = Pid); 
    ELSE IF Vendor = 'Vendor 2' 
      THEN SET price = (SELECT Price FROM V2Table WHERE Pid = GetPrice.Pid); 
    END IF; 

    RETURN price; 
  END

This simple function returns a scalar price value, based on the value of an input parameter that identifies a vendor. It also uses the IF statement.

For more complex logic that requires output parameters, the passing of result sets or other more advanced procedural elements SQL procedures might be more appropriate.