TEC 5323 Advanced Database Technology

Discussion of Week 3

This week's discussion focuses on PL/SQL control structure, which is a major advances from SQL. The control structure will be useful when you create a trigger as a project assignment for this week.

 

Discussion Topics:

1. There are three types of major loop controls in PL/SQL including BASIC loop (Exercise 5.3, example 1, Class Notes: Chapter 5), WHILE loop (Exercise 5.3, example 2,) and FOR loop (Exercise 5.3, example 3).

2. Conditional control (IF...ELSIF...ELSE). Please note the word ELSIF does not have "E" in it. This is the only non-English-conforming syntax I knew in Oracle.

Questions:

1. How many times at least the BASIC loop and WHILE loop will execute? Why?

2. The following is a PL/SQL block:

BEGIN
FOR i IN 1..6 LOOP
IF i=2 OR i=3 THEN
null;
ELSE
INSERT INTO example(one)
VALUES (i);
END IF;
ROLLBACK;
END LOOP;
COMMIT;
END;
How many values will be inserted into the EXAMPLE table? Why? This is a question from Oracle Certified Professional (OCP) DBA exam.

School of Technology
College of Business & Applied Sciences
Eastern Illinois University