Toad World® Forums

PL/SQL programming and layout guidelines

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_retcode and v_error_message are reasonably clear, but what about v_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 THEN keyword to terminate conditional expressions. For example:
    • if (x = 1)
      
    • if ((x = 1) and (y = 2))
      
    • if x = 1 and y = 2
      
  • The THEN keyword can follow on the same line when the condition is short and simple, or (more formally) it can start a new line directly below the if.
  • 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;
    

Thank your for these useful hints. Relating those to Toad:

  • OUT parameters: Toad’s Code Analysis has rule #3806 which helps detecting them.

  • Redundant parentheses in predicates: there is a new rule on its way (#6812) to detect those.

  • Avoid IF when assigning values to booleans: rule #4204 was meant to detect such constructs.

  • Booleans to improve readability: I guess we also need a new rule to detect too complex conditions suggesting the boolean go-between with a meaningful name.

Andre

Redundant parentheses in condition: Next week the following new rules will become available:

6812: Avoid parentheses around the entire condition.

6813: Avoid parentheses around simple conditions.

6814: Avoid parentheses around expressions with same precedence.

6815: Avoid parentheses around one side of a condition.

A really nice information for every beginner to know the key points while entering in to the subject.

Thank you
Sampath Reddy