PL/SQL best practice Standards tips

Oracle Tips by Jonathan Ingram

SQL and 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. The Table below displays some of the development areas that PL/SQL coding standards address.

Development areas addressed by PL/SQL 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.

Introduction to PL/SQL standards

This document defines the SQL and PL/SQL environment and programming standards and procedures for <insert your company name here>. 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

PL/SQL Naming Conventions

Using a set of naming conventions for PL/SQL objects tends to create more meaningful identifiers. This section of the standard will come into play most often when creating identifiers (variables and constants) inside blocks of code.

It is common to utilize abbreviations to shorten identifiers. When doing so, the abbreviations should be meaningful and used consistently (e.g., do not use both ADDR and ADRS as an abbreviation for ADDRESS).

More PL/SQL and SQL standards

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.