Infosys

Infosys

PL\SQL Best Practices

Nov, 2011

INFOSYS LIMITED

Chandigarh

© 2010 Infosys Technologies Limited, India

Infosys

COPYRIGHT NOTICE

© 2010 Infosys Technologies Limited, Bangalore, India. All rights reserved. Infosys believes
the information in this document is accurate as of its publication date; such information is
subject to change without notice. Infosys acknowledges the proprietary rights of other
companies to the trademarks, product names and such other intellectual property rights
mentioned in this document. Except as expressly permitted, neither this document nor any
part of it may be reproduced, stored in a retrieval system, or transmitted in any form or by
any means, electronic, mechanical, printing, photocopying, recording or otherwise, without
the prior permission of Infosys Technologies Limited and/or any named intellectual property
rights holders under this document.

Infosys Technologies Limited

Hosur Road
Electronic City, 3rd Cross
Bangalore 560 100
India.
Telephone: (91) (80)28520 261-270
Fax: (91) (80) 8520 362
Website: http://www.infosys.com

© 2010 Infosys Technologies Limited, India

Infosys

Author: Jasmeet Singh Uppal

Date written: 24/11/2011

Declaration

I hereby declare that this document is based on my personal experiences. To the

best of my knowledge, this document does not contain any material that
infringes the copyrights of any other individual or organization including the
customers of Infosys.

Jasmeet Singh Uppal

Project Details

 H/W Platform: Windows

 S/W Environment: Oracle 11g, SQL Plus.

Target readers: This document is helpful to all who are using SQL in their project.

Keywords: PL\SQL, Best Practices.

Introduction:

This document is intended to explain PL\SQL Best practices.

PL/SQL Best Practices

There are several guidelines for SQL best practices, and some that apply directly to Oracle
SQL. In general, Oracle SQL best practices include the following techniques:

 Declare only the variable which are actually required by the code

If a variable is not necessary in the code then it is not required to be defined and
executed. Many a times it is possible that due to requirement or design changes
there might be several unused variables left in the code, which are unnecessarily
compiled, initialized and executed. This only adds to the cost to performance.
Therefore, after the PL\SQL code has been compiled, search for the un-used
variables in the code if any and remove them.

 Using Built-in Functions rather than to create customs functions

There are several tasks which can be performed by using built-in functions rather
than creating custom functions. Built-in functions are more efficient and are highly
optimized. Hence wherever possible use of built-in functions is recommended rather
that handcrafting the same.

SUBSTR, LPAD, RPAD, LTRIM are few examples of the built-in functions.

© 2010 Infosys Technologies Limited, India

Infosys

 Inefficient conditional control statements

PL/SQL engine when processing IF statement, executes each condition till it returns
true. Once the exact match is found or the condition has become true, it doesn’t
executes the remaining part of the code under IF statement. Therefore, most
probable condition must be placed initially in the IF statement. This will bring the
PL\SQL engine’s processing time and thus improves the overall performance of the
code.

Examples:

1. Consider the following IF statement using AND expression:

IF prize(student_id) AND (marks > 85) THEN

--sequence_of_statements

END IF;

In this above example the function prize is always called with the student_id as the input
whatsoever be the marks obtained by the student.

2. Now consider the following example:

IF (marks > 85) AND prize(student_id) THEN

--sequence_of_statements

END IF;

In this second example the function prize is called only if the marks obtained by the
student are greater than 85. Hence as the function is called only if the initial condition is
true, therefore the overall performance in this case will be improved.

 Unnecessary NOT NULL Constraints

Using NOT NULL Constraints unnecessarily reduces the performance of the code. Every
time a NOT NULL constraint is encountered in a PL\SQL code, the execution engine has
to check whether or not it is having a NULL value. Hench if there are say 100 NOT
NULL constraints in a PL\SQL code, it has to be applied 100 times, which degrades the
performance. Instead it is better to enforce it programmatically where ever required.

Examples:

1. Consider the following PL\SQL code:

DECLARE

© 2010 Infosys Technologies Limited, India

Infosys

v_var1 NUMBER NOT NULL := 9;

v_var2 NUMBER := 7;

v_var3NUMBE := 11;

BEGIN

--sequence_of_statements

v_var3:=v_var1+v_var2;

v_var2:=v_var3+v_var1;

--sequence_of_statements

END;

In the above PL\SQL block variable v_var1 has a NOT NULL constraint. Every time
this variable is encountered in the code, the system has to check whether it is having a
NULL value or not. This every time checking degrades the performance.

2. Now consider the following PL\SQL code:

DECLARE

v_var1 NUMBER; --no constraint added here

v_var2 NUMBER := 7;

v_var3NUMBE:=11;

BEGIN

--sequence_of_statements

v_var1 := v_var3 - v_var2;

IF v_var1 IS NULL THEN --here the constraint is enforced programmatically

--sequence_of_statements

END;

In the above example as no constraint is added while declaring a variable v_var1, it

will not be checked for NOT NULL constraint every time it is being referred in the
PL\SQL block. Instead here we have enforced the NOT NULL constraint
programmatically when required. Hence performance is improved.

© 2010 Infosys Technologies Limited, India

Infosys

 Declarations of Numeric Data Types

When it is required to declare an integer variable in the PL\SQL code, use the data type
PLS_NUMBER rather than using NUMBER data type as PLS_NUMBER is the most
efficient numeric data type in PL\SQL.

PLS_INTEGER is a PL/SQL data type used for storing signed integers. Variables
declared as PLS_INTEGER can be assigned values between -2**31 and 2**31
(-2,147,483,647 to 2,147,483,647).

Example:

Use the following code:

DECLARE

v_id PLS_INTEGER := 55;

BEGIN

v_id := v_id + 2;

--sequence_of_statements

END;

Instead of the following code:

DECLARE

v_id NUMBER := 55;

BEGIN

v_id := v_id + 2;

--sequence_of_statements

END;

© 2010 Infosys Technologies Limited, India

Infosys

 Check the LOOP statements

1. Minimize the number of iterations in the LOOP. EXIT the LOOP as soon as it has
completed the required job.

LOOP

--sequence_of_statements

v_variable := 2 * v_flag

EXIT WHEN v_variable > 10; --exit the LOOP when some condition returns true

END LOOP;

2. LOOP within a LOOP

There is a possibility of unnecessary code execution when a LOOP within a LOOP is

used.

Consider the following PL\SQL block:

FOR var1 in 1..20 LOOP

FOR var2 in 1..50 LOOP

var3 := var1 + 30; --Statement 1

END LOOP;

END LOOP;

In the above example the Statement 1 is executed several times unnecessarily. As this
statement doesn’t refer or depends on the loop variable of the second LOOP, it can be
executed outside the second LOOP. Hence the above code can be re-written as:

FOR var1 in 1..20 LOOP

var3 := var1 + 30;

FOR var2 in 1..50 LOOP

--sequence_of_statements

END LOOP;

END LOOP;

© 2010 Infosys Technologies Limited, India

Infosys

References:

1. Training material provided by E&R: RDBMS Essentials.

2. http://www.plsql-tutorial.com/

© 2010 Infosys Technologies Limited, India