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.