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