INSERT¶
说明¶
向一个基本表或视图中插入数据
语法¶
insert ::=
insert_all ::=
insert_simply ::=
column_list ::=
values_list ::=
returning_clause ::=
参数¶
IGNORE
指定 IGNORE 关键字时在唯一索引(主键是特殊的唯一索引)中插入重复数据不报错。
table_name
一个现有的表或视图。
partition_clause
subpartition_clause
水平分区表的子表信息。详细含义参见 SELECT 语句。
column
要插入数据的表中的字段名。具体请参见注释中的“字段列表”及“示例”。
DEFAULT VALUES
DEFAULT
VALUES相当于一个包含表中所有字段值的列表，对于那些在定义时声明了缺省值的列使用缺省值，其它的列为NULL。若使用DEFAULT VALUES插入数据，则不能同时使用字段列表。
values_list
插入一行或同时插入多行数据的values列表。
Expr
single_row_expression
返回表中一行数据的表达式。
variable_name
存储所选列值的变量。
一个表达式，用于为新插入元组中对应的字段赋值。
select_stmt
一个SELECT语句，其结果将被插入到表table_name中。关于SELECT语句，其参见 SELECT 。
注解
INSERT语句用于向一个基本表或可更新视图中插入数据，关于视图是否可更新请参见 CREATE VIEW 。
当利用values_list分支进行多行插入时，每组VALUE节点的属性个数必须相同，expr仅支持常量、序列、函数及其组合成的表达式等。当column_list分支存在时，values_list中的每组VALUE中的属性个数必须和column_list中的字段个数相同；若cloumn_list分支不存在，则每组VALUE中的属性个数必须小于等于表的属性个数，每组VALUE的中的数据从左到右与表的对应列匹配。一行数据不满足插入条件，则整个多行插入语句执行失败，数据全部不插入。values_list的个数可以通过配置参数DB_MAX_INSERT_VALUES_NUM制定，默认个数为10000。INSERT VALUES多行插入操作不支持对视图插入。
在进行数据插入时可以使用一个字段列表，这有两个用途：
- 通过使用字段列表，在指定字段的值时就可以与它们在表定义中的顺序不同。
通过使用字段列表，可以只显式指定部分字段的值，对于那些不在字段列表中的字段，若定义时指定了缺省值，则使用缺省值，否则将被初始化为NULL。
在向一个基本表或视图中插入数据之前，用户必须拥有在该表或视图上的INSERT权限。关于权限管理，请参见 GRANT 与 REVOKE 。
每一个事务或会话第一次向临时表中插入数据时会创建一个与会话相关联的数据段，即临时表的实例化，通过视图v_sys_temp_instance可以查看当前所有的临时表实例。
RETURNING子句：
returning语句中single_row_expression可以有多列，即类似Returning column1, column2 Into var1, var2形式，暂不支持返回多行结果的表达式，不支持使用聚集函数的表达式。
single_row_expression的类型支持大对象(BLOB、CLOB)及普通数据类型，若为大对象类型则将loblocator（而非数据本身）返回给INTO后相应变量。
variable_name为存储列值的变量，可以是PL/SQL语句中的变量（如i），也可以是绑定参数的变量（如:var1）。
insert、delete、update语句中支持Returning功能，merge语句、EXECUTE IMMEDIATE子句不支持此功能。
支持在DELETE语句中使用DBLINK远端表。
示例¶
示例1: 插入数据(指定列名、插入默认值)
-- 清理环境
DROP TABLE tab1 CASCADE;
-- 创建表并插入数据
CREATE TABLE tab1(a INT, b INT DEFAULT 99, c VARCHAR(20));
SELECT * FROM tab1 ORDER BY a, b, c;
A(int) |B(int) |C(varchar) |
总数目：0
-- 插入数据(不指定列名，提供全部列数据)
INSERT INTO tab1 VALUES(1, 11, 'c');
SELECT * FROM tab1 ORDER BY a, b, c;
A(int) |B(int) |C(varchar) |
----------------------------------------
1 |11 |c |
总数目：1
-- 插入数据(不指定列名，提供部分列数据)
-- 默认从前向后为各列插入数据，缺少数据的列插入 默认值或 NULL
INSERT INTO tab1 VALUES(2);
SELECT * FROM tab1 ORDER BY a, b, c;
A(int) |B(int) |C(varchar) |
----------------------------------------
1 |11 |c |
----------------------------------------
2 |99 |null |
总数目：2
-- 插入数据(指定列名)
-- 未指定的列使用默认值或 NULL
INSERT INTO tab1(c) VALUES('ccc');
SELECT * FROM tab1 ORDER BY a, b, c;
A(int) |B(int) |C(varchar) |
----------------------------------------
1 |11 |c |
----------------------------------------
2 |99 |null |
----------------------------------------
null |99 |ccc |
总数目：3
-- 所有列都插入默认值
-- 没有默认值的列插入 NULL
INSERT INTO tab1 DEFAULT VALUES;
-- 删除表
DROP TABLE tab1;
示例2: 使用 values_list 插入多行数据
-- 清理环境
DROP TABLE tab2 CASCADE;
-- 创建表并插入数据
CREATE TABLE tab2(a INT, b INT DEFAULT 99, c VARCHAR(20));
SELECT * FROM tab2 ORDER BY a, b, c;
A(int) |B(int) |C(varchar) |
总数目：0
-- 使用 values_list 插入多行数据
INSERT INTO tab2 VALUES (1, 11, 'c'),
(2, 12, 'cc'),
(3, 13, 'ccc');
SELECT * FROM tab2 ORDER BY a, b, c;
A(int) |B(int) |C(varchar) |
----------------------------------------
1 |11 |c |
----------------------------------------
2 |12 |cc |
----------------------------------------
3 |13 |ccc |
总数目：3
-- 指定列名，使用 values_list 插入多行数据
INSERT INTO tab2(a, c) VALUES (4, 'cccc'),
(5, 'ccccc'),
(6, 'cccccc');
SELECT * FROM tab2 ORDER BY a, b, c;
A(int) |B(int) |C(varchar) |
----------------------------------------
1 |11 |c |
----------------------------------------
2 |12 |cc |
----------------------------------------
3 |13 |ccc |
----------------------------------------
4 |99 |cccc |
----------------------------------------
5 |99 |ccccc |
----------------------------------------
6 |99 |cccccc |
总数目：6
-- 使用 values_list 插入多行数据，只提供部分数据
INSERT INTO tab2 VALUES (7, 17),
(8, 18),
(9, 19);
SELECT * FROM tab2 ORDER BY a, b, c;
A(int) |B(int) |C(varchar) |
----------------------------------------
1 |11 |c |
----------------------------------------
2 |12 |cc |
----------------------------------------
3 |13 |ccc |
----------------------------------------
4 |99 |cccc |
----------------------------------------
5 |99 |ccccc |
----------------------------------------
6 |99 |cccccc |
----------------------------------------
7 |17 |null |
----------------------------------------
8 |18 |null |
----------------------------------------
9 |19 |null |
总数目：9
-- 使用 values_list 插入多行数据
-- 提供部分数据，但表达式个数不一致，插入失败
INSERT INTO tab2 VALUES (10),
(11, 21, 'ccccccccccccc'),
(12, 22);
ERROR, 每个values里的表达式个数不一致
SELECT * FROM tab2 ORDER BY a, b, c;
A(int) |B(int) |C(varchar) |
----------------------------------------
1 |11 |c |
----------------------------------------
2 |12 |cc |
----------------------------------------
3 |13 |ccc |
----------------------------------------
4 |99 |cccc |
----------------------------------------
5 |99 |ccccc |
----------------------------------------
6 |99 |cccccc |
----------------------------------------
7 |17 |null |
----------------------------------------
8 |18 |null |
----------------------------------------
9 |19 |null |
总数目：9
-- 删除表
DROP TABLE tab2;
示例3: 使用 SELECT 插入多行数据
-- 清理环境
DROP TABLE tab4 CASCADE;
DROP TABLE tab3 CASCADE;
-- 创建表并插入数据
CREATE TABLE tab3(a INT, b INT DEFAULT 99, c VARCHAR(20));
INSERT INTO tab3 VALUES (1, 11, 'c'),
(2, 12, 'cc'),
(3, 13, 'ccc');
SELECT * FROM tab3 ORDER BY a, b, c;
A(int) |B(int) |C(varchar) |
----------------------------------------
1 |11 |c |
----------------------------------------
2 |12 |cc |
----------------------------------------
3 |13 |ccc |
总数目：3
-- 创建表
CREATE TABLE tab4(m INT, n VARCHAR(20));
SELECT * FROM tab4 ORDER BY m, n;
M(int) |N(varchar) |
总数目：0
-- 使用 SELECT 插入多行数据
INSERT INTO tab4 SELECT a, c FROM tab3 WHERE a != 2;
SELECT * FROM tab4 ORDER BY m, n;
M(int) |N(varchar) |
----------------------------
1 |c |
----------------------------
3 |ccc |
总数目：2
-- 删除表
DROP TABLE tab4;
DROP TABLE tab3;
示例4: 使用 RETURNING INTO
-- 清理环境
DROP PROCEDURE proc4 CASCADE;
DEALLOCATE PREPARE pp4;
DROP TABLE tab4 CASCADE;
-- 创建表
CREATE TABLE tab4(a INT, b INT DEFAULT 99, c VARCHAR(20));
SELECT * FROM tab4 ORDER BY a, b, c;
A(int) |B(int) |C(varchar) |
总数目：0
-- 创建 INSERT 查询计划，使用 RETURNING INTO
PREPARE pp4 AS INSERT INTO tab4 VALUES (?, ?, ?) RETURNING b INTO ?;
-- 执行查询计划
EXECUTE pp4(1, 11, 'c', '');
EXECUTE pp4(2, 12, 'cc', '');
EXECUTE pp4(3, 13, 'ccc', '');
SELECT * FROM tab4 ORDER BY a, b, c;
A(int) |B(int) |C(varchar) |
----------------------------------------
1 |11 |c |
----------------------------------------
2 |12 |cc |
----------------------------------------
3 |13 |ccc |
总数目：3
-- PL/SQL 使用 RETURNING INTO
-- 创建存储过程
CREATE OR REPLACE PROCEDURE proc4 AS
v1 INT;
v2 VARCHAR(20);
BEGIN
INSERT INTO tab4 VALUES(4, 14, 'cccc')RETURN a, c INTO v1, v2;
DBMS_OUTPUT.PUT_LINE(v1);
DBMS_OUTPUT.PUT_LINE(v2);
end;
/
-- 执行存储过程，打印 NOTICE 信息(RETURN 变量 v1 、 v2 的值)
EXEC proc4;
SELECT * FROM tab4 ORDER BY a, b, c;
A(int) |B(int) |C(varchar) |
----------------------------------------
1 |11 |c |
----------------------------------------
2 |12 |cc |
----------------------------------------
3 |13 |ccc |
----------------------------------------
4 |14 |cccc |
总数目：4
-- 删除查询计划、存储过程和表
DROP PROCEDURE proc4;
DEALLOCATE PREPARE pp4;
DROP TABLE tab4;
示例5: 使用 INSERT ALL 插入多行数据
-- 准备环境
drop table tab5;
drop table tab5_2;
create table tab5(s_id int, s_name varchar(255));
create table tab5_2(s_id int, s_name varchar(255));
-- 测试
INSERT ALL
INTO tab5 (s_id, s_name) VALUES (1000, 'ZS')
INTO tab5 (s_id, s_name) VALUES (2000, 'LS')
INTO tab5 (s_id, s_name) VALUES (3000, 'WZ')
SELECT * FROM dual;
INSERT ALL
INTO tab5_2 (s_id, s_name) VALUES (1000, 'ZS')
INTO tab5_2 (s_id, s_name) VALUES (2000, 'LS')
INTO tab5_2 (s_id, s_name) VALUES (3000, 'WZ')
SELECT * FROM tab5;
select * from tab5;
S_ID(int) |S_NAME(varchar) |
------------------------------------
1000 |ZS |
------------------------------------
3000 |WZ |
------------------------------------
2000 |LS |
总数目：3
select * from tab5_2;
S_ID(int) |S_NAME(varchar) |
------------------------------------
1000 |ZS |
------------------------------------
3000 |WZ |
------------------------------------
2000 |LS |
总数目：3
-- 环境清理
drop table tab5;
drop table tab5_2;
示例6: 使用 INSERT IGNORE 向唯一索引中插入重复数据
-- 准备环境
drop table tab6 cascade;
--唯一性索引
create table tab6(a int ,b int,c int);
create unique index idx on tab6(a);
insert into tab6 values(1,1,1);
insert into tab6 values(2,2,2);
insert into tab6 values(1,10,10);
ERROR, 不能向索引IDX中插入重复键值(A) = (1)
--插入不报错，但实际数据并没有插入
insert ignore into tab6 values(1,10,10);
select * from tab6 order by a;
A(int) |B(int) |C(int) |
------------------------------------
1 |1 |1 |
------------------------------------
2 |2 |2 |
总数目：2
-- 环境清理
drop table tab6 cascade;