Flow Control statements in PL/SQL

I. condition control statements
1. Only the conditional control statement of if
If concition then
Statements;
End if;
See the following example:

Declare salaryavg number (7,2); empsalary number (7,2); begin select AVG (SAL) into salaryavg from Scott. EMP; select Sal into empsalary from Scott. EMP where empno = & Eno; If empsalary> salaryavg then dbms_output.put_line ('the employee's salary has exceeded the average line' | salaryavg); end if; end;

2. If and else statements
If condition then
Statements;
Else
Statements;
End if;
See the following example:

Declare salaryavg number (7,2); empsalary number (7,2); begin select AVG (SAL) into salaryavg from Scott. EMP; select Sal into empsalary from Scott. EMP where empno = & Eno; If empsalary> salaryavg then dbms_output.put_line ('the employee's salary has exceeded the average' | salaryavg ); else dbms_output.put_line ('the employee's salary has not exceeded the average' | salaryavg); end if; end;

3. If esleif else statement
If condition then
statements;
elsif condition then
statements;
else
statements;
end if;
see the following example:

Declare empsalary number (7,2); begin select Sal into empsalary from Scott. EMP where empno = & Eno; If empsalary <2000 then dbms_output.put_line ('tax 0 RMB '); elsif empsalary <4000 then dbms_output.put_line ('tax payable '| (empsalary * 0.15-175) | 'meta '); else dbms_output.put_line ('tax payable '| (empsalary * 0.25-375) | 'meta'); end if; end;

4. Case statement
Compare a single value:
Case Selector
When expression1 then result1;
When expression2 then result2;
When expression3 then result3;
Else result4;
End case;
See the following example:

Declare Eno number (2); begin Eno: = & No; Case Eno when 10 then dbms_output.put_line ('department 1'); when 20 then dbms_output.put_line ('department 2 '); when 30 then dbms_output.put_line ('department 3'); else dbms_output.put_line ('no Department '); End case; end; -- compare multiple conditions: case when expression1 then result1; when expression2 then result2; When expression3 then result3; else result4; end case;

Ii. Loop Control statements
1. loop
Loop
Statements;
End Loop;
Example 1:

Declare a int; begin A: = 0; loop if a = 10 then exit; end if; dbms_output.put_line (a); A: = a + 1; end loop; end;

Example 2:

Declare a int; begin A: = 0; loop exit when a = 10; dbms_output.put_line (a); A: = a + 1; end loop; end;

2. While Loop

Example 1:

Declare a int; begin A: = 0; while a <10 loop dbms_output.put_line (a); A: = a + 1; end loop; end;

3. For Loop

Example 1:

begin for a in 0 .. 9 loop dbms_output.put_line (a); End loop; end;

4. Multiple cycles and labels
Example 1:

Declare result int; begin < > For I in 1 .. 10 loop < > For J in 1 .. 10 loop result: = I * j; exit outer when result = 10; exit when result = 5; dbms_output.put_line ('inner: '| result); End loop inner; dbms_output.put_line ('outer: '| result); End loop outer; dbms_output.put_line ('Last:' | result); end;

Iii. Sequential Control statements
1. GOTO statement
Syntax: goto labelname;
2. null statement
NULL; the statement is passed directly to the next statement without executing any operation.