Oracle9i SQL Reference Release 1 (9.0.1) Part Number A90125-01 |
|
SQL Statements:
CREATE CLUSTER to CREATE JAVA, 9 of 12
Use the CREATE
FUNCTION
statement to create a standalone stored function or a call specification. (You can also create a function as part of a package using the CREATE
PACKAGE
statement.)
A stored function (also called a user 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 which Java method, or which named function in which shared library, to invoke when a call is made. It also tells Oracle what type conversions to make for the arguments and return value.
See Also:
|
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 CREATE
PROCEDURE
system privilege. To create a function in another user's schema, you must have the CREATE
ANY
PROCEDURE
system privilege. To replace a function in another user's schema, you must have the ALTER
ANY
PROCEDURE
system privilege.
To invoke a call specification, you may need additional privileges (for example, EXECUTE
privileges on 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:
PL/SQL User's Guide and Reference or Oracle9i Java Stored Procedures Developer's Guide for more information on such prerequisites |
create_function::=
create_function
invoker_rights_clause
parallel_enable_clause
streaming_clause::=
streaming_clause
call_spec::=
call_spec
Java_declaration::=
java_declaration
C_declaration::=
c_declaration
Specify OR
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, Oracle 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, Oracle marks the indexes DISABLED
.
Specify the schema to contain the function. If you omit schema
, Oracle creates the function in your current schema.
Specify the name of the function to be created. If creating the function results in compilation errors, Oracle returns an error. You can see the associated compiler error messages with the SHOW
ERRORS
command.
Restrictions on User-Defined Functions
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 CREATE
TABLE
or ALTER
TABLE
statement
DEFAULT
clause of a CREATE
TABLE
or ALTER
TABLE
statement
In addition, when a function is called from within a query or DML statement, the function cannot:
OUT
or IN
OUT
parameters
SELECT
statement. However, a function called from a subquery in a DML statement can write to the database.
Except for the restriction on OUT
and IN
OUT
parameters, Oracle enforces these restrictions not only for the function 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 that function or any function it calls.
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.
Restriction: If you are creating an aggregate function, you can specify only one argument.
Specify IN
to indicate that you must supply a value for the argument when calling the function. This is the default.
Specify OUT
to indicate that the function will set the value of the argument.
Specify IN
OUT
to indicate that a value for the argument can be supplied by you and may be set by the function.
Specify NOCOPY
to instruct Oracle 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 OUT
or IN
OUT
parameter. (IN
parameter values are always passed NOCOPY
.)
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.
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 function's return value. Because every function must return a value, this clause is required. The return value can have any datatype supported by PL/SQL.
The datatype cannot specify a length, precision, or scale. Oracle derives the length, precision, or scale of the return value from the environment from which the function is called.
The 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 CURRENT_USER
.
This clause also determines how Oracle 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 defined-rights function.
See Also:
CURRENT_USER
is determined
DETERMINISTIC
is an optimization hint that lets the system use a saved copy of the function's return result (if such a copy is available). The saved copy could come from a materialized view, a function-based index, or a redundant call to the same function in the same SQL statement. The query optimizer can choose whether to use the saved copy or re-call the function.
The function should reliably return the same result value whenever it is called with the same values for its arguments. Therefore, do not define the function to use package variables or to access the database in any way that might affect the function's return result, because the results of doing so will not be captured if the system chooses not to call the function.
A function must be declared DETERMINISTIC
in order to be called in the expression of a function-based index, or from the query of a materialized view if that view is marked REFRESH
FAST
or ENABLE
QUERY
REWRITE
.
See Also:
|
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 may not be shared among the parallel execution servers.
PARTITION
argument
BY
clause is used only with functions that have a REF
CURSOR
argument type. It lets you define the partitioning of the inputs to the function from the REF
CURSOR
argument.
Partitioning the inputs to the function affects the way the query is parallelized when the function is used as a table function (that is, 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 RANGE
or HASH
partitioning on a specified column list.
streaming_clause
lets you order or cluster the parallel processing by a specified column list.
The columns specified in all of these optional clauses refer to columns that are returned by the REF
CURSOR
argument of the function.
See Also:
Oracle9i Application Developer's Guide - Fundamentals, Oracle9i Data Cartridge Developer's Guide, and PL/SQL User's Guide and Reference for more information on user-defined aggregate functions |
Use PIPELINED
to instruct Oracle 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 then returns rows as they are produced by the function.
PIPELINED
alone (PIPELINED
IS
...), the PL/SQL function body should use the PIPE
keyword. This keyword instructs Oracle to return single elements of the collection out of the function, instead of returning the whole collection as a single value.
PIPELINED
USING
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.
See Also:
Specify AGGREGATE
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 ORDER
BY
clause.
Note:
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 |
In the 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 assumes that the implementation type is in your own schema.
Restriction: If you specify this clause, you can specify only one input argument for the function.
Use the pl/sql_subprogram_body
to declare the function in a PL/SQL subprogram body.
See Also:
Oracle9i Application Developer's Guide - Fundamentals for more information on PL/SQL subprograms |
Use the call_spec
to map a Java or C method name, parameter types, and return type to their SQL counterparts. In Java_declaration,
'string
' identifies the Java implementation of the method.
See Also:
|
AS
EXTERNAL
is an alternative way of declaring a C method. This clause has been deprecated and is supported for backward compatibility only. Oracle Corporation recommends that you use the AS
LANGUAGE
C
syntax.
CREATE
FUNCTION
Examples
The following statement creates the function get_bal
on the demo table oe.orders
(the PL/SQL is in italics):
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;
The 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 acc_no
is NUMBER
.
The function returns the account balance. The RETURN
clause of the CREATE
FUNCTION
statement specifies the datatype 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 function created above can be used in a SQL statement. For example:
SELECT get_bal(165) FROM DUAL; GET_BAL(165) ------------ 2519
The following statement creates 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 (...);
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 ODCIAggregate
routines:
CREATE FUNCTION SecondMax (input NUMBER) RETURN NUMBER PARALLEL_ENABLE AGGREGATE USING SecondMaxImpl;
See Also:
Oracle9i 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 demo table hr.employees
:
SELECT SecondMax(salary), department_id FROM employees GROUP BY department_id HAVING SecondMax(salary) > 9000; SECONDMAX(SALARY) DEPARTMENT_ID ----------------- ------------- 13500 80 17000 90
The following statement creates a function that uses a DBMS_LOB
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;
See Also:
"Function-Based Index on LOB Column Example" to see how to use this function to create a function-based index |
|
Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|