CREATE [OR REPLACE]FUNCTION function_name
[(parameter_name IN datatype,...)]
RETURN datatype AS
BEGIN
[DECLARE
variable_name datatype;
...
BEGIN]
body statements ...
RETURN return_value;
[END;
]
END;
Line 1: Use CREATE FUNCTION to begin the definition of the function. You can add the "OR REPLACE" clause if you want to replace an existing function.Replace function_name with an actual function name.
Naming standards apply just like any database object. Line 2: A function does not require any parameters. However, if you specify parameters, you only can use incoming parameters. Enclose all parameters in a set of parentheses and separate them with commas. A later module in this course explains more about how to use parameters.
Line 3: The RETURN clause and the keyword "AS" are required when defining a function. The RETURN clause defines the datatype of the field returned by the function. Line 4: BEGIN indicates the beginning of the function's definition. Lines 5-8: The DECLARE section is optional. This is where variables are defined. If you use a DECLARE section, you must add the keyword BEGIN to mark the beginning of the function body. Line 9: The body section of the function is where all the work is done. This is a required section. Line 10: There must be at least one RETURN clause inside the body of the function. The return_value can be a variable, an expression, or a literal. Line 11: This END clause closes the body section It is only needed if you have a DECLARE section. Line 12: The END clause completes the entire function syntax and is required.