Oracle SQL : How add IF condition inside FORALL LOOP

Question

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.

APC · Accepted Answer · 2019-10-11T06:15:01.010

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.

score 0 · Answer 2 · answered May 12 '18 at 22:42

The Oracle PL/SQL FORALL statement can only cover one SQL statement (no PL/SQL), as this basically orders the Oracle SQL engine to execute a bulk operation, and the SQL engine can't to PL/SQL blocks.

What you seem to want to do, is a FOR loop. Something like this:

FOR i IN 1 .. v_emp.LAST
LOOP
    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;
END LOOP;

If you still want the MERGE to be executed in bulk, via FORALL, then first use a loop, to calculate the counts, then remove the entries with zero counts from the v_emp collection, so you then can use FORALL with it.

Thanks for your answer.can you please add a sample code to remove the entries with zero count from the v_emp collection? — Yosuva Arulanthu, May 12 '18 at 23:56

score 0 · Answer 3 · answered May 14 '18 at 06:36

Thanks for your answer.can you please add a sample code to remove the entries with zero count from the v_emp collection?

Picking from your comments, you should pick only those records at first place in your query where count is not zero rather than checking it at later stage. See below.

CREATE TABLE emp(emp_NM VARCHAR2(10),
                  emp_id NUMBER,
                  sal NUMBER);
/
INSERT INTO emp VALUES ('X',1,100);                  
INSERT INTO emp VALUES ('A',2,200);
INSERT INTO emp VALUES('B',3,300);
INSERT INTO emp values ('C',4,400);
/
Select * from emp;
/

CREATE OR REPLACE TYPE ep_id IS TABLE OF NUMBER;

/

DECLARE
Type v_emp IS  TABLE OF NUMBER INDEX BY pls_integer;
  emp_var v_emp;

  --Declared a table having emp_id as in your case where you try to get this in a collection v_emp.
  v_ep_id ep_id:=ep_id(1,2,4,5,6);
BEGIN
  ---Query to select only those records whose count is greater than 0.
  SELECT COUNT(emp_id) 
  bulk collect  INTO emp_var
  FROM emp
  --WHERE emp_id IN  (SELECT * FROM TABLE(v_ep_id) ) --< You can use this as well.
  WHERE emp_id MEMBER OF v_ep_id
  GROUP BY emp_id, sal, emp_nm
  HAVING COUNT(emp_id) > 0 ;

  --Here you directly do the merge of the selected records which are not Zero
  FORALL i IN 1 .. emp_var.Count
  MERGE INTO emp1 e1 using dual ON(a.id=b.id)
  WHEN MATCHED
  //Update statement
  WHEN NOT MATCHED
  //Insert statement
  END IF; 

 Exception
 WHEN others then
 raise_application_error(-20001,'Error');
END;

Oracle SQL : How add IF condition inside FORALL LOOP

3 Answers3