| what is the resulting value when no row is selected [message #2077] |
Wed, 19 June 2002 23:28  |
keane
Messages: 23
Registered: June 2002
|
Junior Member |
|
|
CREATE OR REPLACE PROCEDURE selectrec
AS
v_result version_num.num%type;
BEGIN
select num
into v_result
from version_num
where num = 2;
if v_result is NULL then
update version_num set num = 5;
end if;
END
------------------------------------------
the error is no data found, when there is no row selected. How to modify the if condition, so that the update will work when there is no row selected?
Thanks a lot.
keane
|
|
|
|
| Re: what is the resulting value when no row is selected [message #2078 is a reply to message #2077] |
Thu, 20 June 2002 00:27  |
John R
Messages: 156
Registered: March 2000
|
Senior Member |
|
|
Lots of ways of doing this.
You could replace the select with one that will always return a value:
CREATE OR REPLACE PROCEDURE selectrec AS
v_result version_num.num%type;
BEGIN
select count(num)
into v_result
from version_num
where num = 2;
if v_result =0 then
update version_num set num = 5;
end if;
END;
Or you could explicitly trap the exception:
CREATE OR REPLACE PROCEDURE selectrec AS
v_result version_num.num%type;
BEGIN
BEGIN
v_result := null;
select num
into v_result
from version_num
where num = 2;
EXCEPTION
when no_data_found then null;
when too_many_rows then null;
END;
if v_result is NULL then
update version_num set num = 5;
end if;
END;
Or, you could merge the two SQL statements together
CREATE OR REPLACE PROCEDURE selectrec AS
BEGIN
UPDATE version_num
SET num=5
WHERE NOT EXISTS (
select 'x'
from version_num
where num = 2);
END;
Hope this helps.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
