Oracle9i-SQL-20 Oracle9i Extensions to DML and DDL

20Copyright©OracleCorporation,2001.Allrightsreserved.Oracle9iExtensionstoDMLandDDLStatements20-2Copyright©OracleCorporation,2001.Allrightsreserved.ObjectivesAftercompletingthislesson,youshouldbeabletodothefollowing:•Describethefeaturesofmultitableinserts•Usethefollowingtypesofmultitableinserts–UnconditionalINSERT–PivotingINSERT–ConditionalALLINSERT–ConditionalFIRSTINSERT•Createanduseexternaltables•Nametheindexatthetimeofcreatingaprimarykeyconstraint20-3Copyright©OracleCorporation,2001.Allrightsreserved.ReviewoftheINSERTStatement•AddnewrowstoatablebyusingtheINSERTstatement.•Onlyonerowisinsertedatatimewiththissyntax.INSERTINTOtable[(column[,column...])]VALUES(value[,value...]);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.ReviewoftheUPDATEStatement•ModifyexistingrowswiththeUPDATEstatement.•Updatemorethanonerowatatime,ifrequired.•SpecificroworrowsaremodifiedifyouspecifytheWHEREclause.UPDATEtableSETcolumn=value[,column=value,...][WHEREcondition];UPDATEtableSETcolumn=value[,column=value,...][WHEREcondition];UPDATEemployeesSETdepartment_id=70WHEREemployee_id=142;1rowupdated.1rowupdated.20-5Copyright©OracleCorporation,2001.Allrightsreserved.OverviewofMultitableINSERTStatements•TheINSERT...SELECTstatementcanbeusedtoinsertrowsintomultipletablesaspartofasingleDMLstatement.•MultitableINSERTstatementscanbeusedindatawarehousingsystemstotransferdatafromoneormoreoperationalsourcestoasetoftargettables.•Theyprovidesignificantperformanceimprovementover:–SingleDMLversusmultipleINSERT...SELECTstatements–SingleDMLversusaproceduretodomultipleinsertsusingIF...THENsyntax20-7Copyright©OracleCorporation,2001.Allrightsreserved.TypesofMultitableINSERTStatementsOracle9iintroducesthefollowingtypesofmultitableinsertstatements:•UnconditionalINSERT•ConditionalALLINSERT•ConditionalFIRSTINSERT•PivotingINSERT20-8Copyright©OracleCorporation,2001.Allrightsreserved.MultitableINSERTStatementsINSERT[ALL][conditional_insert_clause][insert_into_clausevalues_clause](subquery)[ALL][FIRST][WHENconditionTHEN][insert_into_clausevalues_clause][ELSE][insert_into_clausevalues_clause]conditional_insert_clauseSyntax20-10Copyright©OracleCorporation,2001.Allrightsreserved.UnconditionalINSERTALL•SelecttheEMPLOYEE_ID,HIRE_DATE,SALARY,andMANAGER_IDvaluesfromtheEMPLOYEEStableforthoseemployeeswhoseEMPLOYEE_IDisgreaterthan200.•InsertthesevaluesintotheSAL_HISTORYandMGR_HISTORYtablesusingamultitableINSERT.INSERTALLINTOsal_historyVALUES(EMPID,HIREDATE,SAL)INTOmgr_historyVALUES(EMPID,MGR,SAL)SELECTemployee_idEMPID,hire_dateHIREDATE,salarySAL,manager_idMGRFROMemployeesWHEREemployee_id200;8rowscreated.8rowscreated.20-11Copyright©OracleCorporation,2001.Allrightsreserved.ConditionalINSERTALL•SelecttheEMPLOYEE_ID,HIRE_DATE,SALARYandMANAGER_IDvaluesfromtheEMPLOYEEStableforthoseemployeeswhoseEMPLOYEE_IDisgreaterthan200.•IftheSALARYisgreaterthan$10,000,insertthesevaluesintotheSAL_HISTORYtableusingaconditionalmultitableINSERTstatement.•IftheMANAGER_IDisgreaterthan200,insertthesevaluesintotheMGR_HISTORYtableusingaconditionalmultitableINSERTstatement.20-12Copyright©OracleCorporation,2001.Allrightsreserved.ConditionalINSERTALLINSERTALLWHENSAL10000THENINTOsal_historyVALUES(EMPID,HIREDATE,SAL)WHENMGR200THENINTOmgr_historyVALUES(EMPID,MGR,SAL)SELECTemployee_idEMPID,hire_dateHIREDATE,salarySAL,manager_idMGRFROMemployeesWHEREemployee_id200;4rowscreated.4rowscreated.20-13Copyright©OracleCorporation,2001.Allrightsreserved.ConditionalFIRSTINSERT•SelecttheDEPARTMENT_ID,SUM(SALARY)andMAX(HIRE_DATE)fromtheEMPLOYEEStable.•IftheSUM(SALARY)isgreaterthan$25,000theninsertthesevaluesintotheSPECIAL_SAL,usingaconditionalFIRSTmultitableINSERT.•IfthefirstWHENclauseevaluatestotrue,thesubsequentWHENclausesforthisrowshouldbeskipped.•FortherowsthatdonotsatisfythefirstWHENcondition,insertintotheHIREDATE_HISTORY_00,orHIREDATE_HISTORY_99,orHIREDATE_HISTORYtables,basedonthevalueintheHIRE_DATEcolumnusingaconditionalmultitableINSERT.20-14Copyright©OracleCorporation,2001.Allrightsreserved.ConditionalFIRSTINSERTINSERTFIRSTWHENSAL25000THENINTOspecial_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.8rowscreated.20-15Copyright©OracleCorporation,2001.Allrightsreserved.PivotingINSERT•Supposeyoureceiveasetofsalesrecordsfromanonrelationaldatabasetable,SALES_SOURCE_DATAinthefollowingformat:EMPLOYEE_ID,WEEK_ID,SALES_MON,SALES_TUE,SALES_WED,SALES_THUR,SALES_FRI•YouwouldwanttostoretheserecordsintheSALES_INFOtableinamoretypicalrelationalformat:EMPLOYEE_ID,WEEK,SALES•UsingapivotingINSERT,convertthesetofsalesrecordsfromthenonrelationaldatabasetabletorelationa