ORACLE insert into table oracle学习

ORACLEinsertintotableoracle学习oracle中的insert语句在oracle中使用DML语言的insert语句来向表格中插入数据，先介绍每次只能插入一条数据的语法INSERTINTO表名(列名列表)VALUES(值列表);注意：当对表中所有的列进行赋值，那么列名列表可以省略，小括号也随之省略必须对表中的非空字段进行赋值具有默认值的字段可以不提供值，此时列名列表中的相应的列名也要省略举例：有如下表格定义createtablebook(bookidchar(10)notnull,namevarchar2(60),pricenumber(5,3))使用下面的语句来插入数据INSERTINTOBOOK(bookid,name,price)VALUES('100123','oraclesql',54.70);INSERTINTOBOOKVALUES('100123','oraclesql',54.70);INSERTINTOBOOK(bookid)VALUES('100123');由于bookid是非空，所以，对于book来说，至少要对bookid进行赋值，虽然这样的数据不完整如果想往一个表格中插入多条数据，那么带有values子句的insert就不行了，这时候必须使用insert语句和select语句进行配合来实现同时插入多条数据：例如：现在有一个空表a和一个有数据的表格b，他们的结构是一样,把b表中的所有数据插入到a表中的语句是：INSERTINTOA(列1，列2，列3)SELECT列1，列2，列3FROMB;--查询语句中可以使用任意复杂的条件或者子查询如果数据的来源不是现存表的数据，也想多条插入那么使用如下的方法：INSERTINTOtablename(列1，列2，列3，)SELECT值1，值2，值3FROMDUALUNIONSELECT值1，值2，值3FROMDUALUNIONSELECT值1，值2，值3FROMDUAL如果上面的值有字符和日期型数据，那么使用单引号即可，每一个select语句得到一条数据，然后使用集合操作符union把多条数据合并到一个结果集中，来实现一次插入多条数据的功能。引用要点：一条语句只能插入一张表中1行纪录一条语句可以插入多张表中，每张表中1行纪录如果想一条语句插入表中多行纪录，必须用这样的语句从别的表中选择数据才可以，insertinto.....select*from.....oracleinsert语句语法介绍：BasicInsertsSingleColumnTableOrViewINSERTINTOtable_name(column_name)VALUES(value);CREATETABLEstate(state_abbrevVARCHAR2(2));INSERTINTOstate(state_abbrev)VALUES('WA');COMMIT;SELECT*FROMstate;MultipleColumnTableOrView-AllColumnsINSERTINTOtable_nameVALUES(comma_separated_value_list);ALTERTABLEstateADD(state_nameVARCHAR2(30));INSERTINTOstate(state_abbrev,state_name)VALUES('OR','Oregon');COMMIT;SELECT*FROMstate;MultipleColumnTableOrView-NotAllColumnsINSERTINTOtable_name(comma_separated_column_name_list)VALUES(comma_separated_value_list);RENAMEstateTOstate_city;ALTERTABLEstate_cityADD(city_nameVARCHAR2(30));INSERTINTOstate_city(state_abbrev,city_name)VALUES('CA','SanFrancisco');COMMIT;SELECT*FROMstate_city;ProblemNotSpecifyingColumnNamesDemoINSERTINTOtable_name(comma_separated_column_name_list)VALUES(comma_separated_value_list);descstate_cityINSERTINTOstate_cityVALUES('NV','Nevada','LasVegas');descstate_cityINSERTSELECTInsertFromSELECTstatementINSERTINTOtable_nameSELECTStatement;CREATETABLEzip_new(zip_codeVARCHAR2(5)NOTNULL,state_abbrevVARCHAR2(2)NOTNULL,city_nameVARCHAR2(30));INSERTINTOzip_newSELECTzip_code,state_abbrev,city_nameFROMpostal_code;SELECT*FROMzip_new;RECORDINSERTInsertUsingARecordINSERTINTOtable_nameVALUESrecord_name;CREATETABLEtASSELECTtable_name,tablespace_nameFROMall_tables;SELECTCOUNT(*)FROMt;DECLAREtrect%ROWTYPE;BEGINtrec.table_name:='NEW';trec.tablespace_name:='NEW_TBSP';INSERTINTOtVALUEStrec;COMMIT;END;/SELECTCOUNT(*)FROMt;INSERTWHENDemoTablesCREATETABLEemp(empnoNUMBER(4)NOTNULL,enameVARCHAR2(10),jobVARCHAR2(9),mgrNUMBER(4),salNUMBER(7,2),deptnoNUMBER(2));CREATETABLEemp_10ASSELECT*FROMempWHERE1=0;CREATETABLEemp_20ASSELECT*FROMempWHERE1=0;CREATETABLEemp_30ASSELECT*FROMempWHERE1=0;CREATETABLEleftoverASSELECT*FROMempWHERE1=0;DemoDataINSERTINTOempVALUES(7369,'SMITH','CLERK',7902,800,20);INSERTINTOEMPVALUES(7499,'ALLEN','SALESMAN',7698,1600,30);INSERTINTOEMPVALUES(7521,'WARD','SALESMAN',7698,1250,30);INSERTINTOEMPVALUES(7566,'JONES','MANAGER',7839,2975,20);INSERTINTOEMPVALUES(7654,'MARTIN','SALESMAN',7698,1250,30);INSERTINTOEMPVALUES(7698,'BLAKE','MANAGER',7839,2850,30);INSERTINTOEMPVALUES(7782,'CLARK','MANAGER',7839,2450,10);INSERTINTOEMPVALUES(7788,'SCOTT','ANALYST',7566,3000,20);INSERTINTOEMPVALUES(7839,'KING','PRESIDENT',NULL,5000,10);INSERTINTOEMPVALUES(7844,'TURNER','SALESMAN',7698,1500,30);INSERTINTOEMPVALUES(7876,'ADAMS','CLERK',7788,1100,20);INSERTINTOEMPVALUES(7900,'JAMES','CLERK',7698,950,30);INSERTINTOEMPVALUES(7902,'FORD','ANALYST',7566,3000,60);INSERTINTOEMPVALUES(7934,'MILLER','CLERK',7782,1300,10);COMMIT;DemoInsertStatementINSERTWHEN(condition)THENINTOtable_name(column_list)VALUES(values_list)WHEN(condition)THENINTOtable_name(column_list)VALUES(values_list)ELSEINTOtable_name(column_list)VALUES(values_list)SELECTcolumn_listFROMtable_name;INSERTWHEN(deptno=10)THENINTOemp_10(empno,ename,job,mgr,sal,deptno)VALUES(empno,ename,job,mgr,sal,deptno)WHEN(deptno=20)THENINTOemp_20(empno,ename,job,mgr,sal,deptno)VALUES(empno,ename,job,mgr,sal,deptno)WHEN(deptno=30)THENINTOemp_30(empno,ename,job,mgr,sal,deptno)VALUES(empno,ename,job,mgr,sal,deptno)ELSEINTOleftover(empno,ename,job,mgr,sal,deptno)VALUES(empno,ename,job,mgr,sal,deptno)SELECT*FROMemp;SELECT*FROMemp_10;SELECT*FROMemp_20;SELECT*FROMemp_30;SELECT*FROMleftover;INSERTALLWithouttheWHENclauseINSERTALLperformsallinsertsunconditionallyINSERTALLINTOtable_nameVALUEScolumn_name_list)INTOtable_nameVALUEScolumn_name_list)...SELECTStatement;CREATETABLEap_cust(customer_idVARCHAR2(4),program_idVARCHAR2(3),del_dateDATE);CREATETABLEap_orders(order_dateDATE,program_idVARCHAR2(3));INSERTALLINTOap_custVALUES(customer_id,program_id,delivered_date)INTOap_ordersVALUES(order_date,program_id)SELECTprogram_id,delivered_date,customer_id,order_dateFROMairplanes;SELECT*FROMap_custWHERErownum1001;SELECT*FROMap_ordersWHERErownum1001;CREATETABLEt(pidNUMBER(5),fnameVA