How to create cursor using for loop in PL/SQL to transfer records?

Question

For creating cursor using FOR loop, but my only problem is how can we transfer each row that has been fetch into another table ? Is it possible?

Using blocks of PL/SQL and include a FOR loop of sequel after we fetch rows from specific table and after that we would like to transfer the rows that has been fetch to the other tables.

Answer 1

Here's an example which copies values from Scott's DEPT table into TEST table (owned by Scott as well).

SQL> create table test (deptno number, dname varchar2(20), loc varchar2(20));

Table created.

SQL> begin
  2    for cur_r in (select deptno, dname, loc
  3                  from dept
  4                  where deptno <= 30
  5                 )
  6    loop
  7      insert into test (deptno, dname, loc)
  8        values (cur_r.deptno, cur_r.dname, cur_r.loc);
  9    end loop;
 10  end;
 11  /

PL/SQL procedure successfully completed.

SQL> select * from test;

    DEPTNO DNAME                LOC
---------- -------------------- --------------------
        10 ACCOUNTING           NEW YORK
        20 RESEARCH             DALLAS
        30 SALES                CHICAGO

SQL>