Introduction (The Big Picture)

This is a book about writing stored procedures for an Oracle database. A stored procedure in this context is a generic term for a program written in the Procedure Language extension for SQL (PL/SQL) that is stored inside the database when it is compiled. This means that you can then execute the program from inside the database. Why would you want to do this? Because your program will run faster inside the database.

Let’s slow down for a minute, so I can explain what a stored procedure is and why you would want to use one.

What’s a Stored Procedure?

In order for me to talk about stored procedures, I need to cover a little material on databases and networks first. By now, you’ve probably already seen three of the four diagrams I’m about to show you a few hundred times, but bear with me so I can make sure everyone is on the same page as we start out.

I’m going to assume, since you’re ready to start writing programs against an Oracle database, that you already know what a relational database management system (RDBMS) is. For our purposes, an RDBMS, or database as I’ll refer to it from here forward, is a hardware/software machine (server) that allows us to store, retrieve, and manipulate data in a predictable and organized manner using Structured Query Language (SQL).

SQL acts as the interface to the database. A client program, whether it exists on the same computer or on another, makes a connection to the database, sends a request in the form of SQL to the server, and in return gets back structured data, as in Figure I-1.

A client program that utilizes Oracle on the same host computer, as in Figure I-1, is a client-server program because it accesses a server program to perform some of the client program’s work. The communication between the two processes, client and server, takes place through an interprocess communication (IPC) system provided by the host operating system. This is typically done through memory. Suffice it to say that some communication overhead takes place when communicating between the client and the server in this fashion. This overhead takes time and operating system resources. Regardless, it’s pretty fast.

But not everyone can run the program on the same computer, so some applications resort to the use of client-server architecture over a network. This is what is referred to when most people use the term client-server. Figure I-2 is a diagram that shows the communication between the client and server in a networked client-server architecture, specifically, a client-server diagram with one network connection in between the client (program) and server (database).

The communication between the two processes, client and server, in Figure I-2, is much slower than the architecture shown in Figure I-1, where IPC is used. Even with the best network hardware available, this connection is usually 20 times slower than IPC, or worse. Plus, a second software protocol stack, to allow the network communication, must be added to the use of resources. What’s the net result? The connection between the two processes becomes the slowest, most time-consuming part of any application.

Nowadays, not every application can be run on a computer attached to a high-speed network. Nor can any one machine handle all the end-user requests, so architecture has effectively stretched the bus of the good old mainframe on to the network, and created what is commonly called n-tier architecture. In n-tier architecture, client programs communicate with an application server, which in turn communicates with one or more servers. You might call this Client-Server Gone Wild, but don’t hold your breath waiting for the video. Figure I-3 is a diagram that shows an example of n-tier architecture where a notebook computer, a cell phone, and a tablet all communicate with an application server in order to use the same application through different devices.

Now there are three different kinds of clients, with three different kinds of networks, using networked client-server architecture to communicate with an application server, which in turn uses networked client-server architecture to communicate with a database. Is this complex? Yes, but it’s still just networked client-server architecture. Of course, this means that all the various networks involved conspire to slow down the response time of the application!

With the network being a bottleneck for any networked application, if we can perform some of our application’s computer processing without using the network at all, that portion will simply run faster. With that in mind, examine Figure I-4. It’s a diagram that shows how a stored procedure exists inside the database. Therefore, any computer processing that take place will occur inside the database before data is even sent to a client program, regardless of what type of client-server communication is used. In turn, that portion of the application will simply be more efficient and run faster.

So what’s a stored procedure? In Figure I-4, it’s the box labeled “Program” that exists inside the database. So a stored procedure is a program that resides inside an Oracle database that manipulates data in the database before the data is used outside the database.

Why Use Stored Procedures?

Why use stored procedures? Well, I’ve already touched on that, haven’t I? Here are my favorite reasons to use stored procedures:

• They eliminate the net in work.
• They allow you to more accurately model the real world in your database.
• They provide you with access to functionality that is not available through the standard database interface: SQL.

First, as I already stated in the previous section, using stored procedures allows you to eliminate the network from your data processing work. I can’t emphasize this fact enough. If you write a Java Database Connectivity (JDBC) program that retrieves one million rows from the database, then queries the database for three related pieces of information, and then conditionally updates the retrieved rows, it can take days in Java; it will take only minutes inside Oracle using PL/SQL. That’s a huge difference in performance. I often like to say, “The difference between any fast and slow application is that the fast application uses stored procedures.”

Second, we basically use computers to automate what we do in the real world. Most people use databases to store only the characteristics of the real world, completely ignoring the behavior. Instead, behavior is temporarily kept in the code base for an application. If a change in the code is required, past behavior is lost forever. Yet no one would think of throwing away the data! What’s going on here? Is the history of behavior unimportant or are we ignorant of the problem? I argue it’s the latter. If you want to save both real-world characteristics and behavior, stored procedures allow you to do just that in either a pseudo-object-oriented or a truly object-oriented manner.

Finally, the standard database interface, SQL, is great, but its use is limited to four operations: insert, update, delete, and select. With stored procedures, you have unlimited possibilities. You are free to create as many new operations as are needed to satisfy requirements. The important point here is to perform work where it is done most efficiently. Presentation code should reside in the presentation layer, application code should reside in the application layer (the application server), and persistence code, like entity behavior, should reside in the persistence layer (the database).

Using PL/SQL, you can write stored procedures for the following:

• Data processing
• Data migration
• Data warehousing
• Entity behavior, including so-called business rules
• Interfaces
• Reports
• Service-oriented-architecture routines

Now that you know the what and the why of stored procedures, are you still interested in learning PL/SQL? If so, then please read the next section, where I will tell you what this book is about, how I’m going to teach you PL/SQL, and why.

What’s This Book About?

This book is not a reference. It’s an immersion-based tutorial that teaches you how to program in PL/SQL by making you mimic successful PL/SQL programming.

Do you know that you have free access to outstanding reference materials directly from Oracle? If you download and install the database on your computer, these reference manuals will be accessible from the installation. Or you can download just the manuals to your computer. Oracle has always had superior technical documentation. I believe that has played a huge part in Oracle’s success as a company and a product. Free access to Oracle’s documentation and software for trial purposes has removed any barriers from anyone learning how to use Oracle.

How I’m Going to Teach You and Why

What do I mean by “an immersion-based tutorial?” I’m going to require you to read each and every program listing as part of the learning process. I’m going to start showing you program listings right away. Most listings will be complete contexts. By that, I mean there will be a lot of code in them that I have not yet discussed. I do this because I want you to get used to looking at the code and where things belong in the code from the get-go. Concentrate on the subject of the section and ignore the rest.

Whenever possible, I’m going to ask you to do an exercise that will make you think about and apply what I’ve just covered in the section. You should almost always be able to mimic (or copy and change) code that I’ve shown you before the exercise. I’m going to ask you to program all along. And the next section will often be dependent on you completing a prior exercise, so there’s no skipping an exercise.

Why am I making you look at code, then read code, then try to understand my code, and then ask you to prove you understand my code by having you write code? For several reasons:

• You didn’t learn how to speak by having those around you discuss the merits and drawbacks of various languages you might want to speak. You learned by mimicking those who spoke.
• You didn’t learn how to read by talking about reading. You learned by reading.
• You don’t learn a new language by reading about it. You need to speak it and then write it.
• You don’t learn a new concept by reading about it. You must do your homework, where you actually use it.

So, how can you read a reference and be ready to solve business problems using a programming language? You can’t. In the beginning, you must learn by mimicking someone else’s work (hopefully, good examples)—something that someone has done before you. Then later, using your own creativity, you expand upon your basic knowledge with a reference.

But there’s more to learning PL/SQL than just coding. I’m going to teach you good programming habits, such as these:

• Document as you go.
• Leave bread crumbs.
• Keep it simple for the next person’s sake.
• Make it obvious by using prefixes or suffixes when it helps.
• Make it obvious by using a coding style and sticking to it; consistency is important.
• Make it obvious by adding comments to your code when it’s not obvious.
• Prepare for disaster ahead of time.
• Prepare for testing ahead of time.

And finally, here are my assumptions:

• You’ve programmed in another programming language, so you already know the difference between a function and a procedure.
• You already know SQL.

I’m going to get you started down the right path, not tell you everything about PL/SQL. Your time is precious, so I’m not going to tell you anything that you don’t need to know to get started (except for an occasional joke, or perhaps, my poor attempt at one). You may find my style terse. To me, that’s a compliment.

This is not Oracle PL/SQL for Dummies. Remember that the examples are shown in a full-blown context. Pay attention to the subject; ignore the rest. You can do it!

What PL/SQL Am I Going to Teach You?

What am I going to teach you? Succinctly, how to get you, a programmer, from a novice to professional PL/SQL programmer in ten chapters.” I’ve spent 25 years hiring programmers and then teaching them how to code properly, the past 15 years focusing on how to code in PL/SQL. In this book, I will apply the 80/20 rule in order to teach you the fundamentals of PL/SQL programming without bogging you down in the ever-so-increasing details.

As a scientist, I gather data about what I do all the time, so I have statistics about all the stored procedures that I and others have written since Oracle 7 became a production product. From these statistics, I’ve learned what’s used often and not at all. In this book, I intend to touch on anything that is used 25% or more of the time when writing stored procedures.

Here, I’d like to share some interesting fun facts derived from over 30,000 stored procedures.

PL/SQL Pie, Anyone?

Figure I-5 shows that on average, only 31% of a stored procedure is actually logic. The rest is declaration, built-ins, and SQL. SQL makes up a whopping 26%. So if you don’t know SQL, you’re already behind the eight ball when you start to learn how to write stored procedures using PL/SQL.

SQL, SQL, SQL!

The pretty little histogram in Figure I-6 shows the SQL keywords that are used in 25% or more of all stored procedures. So I need to start out with a SQL refresher for those of you who say you know SQL, but don’t actually have much SQL experience. After all, I want you to buy this book and then go back and buy Beginning Oracle SQL, right?

What About Those Declarations?

Figure I-7 shows the PL/SQL declaration keywords that are used in 25% or more of all stored procedures. Why teach any more than these to a beginner?

And How About Those Built-ins?

Figure I-8 shows the PL/SQL built-ins (functions and procedures that are part of the language) that are used in 25% or more of all stored procedures. Why teach any more than these to a beginner?

What Exactly Am I Supposed to Be Doing?

And last, but certainly not least, the histogram in Figure I-9 shows the PL/SQL keywords that are used in 25% or more of all stored procedures. The list isn’t that large, so introducing these as we go will ease you right into coding logic.

In What Order Am I Going to Teach You PL/SQL?

First, I’m going to help you review your knowledge of SQL. If you don’t understand something in Chapter 1, go buy the Apress book on SQL, as I mentioned earlier, then continue.

Next, I’m going to teach you the basic structure of every PL/SQL program unit type. I’ll show you each program unit type. Concentrate on understanding PL/SQL block structure, and you’ll be OK. Try to understand all the stuff I show you and don’t discuss, and you’ll be totally lost. Remember to focus on the subject being discussed. If you were mining for gold, you wouldn’t stop and examine every grain of dirt, rock, and sand you encountered along the way; you would concentrate on the gold. So concentrate on the gold!

Assuming you understand block structure, the next chapter discusses the use of memory. Basically, it covers data types and variable declarations. You’re a programmer, so there should be nothing really mind-boggling and new for you in this chapter, except maybe NULL.

The next topic is the singleton SQL statement. Whether it’s a select, an insert, an update, or a delete, a singleton should always return one result. So what happens when it doesn’t? Read Chapter 4 to find out!

From singletons to multitons? Something isn’t right here. Anyway, Chapter 5 will cover the use of cursors. Cursors allow you to retrieve multiple rows from a database, one row at a time. At this point, you’ll have coded a couple stored procedures.

In Chapter 6, you’re going to learn to model an entity just as it exists in the real world. You’ll be introduced to object-relational technology. And then you can decide which way to go: relational or object-relational.

Chapter 7 is about troubleshooting. It’s always nice to know how to find an insect in your program. I’ll also show you how to prevent insects in the first place. Oh yeah, did I tell you that I like to call defects defects, not bugs?

Professional programmers create programs with few defects. How do they do that? By testing. Chapter 8 is about automating the testing of your PL/SQL program units. Low error rates mean higher paychecks, too. So don’t skip Chapter 8.

Concerning object-oriented development, it allows you to build reusable components, but reusable components are only valuable if you provide others with documentation, so they know the components exist and how they can use them! Professional programmers document as they go! That’s what Chapter 9 is all about.

And finally, I’ll tell you how use polymorphism to create reusable packages. I’ll show you the polymorphic patterns I used for years to more quickly solve previously encountered business problems.

OK, I was lying about that “finally.” The book’s appendix explains how to acquire and install Oracle RDBMS, and how to use SQL*Plus for PL/SQL programming, in case you really don’t have any experience with SQL before you start with PL/SQL (I knew it).

If you have access to an Oracle database where you can create objects like tables, indexes, types, and stored procedures, then you’re ready to dive right in to Chapter 1. Otherwise, you may want to flip to the appendix, which tells you how to download and install a trial version of Oracle.

Good skill!