PL/SQL condition and Sequence Control

In the previous blog, I wrote the if condition control. For details, see: Click to open the link.

Next, let's talk about if and case.

If first.

Several common if combinations:

1) if-then combination

Syntax:
If condition
Then
Statement;
End if;

Note:
The simplest if judgment.

2) if-then-else combination

Syntax:
If condition_1
Then
Statement_1;
Else
Statement_2;
End if;

Note:
If you make a choice in two mutually exclusive actions, this may be a good choice. Because if-then-else is a "or... or" structure, the "two things will hurt each other". Once appropriate, the control right will be delivered immediately.

3) If-then-elsif combination

Syntax:
If condition_1
Then
Statement_1;
Elsif condition_2
Then
Statement_2;
[Else
Statement_3; -- the else clause is optional
]
End if;

Note:
After 9i, it is better to use the case statement.

The logic is complex, or nested if is involved.

[SQL]
View plaincopyprint?

1. If condition_1
2. Then
3. If condition_2
4. Then
5. Statement_2;
6. Else
7. If condition_3
8. Then
9. Statement_3;
10. Elsif condition_4
11. Then
12. Statement_4;
13. End if;
14. End if;
15. End if;

if condition_1then  if condition_2  then    statement_2;  else    if condition_3    then      statement_3;    elsif condition_4    then      statement_4;    end if;  end if;end if;

If the nested condition logic has more than three layers, you can use functions and other modules to hide the inmost if statement. One advantage of nested if is that the evaluation of the inner condition can be delayed, which is also the main reason for using it. This condition is executed only when the other condition is true. For example, the Labor week allows class cadres to sweep the floor:

[SQL]
View plaincopyprint?

1. If check_monitor (stu_id)
2. Then
3. If clean_room (stu_id)
4. Then
5. Dbms_output.put_line ("work week is all done by Class Cadres ");
   
6. End if;
7. End if;

If check_monitor (stu_id) then if clean_room (stu_id) Then dbms_output.put_line ("work week is all done by Class Cadres"); end if;

If syntax trap

An if always has a matched end if, and there must be spaces between the keyword end and if;
The keyword elsif should not include "E ";
Only use the semicolon (;) after the keyword end if (;)

Case statement

1) Category
Simple case statement
Search-type case statement

2) Simple case
Definition: select the PL/SQL statement to be executed based on the result of a simple expression.
Syntax:
Case expression
When result_1
Then
Statement_1;
When result_2
Then
Statement_2;
...
Else
Statement_n;
End case;
Note:
1) In case, the else behavior is completely different from that in if! In case, if else is not defined and the result value of the Case expression cannot be matched in the when, a case_not_found exception is thrown.
2) The case syntax, expression, and result elements can be scalar or an expression that can obtain scalar results.
Example:
Case true
When salary> = 1000 and salary <= 2000
Then
Give_bonus (employee ID, 150 );
When salary> 2000 and salary <= 4000
Then
Give_bonus (employee ID, 100 );
Else
Give_bonus (employee_id, 0 );
End case;

Search-type case
Definition: Evaluate a series of boolean expressions. Once an expression returns true, the PL/SQL statement associated with the expression is executed.
Syntax:
Case
When expression_1
Statement_1;
When expression_2
Statement_2;
...
Else
Statement_n;
End case;
Note:
1) When expression, first and bottom order evaluation. Stick the most likely when statement to the top, or reap the performance benefits.
2) Pay attention to the boundary overlap problem of the when expression.
Example:
Case
When salary> 4000
Then
Give_bonus (employee ID, 150 );
When salary> 2000
Then
Give_bonus (employee ID, 100 );
Else
Give_bonus (employee_id, 0 );
End case;

Case expression. The front blog has been written. See: Click to open the link

Address: http://blog.csdn.net/linwaterbin/article/details/7952317