ORACLE PL/SQL Instance refinement fifth conditional control: Case statement

5.1 Case Statement

1. The case statement has the following structure

Case SELECTOR

When EXPRESSION 1 then STATEMENT 1;

When Expresssion 2 then STATEMENT 2;

........

When EXPRESSION n then STATEMENT N;

ELSE STATEMENT n+1;

END case;

The reserved word case identifies the beginning of the case statement. The selector determines which when clause should be executed. Each when clause contains an expression and one or more executable statements associated with it. The ELSE clause is optional. His way of working is very similar to the ELSE clause used in the IF-THEN-ELSE statement. The end case is a reserved word that identifies the end of a case statement. Note that the selector is calculated only once. And the When clause is evaluated sequentially. The value of an expression is compared to the value of the selector. If the two values are equal. Then the statement associated with the specific when clause is executed, and the subsequent when clause is not evaluated. If no expression matches the value of the selector, the ELSE clause is selected and executed.

2. Search-Type Case statements

The search case statement has a search condition that can produce a Boolean (True,false,null), and when a particular search condition evaluates to True, the combination of statements associated with that condition is executed. The syntax for a search-type case statement is as follows:

Case

When SEARCH condidtion 1 then STATEMENT 1;

When SEARCH CONDIDTION2 then STATEMENT 2;

........

When SEARCH Condidtionn then STATEMENT N;

ELSE STATEMENT n+1;

END case;

When the search condition evaluates to true, execution control passes the statement associated with it. If none of the search conditions produce true, the statements related to the ELSE clause are executed. Note that the ELSE clause is optional.

3. The difference between a case statement and a search case statement

The search-type case statement does not have a selector (so to speak, because it produces a boolen type of search condition, then we give him a function expression such as mod (v_num,2) =0 judgment is true FALSE OR NULL, and the case statement needs to pass the selector in, Explanation: (V_mum_flag: = MOD (v_num,2), so you have to add V_mum_flag this selector after the case and then Judge selector 0 (when 0 then)

Case statement

Case V_mum_flag

When 0 Then

Statemen

Search-Type Case statement

Case

When MOD (v_num,2) =0 Then

Statemen.

5.2 Case Expression

The case expression returns a value that is then assigned to a variable. is implemented using the copy operator: =

5.3 Nullif and COALESCE functions

1. Nullif function

The Nullif function compares two expressions. If the two are the same, the function returns NULL, otherwise, the value of the first expression is returned.

NULLIF structure:

Nullif (expression1, expression2)

If expression1 equals expression2, NULLIF returns NULL. If expression1 does not equal expresson2,nullif function returns expression1. Note The Nullif function has the opposite effect as the NVL function. NVL If the first expression is a NULL,NVL function returns the second expression. Returns the first expression if the first expression is not NULL.NVL

The Nullif function is equivalent to the following case expression

Case

When EXPRESSION1 = EXPRESSION2 then NULL

ELSE EXPRESSION1

END

There is a limit to the NULLIF function: You cannot give the literal value null to EXPRESSION1,

2. Coalesce[,k??? ' Les] Functions

The COALESCE function compares each expression in the expression list to null and returns the value of the first non-null expression. The COALESCE function has the following structure

COALESCE (Expression1,expression2,........., expressionn)

If expression1 equals null, EXPRESSIOIN2 is computed. If the expression2 evaluates to NULL, the function returns EXPRESSION2 and returns null if the result of all expressions is null.

5.3.1 Nullif function

ORACLE PL/SQL Instance refinement fifth conditional control: Case statement