Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

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.

 

 

This is an excerpt from the bestselling book "Oracle PL/SQL Tuning: Expert Secrets for High Performance Programming" by Dr. Tim Hall, Oracle ACE of the year, 2006.

You can buy the book for only $23.95 (30%-off) when you buy directly from the publisher, and you also get instant access to the code depot of PL/SQL tuning scripts:


 

 
��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational