Learning Oracle PL/SQL

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.

Table 5-1. Possible results of the SELECT INTO statement

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