Oracle兼容-存储过程-FORALL LOOP

1. 语法

1. FORALL var IN expr1 .. expr2 INSERT INTO .. VALUES var(n)

2. 定义和用法

在GreatSQL中支持用 FORALL .. LOOP 循环读取 expr1 和 expr2 之间的所有值，并再赋值给变量 var，之后再执行 INSERT INTO .. VALUES var(i) 写入数据。

3. Oracle兼容说明

在 ORACLE 模式下，GreatSQL存储过程支持 FORALL .. LOOP 用法。该用法如下所述：

  1. 在 FORALL .. LOOP 后面语句目前只支持 INSERT，别的用法未来版本中会增加支持。
  2. 支持用参数获取表中指定的数据，比如 n := 100; SELECT dr_table(n).col_name，同时还支持用表达式获取数据，比如 SELECT dr_table(n+1).col_name。
  3. 对于含有 UDT 字段的表，可以采用类似 t1%ROWTYPE 作为TABLE，此时可以单独查询 UDT 字段中的某个子列。
  4. 在 FORALL var IN expr 中的表达式 expr如果是 RECORD TABLE 那么必须 INDEX BY INT 这种数值类型的，如果是 INDEX BY VARCHAR 这种字符串类型就会报错。
  5. 在 FORALL 后面的变量 var 在其后的 INSERT 子句中必须作为 TABLE 类型变量的行数索引键值，否则会报错。

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> CREATE TABLE t2 (a INT NOT NULL, b VARCHAR(20) NOT NULL);

4. greatsql> INSERT INTO t1 VALUES(1, 't1_row1'), (2, 't1_row2'), (3,'t1_row3') ;
5. greatsql> INSERT INTO t2 VALUES(1, 't2_row1'), (2, 't2_row2'), (3,'t2_row3') ;
    1. 示例1：FORALL LOOP
1. greatsql> SET sql_mode = ORACLE;
2. greatsql> DELIMITER //

4. greatsql> CREATE OR REPLACE PROCEDURE p1() AS
5. TYPE udt1 IS TABLE OF t1%ROWTYPE INDEX BY BINARY_INTEGER;
6. udt_t1 udt1;
7. begin
8. udt_t1(100).a := 100;
9. udt_t1(100).b := 'udt_t1_row100';

11. SELECT  udt_t1(100).a, udt_t1(100).b;

13. -- 这里udt_t1.FIRST和udt_t1.LAST可以被替换成别的表达式，只要是数值类型并且 udt_t1(i) 有数据就可以
14. FORALL i IN udt_t1.FIRST .. udt_t1.LAST INSERT INTO t2 VALUES udt_t1 (i);
15. END; //

17. greatsql> CALL p1() //
18. +---------------+---------------+
19. | udt_t1(100).a | udt_t1(100).b |
20. +---------------+---------------+
21. |           100 | udt_t1_row100 |
22. +---------------+---------------+
23. 1 row in set (0.00 sec)

25. Query OK, 1 row affected (0.00 sec)

27. greatsql> SELECT * FROM t2; //
28. +-----+---------------+
29. | a   | b             |
30. +-----+---------------+
31. |   1 | t2_row1       |
32. |   2 | t2_row2       |
33. |   3 | t2_row3       |
34. | 100 | udt_t1_row100 |
35. +-----+---------------+
36. 4 rows in set (0.00 sec)
    1. 示例2：BULK COLLECT INTO AND FORALL
1. -- 在示例1的基础上继续
2. greatsql> SET sql_mode = ORACLE;
3. greatsql> DELIMITER //

5. greatsql> CREATE OR REPLACE PROCEDURE p1(v_a INT) AS
6. TYPE t1_record IS RECORD(
7. c1 INT := 1,
8. c2 VARCHAR(20)
9. );
10. TYPE t1_list IS TABLE OF t1_record INDEX BY BINARY_INTEGER;
11. t1_record_val t1_list;
12. CURSOR cur1(v_a INT) IS SELECT a, b FROM t1 WHERE a = v_a;
13. BEGIN
14. t1_record_val(1).c1 := 100;
15. SELECT t1_record_val(1).c1;

17. OPEN cur1(v_a);
18. FETCH cur1 BULK COLLECT INTO t1_record_val LIMIT 3;
19. FORALL i IN t1_record_val.FIRST .. t1_record_val.LAST
20. INSERT INTO t2 VALUES t1_record_val(i);
21. SELECT t1_record_val(1).c1;
22. CLOSE cur1;
23. END; //

25. greatsql> CALL p1(1) //
26. +---------------------+
27. | t1_record_val(1).c1 |
28. +---------------------+
29. |                 100 |
30. +---------------------+
31. 1 row in set (0.00 sec)

33. +---------------------+
34. | t1_record_val(1).c1 |
35. +---------------------+
36. |                   2 |
37. +---------------------+
38. 1 row in set (0.00 sec)

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

42. greatsql> SELECT * FROM t2 //
43. +-----+---------------+
44. | a   | b             |
45. +-----+---------------+
46. |   1 | t2_row1       |
47. |   2 | t2_row2       |
48. |   3 | t2_row3       |
49. | 100 | udt_t1_row100 |
50. |   2 | t1_row2       |
51. +-----+---------------+
52. 5 rows in set (0.00 sec)

更多关联用法：

问题反馈

联系我们

扫码关注微信公众号

greatsql-wx