Sample Chapter: Efficient PL/SQL

CHAPTER 1: Efficient PL/SQL

In this chapter, we discuss PL/SQL efficiency. We have deliberately avoided the term "performance" because efficiency is more than just performance. We'll define exactly what we mean by "efficient PL/SQL" and explain how to ensure that the PL/SQL you build meets that definition.

When used intelligently, PL/SQL can build applications that perform well, are resilient to change over time, and scale to large user populations. We contend that PL/SQL should be your language of choice for database-centric application development. Therefore, when you're deploying Oracle within your organization, you should encourage the use of PL/SQL as an integral component of the applications that you develop.

Why Use PL/SQL?

Before covering the important issues surrounding PL/SQL efficiency, let's address the question that we've often been asked by developers over the years: "Do I need to use PL/SQL at all?"

Ever since organizations began their obsession with getting their applications off the mainframe in the late 1980's—moving first to client-server applications and then to Web-based solutions—the application data (stored in an Oracle database) has been getting "further away" from the application code itself.

As a consequence, when stored procedures first appeared in the Oracle server back in version 7, they were generally marketed as a solution for improving client/server applications over slow networks. Rather than making lots of discrete database calls from a client application, we could bundle those calls inside a PL/SQL stored procedure that resided on the server, issue a single call from the client application, and thus reduce our dependency on the speed of the network.

This is a terrible underselling of the value of PL/SQL (and let's face it, underselling a feature is something we generally don't expect from Oracle). Worse, it led to the idea that if you didn't intend to run an application across a wide area network, you didn't need PL/SQL. But as this book will demonstrate time and time again, not using PL/SQL in an Oracle project is equivalent to coding with one hand tied behind your back. Nevertheless, the first battle you're likely to face on a project will not be ensuring that you use PL/SQL efficiently, but convincing management to use it at all!.

Often, the arguments against using PL/SQL during application development include:

• It locks you into using Oracle.
• "It doesn't do anything I could not do in <insert your favorite 3GL here>."

Launching into a tirade about the foolhardiness of building applications that purport to be truly database independent is outside the scope of the book, but avoiding PL/SQL in an Oracle application could well be a death knell for that project. And although it is true that the functionality of PL/SQL is reproducible in most 3GLs, the second argument demonstrates an ignorance of the two key benefits of using PL/SQL.

PL/SQL is Close to the Data

Very, very close, in fact. PL/SQL is inextricably part of the database. As you will see in Chapter 4, "Effective Data Handling," we can forge strong links between variable and data structures used in PL/SQL programs and the equivalent structures in the database.

Also, as will be demonstrated in Chapter 2, "Package It All Up," PL/SQL is aware of structural changes in the database and can provide insulation from those changes. You can track the dependencies between PL/SQL programs and other database objects but not need to perform any code maintenance when those database objects are changed.

Page 1 of 19



This article was originally published on February 5, 2004