我要评论oracle批量插入(insert)
oracle批量插入语句与其他数据库不同，下面列出不同业务需求的插入
假设有一张表student
-- 学生表 create table student( id varchar2(11) primary key, name varchar2(32) not null, sex varchar2(3) not null, age smallint, tel varchar(16) )
注意：
其中[]中代表可选;<>代表必须;table_column的数量必须和column_value一致，并且数据类型要相匹配
1. 单条自定义记录插入
命令格式：
insert into table <tablename>[(<table_column1>,<table_column2>...)] values([<column_value1>,<column_value2>...])
示例：
insertinto student(id, name, sex, age, tel) values (‘13', ‘jack', ‘男', 13, ‘13345674567')
2. 多条自定义记录插入
命令格式1：
insert all
into <tablename>[(<table_column1>,<table_column2>...)]
values([<column_value1>,<column_value2>...])
[into <tablename>[(<table_column1>,<table_column2>...)]
values([<column_value1>,<column_value2>...])]...
select <table_value1>[,<table_value2>...] from dual;
示例：
insert all into student(id, name, sex, age, tel)
into student(id, name, sex, age, tel) values ('12', 'jack1', '男', 12, '13345674567' )
into student(id, name, sex, age, tel) values ('13', 'jack2', '男', 13, '13345674567')
select '14', 'jack', '男', 13, '13345674567' from dual;
注意: 我也不知道为什么要加select from dual语句，反正不加就报错
命令格式2：
insert into <tablename>[(<table_column1>,<table_column2>...)] select [<column_value1>,<column_value2>...] from dual [ union select [<column_value1>,<column_value2>...] from dual ]...
示例：
insert into student(id, name, sex, age, tel) select '10' , 'ldh', '男', 19, '14445674567' from dual union select '11' , 'zxy', '男', 20, '13333674567' from dual union select '12', 'zxc', '男', 21, '15555674567' from dual
命令格式3：
insert into <tablename1>[(<table_column1>,<table_column2>...)] select [<column_value1>,<column_value2>...] from <tablename2> [where [...]] union [ select [<column_value1>,<column_value2>...] from <tablename2> [where [...] ]]
示例：
insert into student(id, name, sex, age, tel) select (id-1)||'' as id, name, sex, age, tel from student where id='11' union select id||'1' as id, name, sex, age, tel from student where id like '1%' union select id||'2' as id, name, sex, age, tel from student where id like '%1' and id/3 != 0
总结
以上为个人经验，希望能给大家一个参考，也希望大家多多支持代码网。
发表评论