Oracle兼容-语法-INSERT ALL

1. 语法

1. INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY]
2. { ALL insert_into_clause ... | conditional_insert_clause }
3. {SELECT ...  }

5. insert_into_clause:
6. INTO tbl_name
7. [PARTITION (partition_name [, partition_name] ...)]
8. [(col_name [, col_name] ...)]
9. [{ VALUES | VALUE} (value_list)]

11. conditional_insert_clause:
12. { ALL | FIRST } when_insert_clause ... [ else_insert_clause ]

14. when_insert_clause:
15. WHEN condition THEN insert_into_clause ...

17. else_insert_clause:
18. ELSE insert_into_clause ...

20. value:
21. {expr | DEFAULT}

23. value_list:
24. value [, value] ...

27. EXPLAIN insert_all_stmt

2. 定义和用法

在多表插入中，INSERT ALL INTO支持从子查询计算返回的行派生的计算行插入到一个或多个表中。

  1. 支持插入不同表。
  2. 支持条件查询插入。

INSERT ALL INTO使用限制：

  1. 插入的目标只能是表，不能是视图，不能是集合表达式
  2. 插入结束后，函数 LAST_INSERT_ID() 返回值为0，避免多表插入歧义。
  3. 运行EXPLAIN FORMAT=JSON 查看执行计划的输出结果只会展示插入第一张表。

3. 示例

1. greatsql> CREATE TABLE t3(id INT PRIMARY KEY, cc VARCHAR(10));
2. greatsql> CREATE TABLE t2 LIKE t3;
3. greatsql> CREATE TABLE t1 LIKE t3;
4. greatsql> INSERT INTO t3 VALUES(1, 'test1'),(2,'test2'), (3,'test3');

6. greatsql> INSERT ALL
7. INTO t1
8. INTO t2
9. SELECT * FROM t3;
10. Query OK, 6 rows affected (0.45 sec)
11. Records: 6  Duplicates: 0  Warnings: 0

13. greatsql> INSERT ALL
14. INTO t1(id) VALUES (id+20)
15. INTO t2(cc,id) VALUES ('test',id+10)
16. SELECT id FROM t3;
17. Query OK, 6 rows affected (0.05 sec)
18. Records: 6  Duplicates: 0  Warnings: 0

20. greatsql> INSERT ALL
21. INTO t1 (id,cc) VALUES(13,'test13')
22. INTO t1 (id,cc) VALUES(12,'test12')
23. SELECT 1 FROM DUAL;
24. Query OK, 2 rows affected (0.06 sec)
25. Records: 2  Duplicates: 0  Warnings: 0

27. greatsql> SELECT * FROM t2;
28. +----+-------+
29. | id | cc    |
30. +----+-------+
31. |  1 | test1 |
32. |  2 | test2 |
33. |  3 | test3 |
34. | 11 | test  |
35. | 12 | test  |
36. | 13 | test  |
37. +----+-------+

39. greatsql> SELECT * FROM t1;
40. +----+--------+
41. | id | cc     |
42. +----+--------+
43. |  1 | test1  |
44. |  2 | test2  |
45. |  3 | test3  |
46. | 12 | test12 |
47. | 13 | test13 |
48. | 21 | NULL   |
49. | 22 | NULL   |
50. | 23 | NULL   |
51. +----+--------+

问题反馈

联系我们

扫码关注微信公众号

greatsql-wx