sql选择进入

发布于 2024-09-10 21:02:00 字数 456 浏览 4 评论 0原文

我在 test.sql 文件中有以下代码。当 test_summary 表中有记录时，一切正常。但是当表中没有记录时，它就会失败。我希望它在没有记录时继续打印 dbms_output 消息并进行处理。我怎样才能做到这一点？

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;

原文

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;

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

深海少女心 2024-09-17 21:02:00

我将添加一个简单的 NO_DATA_FOUND 异常处理程序。

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;

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;

回复收藏 0 原文

半葬歌 2024-09-17 21:02:00

除了加里完全有效的答案之外，您还可以通过使用显式游标来完全避免错误：

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;

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;

回复收藏 0 原文

傲世九天 2024-09-17 21:02:00

我更喜欢有例外的变体（参见@Gary的答案），但还有另一种常见的变体，它会出现问题：

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;

您可以使用 min() - 没关系。
如果没有找到数据，则变量中有空值

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

回复收藏 0 原文

﹎☆浅夏丿初晴 2024-09-17 21:02:00

可以一口气完成这一切。我认为避免未找到的行和太多的行。

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

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

回复收藏 0 原文

~没有更多了~