PL/SQL flow control statements

PL/SQL flow control statements

PL/SQL flow control statements

This section describes PL/SQL flow control statements, including the following three types:
Control statement: IF statement
LOOP statement: LOOP statement and EXIT statement
Sequential statement: GOTO statement, NULL statement
① If statement
IF <Boolean expression> THEN
PL/SQL and SQL 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> = 100 ';
Elsif (v_emp_sal> = 5000) then v_emp_sal_level: = '2017 <= salary <5000 ';
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;

Example:
Declare
V_sal employees. salary % type;
V_msg varchar2 (50 );
Begin
Select salary into v_sal
From employees
Where employee_id = 150;

-- Case cannot be used as follows
/*
Case v_sal when salary> = 10000 then v_msg: = '> = 100'
When salary> = 5000 then v_msg: = '2017 <= salary <5000'
Else v_msg: = 'salary <5000'
End;
*/
 
V_msg: =
Case trunc (v_sal/5000)
When 0 then 'salary <5000'
When 1 then '2014 <= salary <5000'
Else 'salary> = 100'
End;

Dbms_output.put_line (v_sal | ',' | v_msg );
End;

③ Loop
1. Simple Loop
LOOP
The statement to be executed;
Exit when <Condition Statement>;/* If the condition is met, EXIT the loop statement */
End loop;

2. WHILE loop (2 is recommended for comparison with 1)
WHILE <Boolean expression> LOOP
The statement to be executed;
End loop;
3. Digital Circulation
For loop counter IN [REVERSE] lower limit... upper limit LOOP
The statement to be executed;
End loop;
For each loop, 1 is automatically added to the loop variable. If the keyword REVERSE is used, the loop variable is automatically reduced by 1.
The numbers following in reverse must be IN ascending order and must be integers, not variables or expressions. You can use EXIT to EXIT the loop.

For example, print 1-100 using a loop Statement (three methods)

1). LOOP... exit when... END LOOP
Declare
-- Initialization condition
V_ I number (3): = 1;
Begin
Loop
-- Loop body
Dbms_output.put_line (v_ I );
-- Cyclic Condition
Exit when Vanderbilt I = 100;
-- Iteration Condition
V_ I: = v_ I + 1;
End loop;
End;

2). WHILE... LOOP... END LOOP
Declare
-- Initialization condition
V_ I number (3): = 1;
Begin
-- Cyclic Condition
While v_ I <= 100 loop
-- Loop body
Dbms_output.put_line (v_ I );
-- Iteration Condition
V_ I: = v_ I + 1;
End loop;
End;

3 ).
Begin
For I in 1 .. 100 loop
Dbms_output.put_line (I );
End loop;
End;

For example, use the if and while statements to print all prime numbers between 1 and 100.
(Prime number: there are only two positive integers, 2, 3, 5, 7, 11, 13 ,...).

Declare
V_ I int: = 2;
V_j int: = 2;
V_flag boolean: = false;
Begin
While v_ I <100 loop
V_j: = 2;
While v_j <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 | ', which is a prime number ');
End if;
V_flag: = false;
V_ I: = v_ I + 1;
End loop;
End;

④ Number and GOTO
In PL/SQL, The GOTO statement is used to jump to the specified number unconditionally. Syntax:
GOTO label;
......
<Label>/* indicates the identifier enclosed by <> */

For example, print the natural numbers from 1 to. When printing to 50, the system jumps out of the loop and outputs "Print ended"
(Method 1)
Begin
For I in 1 .. 100 loop
Dbms_output.put_line (I );
If (I = 50) then
Goto label;
End if;
End loop;

<Label>
Dbms_output.put_line ('print stopped ');

End;
(Method 2)
Begin
For I in 1 .. 100 loop
Dbms_output.put_line (I );
If (I mod 50 = 0) then
Dbms_output.put_line ('print stopped ');
Exit;
End if;
End loop;
End;

Oracle -- plsql Composite data type

-------------------------------------- Split line --------------------------------------

Rlwrap

SQLPLUS spool to dynamic Log File Name

Oracle SQLPLUS prompt settings

Accelerate SQL return by setting SQLPLUS ARRAYSIZE (row prefetch)

PL/SQL Developer Practical Skills

-------------------------------------- Split line --------------------------------------