#

| Chapter 3 The PL/SQL Development Spiral |

## 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.

#### Example 3.7: The repeated Function

>     CREATE OR REPLACE FUNCTION repeated
>        (string_in IN VARCHAR2,
>         action_in IN VARCHAR2 DEFAULT 'N',
>         num_in IN INTEGER := 1)
>     RETURN VARCHAR2
>     IS
>        v_action VARCHAR2(10) := UPPER (action_in);
>        initval VARCHAR2(32767);
>        nextval VARCHAR2(32767);
>        v_retval VARCHAR2(32767) := string_in;

>     BEGIN
>        assert
>           (v_action IN ('UL', 'LU', 'N'),
>            'Please enter UL LU or N');
>        assert
>           (num_in >= 0,
>            'Duplication count must be at least 0.');

>        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;

>        v_retval := initval;
>        FOR dup_ind IN 1 .. num_in-1
>        LOOP
>           v_retval := v_retval || nextval;
>        END LOOP;
>        RETURN v_retval;
>     END duploop;
>     /

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

### 3.8.1 When the num_in Argument Is 0

This is a boundary check. Zero is the lowest allowable value for `num_in`. Any test case that lies on the boundary of a range of values is a prime candidate for failure. How often have you written an algorithm that works fine in general, but which breaks down exactly on the low or high end or another kind of special case that is perfectly valid?

When `num_in` is 0, the FOR loop does not execute even once. Therefore, the return value is set to the initial value and that is what is returned: the string passed in by the user, converted as specified, repeated zero times.

The repeated function checks out for `num_in` equal to 0. Of course, I should and do execute the function for this case as well, but the code walkthrough comes first. You should be able to deduce logically that your code runs fine before you run it. Here goes:

>     SQL> exec DBMS_OUTPUT.PUT_LINE (repeated ('abc','UL',0));
>     ABC

### 3.8.2 When string_in Is NULL

Another special case. The twice function handles it smoothly since NULL concatenated to NULL is still NULL. Will repeated act any differently? The answer is no. It might execute more concatenations, but it still will return NULL when a NULL string is passed to it for the first argument.

In addition to my code walkthroughs for these cases, I executed repeated for a variety of different inputs and it seems to work just fine. I now have a very generic function to generate string repetitions with case conversion.

3.7 Handling Program Assumptions |  | 3.9 Considering Implementation Options

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.