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.

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 开发者_StackOverflow中文版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.

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

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 ...;

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;

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;

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.

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 ...;