Basic PL/SQL programming (4) [LOOP WHILE FOR] [Exception Handling and custom exceptions]

1. Combination of LOOP and IF in loop LOOP Cases
Declare
X number;
Begin
X: = 0;
Loop -- start of loop
X: = x + 1;
If x> 3 then -- cyclic body
Exit;
End if;
Dbms_output.put_line ('value of inner x '| x); -- cyclic body
End loop; -- end loop
Dbms_output.put_line ('value of external x' | X );
End;
/
Output result:
The value of x in 1
2 In x
3 In x
Value of outer X 4
 
 
 
2. Cases of combining loop with while and loop
 
Declare
X number;
Y varchar2 (10 );
Begin
X: = 1;
While x <= 3 loop -- while and loop
X: = x + 1;
Dbms_output.put_line (the value of 'x is '| x );
End loop; -- end loop
End;
/
 
3. The case of combining for and loop [reverse] is optional, meaning that, in turn, 5 .. 1 means that the for loop increment in Oracle can only be 1 and cannot be modified.
Begin
For I [reverse] in 1 .. 5 loop -- for and loop combined
Dbms_output.put_line ('I value' | I );
End loop;
Dbms_output.put_line ('out ');
End;
/
 
 
4. Use goto to control the cycle of each step.
 
Declare
X number;
Begin
X: = 0;
<Gsign> -- Define a goto tag
X: = x + 1; -- add a few here to control the step.
Dbms_output.put_line (x );
If x <5 then
Goto gsign; -- Define the statement to the place where goto is located.
End if;
End;
/
 
Exception Handling table:
The case is below

 
 
 
 
 
 
 
5. Handling exceptions in Oracle
Declare
X varchar2 (10 );
Begin
Select a1 into x from a where a2 = 'qw'; -- query the value and assign it to the variable.
Dbms_output.put_line ('x value '| x );
Exception
-- Exception Handling data. What should I do if it does not exist?
When NO_DATA_FOUND then dbms_output.put_line ('no data ');
End;
/
 
6. Oracle custom Exception e Exception
Declare
X varchar2 (10 );
E exception; -- Define the exception variable
Begin
Select a1 into x from a where a2 = 'mmm ';
If x <> 'qqq' then
Raise e; -- a custom exception must be raise.
End if;
Exception
When e then -- then write the exception in this way.
Dbms_output.put_line ('aaaaa ');
End;
/
[SQL] view plaincopy
Exit the loop using exit when boolean