|Oracle® Database PL/SQL Language Reference
12c Release 1 (12.1)
|PDF · Mobi · ePub|
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
FUNCTIONstatement differs from a function that you declare and define in a PL/SQL block or package. For information about the latter, see "Function Declaration and Definition".
A call specification declares a Java method or a third-generation language (3GL) subprogram 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.
Note: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.
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
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.
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.
datatype, specify the data type of the return value of the function. The return value can have any data type supported by PL/SQL.
Note:Oracle SQL does not support invoking functions with
BOOLEANparameters 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).
AUTHID property of the function. For information about the
AUTHID property, see "Invoker's Rights and Definer's Rights (AUTHID Property)". The
invoker_rights_clause can appear only once in the function.
accessor (PL/SQL unit) that can invoke the function. An
accessor can appear more than once in
accessible_by_clause can appear only once in the function.
The compiler checks the syntax of
accessible_by_clause, but does not check that each
Whenever the function is invoked, the compiler first does all database-level security checks on the invocation. If any check fails, then the invocation fails, even if the invoker is an
If all database-level security checks on the invocation succeed, and the function has no
accessible_by_clause, then the invocation succeeds. If the function has an
accessible_by_clause, then the invocation succeeds only if the invoker is an
accessible_by_clauseis also called the white list.
Specifies a trigger that can invoke the function. If you specify
trigger_name must be in that schema. If you do not specify
trigger_name must be in the schema containing the function.
Specifies a stored PL/SQL unit that can invoke the function.
unit_kind is either
unit_name is the name of a PL/SQL unit created by the statement
TYPE. If you specify
unit_name must be the name of a unit of that kind.
If you specify
unit_name must be in that schema. If you do not specify
unit_name must be in the schema containing the function.
Indicates that the function returns the same result value whenever it is called with the same values for its parameters.
DETERMINISTIC can appear only once in the function.
You must specify this keyword if you intend to invoke the function in the expression of a function-based index or from the query of a materialized view that is marked
REWRITE. When the database encounters a deterministic function in one of these contexts, it tries to use previously calculated results when possible rather than reexecuting the function. If you subsequently change the semantics of the function, then 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 are not captured if the database chooses not to reexecute the function.
These semantic rules govern the use of the
You can declare a schema-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 invoke another subprogram whether the called program is declared
DETERMINISTIC or not.
It is good programming practice to make functions that fall into these categories
Functions used in a
ORDER methods of a SQL type
Functions that help determine whether or where a row appears in a result set
Indicates that the function can run from a parallel execution server of a parallel query operation. The function must not use session state, such as package variables, as those variables are not necessarily shared among the parallel execution servers. The
parallel_enable_clause can appear only once in the function.
Use the optional
BY clause only with a function that has a
CURSOR data type. This clause 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.
Note:You can partition weak cursor variable arguments to table functions only with
ANY, not with
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 and have the same key values as specified by
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 Data Cartridge Developer's Guide for information about using parallel table functions
Instructs the 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(...))
the database then returns rows as they are produced by the function.
If you specify the keyword
PIPELINED alone (
IS ...), then the PL/SQL function body must 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 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 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.
Oracle Database Data Cartridge Developer's Guide for information about using pipelined table functions
Caches the results of the function.
RESULT_CACHE can appear only once in the function. For more information, see "PL/SQL Function Result Cache".
Identifies the 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 SQL analytic functions. See Oracle Database SQL Language Reference for syntax and semantics of analytic functions.
USING clause, specify the name of the implementation type of the function. The implementation type must be an ADT containing the implementation of the
ODCIAggregate subprograms. If you do not specify
schema, then the database assumes that the implementation type is in your schema.
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.
Maps a C procedure or Java method name, parameter types, and return type to their SQL counterparts. In
string identifies the Java implementation of the method. In
lib_name identifies a library created by the "CREATE LIBRARY Statement".
Deprecated way of declaring a C function, supported only for backward compatibility. 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 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
The hypothetical following statement creates a PL/SQL standalone function
get_val that registers the C subprogram
c_get_val as an external function. (The parameters have been omitted from this example.)
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 ADT
SecondMaxImpl subprograms contains the implementations of the
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
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
CREATE OR REPLACE FUNCTION text_length(a CLOB) RETURN NUMBER DETERMINISTIC IS BEGIN RETURN DBMS_LOB.GETLENGTH(a); END;
"Function Declaration and Definition" for information about creating a function in a PL/SQL block