Oracle兼容-存储过程-EXIT WHEN

1. 语法

  1. EXIT (label) / EXIT (label) WHEN

2. 定义和用法

GreatSQL存储过程中支持用 EXIT (label)/EXIT (label) WHEN 退出当前循环。该用法如下所述：

  1. 退出时可带标签，也可不带标签。

  2. 支持多种退出循环判断条件，包括 EXIT WHEN cursor%FOUND / %NOTFOUND / %ISOPEN 等多种判断条件。

3. 示例

创建测试表并初始化数据

  1. greatsql> CREATE TABLE t1 (a INT NOT NULL, b VARCHAR(20) NOT NULL);
  2. greatsql> INSERT INTO t1 VALUES(1, 'row1'), (2, 'row2'), (3,'row3') ;
    1. 示例1
  1. greatsql> SET sql_mode = ORACLE;
  2. greatsql> DELIMITER //
  4. greatsql> CREATE OR REPLACE PROCEDURE sp_loop1() AS
  5.   CURSOR cur1 IS SELECT a, b FROM t1 WHERE a>1;
  6. BEGIN
  7.   <<forLoop>>
  8.   FOR rec IN cur1 LOOP
  9.     SELECT rec.a ,rec.b; 
  10.     EXIT;   -- 只循环一次就退出
  11.   END LOOP forLoop;
  12.   SELECT 'AFTER LOOP';
  13. END; //
  15. greatsql> CALL sp_loop1() //
  16. +-------+-------+
  17. | rec.a | rec.b |
  18. +-------+-------+
  19. |     2 | row2  |
  20. +-------+-------+
  21. 1 row in set (0.00 sec)
  23. +------------+
  24. | AFTER LOOP |
  25. +------------+
  26. | AFTER LOOP |
  27. +------------+
  28. 1 row in set (0.00 sec)
  30. Query OK, 0 rows affected (0.00 sec)
    1. 示例2
  1. greatsql> SET sql_mode = ORACLE;
  2. greatsql> DELIMITER //
  4. greatsql> CREATE OR REPLACE PROCEDURE sp_loop2() AS
  5.   CURSOR cur1 IS SELECT a, b FROM t1 WHERE a>1;
  6. BEGIN
  7.   <<forLoop>>
  8.   FOR rec IN  cur1 LOOP
  9.     EXIT forLoop;   -- 一次都没循环成功，直接退出
  10.     SELECT rec.a ,rec.b; 
  11.   END LOOP forLoop;
  12.   SELECT 'AFTER LOOP';
  13. END; //
  15. greatsql> CALL sp_loop2() //
  16. +------------+
  17. | AFTER LOOP |
  18. +------------+
  19. | AFTER LOOP |
  20. +------------+
  21. 1 row in set (0.00 sec)
  23. Query OK, 0 rows affected (0.00 sec)
    1. 示例3
  1. greatsql> SET sql_mode = ORACLE;
  2. greatsql> DELIMITER //
  4. greatsql> CREATE OR REPLACE PROCEDURE sp_loop3() AS
  5.   CURSOR cur1 IS SELECT a, b FROM t1;
  6. BEGIN
  7.   <<forLoop>>
  8.   FOR rec IN  cur1 LOOP
  9.      SELECT rec.a ,rec.b; 
  10.      EXIT when cur1%NOTFOUND;
  11.   END LOOP forLoop;
  12.   SELECT 'AFTER LOOP';
  13. END; //
  15. greatsql> CALL sp_loop3() //
  16. +-------+-------+
  17. | rec.a | rec.b |
  18. +-------+-------+
  19. |     1 | row1  |
  20. +-------+-------+
  21. 1 row in set (0.00 sec)
  23. +-------+-------+
  24. | rec.a | rec.b |
  25. +-------+-------+
  26. |     2 | row2  |
  27. +-------+-------+
  28. 1 row in set (0.00 sec)
  30. +-------+-------+
  31. | rec.a | rec.b |
  32. +-------+-------+
  33. |     3 | row3  |
  34. +-------+-------+
  35. 1 row in set (0.01 sec)
  37. +------------+
  38. | AFTER LOOP |
  39. +------------+
  40. | AFTER LOOP |
  41. +------------+
  42. 1 row in set (0.01 sec)
  44. Query OK, 0 rows affected (0.01 sec)
    1. 示例4
  1. greatsql> SET sql_mode = ORACLE;
  2. greatsql> DELIMITER //
  4. greatsql> CREATE OR REPLACE PROCEDURE sp_loop4() AS
  5.   CURSOR cur1 IS SELECT a, b FROM t1;
  6. BEGIN
  7.   <<forLoop>>
  8.   FOR rec IN  cur1 LOOP
  9.     SELECT rec.a ,rec.b; 
  10.     EXIT forLoop when cur1%NOTFOUND;
  11.   END LOOP forLoop;
  12.   SELECT 'AFTER LOOP';
  13. END; //
  15. greatsql> CALL sp_loop4() //
  16. +-------+-------+
  17. | rec.a | rec.b |
  18. +-------+-------+
  19. |     1 | row1  |
  20. +-------+-------+
  21. 1 row in set (0.00 sec)
  23. +-------+-------+
  24. | rec.a | rec.b |
  25. +-------+-------+
  26. |     2 | row2  |
  27. +-------+-------+
  28. 1 row in set (0.00 sec)
  30. +-------+-------+
  31. | rec.a | rec.b |
  32. +-------+-------+
  33. |     3 | row3  |
  34. +-------+-------+
  35. 1 row in set (0.00 sec)
  37. +------------+
  38. | AFTER LOOP |
  39. +------------+
  40. | AFTER LOOP |
  41. +------------+
  42. 1 row in set (0.00 sec)
  44. Query OK, 0 rows affected (0.00 sec)

问题反馈

联系我们

扫码关注微信公众号

greatsql-wx