PL/SQL Process Control statements



PL/SQL Process Control statements
Describes the Process Control statements for PL/SQL, including the following three classes:
Control statement: IF statement
Loop statement: Loop statement, exit statement
Sequential statement: Goto statement, NULL statement
①if statements
IF < Boolean expressions > Then
PL/SQL and statements;
elsif< other Boolean expressions > Then
other statements;
elsif< other Boolean expressions > Then
other statements;
ELSE
other statements;
END IF;

Example:
Declare
     v_emp_name employees.last_name%type;
     v_emp_sal Employees.salary%type;
     v_emp_sal_level varchar2 (20);
Begin
     select Last_name,salary into v_emp_name,v_emp_sal from employees where employee_ id = 150;
    
     if (v_emp_sal >= 10000) then v_emp_sal_level: = ' salary >= 10000 ';
     elsif (v_emp_sal >=) then v_emp_sal_level: = ' 5000<= salary < 10000 ';
     Else V_emp_sal_level: = ' salary < 5000 ';
     End If;
    
     dbms_output.put_line (v_emp_name| | ', ' | | v_emp_sal| | ', ' | | V_emp_sal_level);
End;

②case-expression
Case Selector
When Expression1 then RESULT1
When Expression2 then RESULT2
When Expressionn then RESULTN
[ELSE resultn+1]
END;

Cases:
Declare
V_sal Employees.salary%type;
V_msg VARCHAR2 (50);
Begin
Select Salary into V_sal
From Employees
where employee_id = 150;

--case can not be used in the following way
/*
Case V_sal-Salary >= 10000 then v_msg: = ' >=10000 '
When salary >= v_msg: = ' 5000<= Salary < 10000 '
else v_msg: = ' Salary < 5000 '
End
*/

V_msg: =
Case Trunc (v_sal/5000)
When 0 Then ' salary < 5000 '
When 1 Then ' 5000<= Salary < 10000 '
Else ' Salary >= 10000 '
End

Dbms_output.put_line (V_sal | | ', ' | | V_MSG);
End

③ Cycle
1. Simple cycle
LOOP
The statement to execute;
EXIT when< Conditional Statement >; /* Condition satisfied, exit Loop statement */
END LOOP;

2. While loop (compared with 1, 2 recommended)
while< Boolean expressions > Loops
The statement to execute;
END LOOP;
3. Digital Cycle
For loop counter in[reverse] lower limit: Upper Loop
The statement to execute;
END LOOP;
For Each loop, the loop variable is automatically added 1, and the loop variable is automatically minus 1 using the keyword reverse.
The numbers following the in REVERSE must be in order from small to large, and must be integers, not variables or expressions. You can use Exit to exit the loop.

Example: Using a looping statement to print 1-100. (Three ways)

1) .  LOOP ... EXIT when ... END LOOP
Declare
      -initialization condition
       v_i Number (3): = 1;
Begin
       Loop
      --loop body
         Dbms_output.put_line (v_i);
 --loop Condition
        exit when v_i = +;
 --Iteration Conditions
        v_i: = v_i + 1;
       end Loop;
End;

2). While ... LOOP ... END LOOP
Declare
--Initialization conditions
V_i Number (3): = 1;
Begin
--Cycle conditions
While V_i <= loop
--Loop body
Dbms_output.put_line (v_i);
--Iteration conditions
V_i: = v_i + 1;
End Loop;
End

3).
Begin
For I in 1.. Loop
Dbms_output.put_line (i);
End Loop;
End

Example: Using the IF, while statement to print all the primes between 1-100
(primes: integers with only two positive approximations, 2, 3, 5, 7, 11, 13, ...).

Declare
v_i int: = 2;
V_j int: = 2;
V_flag Boolean: = false;
Begin
While V_i < loop
V_j: = 2;
While V_J&LT;SQRT (v_i) loop
If mod (v_i,v_j) = 0 Then
V_flag: = true;
Exit
End If;
V_j: = V_j + 1;
End Loop;
If V_flag = False Then
Dbms_output.put_line (v_i| | ', is prime number ');
End If;
V_flag: = false;
V_i: = V_i +1;
End Loop;
End

④ Marking and Goto
The goto statement in PL/SQL is meant to jump unconditionally to the specified label. The syntax is as follows:
GOTO label;
. . .  . . .
<<label>>/* Designator is an identifier in <<>> */

Example: Print the natural number of 1--100, when printing to 50 o'clock, jump out of the loop, output "Print end"
(Method i)
Begin
For I in 1..100 loop
Dbms_output.put_line (i);
if (i =) Then
Goto label;
End If;
End Loop;

<<label>>
Dbms_output.put_line (' End of print ');

End
(Method Two)
Begin
For I in 1..100 loop
Dbms_output.put_line (i);
if (i mod = 0) Then
Dbms_output.put_line (' End of print ');
Exit
End If;
End Loop;
End

PL/SQL Process Control statements