forupdate(of)串讲

《forupdate(of)串讲》由会员上传分享，免费在线阅读，更多相关内容在工程资料-天天文库。

1、FORUPDATE(OF)串讲摘录Oracle11g官方文档《OracleDatabaseSQLLanguageReference》对for_update_clause的说明：for_update_clauseTheFORUPDATEclauseletsyoulocktheselectedrowssothatotheruserscannotlockorupdatetherowsuntilyouendyourtransaction.Youcanspecifythisclauseonlyinatop-levelSELECTsta

2、tement,notinsubqueries.Note:PriortoupdatingaLOBvalue,youmustlocktherowcontainingtheLOB.OnewaytolocktherowiswithanembeddedSELECT...FORUPDATEstatement.YoucandothisusingoneoftheprogrammaticlanguagesorDBMS_LOBpackage.FormoreinformationonlockrowsbeforewritingtoaLOB,seeO

3、racleDatabaseSecureFilesandLargeObjectsDeveloper'sGuide.Nestedtablerowsarenotlockedasaresultoflockingtheparenttablerows.Ifyouwantthenestedtablerowstobelocked,thenyoumustlockthemexplicitly.RestrictionsontheFORUPDATEClauseThisclauseissubjecttothefollowingrestrictions

4、:■Youcannotspecifythisclausewiththefollowingotherconstructs:theDISTINCToperator,CURSORexpression,setoperators,group_by_clause,oraggregatefunctions.■ThetableslockedbythisclausemustallbelocatedonthesamedatabaseandonthesamedatabaseasanyLONGcolumnsandsequencesreference

5、dinthesamestatement.UsingtheFORUPDATEClauseonViewsIngeneral,thisclauseisnotsupportedonviews.However,insomecases,aSELECT...FORUPDATEqueryonaviewcansucceedwithoutanyerrors.Thisoccurswhentheviewhasbeenmergedtoitscontainingqueryblockinternallybythequeryoptimizer,andSEL

6、ECT...FORUPDATEsucceedsontheinternallytransformedquery.TheexamplesinthissectionillustratewhenusingtheFORUPDATEclauseonaviewcansucceedorfail.■UsingtheFORUPDATEclauseonmergedviewsAnerrorcanoccurwhenyouusetheFORUPDATEclauseonamergedviewifbothofthefollowingconditionsap

7、ply:–Theunderlyingcolumnoftheviewisanexpression–TheFORUPDATEclauseappliestoacolumnlistThefollowingstatementsucceedsbecausetheunderlyingcolumnoftheviewisnotanexpression:e.g1:SELECTemployee_idFROM(SELECT*FROMemployees)FORUPDATEOFemployee_id;Thefollowingstatementsucce

8、edsbecause,whiletheunderlyingcolumnoftheviewisanexpression,theFORUPDATEclausedoesnotapplytoacolumnlist:e.g2:SELECTemployee_idFROM(SELECTemployee_