 |
|
Oracle PL/SQL Pseudocode
Oracle Tips by
Burleson
|
It’s often useful to write pseudocode when designing a new
procedure. Pseudocode should outline the logical steps of a procedure.
Listing 4.26 contains pseudocode for the Calculate_GPA()
procedure.
Listing 4.26 Pseudocode for the Calculate_GPA()
procedure.
for each class taken by the student loop
if the class is not being audited and earns credit then
get the credit points earned for the class;
get the total hours for the class;
determine the number of credit points earned for the class;
add hours for the class to credited hours taken total;
add credit points to a running total;
end loop;
update the STUDENTS table, setting the overall_gpa column
to the value of the total credit points earned divided by
the total number of credited hours taken;
Creating this type of logical map for a procedure shortens the time
required to write the procedure and also allows non-developers
(perhaps those who are more familiar with the business rules but are
not developers) to look at the logic of the procedure and spot errors.
Pseudocode for procedures often serves as a map for commenting the
procedure, as well.
Comments
A good comment in source code can describe the functionality of
code as effectively as pages and pages of written documentation
outside the code. Good comments describe why the code does what it
does, often explaining how the code enforces business rules. Consider
the following two sample comments:
Comment A:
--
-- If the account balance isn't at least $100, we can't upgrade the
-- account to Gold level.
--
IF (nBalance < 100) THEN
Raise_Application_Error (-20343, 'The balance is too low.');
END IF;
Comment B:
--
-- If nBalance < 100, raise an error.
--
IF (nBalance < 100) THEN
Raise_Application_Error (-20343, 'The balance is too low.');
END IF;
Comment A explains why the code is written this way. Comment B
paraphrases the code but doesn’t explain the business rules behind the
code. There is very little in Comment B that will help you understand
the purpose of the code.
TIP: Single-Line versus Multi-Line Comments
Although PL/SQL supports both
single-line and multi-line comments, it’s a good idea to only use
single-line comments in your code, because, at some point, you might
need to comment out a large block of code. PL/SQL doesn’t support
nested C-style comments.
The exception to this rule is when
you’re working in a 3GL language using one of Oracle’s precompilers.
In this situation, you should use the commenting style that is
specified by your coding standards for the 3GL, because the Oracle
precompilers often don’t recognize the single-line style of
commenting.
Identifiers
Take a look at this block of code and see if you can recognize the
purpose of the variables:
IF (x < 100) THEN
Raise_Application_Error (-20343, 'The balance is too low.');;
END IF;
You might recognize this code as the same code described just a few
lines ago, sans comments and with different variable names. Keeping
track of x in three lines of code is easy, but keeping track of x in a
200-line procedure is another story entirely.
Using meaningful identifier names is the best way to document code,
as well as one of the easiest. Six months down the road, you might
have to debug your 200-line procedure that uses x, y, and z for
variable names.
This is an excerpt from the book "High Performance Oracle
Database Automation" by Jonathan Ingram and Donald K.
Burleson, Series Editor. |
