Learning Oracle PL/SQL

5.1. What's the Big Deal?

In a nutshell, the fundamental challenge to retrieving data is that SQL is a set-oriented language, and procedural languages like PL/SQL—even one that is supposedly a "superset" (no pun intended) of SQL—are record-oriented. So it's a minor challenge to fetch data from the database, because the built-in structures and operators of these two languages don't quite live in the same dimensions. Consider a SQL SELECT statement to list all the books authored by Shakespeare:

SELECT title, date_published
  FROM books
  WHERE UPPER(author) LIKE 'SHAKESPEARE%';

(In this statement, note that % is SQL's wildcard character.) This statement could return dozens of rows—which you can think of as a mathematical "set." On the other hand, typical PL/SQL statements such as the following manipulate one item at a time (a "record"—well, sort of):

favorite_play_title := 'MEASURE FOR MEASURE';
publication_date := TO_DATE('01-FEB-1621', 'DD-MON-YYYY');

When you try to get the data out of the database and into a program, you confront the problem that these variables can hold only one thing at a time.^[1] Some authors describe this as an "impedance mismatch," which is not a bad analogy, as long as you understand enough about electrical circuits to make sense of that comparison.^[2] However you describe it, let's look at how to address the problem.

^[1] Advanced language features called collections and bulk binds make it possible to retrieve multiple rows in each fetch.

^[2] Impedance is ...