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:

-- 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

while a loop
  b;
end loop;

-- 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;

-- 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

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

-- 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;

##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: "

"

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!