Oracle兼容-存储过程-游标（CURSOR）

1. 语法

1. 1. [ FOR rows IN
2. { cursor [ ( cursor_parameter_dec
3. [ [,] cursor_parameter_dec ]... )]
4. | ( select_statement )
5. }
6. LOOP statement... END LOOP [label] ;

8. 2. FETCH CURSOR INTO var

10. 3. CURSOR cursor_name (column_name datatype) IS ...

12. 4. cursor_name CURSOR%ROWTYPE

14. 5. cursor_name TABLE%ROWTYPE

2. 定义和用法

GreatSQL在 ORACLE 模式下支持以下几种游标用法：

    1. 用法1：用 FOR ... IN CURSOR() LOOP ... END LOOP 语法循环读取数据到游标中。
1. [ FOR rows IN
2. { cursor [ ( cursor_parameter_dec
3. [ [,] cursor_parameter_dec ]... )]
4. | ( select_statement )
5. }
6. LOOP statement... END LOOP [label] ;
    1. 语法2：读取游标中的多列数据，并赋值给一个变量，实现 %ROWTYPE 功能。
1. FETCH CURSOR INTO var
    1. 语法3：支持定义游标时附带参数，并采用 OPEN CURSOR(var_list) 方式打开游标。
1. CURSOR cursor_name (column_name datatype) IS ...
    1. 语法4：支持参数定义为 CURSOR%ROWTYPE 类型，用于存放多列。
1. cursor_name CURSOR%ROWTYPE
    1. 用法5：支持参数定义为 TABLE%ROWTYPE 类型，用于存放多列。
1. cursor_name TABLE%ROWTYPE

3. Oracle兼容说明

GreatSQL在 ORACLE 模式下的游标在存储过程中读取数据的用法与Oracle基本一致，仅 %ROWCOUNT 还不支持。

在Oracle中支持用 FOR var_name IN cursor_name LOOP ... END LOOP 和 %ROWTYPE 语法进行游标取值，但在GreatSQL中的用法是 LOOP ... END LOOP。

其他关于游标用法详细描述见下：

  1. 支持用 DECLARE CURSOR c IS ... 和 DECLARE CURSOR(a INT) c IS ... 这两种用法来声明游标。
  2. 可以用 FOR var_name IN CURSOR LOOP 读取数据，游标后面带括号或者不带括号都可以。
  3. 支持用 CURSOR.column_name 来获取指定列数据。
  4. 支持用 CURSOR%FOUND、CURSOR%NOTFOUND、CURSOR%ISOPEN 判断游标的状态。
  5. 支持用 CURSOR%TYPE 来继承一个游标的 %ROWTYPE 类型。
  6. 支持用 TABLE%ROWTYPE 来获取一张已存在的表的结构。
  7. 支持带有 UDT 类型的表定义为游标。也可以 SELECT INTO 用于 UDT 字段。
  8. 在语句块 FOR var IN CURSOR() LOOP ... END LOOP 中的变量 var 只在 FOR LOOP ... END LOOP 这这个语句块范围内有效。

4. 示例

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

1. greatsql> SET SESSION sql_generate_invisible_primary_key = 0;

创建测试表并初始化数据

1. greatsql> DROP TABLE IF EXISTS t;
2. greatsql> CREATE TABLE IF NOT EXISTS t(id INT NOT NULL, c1 VARCHAR(100) NOT NULL);
3. greatsql> INSERT INTO t VALUES(1, 'row1'), (2, 'row2'), (3, 'row3');
    1. 示例1：FOR LOOP循环读取数据
1. greatsql> SET sql_mode = ORACLE;
2. greatsql> DELIMITER //

4. greatsql> CREATE OR REPLACE PROCEDURE sp1_cur_loop() AS
5. CURSOR cur1 IS SELECT * FROM t;
6. BEGIN
7. FOR rec IN cur1() LOOP
8. SELECT rec.id, rec.c1;
9. END LOOP;
10. END; //

12. greatsql> CALL sp1_cur_loop() //
13. +--------+--------+
14. | rec.id | rec.c1 |
15. +--------+--------+
16. |      1 | row1   |
17. +--------+--------+
18. 1 row in set (0.00 sec)

20. +--------+--------+
21. | rec.id | rec.c1 |
22. +--------+--------+
23. |      2 | row2   |
24. +--------+--------+
25. 1 row in set (0.00 sec)

27. +--------+--------+
28. | rec.id | rec.c1 |
29. +--------+--------+
30. |      3 | row3   |
31. +--------+--------+
32. 1 row in set (0.00 sec)

34. Query OK, 0 rows affected (0.00 sec)
    1. 示例2：CURSOR%ROWTYPE 继承数据类型
1. greatsql> SET sql_mode = ORACLE;
2. greatsql> DELIMITER //

4. greatsql> CREATE OR REPLACE PROCEDURE sp2_cur_rowtype() AS
5. CURSOR cur1 IS SELECT * FROM t;

7. rec1 cur1%ROWTYPE;   -- 用 CURSOR%ROWTYPE 继承数据类型
8. rec2 t.id%TYPE;      -- 用 TABLE%TYPE 继承数据类型
9. BEGIN
10. OPEN cur1;    -- 打开游标

12. LOOP        -- 循环游标，直至读取完数据
13. FETCH cur1 INTO rec1;
14. EXIT WHEN cur1%NOTFOUND;

16. rec2 := 1;    -- 对rec2变量单独赋值

18. SELECT rec1.id, rec1.c1, rec2;

20. END LOOP;
21. CLOSE cur1;
22. END; //

24. greatsql> CALL sp2_cur_rowtype() //
25. +---------+---------+------+
26. | rec1.id | rec1.c1 | rec2 |
27. +---------+---------+------+
28. |       1 | row1    |    1 |
29. +---------+---------+------+
30. 1 row in set (0.00 sec)

32. +---------+---------+------+
33. | rec1.id | rec1.c1 | rec2 |
34. +---------+---------+------+
35. |       2 | row2    |    1 |
36. +---------+---------+------+
37. 1 row in set (0.00 sec)

39. +---------+---------+------+
40. | rec1.id | rec1.c1 | rec2 |
41. +---------+---------+------+
42. |       3 | row3    |    1 |
43. +---------+---------+------+
44. 1 row in set (0.00 sec)

46. 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 sp3_cur_var() AS
5. vid INT;

7. -- 定义游标时带参数
8. CURSOR cur1 (vid INT) IS SELECT * FROM t WHERE id = vid;

10. rec1 cur1%ROWTYPE;
11. BEGIN
12. OPEN cur1(2);      -- 打开游标时，传递参数

14. LOOP
15. FETCH cur1 INTO rec1;
16. EXIT WHEN cur1%NOTFOUND;

18. SELECT rec1.id, rec1.c1;

20. END LOOP;

22. CLOSE cur1;
23. END; //

25. greatsql> CALL sp3_cur_var() //
26. +---------+---------+
27. | rec1.id | rec1.c1 |
28. +---------+---------+
29. |       2 | row2    |
30. +---------+---------+
31. 1 row in set (0.00 sec)

33. Query OK, 0 rows affected (0.00 sec)
    1. 示例4：支持表中包含 UDT 类型
1. greatsql> SET sql_mode = ORACLE;
2. greatsql> DELIMITER //

4. greatsql> CREATE OR REPLACE TYPE udt1 AS OBJECT(id INT, c1 VARCHAR(20));

6. greatsql> CREATE TABLE udt_t1(id INT,c2 udt1);

8. greatsql> INSERT INTO udt_t1 VALUES(1, udt1(10, 'c1_row10'));
9. greatsql> INSERT INTO udt_t1 VALUES(2, udt1(20, 'c1_row20'));

11. greatsql> SET udt_format_result = 'DBA';

13. greatsql> CREATE OR REPLACE PROCEDURE sp4_cur_udt() AS
14. CURSOR cur1 (vid INT) IS SELECT * FROM udt_t1 WHERE id = vid;
15. rec1 udt_t1.c2%TYPE;
16. BEGIN
17. FOR rec IN cur1(10) LOOP
18. SELECT rec.id, rec.c2;
19. SELECT rec.c2 INTO rec1;
20. SELECT rec1.id, rec1.c1;
21. END LOOP;

23. SELECT * FROM udt_t1;
24. END; //

26. greatsql> CALL sp4_cur_udt(); //
27. +------+---------------------+
28. | id   | c2                  |
29. +------+---------------------+
30. |    1 | id:10 | c1:c1_row10 |
31. |    2 | id:20 | c1:c1_row20 |
32. +------+---------------------+
33. 2 rows in set (0.00 sec)

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

问题反馈

联系我们

扫码关注微信公众号

greatsql-wx