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.