Oracle Cursor FOR LOOP Insert Example

In this tutorial, I'll show you how to insert records into a table in Oracle using FOR LOOP on the cursor.

Suppose you have the following table (emp_Sal):

create table emp_sal (
 empno number,
 sdate date,
 sal number,
 comm number,
 total_sal number
);

Cursor FOR LOOP Insert Example

And in this table, you need to insert records from the EMP table for all the employees. You can achieve this in Oracle using FOR LOOP on the cursor. Below is an example:

declare
  cursor c_emp
   is
   Select empno, sal, comm 
     from emp;
begin
   for c in c_emp loop
      insert into emp_sal (empno, sdate, sal, comm, total_sal)
        values (c.empno, sysdate, c.sal, c.comm, nvl(c.sal,0) + nvl(c.comm, 0));
   end loop;
   commit;
end;

After running the above PL/SQL block, the records will be inserted into the emp_sal table. You can query the table as below:

Select * from emp_sal;

Output:

Read also:

PL/SQL Cursor FOR LOOP Examples