RETURN as last executable statement
Generally, the best way to make sure that your function always returns a value is to make the last executable statement your RETURN statement. Declare a variable named return_value (which clearly indicates that it will contain the return value for the function), write all the code to come up with that value, and then, at the very end of the function, RETURN the return_value, as shown here:
FUNCTION do_it_all (parameter_list) RETURN NUMBER IS return_value NUMBER;BEGIN ... lots of executable statements ... RETURN return_value;END;Here is a rewrite of the logic in Figure 16-2 to fix the problem of multiple RETURN statements.
IF sales_cur%NOTFOUNDTHEN return_value:= NULL;END IF;CLOSE sales_cur;RETURN return_value; 16.4 Parameters
Procedures and functions can both use parameters to pass information back and forth between the module and the calling PL/SQL block.
The parameters of a module are at least as important as the code that implements the module (the module's body). Sure, you have to make certain that your module fulfills its promise. But the whole point of creating a module is that it can be called, ideally by more than one other module. If the parameter list is confusing or badly designed, it will be very difficult for other programmers to make use of the module, and the result is that few will bother. And it doesn't matter how well you implemented a program if no one uses it.
Many developers do not give enough attention to a module's set of parameters. Considerations regarding parameters include:
The number of parameters
Too few parameters can limit the reusability of your program; with too many parameters, no one will want to reuse your program. Certainly, the number of parameters is largely determined by program requirements, but there are different ways to define parameters (such as bundling multiple parameters in a single record).
The types of parameters
Should you use read-only, write-only, or read-write parameters?
The names of parameters
How should you name your parameters so that their purpose in the module is properly and easily understood?
Default values for parameters
How do you set defaults? When should a parameter be given defaults, and when should the programmer be forced to enter a value?
PL/SQL offers many different features to help you design parameters effectively. This section covers all elements of parameter definition.