How to check if row exists before SELECT INTO statement in Oracle SQL-CodePudding

I'm using Oracle SQL and have a procedure that is doing some operations on tables. During the procedure there is a "SELECT x INTO y FROM TABLE z WHERE..." statement inside a loop. Unfortunatly during the procedure I can't guarante that there is always a row to the corresponding where condition because it changes dynamically. Is it possible to check if a row exists before the statement? I was thinking of sth like "if exists(select ...) then SELECT X INTO y..."

Thanks for the help! Jack

CodePudding user response：

Well, there's no point in checking it first, and re-using the same statement again.

You could handle the exception (possibly in an inner BEGIN-EXCEPTION-END block):

declare
  y number;
begin
  begin               --> inner block starts here
    select x into y from z where ...

    insert into ...
  exception
    -- handle it, somehow; I chose not to do anything
    when no_data_found then
      null;
  end;               --> inner block ends here
end;

Or, if you used cursor FOR loop, you wouldn't have to handle it because - if select returns x, insert would run. Otherwise, nothing in that loop would ever be executed:

begin
  for cur_r in (select x from z where ...) loop
    insert into ...
  end loop;
end;

CodePudding user response：

An exception handler as in Littlefoot's answer is the most correct and explicit approach, however just for completeness you might also consider using an aggregate.

Value 'X' exists in the table:

declare
    p_someparam varchar2(1) := 'X';
    l_somevalue varchar2(1);
    l_check     number;
begin
    select max(dummy), count(*) into l_somevalue, l_check
    from   dual d
    where  d.dummy = p_someparam;

    dbms_output.put_line('Result: '||l_somevalue); 
    dbms_output.put_line(l_check||' row(s) found'); 
end;

Result: X
1 row(s) found

Value 'Z' does not exist in the table:

declare
    p_someparam varchar2(1) := 'Z';
    l_somevalue varchar2(1);
    l_check     number;
begin
    select max(dummy), count(*) into l_somevalue, l_check
    from   dual d
    where  d.dummy = p_someparam;

    dbms_output.put_line('Result: '||l_somevalue); 
    dbms_output.put_line(l_check||' row(s) found'); 
end;

Result: 
0 row(s) found

You can add logic to handle the cases where the count check is 0 or greater than 1.

CodePudding user response：

If you are having procedure then I should say use if statement and then write the sql:

select some_column into some_variable from tablename where condition 

IF somevariable not in (<list of values separated by comma>)THEN
   {statements to execute }
END IF;