PL/SQL - invoke the Functions in SQL Expressions

1.  How to invoke the Functions in SQL Expressions?

We invoke the SQL functions when activates are   too complex, too awkward, or unavailable with SQL.

By invoking functions we can increase efficiency when used in the WHERE clause to filter data, as opposed to filtering the data in the application and also we can manipulate character strings.

Advantages

• Permits calculations that are too complex, awkward, or unavailable with SQL.

• Increases data independence by processing complex data analysis within the Oracle server,

rather than by retrieving the data into an application

• Increases efficiency of queries by performing functions in the query rather than in the

application

• Manipulates new types of data (for example, latitude and longitude) by encoding character

    strings and using functions to operate on the strings.

CREATE OR REPLACE FUNCTION tax(p_value IN NUMBER)

RETURN NUMBER IS

BEGIN

RETURN (p_value * 0.08);

END tax;

/

SELECT employee_id, last_name, salary, tax(salary)

FROM employees WHERE department_id = 100;