3.6 Building a Structured Function

Chapter 3
The PL/SQL Development Spiral

 

---

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