Oracle7 Server SQL Reference
User Functions
You can write your own user functions in PL/SQL to provide functionality that is not available in SQL or SQL functions. User functions are used in a SQL statement anywhere SQL functions can be used; that is, wherever expression can occur.
For example, user functions can be used in the following:
- the select list of a SELECT command
- the condition of a WHERE clause
- the CONNECT BY, START WITH, ORDER BY, and GROUP BY clauses
- the VALUES clause of an INSERT command
- the SET clause of an UPDATE command
For a complete description on the creation and usage of user functions, see Oracle7 Server Application Developer's Guide.
Prequisites
User functions must be created as top-level PL/SQL functions or declared with a package specification before they can be named within a SQL statement. User functions are created as top-level PL/SQL functions by using the CREATE FUNCTION statement described
. Packaged functions are specified with a package with the CREATE PACKAGE statement described
.
To call a packaged user function, you must declare the RESTRICT_REFERENCES pragma in the package specification.
Privileges Required
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.
Restrictions on User Functions
User functions cannot be used in situations that require an unchanging definition. Thus, a user function:
- cannot be used in a CHECK constraint clause of a CREATE TABLE or ALTER TABLE command
- cannot be used in a DEFAULT clause of a CREATE TABLE or ALTER TABLE command
- cannot contain OUT or IN OUT parameters
- cannot update the database
- cannot read or write package state if the function is a remote function
- cannot use the parallelism_clause in SQL commands in the function if the function alters package state
- cannot update variables defined in the function unless the function is a local function and is used in a SELECT list, VALUES clause of an INSERT command, or SET clause of an UPDATE command
Name Precedence
With PL/SQL, 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 ,,,;
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;
Example I
For example, 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;
Example II
Listed below are 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
Naming Conventions
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:
- check for the PAYROLL package in the current schema
- if a 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, an error message is returned
- if the PAYROLL package is found in the current schema, look for a TAX_RATE function in the PAYROLL package; if no such function is found, an error message is returned
You can also refer to a stored top-level function using any synonym that you have defined for it.