Using an IF - ELIF - ELSE Structure

The SPL routine in Figure 408 uses an IF - ELIF - ELSE structure to compare the two arguments that the routine accepts.

CREATE FUNCTION str_compare( str1 CHAR(20), str2 CHAR(20))
   RETURNING INTEGER;

   DEFINE result INTEGER;

   IF str1 > str2 THEN
       result = 1;
   ELIF str2 > str1 THEN
      result = -1;
   ELSE
      result = 0;
   END IF
   RETURN result;
END FUNCTION;

Suppose you define a table named manager with the columns that Figure 409 shows.

CREATE TABLE manager
(
   mgr_name   VARCHAR(30),
   department VARCHAR(12),
   dept_no    SMALLINT,
   direct_reports SET( VARCHAR(30) NOT NULL ),
   projects LIST( ROW ( pro_name VARCHAR(15),
   pro_members SET( VARCHAR(20) NOT NULL ) )
                    NOT NULL),
   salary     INTEGER,
);

The SPL routine in Figure 410 uses an IF - ELIF - ELSE structure to check the number of elements in the SET in the direct_reports column and call various external routines based on the results.

CREATE FUNCTION checklist( d SMALLINT )
   RETURNING VARCHAR(30), VARCHAR(12), INTEGER;

   DEFINE name VARCHAR(30);
   DEFINE dept VARCHAR(12);
   DEFINE num INTEGER;

   SELECT mgr_name, department,
          CARDINALITY(direct_reports) 
      FROM manager INTO name, dept, num
      WHERE dept_no = d;
   IF num > 20 THEN
      EXECUTE FUNCTION add_mgr(dept);
   ELIF num = 0 THEN
      EXECUTE FUNCTION del_mgr(dept);
   ELSE
      RETURN name, dept, num;
   END IF;

END FUNCTION;

The cardinality() function counts the number of elements that a collection contains. For more information, see Cardinality Function (IDS).

An IF - ELIF - ELSE structure in an SPL routine has up to the following four parts:

• An IF THEN condition

  If the condition following the IF statement is TRUE, the routine executes the statements in the IF block. If the condition is false, the routine evaluates the ELIF condition.

  The expression in an IF statement can be any valid condition, as the Condition segment of the IBM Informix: Guide to SQL Syntax describes. For the complete syntax and a detailed discussion of the IF statement, see the IBM Informix: Guide to SQL Syntax.

• One or more ELIF conditions (optional)

  The routine evaluates the ELIF condition only if the IF condition is false. If the ELIF condition is true, the routine executes the statements in the ELIF block. If the ELIF condition is false, the routine either evaluates the next ELIF block or executes the ELSE statement.

• An ELSE condition (optional)

  The routine executes the statements in the ELSE block if the IF condition and all of the ELIF conditions are false.

• An END IF statement

  The END IF statement ends the statement block.