Learning Objectives – after this chapter you will be able to:
- Write IF … THEN … ELSIF … ELSE … END IF with relational and logical operators.
- Use LOOP … END LOOP with EXIT and EXIT WHEN, nested loops and loop labels.
- Use WHILE loops and numeric FOR loops (including REVERSE).
- Use GOTO with labels and know its restrictions.
IF Statement
IF condition-1 THEN statements_set_1; [ELSIF condition-2 THEN statements_set_2;] ... [ELSE statements_set_3;] END IF;Conditions use relational operators: >, >=, <, <=, =, not equal (<>, !=, ~=, ^=), LIKE, BETWEEN … AND, IN and IS NULL, combined with AND (both true) and OR (either true).
Changing the Oracle course fee by number of students
declare
v_ns number(5);
v_fee courses.fee%type;
begin
select count(*) into v_ns from students
where bcode in ( select bcode from batches where ccode = 'ora' );
select fee into v_fee from courses where ccode = 'ora';
if v_ns > 100 then
v_fee := v_fee * 1.2; -- +20%
elsif v_ns > 50 then
v_fee := v_fee * 1.15; -- +15%
elsif v_ns > 10 then
v_fee := v_fee * 1.05; -- +5%
else
v_fee := v_fee * 0.90; -- -10%
end if;
update courses set fee = v_fee where ccode = 'ora';
commit;
end;- Conditions are checked in order; once one is true its statements run and the IF ends. ELSE runs if none is true.
- Every IF needs a matching END IF; ELSIF (spelled without an E) does not, and can appear only after an IF.
In the PDF’s version of this program the “> 10” branch repeats the 15% multiplier; the table in the PDF specifies +5%, which is used above.
LOOP, EXIT and EXIT WHEN
LOOP … END LOOP is the simplest loop and has no termination point – without EXIT it is infinite. EXIT leaves the loop immediately; EXIT WHEN condition leaves it only when the condition is true.
Numbers 1 to 10 with LOOP
declare
i number(2) := 1;
begin
loop
dbms_output.put_line(i);
i := i + 1;
exit when i > 10;
end loop;
end;Output
1 2 3 4 5 6 7 8 9 10
Nested loops and loop labels
A loop inside another loop runs completely for each iteration of the outer loop (e.g. multiplication tables of 1–5). A loop label lets EXIT leave an enclosing loop:
<<outerloop>>
loop
loop
exit outerloop when ... ; -- leaves the OUTER loop
end loop;
end loop;WHILE Loop
WHILE condition LOOP statements; END LOOP;The condition is checked at the beginning of each iteration; statements run as long as it is true, so they may not run at all.
declare
i number(2) := 1;
begin
while i <= 10 loop
dbms_output.put_line(i);
i := i + 1;
end loop;
end;Numeric FOR Loop
FOR counter IN [REVERSE] lowerrange .. upperrange LOOP statements; END LOOP;- The counter is set to lowerrange (to upperrange with REVERSE).
- If the counter is ≤ upperrange (≥ lowerrange with REVERSE) the statements run, otherwise the loop ends.
- The counter is incremented (decremented with REVERSE) by one and only one – the step cannot be changed.
- Step 2 is repeated. The ranges may be expressions.
FOR loop examples
begin
for i in reverse 1..10 loop
dbms_output.put_line(i); -- 10 down to 1
end loop;
end;
-- missing roll numbers between the lowest and highest
declare
v_minrollno students.rollno%type;
v_maxrollno students.rollno%type;
v_count number(2);
begin
select min(rollno), max(rollno) into v_minrollno, v_maxrollno from students;
for i in v_minrollno .. v_maxrollno loop
select count(*) into v_count from students where rollno = i;
if v_count = 0 then
dbms_output.put_line(i);
end if;
end loop;
end;GOTO Statement
GOTO label transfers control to a label (<<name>>). The label must be unique and must precede an executable statement or a PL/SQL block.
GOTO cannot
- branch into an IF statement
- branch into a LOOP
- branch into a sub-block
- branch out of a subprogram (procedure / function)
- branch from an exception handler into the current block
Key Takeaways
- IF … ELSIF … ELSE … END IF chooses among several actions.
- LOOP needs EXIT; WHILE tests first; FOR steps by exactly 1 (REVERSE counts down).
- Loop labels let EXIT leave outer loops; GOTO has strict branching restrictions.
Chapter 16 Notes (PDF)
ch16.pdfKeys: ← → page · + − zoom · F full screen
Question / Answer (15 questions)
Moderate-level questions based on the Chapter 16 notes (PDF). Click a question to see the answer.
Answer : IF condition-1 THEN statements_set_1; [ELSIF condition-2 THEN statements_set_2;] ... [ELSE statements_set_3;] END IF; – the statements of the first true condition are executed; if none is true, the ELSE statements are executed.
Answer : > (greater than), >= , < , <= , = (equal), the not-equal operators <>, !=, ~= and ^=, LIKE (pattern match), BETWEEN..AND (range), IN (list) and IS NULL.
Answer : With AND both conditions must be true for the entire condition to be true; with OR the entire condition is true if any one of the conditions is true, e.g. IF rate > 500 AND qty < 10 THEN.
Answer : Conditions are checked one after another; the IF statement terminates once a condition is true and its statements are executed. Every IF must have a matching END IF, but an ELSIF does not need its own END IF, and ELSIF can be used only after an IF.
Answer : It is the simplest looping structure that repeatedly executes a set of statements. It has no termination point, so unless it is terminated using EXIT it becomes an infinite loop.
Answer : EXIT used alone terminates the current loop as soon as it is executed (typically inside an IF). EXIT WHEN condition terminates the loop only when the given condition is satisfied, e.g. EXIT WHEN i > 10.
Answer : DECLARE i NUMBER(2) := 1; BEGIN LOOP DBMS_OUTPUT.PUT_LINE(i); i := i + 1; EXIT WHEN i > 10; END LOOP; END; – I is displayed, incremented, and the loop ends when I becomes greater than 10.
Answer : A loop placed within another loop. The inner loop is executed completely for each iteration of the outer loop – e.g. displaying multiplication tables up to 10 for numbers 1 to 5.
Answer : By assigning a label to the outer loop (<<outerloop>> before LOOP) and using the label with EXIT, e.g. EXIT outerloop WHEN condition, which terminates the specified enclosing loop instead of the current loop.
Answer : WHILE condition LOOP statements; END LOOP; executes the statements repeatedly as long as the condition is true. The condition is checked at the beginning of each iteration, and the loop terminates once the condition becomes false.
Answer : 1. The counter is set to the lower range. 2. If the counter is less than or equal to the upper range the statements are executed, otherwise the loop terminates. 3. The counter is incremented by one and only one. 4. Step 2 is repeated. The ranges may be expressions.
Answer : With REVERSE the counter is set to the upper range, statements are executed while the counter is greater than or equal to the lower range, and the counter is decremented by one each time – e.g. FOR i IN REVERSE 1..10 displays 10 down to 1.
Answer : No. The FOR loop counter is always incremented (or decremented with REVERSE) by one and only one; it is not possible to change the step value.
Answer : It gets the minimum and maximum roll numbers with MIN and MAX, runs a FOR loop from the minimum to the maximum, counts the students having the current roll number with SELECT COUNT(*), and displays the roll number when the count is 0.
Answer : GOTO transfers control to a named label (<<label>>) that must be unique and precede an executable statement or a block. It cannot branch into an IF statement, into a LOOP or into a sub-block, cannot branch out of a subprogram (procedure or function), and cannot branch from an exception handler into the current block.
MCQ Test : Check Your Understanding (5 questions)
Select an option and press Check Answer, or answer all questions and press Submit Test to see your score. Each answer comes with an explanation.
Bibliography/References
The chapter notes PDF is the course study material and is credited to its author below. The explanations, questions and MCQs on this page are original study aids written for this course from the topics covered in that PDF. The other works are cited for verification and further reading. Oracle, SQL*Plus and PL/SQL are trademarks of Oracle Corporation; other names belong to their respective owners.
- Srikanth, P. (n.d.). Oracle For Beginners (chapter notes – the PDF shown on each chapter page). Srikanth Technologies. http://www.srikanthtechnologies.com/
- Oracle Corporation. (n.d.). Oracle Database PL/SQL Language Reference, 19c. https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/
- Feuerstein, S., & Pribyl, B. (2014). Oracle PL/SQL Programming (6th ed.). O’Reilly Media.