February 28, 2021
Hot Topics:

Sample Chapter: Efficient PL/SQL

  • By Beck, Kallman, Katz, Knox, and McDonald
  • Send Email »
  • More Articles »

We do not want to drift too far away from PL/SQL here, but it's hard to overstate the importance of this issue. If you were to act on every other tip we provide in this book about creating efficient PL/SQL except building your application to use bind variables, chances are your work will have been in vain and your application will not scale. We'll revisit the costs of parsing when we cover dynamic SQL in Chapter 5, "PL/SQL Optimization Techniques."

Use the PL/SQL Features Available

In this section, we would like to focus in on a particular aspect of the PL/SQL features, which is often expressed by the clichi "Don't reinvent the wheel." We preferto extend the cliché to be "Don't reinvent a wheel that is typically more complicated, slower, and sometimes just plain wrong."

The power of PL/SQL is of course not lost on Oracle, which delivers a lot of powerful functionality using PL/SQL, which you should know about and exploit within your own applications. We'll come back to this again in Chapter 2, "Package It All Up," when we look at some of the Oracle supplied packages, but it is useful to cover a few simple examples here, in order to illustrate the point.

Use the Provided Error Handling Facilities

Consider the following procedure, update_emp. It accepts as parameters an employee number and the factor by which we will be decreasing their salary. A simple SQL statement then effects the change.

SQL> create or replace
  2  procedure UPDATE_EMP(p_empno number,p_decrease number)is
  3  begin
  4    update EMP
  5    set SAL =SAL /p_decrease
  6    where empno =p_empno;
  7  end;
  8  /

Procedure created.

To decrease the salary of employee 7379 by a factor of 2, we would simply call the procedure as follows:

SQL> exec UPDATE_EMP(7369,2);

PL/SQL procedure successfully completed.

It doesn't take a university degree to see where this procedure could encounter some problems—we simply pass a value of 0 for the p_decrease para-meter.

SQL> exec UPDATE_EMP(7369,0);
ERROR at line 1:
ORA-01476:divisor is equal to zero
ORA-06512:at "UPDATE_EMP",line 3
ORA-06512:at line 1

But it is at this point that many developers take an incorrect approach to enhancing the code to make it more robust. The processing of errors within PL/SQL is based around the exception handler model, namely, capturing errors as they occur and either taking remedial action or propagating the error back to the calling environment. However, developers regularly avoid using the exception-handling facilities by attempting to anticipate every possible error, and thus ensuring that a PL/SQL program always succeeds. For example, we can erroneously extend the update_emp procedure to return a Boolean variable indicating whether the call was successful. To set this variable, we check for the validity of the p_decrease parameter before performing the update.

SQL> create or replace
  2 procedure UPDATE_EMP(p_empno number,p_decrease number,
  3 p_success out boolean)is
  4 begin
  5 if p_decrease =0 then
  6 p_success :=false;
  7 else
  8 update EMP
  9 set SAL =SAL /p_decrease
  10 where empno =p_empno;
  11 p_success :=true;
  12 end if;
  13 end;
  14 /

Procedure created.

It is impossible to anticipate every error that could occur in a PL/SQL program. To attempt to do so merely adds complexity to the code, and may even cause issues with data integrity—something that we will revisit when we cover transaction management within PL/SQL in Chapter 4, "Effective Data Handling."

Page 11 of 19

This article was originally published on February 5, 2004

Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

Thanks for your registration, follow us on our social networks to keep up-to-date