111. User Defined Functions - llighter/database GitHub Wiki

User-Defined Functions Oracle Help Center

User-Defined Functions

User-defined functions can appear in a SQL statement anywhere SQL functions can appear.

For example, user-defined functions can be used in the following:

  • The select list of a SELECT statement
  • The condition of a WHERE clause
  • CONNECT BY, START WITH, ORDER BY, and GROUP BY clauses
  • The VALUES clause of an INSERT statement
  • The SET clause of an UPDATE statement

Prerequisites

User-defined functions must be created as top-level functions or declared with a package specification before they can be named within a SQL statement.

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.

Name Precedence

Within a SQL statement, the names of database columns take precedence over the names of functions with no parameters. For example, if the Human Resources manager creates the following two objects in the hr schema:

CREATE TABLE new_emps (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 new_emps.new_sal:

SELECT new_sal FROM new_emps;
SELECT new_emps.new_sal FROM new_emps;

To access the function new_sal, you would enter:

SELECT hr.new_sal FROM new_emps;

Example

To call the tax_rate user function from schema hr, execute it against the ss_no and sal columns in tax_table, specify the following:

SELECT hr.tax_rate (ss_no, sal)
    INTO income_tax
    FROM tax_table WHERE ss_no = tax_id;

The INTO clause is PL/SQL that lets you place the results into the variable income_tax.