,Oracle9i對(duì)DML和DDL語(yǔ)句的擴(kuò)展,目標(biāo),通過(guò)本章學(xué)習(xí)，您將可以:描述多表插入的特點(diǎn)使用不同類型的多表插入無(wú)條件的INSERT旋轉(zhuǎn)INSERT有條件的ALLINSERT有條件的FIRSTINSERT創(chuàng)建和使用外部表創(chuàng)建主鍵約束的同時(shí)創(chuàng)建索引,INSERT語(yǔ)句,使用INSERT語(yǔ)句向表中插入新的數(shù)據(jù)使用上面的語(yǔ)句每次只能向表中插入一行數(shù)據(jù),INSERTINTOtable(column,column.)VALUES(value,value.);,INSERTINTOdepartments(department_id,department_name,manager_id,location_id)VALUES(70,PublicRelations,100,1700);1rowcreated.,UPDATE語(yǔ)句,使用UPDATE語(yǔ)句更新表中的數(shù)據(jù)使用上面的語(yǔ)句每次可更新表中的一行或多行數(shù)據(jù)使用WHERE子句指定更新的條件,UPDATEtableSETcolumn=value,column=value,.WHEREcondition;,UPDATEemployeesSETdepartment_id=70WHEREemployee_id=142;1rowupdated.,多表INSERT語(yǔ)句,INSERT.SELECT是使用一個(gè)DML語(yǔ)句向多個(gè)表中插入數(shù)據(jù)的一部分多表INSERT語(yǔ)句可作為數(shù)據(jù)倉(cāng)庫(kù)應(yīng)用中向目標(biāo)數(shù)據(jù)庫(kù)傳送數(shù)據(jù)的一種方法它具有更高的效率:避免使用多各DML語(yǔ)句使用一個(gè)DML完成IF.THEN的邏輯處理,多表INSERT語(yǔ)句的類型,Oracle9i提供以下四種多表INSERT語(yǔ)句類型:無(wú)條件的INSERT有條件的ALLINSERT有條件的FIRSTINSERT旋轉(zhuǎn)INSERT,多表INSERT語(yǔ)句,INSERTALLconditional_insert_clauseinsert_into_clausevalues_clause(subquery),ALLFIRSTWHENconditionTHENinsert_into_clausevalues_clauseELSEinsert_into_clausevalues_clause,conditional_insert_clause,語(yǔ)法,無(wú)條件的INSERTALL應(yīng)用舉例,INSERTALLINTOsal_historyVALUES(EMPID,HIREDATE,SAL)INTOmgr_historyVALUES(EMPID,MGR,SAL)SELECTemployee_idEMPID,hire_dateHIREDATE,salarySAL,manager_idMGRFROMemployeesWHEREemployee_id200;8rowscreated.,有條件的INSERTALL應(yīng)用舉例,INSERTALLWHENSAL10000THENINTOsal_historyVALUES(EMPID,HIREDATE,SAL)WHENMGR200THENINTOmgr_historyVALUES(EMPID,MGR,SAL)SELECTemployee_idEMPID,hire_dateHIREDATE,salarySAL,manager_idMGRFROMemployeesWHEREemployee_id200;4rowscreated.,有條件的FIRSTINSERT應(yīng)用舉例,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.,旋轉(zhuǎn)INSERT應(yīng)用舉例,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.,外部表,外部表是只讀的表，其數(shù)據(jù)存儲(chǔ)在數(shù)據(jù)庫(kù)外的平面文件中外部表的各種參數(shù)在CREATETABLE語(yǔ)句中指定使用外部表,數(shù)據(jù)可以存儲(chǔ)到外部文件或從外部文件中上載數(shù)據(jù)到數(shù)據(jù)庫(kù)數(shù)據(jù)可以使用SQL訪問(wèn),但不能使用DML后在外部表上創(chuàng)建索引,創(chuàng)建路徑,創(chuàng)建外部表之前應(yīng)先使用CREATEDIRECTORY語(yǔ)句創(chuàng)建路徑,CREATEDIRECTORYemp_dirAS/flat_files;,創(chuàng)建外部表舉例,CREATETABLEoldemp(empnoNUMBER,empnameCHAR(20),birthdateDATE)ORGANIZATIONEXTERNAL(TYPEORACLE_LOADERDEFAULTDIRECTORYemp_dirACCESSPARAMETERS(RECORDSDELIMITEDBYNEWLINEBADFILEbad_empLOGFILElog_empFIELDSTERMINATEDBY,(empnoCHAR,empnameCHAR,birthdateCHARdate_formatdatemaskdd-mon-yyyy)LOCATION(emp1.txt)PARALLEL5REJECTLIMIT200;Tablecreated.,查詢外部表,SELECT*FROMoldemp,emp1.txt,創(chuàng)建主鍵約束同時(shí)創(chuàng)建索引舉例,CREATETABLENEW_EMP(employee_idNUMBER(6)PRIMARYKEYUSINGINDEX(CREATEINDEXemp_id_idxONNEW_EMP(employee_id),first_nameVARCHAR2(20),last_nameVARCHAR2(25);Tablecreated.,SELECTINDEX_NAME,TABLE_NAMEFROM