Advanced Oracle PL/SQL Programming with Packages

3.8. Broadening the Scope

Surely I am done with twice now. It is well structured, handles errors gracefully, and offers a reasonable amount of flexibility. It has come a long way from its original one-line version. So I would have to say that, yes indeed, I am done with twice. But a few days of programming go by and I encounter a very interesting requirement:

Take a string and return it repeated it three times, not just twice!

Of course, I instantly think of twice and how it would be very easy to create another function called thrice that performs an additional concatenation—but that otherwise is unchanged. But then I take a coffee break and realize in my moment away from the screen (excellent thinking time—I recommend it to all my readers!) that tomorrow I could run into a need for four repetitions and then five. The twice function is finished—but only within its limited scope. What would be really great is a function that allows me to perform any number of duplications, as specified by the user. Now that would be a neat little function. So let's build it.

First of all, since I am going to let the user specify the number of repetitions, I will need to: (a) change the name of the function and (b) add a third parameter. Here is the new header for my new function:

CREATE OR REPLACE FUNCTION repeated 
   (string_in IN VARCHAR2, 
    action_in IN VARCHAR2 DEFAULT 'N',
    num_in IN INTEGER := 1)   
RETURN VARCHAR2

The name of the function reflects its general utility. It returns a string repeated ...