What is the usage of if in oracle

Usage: 1. The "IF THEN END IF" statement determines whether the statement is executed under a single condition; 2. The "IF THEN ELSE END IF" statement determines whether the statement is executed under two conditions; 3. "IF THEN LESIF ELSE END IF" statement determines whether multiple conditional statements are executed.

The operating environment of this tutorial: Windows 10 system, Oracle 11g version, Dell G3 computer.

What is the usage of if in oracle

IF statement can be used in 3 ways, as shown in the following table:

##1.1 IF-THEN combination

The common format of IF-THEN syntax is as follows:

IF condition
THEN
      ...执行的语句...
END IF;

Among them, condition is a Boolean type variable or constant, and its value may have three situations: TRUE ,FALSE,NULL

For example, there is such a condition IF sal > 1000; dbms_output.put_line('null');END IF;, this condition may return TRUE, FALSE, but when the value of sal is NULL When, that is: IF NULL > 1000, the returned value is NULL

In order to avoid the generation of NULL values ​​in the above situation, we can use IF sal > 1000 OR sal is null; dbms_output.put_line('null' );END IF; to avoid it, you can also use the null value processing function

1.2 IF-THEN-ELSE combination

The syntax format is as follows:

IF condition
THEN
     ... TRUE sequence of executeable statements ... 
ELSE
    ... FALSE/NULL sequence of executeable statements ...  
END IF;

1.3 IF-THEN-ELSIF combination

Please pay special attention: this is ELSIF not ELSEIF, and writing ELSE IF will not work.

The syntax format is as follows:

IF condition-1
THEN
      statements-1
ELSIF condition-N
THEN
      statements-N
[ELSE
      else-statements]
END IF;

2. Short-circuit evaluation

PL/SQL uses the short-circuit evaluation method, which means that PL/SQL does not All expressions in an IF statement need to be evaluated. For example, when evaluating the expression in the IF statement below, if the first condition is FALSE or NULL, PL/SQL will stop evaluating the expression and immediately jump to the ELSE branch;

IF condition1 AND condition2
THEN
      ...
ELSE
      ...
END IF;

According to the short-circuit evaluation principle, when there are multiple conditions, the conditions that consume a lot of CPU and memory resources must be placed at the end of the entire condition set.

Recommended tutorial: "

Oracle Video Tutorial"

The above is the detailed content of What is the usage of if in oracle. For more information, please follow other related articles on the PHP Chinese website!