Amalgamate 2 select statements into 1

Question

I currently have two fairly long select statements, identical apart from the fact that the first is looking to see if the row exists via count(*) and the second selecting the row into the rowtype variable based on an if statement. This is because the data is required for further manipulation

I know it is possible, but I am having a complete brainfreeze and google returns lots of information about select, and if/else but not branching based on select.

I've tried wrapping the select in an if statements and failed so I was wondering what else there is to try.

Update: Psuedo-example:

select count(*) into num_items where <very long operation>;
if (num_items = 1) then
  begin
  select * into row_item where <very long operation>;
  end;
else
  dbms_output.put_line('failure');
end if;

Also: On a slightly unrelated note, how do you get the dbms_output to flush immeadiately so you can see where the script is currently up to? At the moment it only prints once the script has ended.

Thanks.

Answer 1

I think you want SELECT CASE.

If you need more specific help, post your current code.

Answer 2

I think the code you want is this:

begin
  select * into row_item where <very long operation>;
exception
  when too_many_rows then
    dbms_output.put_line('failure');
end;

dbms_output is only really any good for debugging while developing code, or for SQL scripts that you will run from SQL Plus or an IDE. You could use an autonomous transaction to write messages to a table; these can then be read by another session while the program is still running. See this question for more details.

Answer 3

I'd consider something like this:

DECLARE
  CURSOR c IS
    SELECT COUNT(*) OVER () count_rows,
    q.* FROM (<very long operation>) q
    WHERE ROWNUM <= 2;
  r c%ROWTYPE;
BEGIN
  OPEN c;
  FETCH c INTO r;
  IF c%NOTFOUND OR r.count_rows != 1 THEN
    dbms_output.put_line('failure');
  ELSE
    --process the row
  END IF;
END;

This will count at most 2 records from the "very long operation"; but will only fetch the first row. r.count_rows will be 1 if only one row was found, or 2 if more than 1 row was found. This way you avoid the double-whammy of executing the long operation twice.