O'Reilly logo

Advanced Oracle PL/SQL Programming with Packages by Steven Feuerstein

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required