Reference implicit in insert statement : Implicit Cursor « Cursor « Oracle PL / SQL






  1. Oracle PL / SQL
  2. Cursor
  3. Implicit Cursor

Reference implicit in insert statement

  
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>
SQL>
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> INSERT INTO myStudent (student_id, department, course, grade)
  2                           VALUES (10001, 'HIS', 101, 'B');

1 row created.

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

1 row created.

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

1 row created.

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

1 row created.

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

1 row created.

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

1 row created.

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

1 row created.

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

1 row created.

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

1 row created.

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

1 row created.

SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
  2                           VALUES (10008, 'NUT', 307, 'A');

1 row created.

SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
  2                           VALUES (10010, 'NUT', 307, 'A');

1 row created.

SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
  2                           VALUES (10009, 'MUS', 410, 'B');

1 row created.

SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
  2                           VALUES (10006, 'MUS', 410, 'E');

1 row created.

SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
  2                           VALUES (10011, 'MUS', 410, 'B');

1 row created.

SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
  2                           VALUES (10000, 'MUS', 410, 'B');

1 row created.

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

Table created.

SQL>
SQL> BEGIN
  2    FOR myLecturer IN (SELECT id, first_name, last_name
  3                            FROM lecturer
  4                            WHERE major = 'History') LOOP
  5      INSERT INTO myStudent (student_id, department, course)
  6        VALUES (myLecturer.ID, 'HIS', 301);
  7
  8      INSERT INTO MyTable (num_col, char_col)
  9        VALUES (myLecturer.ID,
 10                myLecturer.first_name || ' ' || myLecturer.last_name);
 11    END LOOP;
 12  END;
 13  /

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> select * from MyTable;

 NUM_COL CHAR_COL
-------- ------------------------------------------------------------
######## Mar Wells
######## Pat Poll
######## Tim Viper

SQL>
SQL> drop table MyTable;

Table dropped.

SQL>
SQL> drop table lecturer;

Table dropped.

SQL>
SQL> drop table myStudent;

Table dropped.








Related examples in the same category

1.Use implicit cursor
2.Implicit cursors: SQL%FOUND returns TRUE if SQL statement found one or more records
3.use for loop cursor
4.Implicit Cursors: sql%rowcount
5.Cursor for loop
6.use cursor attributes on the implicit SQL cursor.
7.Implicit cursor by for loop
8.Test cursor attributes with an implicit cursor
9.Use implicit or explicit cursor to insert 50000 rows to a table
10.Use passed in value with creating a cursor
11.Write an implicit cursor in a FOR loop and use the data
12.explicit cursor with cursor variable
13.for data in ( select * from tableName )