CREATE FUNCTION Statement
The CREATE
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.
Note:
A standalone function that you create with the CREATE
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, a C function, or a JavaScript
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 JavaScript function, 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.
Note:
To be callable from SQL statements, a stored function must obey certain rules that control side effects. See "Subprogram Side Effects".
Topics
Prerequisites
To create or replace a standalone function in your schema, you must have the
CREATE
PROCEDURE
system privilege.
To create or replace a standalone function in another user's schema, you must have
the CREATE
ANY
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 CREATE
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.
See Also:
For more information about such prerequisites:
Syntax
create_function ::=
plsql_function_source ::=
( sharing_clause ::= , invoker_rights_clause ::= , accessible_by_clause ::= , default_collation_clause ::= , deterministic_clause ::= , shard_enable_clause ::= , parallel_enable_clause ::= , result_cache_clause ::= , aggregate_clause ::= , pipelined_clause ::=, sql_macro_clause ::= , body ::= , call_spec ::= , datatype ::= , declare_section ::= , parameter_declaration ::= )
Semantics
create_function
OR REPLACE
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 DISABLED
.
[ EDITIONABLE | NONEDITIONABLE ]
Specifies whether the function is an editioned or noneditioned object if editioning is enabled for the schema object type FUNCTION
in schema
. Default: EDITIONABLE
. For information about editioned and noneditioned objects, see Oracle Database Development Guide.
IF NOT EXISTS
Creates the function if it does not already exist. If a function by the same name does exist, the statement is ignored without error and the original function body remains unchanged.
IF NOT EXISTS
cannot be used in combination with OR
REPLACE
.
plsql_function_source
schema
Name of the schema containing the function. Default: your schema.
function_name
Name of the function to be created.
Note:
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.
RETURN datatype
For datatype
, specify the data type of the return value of the function. The return value can have any data type supported by PL/SQL.
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 NOT
NULL
, for example).
body
The required executable part of the function and, optionally, the exception-handling part of the function.
declare_section
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.
call_spec
The reference to a call specification mapping a C procedure, Java method name, or JavaScript function name, parameter types, and return type to their SQL counterparts.
Examples
Example 15-14 Creating a Function
This statement creates the function get_bal
on the sample table oe.orders
.
CREATE FUNCTION IF NOT EXISTS 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; /
The 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 acc_no
is NUMBER
.
The function returns the account balance. The RETURN
clause of the CREATE
FUNCTION
statement specifies the data type of the return value to be NUMBER
.
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 optional IF NOT EXISTS
clause is used to ensure that the
statement is idempotent. The resulting output message (in this case Function
created.
) is the same whether the function is created or the statement is
ignored.
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 15-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 ODCIAggregate
subprograms:
CREATE FUNCTION SecondMax (input NUMBER) RETURN NUMBER PARALLEL_ENABLE AGGREGATE USING SecondMaxImpl;
See Also:
Oracle Database Data Cartridge Developer's Guide for the complete implementation of type and type body for SecondMaxImpl
Use such an aggregate function in a query like this statement, which queries the sample table hr.employees
:
SELECT SecondMax(salary) "SecondMax", department_id
FROM employees
GROUP BY department_id
HAVING SecondMax(salary) > 9000
ORDER BY "SecondMax", department_id;
SecondMax DEPARTMENT_ID
---------- -------------
13500 80
17000 90
Example 15-16 Package Procedure in a Function
This statement creates a function that uses a DBMS_LOB.GETLENGTH
procedure to return the length of a CLOB
column.
CREATE OR REPLACE FUNCTION text_length(a CLOB) RETURN NUMBER DETERMINISTIC IS BEGIN RETURN DBMS_LOB.GETLENGTH(a); END;
Related Topics
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
CALL
statement -
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
ODCITableDescribe
function