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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to select

Examples related to plsql