Shulou(Shulou.com)05/31 Report--

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.