C H A P T E R  4

Functions, Packages, and Procedures

PL/SQL applications are composed of functions, procedures, and packages. Functions are PL/SQL programs that accept zero or more parameters and always return a result. Procedures are similar to functions, but they are not required to return a result. Packages are a combination of related functions, procedures, types, and variables. Each of these PL/SQL components helps formulate the basis for small and large applications alike. They differ from anonymous blocks that have been covered in previous recipes because they are all named routines that are stored within the database. Together, they provide the advantage of reusable code that can be called from any schema in the database to which you've granted the appropriate access.

Let's say you have a few lines of code that perform some calculations on a number and return a result. Will these calculations help you anywhere else? If so, then you should probably encapsulate this code in a function. Maybe you have a nightly script that you use as a batch job to load and execute. Perhaps this script can be turned into a stored procedure and Oracle Scheduler can kick it off each night. What about tasks that use more than one procedure or function? Can these be combined at all? A PL/SQL package would probably be a good choice in this case. After reading through the recipes in this chapter, you should be able to answer these questions at the drop of a hat.