facebook google plus twitter
Webucator's Free Oracle PL/SQL Tutorial

Lesson: Within the Block

Welcome to our free Oracle PL/SQL tutorial. This tutorial is based on Webucator's Oracle PL/SQL Training course.

PL/SQL - by definition - is a procedural language. It is specifically a procedural extension to SQL. A procedural (or imperative) language is used to specify steps to be taken to reach a desired state. Often the steps involved differ based on the values of the variables in the program. PL/SQL includes several control structures to vary the execution flow. Conditional statements are used to differentiate the execution path taken (or steps taken to reach the desired state) based upon the value of one or more variables. Iterative processing (or looping) is used to represent repeated steps. Conditional statements and loops can be nested arbitrarily to produce many different possible results of running the same program.

Lesson Goals

  • Learn about variable assignment (review)
  • Develop logic within PL/SQL program blocks
  • Learn about conditional processing
  • Learn about iterative processing

Conditional Processing

The IF Statement

There are a number of variations of the IF statement available. The simplest involves code that only executes if a condition is true (IF). More complex variations allow you to indicate alternative conditions to test (ELSIF) and a default behavior to take if none of the conditions tested evaluates to true (ELSE). The IF statement provides similar functionality to the SQL DECODE function and the SQL CASE expression.

Code Sample:

Within-the-Block/Demos/if_demo.sql
BEGIN
  dbms_output.put_line('Starting if_demo.sql...');
    
  IF 1=1 THEN
    dbms_output.put_line('In first IF block');
  END IF;
  
  IF TRUE THEN
    dbms_output.put_line('In second IF block');
  END IF;

  IF 1=0 THEN
    dbms_output.put_line('In third IF block');
  END IF;

  IF NOT 1=0 THEN
    dbms_output.put_line('In fourth IF block');
  END IF;

  IF 'a'='a' THEN
    dbms_output.put_line('In fifth IF block');
  END IF;

  IF 'A'='a' THEN
    dbms_output.put_line('In sixth IF block');
  END IF;
 
  dbms_output.put_line('Ending...');
END;

Code Explanation

The most common Boolean test is for equality. The = sign is used to test for equality, while the := combination of characters is used for variable assignment. There is a distinct PL/SQL Boolean type, so TRUE and FALSE represent true and false (not 0 and 1 as in some other languages). If an expression evaluates to false, the block is skipped. The NOT key word changes a false expression to true and vice versa. Besides numbers and Boolean values, character comparisons are also possible (as well as other types). Case sensitivity does matter in the context of a string or character data in a table when testing for equality.

Code Sample:

Within-the-Block/Demos/if_else_demo.sql
DECLARE
  -- Change the value of v_number to see the execution path change
  v_number NUMBER := 1;
BEGIN
  dbms_output.put_line('Starting if_else_demo.sql...');
  
  IF v_number=1 THEN
    dbms_output.put_line('In IF block');
  ELSE
    dbms_output.put_line('In ELSE block');
  END IF;
 
  dbms_output.put_line('Ending...');
END;

Code Explanation

In an IF/ELSE construct, any expression that does not evaluate to TRUE (resulting in the execution of the code in the IF block) will result in the execution of code in the ELSE block.

Code Sample:

Within-the-Block/Demos/if_elsif_demo.sql
DECLARE
  -- Change the value of v_number to see the execution path change
  v_number NUMBER := 1;
BEGIN
  dbms_output.put_line('Starting if_elsif_demo.sql...');
  
  IF v_number=1 THEN
    dbms_output.put_line('In IF block');
  ELSIF v_number=2 THEN
    dbms_output.put_line('In ELSIF block');
  END IF;
 
  dbms_output.put_line('Ending...');
END;

Code Explanation

Any number of ELSIF blocks can be included. These must include an expression to be evaluated (as in a simple IF clause).

Code Sample:

Within-the-Block/Demos/if_elsif_else_demo.sql
DECLARE
  -- Change the value of v_number to see the execution path change
  v_number NUMBER := 1;
BEGIN
  dbms_output.put_line('Starting if_elsif_else_demo.sql...');
  
  IF v_number=1 THEN
    dbms_output.put_line('In IF block');
  ELSIF v_number=2 THEN
    dbms_output.put_line('In ELSIF block');
  ELSE
    dbms_output.put_line('In ELSE block');
  END IF;

  dbms_output.put_line('Ending...');
END;

Code Explanation

IF, ELSIF and ELSE can be used together to guarantee that one of the available blocks will be executed.

Boolean expressions can also be joined together using AND and OR keywords. In some cases, the use of these keywords can reduce the need for ELSIF conditions. Although all conditional scenarios can be addressed using an IF statement in combination with ELSIF and ELSE, the ANSI/ISO CASE statement provides an efficient and readable alternative.

The CASE Statement

With the release of Oracle 9i, Oracle added support for the CASE statement and CASE expression in PL/SQL. Because both the CASE statement and CASE expression come in the same two flavors only the CASE statement is covered here. A simple CASE statement, also known as a selected CASE, begins with the keyword CASE followed by an arbitrarily complex selector expression that can evaluate to any scalar PL/SQL data type (and a few other types as well). A search CASE statement also begins with the keyword CASE but is not followed by a selector expression. Instead, each WHEN clause contain conditions that evaluate to Boolean values.

Code Sample:

Within-the-Block/Demos/case_demo.sql
DECLARE
  -- Change the value of v_number to see the execution path change
  v_number NUMBER := 1;
BEGIN
  dbms_output.put_line('Starting case_demo.sql...');
  -- DBMS_OUTPUT.PUT - no new line  
  -- Selected/simple case statement:
  CASE v_number
    WHEN 1 THEN dbms_output.put('ONE');
    WHEN 2 THEN dbms_output.put('TWO');
    ELSE dbms_output.put('ANOTHER NUMBER');
  END CASE;

  -- Searched case statement:
  CASE 
    WHEN v_number < 2 
      THEN dbms_output.put_line(' is less than 2.');
    WHEN v_number >= 2
      THEN dbms_output.put_line(' is greater than or equal to 2.');
  END CASE;

  dbms_output.put_line('Ending...');
END;

Code Explanation

A CASE statement can be used in a similar manner as the IF/ELSIF/ELSE statement. In the first example a single selector expression is evaluated (simple case), in the second example, different expressions are evaluated (search case).

The following table summarizes the differences between a simple CASE and search CASE statement. Image of PL/SQL Subprogram Characteristics

Iterative Processing

PL/SQL includes several iterative control structures. The basic LOOP, WHILE LOOP, FOR LOOP and CURSOR FOR LOOP provide the ability to iterate. Several commands can be used to interrupt a loop (EXIT WHEN, GOTO, executing a RETURN from the subprogram, raising an exception). Code can become difficult to read if you have deeply nested loops. Loops (as well as PL/SQL blocks) can be labeled using an undeclared identifier enclosed by double angle brackets.

The LOOP Statement

A simple, or indefinite, loop will continue until an EXIT or RETURN is encountered or an EXIT WHEN statement is evaluated to TRUE.

Code Sample:

Within-the-Block/Demos/loop.sql
DECLARE
  v_number NUMBER := 1;
BEGIN
  dbms_output.put_line('Starting for_loop.sql...');
  
  LOOP
    EXIT WHEN v_number > 10; 
    dbms_output.put_line('...' || v_number);     
    v_number := v_number + 1;    
    
  END LOOP;
 
  dbms_output.put_line('Ending...');
END;

Code Explanation

In this example the loop continues until the variable v_number is evaluated to be equal to ten. At that time the control passes to the DBMS_OUTPUT.PUT_LINE statement just before the END keyword.

The WHILE Statement

A WHILE loop continues until the Boolean expression specified beside the WHILE keyword evaluates to false.

Code Sample:

Within-the-Block/Demos/while_loop.sql
DECLARE
  -- Change the value of v_number to see the execution path change
  v_number NUMBER := 1;
BEGIN
  dbms_output.put_line('Starting while_loop.sql...');
   
  WHILE v_number <= 10 LOOP
    dbms_output.put_line('...' || v_number);  
    v_number := v_number + 1;
  END LOOP;
   
  dbms_output.put_line('Ending...');
END;

Code Explanation

In this example, the WHILE loop ceases when v_number is evaluated to be greater than ten.

The FOR Statement

A FOR loop iterates over an integer range specified by the FOR keyword. By default the loop proceeds upward. It can be modified to iterate downward using the REVERSE keyword.

Code Sample:

Within-the-Block/Demos/for_loop.sql
declare
  i number := 100;
BEGIN
  dbms_output.put_line('Starting for_loop.sql...');

  FOR i IN 1..10 LOOP
    dbms_output.put_line('...' || i);
  END LOOP;

  dbms_output.put_line('i = ' || i);
  dbms_output.put_line('Ending...');
END;

Code Explanation

The local variable (i) that increments through each loop iterations implicitly declared and therefore cannot appear in the DECLARE clause. It also cannot be used as the target of an explicit assignment within the loop (or an error will occur). And of course it cannot be referenced outside of the loop.

A Cursor For Loop is used to retrieve each row of a result set returned by a query into a record. It is discussed in a separate lesson.

Salary Classification using Conditional Statements

Duration: 10 to 15 minutes.
  1. Start with the program displayed below that prints out a person's name, salary and the current date (using simple variable assignment).
  2. Add a conditional logic to display "low" if the salary is less than 3000.
  3. Add additional code so that "medium" is displayed if the salary is greater than or equal to 3000 or less than 10000.
  4. Make additional modifications so that "high" is displayed if the salary is greater than or equal to 10000. (At this point, either low, medium, or high will be displayed regardless of what valid numeric value is assigned to salary).
  5. Using different conditional logic, display "low","medium" or "high" depending on the salary value
  6. Using a third form of conditional logic, once again display "low","medium" or "high" depending on the salary value.

Code Sample:

Within-the-Block/Exercises/salary_classification_exercise.sql
DECLARE
  v_name VARCHAR2(45);
  v_current_date DATE;
  v_salary NUMBER;

BEGIN

  dbms_output.put_line('Date:   ' || v_current_date);
  dbms_output.put_line('Name:   ' || v_name);
  dbms_output.put_line('Salary: ' ||
                       trim(to_char(v_salary,'$999,999,999.00')));

END;

Code Explanation

Solution:

Within-the-Block/Solutions/salary_classification_solution.sql
DECLARE
  v_name VARCHAR2(45) := 'Larry Ellison';
  v_current_date DATE := SYSDATE;
  v_salary NUMBER     := 1.00;
  v_message VARCHAR2(10);
BEGIN

  dbms_output.put_line('Date:   ' || v_current_date);
  dbms_output.put_line('Name:   ' || v_name);
  dbms_output.put_line('Salary: ' ||
                       trim(to_char(v_salary,'$999,999,999.00')));
  
  -- Variation 1:  Using IF/ELSIF  
    dbms_output.put_line('Variation 1');
    -- display "low" if salary is less than 3000
    IF v_salary < 3000 THEN
      dbms_output.put_line('low');  
    -- if salary is greater than or equal to 3000 OR
    -- less than 10000 "medium" is displayed.   
    ELSIF v_salary >= 3000 AND v_salary < 10000 THEN 
      dbms_output.put_line('medium');	
    -- "high" is displayed if salary is
    -- greater than or equal to 10000.   
    ELSIF v_salary >= 10000 THEN 
      dbms_output.put_line('high');
    END IF;
  
  -- Variation 2:  Using searched case STATEMENT with else
    dbms_output.put_line('Variation 2');
    CASE
      WHEN v_salary < 3000
        THEN dbms_output.put_line('low');  
      WHEN v_salary >= 3000 AND v_salary < 10000
        THEN dbms_output.put_line('medium');	
      ELSE
        dbms_output.put_line('high');
    END CASE;
  
  -- Variation 3: Using searched case EXPRESSION with else:
  --              Assigning result of case to a variable
    dbms_output.put_line('Variation 3');
    v_message :=  CASE
                    WHEN v_salary < 3000 THEN 'low'  
                    WHEN v_salary >= 3000 AND v_salary < 10000 
                      THEN 'medium'
                    ELSE 'high'
                  END;
    dbms_output.put_line(v_message);
  
END;

Code Explanation

Salary Increases Using Loops

Duration: 10 to 15 minutes.
  1. Start with the program that prints out person's name, salary and the current date (using simple variable assignment).
  2. Add a loop that displays the numbers one through ten. This number will represent the beginning of a series of lines that each represent one year.
  3. Add code to display the date incremented by one year and name beside each of the numbers one through ten. (Review the use of the ADD_MONTHS if needed).
  4. Add code that increments the salary by 5% per year (Compound interest)

Code Sample:

Within-the-Block/Exercises/salary_increase_exercise.sql
DECLARE
  v_name VARCHAR2(45);
  v_current_date DATE;
  v_salary NUMBER;
  
BEGIN

  dbms_output.put(v_name);
  dbms_output.put(' ' ||
                  trim(to_char(v_salary,'$999,999,999.00')));
  dbms_output.put_line(' ' || v_current_date);
  
END;

Code Explanation

Solution:

Within-the-Block/Solutions/salary_increase_solution.sql
DECLARE
  v_name VARCHAR2(45) := 'Larry Ellison';
  v_current_date DATE := current_date;
  v_salary NUMBER     := 1.00;  
BEGIN

  FOR i IN 1..10 LOOP
    
    dbms_output.put(lpad(i,2));
    dbms_output.put(' ' || v_name);
    dbms_output.put(' ' ||
                    trim(to_char(v_salary,'$999,999,999.00')));
    dbms_output.put_line(' ' || v_current_date);
 
    v_current_date := add_months(v_current_date,12);
    v_salary := v_salary + (v_salary * (i * .05));
    
  END LOOP;
  
END;

Code Explanation