How to check if row exists before SELECT INTO statement in Oracle SQL

Question

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

Answer 1

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;