 |
|
Oracle Tips by Burleson |
The
SELECT INTO Clause
As the last example showed, even if an invalid
state value is passed to the function, the internal query will raise
an exception and the function will handle it and return an appropriate
value indicating no authors were found.
There are a number of SQL attributes that are also
available for use with the SELECT INTOquery;
however the attributes are not as useful when used in PL/SQL code.
Below is a list of the attributes.
SQL%NOTFOUND - True if no rows
returned.
SQL%FOUND
- True if one or more rows
returned
SQL%ISOPEN
- True if the SQL cursor is open.
SQL%ROWCOUNT - Number of rows
returned.
Why are these attributes less useful in PL/SQL?
Because of PL/SQL’s ability to raise and handle exceptions, you
already know an attribute’s value before you test the attribute. The
code fragment below creates a function that uses the sql%found
attribute.
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 if SQL%FOUND then return v_authName;
14 end if;
15 end;
16 /
Function
created.
The function compiles and runs returning the
results below. The function uses the sql%found attribute to test that
the query returned a values before the function returns. The example
below uses the function.
SQL> select
auth_Name('CA') from dual;
AUTH_NAME('CA')
--------------------------------------
jeckle
What is the issue with using the sql%found
attribute on line 13? Remember, the SELECT INTOthrows an exception if no data is returned. Therefore, the attribute
check at line 13 is unnecessary because if sql%found was false,
the query would have already raise an exception and would not executed
line 13. Here is an annotated example of the previous function that
implements 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 if SQL%FOUND then return v_authName;
14 end if;
15 exception
16 when TOO_MANY_ROWS
17 then
18 return 'Authors Found: '||SQL%ROWCOUNT;
19 when NO_DATA_FOUND
20 then
21 if SQL%NOTFOUND then return 'No Authors in
that State';
22 end if;
23 when others
24 then raise_application_error(
25 -20011,'Unknown Exception in authName
Function');
26 end;
27 /
Function
created.
We already know line 13 is redundant, but so is
line 21. The only way line 21 is executed is if the SELECT INTOquery
returned no rows and thus sql%notfound would equal true. But
you already know that sql%notfound is true because the statement is
contained in the NO_DATA_FOUND exceptionhandler.
We do not need the condition test in lines 13 or 21 because we already
know the results. Notice that the exception handle catches the
TOO_MANY_ROWSexception and returns the number of rows found in line
18. This appears to be logical, but the example below shows that the
handle does not return the number of rows.
SQL> select
auth_Name('MO') from dual;
AUTH_NAME('MO')
---------------------------------------
Authors Found: 1
Since we know from a previous example that there
are three authors from Missouri, why did sql%rowcount return
only one? When the query returned the second row, the TOO_MANY_ROWs
exception was raised.
As a result of the raising the exception,
sql%rowcount will operate as you would expect. The attribute
sql%rowcount will return zero (0) if the query returned no rows,
(but the NO_DATA_FOUND exception is raised)
and a one (1), if one or more rows were returned. As the example
shows, using the sql%rowcount attribute with a SELECT INTOquery
does not provide any new information about the query’s execution.
The attribute sql%isopen is used to check
that the implicit cursor used by the database has been closed. Since
the database opens and closes the cursor used by the query, any test
of the attribute will result in a false.
So to recap, if sql%found is true, the
query did note raise an exception, so why check it. If the attribute
sql%notfound is true, the NO_DATA_FOUND exceptionhas already been raised so the only place to check the attribute
value is in the exception handler, which your code would not be
executing if data was found.
The sql%isopen attribute will always be
false, because the database opens and closes the implicit cursor used
to retrieve the data. And finally, the attribute sql%rowcount
equals 0 if no rows were found (but the exception is raised) and a 1,
if one or more rows are found (if more than one an exception is
raised).
The one place you can use some of these attributes
is inside an exception handler, as shown in the example below.
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 others
16 then
17 if SQL%NOTFOUND then return 'No Authors
in that State';
18 elsif SQL%FOUND then return 'Too Many
Authors in that State';
19 else raise_application_error(
20 -20011,'Unknown Exception in authName
Function');
21 end if;
22 end;
23 /
Function
created.
SQL> select
auth_Name('CA') from dual;
AUTH_NAME('CA')
---------------------------
jeckle
SQL> select
auth_Name('MO') from dual;
AUTH_NAME('MO')
---------------------------
Too Many Authors in that State
SQL> select
auth_Name('FL') from dual;
AUTH_NAME('FL')
---------------------------
No Authors in that State
In the example above, all the exceptions are
caught by the OTHERS clause
and handled internally using the
IF/THEN/ELSE statement. The code produces the same results as the
original example.
So, which coding method is better? The best
choice is the one that is clearest to the programmers that have to
create and maintain the code. Many times an exception handler will
handle some exceptions and use the OTHERS clauseto
raise exceptions based on the SQL attributes and the SQLERR codes.
But what do we do if we know that our query will
return zero or more rows and we want to process each of the rows
returned? In this case you must use a cursor to retrieve and process
however many rows the query returns.
 |
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. |
|
