PLSQL Best Practice

There are many ways to write PLSQL code - some good, some bad and some in between. Hopefully this article will provide you with a few tips on how to make sure your PL/SQL code is in the good category - that is code that is easy to debug and easy (and cheap) to maintain and code that gets the job done whilst using the minimum amount of resources.

So let's get started.

Use PLSQL Packages

Packages enable you to group related objects together. This includes functions, procedures, type declarations and variables such as cursors.

PLSQL packages also have the advantage that the whole package (i.e. all of the objects in the

package) are loaded into memory the first time any object within the package is referenced. This makes subsequent access to any objects in the package much faster as it is already in memory.

If necessary, packages can be pinned (fixed) in memory by using the procedure DBMS_SHARED_POOL.KEEP so that they are not aged out. This is useful for very large and/or frequently used packages, although this has to be balanced against the reduction in memory available to cache other objects.

Use plenty of white space in your PLSQL code

Using white space (blank lines, extra spacing, indentation) helps to make your code more readable and therefore easier and cheaper to maintain.

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.

No matter how much white space you put in your PL/SQL code, you can't use too much. There's no need to worry about performance effects - when the code is compiled, all the white space is stripped out. The liberal use of white space (along with comments) is especially important for separating blocks of code. By making the code easier on the eye, you make it easier on the brain (to figure out what's going on).

What you're aiming at is for your code to look pretty - literally. To do this make declarations, operators, comments etc, line up (as much as possible) and separate different elements even when they don't need to be separated. It may take you a little longer to write the code in the first place but you'll save more than this by having to spend less time debugging it later.

This first example of an anonymous block uses no extra white space at all and is therefore hard to read.

DECLARE index_for_loop PLS_INTEGER; j NUMBER(3);
BEGIN FOR emp IN (SELECT first_name||' '||last_name emp_name FROM departments JOIN employees USING (department_id)) LOOP DBMS_OUTPUT.PUT_LINE(emp.emp_name); END LOOP; END;

This is how it looks after tidying up:

DECLARE

  index_for_loop PLS_INTEGER;
j NUMBER(3);

BEGIN

    FOR emp IN (

        SELECT first_name||' '||last_name emp_name
        FROM departments
JOIN employees USING (department_id)
        WHERE department_name = 'IT'

         ) LOOP

        DBMS_OUTPUT.PUT_LINE(emp.emp_name);

    END LOOP;

END;

Sprinkle your PLSQL code liberally with comments

Adding plenty of comments to your code to explain why things are being done (as opposed to just what is being done) again makes your code easier to follow and therefore easier to debug and to maintain.

Single line comments

PLSQL comments come in two types - single line and multi-line. Single line comments start with '--' and finish at the end of the line. This type of comment is useful for commenting a particular line of code. For example:

check_cust_credit; -- check limit and no payments over due

The important point to note is that anything after the "--" is considered part of the comment and is therefore ignored by the compiler.

So this whole line is a comment:

--check_cust_credit; --check limit and no payments over due

The comment doesn't have to be at the end of the line of code, it could be underneath but obviously it's worthless if it's not clear to which line it applies.

To have the comment run over more than 1 line you just need to precede each comment with '--'.

For example:

-- this is a
-- comment
-- spread over
-- multiple lines

Mutli-line comments

The other way to create comments is to wrap them in '/*' and '*/'. This type of comment can be dangerous because the comment doesn't finish automatically at the end of the line, instead it continues until ended with '*/'. This makes it easy to create multi-line comments and easy to comment out whole sections of code - either on purpose or accidentally! Note also that you can't nest comments.

/* So this is
/* just 1 comment
closed by this tag */

-- this is also just /*1*/ comment,everything after-- is ignored
/* comments can't be nested /* this comment ends with */

Use sub-programs

Use PLSQL sub-programs to give your code some structure. There's nothing worse than a huge monolithic block of code (except one with no comments).

Some developers believe that performance will suffer if they use sub programs but poor performance is more often caused by poor design or untuned SQL statements. Also if the calling of sub-programs is found to be the cause of poor performance then you can instruct the PL/SQL complier to convert them to inline code by changing the optimiser level.

With the Oracle PLSQL_OPTIMIZE_LEVEL compilation parameter set to 2 you specify explicitly which subprograms should be inlined. With the PLSQL_OPTIMIZE_LEVEL compilation parameter set to 3 the compiler will automatically seek to convert all subprogram calls to inline code unless you explicitly exclude a specific call.

Explicitly enabling or disabling inlining is controlled by use of the INLINE pragma before a procedure call - PRAGMA INLINE (<proc>, <flag>) where <proc> is the name of the subprogram being called and <flag> is either 'YES' or 'NO'.

For example, with the PLSQL_OPTIMIZE_LEVEL compilation parameter set to 2, this instruction will enable the procedure call to be converted to inline code.

PROCEDURE my_proc IS ...

PRAGMA INLINE (my_proc,'YES');
my_proc;

With the PLSQL_OPTIMIZE_LEVEL compilation parameter set to 3, this instruction will prevent the procedure call from being converted to inline code.

PRAGMA INLINE (my_proc,'NO');
my_proc;

Store procedures in the database

Storing procedures in the database means you can't lose them. It also improves performance as the server is likely to be much more powerful than the client and reduces network traffic by obviating the need to send intermediate results back and forth between the client and the server.

We have only provided a few tips here, not a detailed reference which can be found in the PL/SQL language reference manual. If you want to learn more about PLSQL our partners in New Zealand and in the UK offer instructor-led training on PL/SQL (as well as other aspects of Oracle).

Return to the introductory PLSQL tutorial for an overview of Oracle PL/SQL