Pl/sql Process Control

The flow control statements in the PL/SQL program draw on the process control ideas of many high-level languages, but have their own characteristics.

Condition control

The following example describes the use of conditional control statements.
1. If.. Then.. End If condition control
Use If.. Then.. The End If condition controls the syntax structure as shown in Figure 9.15.

Execute the following pl/sql program in "Sqlplus Worksheet", which determines the size of two integer variables. The results of the execution are shown in Figure 9.16.
―――――――――――――――――――――――――――――――――――――
Set Serveroutput on
Declare
Number1 integer:=90;
Number2 integer:=60;
Begin
If Number1>=number2 Then
Dbms_output.put_line (' Number1 greater than equal to number2 ');
End If;
End
―――――――――――――――――――――――――――――――――――――
"Matching program Location": 9th Chapter \ Conditioncontrol1.sql.

2. If.. Then.. else.. End If condition control
Use If.. Then.. else.. The End If condition controls the syntax structure as shown in Figure 9.17.

The following Pl/sql program is executed in "Sqlplus Worksheet", which judges the size of two integer variables and outputs different results. The results of the execution are shown in Figure 9.18.
―――――――――――――――――――――――――――――――――――――
Set Serveroutput on
Declare
Number1 integer:=80;
Number2 integer:=90;
Begin
If Number1>=number2 Then
Dbms_output.put_line (' Number1 greater than equal to number2 ');
Else
Dbms_output.put_line (' Number1 less than number2 ');
End If;
End
―――――――――――――――――――――――――――――――――――――
"Matching program Location": 9th Chapter \ Conditioncontrol2.sql.

3. If nested condition control
The syntax structure, which is controlled by the if nested condition, is shown in Figure 9.19.

The following Pl/sql program is executed in "Sqlplus Worksheet", which judges the size of two integer variables and outputs different results.
The results of the execution are shown in Figure 9.20.
―――――――――――――――――――――――――――――――――――――
Set Serveroutput on
Declare
Number1 integer:=80;
Number2 integer:=90;
Begin
If Number1<=number2 Then
If Number1=number2 Then
Dbms_output.put_line (' number1 equals number2 ');
Else
Dbms_output.put_line (' Number1 less than number2 ');
End If;
Else
Dbms_output.put_line (' Number1 greater than number2 ');
End If;
End
―――――――――――――――――――――――――――――――――――――
"Matching program Location": 9th Chapter \ Conditioncontrol3.sql.


Loop control

The cyclic structure is to execute a set of commands according to certain logical conditions, there are 4 basic cyclic structures in the pl/sql, and many nested cyclic controls can be developed on the basis of them, which introduces the most basic circular control statements.
1. Loop ... Exit.. End Loop loop control
Using loop ... Exit.. The syntax structure of the end loop loop control is shown in Figure 9.21.

The following Pl/sql program is executed in "Sqlplus Worksheet", which counts the number of output loops by adding 1 to the NUMBER1 variable each time until equal to number2.
―――――――――――――――――――――――――――――――――――――
Set Serveroutput on
Declare
Number1 integer:=80;
Number2 integer:=90;
I integer:=0;
Begin
Loop
number1:=number1+1;
If Number1=number2 Then
Exit
Else
i:=i+1;
End If;
End Loop;
Dbms_output.put_line (' Total cycle times: ' | | To_char (i));
End
―――――――――――――――――――――――――――――――――――――
The results of the execution are shown in Figure 9.22.

"Matching program location": the 9th Chapter \loopcontrol1.sql.
2. Loop ... Exit.. When.. End Loop loop control
Using loop ... Exit.. When.. The syntax structure of the end Loop loop control is similar to the structure shown in Figure 9.21.
Exit when is actually equivalent to
If condition Then
Exit
End If;
The following Pl/sql program is executed in "Sqlplus Worksheet", which counts the number of output loops by adding 1 to the NUMBER1 variable each time until equal to number2.
―――――――――――――――――――――――――――――――――――――
Set Serveroutput on
Declare
Number1 integer:=80;
Number2 integer:=90;
I integer:=0;
Begin
Loop
number1:=number1+1;
i:=i+1;
Exit when Number1=number2;
End Loop;
Dbms_output.put_line (' Total cycle times: ' | | To_char (i));
End
―――――――――――――――――――――――――――――――――――――
The results of the execution are shown in Figure 9.23.

"Matching program location": the 9th Chapter \loopcontrol2.sql.
When the loop control end condition controls the end Loop 1 times more than if the condition of the if is used.
3. While.. Loop.. End Loop loop control
Using loop ... Exit.. When.. The syntax for the end Loop loop control is as follows.
While condition loop
Execute the sentence segment;
End Loop;
The following Pl/sql program is executed in "Sqlplus Worksheet", which counts the number of output loops by adding 1 to the NUMBER1 variable each time until equal to number2.
―――――――――――――――――――――――――――――――――――――
Set Serveroutput on
Declare
Number1 integer:=80;
Number2 integer:=90;
I integer:=0;
Begin
While Number1<number2 loop
number1:=number1+1;
i:=i+1;
End Loop;
Dbms_output.put_line (' Total cycle times: ' | | To_char (i));
End
―――――――――――――――――――――――――――――――――――――
The results of the execution are shown in Figure 9.24.

"Matching program location": the 9th Chapter \whilecontrol.sql.
4. For. In.. Loop.. End loop control
Use for.. In.. Loop.. The syntax for End loop control is as follows.
For loop variable in [reverse] loop lower bound ... Loop Upper Loops
Circular processing of sentence segments;
End Loop;
The following Pl/sql program is executed in "Sqlplus Worksheet", which controls the number of number1 increases and outputs the results through the loop variable i. The results of the execution are shown in Figure 9.25.
―――――――――――――――――――――――――――――――――――――
Set Serveroutput on
Declare
Number1 integer:=80;
Number2 integer:=90;
I integer:=0;
Begin
For I in 1..10 loop
number1:=number1+1;
End Loop;
Dbms_output.put_line (' number1 value: ' | | To_char (Number1));
End
―――――――――――――――――――――――――――――――――――――
"Matching program location": the 9th Chapter \forcontrol.sql.