PL/SQL中的循环控制

最新推荐文章于 2025-02-07 08:49:05 发布

原创最新推荐文章于 2025-02-07 08:49:05 发布 · 269 阅读

0 ·

CC 4.0 BY-SA版权

Oracle 专栏收录该内容

17 篇文章

订阅专栏

博客介绍了执行流控制中的循环类型，包括For loop（分为numeric和cursor FOR loop两种）、简单loop（在循环体内执行EXIT、EXIT WHEN、RETURN或抛出异常时终止）以及WHILE loop。

参考原文：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;