15. Procedures and Functions

Contents:
Modular Code
Review of PL/SQL Block Structure
The Anonymous PL/SQL Block
Procedures
Functions
Parameters
Local Modules
Module Overloading
Forward Declarations
Go Forth and Modularize!

Previous parts of this book explored in detail all the components of the PL/SQL language: cursors, exceptions, loops, variables, etc. While you certainly need to know about these components when you write applications using PL/SQL, putting the pieces together to create well structured, easily understood, and smoothly maintainable programs is even more important. Because this module building process goes to the core of our purpose, it is absolutely the most critical technique for a programmer to master.

Few of our tasks are straightforward. Few solutions can be glimpsed in an instant and immediately put to paper or keyboard. The systems we build are, for the most part, large and complex, with many interacting, if not sometimes conflicting, components. Furthermore, as users deserve, demand, and receive applications that are easier to use and vastly more powerful than their predecessors, the inner world of those applications becomes correspondingly more complicated.

One of the biggest challenges in our profession today is to find a way to reduce the complexity of our environment. When faced with a massive problem to solve, a mind is likely to recoil in horror. Where do I start? How can I possibly figure out a way through that jungle of requirements and features?

A human being is not a massively parallel computer. Even the brightest of our bunch have trouble keeping track of more than seven or eight tasks at one time. We need to break down huge, intimidating projects into smaller, more manageable components, and then further decompose those components into individual programs with an understandable scope.

The best way to deal with having too much to deal with is to not deal with it all at once. Use top-down design, or "step-wise refinement," to break down a seemingly impossible challenge into smaller components. Computer scientists have developed comprehensive methodologies (for top-down design and other approaches) and performed studies on this topic -- I urge you to study their findings. When it comes to developing applications in PL/SQL, however, there is a very clear path you must take to reduce complexity and solve your problems: modularize your code!

15.1 Modular Code

Modularization is the process by which you break up large blocks of code into smaller pieces -- modules -- which can be called by other modules. Modularization of code is analogous to normalization of data, with many of the same benefits (and a few additional advantages which accrue specifically to code). With modularization, your code becomes:

More reusable. By breaking up a large program or entire application into individual components which "plug-and-play" together, you will usually find that many modules will be used by more than one other program in your current application. Designed properly, these utility programs could even be of use in other applications!
More manageable. Which would you rather debug: a 10,000-line program or five individual 2,000 line programs that call each other as needed? Our minds work better when we can focus on smaller tasks. You can also test and debug on a smaller scale (unit test) before individual modules are combined for a more complicated system test.
More readable. Modules have names and names describe behavior. The more you move or hide your code behind a programmatic interface, the easier it is to read and understand what that program is doing. Modularization helps you focus on the big picture rather than the individual executable statements.
More reliable. The code you produce will have fewer errors. The errors you do find will be easier to fix because they will be isolated within a module. In addition, your code will be easier to maintain since there is less of it and it is more readable.

Once you have mastered the different control, conditional, and cursor constructs of the PL/SQL language (the IF statement, loops, etc.), you are ready to write programs. You will not really be ready, however, to build an application until you understand how to create and combine PL/SQL modules.

PL/SQL offers the following structures which modularize your code in different ways:

Procedure: A named PL/SQL block that performs one or more actions and is called as an executable PL/SQL statement. You can pass information into and out of a procedure through its parameter list.
Function: A named PL/SQL block that returns a single value and is used just like a PL/SQL expression. You can pass information into a function through its parameter list.
Anonymous block: An unnamed PL/SQL block that performs one or more actions. An anonymous block gives the developer control over scope of identifiers and exception handling.
Package: A named collection of procedures, functions, types, and variables. A package is not really a module (it's more of a meta-module), but is so tightly related to modules that I mention it here.

I use the term module to mean either a function, a procedure, or an anonymous block, which is executed as a standalone script. As is the case with many other programming languages, modules can call other named modules. You can pass information into and out of modules with parameters. Finally, the modular structure of PL/SQL also integrates tightly with exception handlers to provide all encompassing error checking techniques.

This chapter will first review the PL/SQL block structure and anonymous blocks, and then move on to procedures and functions. The final portion of the chapter is devoted to parameters and some advanced features of PL/SQL modules, including overloading and forward referencing.