Oracle9i-SQL-8 Manipulating Data

8Copyright©OracleCorporation,2001.Allrightsreserved.ManipulatingData8-2Copyright©OracleCorporation,2001.Allrightsreserved.ObjectivesAftercompletingthislesson,youshouldbeabletodothefollowing:•DescribeeachDMLstatement•Insertrowsintoatable•Updaterowsinatable•Deleterowsfromatable•Mergerowsinatable•Controltransactions8-3Copyright©OracleCorporation,2001.Allrightsreserved.DataManipulationLanguage•ADMLstatementisexecutedwhenyou:–Addnewrowstoatable–Modifyexistingrowsinatable–Removeexistingrowsfromatable•AtransactionconsistsofacollectionofDMLstatementsthatformalogicalunitofwork.8-4Copyright©OracleCorporation,2001.Allrightsreserved.AddingaNewRowtoaTableDEPARTMENTSNewrow……insertanewrowintotheDEPARMENTStable…8-5Copyright©OracleCorporation,2001.Allrightsreserved.TheINSERTStatementSyntax•AddnewrowstoatablebyusingtheINSERTstatement.•Onlyonerowisinsertedatatimewiththissyntax.INSERTINTOtable[(column[,column...])]VALUES(value[,value...]);INSERTINTOtable[(column[,column...])]VALUES(value[,value...]);8-6Copyright©OracleCorporation,2001.Allrightsreserved.InsertingNewRows•Insertanewrowcontainingvaluesforeachcolumn.•Listvaluesinthedefaultorderofthecolumnsinthetable.•Optionally,listthecolumnsintheINSERTclause.•Enclosecharacteranddatevalueswithinsinglequotationmarks.INSERTINTOdepartments(department_id,department_name,manager_id,location_id)VALUES(70,'PublicRelations',100,1700);1rowcreated.8-7Copyright©OracleCorporation,2001.Allrightsreserved.INSERTINTOdepartmentsVALUES(100,'Finance',NULL,NULL);1rowcreated.1rowcreated.INSERTINTOdepartments(department_id,department_name)VALUES(30,'Purchasing');1rowcreated.1rowcreated.InsertingRowswithNullValues•Implicitmethod:Omitthecolumnfromthecolumnlist.•Explicitmethod:SpecifytheNULLkeywordintheVALUESclause.8-8Copyright©OracleCorporation,2001.Allrightsreserved.INSERTINTOemployees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,commission_pct,manager_id,department_id)VALUES(113,'Louis','Popp','LPOPP','515.124.4567',SYSDATE,'AC_ACCOUNT',6900,NULL,205,100);1rowcreated.1rowcreated.InsertingSpecialValuesTheSYSDATEfunctionrecordsthecurrentdateandtime.8-9Copyright©OracleCorporation,2001.Allrightsreserved.INSERTINTOemployeesVALUES(114,'Den','Raphealy','DRAPHEAL','515.127.4561',TO_DATE('FEB3,1999','MONDD,YYYY'),'AC_ACCOUNT',11000,NULL,100,30);1rowcreated.1rowcreated.InsertingSpecificDateValues•Addanewemployee.•Verifyyouraddition.8-10Copyright©OracleCorporation,2001.Allrightsreserved.INSERTINTOdepartments(department_id,department_name,location_id)VALUES(&department_id,'&department_name',&location);CreatingaScript•Use&substitutioninaSQLstatementtopromptforvalues.•&isaplaceholderforthevariablevalue.1rowcreated.1rowcreated.8-11Copyright©OracleCorporation,2001.Allrightsreserved.•WriteyourINSERTstatementwithasubquery.•DonotusetheVALUESclause.•MatchthenumberofcolumnsintheINSERTclausetothoseinthesubquery.INSERTINTOsales_reps(id,name,salary,commission_pct)SELECTemployee_id,last_name,salary,commission_pctFROMemployeesWHEREjob_idLIKE'%REP%';4rowscreated.4rowscreated.CopyingRowsfromAnotherTable8-12Copyright©OracleCorporation,2001.Allrightsreserved.ChangingDatainaTableEMPLOYEESUpdaterowsintheEMPLOYEEStable.8-13Copyright©OracleCorporation,2001.Allrightsreserved.TheUPDATEStatementSyntax•ModifyexistingrowswiththeUPDATEstatement.•Updatemorethanonerowatatime,ifrequired.UPDATEtableSETcolumn=value[,column=value,...][WHEREcondition];UPDATEtableSETcolumn=value[,column=value,...][WHEREcondition];8-14Copyright©OracleCorporation,2001.Allrightsreserved.UPDATEemployeesSETdepartment_id=70WHEREemployee_id=113;1rowupdated.1rowupdated.•SpecificroworrowsaremodifiedifyouspecifytheWHEREclause.•AllrowsinthetablearemodifiedifyouomittheWHEREclause.UpdatingRowsinaTableUPDATEcopy_empSETdepartment_id=110;22rowsupdated.UPDATEcopy_empSETdepartment_id=110;22rowsupdated.22rowsupdated.8-15Copyright©OracleCorporation,2001.Allrightsreserved.UPDATEemployeesSETjob_id=(SELECTjob_idFROMemployeesWHEREemployee_id=205),salary=(SELECTsalaryFROMemployeesWHEREemployee_id=205)WHEREemployee_id=114;1rowupdated.1rowupdated.UpdatingTwoColumnswithaSubqueryUpdateemployee114’sjobandsalarytomatchthatofemployee205.8-16Copyright©OracleCorporation,2001.Allrightsreserved.UPDATEcopy_empSETdepartment_id=(SELECTdepartment_idFROMemployeesWHEREemployee_id=100)WHEREjob_id=(SELECTjob_idFROMemployeesWHEREemployee_id=200);1rowupdated.1rowupdated.UpdatingRowsBasedonAnotherTableUsesubqueriesinUPDATEstatementstoupdaterowsinatablebasedonvaluesfromanothertable.8-17Copyright©OracleCorporation,2001.Allrightsreserved.UPDATEemployees*ERRORatline1:ORA-02291:integrityconstraint(HR.EMP_DEPT_FK)violated-parentkeynotfoundUPDATEemployees*ERRORatline1:ORA-02291:integrityconstraint(HR.EMP_DEPT_FK)violated-parentkeynotfoundUPDATEemployeesSETdepartment_id=55WHEREdepartment_id=110;UPDATEemployeesSETdepartment_id=55WHEREdepartment_id=110;UpdatingRows:IntegrityConstraintErrorDepartmentnumber55doesnotexist8-18Copyright©OracleCorporation,2001.Allrightsreserved.DeletearowfromtheDEP