Effective Codying Style
Oracle
Naming Coding Standards
Oracle architectural
design tips
Revealing
Logical Structure with Indentation
Indentation is one of the most common and effective techniques used to
display a program's logic via format. As illustrated in the following
examples, programs that are indented are easier to read than those that
are not indented. Here is an unindented IF statement:
IF to_number(the_value) > 22 THEN
IF max_totals = 0 THEN
calc_totals;
ELSE WHILE more_data
LOOP
analyze_results;
END LOOP;
END IF;
END IF;
I have found that a three (or four)-space indentation not only adequately reveals the logical structure of the code but also keeps the statements close enough together to read comfortably. And, with deeply nested structures, you won't run off the right margin as quickly! Here is the three-space indented version of the previous nested IF statement:
IF
to_number(the_value) > 22 THEN
IF
max_totals = 0 THEN
calc_totals;
ELSE
WHILE more_data
LOOP
analyze_results;
END LOOP;
END IF;
END IF;
Using Case to Aid Readability
PL/SQL code is made up of many different components: variables, form
items, report fields, procedures, functions, loops, declarations,
control elements, etc. But they break down roughly into two types of
text: reserved words and application-specific names or
identifiers.Reserved words are those names of language elements that
are reserved by PL/SQL and have a special meaning for the compiler.
Some examples of reserved words in PL/SQL are:
WHILE
IF
BEGIN
TO_CHAR
Application-specific identifiers are the names that you give to data and program structures that are specific to your application and that vary from system to system. The compiler treats these two kinds of text very differently. You can improve the readability of your code greatly by reflecting this difference in the way the text is displayed. Many developers make no distinction between reserved words and application-specific identifiers. Consider the following lines of code:
if
to_number(the_value)>22 and num1 between lval and hval then
newval :=
100;
elsif
to_number(the_value) < 1 then
calc_tots(to_date('12-jan-95'));
else
clear_vals;
end if;
While the use of indentation makes it easier to follow
the logical flow of the IF statement, all the words in the statements
tend to blend together. It is difficult to separate the reserved words
and the application identifiers in this code. Changing entirely to
uppercase also will not improve matters. Indiscriminate, albeit
consistent, use of upper- or lowercase for your code reduces its
readability. The distinction between reserved words and
application-specific identifiers is ignored in the formatting. This
translates into a loss of information and comprehension for a developer.
· The
UPPER-lower Style
You can easily solve this problem by adopting a guideline for using a
mix of upper- and lowercase to your code. I have recoded my previous
example below, this time using the UPPER-lower style: all reserved
words are written in UPPERCASE and all application names are kept in
lowercase:
IF
to_number(the_value) > 22 AND num1 BETWEEN lval AND hval
THEN
newval := 100;
ELSIF TO_NUMBER
(the_value) < 1 THEN
calc_tots (TO_DATE ('12-jan-95'));
ELSE
clear_vals;
END IF;
Using a mixture of upper- and lowercase words
increases the readability of the code by giving a sense of dimension to
the code. The eye can more easily cruise over the text and pick the
different syntactical elements of each statement. You can focus quickly
on the lowercase words for the application-specific content. Consistent
use of this method makes the program listings more attractive and
accessible at a glance.
Formatting
Single Statements
Most of your code consists of individual statements, such as
assignments, calls to modules, and declarations. A consistent approach
to formatting and grouping such statements will improve the readability
of your program as a whole. This section suggests some
guidelines.
· Use at most one
statement per line
PL/SQL uses the semicolon (;) as the logical terminator for a
statement, as a result you can have more than one statement on a line
and you can continue a single executable statement over more than one
line. You will sometimes be tempted to place several statements on a
single line, particularly if they are very simple. Consider the
following line:
new_id := 15; calc_total (new_id); max_dollars :=
105 * sales_adj;
It is very difficult to pick out the individual
statements in this line, in addition to the fact that a procedure is
called in the middle of the line. By placing each statement on its own
line you mirror the complexity of a program--the simple lines look
simple and the complex statements look complex--and reinforce the
top-to-bottom logic of the program:
new_id := 15;
calc_total (new_id);
max_dollars := 105 * sales_adj;
· Use
whitespace inside a statement
You can use all the indentation and blank lines you want to reveal the
logic of a program and still end up with some very dense and unreadable
code. It is also important to employ whitespace within a single line to
make that one statement more comprehensible. Always include a space
between every identifier and separator in a statement. Instead of this:
WHILE(total_sales<maximum_sales AND
company_type='NEW')LOOP
write this:
WHILE (total_sales < maximum_sales AND
company_type = 'NEW') LOOP
· Use
indentation to offset all continuation lines under the first line.
This is the most important guideline. The best way to identify
continuation lines is to use indentation to logically subsume those
lines under the main or first line of the statement. The following call
to generate_company_statistics is obscured because the continuation
line butts right up against the left margin with the module name:
generate_company_statistics (company_id,
last_year_date,
rollup_type, total, average, variance, budgeted, next_year_plan);
If I indent the continuation line, the relationship of the second line to the first becomes clear:
generate_company_statistics (company_id,
last_year_date,
rollup_type, total, average, variance, budgeted,
next_year_plan);
Formatting SQL
Statements
Because PL/SQL is an extension to the SQL language, you can place SQL
statements directly in your PL/SQL programs. You can also define
cursors based on SELECT statements. This section summarizes my
suggestions for formatting SQL statements and cursors for maximum
readability.PL/SQL supports the use of four SQL DML (Data Manipulation
Language) statements: INSERT, UPDATE, DELETE, and SELECT. Each of these
statements is composed of a series of "clauses," as in the WHERE clause
and the ORDER BY clause. SQL statements can be very complex, to say the
least. Without a consistent approach to indentation and alignment
inside these statements, you can end up with a real mess. I have found
the following guidelines useful:
Right-align the reserved words for the clauses against the DML statement.
I recommend that you visually separate the SQL reserved words which
identify the separate clauses from the application-specific column and
table names. The following table shows how I use right-alignment on the
reserved words to create a vertical border between them and the rest of
the SQL statement:
|
|||
SELECT |
INSERT |
UPDATE |
DELETE |
SELECT FROM WHERE AND OR GROUP BY HAVING AND OR ORDER BY |
INSERT INTO VALUES INSERT INTO SELECT FROM WHERE |
UPDATE SET WHERE |
DELETE FROM WHERE |
Here are some
examples of this format in use:
SELECT
last_name, first_name
FROM employee
WHERE department_id = 15
AND hire_date < SYSDATE;
SELECT department_id, SUM
(salary) AS total_salary
FROM employee
GROUP BY department_id
ORDER BY total_salary DESC;
INSERT INTO employee
(employee_id, ... )
VALUES (105 ... );
UPDATE employee
SET hire_date = SYSDATE
WHERE hire_date IS NULL
AND termination_date IS NULL;
This right alignment makes it very easy to identify the different clauses of the SQL statement, particularly with extended SELECTs. You might also consider placing a blank line between clauses of longer SQL statements (this is possible in PL/SQL, but is not acceptable in "native" SQL executed in SQL*Plus).
Use meaningful abbreviations for table and column aliases
It's
impossible to read a program when a query has a six-table join and
the tables have been assigned aliases A, B, C, D, E, and F. How can you
possibly decipher the WHERE clause in the following SELECT?
SELECT ... select
list ...
FROM employee A, company B, history C, bonus D, profile E, sales F
WHERE A.company_id = B.company_id
AND A.employee_id = C.employee_id
AND B.company_id = F.company_id
AND A.employee_id = D.employee_id
AND B.company_id = E.company_id;
With more sensible table
aliases (including no tables aliases at all where the table name was
short enough already), the relationships are much clearer:
SELECT ... select list ...
FROM employee EMP, company CO, history HIST, bonus, profile PROF, sales
WHERE EMP.company_id = CO.company_id
AND EMP.employee_id = HIST.employee_id
AND CO.company_id = SALES.company_id
AND EMP.employee_id = BONUS.employee_id
AND CO.company_id = PROF.company_id;
Formatting Exception Handlers
PL/SQL provides a very powerful facility for dealing with errors. An
entirely separate exception section contains one or more "handlers" to
trap exceptions and execute code when that exception occurs. Logically,
the exception section is structured like a conditional CASE statement
(which, by the way, is not supported by PL/SQL). As you might expect,
the format for the exception section should resemble that of an IF
statement. Here is a general example of the exception section:
EXCEPTION
WHEN
NO_DATA_FOUND THEN
executable_statements1;
WHEN DUP_VAL_ON_INDEX THEN
executable_statements1;
...
WHEN
OTHERS THEN
otherwise_code;
END;
Formatting
PL/SQL Blocks
The PL/SQL block structure forms the backbone of your code. A
consistent formatting style for the block, therefore, is critical. This
formatting should make clear these different sections. Consider the
following function:
FUNCTION
company_name
(company_id_in IN company.company_id%TYPE) RETURN
VARCHAR2 IS cname
company.company_id%TYPE; BEGIN
SELECT name INTO cname FROM company
WHERE company_id = company_id_in;
RETURN cname;
EXCEPTION WHEN
NO_DATA_FOUND THEN
RETURN NULL;
END;
You know that this program is a function because the first word in the program is FUNCTION. Other than that, however, it is very difficult to follow the structure of this program. Where is the declaration section? Where does the executable section begin and end? Here is that same function after we apply some straightforward formatting rules to it:
FUNCTION company_name
(company_id_in IN
company.company_id%TYPE)
RETURN VARCHAR2
IS
cname
company.company_id%TYPE;
BEGIN
SELECT name INTO cname FROM company
WHERE company_id = company_id_in;
RETURN cname;
EXCEPTION
WHEN
NO_DATA_FOUND THEN
RETURN NULL;
END;
The declaration section, which comes
after the IS and before the BEGIN, clearly consists of a single
declaration of the cname variable. The executable section consists of
all the statements after the BEGIN and before the EXCEPTION statement;
these are indented in from the BEGIN. Finally, the exception section
shows a single specific exception handler and a WHEN OTHERS exception.
Generally, indent the statements for a given section from the reserved
words which initiate the section. You can also include a blank line
before each section, as I do above, for the executable section (before
BEGIN) and the exception section (before EXCEPTION). I usually place
the IS keyword on its own line to clearly differentiate between the
header of a module and its declaration section.
Make Comments
Easy to Enter and Maintain
You shouldn't spend a lot of time formatting your comments. You need to
develop a style that is clean and easy to read, but also easy to
maintain. When you have to change a comment, you shouldn't have to
reformat every line in the comment. Lots of fancy formatting is a good
indication that you have a high-maintenance documentation style. The
following block comment is a maintenance nightmare:
/*
===========================================================
|
Parameter
Description
|
|
|
|
company_id The primary
key to
company |
|
start_date Start date
used for date range |
|
end_date
End date for date
range
|
===========================================================
*/
The right-justified vertical lines and column
formatting for the parameters require way too much effort to enter and
maintain. What happens if you add a
parameter with a very long name? What if you need to write a longer
description? A simpler and more maintainable version of this comment
might be:
/*
===========================================================
| Parameter -
Description
|
| company_id - The
primary key to company
| start_date -
Start date used for date range
| end_date - End
date for date range
===========================================================
*/
Maintain
Indentation
Inline commentary should reinforce the indentation and therefore the
logical structure of the program. For example, it is very easy to find
the comments in the make_array procedures shown below.
PROCEDURE
make_array (num_rows_in IN INTEGER)
/* Create an array
of specified numbers of rows */
IS
/*
Handles to Oracle Forms structures */
col_id GROUPCOLUMN;
rg_id
RECORDGROUP;
BEGIN
/*
Create new record group and column */
rg_id
:= CREATE_GROUP ('array');
col_id := ADD_GROUP_COLUMN ('col');
/*
||
Use a loop to create the specified number of rows and
||
set the value in each cell.
*/
FOR
row_index IN 1 .. num_rows_in
LOOP
/* Create a row at the end of the group to accept data */
ADD_GROUP_ROW (return_value, END_OF_GROUP);
FOR col_index IN 1 .. num_columns_in
LOOP
/* Set the initial value in the cell */
SET_GROUP_NUMBER_CELL (col_id, row_index, 0);
END LOOP;
END
LOOP;
END;
Documenting the
Entire Package
A package is often a complicated and long construct. It is composed of
many different types of objects, any of which may be public (visible to
programs and users outside of the package) or private (available only
to other objects in the package). You can use some very simple
documentation guidelines to clarify the structure of the package. As
usual when discussing packages, one must consider the specification
separately from the body. As a meta-module or grouping of modules, the
specification should have a standard header. This header needn't be as
complicated as that of a specific module, because you do not want to
repeat in the package header any information which also belongs in
specific modules. I suggest using the template header shown in the
following example. In the "Major Modifications" section of the header,
do not include every change made to every object in the package.
Instead note significant changes to the package as a whole, such as an
expansion of scope, a change in the way the package and global
variables are managed, etc. Place this header after the package name
and before the IS statement:
PACKAGE
package_name
/*
|| Author:
||
|| Overview:
||
|| Major
Modifications (when, who, what)
||
*/
IS
...
END package_name;
Document the
Package Specification
The package specification is, in essence, a series of declaration
statements. Some of those statements declare variables, while others
declare modules. Follow the same recommendation in commenting a package
as you do in commenting a module's declaration section: provide a
comment for each declaration. In addition to the comments for a
specific declaration, you may also find it useful to provide a banner
before a group of related declarations to make that connection obvious
to the reader. Surround the banner with whitespace (blank lines for the
start/end of a multiline comment block). While you can use many
different formats for this banner, use the simplest possible design
that gets the point across. Everything else is clutter. The package
specification below illustrates the header and declaration-level
comment styles, as well as group banners:
PACKAGE
rg_select
/*
|| Author: Diego
Pafumi
||
|| Overview:
Manage a list of selected items correlated with a
||
block on the screen.
||
|| Major
Modifications (when, who, what)
||
12/94 - DP - Create package
||
3/95 - IS - Enhance to support coordinated blocks
||
*/
IS
/*----------------- Modules to Define the List -------------------*/
/* Initialize the list/record group. */
PROCEDURE init_list (item_name_in IN VARCHAR2);
/* Delete the list */
PROCEDURE delete_list;
/*------------------ Modules to Manage Item Selections -----------*/
/* Mark item as selected */
PROCEDURE select_item (row_in IN INTEGER);
/* De-select the item from the list */
PROCEDURE deselect_item (row_in IN INTEGER);
END
rg_select;
Tables
Table names should reflect the data in the table. The names are a
mix of upper and lowercase letter with no underscores and in singular.
The names should be fully spelled out and in singular or plural, but
please keep only one rule: plural or singular.
Example: UserProfile.
Columns
Table columns should be named in the same was tables are named.
Examples: FormID, ScanDate, ZoneName.
Stored Procedures
Atlantis related stored procedures start with "ap_".
Project specific stored procedure should be prefixed by "p_".
Following the prefix should be an abbreviation for a verb, or action
word. Some typical actions on data are select (sel),
insert (ins), update (upd) and delete (del).
If a combination of actions is possible in a stored procedure, then use
the first letters of the action. For example, if a stored
procedure can do an insert or update, then use iu. An underscore
character should then follow the verb abbreviation.
Next, the name of the table affected or some other noun should be used
to describe what is affected. Each word in this part of the spec
is a mix of upper and lowercase, where the first character is uppercase
and the rest lowercase. There should be no underscores in this
part of the name.
Examples are listed below:
ap_ins_Form - This procedure name is an Atlantis stored procedure that
inserts a record into the Form table.
ap_iu_Document - This procedure name is Atlantis stored procedure that
inserts or updates a record into the Document table.
ap_sel_Zone - This procedure name is an Atlantis stored procedure that
selects records from the Zonetable.
p_del_Account - This procedure is project specific procedure that
deletes records from the Zone table.
Triggers
Atlantis triggers start with "at_" and project specific triggers start
with "t_".
Following this prefix is an indicator describing the action that
triggers the trigger. Use del for delete, ins for
insert and upd for update. If a trigger is used for more
than one of these operations, then just include the first letter.
For example, use iu for an insert/update trigger. Next comes an
underscore followed by the suffix. The suffix contains an
upper/lowercase name that includes the table name and/or any other
descriptive text.
Examples: at_del_Document, t_iu_UserProfile.
Views
Atlantis views will have a prefix of "av_". A project specific
view should have a prefix of "v_" or "view_".
Following the prefix the view name should contain some sort of
descriptive reference. If the view contains a simple join of two
tables, then include the table names. For example: v_Table1Table2.
The suffix should be upper/lowercase.
Indexes
Index names should have an "in_" prefix. The rest of the name is
upper and lowercase. This suffix contains some meaningful text
about the nature of the index.
Example: in_EmployeeID.
Constraints
Primary keys are to be prefixed with "pk_", unique keys with "uk_" or
"unique_" and foreign keys start with "fk_".
The remainder of the name is upper and lowercase and usually contains
the name of the field(s) included in the key.
Example: pk_FormID, fk_ImageType.
Sequence
Sequence names begin with an "s_", followed by an underscore and then
the field name (i.e. s_Field). If field name is ambiguous, then
precede the field name with table name s_TableField.
Oracle architectural
design tips
Use RAM data caching
You must be aware that Oracle9i allows very large memory regions in
order to cache frequently referenced row information. The caching of
frequently referenced information should be a major design goal
primarily because RAM access is two orders of magnitude (more than
10,000 times) faster than row access from disk. The larger the Oracle
data block buffer cache, the faster the SQL queries will execute. The
size of the RAM data buffers will have a direct impact on Oracle
performance, and all systems run fastest when fully cached in the data
buffers.
Buy fast processors
The CPU speed of the Oracle database server has a direct impact on
performance. High-performance 64-bit CPUs will often perform 10 times
faster than 32-bit processors. The 64-bit processors are available on
all major platforms and include:
Use a 64-bit version of Oracle
It is highly recommended that Oracle systems exist on a dedicated
database server with a 64-bit CPU architecture and a 64-bit version of
Oracle. The 64-bit version of Oracle lets you create large SGA regions,
and large projects commonly require more than 20 gigabytes of RAM data
buffers. A serious shortcoming of 32-bit Oracle is the 1.7-gigabyte
size limitation for the SGA.
Use large data blocks on indexes to
minimise disk I/O
Oracle index access performs best with 16K and 32K Oracle block sizes.
You should consult your application-specific OS documentation and
create index tablespaces of the largest value for your environment.
Use Oracle Parallel Query
All table access should be tuned to avoid large-table full-table scans,
but often, such scans are required. Make certain all full-table scans
fully utilize Oracle Parallel Query to improve performance.
Choose proper SQL optimisation
The choice of optimiser mode is critical to Oracle SQL performance. In
Oracle9i, approximately half of all queries run faster with rule-based
optimisation; the other half run fastest with cost-based optimisation.
Always design for package pinning
All frequently referenced PL/SQL packages should be pinned into the
shared pool by using the dbms_shared_pool.keep procedure. Doing so will
greatly speed Oracle PL/SQL execution.