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 any number of times. The third parameter, num_in , indicates the number of times to repeat the string. Notice that the default is 1, which means a single repetition -- thereby matching the functionality of twice . Otherwise the parameter list is the same.

It probably won't take much thought on your part to realize two things about the implementation of repeated :

1. I can use a numeric FOR loop in a very straightforward way to create a string which repeats a substring N times.

2. The case conversion logic that applied itself so clearly in twice is less obvious now. If users specify UPPER-lower, do they want UPPER-lower-UPPER-lower or do they want UPPER-lower-lower-lower?

There is only one answer to this question: I don't know. A different user may want or expect a different outcome. As the creator of repeated , I can either build the function to handle both these two scenarios and other case conversion options, or simply decide that repeated will offer only one option.

In this chapter, I implement repeated in such a way that its case conversion is limited to applying the first half of the conversion to the input string and second half of the conversion to all the repetitions of that string. The following example shows what repeated will do:

SQL> exec DBMS_OUTPUT.PUT_LINE (repeated ('abc','UL',2)); ABCabcabc SQL> exec DBMS_OUTPUT.PUT_LINE (repeated ('abc','LU',2)); abcABCABC

I will leave it to my readers to come up with an implementation of repeated that offers other patterns (or all patterns).[ 3 ] The full implementation of repeated is shown in Example 3.7 . Here I step through that implementation.

[3] Please send me your solutions at Compuserve 72053,441.

The first thing I want to do in repeated is assert the validity of all of my assumptions. I have the same assumption for action that twice did, but I have another assumption as well: that the num_in argument will not be negative. So repeated will add this call to assert:

assert    (num_in >= 0, 'Duplication count must be at least 0.');

Once I know that my arguments are all right, I can proceed to my algorithm. With my new approach to case conversion, I have two different kinds of strings for repetition: the initial string and the repetition string. The cases of these two strings need to be set separately (as you read this section, see if you can tell how twice is only a special case of this logic), based on the action code. I do this in the following IF statement:

   IF v_action = 'UL'    THEN       initval := UPPER (string_in);          nextval := LOWER (string_in);    ELSIF v_action = 'LU'    THEN       initval := LOWER (string_in);             nextval := UPPER (string_in);    ELSE       initval := string_in;             nextval := string_in;    END IF; 

Once I have set the initial and repetition (or next) strings, I can set the initial value for the return value and then use a FOR loop to generate the repeated string:

   v_retval := initval;    FOR dup_ind IN 1 .. num_in    LOOP       v_retval := v_retval || nextval;    END LOOP;

And the return value variable is then ready to be RETURNed by the function.

Now that repeated is coded, let's walk through that code for some specific argument values to see if my logic holds up.