Oracle8i SQL Reference Release 2 (8.1.6) A76989-01 |
|
SQL Statements (continued), 8 of 20
Java_declaration::=
C_declaration::=
To create a stored function or a call specification.
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. For a general discussion of procedures and functions, see "CREATE PROCEDURE". For examples of creating functions, see "Examples".
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.
The CREATE FUNCTION
statement creates a function as a standalone schema object. You can also create a function as part of a package. For information on creating packages, see "CREATE PACKAGE".
For information on modifying a function, see "ALTER FUNCTION". For information on shared libraries, see "CREATE LIBRARY". For information on dropping a standalone function, see "DROP FUNCTION".
See Also:
Oracle8i Application Developer's Guide - Fundamentals for more information about registering external functions. |
Before a stored function can be created, the user SYS must run the SQL script 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 Oracle8i Java Stored Procedures Developer's Guide for more information on such prerequisites. |
|
re-creates 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. For information on recompiling functions, see "ALTER FUNCTION". |
|
|
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 |
|
schema |
is the schema to contain the function. If you omit schema, Oracle creates the function in your current schema. |
|
function |
is 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 |
|
|
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: |
|
|
In addition, when a function is called from within a query or DML statement, the function cannot:
|
|
|
Except for the restriction on |
|
argument |
is the name of an argument to the function. If the function does not accept arguments, you can omit the parentheses following the function name. |
|
|
specifies that you must supply a value for the argument when calling the function. This is the default. |
|
|
specifies the function will set the value of the argument. |
|
|
specifies that a value for the argument can be supplied by you and may be set by the function. |
|
|
instructs 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 |
|
|
|
|
|
These effects may or may not occur on any particular call. You should use |
|
datatype |
is the datatype of an argument. An argument 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 an argument from the environment from which the function is called. |
|
|
specifies 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. For information on PL/SQL datatypes, see PL/SQL User's Guide and Reference. |
|
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 resolves external names in queries, DML operations, and dynamic SQL statements in the function. See Also: PL/SQL User's Guide and Reference. |
|
|
|
specifies that the function executes with the privileges of
This clause also specifies that external names in queries, DML operations, and dynamic SQL statements resolve in the schema of |
|
|
specifies that the function executes 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. |
|
is an optimization hint that allows the system to 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 |
|
|
For information on materialized views, see Oracle8i Data Warehousing Guide. For information on function-based indexes, see "CREATE INDEX". |
|
|
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. See Also: Oracle8i Application Developer's Guide - Fundamentals. |
|
pl/sql_subprogram_body |
declares the function in a PL/SQL subprogram body. See Also: Oracle8i Application Developer's Guide - Fundamentals for more information on PL/SQL subprograms. |
|
call_spec |
maps 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:
|
|
|
|
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 |
The following statement creates the function GET_BAL
.
CREATE FUNCTION get_bal(acc_no IN NUMBER) RETURN NUMBER IS acc_bal NUMBER(11,2); BEGIN SELECT balance INTO acc_bal FROM accounts WHERE account_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 ACCOUNTS
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(100) FROM DUAL;
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.)
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 (...);
|
![]() Copyright © 1999 Oracle Corporation. All Rights Reserved. |
|