for loop in oracle PLSQL

Question

I want the value i to increment by 1 for every insert in the table employee_dep

i := 1
For lh in
 (SELECT DISTINCT z.emp_id
           FROM employee z)
LOOP
 INSERT INTO employee_dep
        (emp_id, emp_lfd, dep_name, dept_no)
        SELECT w.emp_id, i, w.dep_name, w.dep_no)
        FROM
          central_dep w
        where w.emp_id= lh.emp_id;
         i := i+1;
end loop;

But if there are two rows with same emp_id then i is same for both the rows. I want i to be incremented by 1 in the next row. (i to be incremented by 1 for every new insert in the table)

for eg. i wanted the columns to be

emp_id emp_lfd
1111     1
1111     2
1111     3
1122     1
1122     2
1122     3
1122     4

But what i am currently getting is

emp_id emp_lfd
1111     3
1111     3
1111     3
1122     4
1122     4    
1122     4
1122     4

Answer 1

Seems that you just need ROW_NUMBER() analytic function with PARTITION BY emp_id within your INSERT statement such as

INSERT INTO employee_dep(emp_id, emp_lfd, dep_name, dept_no)
SELECT emp_id, ROW_NUMBER() OVER (PARTITION BY emp_id ORDER BY 0), dep_name, dep_no
  FROM central_dep

I've tried to regenerate your case within the Demo