User-Defined Functions «Prev 

Syntax examples of stored functions

Syntax examples of stored functions

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.