What are the control and loop statements of Oracle stored procedures?
This article mainly talks about "what are the control and loop statements of Oracle stored procedures?" interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "what are the control and loop statements of Oracle stored procedures?"
1. Control structure
If-then
If-then-else
If-then-elsif-else
There are mainly these three control structures.
Here's an example:
Create or replace procedure mypro (spNo number) is
V_job emp.job%type
Begin
Select job into v_job from emp where empno = spNo
If v_job = 'PRESIDENT' then
Update emp set sal = sal + 1000 where empno = spNo
Elsif v_job = 'MANAGER' then
Update emp set sal = sal + 500where empno = spNo
Else
Update emp set sal = sal + 200where empno = spNo
End if
End
/
Execution
Begin
-- Call the procedure
Mypro (49)
End
2. Circular statement
The main loop statements of pl/sql are as follows:
Loop-end loop
Exit when
While
For (normal cycle)
For (cursor Loop)
Examples are given one by one:
A, loop cycle
Create or replace procedure proc_loop is
I number
Begin
I: = 0
Loop
I:=i+1
Dbms_output.put_line (I)
If I > 5 then
Exit
End if
End loop
End
B, exit when cycle
Create or replace procedure proc_exit is
I number
Begin
I: = 0
Loop
Exit when (I > 5)
Dbms_output.put_line (I)
I: = iTun1
End loop
End
C, while cycle
Create or replace procedure proc_while is
I number
Begin
I: = 0
While i
