PLSQL:用exit代替continue

最新推荐文章于 2022-06-08 22:48:31 发布

anddyhua

最新推荐文章于 2022-06-08 22:48:31 发布

阅读量4.4k

点赞数

分类专栏： # Note for SQL&PLSQL

本文链接：https://blog.csdn.net/anddyhua/article/details/40015223

版权

Note for SQL&PLSQL 专栏收录该内容

19 篇文章 0 订阅

订阅专栏

因为11g才支持continue，在有些低的版本中可能需要改写

SQL> 
SQL> DECLARE
  2    total   NUMBER(9) := 0;
  3    counter NUMBER(6) := 0;
  4  BEGIN
  5    LOOP
  6      counter := counter + 1;
  7      total := total + counter * counter;
  8      DBMS_OUTPUT.PUT_LINE('Current Counter: ' || TO_CHAR(counter) || ' Current Total: ' || TO_CHAR(total));
  9      -- exit loop when condition is true
 10      EXIT WHEN total > 250;
 11    END LOOP;
 12    DBMS_OUTPUT.PUT_LINE('Counter: ' || TO_CHAR(counter) || ' Total: ' || TO_CHAR(total));
 13  END;
 14  /
 
PL/SQL procedure successfully completed
 
SQL> set serveroutput on
SQL> /
 
Current Counter: 1 Current Total: 1
Current Counter: 2 Current Total: 5
Current Counter: 3 Current Total: 14
Current Counter: 4 Current Total: 30
Current Counter: 5 Current Total: 55
Current Counter: 6 Current Total: 91
Current Counter: 7 Current Total: 140
Current Counter: 8 Current Total: 204
Current Counter: 9 Current Total: 285
Counter: 9 Total: 285
 
PL/SQL procedure successfully completed

先用11g的continue语句改写下，添加一个条件

SQL> 
SQL> DECLARE
  2    total   NUMBER(9) := 0;
  3    counter NUMBER(6) := 0;
  4  BEGIN
  5    LOOP
  6      counter := counter + 1;
  7      total := total + counter * counter;
  8      IF total<31 THEN
  9        CONTINUE;
 10      ELSE
 11      DBMS_OUTPUT.PUT_LINE('Current Counter: ' || TO_CHAR(counter) || ' Current Total: ' || TO_CHAR(total));
 12      END IF;
 13      -- exit loop when condition is true
 14      EXIT WHEN total > 250;
 15    END LOOP;
 16    DBMS_OUTPUT.PUT_LINE('Counter: ' || TO_CHAR(counter) || ' Total: ' || TO_CHAR(total));
 17  END;
 18  /
 
Current Counter: 5 Current Total: 55
Current Counter: 6 Current Total: 91
Current Counter: 7 Current Total: 140
Current Counter: 8 Current Total: 204
Current Counter: 9 Current Total: 285
Counter: 9 Total: 285
 
PL/SQL procedure successfully completed

改写最先让人想到的就是goto了

SQL> 
SQL> DECLARE
  2    total   NUMBER(9) := 0;
  3    counter NUMBER(6) := 0;
  4  BEGIN
  5    LOOP
  6      <<out_loop>>
  7      counter := counter + 1;
  8      total := total + counter * counter;
  9      IF total<31 THEN
 10        GOTO out_loop;
 11      ELSE
 12      DBMS_OUTPUT.PUT_LINE('Current Counter: ' || TO_CHAR(counter) || ' Current Total: ' || TO_CHAR(total));
 13      END IF;
 14      -- exit loop when condition is true
 15      EXIT WHEN total > 250;
 16    END LOOP;
 17    DBMS_OUTPUT.PUT_LINE('Counter: ' || TO_CHAR(counter) || ' Total: ' || TO_CHAR(total));
 18  END;
 19  /
 
Current Counter: 5 Current Total: 55
Current Counter: 6 Current Total: 91
Current Counter: 7 Current Total: 140
Current Counter: 8 Current Total: 204
Current Counter: 9 Current Total: 285
Counter: 9 Total: 285
 
PL/SQL procedure successfully completed

用 exit 也可以，外面套一层循环，if条件

SQL> DECLARE
  2    total   NUMBER(9) := 0;
  3    counter NUMBER(6) := 0;
  4  BEGIN
  5    LOOP
  6  
  7      counter := counter + 1;
  8      total := total + counter * counter;
  9      LOOP
 10      IF total<31 THEN
 11        EXIT;
 12      ELSE
 13      DBMS_OUTPUT.PUT_LINE('Current Counter: ' || TO_CHAR(counter) || ' Current Total: ' || TO_CHAR(total));
 14      EXIT;
 15      END IF;
 16      END LOOP;
 17      -- exit loop when condition is true
 18      EXIT WHEN total > 250;
 19    END LOOP;
 20    DBMS_OUTPUT.PUT_LINE('Counter: ' || TO_CHAR(counter) || ' Total: ' || TO_CHAR(total));
 21  END;
 22  /
 
Current Counter: 5 Current Total: 55
Current Counter: 6 Current Total: 91
Current Counter: 7 Current Total: 140
Current Counter: 8 Current Total: 204
Current Counter: 9 Current Total: 285
Counter: 9 Total: 285
 
PL/SQL procedure successfully completed