Database PL/SQL Development Guidelines

• All PL/SQL objects must be managed thru VSS. Do not edit and compile a PL/SQL object directly thru either TOAD or any other tool.
• Functions should have an extension “FNC”, procedures should be “PRC” and packages should have “PKS’. Both specification and body of the package should be in the same file.
• All PL/SQL objects must have a comment block at the beginning. Sample comments blocks are provided in a separate document.

/**********************************************************************

NAME: <OBJECT NAME>

PURPOSE: <Description>

REVISIONS:

Version Date Author Description

--------- ---------- --------------- ----------------------------

1.0 MM/DD/YYYY XXX  Created this function.

PARAMETERS:

INPUT:

OUTPUT:

**********************************************************************/

• Name all local variables defined inside a function/procedure with a prefix e.g. “v_deal_id”, “v_card_id”, etc.
• Do not use generic variable names like “QUERY” or “QUERY1”. If you want to declare a string variable to hold an SQL query, name it based on what that query returns, e.g. “v_card_query”, “v_deal_query”.
• Use DBMS_OUTPUT.PUT_LINE conditionally based on V_DEBUG_YN flag variable.
• Tables should be accessed only thru synonyms. PIDBO owner name should not be embedded anywhere in PL/SQL.
• All procedures must have exception blocks, which handle exceptions appropriately and set the return code and messages.
• Logically group functions/procedures into packages. Do not place non-related (business area) into same package.
• To get a single value, create functions instead of procedures. Functions are more useful, since they can be used in SELECT statements.
• Every SQL statement must be optimized for proper use of indexes and ensure that there are no full table scans (except for small reference tables < 100 rows). Please use “EXPLAIN” regularly. That will highlight the need for additional indexes on the tables.
• There should be no "SELECT * FROM xxxx" coded anywhere in the system. Select specific column names.
• If you are coding singleton SELECT in a PL/SQL code, ensure that you handle NOTFOUND exception appropriately.
• While declaring variables in functions/procedures, use %TYPE where possible. For example, if you are defining a variable V_RATE_ID, define it as V_RATE_ID RC_RATE.RATE_ID%TYPE; instead of V_RATE_ID NUMBER(10);
• The names of Procedures/Functions/Packages should be meaningful and should be separated with “_”. For example PKG_RATE_SEARCH, PKG_CALENDAR_MAINTENANCE
• All IN and OUT variables should start with IN_ and OUT_ respectively. Exception for IN/OUT type of variable should start with IO_