PL/SQL 03 Process Control

--if syntax
IF condition Then
statements;
[Elsif condition Then
statements;]
[ELSE
statements;]
END IF;

--case syntax

1. Equivalent comparison using a single selector in a case statement

Case Selector
When expression1 and then sequence_of_statements1;
When Expression2 and then sequence_of_statements2;
...
When Expressionn and then SEQUENCE_OF_STATEMENTSN;
[ELSE sequence_of_statementsn+1;]
END case;

2. Use multiple conditions in case statements to compare

Case
When Search_condition1 and then sequence_of_statements1;
When Search_condition2 and then sequence_of_statements2;
...
When Search_conditionn and then SEQUENCE_OF_STATEMENTSN;
[ELSE sequence_of_statementsn+1;]
END case;

--Cycle

1. Basic Loop Loops

LOOP
Statement1;
...
EXIT [when condition];
END LOOP;

2. While loop

While condition LOOP
Statement1;
Statement2;
...
END LOOP;

3. For loop

For counter in [REVERSE]
Lower_bound. Upper_bound LOOP
Statement1;
Statement2;
...
END LOOP;

4. Nesting Loops and labels

A nested loop is a circular statement that embeds another loop statement, whereas a label is used to mark nested blocks or nested loops. By using labels in nested loops, you can distinguish between inner and outer loops, and you can exit the outer loop directly in the inner loop. When writing PL/SQL blocks, you can use the <<label_name>> to define the labels.

Declare
result int;
Begin
<>
For I in 1..100 loop
<>
For j in 1..100 Loop
Result:=i*j;
Exit outer when result=1000;
Exit when result=500;
End loop inner;
Dbms_output.put_line (result);
End loop outer;
Dbms_output.put_line (result);
End
/

As shown above, when a PL/SQL block is executed, the outer loop is exited directly if result=1000, and only the inner loop is exited when result=500.

--Sequential control statement GOTO, NULL

PL/SQL not only provides conditional branching statements and loop control statements, but also provides sequential control statements GOTO and NULL. However, unlike the if,case and LOOP statements, GOTO statements and NULL statements are generally not used.

1. GOTO

The GOTO statement is used to jump to a specific label to execute the statement. Note that the use of GOTO statements increases the complexity of the program and makes the application less readable, so it is generally recommended that users do not use GOTO statements when developing applications.

Its syntax is as follows:

GOTO Label_name;

Where Label_name is the label name that is already defined. Note that when you use GOTO to jump to a specific label, you must include at least one executable statement after the label. Examples are as follows:

Declare
I int:=1;
Begin
Loop
INSERT into temp values (i);
If i=10 Then
Goto End_loop;
End If;
i:=i+1;
End Loop;
<<end_loop>>
Dbms_output.put_line (' End of cycle ');
End
/

As shown above, when the above PL/SQL block is executed, if i=10, it jumps to the label End_loop and executes the subsequent statement.

2, NULL

The null statement will not take any action and will pass control directly to the next statement. The main benefit of using a NULL statement is to improve the readability of the PL/SQL program, as shown below:

Declare
V_sal Emp.sal%type;
V_ename Emp.ename%type;
Begin
Select Ename,sal to V_ename,v_sal from emp where empno= ' &no ';
If v_sal<3000 Then
Update emp set comm=sal*0.1 where Ename=v_ename;
Else
Null
End If;
End
/

When the above PL/SQL block is executed, the employee name and its salary are determined based on the employee number entered, and if the employee's salary is less than 3000, the allowance is set to 10% of the wage, and if the employee's salary is higher than 3000, no action is taken (NULL).

--Example

Declare
CJ Xkb.chengji%type;
State VARCHAR2 (4);
Begin
Select Chengji to CJ from XKB
where stuid=101 and kcid=1001;
If CJ between and then--if statement
State:= ' excellent ';
Elsif CJ between
State:= ' good ';
Elsif CJ between
state:= ' Pass ';
Else
State:= ' poor ';
End If;
Dbms_output.put_line (' score is: ' | | CJ);
Dbms_output.put_line (' rank is: ' | | State);
End

Select Stuname,sex,
       case sex                                           Comparison of--case values
          when ' m ' Then ' Male '
         When the ' F ' then ' female '
         Else ' default value '
        End "Gender"
from Stu

[email protected] test10g> Declare
2 V_deptno Emp.deptno%type;
3 begin
4 v_deptno:= ' &no ';
5 Case V_deptno
6 when Ten Then
7 update emp set comm=100 where Deptno=v_deptno;
8 when
9 Update emp set comm=80 where Deptno=v_deptno;
Ten when
Update emp set comm=50 where Deptno=v_deptno;
Else
Dbms_output.put_line (' not found ');
The case of the end case;
The end;
16/
Enter value for No:10
Old 4:v_deptno:= ' &no ';
New 4:v_deptno:= ' 10 ';

PL/SQL procedure successfully completed.

Select stuname,sex,                                      Comparison of--decode values (not conditionally judged)
       decode (sex,
          ' m ', ' Male ',
         ' f ', ' female ',
         ' default value ') ' Gender '
from Stu

Select Stuid,kcid,
       case                                               --case condition judgment
         when Chengji between and "good"
& nbsp;        when Chengji between and "good"
    & nbsp;    when Chengji between "pass"
         Else ' difference '
       end  state 
from XKB

[email protected] test10g> declare
  2  v_sal emp.sal%type;
  3  V_ename Emp.ename%type;
  4  begin
  5    select Ename,sal into v_ename,v_sal
  6     from EMP where empno= ' &no ';
  7    Case
  8    if v_sal<1000 then
  9       Update emp set comm=100 where Ename=v_ename;
 10    when v_sal<2000 then
 11      update emp Set comm= the Where ename=v_ename;
 12    when v_sal<6000 then
 13      update emp Set comm= the Where ename=v_ename;
 14    End case;
 15  end;
 16 /
Enter value for no:7788
old   6:   from emp where empno= ' &no ';
new   6:   from emp where empno= ' 7788 ';

PL/SQL procedure successfully completed.

Declare
M int:=0;
n Int:=1;
Begin
Loop--loop Loops
M:=m+n;
n:=n+1;
Exit when n=101;
End Loop;
Dbms_output.put_line (' m is: ' | | m);
End

Declare
M int:=0;
n Int:=1;
Begin
While n<=100--while loop
Loop
M:=m+n;
n:=n+1;
End Loop;
Dbms_output.put_line (' m is: ' | | m);
End

Declare
M int:=0;
n int;
Begin
For n in 1..100--for loop
Loop
M:=m+n;
End Loop;
Dbms_output.put_line (' m is: ' | | m);
End

PL/SQL 03 Process Control