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 ...
Get SQL in a Nutshell now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.