PL/SQL - Using IF statements

Speaker Notes

Slide 1:

This presentation will deal with IF statements used in PL/SQL blocks.

Slide 2:

CREATE TABLE...AS SELECT allows for a quick copy to mess up while preserving the original.

Slide 3:

SET VERIFY OFF means that when the user is prompted for the pay identification number, they will not see the old and new returns.

%TYPE means that the type will be determined by the column definition and table specified.

The syntax is:

IF <condition> THEN
    <processing>;
END IF;

Note the required THEN and the semi-colon after the processing statements and after the termination of the IF with the END IF.

Slide 4:

Note again that because of SET VERIFY OFF, the code for old and new do not appear.

Richard Jones (5555) used to make 50000, he now makes 52500 which is 5% more.

Note that this only works for one record at a time. Also note that if the jobcode was not CI, no change would have been made.

Slide 5:

The code now calls for the salary that is read to go into the variable v_salary. If the jobcode = CI then the salary is recalculated and put back in the variable v_salary. If the jobcode is not = CI no change is made and the salary stored in v_salary is used in the update.

Notice in this code an update is made regardless of whether or not the salary was changed. On the next slide, we will see how to change this.

Slide 6:

In this example, the UPDATE will only be done if v_code = 'CI'.

This is far more efficient that updating every time.

Slide 7:

This is the output from the code on the previous page. Since 1111 was entered, that record was checked in the IF. Since the jobcode = CI, the calculation was done and the update was made to the salary.

Slide 8:

This shows the logic flowchart for the simple IF statement.

IF the statement is True (Y) then the new salary is calculated and update is done. If the IF statement is False (N) then no action is taken.

Note that this flowchart matches the code where the update is included in the IF.

Slide 9:

Structure:

IF <condition> THEN
    <processing if true>;
ELSE
    <processing if false>;
END IF;

Note the use of semi-colons after the true processing and after the false processing.

Slide 10:

In this example the ELSE was processed since the statement the record with pay_id = 2222 had a jobcode that was not = to CI.

IF v_jobcode = 'CI' THEN
    v_new_sal := v_new_sal * 1.05;
ELSE
    v_new_sal := v_new_sal * 1.02;
END IF;

Again the := is an assignment sign that assigns the results from the calculation on the right to the variable on the left.

Slide 11:

If the condition is true (Y), the new salary is calculated with a 5% increase. If the condition is false (N), the new salary is calculated with a 2% increase.

Slide 12:

This is an embedded if - an if within an if. The structure is:

IF <condition1 is true> THEN
    <processing if condition1 is true>;
ELSE
    IF <condition2 is true> THEN
        <processing if condition2 is true>
    ELSE
        <processing if condition2 is false>
    END IF;
END IF;

Slide 13:

The first if is not true because the jobcode is not CI.

The second if is true because the jobcode is IN.

Therefore the true statement for the second IF is executed and the salary is increased by 3%.

Slide 14:

The first condition was not true (jobcode was not = CI), so the else was taken and the inner if which was to be processed if the outer if was false is processed.

The inner condition asks if jobcode = IN. It does not so the else is taken and the salary is increased by 1%.

Slide 15:

This has an inner or imbedded if that is checked if the answer to the initial condition is false.

Notice the two ovals on the flowchart - each represents one of the END IF statements in the code. The first closes the inner or embedded IF, the second closes the outer IF.

Slide 16:

IF...ELSIF works like a case structure. If the first condition is true then the first processing is done. If the first condition is false, the the ELSIF condition is checked. If that is true the second processing is done. There can be numerous ELSIF. If there is to be processing that is done by anything that did not meet the criteria, it is coded in the final ELSE. The final ELSE is not needed if there isn't default processing. The entire IF is ended with the END IF.

Note that there is only one END IF with this structure.

Slide 17:

Since record 3333 has a job code of AP, the IF and the ELSIF are both false and processing falls to the ELSE default.

Slide 18:

In this example, the first condition testing for jobcode = CI was false. Processing went to the second condition at the ELSIF and since the jobcode = IN the condition was true and the processing was done.