Fetch out cursor value and insert to another table

  
SQL> CREATE TABLE lecturer (
  2    id               NUMBER(5) PRIMARY KEY,
  3    first_name       VARCHAR2(20),
  4    last_name        VARCHAR2(20),
  5    major            VARCHAR2(30),
  6    current_credits  NUMBER(3)
  7    );

Table created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10001, 'Scott', 'Lawson','Computer Science', 11);

1 row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major, current_credits)
  2                VALUES (10002, 'Mar', 'Wells','History', 4);

1 row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10003, 'Jone', 'Bliss','Computer Science', 8);

1 row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10004, 'Man', 'Kyte','Economics', 8);

1 row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10005, 'Pat', 'Poll','History', 4);

1 row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10006, 'Tim', 'Viper','History', 4);

1 row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10007, 'Barbara', 'Blues','Economics', 7);

1 row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10008, 'David', 'Large','Music', 4);

1 row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10009, 'Chris', 'Elegant','Nutrition', 8);

1 row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10010, 'Rose', 'Bond','Music', 7);

1 row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10011, 'Rita', 'Johnson','Nutrition', 8);

1 row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10012, 'Sharon', 'Clear','Computer Science', 3);

1 row created.

SQL>
SQL> CREATE TABLE myStudent (
  2    student_id NUMBER(5) NOT NULL,
  3    department CHAR(3)   NOT NULL,
  4    course     NUMBER(3) NOT NULL,
  5    grade      CHAR(1)
  6    );

Table created.

SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
  2                           VALUES (10000, 'CS', 102, 'A');

1 row created.

SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
  2                           VALUES (10002, 'CS', 102, 'B');

1 row created.

SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
  2                           VALUES (10003, 'CS', 102, 'C');

1 row created.

SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
  2                           VALUES (10000, 'HIS', 101, 'A');

1 row created.

SQL>
SQL> CREATE TABLE MyTable (
  2    num_col    NUMBER,
  3    char_col   VARCHAR2(60)
  4    );

Table created.

SQL>
SQL>
SQL>
SQL> DECLARE
  2    CURSOR MyHistoryLecturer IS
  3      SELECT id, first_name, last_name
  4        FROM lecturer
  5        WHERE major = 'History';
  6  BEGIN
  7    FOR myLecturer IN MyHistoryLecturer LOOP
  8      INSERT INTO myStudent (student_id, department, course)
  9        VALUES (myLecturer.ID, 'HIS', 101);
 10
 11      INSERT INTO MyTable (num_col, char_col)
 12        VALUES (myLecturer.ID,
 13                myLecturer.first_name || ' ' || myLecturer.last_name);
 14
 15    END LOOP;
 16
 17    COMMIT;
 18  END;
 19  /

PL/SQL procedure successfully completed.

SQL>
SQL> select * from MyTable;

   NUM_COL CHAR_COL
---------- ------------------------------------------------------------
     10002 Mar Wells
     10005 Pat Poll
     10006 Tim Viper

SQL>
SQL> drop table MyTable;

Table dropped.

SQL>
SQL> drop table myStudent;

Table dropped.

SQL> drop table lecturer;

Table dropped.

SQL>

Related examples in the same category

1.	Below is a function that demonstrates how to use the FETCH statement
2.	Fetch value from cursor
3.	Fetches the records from a cursor variable that returns employee information
4.	Fetch cursor to two variables
5.	legal and illegal FETCH statements.
6.	"ORA-1002: fetch out of sequence" because of the commit inside the SELECT..FOR UPDATE loop.
7.	A WHILE cursor fetch loop.
8.	Different BULK_COLLECT statements used for bulk binds
9.	Check Cursor%FOUND after fetching
10.	Fetch value in cursor until NOTFOUND
11.	ORA-01002: fetch out of sequence
12.	PLS-00394: wrong number of values in the INTO list of a FETCH statement
13.	FETCH cursor BULK COLLECT
14.	Fetch cursor data to number variable
15.	Fetch cursor till cursorVariable%notfound
16.	Fetch cursor to table collection of row type
17.	Fetch cursor value to three variables
18.	Fetch row by row
19.	Fetch single column cursor to varchar2 variable

Fetch out cursor value and insert to another table : Cursor Fetch « Cursor « Oracle PL / SQL

Related examples in the same category