plsql standards and best practices
DESCRIPTION
This document list the standards and best practices on PLSQLTRANSCRIPT
PLSQL Coding Best Practices Alwyn Anil D`Souza
Table of Contents
1 Introduction .................................................................................................................................... 3
Benefits of Having Standards ............................................................................................................ 3
2 Naming Conventions ..................................................................................................................... 4
2.1 File Naming ............................................................................................................................. 4
2.2 Identifier naming conventions .............................................................................................. 4
3 PLSQL Coding Guidelines ............................................................................................................... 6
3.1 General Rules ......................................................................................................................... 6
3.2 Variables and Types ............................................................................................................... 8
3.2.1 General Rules ..................................................................................................................... 8
3.2.2 Numeric Data Types ........................................................................................................ 11
3.2.3 Character Data Types ...................................................................................................... 12
3.2.4 Boolean Data Types ......................................................................................................... 13
3.2.5 Large Objects ................................................................................................................... 13
3.3 DML and SQL ........................................................................................................................ 14
3.4 Control Structures ................................................................................................................ 16
3.4.1 CURSOR............................................................................................................................. 16
3.4.2 CASE / IF / DECODE / NVL / NVL2 / COALESCE ................................................................ 19
3.5 Flow Control ......................................................................................................................... 21
3.6 Exception Handling .............................................................................................................. 23
3.7 Dynamic SQL ........................................................................................................................ 26
3.8 Stored Objects ...................................................................................................................... 28
3.9 Packages .............................................................................................................................. 29
3.10 Procedures ........................................................................................................................... 30
3.11 Object Types ......................................................................................................................... 30
3.12 Trigger ................................................................................................................................... 30
3.13 Patterns ................................................................................................................................ 30
3.13.1 Checking the Number of Rows .................................................................................... 30
3.13.2 Access objects of foreign schemas ............................................................................. 31
4 Code Formatting .......................................................................................................................... 32
4.1 Indentation ........................................................................................................................... 32
4.2 Using Case to Aid Readability .............................................................................................. 33
4.3 Formatting Single Statements ............................................................................................. 33
4.4 Formatting Declarations ...................................................................................................... 34
4.5 Formatting Multiline Statements ......................................................................................... 34
5 PLSQL programming tools ........................................................................................................... 34
5.1 Oracle SQL Developer .......................................................................................................... 35
5.2 TOAD™ .................................................................................................................................. 35
5.3 SQL*PLUS .............................................................................................................................. 35
5.4 PL/SQL Developer ................................................................................................................. 35
5.5 SQL Navigator® ................................................................................................................... 35
6 Bibliography ................................................................................................................................. 35
1 Introduction
Benefits of Having Standards
Fewer decisions to make
Having guidelines to follow means that there are some decisions that you not have to make. You also do
not have to justify, argue and defend every decision you make.
Clearer Understanding
Whether you are amending an existing program or writing something new, clearly structured code will tells
you what kind of object you are dealing with and how it fits into the structure. Often when facing a
debugging problem in poorly maintained code, the first step is to straighten out the layout, allowing you to
see how it can be rationalized. Often just understanding what the code means is halfway to solving a
programming problem. Clearly, not understanding what you are working on is likely to prove a hindrance to
efficient programming.
Better Code
Simply using clear names for objects, and laying out code so that the structure is easy to follow, should
reduce spaghetti code and result in better-structured modules. It will be easier for others to see how the
code works, and therefore modify it as necessary without increasing code entropy, which occurs when the
originally intended design of a module is eroded by subsequent changes. The entropy accelerates as the
code becomes harder to understand. You don't have time to figure out how all the sprawling loops and
confusing variables interact with each other, so you paste in another ELSIF and hope for the best.
Eventually it becomes impossible for anyone to figure out with any confidence how the code really works
and you have what is known as a legacy system or third party application.
Easier Maintenance
Maintenance programming becomes easier since the code is easier to read and modify. Coding standards
increase productivity in the development process, make code easier to maintain, and keep code from being
tied to one person or team
Easier to Train
With standard guidelines, it is easy to set up a training program, whether it is hands on session or a CBT.
New hires can be trained in a day or two to conform to the corporate standards.
Easier to Automate
Coding standards are a must for any development team. They ensure consistency and simplify the
maintenance process with legible code
Encapsulation of related functionality is key to making our software maintainable and upgradeable. Try to
bundle your code into packages whenever possible. This will make upgrading, bug fixing, customizing, and
many other things, a possibility
2 Naming Conventions
2.1 File Naming
Maintain your code in text files, rather than editing directly on the database using TOAD™ or PL/SQL Developer or Oracle SQL Developer, etc. This gives you version control, as well as protecting you in the event of your stored module being modified or dropped by another user.
For production code, define package specification and bodies in separate files. This gives them independent version control, and also makes it easier to install a new version of a package body with minimal impact on other schema objects. (The same applies to types.) The following extensions are to be followed.
File Type Extension Example
Package Body pkb Package_Name.pkb
Package Specification pks Package_Name.pks
Procedure prc Procedure_Name.prc
Function fnc Function_Name.fnc
Object type specification typ Type_Name.typ
Object type body tyb Type_Name.tyb
Trigger trg Trigger_Name.trg
Stored Java Procedure sjp Java_Procedure_Name.sjp
Stored Java Function sjf Java_Function_Name.sjf
Anonymous block sql testtrg.sql
DML Script sql Script_Name.tab
Test script tst Script_Name.tst
DDL statements sql Script_Name.sql
2.2 Identifier naming conventions Think of all identifiers as consisting of 5 parts.
<Scope><Type><Primary Identifier><Modifier><Suffix>.
Of the 5 elements only the primary identifier is required. All others are optional and only make the name
better self-documenting.
Scope
Scope is the locality of reference. Knowing this is invaluable to the maintenance programmer. Notice that p is added as the scope of a parameter. This is a good way of denoting that a variable is a parameter to the procedure.
Examples:
Locality Description Example
G Global gv_temp
L Local lv_temp
P Parameter p_param1
Type
Use the simplest possible types there are. There are only two, constants and variables. You can further breakdown these two into the individual data types, but then it gets complicated. We sure do not want to write complicated code.
Examples:
Type Description Example Comment
C Constant gc_loop_count Global constant
C Constant lc_loop_count Local Constant.
C Constant pc_loop_count Parameter
V Variable gv_loop_count Global Variable
V Variable lv_loop_count Local Variable
V Variable pv_loop_count Parameter
In addition to these scalar types, there are other data types that are supported by the PL/SQL language. They are aggregate data types, which are as follows:
Type Description Example
cur Cursor gcur_employee
vcr Cursor(variable) lvcr_employee
tbl Table gtbl_employee
rec Record ltrec_address
One more thing to define. There are two special constructs that are available in PL/SQL. They are Type and Subtype. We are going to treat these as data types.
Type Description Example
typ TYPE gtyp_new_account_table
stp SUBTYPE lstp_employee_ID
Primary identifier
It is the most important part of a name. This can be a single word or a phrase. Example
Account, Student, StuAddr, LearnerEnroll etc.
A modifier further qualifies a primary identifier to make it more readable. These modifiers can either precede or succeed a primary identifier. Modifier It further qualifies a primary identifier to make it more readable. These modifiers can either precede or succeed a primary identifier. Examples:
Primary Identifier Modifier Position Variable
address mailing Precede mailing_address
phone home Precede home_phone
customer_name last Middle customer_last_name
Suffix
The suffix is used to qualify the identifier further to document the usage of the variable. For example, the suffix is used to denote the type of parameter, as in IN, OUT, or INOUT
Type Description Example
I Input only parameter pv_num_items_i
O Output only parameter pv_sum_o
Io Both input and output pv_sum_io
Now that some basic standards are defined let us look at how some of these standards are used in practice.
3 PLSQL Coding Guidelines
3.1 General Rules
Rule 1 : Label your sub blocks.
Rule 2 : Always have a matching loop or block label.
Reason:
Use a label directly in front of loops and nested anonymous blocks:
-To give a name to that portion of code and thereby self-document what it is doing. -So that you can repeat that name with the END statement of that block or loop.
Example:
-- Good BEGIN << prepare_data >> BEGIN NULL; END prepare_data ; << process_data >> BEGIN NULL; END process_data ; END;
Rule 3 : Avoid defining variables that are not used.
Rule 4 : Avoid dead code in your programs.
Reason: Any part of your code, which is no longer used or cannot be reached, should be eliminated from your programs.
Rule 5 : Avoid using literals in your code.
Reason:
- Literals are often used more than once in your code. Having them defined as a constant reduces typos in your code.
- All constants should be collated in just one package used as a library. If these constants should be used in SQL too it is good practice to write a get_<name> deterministic package function for every constant.
Example: -- Bad DECLARE l_function player . function_name %TYPE; BEGIN SELECT p. FUNCTION INTO l_function FROM player p WHERE Name = '' ; IF l_function = 'LEADER' THEN NULL; END IF ; END;
-- Good
CREATE OR replace PACKAGE constants_up IS co_leader CONSTANT player . function_name %TYPE := 'LEADER' ; END constants_up ; / DECLARE l_function player . function_name %TYPE; BEGIN SELECT p. FUNCTION INTO l_function FROM player p WHERE name = '' ; IF l_function = constants_up . co_leader THEN NULL; END IF ; END;
Rule 6 : Avoid storing ROWIDs or UROWIDs in a table.
Reason:
It is an extremely dangerous practice to store ROWIDs in a table, except for some very limited scenarios of runtime duration. Any manually explicit or system generated implicit table reorganization will reassign the row's ROWID and break the data consistency
Rule 7 : Avoid nesting comment blocks.
Reason:
A start-of-comment (/*) was found within comments. This can make the code more difficult to read.This situation can arise as you go through multiple modifications to code.
3.2 Variables and Types
3.2.1 General Rules
Rule 8 : Try to use anchored declarations for variables, constants and types.
Reason:
Changing the size of the database column ename in the emp table from VARCHAR2 (10) to VARCHAR2 (20) will result in an error within your code whenever a value larger than 10 bytes is read. This can be avoided using anchored declarations.
Example:
-- Bad DECLARE l_ename VARCHAR2( 10) ; BEGIN SELECT e. ename INTO l_ename FROM emp e WHERE eid = '100' ; END;
-- Good DECLARE l_ename emp. ename%TYPE; BEGIN SELECT e. ename INTO l_ename FROM emp e WHERE eid = '100' ; END;
Rule 9 : Have a single location to define your types. This single type could either be a type specification package or the database (database defined types).
Reason:
Single point of change when changing the data type. No need to argue where to define types or where to look for existing definitions.
Rule 10 : Never initialize variables with NULL.
Reason:
Variables are initialized to NULL by default.
Rule 11 : Avoid comparisons with NULL value, consider using IS [NOT] NULL.
Reason:
The NULL value can cause confusion both from the standpoint of code review and code execution. You should always use the IS NULL or IS NOT NULL syntax when you need to check to see if a value is or is not NULL.
Rule 12 : Avoid initializing variables using functions in the declaration section.
Reason:
If your initialization fails you will not be able to handle the error in your exceptions block. Example:
-- Bad DECLARE l_code_section VARCHAR2( 30) := 'TEST_PCK' ; l_company_name VARCHAR2( 30) := util_pck . Get_company_name (in_id => 47); BEGIN --Code Section NULL; END;
-- Good DECLARE l_code_section VARCHAR2( 30) := 'TEST_PCK' ; l_company_name VARCHAR2( 30); BEGIN << init >> BEGIN l_companyName := util_pck . Get_company_name(inId => 47 ); EXCEPTION WHEN VALUE_ERROR THEN --Handle Exception NULL; END; END;
Rule 13 : Never overload data structure usages.
Example: -- Bad <<main >> DECLARE l_variable VARCHAR2( 30) := 'TEST_PCK' ; BEGIN << sub >> DECLARE l_variable VARCHAR2( 30) := 'TEST' ; BEGIN dbms_output.Put _line(l_variable || '-' || main . l_variable ); END sub ; END main ;
Rule 14 : Never use quoted identifiers.
Reason:
Quoted identifiers make your code hard to read and maintain.
Example:
-- Bad DECLARE "sal+comm" NUMBER( 10) ; BEGIN --Code Section NULL; END;
Rule 15 : Avoid using overly short names for declared or implicitly declared identifiers.
Reason:
You should ensure that the name you’ve chosen well defines its purpose and usage. While you can save a few keystrokes typing very short names, the resulting code is obscure and hard for anyone besides the author to understand.
Rule 16 : Avoid the use of ROWID or UROWID
Reason:
Rule 17 : Be careful about your use of Oracle-specific data types like ROWID and UROWID. They might offer a slight improvement in performance over other means of identifying a single row (primary key or unique index value), but that is by no means guaranteed.
Rule 18 : Use of ROWID or UROWID means that your SQL statement will not be portable to other SQL databases. Many developers are also not familiar with these data types, which can make the code harder to maintain.
3.2.2 Numeric Data Types
Rule 19 : Avoid declaring NUMBER variables or subtypes with no precision
Reason:
If you do not specify precision NUMBER is defaulted to 38 or the maximum supported by your system, whichever is less. You may well need all this precision, but if you know you do not, you should specify whatever matches your needs.
Rule 20 : Try to use PLS_INTEGER instead of NUMBER for arithmetic operations with integer
values (no decimal point).
Reason:
- PLS_INTEGER has a length of -2,147,483,648 to 2,147,483,647, on a 32bit system.
- There are many reasons to use PLS_INTEGER instead of NUMBER: o PLS_INTEGER uses less memory o PLS_INTEGER uses machine arithmetic, which is up to three times
faster than library arithmetic which is used by NUMBER. -
With ORACLE 11g, the new data type SIMPLE_INTEGER has been
introduced. It is a sub-type of PLS_INTEGER and covers the same range. The basic difference is that
- SIMPLE_INTEGER is always NOT NULL. When the value of the declared variable is never going to be NULL then you can declare it as SIMPLE_INTEGER.
- Another major difference is that you will never face a numeric overflow using SIMPLE_INTEGER as this data type wraps around without giving any error.
- Another difference is that the SIMPLE_INTEGER data type gives major performance boost over PLS_INTEGER when code is compiled in 'NATIVE' mode, because arithmetic operations on SIMPLE_INTEGER type are performed directly at the hardware level.
3.2.3 Character Data Types
Rule 21 : Avoid using CHAR data type.
Reason:
CHAR is a fixed length data type which should only be used when appropriate. CHAR columns/variables are always filled to the specified length with spaces; this may lead to side-effects when comparing the columns with VARCHAR2.
Rule 22 : Avoid using VARCHAR data type.
Reason:
The VARCHAR data type is a subtype of VARCHAR2. There is a strong possibility, that the meaning of VARCHAR might change in future version of ANSI SQL Standard. ORACLE recommends that you avoid using VARCHAR and use VARCHAR2 instead.
Rule 23 : Never use zero-length strings to substitute NULL.
Reason:
Today zero-length strings and NULL are handled similarly by ORACLE. There is no guarantee that this will still be the case in future releases, therefore if you mean NULL use NULL.
Example: -- Bad
l_char := ‘’ ;
-- Good
l_char := NULL;
3.2.4 Boolean Data Types
Rule 24 : Try to use BOOLEAN data type for values with dual meaning.
Reason:
The use of TRUE and FALSE clarifies that this is a Boolean value and makes the code easier to read.
Example: -- Bad DECLARE l_bigger NUMBER( 1); BEGIN IF l_newFile < l_oldFile THEN l_bigger := 1; ELSE l_bigger := 0; END IF ; END;
-- Good DECLARE l_bigger BOOLEAN; BEGIN IF l_newFIle < l_oldFile THEN l_bigger := TRUE; ELSE l_bigger := FALSE; END IF ; END; -- Better DECLARE l_bigger BOOLEAN; BEGIN l_bigger := Nvl(l_newFile < l_oldFile , FALSE); END;
3.2.5 Large Objects
Rule 25 : Avoid using the LONG data type, instead use LOB data types.
Reason:
LONG column support will be discontinued in future ORACLE releases.
The use of LONG values is subject to these restrictions:
• A table can contain only one LONG column. • You cannot create an object type with a LONG attribute. • LONG columns cannot appear in WHERE clauses or in integrity constraints
(except that they can appear in NULL and NOT NULL constraints). • LONG columns cannot be indexed. • LONG data cannot be specified in regular expressions. • A stored function cannot return a LONG value. • You can declare a variable or argument of a PL/SQL program unit using the
LONG data type. However, you cannot then call the program unit from SQL. • Within a single SQL statement, all LONG columns, updated tables, and
locked tables must be located on the same database. • LONG and LONG RAW columns cannot be used in distributed SQL
statements and cannot be replicated. • If a table has both LONG and LOB columns, then you cannot bind more than
4000 bytes of data to both the LONG and LOB columns in the same SQL statement. However, you can bind more than 4000 bytes of data to either the LONG or the LOB column.
In addition, LONG columns cannot appear in these parts of SQL statements:
• GROUP BY clauses, ORDER BY clauses, or CONNECT BY clauses or with the DISTINCT operator in SELECT statements
• The UNIQUE operator of a SELECT statement • The column list of a CREATE CLUSTER statement • The CLUSTER clause of a CREATE MATERIALIZED VIEW statement • SQL built-in functions, expressions, or conditions
• SELECT lists of queries containing GROUP BY clauses • SELECT lists of subqueries or queries combined by the UNION, INTERSECT, or
MINUS set operators • SELECT lists of CREATE TABLE ... AS SELECT statements
• ALTER TABLE ... MOVE statements • SELECT lists in subqueries in INSERT statements
Despite the size of this list, we'll find that most of the time we are blocked by the two restrictions highlighted above.
3.3 DML and SQL
Rule 26 : Always specify the target columns when executing an insert command.
Reason:
The use of TRUE and FALSE clarifies that this is a Boolean value and makes the code easier to read.
Example: -- Bad INSERT INTO dossier VALUES (lv_do_key , lv_do_plaat ) ;
-- Good INSERT INTO dossier (do_key , do_plaat) VALUES (lv_do_key , lv_do_plaat ) ;
Rule 27 : Always use table aliases when your SQL statement involves more than one
source.
Reason:
It is more human readable to use aliases instead of writing columns with no table information.
Example: -- Good SELECT a. pid , a. name, a. birthday , b. country FROM person a JOIN country b ON ( a. cid = b. cid ) WHERE ……
Rule 28 : Try to use ANSI-Join syntax, if supported by your ORACLE version.
Reason:
- ANSI join syntax does not have as many restrictions as the ORACLE join syntax.
- Furthermore ANSI join syntax supports the full outer join. - A third advantage of the ANSI join syntax is the separation of the join
condition from the query filters.
Example: -- Good SELECT a. pid , a. name, a. birthday , b. country FROM person a JOIN country b ON ( a. cid = b. cid ) WHERE …..
Rule 29 : Try to use anchored records as targets for your cursors.
Reason:
- ANSI join syntax does not have as many restrictions as the ORACLE join syntax.
- Furthermore ANSI join syntax supports the full outer join. - A third advantage of the ANSI join syntax is the separation of the join
condition from the query filters.
Example:
-- Bad DECLARE CURSOR c_dossier IS SELECT do_key, do_plaat, do_eidat FROM dossier; lv_do_key dossier.do_key%TYPE; lv_do_plaat dossier.do_plaat%TYPE; lv_do_eidat dossier.do_eidat%TYPE; BEGIN OPEN c_dossier ; FETCH c_dossier INTO lv_do_key, lv_do_plaat, lv_do_eidat ; -- do something with the data NULL; END LOOP; CLOSE c_dossier; END;
-- Good DECLARE CURSOR c_dossier IS SELECT do_key, do_plaat, do_eidat FROM dossier; lv_do_key c_dossier %ROWTYPE; BEGIN OPEN c_dossier ; FETCH c_dossier INTO lv_do_key ; -- do something with the data NULL; END LOOP; CLOSE c_dossier; END;
3.4 Control Structures
3.4.1 CURSOR
Rule 30 : Always use %NOTFOUND instead of NOT %FOUND to check whether a cursor was
successful.
Reason:
The readability of your code will be higher when you avoid negative sentences.
Example: -- Bad BEGIN LOOP FETCH c_employees INTO r_employee ; EXIT WHEN NOT c_employees %FOUND; NULL; END LOOP; END;
-- Good BEGIN LOOP FETCH c_employees INTO r_employee ; EXIT WHEN c_employees %NOTFOUND; NULL; END LOOP; END;
Rule 31 : Always close locally opened cursors.
Reason:
- Any cursors left open can consume additional System Global Area (i.e. SGA) memory space within the database instance, potentially in both the shared and private SQL pools.
- Furthermore, failure to explicitly close cursors may also cause the owning session to exceed its maximum limit of open cursors (as specified by the OPEN_CURSORS database initialization parameter), potentially resulting in the Oracle error of “ORA-01000: maximum open cursors exceeded”.
o For example, the following procedure opens and fetches, but does not close its cursor – which may cause problems like those described above:
Example: -- Bad CREATE PROCEDURE Not_close_cursor (out_count OUT INTEGER) AS CURSOR c1 IS SELECT COUNT ( * ) FROM all_users ; BEGIN out_count := 0 ; OPEN c1 ;
FETCH c1 INTO out_count ; NULL; END;
-- Good CREATE PROCEDURE Not_close_cursor (out_count OUT INTEGER) AS CURSOR c1 IS SELECT COUNT ( * ) FROM all_users ; BEGIN out_count := 0 ; OPEN c1 ; FETCH c1 INTO out_count ; NULL; CLOSE c1 ; END;
Rule 32 : Avoid procedure or function calls between a SQL operation and an implicit cursor
test.
Reason:
Oracle provides a variety of cursor attributes, such as %FOUND and %ROWCOUNT, which you can use to obtain information about the status of your cursor, either implicit or explicit. You should avoid inserting any statements between the cursor operation and the use of an attribute against that cursor. Interposing such a statement can affect the value returned by the attribute, thereby potentially corrupting the logic of your program. In the following example, a procedure call is inserted between the DELETE statement and a check for the value of SQL%ROWCOUNT, which returns the number of rows modified by that last SQL statement executed in the session.
Example: -- Bad CREATE PROCEDURE Remove_emp_and_process (in_id IN emp. empno%TYPE) AS BEGIN DELETE FROM emp WHERE empno = in_id returning deptno INTO l_deptno ; Process_department () ; IF SQL%rowcount > 1 THEN -- Too many rows deleted! Rollback and recover... ROLLBACK; END IF ; END remove_emp_and_process ;
3.4.2 CASE / IF / DECODE / NVL / NVL2 / COALESCE
Rule 33 : Try to use CASE rather than an IF statement with multiple ELSIF paths.
Reason:
IF statements containing multiple ELSIF tend to become complex quickly.
Example: -- Bad IF l_color = 'red' THEN NULL; ELSIF l_color = 'blue' THEN NULL; ELSIF l_color = 'black' THEN NULL; END IF ;
-- Good CASE l_color WHEN 'red' THEN ... WHEN 'blue' THEN ... WHEN 'black' THEN ... END;
Rule 34 : Try to use CASE rather than DECODE.
Reason:
DECODE is an old function that has been replaced by the easier-to- understand and more common CASE function. Contrary to the DECODE statement CASE may also be used directly within PL/SQL.
Example: -- Bad BEGIN SELECT Decode(dummy, 'A' , 1 , 'B' , 2 , 'C' , 3 , 'D' , 4 , 'E' , 5 , 'F' , 6 , 7 ) INTO l_result FROM dual ; END;
-- Good BEGIN l_result := CASE dummy WHEN 'A' THEN 1 WHEN 'B' THEN 2 WHEN 'C' THEN 3 WHEN 'D' THEN 4 WHEN 'E' THEN 5 WHEN 'F' THEN 6 ELSE 7 END; END;
Rule 35 : Always use COALESCE instead of NVL, if parameter 2 of the NVL function is a
function call or a SELECT statement.
Reason:
The NVL function always evaluates both parameters before deciding which one to use. This can be harmful if parameter 2 is either a function call or a select statement, as it will be executed regardless of whether parameter 1 contains a NULL value or not. The COALESCE function does not have this drawback.
Example: -- Bad SELECT Nvl(dummy, Function_call()) FROM dual ;
-- Good SELECT Coalesce(dummy, Function_call()) FROM dual ;
Rule 36 : Always use CASE instead of NVL2 if parameter 2 or 3 of NVL2 is either a function
call or a SELECT statement.
Reason:
The NVL2 function always evaluates all parameters before deciding which one to use. This can be harmful, if parameter 2 or 3 is either a function call or a select statement, as they will be executed regardless of whether parameter 1 contains a NULL value or not.
Example: -- Bad SELECT Nvl2(dummy, 'Yes' , 'No' ) FROM dual ;
-- Good SELECT CASE WHEN dummy IS NULL THEN 'No' ELSE 'Yes' END
FROM dual ;
3.5 Flow Control
Rule 37 : Never use GOTO statements in your code.
Rule 38 : Always label your loops.
Example: -- Good BEGIN << process_employees >> FOR r_employee IN ( SELECT * FROM emp) LOOP NULL; END LOOP process_employees ; END;
Rule 39 : Always use a CURSOR FOR loop to process the complete cursor results unless you are
using bulk operations.
Example: -- Good BEGIN << read_employees >> FOR r_employee IN c_employee LOOP NULL; END LOOP read_employees ; END;
Rule 40 : Always use a NUMERIC FOR loop to process a dense array.
Example: -- Good BEGIN << process_employees >> FOR i IN t_employees . First() .. t_employees . Last() LOOP NULL; END LOOP process_employees ; END;
Rule 41 : Always use a WHILE loop to process a loose array.
Example: -- Good DECLARE l_index PLS_INTEGER; BEGIN l_index := t_employees . First() ; << processemployees >> WHILE l_index IS NOT NULL LOOP l_index := t_employees . NEXT(l_index) ;
END LOOP process_employees ; END
Rule 42 : Avoid using EXIT to stop loop processing unless you are in a basic loop.
Rule 43 : Always use EXIT WHEN instead of an IF statement to exit from a loop.
Example: -- Bad BEGIN << process_employees >> LOOP IF lv_count > 2 THEN EXIT process_employees ; END IF ; END LOOP process_employees ; END;
-- Good BEGIN << process_employees >> LOOP EXIT process_employees WHEN ( lv_count > 2 ) ; END LOOP process_employees ; END;
Rule 44 : Try to label your EXIT WHEN statements.
Example: -- Good BEGIN << outerloop >> FOR l_outlp IN 1 .. 2 LOOP << innerloop >> FOR l_innerlp IN 1 .. 4 LOOP dbms_output . Put_line( 'Outer Loop counter is ' || v_outerlp || ' Inner Loop counter is ' || v_innerlp) ; EXIT outerloop WHEN v_innerlp = 3 ; END LOOP innerloop ; END LOOP outerloop ; END;
Rule 45 : Do not use a cursor for loop to check whether a cursor returns data.
Example: -- Bad DECLARE l_employee_found BOOLEAN := FALSE; BEGIN << check_employees >>
FOR r_employee IN c_employee LOOP l_employee_found := TRUE; END LOOP check_employees ; END;
-- Good DECLARE l_employee_found BOOLEAN := FALSE; BEGIN OPEN c_employee ; FETCH c_employee INTO r_employee ; l_employee_found := c_employee %FOUND; CLOSE c_emplyoee ; END;
Rule 46 : Avoid use of unreferenced FOR loop indexes.
Reason:
The loop index is not used for anything but traffic control inside the loop. This is one of the indicators that a numeric FOR loop is being used incorrectly. The actual body of executable statements completely ignores the loop index. When that is the case, there is a good chance that you don't need the loop at all.
Rule 47 : Avoid hard-coded upper or lower bound values with FOR loops.
Reason:
When you’re LOOP statement uses a hard-coded value for either its upper or lower bounds. This creates a "weak link" in your program because it assumes that this value will never change. A better practice is to create a named constant (or function) in a package specification and reference this named element instead of the hard-coded value.
3.6 Exception Handling
Rule 48 : Never handle unnamed exceptions using the error number.
Example: -- Bad BEGIN --Code Section NULL; EXCEPTION WHEN OTHERS THEN IF SQLCODE = - 1 THEN NULL; END IF ; END;
-- Good DECLARE e_employee_exists EXCEPTION; PRAGMA EXCEPTION_INIT(-1, e_employee_exists ); BEGIN --Code Section NULL; EXCEPTION WHEN e_employee_exists THEN --Handle exception NULL; END;
Rule 49 : Never assign predefined exception names to user defined exceptions.
Reason:
This is error-prone because your local declaration overrides the global declaration. While it is technically possible to use the same names, it causes confusion for others needing to read and maintain this code. Additionally, you will need to be very careful to use the prefix "STANDARD" in front of any reference that needs to use Oracle’s default exception behaviour.
Rule 50 : Avoid use of WHEN OTHERS clause in an exception section without any other specific
handlers.
Reason:
There isn't necessarily anything wrong with using WHEN OTHERS, but it can cause you to "lose" error information unless your handler code is relatively sophisticated. Generally, you should use WHEN OTHERS to grab any and every error only after you have thought about your executable section and decided that you are not able to trap any specific exceptions. If you know, on the other hand, that a certain exception might be raised, include a handler for that error. By declaring two different exception handlers, the code more clearly states what we expect to have happen and how we want to handle the errors. That makes it easier to maintain and enhance. We also avoid hard-coding error numbers in checks against SQLCODE.
Example: --Bad BEGIN NULL; EXCEPTION WHEN OTHERS THEN IF SQLCODE = - 1 THEN Update_instead () ; ELSE err . log ; RAISE; END IF ; END;
--Good BEGIN NULL; EXCEPTION WHEN DUP_VAL_ON_INDEX THEN Update_instead () ; WHEN OTHERS THEN err . log ; RAISE; END;
Rule 51 : Avoid use of EXCEPTION_INIT pragma for a -20,NNN error
Reason:
DECODE is an old function that has been replaced by the easier-to- understand and more common CASE function. Contrary to the DECODE statement CASE may also be used directly within PL/SQL.
Example: --Bad CREATE OR replace PROCEDURE Check_hiredate (date_in IN DATE) IS BEGIN IF date_in < Add_months ( SYSDATE, - 1 * 12 * 18 ) THEN Raise_application_error ( -20734 , 'Employee must be 18 years old.' ) ; END IF ; END check_hiredate ;
--Good CREATE OR replace PROCEDURE Check_hiredate (date_in IN DATE) IS BEGIN IF emp_rules . Emp_too_young (date_in) THEN err . log (errnums.emp_too_young); END IF ; END check_hiredate ;
Rule 52 : Avoid use of the RAISE_APPLICATION_ERROR built-in procedure with a hard-coded -20,
NNN error number or hard-coded message.
Reason:
If you are not very organized in the way you allocate, define and use the error numbers between -20,999 and -20,000 (those reserved by Oracle for its user community), it is very easy to end up with conflicting usages. You should assign these error numbers to named constants and consolidate all definitions within a single package. When you call RAISE_APPLICATION_ERROR, you should reference these named elements and error message text stored in a table. Use your own raise procedure in place of explicit calls to RAISE_APPLICATION_ERROR. If you are raising a "system" exception like NO_DATA_FOUND, you must use RAISE. But when you want to raise an application-specific error, you use
RAISE_APPLICATION_ERROR. If you use the latter, you then have to provide an error number and message. This leads to unnecessary and damaging hard-coded values. A more fail-safe approach is to provide a predefined raise procedure that automatically checks the error number and determines the correct way to raise the error.
Rule 53 : Avoid unhandled exceptions
Reason:
This may be your intention, but you should review the code to confirm this behaviour.
If you are raising an error in a program, then you are clearly predicting a situation in which that error will occur. You should consider including a handler in your code for predictable errors, allowing for a graceful and informative failure. After all, it is much more difficult for an enclosing block to be aware of the various errors you might raise and more importantly, what should be done in response to the error.
The form that this failure takes does not necessarily need to be an exception. When writing functions, you may well decide that in the case of certain exceptions, you will want to return a value such as NULL, rather than allow an exception to propagate out of the function.
Rule 54 : Avoid using Oracle’s predefined exceptions
Reason:
You have raised an exception whose name was defined by Oracle. While it is possible that you have a good reason for "using" one of Oracle's predefined exceptions, you should make sure that you would not be better off declaring your own exception and raising that instead.
If you decide to change the exception you are using, you should apply the same consideration to your own exceptions. Specifically, do not "re-use" exceptions. You should define a separate exception for each error condition, rather than use the same exception for different circumstances.
Being as specific as possible with the errors raised will allow developers to check for, and handle, the different kinds of errors the code might produce.
3.7 Dynamic SQL
Rule 55 : Always use a string variable to execute dynamic SQL.
Reason:
Having the executed statement in a variable makes it easier to debug your code.
Example:
-- Bad DECLARE l_empno emp. empno%TYPE := 4711 ; BEGIN EXECUTE IMMEDIATE 'DELETE FROM emp WHERE epno = :p_empno' USING l_empno ; END;
-- Good DECLARE l_empno emp. empno%TYPE := 4711 ; l_sql VARCHAR2( 32767 ) ; BEGIN l_sql := 'DELETE FROM emp WHERE epno = :p_empno' ; EXECUTE IMMEDIATE l_sql USING l_empno ; EXCEPTION WHEN OTHERS THEN dbms_output . Put_line(l_sql) ; END;
Rule 56 : Try to use output bind arguments in the RETURNING INTO clause of dynamic INSERT,
UPDATE, or DELETE statements rather than the USING clause.
Reason:
When a dynamic INSERT, UPDATE, or DELETE statement has a RETURNING clause, output bind arguments can go in the RETURNING INTO clause or in the USING clause. You should use the RETURNING INTO clause for values returned from a DML operation. Reserve OUT and IN OUT bind variables for dynamic PL/SQL blocks that return values in PL/SQL variables.
Example: DECLARE sql_stmt VARCHAR2( 200 ) ; my_empno NUMBER( 4) := 7902 ; my_ename VARCHAR2( 10) ; my_job VARCHAR2( 9) ; my_sal NUMBER( 7, 2 ) := 3250.00 ; BEGIN sql_stmt := 'UPDATE emp SET sal = :1 WHERE empno = :2 RETURNING ename, job INTO :3, :4' ; /* OLD WAY: Bind returned values through USING clause. */ EXECUTE IMMEDIATE sql_stmt USING my_sal , my_empno, OUT my_ename, OUT my_job ; /* NEW WAY: Bind returned values through RETURNING INTO clause. */ EXECUTE IMMEDIATE sql_stmt USING my_sal , my_empno returning INTO my_ename, my_job ; END;
3.8 Stored Objects
Rule 57 : Try to use named notation when calling program units.
Reason:
Named notation makes sure those changes to the signature of the called program unit do not affect your call. This is not needed for standard functions like (TO_CHAR, TO_DATE, NVL, ROUND, etc.) but should be followed for any other stored object having more than one parameter.
Example: -- Good BEGIN r_emp := Read_employee(p_empno_in => l_empno , p_ename_in => l_ename) ; END;
Rule 58 : Always add the name of the program unit to its end keyword.
Example: -- Good FUNCTION Get_emp (in_empno IN emp. empno%TYPE) RETURN emp%ROWTYPE IS BEGIN NULL; END get_emp ;
Rule 59 : Always use parameters or pull in definitions rather than referencing external variables in
a local program unit.
Reason:
Local procedures and functions offer an excellent way to avoid code redundancy and make your code more readable (and thus more maintainable). Your local program makes a reference, however, to an external data structure, i.e., a variable that is declared outside of the local program. Thus, it is acting as a global variable inside the program.
This external dependency is hidden, and may cause problems in the future. You should instead add a parameter to the parameter list of this program and pass the value through the list. This technique makes your program more reusable and avoids scoping problems, i.e. the program unit is less tied to particular variables in the program. In addition, unit encapsulation makes maintenance a lot easier and cheaper.
Rule 60 : Always ensure that locally defined procedures or functions are referenced.
Reason:
This can occur as the result of changes to code over time, but you should make sure that this situation does not reflect a problem. And you should remove the
declaration to avoid maintenance errors in the future.
You should go through your programs and remove any part of your code that is no longer used. This is a relatively straightforward process for variables and named constants. Simply execute searches for a variable's name in that variable's scope. If you find that the only place it appears is in its declaration, delete the declaration.
There is never a better time to review all the steps you took, and to understand the reasons you took them, then immediately upon completion of your program. If you wait, you will find it particularly difficult to remember those parts of the program that were needed at one point, but were rendered unnecessary in the end.
Rule 61 : Try to remove unused parameters or modify code to use the parameter.
Reason:
This can occur as the result of changes to code over time, but you should make sure that this situation does not reflect a problem in your code. You should go through your programs and remove any part of your code that is no longer used.
3.9 Packages
Rule 62 : Try to keep your packages small. Include only few procedures and functions that are used
in the same context.
Rule 63 : Always use forward declaration for private functions and procedures.
Rule 64 : Avoid declaring global variables public.
Reason:
You should always declare package-level data inside the package body. You can then define "get and set" methods (functions and procedures, respectively) in the package specification to provide controlled access to that data. By doing so you can guarantee data integrity, change your data structure implementation, and also track access to those data structures.
Data structures (scalar variables, collections, cursors) declared in the package specification (not within any specific program) can be referenced directly by any program running in a session with EXECUTE rights to the package.
Instead, declare all package-level data in the package body and provide "get and set" programs - a function to GET the value and a procedure to SET the value - in the package specification. Developers then can access the data using these methods - and will automatically follow all rules you set upon data modification.
Rule 65 : Avoid using an IN OUT parameters as IN / OUT only.
Reason:
By showing the mode of parameters, you help the reader. If you do not specify a parameter mode, the default mode is IN. Explicitly showing the mode indication of all parameters is a more assertive action than simply taking the default mode. Anyone reviewing the code later will be more confident that you intended the parameter mode to be IN /OUT.
3.10 Procedures
Rule 66 : Avoid standalone procedures – put your procedures in packages.
Rule 67 : Avoid using RETURN statements in a PROCEDURE.
Reason:
Use of the RETURN statement is legal within a procedure in PL/SQL, but it is very similar to a GOTO, which means you end up with poorly-structured code that is hard to debug and maintain.
A good general rule to follow as you write your PL/SQL programs is: "one way in and one way out". In other words, there should be just one way to enter or call a program, and there should be one way out, one exit path from a program (or loop) on successful termination. By following this rule, you end up with code that is much easier to trace, debug, and maintain.
Rule 68 : Avoid standalone functions – put your functions in packages.
Rule 69 : Try to use no more than one RETURN statement within a function.
Rule 70 : Always make the RETURN statement the last statement of your function.
Rule 71 : Never use OUT parameters to return values from a function.
Reason:
A function should return all its data through the RETURN clause. Having an OUT parameter prohibits usage of a function within SQL statements.
Rule 72 : Never return a NULL value from a BOOLEAN function.
3.11 Object Types
Rule 73 : There are no object type-specific recommendations to be defined at the time of writing.
3.12 Trigger
Rule 74 : Avoid cascading triggers.
Reason:
Having triggers that act on other tables in a way that causes triggers on that table to fire lead to obscure behaviour.
3.13 Patterns
3.13.1 Checking the Number of Rows
Rule 75 : Never use SELECT COUNT (*) if you are only interested in the existence of a row.
Reason:
If you do a SELECT count (*) all rows will be read according to the WHERE clause,
even if only the availability of data is of interest. For this we have a big performance overhead. If we do a SELECT count (*)... WHERE ROWNUM = 1 there is also a overhead as there will be two communications between the PL/SQL and the SQL engine. See the following example for a better solution.
Example: -- Bad BEGIN SELECT COUNT( * ) INTO l_count FROM cust_order WHERE co . part_nbr = 100 ; IF l_count > 0 THEN SELECT p. part_nbr , p. name, p. unit_cost FROM part p WHERE Part_id = 100 ; END IF ; END;
-- Good BEGIN SELECT p. part_nbr , p. name, p. unit_cost FROM part p WHERE EXISTS ( SELECT 1 FROM cust_order co WHERE co . part_nbr = p. part_nbr) ; END;
3.13.2 Access objects of foreign schemas
Rule 76 : Always use synonyms when accessing objects of another schema.
Reason:
If a connection is needed to a table that is placed in a foreign schema, using synonyms is a good choice. If there are structural changes to that table (e.g. the table name changes or the table changes into another schema) only the synonym has to be changed no changes to the package are needed (single point of change). If you only have read access for a table inside another schema, or there is another reason that doesn’t allow you to change data in this table, you can switch the synonym to a table in your own schema. This is also good practice for testers working on test systems.
Example:
-- Bad SELECT p. lastname INTO l_lastname FROM personal . persons p WHERE p. pnbr = p_pnbr_in ;
-- Good CREATE SYNONYM rech_s_person FOR personal . persons ; SELECT p. lastname INTO l_lastname FROM rech_s_person p WHERE p. pnbr = p_pnbr_in ;
4 Code Formatting
There are two points of view to formatting. One is the developer’s view. The other is the maintainer’s view. A good standard should meet the needs of both views. There is really one fundamental reason for formatting your code: “Reveal and reinforce the logical structure of your program”. Writing code to please the eye is a waste of time. Code never stays that way for long. What is more important is to show the structure and the intent of the program.
4.1 Indentation
Indentation is one of the most common and effective ways to display a program’s logical structure. Programs that are indented are lot easier to read than those that are not. Please be aware that indentation is a double edged sword. It is very easy to mislead with inconsistent indentation.
General indentation rules
• Indent and align nested control structures, continuation lines, and embedded units consistently. • Distinguish between indentation for nested control structures and for continuation lines. • Use spaces for indentation, not the tab character.
Indentation Recommendations
The following indentation conventions are recommended. Note that the minimum indentation is described. More spaces may be required for the vertical alignment recommended in subsequent guidelines.
• Use three spaces as the basic unit of indentation for nesting. • Use three spaces as the basic unit of indentation for continuation lines. 3 or 4 spaces is the ideal way to indent. This amount of spacing not only adequately reveals the logical structure of the code but also keeps the statements close enough together to read comfortably. You also don’t run off the edge of the page with deeply nested structures. Although you should try avoiding deeply nested structures, since most human brains can’t stack more than 5 items at a time.
Alignment
As mentioned above trying to keep programs pretty are a lot of work. Hence the following recommendations.
• Do not try to align statements, operators etc. vertically. This not only takes up time, but also leads to realigning text continuously.
• Indent continuation lines the same three spaces. • Provide one declaration per line (at most). • Place the first parameter specification on a separate line from the function or procedure
declaration. If any of the parameter types are forced beyond the line length limit, place the first parameter specification on a new line indented as for continuation lines.
• Place one formal parameter specification per line. You may choose to place more than one parameter per line, but always follow the previous rule.
4.2 Using Case to Aid Readability
PL/SQL code is made up of many different components: variables, form items, report fields, procedures, functions, loops, etc. All these fall into two major categories.
1. Reserved words and 2. Program specific identifiers.
Reserved words are those language elements that are used by PL/SQL. They have special meaning to the compiler and hence are reserved. Program specific identifiers are the names that a programmer gives to the various components of program such as variables, constants, procedures etc. The PL/SQL compiler treats these two types of text very differently. You can improve the readability of the code greatly by reflecting this difference in the way the text is displayed. Using indentation highlights the logical structure of a program. To distinguish between reserved words and program specific identifiers, use of the upper and lowercase strategy is recommended. Use all UPPER case of reserved words and lower case of program specific identifiers. This increases the readability of the code.
4.3 Formatting Single Statements
Most of the programs consist of single statements. Consistent approach to formatting and grouping such statements will improve the readability of the program. The following are recommended rules.
• Use at most one statement per line PL/SQL uses a logical line terminator, semicolon(;). This allows for placing more than one statement
per line as well as continuing a single statement on multiple lines.
lv_var1 := 0 ; lv_var2 := 1 ; -- This is valid
lv_var1 := 0 ; -- But code this
lv_var2 := 1 ; -- way instead.
• Use white space inside a statement. • Always include a space between an identifier and a separator.
lv_var1:=0; -- This is valid
lv_var1 := 0 ; -- But code this way for clarity.
• Use spaces to make module calls and their parameter lists more understandable.
calc_totals(pv_company_id, pv_end_of_year_date,pv_total_type); -- Valid
calc_totals (pv_company_id, pv_end_of_year_date, pv_total_type; -- Clearer
4.4 Formatting Declarations
Declaration section is used to declare local variables and other structures uses in the PL/SQL block. The following rules are recommended.
• Place one declaration on each line This follows the same logic that was described previously.
• Ignore alignment for declarations
This again is a personal preference. But keeping declarations aligned is probably more trouble than it is worth.
4.5 Formatting Multiline Statements
As mentioned previously, PL/SQLs logical line structure allows for very long strings of text for statements, which may not fit on a traditional line of 80 - 132 columns. So it is easy to spread statements like this across many lines, which makes it difficult to read. Here are some recommendations.
• Use indentation to offset all continuation lines under the first line. • This is the most important guideline. By indenting the continuation lines the same 3 spaces
that are recommended, they are subsumed under the first line. • Place the module name on a separate line from the parameters, Indent module-call
continuation lines to align parameters vertically.
Gen_stats
(pv_company_id,
pv_last_year_date,
pv_rollup_type,
pv_total) ; • Make it obvious that a statement is continued.
FOR month_index IN -- the IN statement needs its
Lv_first_month .. lv_last_month -- range
LOOP
gv_q1_sales :=
lv_month1_sales + -- An assignment cannot end with a “+”.
lv_month2_sales +
lv_month3_sales;
Gen_stats
(pv_company_id, pv_last_year_date, -- Last comma indicates
pv_rollup_type, pv_total) ; -- other parameters to follow.
5 PLSQL programming tools
This is definition of programming tool from Wikipedia
A programming tool or software development tool is a program or application that software developers use to
create, debug, maintain, or otherwise support other programs and applications. The term usually refers to
relatively simple programs that can be combined together to accomplish a task, much as one might use multiple
hand tools to fix a physical object.
But there are certain caveats about tools also. Certain tools hide some functionality or are configured in a way which makes us believe that any piece of code or certain commands will execute the same in another tool or the SQL*PLUS editor.
5.1 Oracle SQL Developer
Oracle SQL Developer is a free and fully supported graphical tool for database development. With SQL Developer, you can browse database objects, run SQL statements and SQL scripts, and edit and debug PL/SQL statements. You can also run any number of provided reports, as well as create and save your own. SQL Developer enhances productivity and simplifies your database development tasks. NOTE:-Oracle SQL Developer has a setting of NLS_SEMANTICS_LENGTH set to BYTE by default. So even if you have a Database with the NLS_SEMANTICS_LENGTH as CHAR (to support multi-byte characters like Japanese and Chinese), the table and columns created by this tool will be set to BYTE character length.
5.2 TOAD™
Quest Software Solution’s Toad™ is another nice tool, but used mostly for Database Administration. Quest has the SQL Navigator as the product for PL/SQL development. But this tool can also be effectively used for the development.
5.3 SQL*PLUS
Finally the default SQL*Plus, is a command line SQL and PL/SQL language interface and reporting tool that ships with the Oracle Database Client and Server software. It can be used interactively or driven from scripts. It depends on which school of thought you are coming. Some people find this better than any of the tools mentioned above. For PL/SQL development you can use some editor and run the code on the SQL prompt. But it is always nice to use some PL/SQL tool for development.
5.4 PL/SQL Developer
The AllroundAutomation’s PL/SQL Developer is an Integrated Development Environment that is specifically targeted at the development of stored program units for Oracle Databases. Over time we have seen more and more business logic and application logic move into the Oracle Server, so that PL/SQL programming has become a significant part of the total development process. PL/SQL Developer focuses on ease of use, code quality and productivity, key advantages during Oracle application development.
5.5 SQL Navigator®
Quest Software Solution’s SQL Navigator® is a PL/SQL development solution that streamlines workflow by adding a drag-and-drop, graphical user interface to the PL/SQL development environment. SQL Navigator speeds the development of Oracle-based applications and combines coding, tuning, debugging, Web development and version control to deliver higher quality applications and save valuable time.
6 Bibliography
Billington, A. (n.d.). oracle-developer.net. Retrieved April 2012, from oracle-developer.net:
http://www.oracle-developer.net/display.php?id=430
CodeXpert. (n.d.). Quest Software.
Feuerstein, S. (2007). ORACLE PL/SQL Best Practices. O'Reilly Media.
williamRobertson. (n.d.). Retrieved from
http://www.williamrobertson.net/documents/plsqlcodingstandards.html