17. Calling PL/SQL Functions in SQL
PL/SQL is a procedural language extension to SQL, so you can also issue native calls to SQL statements such as SELECT, INSERT, and UPDATE from within your PL/SQL programs. Until Release 2.1 of PL/SQL (which comes with Oracle7 Release 7.1 of the RDBMS), however, you weren't able to place your own PL/SQL functions inside a SQL statement.
17.1 Looking at the Problem
The restriction on putting PL/SQL functions inside an SQL statement often resulted in cumbersome SQL statements and redundant implementation of business rules. Suppose, for example, you need to calculate and use an employee's total compensation both in native SQL and also in your forms. The computation itself is straightforward enough:
Total compensation = salary + bonus
My SQL statement would include this formula:
SELECT employee_name, salary + NVL (bonus, 0) FROM employee;
while my Post-Query trigger in my Oracle Forms application would employ the following PL/SQL code:
:employee.total_comp := :employee.salary + NVL (:employee.bonus, 0);
In this case, the calculation is very simple, but the fact remains that if you need to change the total compensation formula for any reason (different kinds of bonuses, for example), you would then have to change all of these hardcoded calculations both in the SQL statements and in the front end application components.
A far better approach is to create a function that returns the total compensation:
FUNCTION total_comp (salary_in IN employee.salary%TYPE, bonus_in IN employee.bonus%TYPE) RETURN NUMBER IS BEGIN RETURN salary_in + NVL (bonus_in, 0); END;
Then I could replace the formulas in my code as follows:
SELECT employee_name, total_comp (salary, bonus) FROM employee; :employee.total_comp := total_comp (:employee.salary, :employee.bonus);
Until Release 2.1 of PL/SQL the above SELECT statement raised the following error:
ORA-00919: invalid function
because there was no mechanism for SQL to resolve references to programmer-defined functions stored in the database. Now, Oracle has made the relationship between PL/SQL and SQL more of a two-way street. This makes sense, since the functions are stored in the database (in tables, of course) and therefore easily accessible at the SQL layer via a SELECT statement.
With PL/SQL Release 2.1, you can now call stored functions anywhere in a SQL statement where an expression is allowed, including the SELECT, WHERE, START WITH, GROUP BY, HAVING, ORDER BY, SET, and VALUES clauses (since stored procedures are in and of themselves PL/SQL executable statements, they cannot be embedded in a SQL statement).
You can use one of your own functions just as you would a built-in SQL function such as TO_DATE or SUBSTR or LENGTH. On the disk I've included a package, ps.parse (filenames psparse.sps and psparse.spb ) that includes a function that returns the number of atomics (words and/or delimiters) in a string. I can employ this directly in a SQL statement to show the distribution of words in a series of textual notes, as follows:
SELECT line_number, ps_parse.number_of_atomics (line_text) AS num_words FROM notes ORDER BY num_words DESC;
Notice that, in this case, I have assigned a column alias to my function call using the "AS" syntax. I can then use that alias in the ORDER BY without having to repeat the syntax for the function call itself.
The ability to place programmer-defined PL/SQL functions inside SQL is a very powerful enhancement to the Oracle development environment. With these functions you will be able to do the following:
You can place functions in a VALUES list, a SET clause, or a GROUP BY clause, as shown in the following:
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.