当结果可能为空时，如何在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会抱怨变量未设置.我只能运行两次查询，第一个查询执行计数，如果计数为零，则将变量设置为null，如果计数为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.

因此代码如下:

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屋！