Oracle PL/SQL Programming, 2nd Edition

Oracle PL/SQL Programming, 2nd EditionSearch this book
Previous: 16.6 Package InitializationChapter 17Next: 17.2 Syntax for Calling Stored Functions in SQL
 

17. Calling PL/SQL Functions in SQL

Contents:
Looking at the Problem
Syntax for Calling Stored Functions in SQL
Requirements for Stored Functions in SQL
Restrictions on PL/SQL Functions in SQL
Calling Packaged Functions in SQL
Column/Function Name Precedence
Realities: Calling PL/SQL Functions in SQL
Examples of Embedded PL/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.

NOTE: The capabilities described in this chapter are available only in PL/SQL Release 2.1 and above.

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:


Previous: 16.6 Package InitializationOracle PL/SQL Programming, 2nd EditionNext: 17.2 Syntax for Calling Stored Functions in SQL
16.6 Package InitializationBook Index17.2 Syntax for Calling Stored Functions in SQL

The Oracle Library Navigation

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.

Library Home Oracle PL/SQL Programming, 2nd. Ed. Guide to Oracle 8i Features Oracle Built-in Packages Advanced PL/SQL Programming with Packages Oracle Web Applications Oracle PL/SQL Language Pocket Reference Oracle PL/SQL Built-ins Pocket Reference