快捷導(dǎo)航

ORACLE如何批量插入(Insert)

更新時(shí)間：2023年12月04日 09:52:20 作者：Immature_7

這篇文章主要介紹了ORACLE如何批量插入(Insert),具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教

ORACLE批量插入(Insert)

Oracle批量插入語(yǔ)句與其他數(shù)據(jù)庫(kù)不同，下面列出不同業(yè)務(wù)需求的插入

假設(shè)有一張表Student

-- 學(xué)生表
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的數(shù)量必須和column_value一致，并且數(shù)據(jù)類(lèi)型要相匹配

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語(yǔ)句，反正不加就報(bào)錯(cuò)

命令格式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