Tutorial :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;

Solution:1

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;

Solution:2

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;

Solution:3

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

Solution:4

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

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com