CREATE FUNCTION statement (PL/SQL)

The CREATE FUNCTION statement defines a scalar or pipelined function that is stored in the database.

Invocation

A scalar function returns a single value each time it is invoked, and is generally valid wherever an SQL expression is valid. A pipelined function computes a table one row at a time and can be referenced in the FROM clause of SELECT statements.

This statement can be executed from the Db2® command line processor, any supported interactive SQL interface, an application, or routine.

Authorization

The privileges held by the authorization ID of the statement must include at least one of the following:

If the schema name of the function does not exist, IMPLICIT_SCHEMA authority on the database
If the schema name of the function refers to an existing schema, CREATEIN privilege on the schema
DBADM authority

The privileges held by the authorization ID of the statement must also include all of the privileges necessary to invoke the SQL statements that are specified in the function body.

The authorization ID of the statement must be the owner of the matched function if OR REPLACE is specified (SQLSTATE 42501).

Syntax

Description

The CREATE FUNCTION statement specifies the name of the function, the optional parameters, the return type of the function, and the body of the function. The body of the function is a block that is enclosed by the BEGIN and END keywords. It can contain an optional EXCEPTION section that defines an action to be taken when a defined exception condition occurs.

OR REPLACE: Indicates that if a function with the same name already exists in the schema, the new function is to replace the existing one. If this option is not specified, the new function cannot replace an existing one with the same name in the same schema.
FUNCTION name: Specifies an identifier for the function.
parameter-name: Specifies the name of a parameter. The name cannot be the same as any other parameter-name in the parameter list (SQLSTATE 42734).
data-type: Specifies one of the supported PL/SQL data types.
RETURN return-type: Specifies the data type of the scalar value that is returned by the function.
PIPELINED: Specifies that the function being created is a pipelined function.
IS or AS: Introduces the block that defines the function body.
declaration: Specifies one or more variable, cursor, or REF CURSOR type declarations.
statement: Specifies one or more PL/SQL program statements. Each statement must be terminated by a semicolon.
exception: Specifies an exception condition name.

Notes

A PL/SQL function cannot take any action that changes the state of an object that the database manager does not manage.

The CREATE FUNCTION statement can be submitted in obfuscated form. In an obfuscated statement, only the function name is readable. The rest of the statement is encoded in such a way that it is not readable, but can be decoded by the database server. Obfuscated statements can be produced by calling the DBMS_DDL.WRAP function.

Examples

The following example shows a basic function that takes no parameters:

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

The following example shows a 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;

The following example shows a pipelined function that returns a table with two rows:

CREATE TYPE row_typ as OBJECT(f1 NUMBER, f2 VARCHAR2(10))

CREATE TYPE arr_typ as TABLE OF row_typ

CREATE FUNCTION pipe_func
RETURN arr_typ
PIPELINED
IS
BEGIN
    PIPE ROW (1, 'one');
    PIPE ROW (2, 'two');
    RETURN;
END pipe_func;