今天刚安装完数据库,所以要开始多加练习了,在插入数据时,由于本人比较懒,所以就想着看看oracle中有没有一次插入多条数据的指令。以下如有错,请见谅。
因为以前接触过SQLServer,又想着数据库的操作命令都差不多,所以第一次是采用了SQLserver的语句,结果报错了,如下:SQL> insert into class values('01','chinese','01','30'),('02','math','02','40');insert into class values('01','chinese','01','30'),('02','math','02','40')ERROR at line 1:ORA-00933: SQL command not properly ended
第二次采用的是百度找的办法:insert into tab_nameselect 值1,值2,... from dual union allselect 值1,值2,... from dual union all...select 值1,值2,... from dual ;
运行如下:SQL> insert into studentselect 6,xiaoqi,20,woman,chinese from dual union allselect 7,haha,21,man,chinese from deal;运行结果:
select 6,xiaoqi,20,woman,chinese from dual union allERROR at line 2:ORA-00904: "CHINESE": invalid identifier
报错原因是:无效的标识符,可是实际错误并不是如此,因此我再试了一次只添加一条语句:SQL> insert into student values('6','xiaoqi','20','woman','chinese');1 row created.
添加成功了,所以并不是chinese是无效标识符。
最后,请教别人的办法,虽然这样还是没有让我偷懒成功,但是运行速度却变快了,而且更容易修改,如下:[oracle@localhost ~]$ vi insert_data.sql[oracle@localhost ~]$ sqlplus 用户名/密码SQL*Plus: Release 11.2.0.1.0 Production on Wed Dec 14 17:01:26 2016Copyright (c) 1982, 2009, Oracle. All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> @insert_data.sql1 row created.1 row created.1 row created....1 row created.
添加成功了。
创建一个文件(文件名任意写),文件里面还是要严格按照oracle添加语句来写。虽说没有省事,但是运行速度快了而且文件里的东西修改更加方便。
文件里的添加语句如下:insert into class values('01','chinese','01','30');insert into class values('02','math','02','40');insert into class values('03','english','03','50');insert into class values('04','physics','04','60');insert into class values('05','computer','05','80');