当结果可能为空时，如何在 PL/SQL 中选择一个变量？

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

目前，我不能直接做一个 select into变量，因为如果查询没有返回任何值，PL/SQL 会抱怨变量没有被设置。我只能运行查询两次，第一次执行计数，如果计数为零，则将变量设置为 null，如果计数为1，则选择变量。

所以代码应该是这样的:

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台词有点犹豫。

346806 次浏览
小开
最佳答案

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;
小开

I would recommend using a cursor. A cursor fetch is always a single row (unless you use a bulk collection), and cursors do not automatically throw no_data_found or too_many_rows exceptions; although you may inspect the cursor attribute once opened to determine if you have a row and how many.

declare
v_column my_table.column%type;
l_count pls_integer;
cursor my_cursor is
select count(*) from my_table where ...;


begin
open my_cursor;
fetch my_cursor into l_count;
close my_cursor;


if l_count = 1 then
select whse_code into v_column from my_table where ...;
else
v_column := null;
end if;
end;

Or, even more simple:

    declare
v_column my_table.column%type;
cursor my_cursor is
select column from my_table where ...;


begin
open my_cursor;
fetch my_cursor into v_column;
-- Optional IF .. THEN based on FOUND or NOTFOUND
-- Not really needed if v_column is not set
if my_cursor%notfound then
v_column := null;
end if;
close my_cursor;
end;
小开

I use this syntax for flexibility and speed -

    begin
--
with KLUJ as
( select 0 ROES from dual
union
select count(*) from MY_TABLE where rownum = 1
) select max(ROES) into has_rows from KLUJ;
--
end;

Dual returns 1 row, rownum adds 0 or 1 rows, and max() groups to exactly 1. This gives 0 for no rows in a table and 1 for any other number of rows.

I extend the where clause to count rows by condition, remove rownum to count rows meeting a condition, and increase rownum to count rows meeting the condition up to a limit.

小开

Using an Cursor FOR LOOP Statement is my favourite way to do this.

It is safer than using an explicit cursor, because you don't need to remember to close it, so you can't "leak" cursors.

You don't need "into" variables, you don't need to "FETCH", you don't need to catch and handle "NO DATA FOUND" exceptions.

Try it, you'll never go back.

v_column my_table.column%TYPE;


v_column := null;


FOR rMyTable IN (SELECT COLUMN FROM MY_TABLE WHERE ....) LOOP
v_column := rMyTable.COLUMN;
EXIT;  -- Exit the loop if you only want the first result.
END LOOP;
小开

COALESCE will always return the first non-null result. By doing this, you will get the count that you want or 0:

select coalesce(count(column) ,0) into v_counter from my_table where ...;
小开

What about using MAX? That way if no data is found the variable is set to NULL, otherwise the maximum value.
Since you expect either 0 or 1 value, MAX should be OK to use.

v_column my_table.column%TYPE;
select MAX(column) into v_column from my_table where ...;
小开

I know it's an old thread, but I still think it's worth to answer it.

select (
SELECT COLUMN FROM MY_TABLE WHERE ....
) into v_column
from dual;

Example of use:

declare v_column VARCHAR2(100);
begin
select (SELECT TABLE_NAME FROM ALL_TABLES WHERE TABLE_NAME = 'DOES NOT EXIST')
into v_column
from dual;
DBMS_OUTPUT.PUT_LINE('v_column=' || v_column);
end;
小开

From all the answers above, Björn's answer seems to be the most elegant and short. I personally used this approach many times. MAX or MIN function will do the job equally well. Complete PL/SQL follows, just the where clause should be specified.

declare v_column my_table.column%TYPE;
begin
select MIN(column) into v_column from my_table where ...;
DBMS_OUTPUT.PUT_LINE('v_column=' || v_column);
end;

提交答案