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