plsql - How to select into a variable in PL/SQL when the result might be null?

Question

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.

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;

thanks.

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.

Answer 1

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;