	Documentation


Prev	Fast Backward		Fast Forward	Next

CREATE FUNCTION (SPL)

Name

CREATE FUNCTION -- define a new function in SPL

Synopsis

CREATE [ OR REPLACE ] FUNCTION name
[ ( parameters ) ]
RETURN data_type
{ IS | AS }
    [ declarations ]
BEGIN
    statements
END name;

Description

CREATE FUNCTION defines a new function. CREATE OR REPLACE FUNCTION will either create a new function, or replace an existing definition.

A function is a subprogram that computes a value. Functions and procedures are structured alike, except that functions have a RETURN clause.

If you drop and then recreate a function, the new function is not the same entity as the old; you will break existing rules, views, triggers, etc. that referred to the old function. Use CREATE OR REPLACE FUNCTION to change a function definition without breaking objects that refer to the function.

The user that creates the function becomes the owner of the function.

Parameters

name: The name (optionally schema-qualified) of the function to create.
parameters: It is a list of formal parameters.
datatype: For datatype, specify the datatype of the return value of the function. Because every function must return a value, this clause is required. The return value can have any datatype supported by SPL.
declarations: These are variable, cursor, or type declarations made within the function.
statements: Statements are SPL program statements which will be executed within the function.

To be able to define a function, the user must have the USAGE privilege on the language.

Notes

EnterpriseDB allows function overloading; that is, the same name can be used for several different functions so long as they have distinct argument types. However, the C names of all functions must be different, so you must give overloaded C functions different C names (for example, use the argument types as part of the C names).

When repeated CREATE FUNCTION calls refer to the same object file, the file is only loaded once.

Use DROP FUNCTION to remove user-defined functions.

To be able to define a function, the user must have the USAGE privilege on the language.

Examples

Here is a trivial example to help you get started.

CREATE OR REPLACE FUNCTION simple_function
RETURN VARCHAR2
IS
BEGIN
    RETURN 'That''s All Folks!';
END simple_function;

The following is another function that takes two input parameters.

CREATE OR REPLACE FUNCTION emp_comp (
    p_sal           NUMBER,
    p_comm          NUMBER
) RETURN NUMBER
IS
BEGIN
    RETURN (p_sal + NVL(p_comm, 0)) * 24;
END emp_comp;