PL/SQL中的循环控制

参考原文：controlling-the-flow-of-execution

1, For loop

For loop分为numeric and a cursor FOR loop两种：

PROCEDURE display_multiple_years ( -- numeric FOR loop的例子
   start_year_in IN PLS_INTEGER
  ,end_year_in   IN PLS_INTEGER
)
IS
BEGIN
  FOR l_current_year  -- 注意这里不需要声明l_current_year变量。
  IN start_year_in .. end_year_in -- 需要指定起始和结束的整型值。
  LOOP
     display_total_sales 
             (l_current_year);
  END LOOP;
END display_multiple_years;

PROCEDURE display_multiple_years (
    start_year_in IN PLS_INTEGER
   ,end_year_in IN PLS_INTEGER
)
IS
BEGIN
  FOR l_current_year IN ( -- 也可以使用游标或select语句
     SELECT * FROM sales_data
       WHERE year 
       BETWEEN start_year_in 
       AND end_year_in)
  LOOP
     display_total_sales 
             (l_current_year);
  END LOOP;
END display_multiple_years;

2, 简单loop

The loop will terminate if you execute an EXIT, EXIT WHEN, or RETURN within the body of the loop (or if an exception is raised).

PROCEDURE display_multiple_years (
   start_year_in IN PLS_INTEGER
  ,end_year_in   IN PLS_INTEGER
)
IS
   l_current_year PLS_INTEGER := start_year_in;
BEGIN
   LOOP
      EXIT WHEN l_current_year > end_year_in;
      display_total_sales (l_current_year);
      l_current_year :=  l_current_year + 1;
   END LOOP;
END display_multiple_years;

3，WHILE loop

PROCEDURE display_multiple_years (
   start_year_in IN PLS_INTEGER
  ,end_year_in   IN PLS_INTEGER
)
IS
   l_current_year PLS_INTEGER := start_year_in;
BEGIN
   WHILE (l_current_year <= end_year_in)
   LOOP
      display_total_sales (l_current_year);
      l_current_year :=  l_current_year + 1;
   END LOOP;
END display_multiple_years;