 |
|
Branching and Conditional Control
Oracle Tips by Burleson Consulting
|
The following Tip is from the
outstanding book "Oracle
PL/SQL Tuning: Expert Secrets for High Performance Programming" by
Dr. Tim Hall, Oracle ACE of the year, 2006:
The IF-THEN-ELSE and CASE statements allow
code to decide on the correct course of action for the current
circumstances. In the following example, the IF-THEN-ELSE statement
is used to decide whether today is a weekend day.
SET
SERVEROUTPUT ON
DECLARE
l_day VARCHAR2(10);
BEGIN
l_day := TRIM(TO_CHAR(SYSDATE, 'DAY'));
IF l_day IN ('SATURDAY', 'SUNDAY') THEN
DBMS_OUTPUT.put_line('It''s the weekend!');
ELSE
DBMS_OUTPUT.put_line('It''s not the weekend yet!');
END IF;
END;
/
First, the expression between the IF and the
THEN is evaluated. If the expression equates to TRUE, the code
between the THEN and the ELSE is performed. If the expression equates
to FALSE, the code between the ELSE and the END IF is performed. The
IF-THEN-ELSE statement can be extended to cope with multiple decisions
by using the ELSIF keyword. The example below uses this extended form
to produce a different message for Saturday and Sunday.
Also see my notes on
elsif.
SET
SERVEROUTPUT ON
DECLARE
l_day VARCHAR2(10);
BEGIN
l_day := TRIM(TO_CHAR(SYSDATE, 'DAY'));
IF l_day =
'SATURDAY' THEN
DBMS_OUTPUT.put_line('The weekend has just started!');
ELSIF l_day = 'SUNDAY' THEN
DBMS_OUTPUT.put_line('The weekend is nearly over!');
ELSE
DBMS_OUTPUT.put_line('It''s not the weekend yet!');
END IF;
END;
/
SQL CASE expressions were introduced in the
later releases of Oracle 8i, but Oracle 9i included support for CASE
statements in PL/SQL for the first time. The CASE statement is the
natural replacement for large IF-THEN-ELSIF-ELSE statements. The
following code gives an example of a matched CASE statement.
SET
SERVEROUTPUT ON
DECLARE
l_day VARCHAR2(10);
BEGIN
l_day := TRIM(TO_CHAR(SYSDATE, 'DAY'));
CASE l_day
WHEN 'SATURDAY' THEN
DBMS_OUTPUT.put_line('The weekend has just started!');
WHEN 'SUNDAY' THEN
DBMS_OUTPUT.put_line('The weekend is nearly over!');
ELSE
DBMS_OUTPUT.put_line('It''s not the weekend yet!');
END CASE;
END;
/
The WHEN clauses of a matched CASE statement
simply state the value to be compared. If the value of the variable
specified after the CASE keyword matches this comparison value, the
code after the THEN keyword is performed.
A searched CASE statement has a slightly
different format, with each WHEN clause containing a full expression,
as shown below.
SET
SERVEROUTPUT ON
DECLARE
l_day VARCHAR2(10);
BEGIN
l_day := TRIM(TO_CHAR(SYSDATE, 'DAY'));
CASE
WHEN l_day = 'SATURDAY' THEN
DBMS_OUTPUT.put_line('The weekend has just started!');
WHEN l_day = 'SUNDAY' THEN
DBMS_OUTPUT.put_line('The weekend is nearly over!');
ELSE
DBMS_OUTPUT.put_line('It''s not the weekend yet!');
END CASE;
END;
/
Looping Statements in PL/SQL
Loops allow sections of code to be processed
multiple times. In its most basic form, a loop consists of the LOOP
and END LOOP statement, but this form is of little use as the loop
will run forever.
BEGIN
LOOP
NULL;
END LOOP;
END;
/
Typically it would be expected to define an
end condition for the loop using the EXIT WHEN statement along with a
Boolean expression. When the expression equates to TRUE, the loop
stops. The example below uses this syntax to print out numbers from 1
to 5.
SET
SERVEROUTPUT ON
DECLARE
i NUMBER := 1;
BEGIN
LOOP
EXIT WHEN i > 5;
DBMS_OUTPUT.put_line(i);
i := i + 1;
END LOOP;
END;
/
The placement of the EXIT WHEN statement can
affect the processing inside the loop. For example, placing it at the
start of the loop means the code within the loop may be executed “0 to
many” times, like a while-do loop in other languages. Placing the
EXIT WHEN at the end of the loop means the code within the loop may be
executed “1 to many” times, like a do-while loop in other languages.
The FOR-LOOP statement allows code within the
loop to be repeated a specified number of times based on the lower and
upper bounds specified in the statement. The example below shows how
the previous example could be recoded to use a FOR-LOOP statement.
SET
SERVEROUTPUT ON
BEGIN
FOR i IN 1 .. 5 LOOP
DBMS_OUTPUT.put_line(i);
END LOOP;
END;
/
The WHILE-LOOP statement allows code within
the loop to be repeated until a specified expression equates to TRUE.
The following example shows how the previous examples can be recoded
to use a WHILE-LOOP statement.
SET
SERVEROUTPUT ON
DECLARE
i NUMBER := 1;
BEGIN
WHILE i <= 5 LOOP
DBMS_OUTPUT.put_line(i);
i := i + 1;
END LOOP;
END;
/
In addition to these loops, a special cursor
FOR-LOOP is available as shown earlier.
GOTO in PL/SQL
The GOTO statement allows a program to branch
unconditionally to a predefined label. The following example uses the
GOTO statement to repeat the functionality of the examples in the
previous section.
SET
SERVEROUTPUT ON
DECLARE
i NUMBER := 1;
BEGIN
LOOP
IF i > 5 THEN
GOTO exit_from_loop;
END IF;
DBMS_OUTPUT.put_line(i);
i := i + 1;
END LOOP;
<<
exit_from_loop >>
NULL;
END;
/
In this example, the GOTO has been made
conditional by surrounding it with an IF statement. When the GOTO is
called, the program execution immediately jumps to the appropriate
label defined using double-angled brackets.
|