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:

• Consolidate business rule logic into a smaller number of well tuned and easily maintained functions. You do not have to repeat this logic across individual SQL statements and PL/SQL programs. This is probably the most far-reaching and important advantage of using functions in PL/SQL.

• Improve the performance of your SQL statements. SQL is a nonprocedural language, yet application requirements often demand procedural logic in your SQL. The SQL language is robust enough to let you get at the answer, but in some situations it is a very inefficient way to get that answer. Embedded PL/SQL can sometimes do the job much more quickly. There is also, of course, some overhead associated with calling these functions from within SQL. You will consequently need to evaluate carefully when and where PL/SQL functions in SQL will do you the most good.

• Simplify your SQL statements. All the reasons you have to modularize your PL/SQL code apply to SQL as well, particularly the need to hide complicated expressions and logic behind a function specification. From the DECODE statement to nested, correlated subselects, the readability of many SQL statements will benefit from programmer-defined functions.

• Perform actions in SQL which are otherwise impossible. SQL is a set-at-a-time language, identifying a set of rows and applying actions to those rows. It does not support iterative processing against an individual column value. Suppose you need to identify the number of occurrences of a substring within the names of companies. With pure SQL, you cannot do this. You can, on the other hand, apply a PL/SQL function to the company name in a SELECT list which can perform this kind of iterative processing.

You can place functions in a VALUES list, a SET clause, or a GROUP BY clause, as shown in the following:

• VALUES list. Consider the following example:

  INSERT INTO notes
     (call_id, line_text, line_number)
  VALUES
     (:call.call_id, :note.text, next_line_number (:call.call_id));

  The next_line_number function obtains the next sequence number for the notes for that particular call (i.e., while you might use the sequence generator to obtain the next unique call ID number, the line_number for notes for a given call always starts from one, so a sequence generator is inapplicable).

• SET clause. The max_compensation function returns the maximum compensation possible for an employee's department.

  UPDATE employee SET salary = max_compensation (department_id)
   WHERE employee_id = 1005;

  In this case the max_compensation function replaces a subselect, which would use the SQL AVG function to compute the value.

• GROUP BY clause. My company has done a very poor job of normalizing its job titles; there are 15 different variations of VICE PRESIDENT, 20 different kinds of MANAGER, and so forth. The following SELECT statement cuts through all the confusion and displays the total salary for each "category" of job.

  SELECT job_category (job_title_id) as title, SUM (salary)
    FROM employee
   GROUP BY title;

  The function is used both in the SELECT list and in the GROUP BY.