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. Table D.1 displays some of the development areas that PL/SQL coding standards address.
Table D.1 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 SQL And PL/SQL Coding Standard
This document defines the SQL and PL/SQL environment and programming standards and procedures for . 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.
________________________________________
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.
________________________________________
Headers
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:
--
-- 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 SQL And PL/SQL Statements
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 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

END IF;

Incorrect:
IF nSSN < 2.5 THEN

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)



Incorrect:
PROCEDURE Days_Between (dStartDate IN date,
dEndDate IN date,
nGPA IN OUT number,
nDaysBetween OUT number)


• 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);
Capitalization
Table D.2 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.
Table D.2 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;
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 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.
________________________________________
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

END IF;
ELSIF (x > 10) THEN

ELSE

END IF;

IF (x < 7) THEN
IF (y < 0) THEN

END IF;
ELSIF (x > 10) THEN

ELSE

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

ELSIF (some expression) THEN

END IF;
END IF;
END IF;


• 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);
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)));
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


--
-- 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


ELSE

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

END LOOP;
• Before each exception after the first declared within the EXCEPTION section of a PL/SQL block.
EXCEPTION
WHEN NO_DATA_FOUND THEN


WHEN TOO_MANY_ROWS THEN


WHEN OTHERS THEN

• Before and after the variable, constant, and type declarations for a PL/SQL block.
PROCEDURE Update_Student_GPA (nSSN IN number)
IS




BEGIN
;
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

END IF;
END IF;
END IF;


Named Vs. Positional Notation
Procedures and functions should always be called using named notation for their parameters. This helps identify the data that is being passed to the stored PL/SQL object (assuming that the identifiers chosen for the parameters are meaningful). Place only one parameter on each line of the call:
DaysBetween (dStartDate => dEnrolledDate,
dEndDate => dGraduationDate,
nGPA => nFinalGPA,
nDaysBetween => nDuration);
Statements Per Line And Line Width
Place only one statement (or part thereof) per line of code. Break compound statements over multiple lines. Do not exceed a maximum line width of 80 characters (including indentation). Due to space limitations, the lines in this book are limited to a maximum of 70 characters.
If your code has a complex equation or formula that is expressed using a single statement or operation, consider breaking the code into several smaller statements to make the operations less intimidating. The equation will be much easier to debug; in addition, the process of breaking the equation into subsections will increase your awareness of any mistakes that you make.
SQL Statement Formatting Rules
The formatting of SQL statements is one of the few areas in which this standard can be “the law.” All SQL statements should conform precisely to these standards to the maximum extent possible; even slight deviations can have a performance impact on an Oracle database.
Oracle caches individual SQL statements within its shared global area (SGA). When a new statement is issued, Oracle does a block comparison of the statement against statements in the SGA. If a match is found, Oracle re-executes the stored version of the statement, rather than parsing the new statement and then executing it.
For instance, presume that the SGA contains the following SQL statement:
SELECT name, ssn, grade
FROM STUDENT_GRADES
WHERE grade > 90;
Now presume that someone executes this statement:
SELECT name, ssn, grade
FROM STUDENT_GRADES
WHERE grade > 90;
The second statement, although functionally identical to the statement already existing in the SGA, would not find a match in the SGA and would be parsed to determine the proper execution plan for the statement, thus increasing the overhead of the statement and slowing the response to the user.
To make matters worse, the first (and the correct) statement might be pushed out of the SGA to make room for the new statement, further impacting the processing of code that does follow the standard. The placement of a single space or shifting the case of a single character will force an SQL statement to be parsed and placed in the SGA instead of finding a matching statement in the SGA, due to the nature of the block comparison algorithm that Oracle uses.
Admittedly, the time required to parse a single statement to determine an execution plan is very small, but with tens or hundreds of users hitting the database at the same time, those milliseconds add up very quickly. Performance improvements are sometimes achieved in one fell swoop, but a lot of performance tuning work is the result of painstakingly wringing small improvements out of a lot of different pieces of code.
A number of examples are listed in the following sections. In each section, one example is labeled Correct and the other examples are labeled Incorrect. The example labeled correct is the only acceptable format for statements of the type, even though the other statements shown (and many variations not shown) are functionally equivalent to the “correct” statement.
DELETE Statements
Correct:
DELETE
FROM STUDENTS
WHERE ssn = 999999999;
Incorrect:
DELETE
FROM STUDENTS
WHERE ssn = 999999999;
Incorrect:
DELETE STUDENTS WHERE ssn = 999999999;
INSERT Statements
Correct:
INSERT
INTO STUDENTS
(ssn,
first_name,
last_name,
...
most_recent_gpa)
VALUES (999999999,
'Roger',
'Smith',
...
NULL);
Incorrect:
INSERT
INTO STUDENTS (ssn, first_name, last_name,... most_recent_gpa)
VALUES (999999999, 'Roger', 'Smith',...NULL);
Incorrect:
INSERT INTO STUDENTS
VALUES (999999999, 'Roger', 'Smith', ... NULL);
Incorrect:
INSERT INTO STUDENTS VALUES (999999999, 'Roger', 'Smith',...NULL);
SELECT Statements
Correct:
SELECT last_name, first_name, middle_name, ssn, most_recent_gpa
FROM STUDENTS
WHERE ssn = 999999999;
Incorrect:
SELECT last_name, first_name FROM STUDENTS
WHERE
ssn = 999999999;
Incorrect:
SELECT ssn, most_recent_gpa
FROM STUDENTS
WHERE most_recent_gpa < 2.0;
UPDATE Statements
Correct:
UPDATE STUDENTS
SET apartment_number = 'H',
street_address = '16 Northwest Main Street'
WHERE ssn = 999999999;
Incorrect:
UPDATE STUDENTS
SET apartment_number = 'H',
street_address = '16 Northwest Main Street'
WHERE ssn = 999999999;
Incorrect:
UPDATE STUDENTS
SET apartment_number = 'H', street_address = '16 Northwest Main
Street'
WHERE ssn = 999999999;
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).
Database Triggers
Database triggers are named using this convention:
table_name + trigger_type_extension
In this example, table_name is the name of the trigger’s base table, and trigger_type_extension represents one of the four types of database triggers listed in Table D.4, plus one or more of the letters shown in Table D.5 to indicate which DML statements cause the trigger to fire.
Table D.4 The four types of database triggers. ________________________________________
Trigger Type Extension Fires
Before statement _B Once, before the DML statement acting on the table.
Before row _BR Once for each row affected by the DML statement, before the DML statement is executed.
After row _AR Once for each row affected by the DML statement, after the DML statement is executed.
After statement _A Once, after the DML statement has finished executing.


Table D.5 Modifications to trigger_type_extension to indicate DML statements handled by the trigger. ________________________________________
Trigger Type Extension DML Statement
D The trigger fires when a DELETE statement modifies the base table.
I The trigger fires when an INSERT statement modifies the base table.
U The trigger fires when an UPDATE statement modifies the base table.


Thus, a BEFORE INSERT or UPDATE row level trigger on the STUDENTS table would be named STUDENTS_BRIU.
If the length of the trigger name exceeds 30 characters when following this standard, abbreviate the name of the trigger’s base table to create the name of the trigger. Under no circumstances should the trigger type extension be abbreviated.
Identifiers
When declaring variables and constants, the developer should preface a meaningful identifier with one of the prefixes shown in Table D.6.
Table D.6 Datatype prefixes for use in identifiers. ________________________________________
Datatype Prefix Example
binary_integer bi biArrayIndex
boolean b bStudentQualifiesForAid
char c cYesOrNo
date d dEnrolledDate
exception x xTABLE_DOES_NOT_EXIST
integer i iCoursesCarried
long l lComments
longraw lr lrStudentPhoto
natural na naArrayIndex
number n nRemainingBalance
raw r rStudentPhoto
rowid row rowStudent
varchar2 v vStudentFirstName


Identifiers should always use mixed-case and capital letters to indicate separation of elements within an identifier. Thus, a variable of type varchar2 that holds a student’s first name would be vStudentFirstName.
The identifiers used for explicitly declared cursors should be meaningful; the suffix _cur should be appended to the identifier. For example:
CURSOR Students_cur
IS
SELECT first_name, middle_name, last_name, overall_gpa, most_recent_gpa
FROM STUDENTS;
Identifiers declared using %TYPE should still include a datatype prefix as part of the identifier name:
nStudentSSN STUDENTS.ssn%TYPE;
Identifiers declared using %ROWTYPE should be named like the object that is lending the variable its structure. These identifiers should always include the _rec suffix as part of the identifier:
Students_rec STUDENTS%ROWTYPE;
FailingStudents_rec FailingStudents_cur%ROWTYPE;
Procedures And Functions
Stored procedures and functions should be named first by the type of action the object performs and then by the object of that action. For instance, a procedure that calculates interest on a student’s remaining balance would be named Calculate_Balance_Interest.
Packages
Packages should be named in accordance with the general purpose of the procedures and functions contained within the package. For instance, a package containing routines used to calculate a student’s GPA would be named GPA_Calculations.
Written Documentation
All documentation pertaining to system design should follow the conventions identified in this standard. This applies especially to pseudocode that is used to document stored PL/SQL objects.
In addition, all written documentation should reference calls to built-in and developer-written procedures and functions using a set of parentheses, as in “will call the Calculate_Semester_GPA() procedure”. The names of built-in and developer-written code modules should also be referenced in bold.
Taking Advantage Of Standardized Code
Oracle stores the source code for PL/SQL objects inside the ALL_SOURCE view of the DBA_SOURCE table in the SYS schema. This allows you to query the most recent source code for a procedure or function from the database.
If the coding standards are followed fairly closely, it’s possible to write scripts that work with your source code to assist you in your documentation efforts. Included on the CD-ROM is a script that takes full advantage of the coding standards to collect information about and document source code.
The Build_SUID_Matrix Package
Oracle stores dependencies in the ALL_DEPENDENCIES view, but this information exists only at the object-to-object and object-to-table levels. The view cannot, for instance, state precisely which objects perform INSERT statements on a specific table.
The Build_SUID_Matrix package was designed to locate all references to tables within a specified PL/SQL object. The procedures and functions in the package locate table references and sort the references by type (SELECT, INSERT, UPDATE, DELETE, and %TYPE or %ROWTYPE). The package populates the SUID_MATRIX table with this information.
Once the SUID_MATRIX table is fully populated, a query can be run to see precisely which objects access a specified table or which tables a specified object references. This is particularly useful when:
• Examining the impact of creating a new index.
• Examining the impact of altering a table’s structure.
• Determining which objects perform a particular type of operation against a table.
Improving The Build_SUID_Matrix Package
There are a number of potential improvements that can be made to the Build_SUID_Matrix package:
• Improving the level of detail determined to the column level. Knowing which objects modify data in a table is very useful, but if 20 routines update a table and only a few routines update a particular column that has a suspect value, it’s even quicker to find the routines that modify the value in that column.
• Recognizing objects within packages. The current implementation of the package only recognizes objects to the package level and doesn’t differentiate between procedures and functions within the package.