15. Procedures and Functions
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:
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:
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.
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.