Learning Oracle PL/SQL

3.2. A First Programming Exercise

A fairly simple place to begin is by writing a PL/SQL program that will add a new book to the database. Of course, I could just write a SQL INSERT statement (or two) whenever I need to perform this function:

INSERT INTO books (isbn, title, author)
VALUES ('0-596-00180-0', 'Learning Oracle PL/SQL, 'Bill Pribyl with Steven Feuerstein');

Why would I ever bother writing a PL/SQL program?

3.2.1. Rationale for the Design

Say I have two different places where I need to add books to the catalog: one needs to be interactive, enabling hand-entry of the input data, and one automatic, retrieving the book's properties from a remote database. So now what do I do? Duplicate the INSERT statements in these programs? And maybe I later write a third program that adds book records by reading them off a CD-ROM. Just cut and paste another copy of the INSERTs, right? Now pretend the design of those tables changes, and I have to change all of my programs. Oops.

There are several good reasons to put the INSERTs into a PL/SQL program, but the most important benefits are:

• To reduce, if not eliminate, a lot of tedious, error-prone software maintenance work when the database structure changes

• To help optimize database server performance

• To centralize complexity

Writing correct SQL statements may require interpreting and coding a lot of complicated business rules; having to re-code all this logic in every application is a needlessly risky waste of time and effort. The ...