#INSERT
# 通用描述
INSERT用于执行对数据库表的插入数据操作。
单机部署中，YashanDB支持对某一张表插入数据，也支持同时对多张表插入数据。
AC只支持后台生成数据，不支持通过INSERT插入数据。
在INSERT的事务（Transaction）被提交（Commit）之前，其他会话无法查询到这些新插入的数据。在YashanDB里，可以通过打开自动提交（SET AUTOCOMMIT ON）的开关，此时其他会话将可以及时查询到这些数据。
# 语句定义
insert::=
set_clause::=
# hint
该语句用于提出给定的方案到优化器（Optimizer ），使其按照此方案生成语句的执行计划。查看hint说明。
# single_table_insert
对单张表执行INSERT操作的语句。
# single_insert_into_clause
该语句用于指定要插入数据的表（包括本地数据库的表或者通过在表名后面加@database_link指定远程数据库的表，其中database_link为本地数据库到远程数据库建立的database link名字）及列字段，可为表定义一个别名。
# column_name
列字段，指定多个时以,分隔，也可全部省略，表示按表的列字段定义顺序插入所有列项。而在指定列字段时，对于非空且没有DEFAULT值的列字段必须指定。
在列字段上定义的所有约束，对插入的数据也会起到约束作用，如违反约束则插入数据失败并提示错误。
示例
--area表的DHQ列字段为非空项，提示错误
INSERT INTO area VALUES ('01','0000','');
YAS-04006 can not insert null value to column DHQ
--area表的DHQ列字段非空且定义了DEFAULT值，插入成功
INSERT INTO area (area_no) VALUES ('07');
--branches表的BRANCH_NAME列字段非空且无DEFAULT值，则必须指定该列插入，否则提示错误
INSERT INTO branches (branch_no) VALUES ('0202');
YAS-04006 can not insert null value to column BRANCH_NAME
# table_reference
该语句用于指定要插入数据的表（包括本地数据库的表或者通过在表名后面加@database_link指定远程数据库的表，其中database_link为本地数据库到远程数据库建立的database link名字）或表分区名称。
当该语句用于指定要插入记录的对象是通过@database_link指定远程数据库的表，有如下限制：
1.不支持多表insert语法。
2.不支持显示指定分区。
3.不支持insert duplicate update语法。
4.不支持insert return语法。
对于分区表，如未显式指定分区对象，由系统根据分区项字段的值判断要插入的表分区。而在显式指定时：
- 当分区表类型为RANGE时，新插入记录的分区项字段值请注意不要超出其所在分区的界值，否则会插入失败并提示错误。
- 当分区表类型为LIST时，新插入记录的分区项字段值请注意数据值在其所在分区的列表范围内，否则会插入失败并提示错误。
示例
--sales_info_range为一张范围分区表，已有p_sales_info_range_1（VALUES LESS THAN('2011')）、p_sales_info_range_2（VALUES LESS THAN('2021')）和p_sales_info_range_3（VALUES LESS THAN('2031')）三个分区
--未指定分区时，按分区项判断界值，插入数据成功
INSERT INTO sales_info_range VALUES ('2012','01','0103','11002',5,100,'');
--插入超过分区最大值的数据失败
INSERT INTO sales_info_range VALUES ('2040','01','0103','11002',5,100,'');
YAS-02115 partition key does not map to any partition
--'2012'与p1分区的界值冲突，插入数据失败
INSERT INTO sales_info_range PARTITION (p_sales_info_range_1) VALUES ('2012','01','0103','11002',5,100,'');
YAS-02120 the partition number is invalid or out-of-range
# partition_extension_clause
该语句用于指定分区或子分区。
指定分区或子分区的方式均有如下两种：
- 根据名称
- 根据提供的键值
PARTITION (partition)
该语句用于根据名称直接获取分区对象。
示例
--单机部署
INSERT INTO sales_info_range PARTITION (p_sales_info_range_3) VALUES ('2029','01','0103','11002',5,100,'');
PARTITION FOR (partition_key_value)
该语句用于根据提供的键值（Key Value）获取分区对象。此时会将键值与表分区界值比较，计算得到分区对象，键值可为界值范围内的任意值。键值的个数须与分区列个数对应，多项用,分隔。
示例
--单机部署
INSERT INTO sales_info_range PARTITION FOR('2015') VALUES ('2012','01','0103','11002',5,100,'');
SUBPARTITION (subpartition)
该语句用于根据名称直接获取子分区对象。
示例
--单机部署
CREATE TABLE sub_insert(year INT,name CHAR(20),age INT)
PARTITION BY LIST(year)
SUBPARTITION BY RANGE(age)
(PARTITION p1 VALUES(1)(SUBPARTITION sp1 VALUES LESS THAN(10),SUBPARTITION sp2 VALUES LESS THAN(20)),
PARTITION p2 VALUES(2)(SUBPARTITION sp3 VALUES LESS THAN(10),SUBPARTITION sp4 VALUES LESS THAN(20)));
INSERT INTO sub_insert SUBPARTITION(sp1) VALUES(1,'charley',6);
--分布式部署，本例中使用的子分区名为SYS_P6_SP_SALES_INFO_RANGE_1（由于分布式部署一级分区为hash分区，可能导致执行结果不同）
INSERT INTO sales_info_range SUBPARTITION(SYS_P6_SP_SALES_INFO_RANGE_1) VALUES ('2012','01','0103','11002',5,100,'');
SUBPARTITION FOR (subpartition_key_value)
该语句用于根据提供的键值（Key Value）获取子分区对象。此时会将键值与表分区界值比较，计算得到子分区对象，键值可为界值范围内的任意值。键值的个数须与分区列和子分区列个数对应，多项用,分隔。
示例
--单机部署
CREATE TABLE sub_insertfor(year INT,name CHAR(20),age INT)
PARTITION BY LIST(year)
SUBPARTITION BY RANGE(age)
(PARTITION p1 VALUES(1)(SUBPARTITION sp1 VALUES LESS THAN(10),SUBPARTITION sp2 VALUES LESS THAN(20)),
PARTITION p2 VALUES(2)(SUBPARTITION sp3 VALUES LESS THAN(10),SUBPARTITION sp4 VALUES LESS THAN(20)));
INSERT INTO sub_insertfor SUBPARTITION FOR(1,8) VALUES(1,'charley',6);
--分布式部署（由于分布式部署一级分区为hash分区，可能导致执行结果不同）
INSERT INTO sales_info_range SUBPARTITION FOR('11002','2018') VALUES ('2012','01','0103','11002',5,100,'');
# subquery
该语句用于指定要插入的数据从一个子查询中获得。插入对象的列项需与子查询的select_list列项按顺序一一对应。
查看SELECT文档中的subquery部分可获得子查询相关详细信息。
示例
INSERT INTO sales_info SELECT '2021','01',a.branch_no,'11002',100,1000,'' FROM branches a WHERE area_no ='02';
# insert_values_clause
该语句用于按single_insert_into_clause中定义的列字段一一指定对应的数据值。
YashanDB支持按如下语句对列字段赋值：
其中，当使用DEFAULT对列字段赋值时，如对应的列上已定义了DEFAULT值，则插入的数据为该DEFAULT值，否则为NULL，基于这个规则，如对应的列存在非空约束，则插入数据失败。
当表达式的结果与列字段定义的数据类型不一致时，系统会先进行数据类型转换，转换失败时则返回错误。
VALUES关键字后可以跟多组值，代表一条SQL语句可以插入多组值，这组值的上限为32768个。
示例
-area表上的DHQ字段已定义了DEFAULT值'ShenZhen',则按此值插入数据成功
INSERT INTO area VALUES ('08',33005,DEFAULT);
--branches表上的BRANCH_NAME字段非空且无DEFAULT值，则按NULL值插入数据失败
INSERT INTO BRANCHES VALUES ('0202',DEFAULT,'','02');
YAS-04006 can not insert null value to column BRANCH_NAME
--area表的area_no字段插入两组值，插入成功
INSERT INTO area (area_no) VALUES ('20'),('21');
--area表的area_no字段插入超过33824组值，失败报错
YAS-04816 insert values cannot exceed 32768 groups
# on_duplicate_clause
该语句用于指定当插入的数据导致表的唯一约束（包括唯一索引）冲突时，不报错，而是对冲突行使用set_clause执行更新。
当表中存在多个唯一约束时，系统将只针对第一个约束项（按约束项的声明顺序确定）所产生的冲突行进行本操作处理。
需注意的是，在分布式部署中不能对分布表一级分区键执行本语句操作，否则返回失败。
# set_clause
指定更新的字段和值，指定值的规则和要求与insert_values_clause一致，不满足要求时更新失败，系统提示错误。
示例1：单机部署中
--为area表的DHQ字段增加唯一约束，且在area_no上已存在主键约束
SELECT * FROM area;
AREA_NO AREA_NAME DHQ
------- ---------------- -------------
01 华东 Shanghai
02 华西 Chengdu
03 华南 Guangzhou
04 华北 Beijing
05 华中 Wuhan
ALTER TABLE area ADD CONSTRAINT c_area_unique UNIQUE (DHQ);
--插入的数据导致DHQ唯一约束冲突时，通过set_clause将冲突值修改，更新冲突行
INSERT INTO area VALUES('08',33005,'Shanghai') ON DUPLICATE KEY UPDATE DHQ = 'XiAn';
SELECT * FROM area;
AREA_NO AREA_NAME DHQ
------- ---------------- -------------
01 华东 XiAn
02 华西 Chengdu
03 华南 Guangzhou
04 华北 Beijing
05 华中 Wuhan
--area表的area_no和DHQ列均存在唯一约束，同时冲突时set_clause只作用于第一项约束的记录
INSERT INTO area VALUES('03',33006,'Chengdu') ON DUPLICATE KEY UPDATE DHQ = 'Shenzhen';
SELECT * FROM area;
AREA_NO AREA_NAME DHQ
------- ---------------- -------------
01 华东 XiAn
02 华西 Chengdu
03 华南 Shenzhen
04 华北 Beijing
05 华中 Wuhan
示例2：分布式部署中
--创建area0表，并为area表的DHQ字段增加唯一约束
CREATE DUPLICATED TABLE area0
(area_no CHAR(2) NOT NULL,
area_name VARCHAR2(60),
DHQ VARCHAR2(20) DEFAULT 'ShenZhen' NOT NULL UNIQUE);
INSERT INTO area0 VALUES ('01','华东','Shanghai');
INSERT INTO area0 VALUES ('02','华西','Chengdu');
INSERT INTO area0 VALUES ('03','华南','Guangzhou');
INSERT INTO area0 VALUES ('04','华北','Beijing');
INSERT INTO area0 VALUES ('05','华中','Wuhan');
--插入的数据导致DHQ唯一约束冲突时，通过set_clause将冲突值修改，更新冲突行
INSERT INTO area0 VALUES('08',33005,'Shanghai') ON DUPLICATE KEY UPDATE DHQ = 'XiAn';
SELECT * FROM area0;
AREA_NO AREA_NAME DHQ
------- ----------------------------- ---------------
01 华东 XiAn
02 华西 Chengdu
03 华南 Guangzhou
04 华北 Beijing
05 华中 Wuhan
# returning_clause
本语句表示在插入数据后返回一个结果集，并将结果集赋值给指定的变量。本语句只可与insert_values_clause结合使用。
expr
指定结果列，多列以,分隔，每一列为一个通用表达式，但不能为伪列、聚集函数和窗口函数。
variable
与expr一一对应的赋值变量，根据运用的语句不同，variable可以为一个已声明的变量，或者为一个绑定参数。
本语句可以在过程体或者驱动客户端程序中使用，PL/SQL手册的DML Statement和EXECUTE Statement中分别提供了该语句作为静态SQL和动态SQL在过程体中的使用示例。
# multi_table_insert
本语句适用于单机部署中的行存表，对一张或多张表同时进行INSERT操作，多张表的操作以空格分隔。
对所有表均可以指定按值插入（insert_values_clause），或是按子查询插入（subquery）。但是整个语句中最多只能有一个子查询，且subquery语句必须位于整个语句最后面。
该语句要求最后必须有一个子查询，当所有表都使用insert_values_clause时，可以在最后放入SELECT * FROM DUAL语句作为subquery。
若子查询返回结果集为空，则所有表都不执行插入。
# multi_insert_into_clause
除不可以为待插入的表指定别名外，该语句与single_insert_into_clause保持一致。
示例
--语句后面必须有subquery
INSERT ALL INTO area VALUES('00','unknown','unknown')
INTO branches VALUES('0002','南山','00','----')
INTO branches VALUES('0003','福田','00','----')
SELECT * FROM DUAL;
--subquery必须放在语句最后面
ROLLBACK;
INSERT ALL INTO area VALUES('00','unknown','unknown')
INTO branches
INTO branches VALUES('0003','福田','00','----')
SELECT '0002','南山','00',b.address FROM branches b WHERE b.branch_no='0101';
--当subquery查询结果为空时，所有数据均不会插入
ROLLBACK;
INSERT ALL INTO area VALUES('00','unknown','unknown')
INTO branches
INTO branches VALUES('0003','福田','00','----')
SELECT '0002','南山','00',b.address FROM branches b WHERE b.branch_no='0109';
下载文档
复制链接