PLSQL:用exit代替continue

来源：互联网发布：u盘数据恢复免费编辑：程序博客网时间：2024/05/01 23:52

因为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 onSQL> / Current Counter: 1 Current Total: 1Current Counter: 2 Current Total: 5Current Counter: 3 Current Total: 14Current Counter: 4 Current Total: 30Current Counter: 5 Current Total: 55Current Counter: 6 Current Total: 91Current Counter: 7 Current Total: 140Current Counter: 8 Current Total: 204Current Counter: 9 Current Total: 285Counter: 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: 55Current Counter: 6 Current Total: 91Current Counter: 7 Current Total: 140Current Counter: 8 Current Total: 204Current Counter: 9 Current Total: 285Counter: 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: 55Current Counter: 6 Current Total: 91Current Counter: 7 Current Total: 140Current Counter: 8 Current Total: 204Current Counter: 9 Current Total: 285Counter: 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: 55Current Counter: 6 Current Total: 91Current Counter: 7 Current Total: 140Current Counter: 8 Current Total: 204Current Counter: 9 Current Total: 285Counter: 9 Total: 285 PL/SQL procedure successfully completed

0 0