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:
EMPNO | SDATE | SAL | COMM | TOTAL_SAL |
---|---|---|---|---|
7839 | 11/09/2022 | 5000 | - | 5000 |
7698 | 11/09/2022 | 2850 | - | 2850 |
7782 | 11/09/2022 | 2450 | 4989 | 7439 |
Read also:
PL/SQL Cursor FOR LOOP Examples