Oracle PL/SQL Programming: A Developer's Workbook

Chapter 17. Calling Functions in SQL

As we’ve seen, you can use functions to compute and return a value for a set of input parameters. For example, you can write a lookup function that returns an employee name for a given primary key. What many developers don’t realize is that you can (within certain limits) use these functions in SQL statements, allowing you to considerably simplify complex statements. For example, you could replace messy and error-prone DECODE statements with a simple function call or eliminate complex outer-joins or subqueries with a lookup function.

This chapter describes the features that allow PL/SQL developers to embed calls to their own functions inside SQL statements and tests your ability to use these functions in SQL.

Tip

Prior to Oracle 8.1, if you wanted to call—directly or indirectly—package-based functions and procedures from within an SQL statement, it was necessary to provide RESTRICT_REFERENCE pragmas in the package specification. A number of exercises in this chapter cover this topic. As of Oracle 8.1, you’re no longer required to provide the pragmas; the PL/SQL runtime engine figures it out by itself.

Beginner

17-1.	Can you call a PL/SQL function from within a SQL statement?
17-2.	Can your own function act like a SQL group function (SUM, MIN, MAX, etc.) in a SQL statement?
17-3.	The following query contains a redundant formula to compute the total compensation of an employee (salary + commission): SELECT ename, sal + NVL (comm, 0) FROM emp WHERE sal + NVL ...