O'Reilly logo

Learning Oracle PL/SQL by Steven Feuerstein, Bill Pribyl

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required