[bbk4981]第08集 - Chapter 05- Writing Control Structures(00)

Objectives

After completing this lesson,you should be able to do the following:

• Identify the uses and types of control structures
• Construct an IF statement
• Use CASE statements and CASE expressions
• Construct and identify loop statements
• Use guidelines when using conditional control structures

PL/SQL中，要区分statement和expressions.

Agenda

• Using IF statements
• Using CASE statements and CASE expressions
• Constructing and identifying loop statements

Controlling Flow of Execution

IF Statements

Syntax:

IF condition    THEN
        statements;
[ELSIF condition THEN
        statements;]
[ELSE
        statements;]
END IF;

• The condition is a Boolean variable,constant,or expression that evaluates to TRUE,FALSE,or NULL.
• If condition evaluates to TRUE,the executable statements found after the TEHN keyword and before the matching END IF statement are executed.
• If condition evaluates to FALSE or NULL,those statements are not executed.

Simple IF Statement

DECLARE
        v_myage NUMBER :=31;
BEGIN
        IF v_myage < 11 THEN
                DBMS_OUTPUT.PUT_LINE('I am a child!');
        ELSIF v_myage > 18 THEN
                DBMS_OUTPUT.PUT_LINE('I am a man!');

        END IF;
END;

/

DECLARE
        v_myage NUMBER := 31;
BEGIN
        IF v_myage < 11 THEN
                DBMS_OUTPUT.PUT_LINE('I am a child!');
        ELSIF v_myage < 20 THEN
                DBMS_OUTPUT.PUT_LINE('I am yung!');

        ELSIF v_myage < 30 THEN
                DBMS_OUTPUT.PUT_LINE('I am in my twenties!');
        ELSIF v_myage < 40 THEN
                DBMS_OUTPUT.PUT_LINE('I am in my thirties!');
        ELSE
                DBMS_OUTPUT.PUT_LINE('I am alwarys yung!');
        END IF;
END;

/

DECLARE
        v_myage NUMBER;
BEGIN

        IF v_myage < 11 THEN
                DBMS_OUTPUT.PUT_LINE('I am a child!');
        ELSIF v_myage < 40 THEN
                DBMS_OUTPUT.PUT_LINE('I am in my thirty!');
        ELSIF v_myage IS NULL THEN
                DBMS_OUTPUT.PUT_LINE('I am a NULL value!');
        END IF;
END;

/

Simple CASE Statement

DECLARE
        v_myage NUMBER := 31;
BEGIN
        CASE
                WHEN v_myage< 11 THEN DBMS_OUTPUT.PUT_LINE('00至10岁之间');
                WHEN v_myage< 21 THEN DBMS_OUTPUT.PUT_LINE('11至20岁之间');
                WHEN v_myage< 31 THEN DBMS_OUTPUT.PUT_LINE('20至30岁之间');
                WHEN v_myage< 41 THEN DBMS_OUTPUT.PUT_LINE('30至40岁之间');
                ELSE DBMS_OUTPUT.PUT_LINE('超过40岁了，属于中年人!');
        END CASE;
END;

/

Avoiding IF Syntax Gotchas

keep in mind these points about IF statement syntax:

• Always match up an IF with an END IF
• You must have a space between the keywords END and IF
• The ELSIF keyword should not have an embedded "E"
• Place a semiconlon(;) only after the END IF keywords

CASE Expressions

A CASE expression selects a result and returns it.

To select the result,the CASE expression uses expressions.The value returned by these expressions is used to select one of several alternatives.

CASE selector
        WHEN expression1 THEN result1
        WHEN expression2 THEN result2
        ...
        WHEN expressionN THEN resultN
        [ELSIF resultN+1]
END;

CASE Expressions:Example

Select CASE expression

SET VERIFY OFF
DECLARE
        v_grade CHAR(1) := UPPER('&grade');
        v_appraisal VARCHAR2(20);
BEGIN
        v_appraisal :=
                CASE v_grade
                        WHEN 'A' THEN 'Excellent'
                        WHEN 'B' THEN 'Very Good'
                        WHEN 'C' THEN 'Good'
                        WHEN 'D' THEN 'No such grade'
                END;

        DBMS_OUTPUT.PUT_LINE('Grade:' || v_grade || ',Appraisal ' || v_appraisal);

END;

/

Searched Case Expression

SET VERIFY OFF
DECLARE
        v_grade CHAR(1) := UPPER('&grade');
        v_appraisal VARCHAR2(20);
BEGIN
        v_appraisal := CASE
                WHEN v_grade = 'A' THEN 'Excellent'
                WHEN v_grade IN ('B','C') THEN 'Good'
                ELSE 'No such grade'
        END;

        DBMS_OUTPUT.PUT_LINE('Grade:' || v_grade || ',Appraisal ' || v_appraisal);
END;

/

CASE STATEMENT

所谓Statement就是一个独立的语句，不能再进行赋值了；

CASE STATEMENT:CASE ... END CASE;

CASE EXPRESSION:CASE ... END;

DECLARE
        opt NUMBER := &optor;

BEGIN
        IF opt = 1 THEN DBMS_OUTPUT.PUT_LINE('true');
        END IF;
        CASE
                WHEN opt = 1 THEN DBMS_OUTPUT.PUT_LINE('Today is MON !');
                WHEN opt = 2 THEN DBMS_OUTPUT.PUT_LINE('Today is TUS !');
                WHEN opt = 3 THEN DBMS_OUTPUT.PUT_LINE('Today is WEN !');
                WHEN opt = 4 THEN DBMS_OUTPUT.PUT_LINE('Today is THU !');
                WHEN opt = 5 THEN DBMS_OUTPUT.PUT_LINE('Today is FRI !');
                WHEN opt = 6 THEN DBMS_OUTPUT.PUT_LINE('Today is SAT !');
                WHEN opt = 7 THEN DBMS_OUTPUT.PUT_LINE('Today is SUN !');
                ELSE DBMS_OUTPUT.PUT_LINE('No Day!');
        END CASE;


        CASE opt
                WHEN 1 THEN DBMS_OUTPUT.PUT_LINE('A');
                WHEN 2 THEN DBMS_OUTPUT.PUT_LINE('B');
                WHEN 3 THEN DBMS_OUTPUT.PUT_LINE('C');
                WHEN 4 THEN DBMS_OUTPUT.PUT_LINE('D');
                WHEN 5 THEN DBMS_OUTPUT.PUT_LINE('E');
                WHEN 6 THEN DBMS_OUTPUT.PUT_LINE('F');
                WHEN 7 THEN DBMS_OUTPUT.PUT_LINE('G');
                ELSE DBMS_OUTPUT.PUT_LINE('O');
        END CASE;
END;

/

Handing Nulls

When you are working with nulls,you can avoid some common mistakes by keeping in mind the following rules:

• Simple comparisions involving nulls always yield NULL.
• Applying the logical operator NOT to a null yields NULL.
• If the condition yields NULL in conditional control statements,its associated sequence of statements is not executed.