Oracle兼容-存储过程-REF CURSOR, SYS_REFCURSOR

1. 语法

1. 1. var SYS_REFCURSOR

3. 2. TYPE type_name IS REF CURSOR

5. 3. OPEN cursor_name FOR {select_statement | dynamic_string}

7. 4. FOR rows IN
8. {
9. ( select_statement )
10. }
11. LOOP statement... END LOOP [label] ;

13. 5. CREATE [OR REPLACE] sp_name(var_list) RETURN SYS_REFCURSOR AS routine_body

15. 6. CREATE [OR REPLACE] sp_name(var_list) AS routine_body

17. 7. cursor1 := cursor2

19. 8. SELECT cursor1 INTO cursor2

2. 定义和用法

在 ORACLE 模式下，GreatSQL存储过程/存储函数支持以下几种 REF CURSOR, SYS_REFCURSOR 用法：

    1. 语法1：支持用 var SYS_REFCURSOR 语法来定义 SYS_REFCURSOR。
1. var SYS_REFCURSOR
    1. 语法2：支持用 TYPE ... IS REF CURSOR 语法定义 REF CURSOR。
1. TYPE type_name IS REF CURSOR
    1. 语法3：支持用 OPEN ... FOR 打开 REF CURSOR。
1. OPEN cursor_name FOR {select_statement | dynamic_string}
    1. 语法4：FOR rows IN (select_stmt) LOOP 会隐式创建 REF CURSOR，这里的 rows 不需要提前定义，且只在当前 FOR ... LOOP 语句块中有效。
1. FOR rows IN
2. {
3. ( select_statement )
4. }
5. LOOP statement... END LOOP [label] ;

更多关于 FOR rows IN CURSOR 用法参考：Oracle兼容-存储过程-游标（CURSOR）。

    1. 语法5：FUNCTION RETURN SYS_REFCURSOR 支持函数返回 REF CURSOR 类型数据。对于 FUNCTION 不支持变量中包含 SYS_REFCURSOR 类型参数。
1. CREATE [OR REPLACE] FUNCTION sp_name(var_list) RETURN SYS_REFCURSOR AS routine_body
    1. 语法6：支持存储过程的参数带 OUT SYS_REFCURSOR，并支持 REF CURSOR 类型返回结果。
1. CREATE [OR REPLACE] sp_name(var_list) AS routine_body
    1. 语法7：支持将 REF CURSOR 作为参数来赋值，二者享有共同的 REF CURSOR 状态。cursor1 和 cursor2 都是 REF CURSOR 类型，享有共同的 REF CURSOR 状态。如果其中一个重新 OPEN CURSOR FOR 或者 CLOSE CURSOR，那么会影响所有关联 REF CURSOR。如果只是其中一个被赋值，那么不会影响另外的关联 REF CURSOR，只会影响其他被赋值的 REF CURSOR（详见下方示例12）。
1. cursor1 := cursor2
    1. 语法8：采用 SELECT cursor1 INTO cursor2 语法就可以进行 REF CURSOR 赋值，二者享有共同的 REF CURSOR 状态。
1. SELECT cursor1 INTO cursor2

3. Oracle兼容说明

在 ORACLE 模式下，GreatSQL存储过程/存储函数中 REF CURSOR, SYS_REFCURSOR 用法与Oracle用法基本一致。

GreatSQL在兼容性差异主要有以下几点：

  1. 只支持在存储过程/存储函数内部中使用 SYS_REFCURSOR，也支持作为参数传入和输出。
  2. REF CURSOR 和 SYS_REFCURSOR 的参数也作为存储过程/存储函数的参数，因此不能再定义同名参数。
  3. 如果只定义了 REF CURSOR 或 SYS_REFCURSOR 而没有定义具体SQL语句，则这个游标无法使用。
  4. 语句 OPEN sp_name FOR 可以用在 LOOP .. END LOOP 语句块里。
  5. 支持在 OPEN sp_name FOR 之后无需先 CLOSE 而可以继续 OPEN sp_name FOR 再次使用，这点和 OPEN sp_name 用法不一样。
  6. 游标 cur1 没有定义的话，cur1%ISOPEN 依然可以被使用而不会报错；而 cur1%FOUND, cur1%NOTFOUND, cur1%ROWCOUNT 则会报错。
  7. 对于 CALL sp1(IN var_name) 中的参数 var_name 只能执行 FETCH 和 CLOSE 操作，不能进行赋值和 OPEN FOR 操作，这点与Oracle行为一致。
  8. 对于 CALL sp1(var_name) 中的 var_name 必须为REF CURSOR类型，否则会报错（详见下方示例11）。
  9. 如果被关联的游标关闭以后，在Oracle中该游标是不能再被打开只能被赋值使用；但在GreatSQL允许再次被打开使用，也可以再被赋值使用，这点二者不一样（详见下方示例13）。
  10. 在ORACLE模式下，SELECT 结果为 SYS_REFCURSOR 的存储函数结果的输出为 NULL (详见下方示例10)。

4. 示例

修改 sql_generate_invisible_primary_key 选项设定，因为下面案例中创建的表没有显式主键，关闭该选项可以避免自动创建隐式主键 my_row_id，可能会对下面的案例造成影响。

1. greatsql> SET SESSION sql_generate_invisible_primary_key = 0;

创建测试表并初始化数据

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：SYS_REFCURSOR, OPEN CURSOR
1. greatsql> SET sql_mode = ORACLE;
2. greatsql> DELIMITER //

4. greatsql> CREATE OR REPLACE PROCEDURE p1() AS
5. cur1 SYS_REFCURSOR;
6. sql1 VARCHAR(100);
7. rtype t1%ROWTYPE;
8. BEGIN
9. sql1 := 'SELECT * FROM t1';

11. OPEN cur1 FOR sql1;

13. LOOP
14. FETCH cur1 INTO rtype;
15. EXIT WHEN cur1%NOTFOUND;

17. SELECT rtype.a, rtype.b;
18. END LOOP;
19. END; //

21. greatsql> CALL p1() //
22. +---------+---------+
23. | rtype.a | rtype.b |
24. +---------+---------+
25. |       1 | row1    |
26. +---------+---------+
27. 1 row in set (0.00 sec)

29. +---------+---------+
30. | rtype.a | rtype.b |
31. +---------+---------+
32. |       2 | row2    |
33. +---------+---------+
34. 1 row in set (0.00 sec)

36. +---------+---------+
37. | rtype.a | rtype.b |
38. +---------+---------+
39. |       3 | row3    |
40. +---------+---------+
41. 1 row in set (0.00 sec)

43. Query OK, 0 rows affected (0.00 sec)
    1. 示例2：SYS_REFCURSOR, OPEN CURSOR FOR
1. greatsql> SET sql_mode = ORACLE;
2. greatsql> DELIMITER //

4. greatsql> CREATE OR REPLACE PROCEDURE p1() AS
5. cur1 SYS_REFCURSOR;
6. rtype t1%ROWTYPE;
7. BEGIN
8. OPEN cur1 FOR SELECT * FROM t1;

10. LOOP
11. FETCH cur1 INTO rtype;
12. EXIT WHEN cur1%NOTFOUND;

14. SELECT rtype.a, rtype.b;
15. END LOOP;
16. END; //

18. greatsql> CALL p1() //
19. +---------+---------+
20. | rtype.a | rtype.b |
21. +---------+---------+
22. |       1 | row1    |
23. +---------+---------+
24. 1 row in set (0.00 sec)

26. +---------+---------+
27. | rtype.a | rtype.b |
28. +---------+---------+
29. |       2 | row2    |
30. +---------+---------+
31. 1 row in set (0.00 sec)

33. +---------+---------+
34. | rtype.a | rtype.b |
35. +---------+---------+
36. |       3 | row3    |
37. +---------+---------+
38. 1 row in set (0.00 sec)

40. Query OK, 0 rows affected (0.00 sec)
    1. 示例3：FOR ident in (SELECT_stmt) LOOP
1. greatsql> SET sql_mode = ORACLE;
2. greatsql> DELIMITER //

4. CREATE OR REPLACE PROCEDURE p1() AS
5. BEGIN
6. --这里的rtype只在当前FOR LOOP内部使用，外面无法使用
7. FOR rtype in (SELECT * FROM t1) LOOP
8. SELECT rtype.a, rtype.b;
9. END LOOP;
10. END; //

12. greatsql> CALL p1() //
13. +-----------+-----------+
14. | rtype.a | rtype.b |
15. +-----------+-----------+
16. |         1 | aa        |
17. +-----------+-----------+
18. 1 row in set (0.02 sec)

20. +-----------+-----------+
21. | rtype.a | rtype.b |
22. +-----------+-----------+
23. |         2 | bb        |
24. +-----------+-----------+
25. 1 row in set (0.02 sec)

27. +-----------+-----------+
28. | rtype.a | rtype.b |
29. +-----------+-----------+
30. |         3 | cc        |
31. +-----------+-----------+
32. 1 row in set (0.02 sec)

34. Query OK, 0 rows affected (0.02 sec)
    1. 示例4：REF CURSOR
1. greatsql> SET sql_mode = ORACLE;
2. greatsql> DELIMITER //

4. CREATE OR REPLACE PROCEDURE p1() AS
5. TYPE dr_CURSOR IS REF CURSOR;
6. cur1 dr_CURSOR;
7. rtype t1%ROWTYPE;
8. BEGIN
9. -- 这里可以改写成sql1 := 'SELECT * FROM t1 WHERE a<3';
10. -- OPEN cur1 FOR sql1;sql1需要提前声明。
11. OPEN cur1 FOR SELECT * FROM t1 WHERE a<3;

13. LOOP
14. FETCH cur1 INTO rtype;
15. EXIT WHEN cur1%NOTFOUND;

17. SELECT rtype.a, rtype.b;
18. END LOOP;
19. END; //

21. greatsql> CALL p1() //
22. +-----------+-----------+
23. | rtype.a | rtype.b |
24. +-----------+-----------+
25. |         1 | aa        |
26. +-----------+-----------+
27. 1 row in set (0.01 sec)

29. +-----------+-----------+
30. | rtype.a | rtype.b |
31. +-----------+-----------+
32. |         2 | bb        |
33. +-----------+-----------+
34. 1 row in set (0.01 sec)

36. Query OK, 0 rows affected (0.01 sec)
    1. 示例5：duplicate var with declared and FOR LOOP
1. greatsql> SET sql_mode = ORACLE;
2. greatsql> DELIMITER //

4. use db1;
5. set sql_mode="oracle";
6. CREATE TABLE t1 (a INT, b VARCHAR(3));
7. INSERT INTO t1 VALUES(1,'aa'),(2,'bb'),(3,'cc') ;
8. DELIMITER //
9. CREATE OR REPLACE PROCEDURE p1() AS
10. rtype int;
11. BEGIN
12. FOR rtype in (SELECT * FROM t1) LOOP
13. SELECT rtype.a, rtype.b;
14. END LOOP;
15. SELECT rtype;
16. END; //

18. > CALL p1() //
    1. 示例6：FUNCTION RETURN SYS_REFCURSOR
1. greatsql> SET sql_mode = ORACLE;
2. greatsql> SET udt_format_result = 'DBA';
3. greatsql> DELIMITER //

5. greatsql> CREATE OR REPLACE FUNCTION returnaCURSOR RETURN SYS_REFCURSOR
6. AS
7. cur1 SYS_REFCURSOR;
8. BEGIN
9. OPEN cur1 FOR SELECT * FROM t1 WHERE a < 3;

11. RETURN cur1;
12. END; //

14. greatsql> CREATE OR REPLACE PROCEDURE p1() AS
15. a INT;
16. b VARCHAR(20);
17. c SYS_REFCURSOR;
18. BEGIN
19. c := returnaCURSOR();

21. LOOP
22. FETCH c INTO a, b;
23. EXIT WHEN c%NOTFOUND;
24. SELECT a, b;
25. END LOOP;

27. CLOSE c;
28. END; //

30. greatsql> CALL p1() //
31. +------+------+
32. | a    | b    |
33. +------+------+
34. |    1 | row1 |
35. +------+------+
36. 1 row in set (0.00 sec)

38. +------+------+
39. | a    | b    |
40. +------+------+
41. |    2 | row2 |
42. +------+------+
43. 1 row in set (0.00 sec)

45. Query OK, 0 rows affected (0.00 sec)
    1. 示例7：PROCEDURE WITH OUT SYS_REFCURSOR
1. greatsql> SET sql_mode = ORACLE;
2. greatsql> SET udt_format_result = 'DBA';
3. greatsql> DELIMITER //

5. greatsql> CREATE OR REPLACE PROCEDURE sp_return(ref_rs OUT SYS_REFCURSOR)
6. IS
7. sql1 VARCHAR(100);
8. BEGIN
9. sql1 := 'SELECT a, b FROM t1 WHERE a > 2';
10. OPEN ref_rs FOR sql1;
11. END; //

13. greatsql> CREATE OR REPLACE PROCEDURE p1() AS
14. a INT;
15. b VARCHAR(20);
16. c SYS_REFCURSOR;
17. BEGIN
18. CALL sp_return(c);

20. LOOP
21. FETCH c INTO a, b;
22. EXIT WHEN c%NOTFOUND;
23. SELECT a, b;
24. END LOOP;

26. CLOSE c;
27. END; //

29. greatsql> CALL p1() //
30. +------+------+
31. | a    | b    |
32. +------+------+
33. |    3 | row3 |
34. +------+------+
35. 1 row in set (0.00 sec)

37. Query OK, 0 rows affected (0.00 sec)
    1. 示例8：SET REF CURSOR
1. greatsql> SET sql_mode = ORACLE;
2. greatsql> SET udt_format_result = 'DBA';
3. greatsql> DELIMITER //

5. greatsql> CREATE OR REPLACE FUNCTION return_cur RETURN SYS_REFCURSOR
6. AS
7. cur1 SYS_REFCURSOR;
8. BEGIN
9. OPEN cur1 FOR SELECT * FROM t1 WHERE a < 3;

11. RETURN cur1;
12. END; //

14. greatsql> CREATE OR REPLACE PROCEDURE p1() AS
15. cur1 SYS_REFCURSOR;
16. cur2 SYS_REFCURSOR;
17. a INT;
18. b VARCHAR(20);
19. BEGIN
20. cur1 := return_cur();
21. cur2 := cur1;

23. LOOP
24. FETCH cur2 INTO a, b;
25. EXIT WHEN cur2%NOTFOUND;
26. SELECT a, b;
27. END LOOP;
28. CLOSE cur2;
29. END; //

31. greatsql> CALL p1() //
32. +------+------+
33. | a    | b    |
34. +------+------+
35. |    1 | row1 |
36. +------+------+
37. 1 row in set (0.00 sec)

39. +------+------+
40. | a    | b    |
41. +------+------+
42. |    2 | row2 |
43. +------+------+
44. 1 row in set (0.00 sec)

46. Query OK, 0 rows affected (0.00 sec)
    1. 示例9：SELECT REF CURSOR INTO REF CURSOR
1. greatsql> SET sql_mode = ORACLE;
2. greatsql> SET udt_format_result = 'DBA';
3. greatsql> DELIMITER //

5. greatsql> CREATE OR REPLACE FUNCTION return_cur RETURN SYS_REFCURSOR
6. AS
7. cur1 SYS_REFCURSOR;
8. BEGIN
9. OPEN cur1 FOR SELECT * FROM t1 WHERE a < 3;

11. RETURN cur1;
12. END; //

14. greatsql> CREATE OR REPLACE PROCEDURE p1() AS
15. cur1 SYS_REFCURSOR;
16. cur2 SYS_REFCURSOR;
17. a INT;
18. b VARCHAR(20);
19. BEGIN
20. cur1 := return_cur();
21. SELECT cur1 INTO cur2;
22. LOOP
23. FETCH cur2 INTO a, b;
24. EXIT WHEN cur2%NOTFOUND;
25. SELECT a, b;
26. END LOOP;
27. CLOSE cur2;
28. END; //

30. greatsql> CALL p1() //
31. +------+------+
32. | a    | b    |
33. +------+------+
34. |    1 | row1 |
35. +------+------+
36. 1 row in set (0.00 sec)

38. +------+------+
39. | a    | b    |
40. +------+------+
41. |    2 | row2 |
42. +------+------+
43. 1 row in set (0.00 sec)

45. Query OK, 0 rows affected (0.00 sec)
    1. 示例10：SELECT SYS_REFCURSOR FUNCTION
1. greatsql> SET sql_mode = ORACLE;
2. greatsql> SET udt_format_result = 'DBA';
3. greatsql> DELIMITER //

5. greatsql> CREATE OR REPLACE FUNCTION return_cur RETURN SYS_REFCURSOR
6. AS
7. cur1 SYS_REFCURSOR;
8. BEGIN
9. OPEN cur1 FOR SELECT 1, 'row1';

11. RETURN cur1;
12. END; //

14. greatsql> CREATE OR REPLACE PROCEDURE p1() AS
15. type dr_CURSOR IS REF CURSOR;
16. cur1 dr_CURSOR;
17. BEGIN
18. SELECT return_cur() FROM t1;
19. END; //

21. greatsql> CALL p1() //
22. ERROR 1235 (42000): This version of MySQL doesn't yet support 'REF CURSOR used in table'
    1. 示例11：参数返回
1. greatsql> SET sql_mode = ORACLE;
2. greatsql> SET udt_format_result = 'DBA';
3. greatsql> DELIMITER //

5. greatsql> CREATE OR REPLACE PROCEDURE sp_return(ref_rs OUT SYS_REFCURSOR) IS
6. sql1 VARCHAR(100);
7. BEGIN
8. sql1  := 'SELECT a, b FROM t1 WHERE a > 2';
9. OPEN ref_rs FOR sql1;
10. END; //

12. greatsql> CREATE OR REPLACE PROCEDURE p1() AS
13. a INT;
14. BEGIN
15. CALL sp_return(a);
16. END; //

18. greatsql> CALL p1() //
19. ERROR 7553 (HY000): inconsistent datatypes: udt type and non udt type
    1. 示例12：OPEN FOR 和 CLOSE 以及赋值对其他 REF CURSOR 的影响
1. greatsql> SET sql_mode = ORACLE;
2. greatsql> SET udt_format_result = 'DBA';
3. greatsql> DELIMITER //

5. -- 1、OPEN FOR的影响范围
6. greatsql> CREATE OR REPLACE FUNCTION return_cur RETURN SYS_REFCURSOR
7. AS
8. cur1 SYS_REFCURSOR;
9. BEGIN
10. OPEN cur1 FOR SELECT * FROM t1 WHERE a < 3;

12. RETURN cur1;
13. END; //

15. greatsql> CREATE OR REPLACE PROCEDURE p1() IS
16. v_a INT;
17. v_b VARCHAR(30);
18. cur1 SYS_REFCURSOR;
19. cur2 SYS_REFCURSOR;
20. BEGIN
21. cur1 := return_cur();
22. SELECT cur1 INTO cur2 FROM DUAL;

24. OPEN cur2 FOR SELECT * FROM t1 WHERE a = 1;
25. LOOP
26. FETCH cur1 INTO v_a, v_b;
27. EXIT WHEN cur1%NOTFOUND;
28. SELECT v_a, v_b;
29. END LOOP;
30. CLOSE cur2;
31. END; //

33. -- OPEN FOR以后影响了所有关联过的REF CURSOR
34. greatsql> CALL p1() //
35. +------+------+
36. | v_a  | v_b  |
37. +------+------+
38. |    1 | row1 |
39. +------+------+
40. 1 row in set (0.00 sec)

42. Query OK, 0 rows affected (0.00 sec)

44. -- 2、CLOSE的影响范围
45. -- 略过FUNCTION return_cur()的创建过程，复用上例结果
46. greatsql> CREATE OR REPLACE PROCEDURE p1() IS
47. a INT;
48. b VARCHAR(30);
49. cur1 SYS_REFCURSOR;
50. cur2 SYS_REFCURSOR;
51. BEGIN
52. cur1 := return_cur();
53. SELECT cur1 INTO cur2 FROM DUAL;
54. CLOSE cur2;   -- 在打开游标cur1之前关闭cur2，则cur1受到影响，如果放在FETCH cur1之后就可以
55. FETCH cur1 INTO a, b;
56. END; //

58. -- CLOSE以后影响了所有关联REF CURSOR
59. greatsql> CALL p1() //
60. ERROR 1324 (42000): Undefined CURSOR: cur1

62. -- 3、单纯set的影响范围
63. CREATE OR REPLACE FUNCTION return_cur1 RETURN SYS_REFCURSOR
64. AS
65. cur1 SYS_REFCURSOR;
66. BEGIN
67. OPEN cur1 FOR SELECT * FROM t1 WHERE a = 2;

69. RETURN cur1;
70. END; //

72. CREATE OR REPLACE PROCEDURE p1() IS
73. v_a INT;
74. v_b VARCHAR(30);
75. cur1 SYS_REFCURSOR;
76. cur2 SYS_REFCURSOR;
77. cur3 SYS_REFCURSOR;
78. BEGIN
79. cur1 := return_cur();

81. SELECT cur1 INTO cur2 FROM DUAL;
82. cur3 := cur2;
83. cur1 := return_cur1();

85. LOOP
86. FETCH cur3 INTO v_a, v_b;
87. EXIT WHEN cur3%NOTFOUND;

89. SELECT v_a, v_b;
90. END LOOP;

92. LOOP
93. FETCH cur1 INTO v_a, v_b;
94. EXIT WHEN cur1%NOTFOUND;
95. SELECT v_a, v_b;
96. END LOOP;
97. END; //

99. -- 从结果可以看到，最初的cur1被改变后，只影响了自己，没有影响被它赋值的cur2和cur3
100. greatsql> CALL p1() //
101. +------+------+
102. | v_a  | v_b  |
103. +------+------+
104. |    1 | row1 |
105. +------+------+
106. 1 row in set (0.00 sec)

108. +------+------+
109. | v_a  | v_b  |
110. +------+------+
111. |    2 | row2 |
112. +------+------+
113. 1 row in set (0.00 sec)

115. +------+------+
116. | v_a  | v_b  |
117. +------+------+
118. |    2 | row2 |
119. +------+------+
120. 1 row in set (0.00 sec)

122. Query OK, 0 rows affected (0.00 sec)
    1. 示例13：CLOSE REF CURSOR 对关联游标的影响
1. greatsql> SET sql_mode = ORACLE;
2. greatsql> SET udt_format_result = 'DBA';
3. greatsql> DELIMITER //

5. greatsql> CREATE OR REPLACE FUNCTION return_cur RETURN SYS_REFCURSOR
6. AS
7. cur1 SYS_REFCURSOR;
8. BEGIN
9. OPEN cur1 FOR SELECT * FROM t1 WHERE a < 3;

11. RETURN cur1;
12. END; //

14. greatsql> CREATE OR REPLACE PROCEDURE p1() IS
15. v_a INT;
16. v_b VARCHAR(30);
17. cur1 SYS_REFCURSOR;
18. cur2 SYS_REFCURSOR;
19. BEGIN
20. cur1 := return_cur();
21. SELECT cur1 INTO cur2 FROM DUAL;
22. CLOSE cur2;

24. -- 在GreatSQL中这里再次被OPEN是允许的，但在Oracle中会报告invalid CURSOR错误
25. OPEN cur1 FOR SELECT * FROM t1 WHERE a = 1;
26. LOOP
27. FETCH cur1 INTO v_a, v_b;
28. EXIT WHEN cur1%NOTFOUND;
29. SELECT v_a, v_b;
30. END LOOP;
31. END; //

33. greatsql> CALL p1() //
34. +------+------+
35. | v_a  | v_b  |
36. +------+------+
37. |    1 | row1 |
38. +------+------+
39. 1 row in set (0.00 sec)

41. Query OK, 0 rows affected (0.00 sec)
    1. 示例14：in SYS_REFCURSOR
1. greatsql> SET sql_mode = ORACLE;
2. greatsql> SET udt_format_result = 'DBA';
3. greatsql> DELIMITER //

5. greatsql> CREATE OR REPLACE PROCEDURE sp_return(ref_rs in SYS_REFCURSOR) IS
6. sql1 VARCHAR(100);
7. v_a INT;
8. v_b VARCHAR(30);
9. BEGIN
10. FETCH ref_rs INTO v_a, v_b;
11. SELECT v_a, v_b;
12. END //

14. greatsql> CREATE OR REPLACE PROCEDURE p1() AS
15. cur1 SYS_REFCURSOR;
16. cur2 SYS_REFCURSOR;
17. v_a INT;
18. v_b VARCHAR(20);
19. BEGIN
20. OPEN cur1 FOR SELECT * FROM t1;
21. CALL sp_return(cur1);

23. SELECT 'AFTER CALL sp_return';

25. LOOP
26. FETCH cur1 INTO v_a, v_b;
27. EXIT WHEN cur1%NOTFOUND;
28. SELECT v_a, v_b;
29. END LOOP;

31. CLOSE cur1;
32. END //

34. greatsql> CALL p1() //
35. +------+------+
36. | v_a  | v_b  |
37. +------+------+
38. |    1 | row1 |
39. +------+------+
40. 1 row in set (0.00 sec)

42. +----------------------+
43. | AFTER CALL sp_return |
44. +----------------------+
45. | AFTER CALL sp_return |
46. +----------------------+
47. 1 row in set (0.00 sec)

49. +------+------+
50. | v_a  | v_b  |
51. +------+------+
52. |    2 | row2 |
53. +------+------+
54. 1 row in set (0.00 sec)

56. +------+------+
57. | v_a  | v_b  |
58. +------+------+
59. |    3 | row3 |
60. +------+------+
61. 1 row in set (0.00 sec)

63. Query OK, 0 rows affected (0.00 sec)

问题反馈

联系我们

扫码关注微信公众号

greatsql-wx