The CREATE FUNCTION statement defines a scalar or pipelined function that is stored in the database.
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.
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).
>>-CREATE--+------------+--FUNCTION--name-----------------------> '-OR REPLACE-' >--+---------------------------------------------------------------------------------+--> '-(--+-----------------------------------------------------------------------+--)-' | .-,-----------------------------------------------------------------. | | V .-IN-----. | | '---parameter-name--+--------+--| data-type |--+--------------------+-+-' +-OUT----+ '-| default-clause |-' '-IN OUT-' >--RETURN--return-type--+-----------+--+-IS-+-------------------> '-PIPELINED-' '-AS-' .-----------------. .-----------. V | V | >----+-------------+-+--BEGIN----statement-+--------------------> '-declaration-' >--+----------------------------------------------------------------------------+--> | .-------------------------------------------------------------. | | | .-------------------. .-----------. | | | V V | V | | | '-EXCEPTION----WHEN--exception----+---------------+-+--THEN----statement-+-+-' '-OR--exception-' >--END--+------+----------------------------------------------->< '-name-'
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.
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.
CREATE OR REPLACE FUNCTION simple_function
RETURN VARCHAR2
IS
BEGIN
RETURN 'That''s All Folks!';
END simple_function;
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;
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;