How many rows will be inserted into the tab1 table after the following PL/SQL bl
ID: 3866795 • Letter: H
Question
How many rows will be inserted into the tab1 table after the following PL/SQL block has been executed successfully (no runtime error)?
DECLARE
v_count NUMBER := 1;
v_1 NUMBER := 1;
BEGIN
DELETE FROM tab1;
COMMIT;
FOR i IN REVERSE 6..11 LOOP
INSERT INTO tab1 VALUES (i, i*2, i+3);
END LOOP;
SELECT COUNT(*)
INTO v_count
FROM tab1;
FOR i IN 3..v_count + 11 LOOP
INSERT INTO tab1 VALUES (i, i+10, i+20);
END LOOP;
INSERT INTO tab1 VALUES (99, 205, 306);
SELECT COUNT(*)
INTO v_1
FROM tab1;
WHILE v_1 >= 7 LOOP
IF v_1 = 10 OR v_1 = 25 OR v_1 = 35 OR v_1 = 45 THEN
INSERT INTO tab1 VALUES (v_1*20, v_1*30, v_1*40);
ELSE
v_1 := v_1 - 1;
END IF;
v_1 := v_1 - 2;
END LOOP;
FOR i IN 2..48 LOOP
IF i = 4 OR i = 16 OR i= 32 OR i = 48 THEN
INSERT INTO tab1 VALUES (i*20, i*30, i*40);
END IF;
INSERT INTO tab1 VALUES (i*21, i*31, i*41);
END LOOP;
INSERT INTO tab1 VALUES (616, 222, 243);
INSERT INTO tab1 VALUES (77, 88, 99);
INSERT INTO tab1 VALUES (77, 88, 55);
INSERT INTO tab1 VALUES (66, 88, 99);
INSERT INTO tab1 VALUES (66, 88, 91);
COMMIT;
END;
A. 75
B. 76
C. 77
D. 78
E. 79
F 80
Explanation / Answer
1. Initially all the rows from table tab_1 is deleted.
2. First for loop (FOR i IN REVERSE 6..11 LOOP) iterates for 6 times (11, 10, 9, 7, 8, 6) and inserts 6 records into table tab1.
3. Second for loop (FOR i IN 3..v_count + 11 LOOP) iterates from 3 to 17 (6 + 11) and inserts 15 records into table tab1.
4. As of now, total records in table tab1 is 21 records.
5. A single insert statement inserts another record into table. (Total records as of now: 22)
6. Value of v_1 will be 22
7. While loop gets iterated for 6 times. Values of v_1 at each iteration
Iteration 1: v_1 = 22
Iteration 2: v_1 = 19
Iteration 3: v_1 = 16
Iteration 4: v_1 = 13
Iteration 5: v_1 = 10
Iteration 6: v_1 = 8
When the value of v_1 is 10 a new record is inserted into table. (Total records as of now: 23)
8. Last for loop iterates from 2 to 48 and inserts 51 records into table tab1 and four extra records when the value of i = 4 OR i = 16 OR i= 32 OR i = 48.
9. As of now, total records in table tab1 is 74 (23 + 51) records.
10. Five individual insert statements inserts 5 more records in to table tab1.
11. As a total, 79 rows will be inserted into the tab1 table after the following PL/SQL block has been executed successfully.
Hence correct option is (E - 79)
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.