sqloracle-databaseplsqlbulk-operations

Oracle SQL : How add IF condition inside FORALL LOOP

I am trying to merge table into emp1, if the select_count is not equal to 0. But I couldn't add select and if statement inside FORALL Loop. Can anyone help me to achieve this? Thanks.

FORALL i IN 1 .. v_emp.LAST
    select count(emp_id) into select_count from emp where emp_id=v_emp(i).emp_id;
    IF select_count <> 0 THEN 
       MERGE INTO emp1 e1 using dual ON(a.id=b.id)
       WHEN MATCHED
         //Update statement
       WHEN NOT MATCHED 
         //Insert statement
    END IF;

The above code throwing error message:

PLS-00201 : Identifier 'I' must be declared.

Solution

  • FORALL is emphatically not a loop construct. It is an atomic statement, so there is no way to inject a condition into it.

    It seems unnecessary to combine FORALL with MERGE. MERGE is already a set operation and it also provides conditionals. Perhaps all you need do is change your implementation to drive off the USING clause.

    You haven't described all the logic you're trying to implement so the following is a guess: you'll need to convert it to fit your needs.

     merge into emp1 
 using ( select * from table ( v_emp ) t
         where t.emp_id not in ( select e.emp_id 
                                 from emp e )
      ) q
 on (q.emp_id = emp1.emp_id)
 when not matched then 
      insert ...
 when matched then
      update ...

    If this doesn't solve your problem, please edit your question to explain more about your scenario and the business logic you're trying to implement.