AntDB-Oracle Compatibility Developer’s Manual P4–6

Function Overview

A function is an SPL procedure that is called as an expression. When the function is finished running, it returns a value instead of where the function was called in the expression. A function can receive a value in the form of an input parameter from the program that called the function. In addition to returning the value itself, the function can also return the value as an output parameter to the caller of the function. It should be noted, however, that the use of output parameters in functions is not advocated in the actual process of writing programs.

Create Function

The CREATE FUNCTION command defines and names a function that will be stored in the database.

CREATE [ OR REPLACE ] FUNCTION name [ (parameters) ]

RETURN data_type

{ IS | AS }

[ declarations ]

BEGIN

statements

END [ name ];

name is the identifier of the function. If [ OR REPLACE ] is defined and a function with the same name exists in the schema, then the newly created function will replace the existing function with the same name. Conversely, a newly created function cannot replace an existing function in the same schema. parameters is a list of formal parameters. data_type is the type of the function's return value.

declarations are declarations of variables, cursors or types. statements are the statements used by the SPL application.

The BEGIN-END block contains a section to catch exceptions.

The following is an example of a function that is simple and takes no arguments.

\set PLSQL_MODE ON
CREATE OR REPLACE FUNCTION simple_function()
RETURN VARCHAR2
IS
BEGIN
RETURN 'That''s All Folks!';
END simple_function;
/

Here is another example of a function with two input arguments. More information about parameters is discussed later in this section.

\set PLSQL_MODE ON
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;
/

See the CREATE FUNCTION command for details.