Skip to Main Content
Learning Oracle PL/SQL
book

Learning Oracle PL/SQL

by Bill Pribyl, Steven Feuerstein
November 2001
Beginner content levelBeginner
424 pages
11h 11m
English
O'Reilly Media, Inc.
Content preview from 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 ...
Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Learning Oracle PL/SQL

Learning Oracle PL/SQL

Darryl Hurley
Oracle PL/SQL Programming, Third Edition

Oracle PL/SQL Programming, Third Edition

Steven Feuerstein, Bill Pribyl

Publisher Resources

ISBN: 0596001800Supplemental ContentCatalog PageErrata