SQL PL Conditional Structures

 Recently I gave a brief introduction to the SQL Procedures Language, or SQL PL, a procedural language that works with the DB2 family of database management systems. Today I present the conditional structures, IF and CASE. If you so choose, you will master them in minutes.

But first, I need to clear up one matter. In my previous article, I referred to SQL PL as a proprietary language. Mike Cain, of the DB2 for i Center of Excellence, emailed to correct my error. Mike pointed out that SQL PL is based on SQL/Persistent Stored Modules (SQL/PSM), which is an ISO standard. Therefore, SQL PL is not proprietary, but a standard language that other DBMS vendors have so far not chosen to implement. There is a difference, and Mike is correct.

The bottom line, however, remains the same: SQL PL runs on DB2 only.

Having corrected my poor choice of words, I turn to the matter at hand–making decisions.

SQL PL has two decision-making structures, and they are very easily learned.

IF

The IF structure has three forms:

* IF — THEN — END IF
* IF — THEN — ELSE — END IF
* IF — THEN — ELSEIF — THEN — ELSE — END IF

I won’t explain the logic, since they work identically to their RPG counterparts. However, the placement of semicolons is different, so let’s look at that.

* Do not place semicolons after THEN and ELSE.
* Place one statement after THEN and ELSE. That statement may be a simple statement or a compound statement.
* Terminate each simple statement, including simple statements within compound statements, with a semicolon.
* Terminate END IF with a semicolon only when something follows it.

Let’s look at a couple of examples.

This trigger fires before an item is added to the item master table. If the stocking unit of measure is kilograms, the trigger ensures that the weight is either positive or null.

create trigger ItemInsert
   no cascade
   before insert on items
   referencing new row as n
   for each row
   mode db2row
if n.Stocking_UOM = 'KG' and n.weight <= 0 then
   signal sqlstate '86100'
      set Message_text = 'Weight must be positive or null';
end if

If I try to insert an item with a weight of -4 kilograms, the system prohibits the insertion and returns SQL state 86100. Notice that there is no semicolon after the END IF, since nothing follows.

Here is a beefier version of the previous example, showing nested IF and the ELSE statement.

create trigger iteminsert
   no cascade
   before insert on items
   referencing new row as n
   for each row
   mode db2row
if Stocking_UOM = 'KG' then
   if n.weight <= 0 then
      signal sqlstate '86100'
         set Message_text = 'Weight must be positive or null';
   end if;
else
   signal sqlstate '86199'
      set Message_text = 'Invalid unit of measure';
end if

CASE

The CASE structure, like the CASE expression that you probably use in SELECT statements, has two forms–a simple form and a searched form:

* CASE value WHEN — ELSE — END CASE
* CASE WHEN — ELSE — END CASE

If you’re not familiar with simple and searched CASE, I recommend that you read this article by Skip Machesani. It deals with the CASE expression, but the explanation of simple and searched CASE applies to the CASE structure as well.

Be aware that the SQL PL CASE structure and the CASE expression differ in at least three ways:

* The CASE expression returns a value, as if it were a function. The SQL PL CASE is a control structure.
* SQL PL does not require ELSE, but if no WHEN matches and there is no ELSE, the CASE fails with SQLSTATE 20000. The CASE expression returns a null when no WHEN matches and there is no ELSE.
* You must use END CASE, not END, to terminate the SQL PL CASE. You can terminate the CASE expression with either END or END CASE, but if you add a correlation name, as I usually do, you have to terminate the CASE expression with END.

Here’s the same trigger, beefed up a bit more than before.

create trigger ItemInsert
   no cascade
   before insert on items
   referencing new row as n
   for each row
   mode db2row
case
   when Stocking_UOM = 'KG' then
      if n.weight <= 0 then
         signal sqlstate '86100'
            set Message_text = 'Weight must be positive or null';
      end if;
   when Stocking_UOM in ('CM', 'M') then
      if n.length <= 0 then
         signal sqlstate '86100'
            set Message_text = 'Length must be positive or null';
      end if;
   else
      signal sqlstate '86199'
         set Message_text = 'Invalid unit of measure';
end case

IF and CASE are not difficult to learn. I can’t think of any way the designers of SQL PL could have made them any easier.

Ted Holt welcomes your comments and questions. Email him through the IT Jungle Contacts page.

RELATED STORIES

A Brief Introduction to the SQL Procedures Language

A Case for CASE

SQL Procedures, Triggers, and Functions on IBM DB2 for i