5.2. A Simple-Minded Approach to Retrieving One Row
The simplest introduction to solving the problem we've described is PL/SQL's SELECT...INTO statement. This is the closest thing to just dropping the SELECT statement directly into your PL/SQL program.
Attempting to use this approach on the query shown in the previous section would look like this:
DECLARE
favorite_play_title VARCHAR2(??);
publication_date DATE;
BEGIN
SELECT title, date_published
INTO favorite_play_title, publication_date
FROM books
WHERE UPPER(author) LIKE 'SHAKESPEARE%';
END;
/
As you can surmise, the general syntax for a SELECT INTO is:
DECLARE
local_var1 DATATYPE ;
local_var2 DATATYPE ;
BEGIN
SELECT column1 , column2 , ...
INTO
local_var1 , local_var2 , ...
FROM table_name
WHERE where_clause ;
When this block executes, Oracle reads the value of each column from the table and assigns it to the corresponding local variable listed in the INTO clause...as long as there is exactly one row that matches the where-clause. If not, Oracle raises an exception. Here is where "simple-minded" is not necessarily the same thing as "simple." There are actually three possible outcomes of a SELECT INTO statement, as shown in Table 5-1.
| Number of rows matching where-clause | Runtime behavior | Value of SQLCODE |
|---|---|---|
| 1 | Success; assigns column values to local variables | 0 (no error) |
| 0 | Raises NO_DATA_FOUND exception | 100 |
| More than 1 | Raises TOO_MANY_ROWS exception | -1422 ... |