 |
|
Oracle Tips by Burleson |
The
SELECT INTO Clause
The SELECT INTOclause 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 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.
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.
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 INTOclause
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 INTOcan only
return one row of data, it 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.
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 INTOclause 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 INTOraised a TOO_MANY_ROWS
exception.
Another issue with using SELECT INTOstatement is that it 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
INTOraises a NO_DATA_FOUND 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 INTOquery 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 INTOquery
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_ROWSand NO_DATA_FOUND
exception, which should cover most of the
exceptions the function will encounter.
However, if an unexpected exception is raised, the
function code raises an application error on line 20. As shown below,
the function will provide an appropriate return value when used.
SQL> select
auth_Name('MO') from dual;
AUTH_NAME('MO')
--------------------------------------------
Too Many Authors in that State
SQL> select
auth_Name('CO') from dual;
AUTH_NAME('CO')
--------------------------------------------
No Authors in that State
SQL> select
auth_Name('CA') from dual;
AUTH_NAME('CA')
--------------------------------------------
jeckle
SQL> select
auth_Name('KY') from dual;
AUTH_NAME('KY')
--------------------------------------------
mee
SQL> select
auth_Name('12345') from dual;
AUTH_NAME('12345')
--------------------------------------------
No Authors in that State
 |
For more details and scripts, see my new book "
Oracle
Tuning: The Definitive Reference", over 900 pages
of BC's favorite tuning tips & scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot. |
|
