[Chapter 3] 3.6 Building a Structured Function

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:

 1    FUNCTION twice RETURN VARCHAR2
 2    IS
 3       v_retval VARCHAR2(100) := 'null';
 4    BEGIN
 5
 6       RETURN v_retval;
 7
 8    EXCEPTION
 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

CREATE OR REPLACE FUNCTION twice 
   (string_in IN VARCHAR2, 
    action_in IN VARCHAR2 DEFAULT 'N')
RETURN VARCHAR2
IS
   v_action VARCHAR2(10) := UPPER (action_in);
   v_retval VARCHAR2(100);
BEGIN
   IF v_action = 'UL'
   THEN
      v_retval := UPPER (string_in) || LOWER (string_in);      
      
   ELSIF v_action = 'LU'
   THEN
      v_retval := LOWER (string_in) || UPPER (string_in);      
      
   ELSIF v_action = 'N' 
   THEN
      v_retval := string_in || string_in;
   END IF; 
   RETURN v_retval;
EXCEPTION
   WHEN OTHERS
   THEN
      RETURN NULL;
END twice;
/