Oracle兼容-存储过程-BULK COLLECT

1. 语法

1. [SELECT|FETCH] .. BULK COLLECT INTO

2. 定义和用法

GreatSQL存储过程中支持用 SELECT|FETCH .. BULK COLLECT INTO 获取多行数据。该用法如下所述：

    1. 支持用 SELECT .. BULK COLLECT INTO 语法取表中多行数据，并赋值给 TABLE 类型变量。
    1. 支持用 FETCH .. BULK COLLECT INTO .. LIMIT n 语法取游标中的多行数据，并赋值给 TABLE 类型变量。

3. Oracle兼容说明

在 ORACLE 模式下，GreatSQL存储过程支持 [SELECT|FETCH] .. BULK COLLECT INTO 用法。该用法如下所述：

  1. 在 [SELECT|FETCH] .. BULK COLLECT INTO var 中的变量 var 只支持一层表类型，比如 var，不支持 a.b.var 这种变量类型。
  2. 在 FETCH .. BULK COLLECT INTO var 中如果不加 LIMIT n 子句，则默认一次性最多读取 @@select_bulk_into_batch 行数据，其可选范围 [1, 65535]，默认值为 10000。如果制定了 LIMIT n 子句，但 n > @@select_bulk_into_batch 时，会提示错误：ER_WRONG_BATCH_FOR_BULK_INTO，这是为了保证不发生内存溢出。
  3. 新增系统选项 select_bulk_into_batch 使用说明：
参数 说明
参数名称 select_bulk_into_batch
参数解释 FETCH .. BULK COLLECT INTO var 时一次性最多读取的行数限制
参数类型 INT
参数取值范围(行) [1, 65535]
默认值(行) 10000
建议最佳数值(行) 1000
使用示例 SET @@select_bulk_into_batch = n; SHOW VARIABLES LIKE ‘select_bulk_into_batch’;

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') ;

4. greatsql> CREATE TABLE t2 (a INT NOT NULL, b VARCHAR(20) NOT NULL);
    1. 示例1：SELECT BULK COLLECT
1. greatsql> SET sql_mode = ORACLE;
2. greatsql> DELIMITER //

4. CREATE OR REPLACE PROCEDURE bulk_sp1() AS
5. TYPE udtt IS TABLE OF t1%ROWTYPE INDEX BY BINARY_INTEGER;
6. udtt_t1 udtt;
7. BEGIN
8. SELECT * BULK COLLECT INTO udtt_t1 FROM t1;
9. FOR i IN udtt_t1.FIRST .. udtt_t1.LAST LOOP
10. SELECT udtt_t1(i).a, udtt_t1(i).b;
11. END LOOP;
12. END; //

14. greatsql> CALL bulk_sp1() //
15. +--------------+--------------+
16. | udtt_t1(i).a | udtt_t1(i).b |
17. +--------------+--------------+
18. |            1 | row1         |
19. +--------------+--------------+
20. 1 row in set (0.00 sec)

22. +--------------+--------------+
23. | udtt_t1(i).a | udtt_t1(i).b |
24. +--------------+--------------+
25. |            2 | row2         |
26. +--------------+--------------+
27. 1 row in set (0.00 sec)

29. +--------------+--------------+
30. | udtt_t1(i).a | udtt_t1(i).b |
31. +--------------+--------------+
32. |            3 | row3         |
33. +--------------+--------------+
34. 1 row in set (0.00 sec)

36. Query OK, 0 rows affected (0.00 sec)
    1. 示例2：FETCH BULK COLLECT
1. greatsql> SET sql_mode = ORACLE;
2. greatsql> DELIMITER //

4. greatsql> CREATE OR REPLACE PROCEDURE bulk_sp2(v_a INT) AS
5. TYPE t1_record IS RECORD(
6. id INT := 1,
7. name_d VARCHAR(20)
8. );
9. TYPE t1_list IS TABLE OF t1_record INDEX BY BINARY_INTEGER;
10. t1_record_val t1_list;

12. CURSOR c(v_a INT) IS SELECT a, b FROM t1 WHERE a = v_a;
13. BEGIN
14. OPEN c(v_a);

16. -- 这里实际只获取到两行数据，而LIMIT限定为3，不会报错
17. FETCH c BULK COLLECT  INTO t1_record_val LIMIT 3;

19. -- 目前FORALL后面只支持INSERT操作
20. FORALL i IN t1_record_val.FIRST .. t1_record_val.LAST
21. INSERT INTO t2 VALUES t1_record_val(i);

23. CLOSE c;
24. END; //

26. greatsql> CALL bulk_sp2(1) //
27. Query OK, 1 row affected (0.00 sec)

29. greatsql> SELECT * FROM t2 //
30. +---+------+
31. | a | b    |
32. +---+------+
33. | 1 | row1 |
34. +---+------+
35. 1 row in set (0.01 sec)
    1. 示例3：FETCH BULK COLLECT INTO batch
1. greatsql> SET sql_mode = ORACLE;
2. greatsql> SET select_bulk_into_batch = 5;
3. greatsql> DELIMITER //

5. greatsql> CREATE OR REPLACE PROCEDURE bulk_sp3() AS
6. CURSOR cur1 IS SELECT * FROM t1;
7. TYPE udtt IS TABLE OF t1%ROWTYPE INDEX BY BINARY_INTEGER;
8. udtt_t1 udtt;
9. v_i INT := 0;
10. BEGIN
11. OPEN cur1;
12. LOOP
13. SELECT v_i;

15. -- 因为fetch只会执行2次，因此第三次就可以退出了。
16. EXIT WHEN v_i % @@select_bulk_into_batch > 1;

18. FETCH cur1 BULK COLLECT INTO udtt_t1;

20. FOR i IN udtt_t1.FIRST .. udtt_t1.LAST LOOP
21. SELECT udtt_t1(i).a, udtt_t1(i).b;

23. END LOOP;

25. v_i := v_i + 1;
26. END LOOP;
27. CLOSE cur1;
28. END; //

30. greatsql> CALL bulk_sp3() //
31. +------+
32. | v_i  |
33. +------+
34. |    0 |
35. +------+
36. 1 row in set (0.00 sec)

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

45. +--------------+--------------+
46. | udtt_t1(i).a | udtt_t1(i).b |
47. +--------------+--------------+
48. |            2 | row2         |
49. +--------------+--------------+
50. 1 row in set (0.00 sec)

52. +--------------+--------------+
53. | udtt_t1(i).a | udtt_t1(i).b |
54. +--------------+--------------+
55. |            3 | row3         |
56. +--------------+--------------+
57. 1 row in set (0.00 sec)

59. +------+
60. | v_i  |
61. +------+
62. |    1 |
63. +------+
64. 1 row in set (0.00 sec)

66. +------+
67. | v_i  |
68. +------+
69. |    2 |
70. +------+
71. 1 row in set (0.00 sec)

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

更多关联用法：

问题反馈

联系我们

扫码关注微信公众号

greatsql-wx