Advanced Oracle PL/SQL Programming with Packages

Advanced Oracle PL/SQL Programming with PackagesSearch this book
Previous: 3.1 The Basic ProblemChapter 3
The PL/SQL Development Spiral
Next: 3.3 Supplying Backward Compatibility
 

3.2 Adding Value

Sometimes it is worth stepping back and searching for the bigger picture before embarking on one's enhancements. In this case, I find myself wondering what other twists and turns I might encounter in my application development. Since I need UPPER-lower string duplication, I might also run into a requirement to perform lower-UPPER string duplication. As long as I am changing the twice function for one of these variations, I should try to stay ahead of the game and handle both variations.

So I will restate the new requirements of twice: double the specified string. Return the new string with the same case as the original, and return it in UPPER-lower or return it in lower-UPPER, depending on the user request.

When stated in this way, an obvious question pops up: how is the user going to specify the case handling in the call to twice? For a standalone function, this means adding a parameter. Instead of just accepting the string value for doubling, twice must also receive the type of action to perform. The new header for twice, therefore, must be:

FUNCTION twice (string_in IN VARCHAR2, action_in IN VARCHAR2)

where the action can be one of these values:

N

No change to case

UL

UPPER-lower case conversion

LU

lower-UPPER case conversion

Once the parameter and valid options are in place, the implementation is straightforward (and is shown in Example 3.2). I simply use an IF statement to direct the runtime engine to the right RETURN statement.

Example 3.2: The twice Function with Alternative Actions

CREATE OR REPLACE FUNCTION twice 
   (string_in IN VARCHAR2, action_in IN VARCHAR2)
RETURN VARCHAR2
IS
BEGIN
   IF action_in = 'UL'
   THEN
      RETURN (UPPER (string_in) || LOWER (string_in));      
      
   ELSIF action_in = 'LU'
   THEN
      RETURN (LOWER (string_in) || UPPER (string_in));      
      
   ELSIF action_in = 'N' 
   THEN
      RETURN string_in || string_in;
   END IF; 
END twice;

With this new version of twice, I can display the following string doublings:

SQL> exec DBMS_OUTPUT.PUT_LINE (twice ('abc', 'UL'));
ABCabc
SQL> exec DBMS_OUTPUT.PUT_LINE (twice ('abc', 'LU'));
abcABC
SQL> exec DBMS_OUTPUT.PUT_LINE (twice ('abc', 'N'));
abcabc

My twice function is starting to look interesting. It handles a number of different flavors of conversion and seems easy to use. I'm glad I decided to enhance twice.


Previous: 3.1 The Basic ProblemAdvanced Oracle PL/SQL Programming with PackagesNext: 3.3 Supplying Backward Compatibility
3.1 The Basic ProblemBook Index3.3 Supplying Backward Compatibility

The Oracle Library Navigation

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

Library Home Oracle PL/SQL Programming, 2nd. Ed. Guide to Oracle 8i Features Oracle Built-in Packages Advanced PL/SQL Programming with Packages Oracle Web Applications Oracle PL/SQL Language Pocket Reference Oracle PL/SQL Built-ins Pocket Reference
This HTML Help has been published using the chm2web software.