28

I'm writing a procedure, and i need to check whether my select query returned an empty record or not. (In this example whether there is no x,y shelf)

How can i do that?

I tried this:

temp shelves.loadability%TYPE := NULL;
BEGIN

select loadability into temp from shelves where rownumber = x and columnnumber = y;
IF temp IS NOT NULL THEN
/* do something when it's not empty */
ELSE
/* do the other thing when it's empty */
END IF;

But the second branch of the if never works...

EDIT:

Oh, it was so easy...

temp shelves.loadability%TYPE;
BEGIN

select count(*) into temp from shelves where rownumber = x and columnnumber = y;
IF temp != 0 THEN
/* do something when it's not empty */
ELSE
/* do the other thing when it's empty */
END IF;

END;
WonderCsabo
  • 11,947
  • 13
  • 63
  • 105

4 Answers4

20

Use an exception handler

Begin
  select column
  into variable
  from table
  where ...;

  -- Do something with your variable

exception
 when no_data_found then
    -- Your query returned no rows --

 when too_many_rows
    -- Your query returned more than 1 row --

end;
Rene
  • 10,391
  • 5
  • 33
  • 46
  • 23
    Is that good practice? You're using an `exception` like a `condition`. – Yehuda Shapira Jan 24 '12 at 13:02
  • 1
    Not, it is not good practice, see the "Tip" in the Error Trapping section: https://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING – alisianoi Oct 24 '16 at 18:12
  • 1
    It's called exception handling. It's the way plsql works. – Rene Feb 07 '17 at 12:59
  • 1
    I don't think that this is a good approach. Because, by definition `exceptional condition` is a case that the program is unaware of how to handle. At such a case, the safest thing to do is gracefully terminate the process/function/procedure. Implementing business rules inside the `graceful shutdown routine` wouldn't be a really good idea, in terms of good programming practices! – Romeo Sierra Apr 04 '18 at 06:42
  • When you execute a select ... into in PLSQL you must expect 0, 1, or more results. And you need to handle these cases. Of course you can do a select count(*) first, but there is no 100% garantee that the data hasn't changed when you execute the select into, a moment later. Handling the no_data_found and too_many_rows exceptions is a very standard way of working with select .. into queries in PLSQL. – Rene Apr 04 '18 at 08:05
  • `do a select count(*) first, but there is no 100% garantee that the data hasn't changed when you execute the select into, a moment later` Didn't get this! Elaborate will you? – Romeo Sierra Apr 04 '18 at 08:13
  • Say you want to select a row from a table where name='John'. First you check that John exists in the table with a select count(*). If his gives you 1 then you can execute the select into query. But maybe, just after you did the select count(*), somebody deleted the John record. Now, your select into query will throw a no_data_found exception. So you end up having to handle the no_data_found exception anyway. No point in having the extra overhead of a select count(*). – Rene Apr 04 '18 at 08:21
  • Well your point is correct. Even if you manage to complete the `SELECT INTO` and evade without any problems, if the deletion that you have mentioned take place *just after you do the selection*, You are still in a different problem right? Now you have a tuple that you have selected into the cache, **which is not available in the persistent layer** isn't it? This is an inherent problem with concurrency if I have not mistaken. – Romeo Sierra Apr 04 '18 at 08:49
6

Exception handling would be the first thing I think of too, but if you don't want to burden yourself with handling all the different cases, I tend to use a select count(*) from. The nice thing with count(*) is that it ALWAYS returns something (assuming your query is legal). In this case you could count to see if it returns 0 (no matches) or more (in which case you can do something.

You could get something like this:

declare
  v_count number := 0;
begin
  select count(*) into v_count from table where condition;

  if v_count = 0 then
      --do something
  else
      --do something else
  end if;
end;
schwarz
  • 193
  • 2
  • 11
3

It is generally more SQL-like to just do the work for the records that exist. In other words, you could perform your task for each occurrence of the match, and if there are no occurrences you don't do it. So you would not even need the IF-ELSE construct.

I won't recommend using a cursor to do the work, because that would be counter to my first suggestion that you do it more SQL-like. But if you have to do it this way, then a cursor might do what you want.

And yes, I realize that does not answer your question directly.

MJB
  • 7,639
  • 2
  • 31
  • 41
0

catch first not wanted condition and use count(1) because count(*) actually trying to count something and add rownum=1, you need only one first not matching condition. i use this statement.

       declare
      v_check number;
    begin
      select count(1) into v_check 
from table
 where condition(something not wanted) AND rownum=1;

      if v_check = 0 then 
           --do something else
      elsif v_check = 1 --dont want theat
         rise some error or more..
      end if;
    end;

For you just 

select count(1) into v_check from dual where exists (select count(1) 
    from table
     where condition AND rownum=1);

if v_check = 0 then --nothing found
                 something...
          elsif v_check = 1 --found something
            something...
          end if;
        end;
Strauteka
  • 198
  • 1
  • 10