sql select into

Question

I have the below code in test.sql file. When there is a record in test_summary table everything works fine. But when there is no record inside the table it fails. I want it to continue to print the dbms_output message and process when there is no record. How can I do that?

declare

    total_var      number(20,2) := 0.0;
    nl_var          number(20,2) := 0.0;

begin

    select col1,col2
    into total_var,nl_var
    from testsch.test_summary;

    dbms_output.put_LINE('');
    dbms_output.put_LINE('testing1' || total_var);
    dbms_output.put_LINE('testing2' || nl_var);

end;

Answer 1

There are two ways to do it. First test for existence (like MrUpsideDown's post, but cleaner):

if exists (select * from testsch.test_summary) then
    select col1,col2
    into total_var,nl_var
    from testsch.test_summary;
end if;

or for some db kung fu to guarantee that you always get at least one row:

select col1,col2
into total_var,nl_var
from (select 'x' from dual) x
left join testsch.test_summary on 1=1;

col1 and col2 will be null if there are no rows. if you want to provide a value (e.g. 0) when there is no row, use this:

select ifnull(col1, 0), ifnull(col2, 0)
into total_var,nl_var
from (select 'x' from dual) x
left join testsch.test_summary on 1=1;

Answer 2

I'd add a simple NO_DATA_FOUND exception handler.

declare
    total_var      number(20,2) := 0.0;
    nl_var          number(20,2) := 0.0;
begin
    begin
      select col1,col2
      into total_var,nl_var
      from testsch.test_summary;
    exception
      when no_data_found then null;
    end;
    dbms_output.put_LINE('');
    dbms_output.put_LINE('testing1' || total_var);
    dbms_output.put_LINE('testing2' || nl_var);
end;

Answer 3

In addition to Gary's perfectly valid answer, you can also avoid the error altogether by using an explicit cursor:

declare
    total_var      number(20,2) := 0.0;
    nl_var          number(20,2) := 0.0;
    cursor cur_test_summary is
      select col1,col2
      from testsch.test_summary;
begin
    open cur_test_summary;
    fetch cur_test_summary into total_var, nl_var;
    close cur_test_summary;
    dbms_output.put_LINE('');
    dbms_output.put_LINE('testing1' || total_var);
    dbms_output.put_LINE('testing2' || nl_var);
end;

Answer 4

I prefer variant with exception (see answer from @Gary), but there are another common variant, wich dials with problem:

declare

    total_var      number(20,2) := 0.0;
    nl_var          number(20,2) := 0.0;

begin

    select max(col1) , max(col2)
    into total_var,nl_var
    from testsch.test_summary;

    dbms_output.put_LINE('');
    dbms_output.put_LINE('testing1' || total_var);
    dbms_output.put_LINE('testing2' || nl_var);

end;

Your can use min() - don't matter. If no data found you got null values in variables

Answer 5

Could do it all in one go. Avoid the not found and too many rows I believe.

select col1,col2 into total_var,nl_var
from
(
    select col1,col2 from 
    (
      select col1,col2
      from testsch.test_summary
      UNION ALL
      select null,null from dual
    ) t1 order by col1
) where rownum = 1