0

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.

Improve this question

2 Answers 2

Reset to default
2

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;
/
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

I have tested your code ... it worked and I showed the work to the lecturer right away. He said that even though it is correct but he won't give any marks for this solution. He wants it to be done in FOR LOOP. That's why he has given the hint which I have posted at the end of my question. You can have a look at the FOR LOOP hint at the end of the question for your reference. It would be really helpful if the solution code in the FOR LOOP body is provided.
I've updated my aswer with two examples how it can be done in pl/sql LOOPs
1

By the way, I have solved the problem and it worked successfully. This is how I have approached it:

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

BEGIN

SELECT last_date - first_date 
INTO no_of_days 
FROM DUAL;

FOR j in 1.. no_of_days
LOOP
INSERT INTO Screening (screening_id,plan_id,theatre_id,screening_date,screening_start_hh24,screening_start_mm60)
VALUES (screening_seq.NEXTVAL,1,3,first_date + j,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,first_date + j,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,first_date + j,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,first_date + j,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,first_date + j,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,first_date + j,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,first_date + j,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,first_date + j,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,first_date + j,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,first_date + j,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,first_date + j,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,first_date + j,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,first_date + j,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,first_date + j,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,first_date + j,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,first_date + j,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,first_date + j,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,first_date + j,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,first_date + j,21,0);
END LOOP;

END;
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.