Now that naming standards are defined, here are some general guidelines for good programming practices. Many of them are universal and would apply to any programming language, even though we are only considering PL/SQL and SQL here.
OUT parameters (and why I don't like them)
A function can only return one variable, so if you need to return two or more values (perhaps forgetting about records, object types and collections), multiple OUT parameters can seem like the obvious solution. Sometimes there is a requirement (or at least a tradition) for a subprogram to provide a return code and message as OUT parameters so that the calling program can explicitly access the returned status information without having to parse SQLERRM. Now this may be a real issue with external tools that cannot handle PL/SQL functions or exceptions, but in general it is not such a great solution, because:
-
The 'Return code' approach abandons the standard exception handling model. When you call the procedure, do you trust that all possible exceptions will be converted into codes and handled, or do you (as a good defensive programmer) write an exception handler anyway? If so, didn't you just waste your time interpreting its return code? The procedure always technically 'succeeds' in that it returns without error, and so we always have to read its return code and test whether it was greater than zero or whatever the rule is.
-
OUT parameters complicate your procedure call. For example,
mypackage.do_stuff(v_account, v_invoice_id, v_order_priority, v_retcode, v_error_message);Now anyone maintaining the code has to figure out which of those arguments are IN and affect processing, OUT and pass back information, or, heaven help them, IN OUT. Note that there is no immediate way to tell which is which. In the above example,
v_retcodeandv_error_messageare reasonably clear, but what aboutv_invoice_id? Maybe the procedure generates an invoice and passes back its key. To check this, you will have to find the procedure's declaration and match each parameter against the corresponding item. If you ever have to maintain a poorly-written legacy system, you will quickly find yourself cursing OUT parameters. -
The equivalent function is more flexible as it can be called from Oracle PL SQL queries (if it returns SQL types). SQL queries cannot use procedures (although SQL can call procedures using the CALL statement.)
If you do have to provide separate return status information, you should at least consider defining a standard record type, e.g:
type status_rectype is record
( code pls_integer
, message varchar2(1000) );
v_return_status status_rectype;
Now you can call procedures using:
mypackage.do_stuff(v_account, v_invoice_id, v_order_priority, v_return_status);
Of course, OUT parameters have their uses and we are not recommending a complete ban; just that you should consider other options first.
Conditional Statements
Layout
- Do not add redundant bracketting. Notice that PL/SQL provides the
THENkeyword to terminate conditional expressions. For example:-
if (x = 1) -
if ((x = 1) and (y = 2)) -
if x = 1 and y = 2
-
- The
THENkeyword can follow on the same line when the condition is short and simple, or (more formally) it can start a new line directly below theif. - Similarly, where you have more than one condition to test, if there are only two or three and they are very simple you can place them all on one line. If there are more or they require more space, write one per line, aligned vertically:
-
if to_char(sysdate,'D') > 1 and mypackage.myprocedure(1,2,3,4) not between 1 and 99 then -
if to_char(sysdate,'D') > 1 and mypackage.myprocedure(1,2,3,4) not between 1 and 99 then -
if to_char(sysdate,'D') > 1 and mypackage.myprocedure(1,2,3,4) not between 1 and 99 then
-
- Where you have a mixture of OR and AND conditions, unless they are very simple (such as one-word boolean variables) use spaces to align bracketed conditions vertically:
-
if a = 1 and (b = 2 or c = 3 or d = 4) then -
if a = 1 and ( b = 2 or c = 3 or d = 4 ) then
-
Usage
Use Boolean Elements to Improve Readability
Boolean variables and functions can greatly improve readability of programs. You can hide complex expressions behind a name which describes the expression.
Compare the two if statements below.
-
if total_sal between 10000 and 50000 and emp_status(emp_rec.empno) = 'N' and months_between(emp_rec.hiredate, sysdate) > 10 then give_raise(emp_rec.empno); end if; -
eligible_for_raise := total_sal between 10000 and 50000 and emp_status(emp_rec.empno) = 'N' and months_between(emp_rec.hiredate, sysdate) > 10; if eligible_for_raise then give_raise(emp_rec.empno); end if;
On its own, this particular example isn't so great, but you can see that the approach could be very helpful in situations where various conditions are tested repeatedly in different combinations.
Avoid IF when assigning values to Boolean variables
Although it is valid to assign explicit TRUE and FALSE values to a Boolean, using the test expression itself as a Boolean expression is much more elegant, and saves a processing step as well as several lines of code:
-
if hiredate < sysdate then date_in_past := TRUE; else date_in_past := FALSE; end if; -
date_in_past := hiredate < sysdate;