PL/SQL select into - if data exists

Question

I need to select into a local variable only if there exists data.

SELECT column1 INTO local_variable FROM table1 where column2 = <condition>;

Here if there is no data matching the condition I get a no data found error.

I need to select into the local variable only if there is some data matching the condition. Is there a simple query that will solve my problem.

Answer 1

Probably the best way is to handle no_data_found

begin
  SELECT column1 INTO local_variable 
  FROM table1 where column2 = p_val;
exception
  when no_data_found then
    local_variable := null;
end;

Also, if you are selecting with primary key /unique key (that is column2 is unique) then there is a trick you can do

SELECT max(column1) INTO local_variable 
  FROM table1 where column2 = p_val;

Answer 2

Well…do a count before doing the select. Or just handle the no_data_found exception.

You can open a cursor and fetch the rows, do a count and if it is greater than 0 then do your stuff with that record 😉