les20_oracle9i对dml和ddl语句的扩展

Copyright©OracleCorporation,2001.Allrightsreserved.Oracle9i对DML和DDL语句的扩展20-2Copyright©OracleCorporation,2001.Allrightsreserved.目标通过本章学习，您将可以:•描述多表插入的特点•使用不同类型的多表插入–无条件的INSERT–旋转INSERT–有条件的ALLINSERT–有条件的FIRSTINSERT•创建和使用外部表•创建主键约束的同时创建索引20-3Copyright©OracleCorporation,2001.Allrightsreserved.INSERT语句•使用INSERT语句向表中插入新的数据•使用上面的语句每次只能向表中插入一行数据INSERTINTOtable[(column[,column...])]VALUES(value[,value...]);INSERTINTOdepartments(department_id,department_name,manager_id,location_id)VALUES(70,'PublicRelations',100,1700);1rowcreated.20-4Copyright©OracleCorporation,2001.Allrightsreserved.UPDATE语句•使用UPDATE语句更新表中的数据•使用上面的语句每次可更新表中的一行或多行数据•使用WHERE子句指定更新的条件UPDATEtableSETcolumn=value[,column=value,...][WHEREcondition];UPDATEemployeesSETdepartment_id=70WHEREemployee_id=142;1rowupdated.20-5Copyright©OracleCorporation,2001.Allrightsreserved.多表INSERT语句•INSERT...SELECT是使用一个DML语句向多个表中插入数据的一部分•多表INSERT语句可作为数据仓库应用中向目标数据库传送数据的一种方法•它具有更高的效率:–避免使用多各DML语句–使用一个DML完成IF...THEN的逻辑处理20-6Copyright©OracleCorporation,2001.Allrightsreserved.多表INSERT语句的类型Oracle9i提供以下四种多表INSERT语句类型:•无条件的INSERT•有条件的ALLINSERT•有条件的FIRSTINSERT•旋转INSERT20-7Copyright©OracleCorporation,2001.Allrightsreserved.多表INSERT语句INSERT[ALL][conditional_insert_clause][insert_into_clausevalues_clause](subquery)[ALL][FIRST][WHENconditionTHEN][insert_into_clausevalues_clause][ELSE][insert_into_clausevalues_clause]conditional_insert_clause语法20-8Copyright©OracleCorporation,2001.Allrightsreserved.无条件的INSERTALL应用举例INSERTALLINTOsal_historyVALUES(EMPID,HIREDATE,SAL)INTOmgr_historyVALUES(EMPID,MGR,SAL)SELECTemployee_idEMPID,hire_dateHIREDATE,salarySAL,manager_idMGRFROMemployeesWHEREemployee_id200;8rowscreated.20-9Copyright©OracleCorporation,2001.Allrightsreserved.有条件的INSERTALL应用举例INSERTALLWHENSAL10000THENINTOsal_historyVALUES(EMPID,HIREDATE,SAL)WHENMGR200THENINTOmgr_historyVALUES(EMPID,MGR,SAL)SELECTemployee_idEMPID,hire_dateHIREDATE,salarySAL,manager_idMGRFROMemployeesWHEREemployee_id200;4rowscreated.20-10Copyright©OracleCorporation,2001.Allrightsreserved.有条件的FIRSTINSERT应用举例INSERTFIRSTWHENSAL25000THENINTOspecial_salVALUES(DEPTID,SAL)WHENHIREDATElike('%00%')THENINTOhiredate_history_00VALUES(DEPTID,HIREDATE)WHENHIREDATElike('%99%')THENINTOhiredate_history_99VALUES(DEPTID,HIREDATE)ELSEINTOhiredate_historyVALUES(DEPTID,HIREDATE)SELECTdepartment_idDEPTID,SUM(salary)SAL,MAX(hire_date)HIREDATEFROMemployeesGROUPBYdepartment_id;8rowscreated.20-11Copyright©OracleCorporation,2001.Allrightsreserved.旋转INSERT应用举例INSERTALLINTOsales_infoVALUES(employee_id,week_id,sales_MON)INTOsales_infoVALUES(employee_id,week_id,sales_TUE)INTOsales_infoVALUES(employee_id,week_id,sales_WED)INTOsales_infoVALUES(employee_id,week_id,sales_THUR)INTOsales_infoVALUES(employee_id,week_id,sales_FRI)SELECTEMPLOYEE_ID,week_id,sales_MON,sales_TUE,sales_WED,sales_THUR,sales_FRIFROMsales_source_data;5rowscreated.20-12Copyright©OracleCorporation,2001.Allrightsreserved.外部表•外部表是只读的表，其数据存储在数据库外的平面文件中•外部表的各种参数在CREATETABLE语句中指定•使用外部表,数据可以存储到外部文件或从外部文件中上载数据到数据库•数据可以使用SQL访问,但不能使用DML后在外部表上创建索引20-13Copyright©OracleCorporation,2001.Allrightsreserved.创建路径创建外部表之前应先使用CREATEDIRECTORY语句创建路径CREATEDIRECTORYemp_dirAS'/flat_files';20-14Copyright©OracleCorporation,2001.Allrightsreserved.创建外部表举例CREATETABLEoldemp(empnoNUMBER,empnameCHAR(20),birthdateDATE)ORGANIZATIONEXTERNAL(TYPEORACLE_LOADERDEFAULTDIRECTORYemp_dirACCESSPARAMETERS(RECORDSDELIMITEDBYNEWLINEBADFILE'bad_emp'LOGFILE'log_emp'FIELDSTERMINATEDBY','(empnoCHAR,empnameCHAR,birthdateCHARdate_formatdatemaskdd-mon-yyyy))LOCATION('emp1.txt'))PARALLEL5REJECTLIMIT200;Tablecreated.20-15Copyright©OracleCorporation,2001.Allrightsreserved.查询外部表SELECT*FROMoldempemp1.txt20-16Copyright©OracleCorporation,2001.Allrightsreserved.创建主键约束同时创建索引举例CREATETABLENEW_EMP(employee_idNUMBER(6)PRIMARYKEYUSINGINDEX(CREATEINDEXemp_id_idxONNEW_EMP(employee_id)),first_nameVARCHAR2(20),last_nameVARCHAR2(25));Tablecreated.SELECTINDEX_NAME,TABLE_NAMEFROMUSER_INDEXESWHERETABLE_NAME='NEW_EMP';20-17Copyright©OracleCorporation,2001.Allrightsreserved.总结通过本章学习,您已经可以:•使用多表插入代替多个单独的DML语句•创建外部表•创建主键约束同时创建索引