Best practice in PL/SQL

1)      Some declarations may be costly process

Defer the declarations till it is required

Search of unwanted variables and remove them

2)      Prefer Built-in functions over the user defined.

3)      Writing efficient conditional statements

Evaluation of expressions takes less time than execution of functions as functions involve context switch

Ex : 

IF (load < 5000) AND func(id) THEN

…

END IF;

4)      Check the loop statement

Always check if loop contains only those statements which depend on the loop

Exit the loop as soon as the required job is done.

5)      Avoid implicit data type conversions

Ex :

DECLARE

                vNum NUMBER; vChar CHAR(5);

BEGIN

                vNum := vNum + 15; -- converted

                vNum :=vNum + 15.0; -- not converted

                vChar:=25; -- converted

                vChar:=’25’; -- not converted

END

6)      Choose optimized data type

Ex :

PLS_INTEGER requires less storage than INTEGER and NUMBER.

PLS_INTEGER uses machine arithmetic whereas INTEGER and NUMBER use library arithmetic which consumes more time.

7)      Avoid unnecessary NOT NULL constraints.

Ex :

PROCEDURE calc_m IS

                m NUMBER NOT NULL := 0;  a NUMBER; b NUMBER;

                BEGIN

                                m:=a+b;

                END

 

PROCEDURE calc_m IS

                m NUMBER;  a NUMBER; b NUMBER;

                BEGIN

                                m:=a+b;

                                IF m IS NULL THEN

                                END IF;

                END