Name
RETURN
Synopsis
The RETURN
statement terminates processing
within a SQL-invoked function (as opposed to a host-invoked function)
and returns the function’s result value.
|
Vendor |
Command |
|---|---|
|
SQL Server |
Supported |
|
MySQL |
Supported |
|
Oracle |
Supported |
|
PostgreSQL |
Supported |
SQL99 Syntax and Description
RETURNS return_parameter_value | NULL
The RETURN function is used within a function to
end its processing. Using the NULL clause
terminates the function without returning an actual value. Otherwise,
the parameter value specified is returned either as a variable or as
a literal expression.
Although the RETURN statement is categorized as
a separate command within SQL, it is deeply intertwined with the
CREATE FUNCTION
statement. Check the CREATE
FUNCTION statement for a more complete understanding of
each vendor’s implementation of RETURN.
Examples
This example creates a function. The function returns the value that is stored in the proj_rev variable to the calling session:
CREATE FUNCTION project_revenue (project IN varchar2)
RETURN NUMBER
AS
proj_rev NUMBER(10,2);
BEGIN
SELECT SUM(DECODE(action,'COMPLETED',amount,0) -
SUM(DECODE(action,'STARTED',amount,0) +
SUM(DECODE(action,'PAYMENT',amount,0)
INTO proj_rev
FROM construction_actions
WHERE project_name = project;
RETURN (proj_rev);
END;This example creates a function that returns a calculated value to the calling session:
CREATE FUNCTION metric_volume -- Input dimensions in centimeters. (@length decimal(4,1), @width decimal(4,1), @height ...