Oracle8i SQL Reference Release 2 (8.1.6) A76989-01 |
|
Functions, 121 of 121
You can write user-defined functions in PL/SQL or Java to provide functionality that is not available in SQL or SQL functions. User functions can appear in a SQL statement anywhere SQL functions can appear, that is, wherever an expression can occur.
For example, user functions can be used in the following:
SELECT
statement
WHERE
clause
CONNECT
BY
, START
WITH
, ORDER
BY
, and GROUP
BY
clauses
VALUES
clause of an INSERT
statement
SET
clause of an UPDATE
statement
See Also:
User functions must be created as top-level functions or declared with a package specification before they can be named within a SQL statement. Create user functions as top-level functions by using the CREATE
FUNCTION
statement described in "CREATE FUNCTION". To specify packaged functions, see "CREATE PACKAGE".
To use a user function in a SQL expression, you must own or have EXECUTE
privilege on the user function. To query a view defined with a user function, you must have SELECT
privileges on the view. No separate EXECUTE
privileges are needed to select from the view.
Within a SQL statement, the names of database columns take precedence over the names of functions with no parameters. For example, if user SCOTT
creates the following two objects in his own schema:
CREATE TABLE emp(new_sal NUMBER, ...); CREATE FUNCTION new_sal RETURN NUMBER IS BEGIN ... END;
then in the following two statements, the reference to NEW_SAL
refers to the column EMP
.NEW_SAL
:
SELECT new_sal FROM emp; SELECT emp.new_sal FROM emp;
To access the function NEW_SAL
, you would enter:
SELECT scott.new_sal FROM emp;
Here are some sample calls to user functions that are allowed in SQL expressions:
circle_area (radius) payroll.tax_rate (empno) scott.payroll.tax_rate (dependent, empno)@ny
To call the TAX_RATE
user function from schema SCOTT
, execute it against the SS_NO
and SAL
columns in TAX_TABLE
, and place the results in the variable INCOME_TAX
, specify the following:
SELECT scott.tax_rate (ss_no, sal) INTO income_tax FROM tax_table WHERE ss_no = tax_id;
If only one of the optional schema or package names is given, the first identifier can be either a schema name or a package name. For example, to determine whether PAYROLL
in the reference PAYROLL.TAX_RATE
is a schema or package name, Oracle proceeds as follows:
PAYROLL
package in the current schema.
PAYROLL
package is not found, look for a schema name PAYROLL
that contains a top-level TAX_RATE
function. If no such function is found, return an error.
PAYROLL
package is found in the current schema, look for a TAX_RATE
function in the PAYROLL
package. If no such function is found, return an error.
You can also refer to a stored top-level function using any synonym that you have defined for it.
|
![]() Copyright © 1999 Oracle Corporation. All Rights Reserved. |
|