I'm using Oracle SQL and have a procedure that is doing some operations on tables. During the procedure there is a "SELECT x INTO y FROM TABLE z WHERE..." statement inside a loop. Unfortunatly during the procedure I can't guarante that there is always a row to the corresponding where condition because it changes dynamically. Is it possible to check if a row exists before the statement? I was thinking of sth like "if exists(select ...) then SELECT X INTO y..."
Thanks for the help! Jack
Well, there's no point in checking it first, and re-using the same statement again.
You could handle the exception (possibly in an inner BEGIN-EXCEPTION-END block):
declare
y number;
begin
begin --> inner block starts here
select x into y from z where ...
insert into ...
exception
-- handle it, somehow; I chose not to do anything
when no_data_found then
null;
end; --> inner block ends here
end;
Or, if you used cursor FOR loop, you wouldn't have to handle it because - if select returns x, insert would run. Otherwise, nothing in that loop would ever be executed:
begin
for cur_r in (select x from z where ...) loop
insert into ...
end loop;
end;
An exception handler as in Littlefoot's answer is the most correct and explicit approach, however just for completeness you might also consider using an aggregate.
Value 'X' exists in the table:
declare
p_someparam varchar2(1) := 'X';
l_somevalue varchar2(1);
l_check number;
begin
select max(dummy), count(*) into l_somevalue, l_check
from dual d
where d.dummy = p_someparam;
dbms_output.put_line('Result: '||l_somevalue);
dbms_output.put_line(l_check||' row(s) found');
end;
Result: X
1 row(s) found
Value 'Z' does not exist in the table:
declare
p_someparam varchar2(1) := 'Z';
l_somevalue varchar2(1);
l_check number;
begin
select max(dummy), count(*) into l_somevalue, l_check
from dual d
where d.dummy = p_someparam;
dbms_output.put_line('Result: '||l_somevalue);
dbms_output.put_line(l_check||' row(s) found');
end;
Result:
0 row(s) found
You can add logic to handle the cases where the count check is 0 or greater than 1.
If you are having procedure then I should say use if statement and then write the sql:
select some_column into some_variable from tablename where condition
IF somevariable not in (<list of values separated by comma>)THEN
{statements to execute }
END IF;
