ORACLE如何批量插入(Insert)


    目录
  • ORACLE批量插入(Insert)
  • 1. 单条自定义记录插入
  • 2. 多条自定义记录插入
  • 总结

    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

    总结
    以上为个人经验，希望能给大家一个参考，也希望大家多多支持电脑手机教程网。
相关文章！