CODEWITHSUNDEEP
plsql
Avinash
Avinash

Reputation: 879

PL/SQL select into - if data exists

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.

Upvotes: 9

Views: 13209

Answers (2)

Andr&#233; Fonseca
Andr&#233; Fonseca

Reputation: 79

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 😉

Upvotes: 3

bpgergo
bpgergo

Reputation: 16037

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;

Upvotes: 15

Related Questions