The SELECT INTO Clause
The SELECT INTO clause of SQL is used to retrieve one row or set of
columns from the Oracle database. The SELECT INTO is actually a
standard SQL query where the SELECT INTO clause is used to place the
returned data into predefined variables.
SQL> declare
2 v_authName author.author_last_name%type;
3 begin
4 select
5 author_last_name into v_authName
6 from
7 author
8 where
9 author_key = 'A103';
10
11 dbms_output.put_line('Name: '||v_authName);
12 end;
13 /
Name: weaton
Here the author_key was used to retrieve one author’s last
name and place it in the variable called v_authName. The
query can also retrieve an entire row as a record with SELECT INTO.
In the example below a record based on the columns of the author
table is declared in line two below. Because v_author is
declared as an author table %rowtype , you can safely use the
SELECT * clause to retrieve all the columns.
SQL> declare
2 v_author author%rowtype;
3 begin
4 select
5 * into v_author
6 from
7 author
8 where
9 author_key = 'A103';
10
11 dbms_output.put_line('Name:
'||v_author.author_first_name||' '||
v_author.author_last_name);
12 end;
13 /
Name: erin weaton
If the DBA adds a column to the author table, the query above
will still execute. The record variable v_author contains a
record that includes all the columns in the author table. If
the value of a column in the table is NULL, it will also be NULL in
the record. The individual columns are accessed using the dot “.”
notation with SELECT INTO.
You can see this in line 11 of the listing. Although it is
important to define variables using the database datatype definition
of the retrieved data, sometime this is not possible.
This is shown in the example below.
SQL> declare
2 v_totalName varchar2(80);
3 begin
4 select
5 initcap(author_last_name||',
'||author_first_name)
into v_totalName
6 from
7 author
8 where
9 author_key = 'A105';
10 dbms_output.put_line('Name: '||
v_totalName);
11 end;
12 /
Name: Withers, Lester
The query above is returning a string value created from multiple
table columns. The variable v_totalname must be defined as a
datatype that can hold the composite string returned by the query.
If the SELECT INTO clause attempts to load a value into a variable
that is the wrong datatype, or not large enough to contain the
value, an exception is raised.
Although a SELECT INTO can only return one row of data, SELECT INTO
can return multiple columns into multiple variables. In the example
below, two columns are selected from a table and loaded into two
predefined variables.
SQL> declare
2 v_lname author.author_last_name%type;
3 v_fname author.author_first_name%type;
4 begin
5 select
6 author_first_name, author_last_name
7 into v_fname, v_lname
8 from
9 author
10 where author_key = 'A108';
11
12 dbms_output.put_line('Name: '||v_fname||'
'||v_lname);
13 end;
14 /
Name: minnie mee
A single row of column values is loaded into the list of variables
when multiple columns are selected. The order of the columns and
the order of the variables must match when using the SELECT INTO
clause.
In each example so far, the restriction defined in the query’s WHERE
clause has been based on a primary key. Using a unique or primary
key is important as the SELECT INTO clause can not retrieve more
that one row of data. If the query returns more that one row an
exception is thrown.
SQL> declare
2 v_authName author.author_last_name%type;
3 begin
4 select
5 author_last_name into v_authName
6 from
7 author
8 where
9 author_state = 'MO';
10 dbms_output.put_line('Name: '||v_authName);
11 end;
12 /
declare
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 4
The above example retrieved the authors from the author table
with an author_state of ‘Missouri’. There are three authors
in the author table from Missouri and the SELECT INTO raised a
TOO_MANY_ROWS exception.
Another issue with using SELECT INTO statement is that SELECT INTO
throws an exception is it fails to return at least one rows.
SQL> declare
2 v_authName author.author_last_name%type;
3 begin
4 select
5 author_last_name into v_authName
6 from
7 author
8 where
9 author_state = 'FL';
10 dbms_output.put_line('Name: '||v_authName);
11 end;
12 /
declare
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 4
Here the query asks for authors from Florida. Since there are not
any authors in the table from Florida, the SELECT INTO raises a
NO_DATA_FOUND exception exception" . Your PL/SQL code should be
written in such a way that it is able to handle these exceptions.
Anytime the SELECT INTO raises an exception, the query will not load
a value into the defined variable.
When you try and access the variable, you will either get another
exception or worse, use an old or invalid variable value. So using
the SELECT INTO query can be problematic. However since a SELECT
INTO query can return one and only one row of data, it makes a
perfect PL/SQL function. Within the function, your code can catch
and handle the possible exceptions.
Below is an example of wrapping the SELECT INTO query in a
function with exception handling.
SQL> create or replace function auth_Name
2 ( v_auth_state IN author.author_state%type)
3 return varchar2
4 as
5 v_authName author.author_last_name%type;
6 begin
7 select
8 author_last_name into v_authName
9 from
10 author
11 where
12 author_state = v_auth_state;
13 return v_authName;
14 exception
15 when TOO_MANY_ROWS
16 then return 'Too Many Authors in that
State';
17 when NO_DATA_FOUND
18 then return 'No Authors in that State';
19 when others
20 then raise_application_error(
21 -20011,'Unknown Exception in authName
Function');
22 end;
23 /
Function created.
Notice when the function code catches an exception it must handle it
and return something appropriate or pass the exception back to the
calling block. In the above example, the code catches and handles
the TOO_MANY_ROWS and NO_DATA_FOUND exception , which should
cover most of the exceptions the function will encounter.
For more information, see the $9.95 book "Easy PL/SQL Programming"
by Col. John Garmany:
http://www.rampant-books.com/book_2005_1_easy_plsql.htm
