3.9 Considering Implementation Options

That's right. We're still not finished. As you build more and more complex programs, you should always remember the following piece of wisdom:

In PL/SQL, just as in SQL, there is always more than way to implement a requirement.

SQL is notorious (at least to me, since I am far from a SQL guru) for having many different potential solutions for a single data request. Usually 99.95% of these solutions perform horribly. The situation is similar, though perhaps less extreme, for the PL/SQL language. You can usually find at least two or three ways to solve a given problem. And it is usually worth the trouble to at least consider more than one implementation. Why? First, even though your instinctive (first) approach may work fine, another technique might be even better from the standpoints of performance, maintainability, or readability. Second, by coming at the problem from another angle, you might well uncover logical flaws in your prior implementation(s), such as unconsidered cases.

Let's take another look at the repeated function with these issues in mind. Actually, as wrote the last sentence of the previous section, I was immediately reminded of another implementation. That sentence read, in part:

"I now have a very generic function to generate string repetitions..."

I thought to myself: "Big deal. I mean, PL/SQL itself has a very generic function or two to generate repetitions of a string." These builtin functions don't perform case conversion, so I don't feel downright stupid writing my own string-repeater. It is worth, however, considering those builtins for use within the repeated function.

Which functions do I refer to as "string-repeaters"? RPAD and LPAD. These pad functions are commonly used to pad on the left or right with spaces. Yet that is simply the default mode of operation for these functions. You can pad to the specified length with any pattern of characters you want. The following use of LPAD, for example, pads the string "Eli" with the words "My son" to a length of 20 characters:

SQL> exec DBMS_OUTPUT.PUT_LINE (LPAD ('Eli', 20, 'My son ')); My son My son My Eli

Notice that it stuck "My" in three times. That's because it pads as far as possible to fill the 20 characters and then stop. I can put this builtin repeater to work quite easily in the repeated function. The only trick is to calculate the total length of the string I want to generate. Example 3.8 contains the full implementation of the RPAD version of repeated .

It is exactly the same as the FOR loop version, except that in place of the loop, I use this line:

v_retval := RPAD (initval, LENGTH (string_in) * (num_in+1), nextval);

The total length of the return value is the length of the specified string multiplied by the number of repetitions plus one. So if the user specifies zero repetitions, the total length is the same as the original string, and RPAD does nothing. If the user wants one repetition, the total length is double the original, leaving enough room for RPAD to pad initval on the right with nextval just once -- resulting in twice the original string. This pattern works for additional multiples as well.

The RPAD approach requires fewer lines of code than the loop version. For example, with RPAD I don't even have to initialize the return value variable to initval . The single assignment covers the num_in = 0 case as well as the non-trivial repetitions. Which technique should I use? More to the point, which should I make available to others to use?

The deciding factor in this case should be: which is more efficient? This is a low-level utility. It might be called many times deep down in the bowels of an application. So a minor difference in performance between the two implementations could have a multiplying effect on overall performance of the application.