FUNCTION statement creates or replaces a standalone function or a call specification.
A standalone function is a function (a subprogram that returns a single value) that is stored in the database.
A standalone function that you create with the
FUNCTION statement differs from a function that you declare and define in a PL/SQL block or package. For more information, see "Function Declaration and Definition" and CREATE PACKAGE Statement.
A call specification declares a Java method or a C function so that it can be invoked from PL/SQL. You can also use the SQL
CALL statement to invoke such a method or subprogram. The call specification tells the database which Java method, or which named function in which shared library, to invoke when an invocation is made. It also tells the database what type conversions to make for the arguments and return value.
To be callable from SQL statements, a stored function must obey certain rules that control side effects. See "Subprogram Side Effects".
To create or replace a standalone function in your schema, you must have the
PROCEDURE system privilege.
To create or replace a standalone function in another user's schema, you must have the
PROCEDURE system privilege.
To invoke a call specification, you may need additional privileges, for example,
EXECUTE privileges on a C library for a C call specification.
To embed a
FUNCTION statement inside an Oracle precompiler program, you must terminate the statement with the keyword
END-EXEC followed by the embedded SQL statement terminator for the specific language.
( sharing_clause ::= , invoker_rights_clause ::= , accessible_by_clause ::= , default_collation_clause ::= , deterministic_clause ::= , parallel_enable_clause ::= , result_cache_clause ::= , aggregate_clause ::= , pipelined_clause ::= , body ::= , call_spec ::= , datatype ::= , declare_section ::= , parameter_declaration ::= )
Re-creates the function if it exists, and recompiles it.
Users who were granted privileges on the function before it was redefined can still access the function without being regranted the privileges.
If any function-based indexes depend on the function, then the database marks the indexes
[ EDITIONABLE | NONEDITIONABLE ]
Specifies whether the function is an editioned or noneditioned object if editioning is enabled for the schema object type
EDITIONABLE. For information about editioned and noneditioned objects, see Oracle Database Development Guide.
Name of the schema containing the function. Default: your schema.
Name of the function to be created.
If you plan to invoke a stored subprogram using a stub generated by SQL*Module, then the stored subprogram name must also be a legal identifier in the invoking host 3GL language, such as Ada or C.
datatype, specify the data type of the return value of the function. The return value can have any data type supported by PL/SQL.
Oracle SQL does not support invoking functions with
BOOLEAN parameters or returns. Therefore, for SQL statements to invoke your user-defined functions, you must design them to return numbers (0 or 1) or character strings ('
TRUE' or '
The data type cannot specify a length, precision, or scale. The database derives the length, precision, or scale of the return value from the environment from which the function is called.
If the return type is
ANYDATASET and you intend to use the function in the
FROM clause of a query, then you must also specify the
PIPELINED clause and define a describe method (
ODCITableDescribe) as part of the implementation type of the function.
You cannot constrain this data type (with
NULL, for example).
The required executable part of the function and, optionally, the exception-handling part of the function.
The optional declarative part of the function. Declarations are local to the function, can be referenced in body, and cease to exist when the function completes execution.
Example 14-14 Creating a Function
This statement creates the function
get_bal on the sample table
CREATE FUNCTION get_bal(acc_no IN NUMBER) RETURN NUMBER IS acc_bal NUMBER(11,2); BEGIN SELECT order_total INTO acc_bal FROM orders WHERE customer_id = acc_no; RETURN(acc_bal); END; /
get_bal function returns the balance of a specified account.
When you invoke the function, you must specify the argument
acc_no, the number of the account whose balance is sought. The data type of
The function returns the account balance. The
RETURN clause of the
FUNCTION statement specifies the data type of the return value to be
The function uses a
SELECT statement to select the
balance column from the row identified by the argument
acc_no in the
orders table. The function uses a
RETURN statement to return this value to the environment in which the function is called.
The function created in the preceding example can be used in a SQL statement. For example:
SELECT get_bal(165) FROM DUAL; GET_BAL(165) ------------ 2519
Example 14-15 Creating Aggregate Functions
The next statement creates an aggregate function called
SecondMax to aggregate over number values. It assumes that the ADT
SecondMaxImpl subprograms contains the implementations of the
CREATE FUNCTION SecondMax (input NUMBER) RETURN NUMBER PARALLEL_ENABLE AGGREGATE USING SecondMaxImpl;
Oracle Database Data Cartridge Developer's Guide for the complete implementation of type and type body for
Use such an aggregate function in a query like this statement, which queries the sample table
SELECT SecondMax(salary) "SecondMax", department_id FROM employees GROUP BY department_id HAVING SecondMax(salary) > 9000 ORDER BY "SecondMax", department_id; SecondMax DEPARTMENT_ID --------- ------------- 9450 100 13670.74 50 14175 80 18742.5 90
Example 14-16 Package Procedure in a Function
This statement creates a function that uses a
DBMS_LOB.GETLENGTH procedure to return the length of a
CREATE OR REPLACE FUNCTION text_length(a CLOB) RETURN NUMBER DETERMINISTIC IS BEGIN RETURN DBMS_LOB.GETLENGTH(a); END;
In this chapter:
In other chapters:
"Function Declaration and Definition" for information about creating a function in a PL/SQL block
In other books:
Oracle Database SQL Language Reference for information about the
Oracle Database Development Guide for information about restrictions on user-defined functions that are called from SQL statements
Oracle Database Development Guide for more information about call specifications
Oracle Database Data Cartridge Developer's Guide for information about defining the