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 ... |
Get Learning Oracle PL/SQL now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.