|Oracle® Database SQL Language Reference
11g Release 2 (11.2)
Part Number E17118-03
Functions are defined using PL/SQL. Therefore, this section provides some general information but refers to Oracle Database PL/SQL Language Reference for details of syntax and semantics.
FUNCTION statement to create a standalone stored function or a call specification.
A stored function (also called a user function or user-defined function) is a set of PL/SQL statements you can call by name. Stored functions are very similar to procedures, except that a function returns a value to the environment in which it is called. User functions can be used as part of a SQL expression.
A call specification declares a Java method or a third-generation language (3GL) routine so that it can be called from PL/SQL. You can also use the
CALL SQL statement to call such a method or routine. The call specification tells Oracle Database which Java method, or which named function in which shared library, to invoke when a call is made. It also tells the database what type conversions to make for the arguments and return value.
Note:You can also create a function as part of a package using the
CREATE PROCEDURE for a general discussion of procedures and functions, CREATE PACKAGE for information on creating packages, ALTER FUNCTION and DROP FUNCTION for information on modifying and dropping a function
CREATE LIBRARY for information on shared libraries
Oracle Database Advanced Application Developer's Guide for more information about registering external functions
To create or replace a function in your own schema, you must have the
PROCEDURE system privilege. To create or replace a function in another user's schema, you must have the
PROCEDURE system privilege.
Functions are defined using PL/SQL. Therefore, the syntax diagram in this book shows only the SQL keywords. Please refer to Oracle Database PL/SQL Language Reference for the PL/SQL syntax, semantics, and examples.
plsql_source: See Oracle Database PL/SQL Language Reference.)
REPLACE to re-create the function if it already exists. Use this clause to change the definition of an existing function without dropping, re-creating, and regranting object privileges previously granted on the function. If you redefine a function, then Oracle Database recompiles it.
Users who had previously been granted privileges on a redefined function can still access the function without being regranted the privileges.
If any function-based indexes depend on the function, then Oracle Database marks the indexes
FUNCTIONfor information on recompiling functions using SQL
See Oracle Database PL/SQL Language Reference for the syntax and semantics of the
plsql_source, including examples.