Before trying to write the function, let's examine the generic syntax for creating this second type of stored program.
Here is the template for creating a function. As you look it over, you'll probably realize that you have seen most of these elements before, other than those in boldface.
CREATE [ OR REPLACE ] FUNCTION procedure_name (parameter1 MODE DATATYPE DEFAULT expression , parameter2 MODE DATATYPE DEFAULT expression , ...) RETURN DATATYPE AS [ variable1 DATATYPE ; variable2 DATATYPE ; ... ] BEGIN executable_statement ; RETURN expression; [ EXCEPTION WHEN exception_name THEN executable_statement ; ] END; /
The differences between this function template and the procedure template are minimal. In addition to the fact that the CREATE statement says FUNCTION instead of PROCEDURE, this code differs from a procedure in only two places: the header, which specifies the returned datatype, and the body, which must explicitly convey a value back to the caller.
In the header, the RETURN clause is part of the function declaration. It tells the compiler (and other programs) what datatype to expect back when you invoke the function.
Inside the executable section, this use of RETURN is known as the return statement, and it says "Okay, I'm all done; it's time to send back (return) the following value." You can also put a RETURN statement in the EXCEPTION section. ...