|Oracle® Database SQL Reference
10g Release 2 (10.2)
|PDF · Mobi · ePub|
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 SQL and PL/SQL. 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
"Examples" for examples of creating functions
CREATE LIBRARY for information on shared libraries
Oracle Database Application Developer's Guide - Fundamentals for more information about registering external functions
Before a stored function can be created, the user
SYS must run a SQL script that is commonly called
DBMSSTDX.SQL. The exact name and location of this script depend on your operating system.
To create a function in your own schema, you must have the
PROCEDURE system privilege. To create a function in another user's schema, you must have the
PROCEDURE system privilege. To replace a 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.
See Also:PL/SQL User's Guide and Reference or Oracle Database Java Developer's Guide for more information on such prerequisites
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
See Also:ALTER FUNCTION for information on recompiling functions
Specify the schema to contain the function. If you omit
schema, Oracle Database creates the function in your current schema.
Specify the name of the function to be created. If creating the function results in compilation errors, then Oracle Database returns an error. You can see the associated compiler error messages with the
User-defined functions cannot be used in situations that require an unchanging definition. Thus, you cannot use user-defined functions:
CHECK constraint clause of a
DEFAULT clause of a
In addition, when a function is called from within a query or DML statement, the function cannot:
Commit or roll back the current transaction, create a savepoint or roll back to a savepoint, or alter the session or the system. DDL statements implicitly commit the current transaction, so a user-defined function cannot execute any DDL statements.
Write to the database, if the function is being called from a
SELECT statement. However, a function called from a subquery in a DML statement can write to the database.
Write to the same table that is being modified by the statement from which the function is called, if the function is called from a DML statement.
Except for the restriction on
OUT parameters, Oracle Database enforces these restrictions not only for
function when called directly from the SQL statement, but also for any functions that
function calls, and on any functions called from the SQL statements executed by
function or any functions it calls.
See Also:"Creating a Function: Examples"
Specify the name of an argument to the function. If the function does not accept arguments, you can omit the parentheses following the function name.
NOCOPY to instruct Oracle Database to pass this argument as fast as possible. This clause can significantly enhance performance when passing a large value like a record, an index-by table, or a varray to an
IN parameter values are always passed
When you specify
NOCOPY, assignments made to a package variable may show immediately in this parameter, or assignments made to this parameter may show immediately in a package variable, if the package variable is passed as the actual assignment corresponding to this parameter.
Similarly, changes made either to this parameter or to another parameter may be visible immediately through both names if the same variable is passed to both.
If the procedure is exited with an unhandled exception, any assignment made to this parameter may be visible in the caller's variable.
These effects may or may not occur on any particular call. You should use
NOCOPY only when these effects would not matter.
For datatype, specify the datatype of the return value of the function. Because every function must return a value, this clause is required. The return value can have any datatype supported by PL/SQL.
Note:Oracle SQL does not support calling of functions with Boolean parameters or returns. Therefore, if your user-defined functions will be called from SQL statements, you must design them to return numbers (0 or 1) or character strings ('
TRUE' or '
The datatype cannot specify a length, precision, or scale. Oracle 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.
invoker_rights_clause lets you specify whether the function executes with the privileges and in the schema of the user who owns it or with the privileges and in the schema of
This clause also determines how Oracle Database resolves external names in queries, DML operations, and dynamic SQL statements in the function.
CURRENT_USER if you want the function to execute with the privileges of
CURRENT_USER. This clause creates an invoker-rights function.
This clause also specifies that external names in queries, DML operations, and dynamic SQL statements resolve in the schema of
CURRENT_USER. External names in all other statements resolve in the schema in which the function resides.
DEFINER if you want the function to execute with the privileges of the owner of the schema in which the function resides, and that external names resolve in the schema where the function resides. This is the default and creates a definer-rights function.
Oracle Database Concepts and Oracle Database Application Developer's Guide - Fundamentals for information on how
CURRENT_USER is determined
Oracle Database Security Guide for information on invoker-rights and definer-rights types
DETERMINISTIC to indicate that the function returns the same result value whenever it is called with the same values for its arguments.
You must specify this keyword if you intend to call the function in the expression of a function-based index or from the query of a materialized view that is marked
REWRITE. When Oracle Database encounters a deterministic function in one of these contexts, it attempts to use previously calculated results when possible rather than reexecuting the function. If you subsequently change the semantics of the function, you must manually rebuild all dependent function-based indexes and materialized views.
Do not specify this clause to define a function that uses package variables or that accesses the database in any way that might affect the return result of the function. The results of doing so will not be captured if Oracle Database chooses not to reexecute the function.
The following semantic rules govern the use of the
You can declare a top-level subprogram
You can declare a package-level subprogram
DETERMINISTIC in the package specification but not in the package body.
You cannot declare
DETERMINISTIC a private subprogram (declared inside another subprogram or inside a package body).
DETERMINISTIC subprogram can call another subprogram whether the called program is declared
DETERMINISTIC or not.
PARALLEL_ENABLE is an optimization hint indicating that the function can be executed from a parallel execution server of a parallel query operation. The function should not use session state, such as package variables, as those variables are not necessarily shared among the parallel execution servers.
BY clause is used only with functions that have a
CURSOR argument type. It lets you define the partitioning of the inputs to the function from the
Partitioning the inputs to the function affects the way the query is parallelized when the function is used as a table function in the
FROM clause of the query.
ANY indicates that the data can be partitioned randomly among the parallel execution servers. Alternatively, you can specify
HASH partitioning on a specified column list.
streaming_clause lets you order or cluster the parallel processing by a specified column list.
BY indicates that the rows on a parallel execution server must be locally ordered.
BY indicates that the rows on a parallel execution server must have the same key values as specified by the
expr identifies the
CURSOR parameter name of the table function on which partitioning was specified, and on whose columns you are specifying ordering or clustering for each slave in a parallel query execution.
The columns specified in all of these optional clauses refer to columns that are returned by the
CURSOR argument of the function.
See Also:Oracle Database Application Developer's Guide - Fundamentals, Oracle Data Cartridge Developer's Guide, and PL/SQL User's Guide and Reference for more information on user-defined aggregate functions
PIPELINED to instruct Oracle Database to return the results of a table function iteratively. A table function returns a collection type (a nested table or varray). You query table functions by using the
TABLE keyword before the function name in the
FROM clause of the query. For example:
SELECT * FROM TABLE(function_name(...))
Oracle Database then returns rows as they are produced by the function.
If you specify the keyword
PIPELINED alone (
IS ...), the PL/SQL function body should use the
PIPE keyword. This keyword instructs the database to return single elements of the collection out of the function, instead of returning the whole collection as a single value.
You can specify the
implementation_type clause if you want to predefine an interface containing the start, fetch, and close operations. The implementation type must implement the
ODCITable interface and must exist at the time the table function is created. This clause is useful for table functions that will be implemented in external languages such as C++ and Java.
If the return type of the function is
ANYDATASET, then you must also define a describe method (
ODCITableDescribe) as part of the implementation type of the function.
PL/SQL User's Guide and Reference and Oracle Database Application Developer's Guide - Fundamentals for more information on table functions
Oracle Data Cartridge Developer's Guide for information on ODCI routines
USING to identify this function as an aggregate function, or one that evaluates a group of rows and returns a single row. You can specify aggregate functions in the select list,
HAVING clause, and
When you specify a user-defined aggregate function in a query, you can treat it as an analytic function (one that operates on a query result set). To do so, use the
analytic_clause syntax available for built-in analytic functions. See "Analytic Functions" for syntax and semantics.
USING clause, specify the name of the implementation type of the function. The implementation type must be an object type containing the implementation of the
ODCIAggregate routines. If you do not specify
schema, Oracle Database assumes that the implementation type is in your own schema.
See Also:Oracle Data Cartridge Developer's Guide for information on ODCI routines and "Creating Aggregate Functions: Example"
Use the appropriate part of this clause to declare the body of the function.
See Also:Oracle Database Application Developer's Guide - Fundamentals for more information on PL/SQL subprograms and "Using a Packaged Procedure in a Function: Example"
Oracle Database Application Developer's Guide - Fundamentals for an explanation of the parameters and semantics of the
AS EXTERNAL In earlier releases,
EXTERNAL was an alternative way of declaring a C method. This clause has been deprecated and is supported for backward compatibility only. Oracle recommends that you use the
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 call the function, you must specify the argument
acc_no, the number of the account whose balance is sought. The datatype of
The function returns the account balance. The
RETURN clause of the
FUNCTION statement specifies the datatype 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
The hypothetical following statement creates a PL/SQL standalone function
get_val that registers the C routine
c_get_val as an external function. (The parameters have been omitted from this example; the PL/SQL is in italics.)
CREATE FUNCTION get_val ( x_val IN NUMBER, y_val IN NUMBER, image IN LONG RAW ) RETURN BINARY_INTEGER AS LANGUAGE C NAME "c_get_val" LIBRARY c_utils PARAMETERS (...);
Creating Aggregate Functions: Example The next statement creates an aggregate function called
SecondMax to aggregate over number values. It assumes that the object type
SecondMaxImpl routines contains the implementations of the
CREATE FUNCTION SecondMax (input NUMBER) RETURN NUMBER PARALLEL_ENABLE AGGREGATE USING SecondMaxImpl;
See Also:Oracle Data Cartridge Developer's Guide for the complete implementation of type and type body for
You would use such an aggregate function in a query like the following statement, which queries the sample table
SELECT SecondMax(salary), department_id FROM employees GROUP BY department_id HAVING SecondMax(salary) > 9000; SECONDMAX(SALARY) DEPARTMENT_ID ----------------- ------------- 13500 80 17000 90
CREATE OR REPLACE FUNCTION text_length(a CLOB) RETURN NUMBER DETERMINISTIC IS BEGIN RETURN DBMS_LOB.GETLENGTH(a); END;
See Also:"Creating a Function-Based Index on a LOB Column: Example" to see how to use this function to create a function-based index