CODEWITHSUNDEEP
sqloracle-databaseplsql
Arav
Arav

Reputation: 5247

sql select into

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;

Upvotes: 2

Views: 677

Answers (4)

rfusca
rfusca

Reputation: 7705

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

Upvotes: 0

ThinkJet
ThinkJet

Reputation: 6735

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

Upvotes: 0

Allan
Allan

Reputation: 17429

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;

Upvotes: 0

Gary Myers
Gary Myers

Reputation: 35401

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;

Upvotes: 8

Related Questions