PLSQL Tutorial - 
The 7 Worst Crimes Committed In Oracle PL/SQL 

In this PLSQL tutorial we're going to look at 7 common coding crimes - i.e. the things you shouldn't do if you want your PL/SQL code to be fast, efficient and easy to maintain. We're also going to see examples of good coding without which this PLSQL tutorial wouldn't be complete. 

1. Using PL/SQL when pure SQL would be faster

Have you ever seen this sort of thing in PL/SQL?

FOR rec IN (SELECT id FROM employee) LOOP

     UPDATE employee 
     SET salary=salary*1.1
     WHERE employee.id=rec.id;

END LOOP;

This sort of sloppy coding gives all developers a bad name and is rightly top of the list of crimes in our PLSQL tutorial.

Let's disect what's wrong with it. Firstly, the context switching between SQL and PL/SQL is likely to make it very slow. Secondly, the table is being updated one row at a time which means many more update statements have to be executed - again likely to make it very slow - and thirdly, it's just completely unnecessary.

A much faster method of doing this both in terms of execution speed and time to write the code is as a SQL statement like this:-

UPDATE employee SET salary=salary*1.1


You don't need PL/SQL at all for this!

2. Nested PL/SQL Cursor FOR LOOPs

Crime #2 in our PLSQL tutorial of the 7 worst crimes is another common one. In this case the crime is the nesting of cursor FOR LOOPs and using the outer cursors only to drive the inner cursor.

For example:

FOR dep_rec IN (SELECT id FROM department) LOOP

FOR emp_rec IN (SELECT id FROM employee WHERE dept_id=dep_rec.id) LOOP

       <do something with employee record>

   END LOOP;

END LOOP;

In this case the two cursors should be combined into one. Look out for this in anything that runs slowly. Fixing this could reduce run time by hours.

3. Monolithic PL/SQL blocks

This is crime #3 in our PLSQL tutorial.


Oracle Tips & Tricks to SKYROCKET Your Career!

If you're not already a subscriber you're  missing out on a myriad of tips and techniques to help you become a better, faster, smarter developer. Subscribe now and ignite your career.


As PL/SQL is a block structured language you can decompose your programs into functions, procedures and anonymous blocks and group these blocks into packages. Also, each type of block may be nested in any other type of block. I.e functions can be nested inside procedures, anonymous blocks and other functions; procedures can be nested inside functions, anonymous blocks and other procedures; and anonymous blocks can be nested inside procedures, functions and other anonymous blocks

This means there is no need for blocks that go on for ever. Doing this makes your code hard to debug, almost impossible to maintain and very expensive - it may even have to be completely rewritten to make changes so that the next developer can understand the code.

Take a few extra minutes to think about your PL/SQL code and break it up into procedures, functions and packages and save your successors hours (if not days) of head scratching, swearing and frustration.

4. Complex, multi-level, nested IF statements

Crime #4 in this PLSQL tutorial can aslo cause hours of frustration and worse it can lead to subtle, infrequent and, therefore, very hard to detect bugs.

PL/SQL IF statements need to be written so that they can be  understood quickly and easily, otherwise every now and then a set of conditions may occur which fall through the cracks and cause unintended consequences. These sorts of bugs can be very difficult to track down becuae they occur so infrequently. 

Ensure you code your IF statements carefully. Break them up using white space (blank lines), encapsulate complex logic and lengthy sections of code in functions and procedures and make sure you include some comments on why you're doing things not just what you're doing.

IF a or j or k AND z or x THEN
  IF b AND NOT z OR x THEN
    IF c THEN 
      IF a OR d THEN <do something step 1>
                      ..
           <do something step 99>
      ELSE <do something different step 1>
           
<do something different step 2>
           <do something different step 3>
                      ..
           <do something different step 99>
      END IF;
    ELSE <do something else here> 
    END IF; 
  ELSE <do something else different here>
  END IF;
END IF:


This is much easier to follow when rewritten to use functions and procudeures, something like this:

IF employee_due_for_payrise THEN /* >6 mths service etc */
  IF bonus_conditions_met THEN /* good employee */
    IF long_service_conditions_met THEN
      IF model_employee or likely_to_leave THEN award_max_payrise; ELSE award_min_payrise; END IF:
    ELSE <do something else here> 
    END IF; 
  ELSE <do something else different here>
  END IF;
END IF;

By using nested procedures and functions you can encapsulate and hide all the complexity you want and make the logic much easier to follow, easier to read and easier to maintain.

5. EXCEPTION WHEN OTHERS THEN NULL;

This one is number 5 on our list of the worst PL/SQL crimes. Essentially what is happening here is that in the absence of any specific named exception handlers, all Oracle database errors are trapped and ignored. No errors are reported. Nothing!

To be fair, it is occasionally acceptable for a very specific situation with a limited number of possible errors but it shouldn't be used indiscriminately. In nearly every case this catch-all exception handler should be replaced with a handler for a named exception.

6. EXCEPTION WHEN <Oracle exception> THEN RAISE;

Crime number 6 in our PLSQL tutorial is not quite as bad as number 5 but only because it doesn't affect anything. In this case there's no point in having a local exception handler if all it does is RAISE it to be dealt with by the enclosing block. If an exception occurs and there is no exception handler in the current block then Oracle will automatically propagate the exception to the enclosing block.

7. Jumping out of PL/SQL LOOPs 

The final crime in our PLSQL tutorial is jumping out of loops. There are 2 ways to do this - using the GOTO statement and using EXIT [WHEN]-neither of which are necessary.

Example 1 - PL/SQL FOR LOOP with EXIT WHEN statement

DECLARE
  emp_rec employees%ROWTYPE;
  CURSOR emp_cur is SELECT * FROM employees;
BEGIN
  OPEN emp_cur;
  FOR i IN 1..10 LOOP
    FETCH emp_cur INTO emp_rec;
    EXIT WHEN emp_cur%NOTFOUND;
    <do some processing here>
  END LOOP;
  CLOSE emp_cur;
END;

Example 2 - PL/SQL FOR LOOP with GOTO statement

DECLARE
  emp_rec employees%ROWTYPE;
  CURSOR emp_cur is SELECT * FROM employees;
BEGIN
  OPEN emp_cur;
  FOR i IN 1..10 LOOP
    FETCH emp_cur INTO emp_rec;
    IF emp_cur%NOTFOUND THEN GOTO continue; END IF;
    <do some processing here>
  END LOOP;
<<continue>>
  CLOSE emp_cur;
END;

Both of these could be rewritten without having to exit the loop prematurely.

Example 3 - PL/SQL FOR LOOP as it should be

BEGIN
  FOR emp (SELECT * FROM employees WHERE rownum <= 10) LOOP
    <do some processing here>
  END LOOP;
END;


Learn PL/SQL fast. See our partner Smartsoft for instructor-led Oracle PL/SQL training in the UK 

Return to Introductory PLSQL Tutorial for an overview of pl/sql.