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