3. The PL/SQL Development Spiral

Contents:
The Basic Problem
Adding Value
Supplying Backward Compatibility
Improving the User Interface
Rough Waters Ahead
Building a Structured Function
Handling Program Assumptions
Broadening the Scope
Considering Implementation Options
Choosing the Best Performer
Don't Forget Backward Compatibility
Obliterating the Literals
Glancing Backward, Looking Upward

How many times have you written a program, gotten it to compile first time around, and found that it had no bugs? Let me rephrase that question: have you ever written a program of more than, say, five lines that compiled and executed without bugs the first time around? Please send me your resume if the answer is yes. We need developers like you. I will make a confession: I have never once been able to get it right the first time. Perhaps I am just too impatient to walk through my code properly. I certainly haven't found the patience to discover the joys of computer-assisted software engineering. I am just a hacker at heart.

Even with lots of patience and prior analysis, however, I believe that it is wrong to set as a realizable objective to "get it right the first time." Software development should be seen largely as an iterative process. You get closer and closer to "perfection" as you take multiple passes at a solution. I like to think of this process as a spiral towards excellence in code. A spiral is different from a cycle, which is the term often used to portray the, well, "lifecycle" of development. A cycle or circle has you coming back around to where you were before. A spiral implies that when you come back around, you are at a higher place than you were on the previous spin. You are closer to the ideal.

There are many ways to apply this philosophical thinking to PL/SQL development. The single most important non-technical (i.e., not specific to computers) skill is that of problem-solving. The single most important technical skill to nurture as a programmer is that of code modularization. In this chapter, I explore the so-called spiral towards excellence in the context of an exercise in building a very basic and -- on the surface -- trivial PL/SQL utility.[ 1 ]

[1] All of the code for this chapter is included on the companion disk in the file spiral.all .

Most of this chapter traces the evolution of a standalone function. Why, in a book about packages, am I showing you how to build this function? First, the lessons you will absorb from this chapter apply very directly to the more complex work on package construction. Second, the best solution to the problem addressed by my function turns out to be a package!

3.1 The Basic Problem

Let's suppose that I am writing an application that does lots of string manipulation. One action that I find myself coding again and again is the duplication of a string. In other words, I need to convert "abc" to "abcabc". I write this:

v_new_string := v_old_string || v_old_string;

and then I write this:

v_comp_name := v_comp_name || v_comp_name;

and I sense a pattern.[ 2 ]

[2] You are not allowed to wonder why I would need to do something like this. I am not obligated to construct a real-life application that does this. You just have to take my word for it.

Whenever I notice a repetition in my coding, I instinctively put on the brakes and examine more closely. A pattern implies that I can generalize to a formula. I can then encapsulate my formula into a function or procedure. I can then write the formula once and apply it often. This cuts down on my typing and improves my ability to maintain and even enhance my code.

In this case, the pattern is clear: I want to double the supplied string and return it to another PL/SQL variable. I want, in other words, to create a function. So in very quick order I write the twice function as shown in Example 3.1 :

Example 3.1: The First Version of the twice Function

CREATE OR REPLACE FUNCTION twice (string_in IN VARCHAR2)
RETURN VARCHAR2
IS
BEGIN
   RETURN string_in || string_in;
END twice;
/

With twice created in the database, I can replace those two explicit concatenations with these calls to twice :

v_new_string := twice (v_old_string);
v_comp_name := twice (v_comp_name);

I have added another fine implement to my toolbox and I continue on my merry, programming way. Lo and behold, just as I would have predicted, I soon run into the need for twice again. This time, I want to double-up the description of a product type, but I also need to make sure that it is in upper case, so I type:

v_prodtype:= twice (UPPER (v_prodtype));

So far so very good. I code my little heart out until I run into a new variation on my twice theme: I want to double the string, but this time I need to uppercase the first instance and lowercase the second. It doesn't take too lengthy an analysis to conclude that the twice function cannot handle this requirement.

I now face a crucial and common modularization dilemma: should I try to enhance twice to handle this new twist or should I leave it as is and build yet another function for UPPER-lower? A part of me would like to tell you that this isn't really much of a dilemma, that you should always widen the scope of your existing program. That would be the most elegant solution, but it would also be irresponsible advice.

We write programs so people can use them, not so we can marvel at their elegance. There are definitely situations in which it makes more sense to leave well enough alone and start with a brand-new build. I am not yet ready to give up on twice , for several reasons:

The point of this chapter is to step you upwards through the spiral. Abandoning the function now would be to start a new spiral, not climb this one.
The twice function is still very basic and unexplored from the standpoint of scope expansion. It is at least worth trying to incorporate this new requirement. If it proves unnatural, then the effort ought to be abandoned.

Once I have made this analysis, I am ready to revamp the twice function in order to add value for the end user (currently myself, but in time others as well).