Conditional Control: IF & case statements

The IF statement has the following basic forms:

I. If-then statement

IfConditionThen
Statement 1;
...
Statement N;
End if;

II. If-then-else statement

IfConditionThen
Statement 1;
Else
Statement 2;
End if;

Iii. elslf statements

IfCondition 1Then
Statement 1;
ElsifCondition 2Then
Statement 2;
ElsifCondition 3Then
Statement 3;
...
Else
Statement N;--> In an elsif statement, the else clause is not required.
End if

Next, let's take an example: Use the sysdate function to determine whether today is a weekend. If it is a weekend, determine whether it is a morning or afternoon. Whether it is a weekend or not, the last day is displayed.

Note: The nested use of if and in the elsif statement, the else clause is not required.

Declare v_day varchar2 (10); v_time number (2); begin v_day: = rtrim (to_char (sysdate, 'day'); v_time: = to_char (sysdate, 'hh24'); If v_day in ('saturday', 'sunday ') then --> The following nested elsif statement if v_time> = 6 and v_time <12 then dbms_output.put_line ('good moring '); elsif v_time> = 12 and v_time <= 18 then dbms_output.put_line ('good afternoon'); end if; dbms_output.put_line ('the time is: '| to_char (sysdate, 'yyyy-mm-dd hh24: MI: ss'); end;

Case statement

The case statement has two forms: Case and search case.

I. Case statements

Case Selector
When expression 1 then Statement 1;
When expression 2 then Statement 2;
...
When expression n then statement N;
Else statement n + 1;
End case;

The following is an example: used to judge the parity of the input value.

DECLARE  v_num NUMBER := &user_num;  v_num_flag NUMBER;BEGIN  v_num_flag := MOD(v_num,2);  CASE v_num_flag    WHEN 0 THEN      DBMS_OUTPUT.PUT_LINE(v_num||‘ is even number‘);    ELSE      DBMS_OUTPUT.PUT_LINE(v_num||‘ is odd number‘);  END CASE;END;

Ii. Search-type case statement

Case
When Search Condition 1 then Statement 1;
When Search Condition 2 then Statement 2;
...
When search condition n then statement N;
Else statement n + 1;
End case;

The following example is rewritten using the search-type case statement --> Note: The v_num_flag variable is not declared and can be determined directly in the when.

DECLARE  v_num NUMBER := &user_num;BEGIN  CASE    WHEN MOD(v_num,2)=0 THEN      DBMS_OUTPUT.PUT_LINE(v_num||‘ is even number‘);    ELSE      DBMS_OUTPUT.PUT_LINE(v_num||‘ is odd number‘);  END CASE;END;

 

Case expression

If the case expression is used, the preceding case statement and the search case statement can be rewritten as follows:

1. corresponding case statement

DECLARE  v_num NUMBER := &user_num;  v_num_flag number;  v_result varchar2(30);BEGIN  v_num_flag := MOD(v_num,2);  v_result :=    CASE v_num_flag       WHEN 0 THEN          v_num||‘ is even number‘       ELSE          v_num ||‘ is odd number‘    END;  DBMS_OUTPUT.PUT_LINE(v_result);END;

2. Corresponding search-type case statement

DECLARE  v_num NUMBER := &user_num;  v_result varchar2(30);BEGIN  v_result :=    CASE       WHEN MOD(v_num,2) =  0 THEN          v_num||‘ is even number‘       ELSE          v_num ||‘ is odd number‘    END;  DBMS_OUTPUT.PUT_LINE(v_result);END;

Note: syntax differences between case statements and case expressions: In case statements, executable statements in the when and else clauses end with semicolons. In a case expression, the end of the expressions contained in the when and else clauses is not a semicolon. The case statement ends with end case, and the case expression ends with end.