Advanced Oracle PL/SQL Programming with Packages

Advanced Oracle PL/SQL Programming with PackagesSearch this book
Previous: 3.5 Rough Waters AheadChapter 3
The PL/SQL Development Spiral
Next: 3.7 Handling Program Assumptions

3.6 Building a Structured Function

Consider the problem of a function that does not execute a RETURN. The whole point of a function is to return a value. Not only should it return a value when everything goes right, it should even return a value when the function fails and raises an exception (NULL being the usual candidate under these circumstances).

In the twice function all my RETURN statements are nested inside IF clauses. So an invalid entry by the user means that all those RETURNs are ignored. There are lots of ways to fix this specific problem. You could include an ELSE statement. You could make sure that the action was valid at the start of the function (we'll look at that in a moment). The best all-around solution, however, is to always construct your functions with the following templated structure:

 2    IS
 3       v_retval VARCHAR2(100) := 'null';
 4    BEGIN
 6       RETURN v_retval;
 9       WHEN OTHERS
10       THEN
11          RETURN NULL;
12    END twice;

In this template I declare a local variable (the return value or v_retval) with the same datatype as the function itself. I then always make the last line of the function a RETURN of the v_retval variable's value. In addition, my exception returns NULL if any kind of exception is raised. You will never get a -6503 error with this template -- and it is easier to debug than functions with RETURN statements scattered throughout the body of the program.

A version of twice that follows the template is shown in Example 3.4. Now I have a return value variable as the last line of the function body. To do this, I simply replaced each of the individual RETURN statements inside the IF statement with an assignment to v_retval. I have not, therefore, added any kind of special handling for invalid actions. Yet I no longer have to worry about -6503, because I have chosen a structure for my function that automatically rules out that possibility. Furthermore, it even returns a sensible value in the case of a bad action code. The v_retval is initialized by PL/SQL to NULL. If the user passes a code like BS, the value of v_retval will not be changed and, as a result, NULL will be returned, indicating an incorrect value (or, come to think of it, NULL input).

Example 3.4: A Template-based twice Function

   (string_in IN VARCHAR2, 
    action_in IN VARCHAR2 DEFAULT 'N')
   v_action VARCHAR2(10) := UPPER (action_in);
   v_retval VARCHAR2(100);
   IF v_action = 'UL'
      v_retval := UPPER (string_in) || LOWER (string_in);      
   ELSIF v_action = 'LU'
      v_retval := LOWER (string_in) || UPPER (string_in);      
   ELSIF v_action = 'N' 
      v_retval := string_in || string_in;
   END IF; 
   RETURN v_retval;
END twice;

Previous: 3.5 Rough Waters AheadAdvanced Oracle PL/SQL Programming with PackagesNext: 3.7 Handling Program Assumptions
3.5 Rough Waters AheadBook Index3.7 Handling Program Assumptions

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