Oracle PL/SQL Programming, 2nd Edition

Oracle PL/SQL Programming, 2nd EditionSearch this book
Previous: IV. Modular Code Chapter 15Next: 15.2 Review of PL/SQL Block Structure

15. Procedures and Functions

Modular Code
Review of PL/SQL Block Structure
The Anonymous PL/SQL Block
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:

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:


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.


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.


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.

Previous: IV. Modular Code Oracle PL/SQL Programming, 2nd EditionNext: 15.2 Review of PL/SQL Block Structure
IV. Modular Code Book Index15.2 Review of PL/SQL Block Structure

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