 |
|
PL/SQL Coding Standards
Oracle Tips |
PL/SQL Coding Standards
PL/SQL has become a mature and popular language since its introduction in
1991, yet the language is still relatively young compared to its ancestors (both
direct ancestors, like Ada, and indirect ancestors, like COBOL and Fortran). One
of the most common questions I am asked is about coding standards for SQL and
PL/SQL.
The primary purpose of coding standards has always been to make maintenance
easier for developers. In order to satisfy this requirement, PL/SQL coding
standards must address several areas of the development process. Below displays
some of the development areas that PL/SQL coding standards address.
|
Development areas addressed by coding standards.
|
|
Development Area |
Includes |
|
Vertical spacing |
Spacing between statements; spacing between procedures and
functions within package bodies. |
|
Horizontal spacing |
Spacing between identifiers and operators; number of statements
per line; maximum line width. |
|
Procedural calls |
Use of positional or named notation when calling stored PL/SQL
objects. |
|
Commenting |
Type of comments to be used and the frequency, spacing,
positioning, and content of comments. |
|
Code reuse/modularity |
Contents of procedures and functions; organization of procedures
and functions into packages. |
|
Identifiers |
Rules for naming identifiers; rules for naming stored PL/SQL
objects. |
|
SQL statements |
Formatting rules for embedded SQL statements. |
|
Performance |
Performance tips (particularly for embedded SQL statements).
|
|
Debugging |
A
standard method of handling exceptions inside PL/SQL objects as
well as inside other applications that call stored PL/SQL
objects. |
|
Testability |
Rules for writing stored PL/SQL objects so that unit testing can
be accomplished. |
|
Development environment |
Rules determining what tools will be used for application
development and what processes must be followed by developers.
|
|
Capitalization |
Rules determining which keywords will be used in UPPER case,
Mixed case, and lower case. |
|
Conformance to standards |
Rules designating when it is allowable for code to not agree
with the coding standard. |
|
Documentation |
Rules that designate whether the coding standards will be
applied to design documents and what type of documentation must
exist for particular routines. |
|
|
|
The level of specifics contained in a coding standard should be fairly
strict. While this may seem to be a burden during the development process, the
people who do maintenance down the line will appreciate adherence to the
standards.
The best way to ensure adherence to standards is to use structured peer
review when a code module is completed. If the code is readable, the peer review
process will flow more smoothly, and the reviewers will be able to concentrate
their review time on understanding the intimacies of code rather than on
deciphering an entry for the obfuscated PL/SQL contest! Peer reviews also
provide a last line of defense against “sleeper” bugs (often overlooked by even
the best developers), which are found a lot more easily when the code is
readable.
If your organization does some or all of its own SQL and PL/SQL training, you
should make an effort to incorporate standards training into your course
materials.
I hope you find the examples presented in this text easy to read and
understand. I also hope that you notice the consistency of style in the way
examples appear. Every piece of code on the CD-ROM and in the text conforms to
the following coding standard.
A Sample PL/SQL Coding Standard
This document defines the SQL and PL/SQL environment and programming
standards and procedures for you. The standards established in this document
apply to all SQL and PL/SQL development efforts.
These PL/SQL coding standards were written to allow for consistency in PL/SQL
code written by various developers while allowing for some individual styles and
preferences to be expressed. The central purpose of any coding standard for SQL
and PL/SQL must deal with database performance, clarity of code, and
maintainability of code. This standard should be considered a guideline for
developing easily maintainable SQL and PL/SQL applications in a high-performance
Oracle database.
Developers should attempt to meet the spirit of this document by applying
good judgment, rather than strictly adhering to the letter of the standard. This
standard applies to all developer-written SQL and PL/SQL code (including
scripts, stored procedures and functions, database triggers, and stored
packages). Generated code is not governed by this standard.
This document is a living document that evolves based on the experiences of
you, the developer. You should be aware that changes may occur to this document
in the future, based on your (or other developers’) experiences and insights.
The Development Environment And Processes
This section of the standard is highly dependent on the nature of your
organization; therefore, I will only give suggestions on the types of material
that should be included in this section of your coding standard. The
Development Environment And Processes section of the standard should address
the following issues:
• Your version control
processes—A good working knowledge of PL/SQL is important to a developer,
but the inability of a single developer to follow version control processes
could be disastrous!
• Standard tools for
your development efforts—If you have a standard configuration for your
tools, it should be described in painstaking detail. If your tools support
central administration of this configuration, so much the better.
• Peer review
practices—Many organizations have formal peer review processes in place to
validate the quality of design documents, code, and test results. If your
organization uses these processes, the coding standard should (at the very
least) point developers to your process documents.
• Documentation—Describe
the documentation required when developers create new code and what documents
need to be updated when maintenance is performed. Provide reasonable facsimiles
of this documentation (or excerpts from real documents that satisfy your
requirements).
• Testing standards in
place—Provide examples of thorough test scripts and plans. Tests for each
aspect of your system (front end, stored procedures and functions, packages, and
database triggers) should be discussed in detail.
• Standard routines—You
should describe whether standard routines, such as error handling, help system
calls, and so forth, are available and when these routines should be used.
Provide a detailed explanation of each standard call and its interfaces.
These issues can be addressed in appendices to your coding standard or in
other documents, as long as your developers receive the necessary information.
References To Other
Documents
I personally favor one stop shopping; pack the standard with as much information
as possible and reduce the amount of time developers spend tracking down other
documents.
PL/SQL Programming Design Standards
Developers should design for modularity. Black box is a term often
used in conjunction with modules; each module should perform one (and only one)
function using a defined interface and produce a predictable result. So long as
the interface for a code module is not changed, the code module may be altered
without affecting outside code.
Each module contains one or more routines (and the data structures and
variables needed to support the routines). PL/SQL allows developers to implement
modularity through the use of packages, which can contain procedures and
functions as well as global type, variable, and constant declarations.
Stored functions that use parameters of the IN OUT and OUT
types are not allowed. Stored functions should use only the RETURN
statement to return a value. Developers are encouraged to identify routines that
can be reused. This code can be centralized, tested, and used by other
developers to improve the reliability of system code and to reduce development
time for more complex modules.
The Modularity Ombudsman
If your organization consists of ten or more people, it might be a good idea to
appoint a “Modularity Ombudsman” who has some experience with SQL and PL/SQL.
The responsibilities of this position include:
• Identifying
code segments that can be reused
• Developing
and testing reusable modules
• Documenting
modules and promoting their use
The modularity ombudsman should also take
part in peer review for new modules to help increase code reuse throughout your
project.
PL/SQL Header Standards
A header should appear at the start of any script, procedure, function,
package body, or package spec. Consider this template header:
-- *****************************************************************
-- Description: Describe the purpose of the object. If necessary,
-- describe the design of the object at a very high level.
--
-- Input Parameters:
--
-- Output Parameters:
--
-- Error Conditions Raised:
--
-- Author: <your name>
--
-- Revision History
-- Date Author Reason for Change
-- ----------------------------------------------------------------
-- 03 JAN 1997 J.Schmoe Created.
-- *****************************************************************
The Usefulness Of
Headers
There are some people who dislike headers or feel that a header is a useless
burden to place on a developer. I disagree: At no other point in the code are
provisions made for documenting the overall purpose, logic, and interface of a
module. In my opinion, a header is the most essential documentation for any
piece of stored code.
Formatting Guidelines for PL/SQL code
These guidelines are provided to give code a generally consistent appearance,
including indentation, horizontal alignment, and vertical alignment. Adherence
to these standards will make code more readable and more easily understood when
maintenance is necessary.
Alignment of PL/SQL Operators
These guidelines enhance the readability of code by adding white space and
clarifying complex expressions.
• Arrange series of
statements containing similar operators into columns whenever it will not cause
excessive white space and you have sufficient room to do so.
Correct:
vFirstName := 'Roger';
vLastName := 'Smith';
vSSN := 999999999;
Incorrect:
vFirstName := 'Roger';
vLastName := 'Smith';
vSSN := 999999999;
• Always use parentheses
in expressions containing more than one identifier or literal. This clarifies
code for inexperienced developers who are not familiar with operator precedence
and helps eliminate the possibility that you’ve overlooked something in your
equation.
Correct:
IF (nSSN < 2.5) THEN
<statements>
END IF;
Incorrect:
IF nSSN < 2.5 THEN
<statements>
END IF;
• Align the IN and
OUT keywords in columns when defining the interface for a procedure or
function.
Correct:
PROCEDURE Days_Between (dStartDate IN date,
dEndDate IN date,
nGPA IN OUT number,
nDaysBetween OUT number)
<procedure declarations and body>
Incorrect:
PROCEDURE Days_Between (dStartDate IN date,
dEndDate IN date,
nGPA IN OUT number,
nDaysBetween OUT number)
<procedure declarations and body>
• When calling a
procedure or function, align the parameters into a column. This reduces the
visual clutter around the call, making it stand out from the rest of the code.
Correct:
DaysBetween (dStartDate => dEnrolledDate,
dEndDate => dGraduationDate,
nGPA => nFinalGPA,
nDaysBetween => nDuration);
Incorrect:
DaysBetween (dStartDate => dEnrolledDate,
dEndDate => dGraduationDate,
nGPA => nFinalGPA,
nDaysBetween => nDuration);
PL/SQL Capitalization
Below contains a list of keywords that should always be fully capitalized
when referenced in code. Some of these keywords are commonly used reserved
words; reserved words that do not appear on this list should be capitalized as
well.
|
Capitalize these keywords.
|
|
ALL |
FALSE |
MINUS |
ROWTYPE |
|
AND |
FETCH |
NOT |
SELECT |
|
AS |
FOR |
NOTFOUND |
SET |
|
BEGIN |
FOUND |
NULL |
SQLCODE |
|
BETWEEN |
FROM |
OPEN |
SQLERRM |
|
BODY |
FUNCTION |
OR |
TABLE |
|
CLOSE |
GOTO |
ORDER BY |
THEN |
|
COMMIT |
GROUP BY |
OUT |
TYPE |
|
CONSTANT |
HAVING |
PACKAGE |
UNION |
|
CREATE |
IF |
PROCEDURE |
UNION ALL |
|
DECLARE |
IN |
RAISE |
UPDATE |
|
DELETE |
INSERT |
REPLACE |
VALUES |
|
ELSE |
INTERSECT |
RETURN |
VIEW |
|
ELSIF |
INTO |
ROLLBACK |
WHEN |
|
END |
IS |
ROWCOUNT |
WHERE |
|
EXCEPTION |
LIKE |
ROWID |
WHILE |
|
EXIT |
LOOP |
ROWNUM |
|
|
|
|
The keyword REPLACE is to be used in uppercase only when used as part
of the CREATE OR REPLACE clause that is used to create a stored PL/SQL
object. Calls to the SQL function replace() should not be presented in
uppercase.
In addition to the keywords presented in Table D.2, fully capitalize all of
the following:
• The names of all
standard exceptions (NO_DATA_FOUND, OTHERS, TOO_MANY_ROWS),
and all user-defined exceptions.
• The names of all
constants and all user-defined datatypes.
• All acronyms (ANSI,
ASCII, HUD, NASA, NOAA, YMCA, and so forth).
• The names of all
tables, snapshots, and views, as well as the aliases given to these objects in
queries.
• The names of all
database triggers.
Use mixed case to refer to the names of user-defined procedures and functions
(functions provided by SQL*Plus and PL/SQL are still referenced in lower case).
For example:
Calculate_GPA
DBMS_Output.Put_Line
Optionally, use mixed case for user-defined identifiers. If you choose this
method, use capital letters to help make the identifier names more meaningful by
visually breaking variable names into words; here are some examples:
vString
nBaseSalary
nGPA
iTardyDays
iClassNumber
lComments
rStudentPhoto
All text not handled by these rules should use lowercase. Consider the
following:
CREATE OR REPLACE PACKAGE My_Sample_Package AS
PROCEDURE My_Sample_Procedure (nParameter1 IN number,
nParameter2 OUT number)
IS
YES CONSTANT char (1) := 'Y';
BEGIN
IF (some expression) THEN
replace (vString, chr (9), ' ');
END IF;
END My_Sample_Procedure;
END My_Sample_Package;
Using PL/SQL Comments
As much as some developers dislike the task, commenting code is essential if
the code is going to be maintained. There are a number of steps that can be
taken to make comments less necessary:
• Use meaningful
identifiers for variables, constants, and parameters. If you use abbreviations
to compose identifiers, use the abbreviations consistently (e.g., don’t use both
ADDR and ADRS to signify ADDRESS).
• Use the named parameter
style of executing procedures and functions. This is especially effective if
both the parameters and the variables passed to the stored PL/SQL object have
meaningful identifiers.
• Comments about
revisions belong in the prologue, not in the body of the module.
TIP: Commenting Changes And Problem Tracking
If you’re using a problem-tracking system on your project, it’s better to
reference a particular report from that system and provide a brief summary of
the changes made to solve that problem. Don’t attempt to include all the
information about the problem in the prologue; that’s why you bought a problem
tracking system!
• Break complex equations
and formulas into several smaller statements.
• Reuse existing
functions and procedures to accomplish your tasks. Identify code that can be
reused.
There are a number of locations in PL/SQL code where comments should almost
always be used, including the following instances:
• Before each loop
structure.
• Before each
BEGIN…END sub-block.
• Before each conditional
logic expression (IF <condition> THEN).
• Before any other
logically significant statements.
Do not comment each line of code! Only comment important parts of your code,
explaining why the code is written in a particular way. Explain business rules
if possible. Never use a comment to restate the actions of a piece of code.
PL/SQL supports the following two styles of commenting:
/* We need to determine which students are in academic trouble. */
-- We need to determine which students are in academic trouble.
PL/SQL does not support the nesting of C-style comments; you cannot comment
out a C-style comment using other C-style comments. For this reason, it is
strongly recommended that only the double-dash (--) style of commenting be used
except when commenting out blocks of code.
The exception to this rule is inside 3GL programs that use the Oracle
Precompilers. The Oracle Precompilers don’t support single line comments. On
these occasions, use the commenting style most appropriate to the 3GL.
If a comment is required, place the comment on the line immediately preceding
the line of code. Do not append comments to the end of code; if a comment is
warranted by the complexity of the code and you have used meaningful
identifiers, the comment should be complicated enough that you need to explain
the situation using more than one or two words.
Correct:
--
-- Determine which students might be in trouble academically. We want
-- to help them perform better in school.
--
IF (some condition) THEN
Incorrect:
IF (some condition) THEN -- who's got bad grades?
All comments should use proper grammar, punctuation, and spelling. Comments
should be complete, coherent sentences.
TIP: Volume Of Comments
As a general rule, about one-third of your final code should be comments. This
figure often varies depending on the size and complexity of the code, but is an
excellent rule of thumb.
PL/SQL Indentation
The most important element in readable code is consistent indentation, which
illustrates clearly the logic flow of a procedure. Consider these blocks of
code:
IF (x < 7) THEN
IF (y < 0) THEN
<statements>
END IF;
ELSIF (x > 10) THEN
<statements>
ELSE
<statements>
END IF;
IF (x < 7) THEN
IF (y < 0) THEN
<statements>
END IF;
ELSIF (x > 10) THEN
<statements>
ELSE
<statements>
END IF;
Horizontal alignment in the second block of code makes it much easier to
follow, even though it is syntactically and functionally identical to the first
block of code.
You should not use more than three or four levels of indentation in any block
of code. If this many levels of indentation become necessary, consider breaking
the code into smaller modules. Too many levels of indentation is almost as bad
as no indentation at all.
• Code should always be
indented consistently, using three spaces for each level of indentation.
Variable, type, and constant declarations should all be indented to the first
level of indentation. Do not use tab characters.
IF (some expression) THEN
IF (some expression) THEN
IF (some expression) THEN
<statements>
ELSIF (some expression) THEN
<statements>
END IF;
END IF;
END IF;
<statements>
• Statements following
the WHEN clause of an exception handler should be indented five spaces,
in order to create a column-like effect within the exception handler.
Correct:
EXCEPTION
WHEN OTHERS THEN
DBMS_Output.Put_Line (SQLERRM);
Incorrect:
EXCEPTION
WHEN OTHERS THEN
DBMS_Output.Put_Line (SQLERRM);
PL/SQL Horizontal Spacing
You’ve probably heard of the obfuscated C contest, where the entrants attempt
to cram as much code as possible onto a single line that does some type of work
while remaining completely unreadable. If you have any experience maintaining
code, you’ve probably seen more than a few pieces of code that have a good
chance of winning a contest like this. The guidelines presented in this section
are an attempt to guide you away from writing hard-to-read code.
• One of the most
important elements in creating readable code is the spacing placed around
operators. Table D.3 shows common operators and keywords that need to be
preceded and followed by a space when they are used in expressions.
|
Table D.3 Operators and keywords to be preceded and
followed by a space when used in expressions.
|
|
+
|
-
|
*
|
/
|
|
&
|
<
|
>
|
=
|
|
!= |
<= |
>= |
:= |
|
=> |
|| |
.. |
:
|
|
<> |
IN |
OUT |
AND |
|
OR |
NOT |
NULL |
|
|
|
|
Often more than one of the operators and keywords shown in Table D.3 will be
adjacent to each other inside an expression. In this instance, it is recommended
that only one space lie between the two operators/identifiers. For example:
IF (vMajor IS NOT NULL) THEN
• Spaces should precede
and follow character (') literals.
SELECT first_name || ' ' || middle_name || ' ' || last_name
'student_name'
FROM STUDENTS
WHERE ssn = 999999999;
• Do not leave any blank
spaces preceding or following the ** operator.
nRaisedNum := nNum**nPower;
• Do not leave blank
spaces before or after the plus (+) and minus (-) signs when used as unary
operators.
nNumber := -nSecondNumber;
nNumber := +nSecondNumber;
• Do not use spaces
between multiple parentheses or semicolons (;). Always precede the first opening
parenthesis of a set with a space.
AND (((x < 5) AND (y < 5))
OR ((x > 5) AND (y > 5)));
PL/SQL Vertical Spacing
Vertical spacing helps distance elements in the code from one another,
reducing the visual clutter above and below statements. To create appropriate
vertical spacing for your code, place a blank line in the locations described in
the following list:
• Before lines containing
the keywords IF, ELSE, ELSIF, and EXCEPTION. If the
line is preceded by a comment, place the blank line before the comment instead
of before the line of text.
--
-- If the student's grade point average meets the criteria for
-- mandatory academic counseling, add the student's name and social
-- security number to the list.
--
IF (nRealGPA < 1.5) THEN
<statements>
--
-- We also want to consider students who are failing two or more
-- classes, even if their GPA is above 1.5.
--
ELSIF Has_Two_Fails (nForSSN => nSSN) THEN
<statements>
ELSE
<statements>
END IF;
• Before any line
containing the LOOP keyword. Do not place a blank line before source code
containing the END LOOP keyword. (As with lines of code containing the
IF keyword, keep the comments for a line of code with the comment by placing
a blank line before the comment.)
--
-- For each student returned by the query, add the student's social
-- security number to the PL/SQL table.
--
FOR Students_rec IN Students_cur LOOP
<statements>
END LOOP;
• Before each exception
after the first declared within the EXCEPTION section of a PL/SQL block.
EXCEPTION
WHEN NO_DATA_FOUND THEN
<statements>
WHEN TOO_MANY_ROWS THEN
<statements>
WHEN OTHERS THEN
<statements>
• Before and after the
variable, constant, and type declarations for a PL/SQL block.
PROCEDURE Update_Student_GPA (nSSN IN number)
IS
<declaration>
<declaration>
BEGIN
<statements>;
END Update_Student_GPA;
• Following the
declaration of the procedure and its parameters.
PROCEDURE Update_Student_GPA (nSSN IN number)
IS
• Do not place an empty
line before a line containing the END IF keyword. Do place blank lines
after the last line of code containing the END IF keyword.
IF (some expression) THEN
IF (some expression) THEN
IF (some expression) THEN
<statements>
END IF;
END IF;
END IF;
