Oracle PL/SQL - Inserting Records into Tables

  1. HOME
  2. Oracle PL/SQL
  3. PL SQL Data Type
  4. RECORD Types

Introduction

The PL/SQL can insert a record into a table.

The record must represent a row of the table.

The following code creates the table schedule and initializes it by putting default values in a record and inserting the record into the table for each week.

Demo

SQL>
SQL> --Initializing Table by Inserting Record of Default Values
SQL> DROP TABLE schedule;
DROP TABLE schedule
           *-- w  w  w  .j  a va 2s  .co m
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> CREATE TABLE schedule (
  2    week  NUMBER,
  3    Mon   VARCHAR2(10),
  4    Tue   VARCHAR2(10),
  5    Wed   VARCHAR2(10),
  6    Thu   VARCHAR2(10),
  7    Fri   VARCHAR2(10),
  8    Sat   VARCHAR2(10),
  9    Sun   VARCHAR2(10)
 10   );
SQL>
SQL> DECLARE
  2    default_week  schedule%ROWTYPE;
  3    i             NUMBER;
  4  BEGIN
  5    default_week.Mon := '0800-1700';
  6    default_week.Tue := '0800-1700';
  7    default_week.Wed := '0800-1700';
  8    default_week.Thu := '0800-1700';
  9    default_week.Fri := '0800-1700';
 10    default_week.Sat := 'Day Off';
 11    default_week.Sun := 'Day Off';
 12
 13    FOR i IN 1..6 LOOP
 14      default_week.week    := i;
 15
 16      INSERT INTO schedule VALUES default_week;
 17    END LOOP;
 18  END;
 19  /

PL/SQL procedure successfully completed.

SQL>
SQL> SELECT * FROM schedule;

      WEEK MON        TUE        WED
---------- ---------- ---------- ----------
THU        FRI        SAT        SUN
---------- ---------- ---------- ----------
         1 0800-1700  0800-1700  0800-1700
0800-1700  0800-1700  Day Off    Day Off

         2 0800-1700  0800-1700  0800-1700
0800-1700  0800-1700  Day Off    Day Off

         3 0800-1700  0800-1700  0800-1700
0800-1700  0800-1700  Day Off    Day Off

         4 0800-1700  0800-1700  0800-1700
0800-1700  0800-1700  Day Off    Day Off

         5 0800-1700  0800-1700  0800-1700
0800-1700  0800-1700  Day Off    Day Off

         6 0800-1700  0800-1700  0800-1700
0800-1700  0800-1700  Day Off    Day Off


6 rows selected.

SQL>

To efficiently insert a collection of records into a table, put the INSERT statement inside a FORALL statement.

Related Topic