PL/SQL Best Practices
The primary purpose of PL/SQL coding
standards has always been to make maintenance easier for developers.
PL/SQL best practices make the job even easier. In order to satisfy
the requirement for making maintenance easier for the developers, PL/SQL coding standards must
address several areas of the development process. PL/SQL best
practices take the implementation of coding standards to a higher
level.
See my notes on PL/SQL best practices:
Jay Singh posted a great set of PL/SQL
best practices and coding guidelines on Oracle-L:
The following are the eight
guidelines for PL/SQL best practices, according to Jay:
1) While writing PL/SQL, use
the correct datatype so that implicit conversion will be avoided
2) Use bind variable to avoid
unnecessary parsing
3) Use BULK COLLECT, %
ATTRIBUTES wherever required
4) MODULARITY
Write the code that fit into
your screen size.
Through successive
refinement, you can reduce a complex problem to a
set of simple problems that
have easy-to-implement solutions.
5) EXCEPTION WHEN OTHERS is
almost always a BUG unless it is immediately
followed by a RAISE. Use
WHEN OTHERS exception as the last resort and handle exceptions.
For example:
EXCEPTION
WHEN OTHERS THEN
if (sqlcode=-54) then
.... deal with it.
else
RAISE;
end if;
6) Tom's Mantra
If (possible in SQL)
do it;
else if(possible in PL/SQL)
do it;
else if(possible in JAVA)
do it;
else
..
..
end if;
7) % ATTRIBUTES
Use %TYPE and %ROWTYPE
attributes. No code change is required when schema structure
changes.
8) BEFORE VS AFTER TRIGGER
NEVER USE BEFORE TRIGGER FOR
VALIDATIONS. Use BEFORE triggers ONLY to modify :NEW value.
Oracle Documentation States:
"BEFORE row triggers are slightly more efficient than
AFTER row triggers. With
AFTER row
triggers, affected data blocks must be read (logical read, not
physical read) once for the trigger and then again for the
triggering statement. Alternatively, with
BEFORE row
triggers, the data blocks must be read only once for both the
triggering statement and the trigger."
|
