February 27, 2021
Hot Topics:

Sample Chapter: Efficient PL/SQL

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

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

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