Adding multiple records in a table by using FOR LOOP in PL/SQL

Question

This is the table I have created:

   CREATE TABLE Screening
  (
   screening_id NUMBER(6) PRIMARY KEY,
   CONSTRAINT check_screening_id CHECK(screening_id > 0),
   plan_id NUMBER(4) NOT NULL,
   theatre_id NUMBER(1) NOT NULL,
   screening_date DATE NOT NULL,
   screening_start_hh24 NUMBER(2) NOT NULL,
   CONSTRAINT check_start_hh24 CHECK(screening_start_hh24 BETWEEN 9 AND 22),
   screening_start_mm60 NUMBER(2) NOT NULL,
   CONSTRAINT check_start_mm60 CHECK(screening_start_mm60 BETWEEN 0 AND 59)
  );

Then I have created a sequence which I will use it to insert in the screening_id column of Screening table:

    CREATE SEQUENCE screening_seq 
    START WITH 1 
    INCREMENT BY 1 
    NOCACHE 
    NOCYCLE;

Then I have inserted 19 rows in Screening table:

INSERT INTO Screening (screening_id,plan_id,theatre_id,screening_date,screening_start_hh24,screening_start_mm60)
VALUES (screening_seq.NEXTVAL,1,3,TO_DATE('11/9/2017','DD/MM/YYYY'),9,0);
INSERT INTO Screening (screening_id,plan_id,theatre_id,screening_date,screening_start_hh24,screening_start_mm60)
VALUES (screening_seq.NEXTVAL,1,3,TO_DATE('11/9/2017','DD/MM/YYYY'),11,0);
INSERT INTO Screening (screening_id,plan_id,theatre_id,screening_date,screening_start_hh24,screening_start_mm60)
VALUES (screening_seq.NEXTVAL,1,3,TO_DATE('11/9/2017','DD/MM/YYYY'),13,0);
INSERT INTO Screening (screening_id,plan_id,theatre_id,screening_date,screening_start_hh24,screening_start_mm60)
VALUES (screening_seq.NEXTVAL,1,2,TO_DATE('11/9/2017','DD/MM/YYYY'),13,0);
INSERT INTO Screening (screening_id,plan_id,theatre_id,screening_date,screening_start_hh24,screening_start_mm60)
VALUES (screening_seq.NEXTVAL,1,3,TO_DATE('11/9/2017','DD/MM/YYYY'),15,0);
INSERT INTO Screening (screening_id,plan_id,theatre_id,screening_date,screening_start_hh24,screening_start_mm60)
VALUES (screening_seq.NEXTVAL,1,3,TO_DATE('11/9/2017','DD/MM/YYYY'),17,0);
INSERT INTO Screening (screening_id,plan_id,theatre_id,screening_date,screening_start_hh24,screening_start_mm60)
VALUES (screening_seq.NEXTVAL,2,1,TO_DATE('11/9/2017','DD/MM/YYYY'),9,0);
INSERT INTO Screening (screening_id,plan_id,theatre_id,screening_date,screening_start_hh24,screening_start_mm60)
VALUES (screening_seq.NEXTVAL,2,1,TO_DATE('11/9/2017','DD/MM/YYYY'),12,0);
INSERT INTO Screening (screening_id,plan_id,theatre_id,screening_date,screening_start_hh24,screening_start_mm60)
VALUES (screening_seq.NEXTVAL,2,1,TO_DATE('11/9/2017','DD/MM/YYYY'),15,0);
INSERT INTO Screening (screening_id,plan_id,theatre_id,screening_date,screening_start_hh24,screening_start_mm60)
VALUES (screening_seq.NEXTVAL,2,2,TO_DATE('11/9/2017','DD/MM/YYYY'),9,0);
INSERT INTO Screening (screening_id,plan_id,theatre_id,screening_date,screening_start_hh24,screening_start_mm60)
VALUES (screening_seq.NEXTVAL,2,2,TO_DATE('11/9/2017','DD/MM/YYYY'),15,0);
INSERT INTO Screening (screening_id,plan_id,theatre_id,screening_date,screening_start_hh24,screening_start_mm60)
VALUES (screening_seq.NEXTVAL,3,4,TO_DATE('11/9/2017','DD/MM/YYYY'),9,0);
INSERT INTO Screening (screening_id,plan_id,theatre_id,screening_date,screening_start_hh24,screening_start_mm60)
VALUES (screening_seq.NEXTVAL,3,4,TO_DATE('11/9/2017','DD/MM/YYYY'),12,0);
INSERT INTO Screening (screening_id,plan_id,theatre_id,screening_date,screening_start_hh24,screening_start_mm60)
VALUES (screening_seq.NEXTVAL,3,4,TO_DATE('11/9/2017','DD/MM/YYYY'),15,0);
INSERT INTO Screening (screening_id,plan_id,theatre_id,screening_date,screening_start_hh24,screening_start_mm60)
VALUES (screening_seq.NEXTVAL,3,4,TO_DATE('11/9/2017','DD/MM/YYYY'),18,0);
INSERT INTO Screening (screening_id,plan_id,theatre_id,screening_date,screening_start_hh24,screening_start_mm60)
VALUES (screening_seq.NEXTVAL,4,1,TO_DATE('11/9/2017','DD/MM/YYYY'),18,0);
INSERT INTO Screening (screening_id,plan_id,theatre_id,screening_date,screening_start_hh24,screening_start_mm60)
VALUES (screening_seq.NEXTVAL,4,2,TO_DATE('11/9/2017','DD/MM/YYYY'),18,0);
INSERT INTO Screening (screening_id,plan_id,theatre_id,screening_date,screening_start_hh24,screening_start_mm60)
VALUES (screening_seq.NEXTVAL,4,3,TO_DATE('11/9/2017','DD/MM/YYYY'),19,0);
INSERT INTO Screening (screening_id,plan_id,theatre_id,screening_date,screening_start_hh24,screening_start_mm60)
VALUES (screening_seq.NEXTVAL,4,4,TO_DATE('11/9/2017','DD/MM/YYYY'),21,0);

Now I have got the following assignment question:

On the first day (11/9/2017) (i.e. value of screening_date column), 19 records have already been added manually in the Screening table which has been demonstrated a while ago. Similarly, we also need to add the same 19 records on the second day (12/9/2017) (i.e. value of screening_date column). In other words, we keep repeating the process of inserting the same 19 records in the Screening table until 28th day (8/10/2017) (i.e. value of screening_date column). Therefore, for 28 days we need to insert a total of (28 X 19) = 532 records. However, instead of adding all the 532 records manually, we are looking to insert them by using a FOR LOOP in PL/SQL.

A hint has already been given:

DECLARE
first_date DATE := TO_DATE('11/09/2017','DD/MM/YYYY');
last_date  DATE := TO_DATE('08/10/2017','DD/MM/YYYY');
(no_of_days_var) NUMBER;

BEGIN

SELECT last_date - first_date 
INTO no_of_days 
FROM DUAL;

FOR j in 1.. no_of_days
LOOP

-- your 19 insert statements with screening_date being (your first date var) + j

END LOOP;
END;

I am just stuck and confused regarding what code to add in the FOR LOOP body. That is the only thing I need to get. It would be really helpful, if the solution code in the FOR LOOP body is provided.

krokodilko · Accepted Answer

Don't use PL/SL loops, just run one simple INSERT ... SELECT ,
and replace "on the fly" some values (id and date) in the SELECT expressions list.
This is an example for the single one day only:

INSERT INTO Screening( 
    screening_id, plan_id,theatre_id,screening_date,
    screening_start_hh24, screening_start_mm60
)
SELECT screening_seq.NEXTVAL As screening_id,
       plan_id,
       theatre_id,
       date '2017-09-12' As screening_date,
       screening_start_hh24,
       screening_start_mm60
FROM Screening
where screening_date = date '2017-09-11'
;

This is an example for all 28 days (11th of september + 27 next days)

INSERT INTO Screening( 
    screening_id, plan_id,theatre_id,screening_date,screening_start_hh24,screening_start_mm60
)
SELECT screening_seq.NEXTVAL As screening_id,
       s.plan_id,
       s.theatre_id,
       rows_generator.new_date As screening_date,
       s.screening_start_hh24,
       s.screening_start_mm60
FROM Screening s
CROSS JOIN (
    select date '2017-09-11' + level as new_date
    from dual
    connect by level <=  date '2017-10-08' - date '2017-09-11'
) rows_generator
where s.screening_date = date '2017-09-11'
;

PL/SQL version 1:

BEGIN
  FOR i IN  1 .. date '2017-10-08' - date '2017-09-12' + 1 LOOP
     INSERT INTO Screening( 
            screening_id, plan_id,theatre_id,screening_date,
            screening_start_hh24, screening_start_mm60
     )
     SELECT screening_seq.NEXTVAL As screening_id,
           plan_id,
           theatre_id,
           date '2017-09-12' + i As screening_date,
           screening_start_hh24,
           screening_start_mm60
     FROM Screening
     WHERE screening_date = date '2017-09-12' ;
  END LOOP;
END;
/

PL/SQL Version 2 using bulk collect and forall

DECLARE 
   TYPE my_row_t IS TABLE OF Screening%rowtype;
   my_rows my_row_t;
BEGIN
   -- store rows in the collection
   SELECT * 
   BULK COLLECT INTO my_rows
   FROM Screening
   WHERE screening_date = date '2017-09-12' ;

  FOR i IN  1 .. date '2017-10-08' - date '2017-09-12' + 1 LOOP

      -- update some values in the collection
      FOR j IN 1 .. my_rows.count LOOP
          my_rows( j ).screening_id := screening_seq.NEXTVAL;
          my_rows( j ).screening_date := date '2017-09-12' + j;
      END LOOP;

      -- insert rows from the collection to the table
      FORALL j IN 1 .. my_rows.count 
      INSERT INTO Screening VALUES my_rows( j );

  END LOOP;
END;
/

Adding multiple records in a table by using FOR LOOP in PL/SQL

Answers (2)

Related Questions