当结果可能为空时，如何在 PL/SQL 中选择一个变量? [英] How to select into a variable in PL/SQL when the result might be null?

问题描述

有没有办法只运行一次查询来选择一个变量，考虑到查询可能什么都不返回，那么在这种情况下变量应该为空.

Is there a way in to just run a query once to select into a variable, considering that the query might return nothing, then in that case the variable should be null.

目前，我不能直接对变量执行select into，因为如果查询没有返回任何内容，PL/SQL 会抱怨变量未设置.我只能运行两次查询，第一个进行计数，如果计数为零，则将变量设置为空，如果计数为 1，则选择到变量中.

Currently, I can't do a select into a variable directly, since if the query returns nothing, the PL/SQL would complain variable not getting set. I can only run the query twice, with the first one do the count and if the count is zero, set the variable to null, and if the count is 1, select into the variable.

所以代码应该是这样的:

So the code would be like:

v_column my_table.column%TYPE;
v_counter number;
select count(column) into v_counter from my_table where ...;
if (v_counter = 0) then
    v_column := null;
elsif (v_counter = 1) then
    select column into v_column from my_table where ...;
end if;

谢谢.

更新:我没有使用异常的原因是我在分配了v_column之后还有一些下面的逻辑，我必须在异常部分使用goto才能跳回到下面代码.我对 goto 行有点犹豫.

Update: The reason I didn't use exception is I still have some following logic after assigning the v_column, and I have to use goto in the exception section to jump back to the following code. I'm kind of hesitate of goto lines.

推荐答案

您可以通过将变量设置为 NULL 来简单地处理 NO_DATA_FOUND 异常.这样，只需要一个查询.

You can simply handle the NO_DATA_FOUND exception by setting your variable to NULL. This way, only one query is required.

    v_column my_table.column%TYPE;

BEGIN

    BEGIN
      select column into v_column from my_table where ...;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        v_column := NULL;
    END;

    ... use v_column here
END;

这篇关于当结果可能为空时，如何在 PL/SQL 中选择一个变量?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！