Execute routines in expressions

Just as with built-in functions, you can execute SPL routines (and external routines from SPL routines) by using them in expressions in SQL and SPL statements. A routine used in an expression is usually a function, because it returns a value to the rest of the statement.

For example, you might execute a function by a LET statement that assigns the return value to a variable. The statements in the following figure perform the same task. They execute an external function within an SPL routine and assign the return value to the variable a.

Figure 1: Execute an external function within an SPL routine.

LET a = area( rectv.length, rectv.width );

CALL area( rectv.length, rectv.width ) RETURNING a;
   -- these statements are equivalent

You can also execute an SPL routine from an SQL statement, as the following figure shows. Suppose you write an SPL function, increase_by_pct, which increases a given price by a given percentage. After you write an SPL routine, it is available for use in any other SPL routine.

Figure 2: Execute an SPL routine from an SQL statement.

CREATE FUNCTION raise_price ( num INT )
   RETURNING DECIMAL;

   DEFINE p DECIMAL;

   SELECT increase_by_pct(price, 20) INTO p
      FROM inventory WHERE prod_num = num;

   RETURN p;

END FUNCTION;

The example selects the price column of a specified row of inventory and uses the value as an argument to the SPL function increase_by_pct. The function then returns the new value of price, increased by 20 percent, in the variable p.