Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

EnterpriseDB: Control Structures
Oracle Tips by Burleson
 

Conditional structures control flow within a program.  When you need to conditionally execute code or when you need to repeat code, you use a control structure.

SPL, like most robust programming languages, supports several flavors of control.  Conditional structures, which allow you to chose whether or not to execute code, include the IF and CASE statements.  A looping structure allows you to repeatedly execute statements and includes LOOP, WHILE and FOR.

Conditionals

SPL provides two conditional flow control statements:  IF and CASE.  Case is further broken out into the CASE statement and the CASE expression.

IF

The simplest conditional is the IF statement.  Every language out there that is worth using has an IF statement.  An IF statement evaluates an expression and if it is TRUE, executes a block of code.  You may optionally include a FALSE block to execute when the condition is FALSE (or evaluates to NULL). 

The structure of the IF statement is:

IF <expression is TRUE>
THEN
  <executable code>
[ELSE  -- False Block
  <executable code>]
END IF;

IF statements may be continued with ELSIF so that you can check multiple conditions.  It is considered best practice to use the CASE statement when you need to check multiple conditions.  The syntax for ELSIF is:

IF <expression is TRUE>
THEN
  <executable code>
ELSIF <expression is TRUE>
THEN
  <executable code>
ELSIF <expression is TRUE>
THEN
  <executable code>
.
.
.
[ELSE  -- False Block
  <executable code>]
END IF;

You may also nest an IF statement inside of another IF statement:

IF <expression is TRUE>
THEN
  IF <a different expression is TRUE>
  THEN
    <executable code>
  END IF;
  <executable code>
[ELSE  -- False Block

 
<executable code>]
END IF;

Let's take a look at an example:

DECLARE

  v_var1 NUMBER := 0;
  v_var2 NUMBER := 1;
BEGIN 

  IF v_var1 = v_var2               -- IF 1
  THEN
    DBMS_OUTPUT.PUT_LINE( 'v_var1 = v_var2');
  ELSE
    DBMS_OUTPUT.PUT_LINE( 'v_var1 != v_var2');
  END IF;

 
IF v_var1 > 5                    -- IF 2
  THEN
    DBMS_OUTPUT.PUT_LINE( 'v_var1 > 5');
  ELSIF v_var1 = 5
  THEN
    DBMS_OUTPUT.PUT_LINE( 'v_var1 = 5');
  ELSIF v_var1 < 5
  THEN
    DBMS_OUTPUT.PUT_LINE( 'v_var1 < 5');
  ELSE

  
  DBMS_OUTPUT.PUT_LINE( 'v_var1 = UNKNOWN or NULL');
  END IF; 

  v_var1 := v_var2; 

  IF v_var1 = v_var2                 -- IF 3  THEN

    DBMS_OUTPUT.PUT_LINE( 'v_var1 = v_var2');
  ELSE
    DBMS_OUTPUT.PUT_LINE( 'v_var1 != v_var2');
  END IF;

END;

INFO:  v_var1 != v_var2
INFO:  v_var1 < 5
INFO:  v_var1 = v_var2

IF 2 would better be replaced by what is called a case statement.  A case works like an IF statement but is meant to check multiple expressions and to execute the code on the one that is the first match in the list.

 

             
This is an excerpt from the book "EnterpriseDB: The Definitive Reference" by Rampant TechPress.

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter