You can use EXECUTE PROCEDURE or EXECUTE FUNCTION to execute
an SPL routine or external routine. In general, it is best to use
EXECUTE PROCEDURE with procedures and EXECUTE FUNCTION with functions.
Tip: For backward compatibility, the EXECUTE PROCEDURE statement
allows you to use an SPL function name and an INTO clause to return
values. However, it is recommended that you use EXECUTE PROCEDURE
only with procedures and EXECUTE FUNCTION only with functions.
You
can issue EXECUTE PROCEDURE and EXECUTE FUNCTION statements as stand-alone
statements from or
from within an SPL routine or external routine. If the routine name
is unique within the database, and if it does not require arguments,
you can execute it by entering just its name and parentheses after
EXECUTE PROCEDURE, as he following figure shows.Figure 1: Execute a procedure.
EXECUTE PROCEDURE update_orders();
The INTO clause is never present when you invoke a procedure
with the EXECUTE statement because a procedure does not return a value.
If
the routine expects arguments, you must enter the argument values
within parentheses, as the following figure shows. Figure 2: Execute a procedure with arguments.
EXECUTE FUNCTION scale_rectangles(107, 1.9)
INTO new;
The statement executes a function. Because a function returns
a value, EXECUTE FUNCTION uses an INTO clause that specifies a variable
where the return value is stored. The INTO clause must always be present
when you use an EXECUTE statement to execute a function.
If
the database has more than one procedure or function of the same name, HCL
OneDB™ locates
the right function based on the data types of the arguments. For example,
the statement in the previous figure supplies INTEGER and REAL values
as arguments, so if your database contains multiple routines named scale_rectangles(),
the database server executes only the scale_rectangles() function
that accepts INTEGER and REAL data types.
The parameter list
of an SPL routine always has parameter names as well as data types.
When you execute the routine, the parameter names are optional. However,
if you pass arguments by name (instead of just by value) to EXECUTE
PROCEDURE or EXECUTE FUNCTION, as in the following figure, HCL
OneDB resolves
the routine-by-routine name and arguments only, a process known as partial
routine resolution. Figure 3: Execute a routine passing arguments by name.
EXECUTE FUNCTION scale_rectangles( rectid = 107,
scale = 1.9 ) INTO new_rectangle;
You can also execute an SPL routine stored on another
database server by adding a qualified routine name to
the statement; that is, a name in the form database@dbserver:owner_name.routine_name,
as in the following figure. Figure 4: Execute an SPL routine stored on another
database server.