What are the loop statements in Oracle stored procedures?

Loop statements in oracle stored procedures: 1. LOOP loop, the syntax is "loop loop body; EXIT WHEN end loop conditional expression; END LOOP;"; 2. WHILE loop, the syntax is "while conditional expression Formula loop loop statement;end loop;"; 3. For loop, the syntax is "for variable IN (reverse) counter lower limit value...counter upper limit value LOOP loop body;END LOOP;".

The operating environment of this tutorial: Windows 10 system, Oracle version 12c, Dell G3 computer.

What are the loop statements in Oracle stored procedures?

When the program needs to repeatedly perform a certain operation, it must use a loop structure. Loop statements in PL/SQL mainly include three types: LOOP statement, WHERE statement and FOR statement.

LOOP statement

The LOOP statement will first execute the loop body once, and then determine whether the conditional expression after the EXIT WHEN keyword is true or false. When it is true, Exit the loop body, otherwise the program will execute the loop body again.

Basic syntax:

loop
    A;
    EXIT WHEN B;
END LOOP;

A: Represents the SQL statement in the loop body, which can be one sentence or multiple sentences. This is the core part of the loop body. These statements are executed at least once.

B: Loop end conditional expression, when it is true, exit the loop, otherwise execute the loop body again.

Code example:

-- Created on 2020/12/16 by GUO 
declare 
  i int:= 0;
begin
  loop
    i:=i+1;
    dbms_output.put_line(i);
    EXIT WHEN i > 3;
  END LOOP;
end;

Running result:

##Practical example:

When using Loop cursor, take The value in the cursor must be reassigned, otherwise an error will be reported.

-- Created on 2020/12/17 by GUO 
declare
  cursor user is
    select * from user_table;
  user1 user_table%rowtype;
begin
  open user;
  loop
    fetch user into user1;
    exit when user%notfound;
    dbms_output.put_line('用户名称：' || user1.USER_NAME);
    dbms_output.put_line('用户年龄：' || user1.USER_AGE);
  end loop;
  close user; --关闭游标
end;

WHILE statement

Before execution, first determine whether the value of the conditional expression is true. If true, execute the loop body. Otherwise, exit the WHILE loop and continue execution. Code behind the loop.

Basic syntax:

while a loop
  b;
end loop;

A: Represents a conditional expression. When the value is true, the program executes the loop body, otherwise it exits.

B: The sql statement in the loop body.

Code example:

-- Created on 2020/12/17 by GUO 
declare
  i int := 0;
begin
  while i < 3 loop
    i := i + 1;
    dbms_output.put_line(i);
  end loop;
end;

Running result:

Practical example:

-- Created on 2020/12/17 by GUO 
declare
  cursor user is
    select * from user_table;
  user1 user_table%rowtype;
begin
  open user;
  fetch user into user1;
  while(user%found)loop
  dbms_output.put_line('用户名称：' || user1.USER_NAME);
  fetch user into user1;
  end loop;
end;

FOR statement

The FOR statement is a loop control statement that can set the number of loops in advance. It has a loop counter, usually an integer variable, and the number of loops is controlled through this counter.

Basic syntax:

for A IN (reverse) B...C LOOP
   D;
 END LOOP;

A: Represents a variable, usually a certificate type, used as a counter. The default value is incrementing. When the reverse keyword is used in a loop, it will loop. Decreasingly.

B: Counter lower limit value. When the counter value is less than the lower limit value, the loop is terminated.

C: Counter upper limit value. When the counter value is greater than the upper limit value, the loop is terminated.

D: Loop body.

Code example:

-- Created on 2020/12/17 by GUO 
declare 
  i int := 0;
begin
  for i IN reverse 1..5 LOOP
    dbms_output.put_line(i);
  END LOOP;
end;

Run result:

##Practical example:

Use with cursor

-- Created on 2020/12/17 by GUO 
declare
  cursor user is
    select * from user_table;
begin
  for user1 in user loop
  dbms_output.put_line('用户名称：'||user1.USER_NAME);
  dbms_output.put_line('用户年龄：'||user1.USER_AGE);
  end loop;
end;

Recommended tutorial: "

Oracle Video Tutorial

"

The above is the detailed content of What are the loop statements in Oracle stored procedures?. For more information, please follow other related articles on the PHP Chinese website!