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 > Thenpl/sql and SQL statements; elsif< other Boolean expressions > then other statements; elsif< other Boolean expressions > then other statements; else other statements; END IF;

Cases:

Declarev_emp_name employees.last_name%type;v_emp_sal employees.salary%type;v_emp_sal_level varchar2 (20); Beginselect last_name,salary to V_emp_name,v_emp_sal from employees where employee_id = 150;if (v_emp_sal >= 10000) th En v_emp_sal_level: = ' salary >= 10000 '; elsif (v_emp_sal >=) then v_emp_sal_level: = ' 5000<= Salary < 1000 0 '; else v_emp_sal_level: = ' salary < '; end If;dbms_output.put_line (v_emp_name| | ', ' | | v_emp_sal| | ', ' | | V_emp_sal_level); end;

②case-expression

Case Selectorwhen Expression1 then Result1when expression2 then Result2when expressionn then resultn[ELSE resultn+1]end;

Cases:

Declarev_sal employees.salary%type;v_msg varchar2 () beginselect salary into v_salfrom employeeswhere employee_id = 150;--case cannot use/*case v_sal when salary >= 10000 and v_msg: = ' >=10000 ' When salary >= and v_msg: = ' 50 00<= Salary < 10000 ' else v_msg: = ' Salary < ' end;*/v_msg: =case trunc (v_sal/5000) when 0 Then ' salary < 5 ' 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 expression > Loop to execute the statement; END LOOP;

3. Digital Cycle

For loop counter in[reverse] lower limit: Upper loop to execute the statement; 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;beginloop--loop body dbms_output.put_line (v_i);--loop condition exit when v_i = 100;--iteration condition v_i: = v_i + 1;e nd loop;end;

2). While ... LOOP ... END LOOP

declare--Initialization Condition v_i number (3): = 1;begin--loop condition while v_i <= [loop--] (dbms_output.put_line);--Iteration condition V_i: = v_i + v_i nd loop;end;

3).

Beginfor i in 1.. Loopdbms_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, ...).

declarev_i int: = 2;v_j int: = 2;v_flag Boolean: = False;beginwhile v_i < loopv_j: = 2;while v_j<sqrt (v_i) Loopi F mod (V_i,v_j) = 0 Thenv_flag: = true;exit;end If;v_j: = V_j + 1;end loop;if V_flag = False Thendbms_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)

Beginfor i in 1..100 loopdbms_output.put_line (i); if (i =) Thengoto label;end if;end Loop;<<label>>dbms_ Output.put_line (' End of print '); end;

(Method Two)

Beginfor i in 1..100 loopdbms_output.put_line (i), if (i mod = 0) thendbms_output.put_line (' End of print '); Exit;end if;end Loop;e nd

PL/SQL Process Control statements