Oracle PL/SQL from if to then

In Oracle development, the conditional expressions between If and then are full of "art" and require us to discover and appreciate them.

Appreciation 1: Three-value Logic

If an expression contains a null value, what is the final result of the expression? For example, 2 <null. Null is a black box, and Oracle does not know what is missing. The only answer that can be given is "I don't know ". This is the core of the Three-value Logic. Possible results can be true, false, or null.

Example:

[SQL]
View plaincopyprint?

1. If salary> 4000
2. Then
3. Give_bonus (employee_id );
4. End if;

IF salary>4000THEN  give_bonus(employee_id);END IF;

The null value in a Boolean expression causes the result of the entire expression to be null. That is, if salary is null, then salary> 4000 is null.

Some operators (is null/is not null) or functions (nvl () are designed to specifically process null values and control the results to binary logic.

For example:

[SQL]
View plaincopyprint?

1. If Salary & gt; 4000 or salary
   Is null
   
2. Then
3. Give_bonus (employee_id );
4. End if;

IF salary>4000 or salary IS NULLTHEN   give_bonus(employee_id);END IF;

In this way, employees with missing salaries can also receive additional allowances.

Therefore, for each variable in each Boolean expression, consider the consequences of null.

Appreciation 2: Short Circuit

PL/SQL uses the short-circuit evaluate method, that is, PL/SQL does not need to evaluate all expressions in an if statement. For example, when the expression in the IF statement below is evaluated, as long as the result of condition1 is false or null, PL/SQL will stop evaluate the entire expression, give control to the else branch.

[SQL]
View plaincopyprint?

1. If condition1 and condition2
   
2. Then
3. ...
4. Else
5. ...
6. End if;

IF condition1 AND condition2THEN  ...ELSE  ...END IF;

Similar to and, if condition 1 is true, PL/SQL immediately executes the then branch using the OR operator.

If a condition is expensive, such as consuming a large amount of CPU and memory resources, short circuit is very useful. In this case, make sure that the expensive condition is placed at the end of the entire condition set:

[SQL]
View plaincopyprint?

1. If low_cpu_condition and high_cpu_condition
   
2. Then
3. ...
4. Else
5. ...
6. End if;

IF low_cpu_condition AND high_cpu_conditionTHEN  ...ELSE  ...END IF;

Appreciation 3: Boolean sign

Generally, it is easier to use a Boolean variable as a flag. If you want to do this, remember that you can directly assign a Boolean expression to a Boolean variable. Use the following code: