Effective Coding Style

Effective Codying Style
Oracle Naming Coding Standards
Oracle architectural design tips

Effective Coding Style

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 unintended IF statement:

IF to_number(the_value) > 22THEN
IF max_totals = 0 THEN
calc_totals;
ELSE WHILE more_data
LOOP
analyze_results;
END LOOP;
END IF;
END IF;

Three-space indentation not only adequately reveals the logical structure of the code but also keeps the statements close enough together to read comfortably. 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

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.

Comment Your Code
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. Please use some very simple documentation guidelines to clarify the structure of the code. Here are my recommendations:

CREATE OR REPLACE Procedure FDATA_ISSUEDATA_IMPORT
IS
--********************************************************************************************************
-- PURPOSE:    Import Issuedata Files
-- PARAMETERS: None
-- VERSION:    FraudGuard 8.35x Issuedata Process using Java
-- NOTES:      An oracle DB job will call this procedure
--             The logic of this procedure is the following:
--             -- An Oracle directory called ISSUEDATA is needed, that directory is created
--                during installation. If directory is modified after that, will get that modification
--             -- Create a unique external table based on the definitions loaded on the ISSUEMAPFIELD table
--                That external table will load the issuedata file
--             -- Copy all those rows to the TEMP_ISSUEDATA Table
--             -- After that I can perform massive loads of data into the ISSUEDATA Table
--RESTRICTIONS: Customer CAN REMOVE a field from ISSUEMAPFIELD
--              Customer CAN modify STARTPOSITION,FIELDLENGTH or DATAFORMAT on ISSUEMAPFIELD
--              Customer CAN modify IssuePollExtension and PollingLocation
--
-- CONSIDERATIONS FOR THIS CUSTOMER: Run it at 8:45 am, 10:45 am, 12:45 pm, and 2:15pm
--
-- REVISIONS: (when, who, what)
--       01/09/06 - Developed by Diego Pafumi
--       05/22/07 - Added Logic to process 2 payees for PCR 15365
--********************************************************************************************************
   v_Extension varchar2(10);
   v_IssueFileName varchar2(100);
   v_Location varchar2(100);
   v_TrailerIdentifier SYSTEMPROPERTY.PROPERTYVALUE%TYPE;

Oracle Naming Coding Standards

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. It is a bad idea to use a prefix or suffix to identify tables as '_TAB' suffix for all tables.
Example: UserProfile.

Columns
Table columns should be named in the same way as tables are named.
Examples: FormID, ScanDate, ZoneName.

Stored Procedures
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
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 put 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_", "fk_", "pk_" or "un_" prefix followed by table name and fields used on the index. I'd suggest:
"fk_" for FK Indexes
"pk_" for PK Indexes
"un_" for Unique Indexes
"in_", for Indexes created for speed reasons.
Examples:
FK_BANK_FLOWSETTINGID
PK_MACHINE
IN_FLOWDATA_DOCUMENTID_FIELDID
UN_SYSTEMPROP_APPIDPROPNAME

Constraints
Primary keys are to be prefixed with "pk_", unique keys with "un_" and foreign keys start with "fk_".
The remainder of the name usually contains the name of the field(s) included in the key.
Examples:
FK_BANK_CENTERID
PK_FORM
UN_CUSTOMER_CUSTOMERNAMEBANKID

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.
Example: S_ACCOUNTID

PL/SQL
Prefix scalar variable names start with v_ like v_ErrorCode.
Prefix parameters with p_ like p_AccountNumber
Prefix constants with c_ like c_Tax.
Prefix record collections with r_ (alternatively suffix with _record)
Prefix %rowtype% collections with rt_ (alternatively suffix with _record_type)
Prefix pl/sql tables with t_ (alternatively suffix with _table)
Prefix table types with tt_ (alternatively suffix with _table_type)

Declare with Care!!
The following section offers some concrete advice on potential issues you might encounter when declaring variables in PL/SQL

NUMBER If you don’t specify a precision, as in NUMBER(12,2), Oracle supports up to 38 digits of precision. If you don’t need this precision, you’re wasting memory.
CHAR this is a fixed-length character string and is mostly available for compatibility purposes with code written in earlier versions of Oracle. The values assigned to CHAR variables are right-padded with spaces, which can result in unexpected behavior. Avoid CHAR unless it’s specifically needed.
VARCHAR2 The greatest challenge you will run into with VARCHAR2 is to avoid the tendency to hard-code a maximum length, as in VARCHAR2(30). Use %TYPE as described later in this section.
INTEGER If your integer values fall within the range of 2147483647 .. 2147483647), you should declare your variables as PLS_INTEGER. This is the most efficient format for integer manipulation (until you get to Oracle Database 10g Release 2, at which point BINARY_INTEGER, PLS_INTEGER and all the other subtypes of BINARY_INTEGER offer the same performance).

Anchor variables to database datatypes using %TYPE and %ROWTYPE.
When you declare a variable using %TYPE or %ROWTYPE, you “anchor” the type of that data to another, previously defined element. If your program variable has the same datatype as a column in a table or view, use %TYPE to define it from that column. If your record has the same structure as a row in a table or view, use %ROWTYPE to define it from that table. Your code will automatically adapt to underlying changes in data structures.

Another Opinion
http://www.williamrobertson.net/documents/plsqlcodingstandards.html

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:

Windows—Intel Itanium processor
HP—PA-8000 processor
Solaris—500-MHz Ultrasparc-iie processor
IBM AIX—RS/6000 PowerPC processor

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.

Take Adventage of Multi-Block Reads
On most servers, Oracle can read up to 1 Mb (typically 64-128 blocks) at one time. That is why a full table scan can be performed so quickly. Keep in mind, however, that 2 conditions must be met:
1) The database parameter Db_File_Multiblock_Read_Count must be set correctly; and
2) The table or index being scanned must have extent sizes of at least 1Mb.

If the multiblock parameter is set too low at the database level, you can easily alter your session to set the parameter higher. The second point above recognizes that Oracle will not continue a multi-block scan across extent boundaries. If most of the extents are great than 1 megabyte, it’s probably okay. (This is one of those cases where extent sizing really does matter.) It is especially important to optimize your reads if you are using Oracle parallelism. Why launch many extra processes if you don’t first optimize what a single process can do?

Avoid Massive Deletes
Oracle is simply not very fast at deleting millions of rows. Instead, copy and temporarily store the rows you DO want, truncate the table, and then put the rows back in. This can easily be 10x faster. If this method is not feasible due to business considerations, consider multi-threading (discussed later.)

Use Summary Tables and Materialized Views
If you repeatedly access a large table, consider building an aggregate table, or materialized view. A materialized view used in this manner is just like a table, but you can rebuild it with a single command. Remember to use Oracle parallelism to speed-up the refresh. Whether you use a materialized view, or actual table, the idea is to create a “pre-digested” form of the data. You include only the columns and rows that meet your conditions. Once you build the summary table, you can also build custom indexes that optimize your query. A commit after every row will usually wreck performance. Most jobs should commit no sooner than every 1,000 rows. Committing every 10,000 rows would be a good rule of thumb. Unless you are working with many millions of rows, further increasing the commit interval doesn’t really offer much improvement.

Analyze Tables
If you build and populate a new table, don’t forget to gather statistics. It’s very common to see a performance bottleneck caused by incomplete or inaccurate table statistics. It’s not necessary to sample all the rows when gathering statistics; the “estimate” option is usually fine.

Speed Up Inserts
In cases where you don’t really need transaction logging, you can speed up inserts a bit by using the Nologging feature. For inserting rows, set the table NOLOGGING (using Alter Table …) , then use this syntax: INSERT /*+APPEND */ .
Remember, however, that all the new rows will be placed at the end of the table--above the “high water” mark. This means that if you are performing deletes from the table, these “holes” will never be filled. The table will grow rapidly. Eventually, you will need to rebuild the table to crunch it down.

Use Oracle Parallelism
For scans of huge tables or indexes, it is often good practice to invoke Oracle Parallel Query Option (PQO.) On most systems, using a parallel degree of 6 gives excellent performance. Of course, this assumes that you have the spare resources (disk and cpu). For example:
Select /*+Parallel (T 6) */ Emp, Name from Giant_Table T
Remember that Oracle will actually start a total of 2x the degree specified (one set for reading, another set to process/sort). So, the example above will actually result in 12 processes. It is usually best to invoke parallelism via a SQL hint, rather than setting the table to a degree higher than one. If you set the table to invoke parallelism, Oracle will tend to start up the slave processes for many queries, whether you actually want parallelism or not.

Use Bind Variables
If you will be repeating a massive number of SQL statements, it is very important to properly use bind variables. In this way, the database engine avoids a re-parse of each statement. Also, a large number of unique SQL statements tends to flood the shared pool, causing other SQL statements to be released as the engine makes room for the new SQL. This will annoy the DBA.