仓酷云

标题: MSSQL教程之Building a T-SQL Loop [打印本页]

作者: 小女巫 时间: 2015-1-16 22:35
标题: MSSQL教程之Building a T-SQL Loop
对于insert和delete，event中包含了插入/删除的记录的所有字段的值(太爽了。。)November5,2003
T-SQLProgrammingPart2-BuildingaT-SQLLoop
ByGregoryA.Larsen

ThisisthesecondarticleinmyT-SQLprogrammingseries.ThisarticlewilldiscussbuildingaprogramloopusingT-SQL.Inadditiontotalkingaboutbuildingaloop,Iwillalsodiscusswaysofcontrollingtheloopprocessing,anddifferentmethodstobreakoutofaloop.

Aprogrammingloopisachunkofcodethatisexecutedoverandoveragain.Intheloopsomelogicisexecutedrepeatedlyinaniterativefashionuntilsomeconditionismetthatallowsthecodetobreakoutoftheloop.Oneexampleofwhereyoumightusealoopwouldbetoprocessthroughasetofrecordsonerecordatatime.Anotherexamplemightbewhereyouneedtogeneratesometestdataandaloopwouldallowyoutoinsertarecordintoyourtestdatatablewithslightlydifferentcolumnvalues,eachtimetheloopisexecuted.InthisarticleIwilldiscusstheWHILE,BREAK,CONTINUE,andGOTOstatements.
WHILEStatement
InT-SQLtheWHILEstatementisthemostcommonlyusedwaytoexecutealoop.HereisthebasicsyntaxforaWHILEloop:

WHILE<Booleanexpression><codeblock>

Wherea<Booleanexpression>isanyexpressionthatequatestoatrueorfalseanswer,andthe<codeblock>isthedesirecodetobeexecutedwhilethe<Booleanexpression>istrue.Letsgothrougharealsimpleexample.

InthisexampleIwillincrementacounterfrom1to10anddisplaythecountereachtimethroughtheWHILEloop.

declare@counterintset@counter=0while@counter<10beginset@counter=@counter+1printThecounteris+cast(@counteraschar)end

HerethecodeexecutestheWHILEstatementaslongasthe@counterintegervariableislessthan10,thisistheBooleanexpressionoftheWHILEloop.The@countervariablestartsoutatzero,andeachtimethroughtheWHILEloopitisincrementedby1.ThePRINTstatementdisplaysthevalueinthe@countervariableeachtimethroughtheWHILEloop.Theoutputfromthissamplelookslikethis:

Thecounteris1Thecounteris2Thecounteris3Thecounteris4Thecounteris5Thecounteris6Thecounteris7Thecounteris8Thecounteris9Thecounteris10

Asyoucansee,oncethe@countervariablereaches10theBooleanexpressionthatiscontrollingtheWHILEloopisnolongertrue,sothecodewithinthewhileloopisnolongerexecuted.

Notonlycanyouhaveasinglewhileloop,butyoucanhaveWHILEloopsinsideWHILEloops.OrcommonlyknowasnestingofWHILEloops.Therearelotsofdifferentuseswherenestingisvaluable.IcommonlyusenestingofWHILEloopstogeneratetestdata.MynextexamplewillusetheWHILElooptogeneratetestrecordsforaPARTtable.AgivenPARTrecordisuniquelyidentifiedbyaPart_Id,andaCategory_Id.ForeachPart_IdtherearethreedifferentCategory_Ids.Hereismyexamplethatgenerates6uniquerecordsformyPARTtableusinganestedWHILEloop.

declare@Part_Idintdeclare@Category_Idintdeclare@Descvarchar(50)createtablePART(Part_Idint,Category_Idint,Descriptionvarchar(50))set@Part_Id=0set@Category_Id=0while@Part_Id<2beginset@Part_Id=@Part_Id+1while@Category_Id<3beginset@Category_Id=@Category_Id+1set@Desc=Part_Idis+cast(@Part_Idaschar(1))+Category_Id+cast(@Category_Idaschar(1))insertintoPARTvalues(@Part_Id,@Category_Id,@Desc)endset@Category_Id=0endselect*fromPARTdroptablePART

HereistheoutputfromtheSELECTstatementatthebottomofthisnestedWHILEloopexample.

Part_IdCategory_IdDescription---------------------------------------------------------------11Part_Idis1Category_Id112Part_Idis1Category_Id213Part_Idis1Category_Id321Part_Idis2Category_Id122Part_Idis2Category_Id223Part_Idis2Category_Id3

Asyoucansee,byusinganestedWHILEloopeachcombinationofPart_IdandCategory_Idisunique.ThecodewithinthefirstWHILEloopcontrolledtheincrementingofthePart_Id,whereasthesecondWHILEloopsettheCategory_Idtoadifferentvalueeachtimethroughtheloop.Thecodewithinthefirstwhileloopwasexecutedonlytwice,butthecodeinsidethesecondWHILEloopwasexecuted6times.Thusgivingme6samplePARTrecords.

BREAKandCONTINUEStatements
Nowsometimesyouwanttobuildaloopthatwillprocessthroughlogicallytotheendmostofthetime,butnotallthetime.Inotherwords,youmaywanttobreakoutoftheloopifsomeparticularconditionarises.Alsoinadditiontobreakingoutoftheloop,youmaynotwanttoprocessallthecodeintheloopbeforegoingbacktothetopoftheloopandstartingthroughthenextiterationoftheloop.ForthesekindsofprogrammingrequirementsSQLServerprovidestheBREAKandCONTINUEstatements.

TheBREAKstatementexitsoutoftheinnermostWHILEloop,andproceedstothestatementfollowingtheENDstatementthatisassociatedwiththeloopinwhichtheBREAKstatementisexecuted.TheCONTINUEstatementskipsexecutingtherestofthestatementsbetweentheCONTINUEstatementandtheENDstatementofthecurrentloopandstartsexecutingatthefirstlinefollowingtheBEGINstatementofthecurrentWHILEloop.LetsgothoughacoupleofBREAKandCONTINUEexamples.

FortheBREAKstatementImgoingtomodifymylastexamplethatgeneratedPARTtablerecords.ThistimeImgoingtoBREAKoutoftheinnerWHILEloopwhenCategory_IDis2andPART_IDis1.HereismycodefortheBREAKstatement.

declare@Part_Idintdeclare@Category_Idintdeclare@Descvarchar(50)createtablePART(Part_Idint,Category_Idint,Descriptionvarchar(50))set@Part_Id=0set@Category_Id=0while@Part_Id<2beginset@Part_Id=@Part_Id+1while@Category_Id<3beginset@Category_Id=@Category_Id+1If@Category_ID=2and@Part_ID=1Breakset@Desc=Part_Idis+cast(@Part_Idaschar(1))+Category_Id+cast(@Category_Idaschar(1))insertintoPARTvalues(@Part_Id,@Category_Id,@Desc)endset@Category_Id=0endselect*fromPARTdroptablePART

HereistheoutputforthiscodethatcontainsaBREAKstatementinsidetheinnerWHILEloop.

Part_IdCategory_IdDescription---------------------------------------------------------------11Part_Idis1Category_Id121Part_Idis2Category_Id122Part_Idis2Category_Id223Part_Idis2Category_Id3

FromthisoutputyoucanseethatnorecordswereinsertedforPart_Id=1andCategory_Id=2or3,whereastherearerecordsforPart_Id=2withallvaluesfortheCategory_Idcolumn.ThisisbecausetheIFstatementintheinnerloopforcedtheBREAKstatementtoexittheinnerloop.SincetherewererecordsgenerateforPart_Id=2,showsthattheBREAKstatementonlyexitedtheinnerloopandnottheouterloop.

NowjusttostaywiththesameexampleIvebeenusing,letsreplacetheBREAKstatementinthecodeabovewithaCONTINUEstatement.HereisthecodefordemonstratingtheCONTINUEstatement.

declare@Part_Idintdeclare@Category_Idintdeclare@Descvarchar(50)createtablePART(Part_Idint,Category_Idint,Descriptionvarchar(50))set@Part_Id=0set@Category_Id=0while@Part_Id<2beginset@Part_Id=@Part_Id+1while@Category_Id<3beginset@Category_Id=@Category_Id+1If@Category_ID=2and@Part_ID=1Continueset@Desc=Part_Idis+cast(@Part_Idaschar(1))+Category_Id+cast(@Category_Idaschar(1))insertintoPARTvalues(@Part_Id,@Category_Id,@Desc)endset@Category_Id=0endselect*fromPARTdroptablePART

WhenyouusetheCONTINUEstatementyougetthefollowingoutput.

---------------------------------------------------------------11Part_Idis1Category_Id113Part_Idis1Category_Id321Part_Idis2Category_Id122Part_Idis2Category_Id223Part_Idis2Category_Id3

Asyoucansee,whenIusetheCONTINUEstatementonlytherecordwithCategory_Id=2andPart_Id=1ismissing.ThisisbecausetheCONTINUEstatementdoesnotbreakoutoftheinnerWHILEloopbutonlygoesbacktothetopoftheWHILEloopwithoutinsertingtherecord.ThishappensonlywhenCategory_Idis2andPart_Idisequalto1.WhenPart_Id=1andCategory_Id=3theinsertstatementisstillexecuted.
GOTOStatement
TheBREAKstatementwillonlyexityoufromthecurrentlyprocessingWHILEloop,itwillnotbreakoutofallWHILEloops.However,occasionallythisisthekindoffunctionalityyourT-SQLscriptneeds.TohaveyourcodebreakoutofallWHILEloops,nomatterhowmanynestedWHILEstatementsyouhave,youwillneedtousetheGOTOstatement.NowIknowmostprogrammerscringeatthethoughtofusingtheGOTOstatement,butinthiscaseIfeeltheGOTOisanexceptablepractice.UsingmysameexampleIwillusetheGOTOtobreakoutofbothWHILEloops,whenthePART_Id=1andtheCategory_ID=3.

declare@Part_Idintdeclare@Category_Idintdeclare@Descvarchar(50)createtablePART(Part_Idint,Category_Idint,Descriptionvarchar(50))set@Part_Id=0set@Category_Id=0while@Part_Id<2beginset@Part_Id=@Part_Id+1while@Category_Id<3beginset@Category_Id=@Category_Id+1If@Category_ID=3and@Part_ID=1GOTOBREAK_OUTset@Desc=Part_Idis+cast(@Part_Idaschar(1))+Category_Id+cast(@Category_Idaschar(1))insertintoPARTvalues(@Part_Id,@Category_Id,@Desc)endset@Category_Id=0endBREAK_OUT:select*fromPARTdroptablePART

HereistheoutputfromthisGOTOcode:

Part_IdCategory_IdDescription---------------------------------------------------------------11Part_Idis1Category_Id112Part_Idis1Category_Id2

HeretheGOTOlogicstoppedtheinsertionofrecordsintothePARTtablewhen@Category_ID=3and@Part_Id=1.Thisisdonebyexecutingthe"GOTOBREAKOUT"statement.NotethatwhenthisGOTOstatementwasexecuteditbranchedtothelabel"BREAKOUT:"whichcanbefoundfollowingtheENDstatementforthefirst,outermostWHILEstatement.
Conclusion
HopefullynowyouhaveabetterideaofhowtocodeaT-SQLWHILEloop.IveexplainedhowtocontroltheWHILEloop,breakoutofaloopbyusingtheBREAKstatement,usetheCONTINUEstatementtoskipsomeofthecodeinthewhileloop,and/orbreakoutofallWHILEloopsusingtheGOTOstatement.ThetechniquesIvedescribedshouldgiveyouthebasisforbuildingallyourWHILEstatementsfromasingleWHILElooptoacomplexsetofnestedWHILEloops.Mynextarticleinthisserieswilldiscusshowtoprocessthroughasetofrecords.
这章描述如何检查和处理在MySQL数据库中的数据损坏。如果你的表损坏很多，你应该尝试找出其原因!见G.1调试一个MySQL服务器。

作者: 再现理想 时间: 2015-1-19 18:02
Mirror可以算是SQLServer的Dataguard了。但是能不能被大伙用起来就不知道了。

作者: 愤怒的大鸟 时间: 2015-1-25 18:50
但换公司用MSSQL2K感觉自己好像根本就不了解MSSQL。什么DTS触发器以前根本没用过。

作者: 精灵巫婆 时间: 2015-2-3 13:37
这是一个不错的新特性。虽然索引的附加字段没有索引键值效率高，但是相对映射到数据表中效率还是提高了很多。我做过试验，在我的实验环境中会比映射到表中提高30%左右的效率。

作者: 透明 时间: 2015-2-9 02:57
语句级快照和事务级快照终于为SQLServer的并发性能带来了突破。个人感觉语句级快照大家应该应用。事务级快照，如果是高并发系统还要慎用。如果一个用户总是被提示修改不成功要求重试时，会杀人的！

作者: 飘飘悠悠 时间: 2015-2-26 19:38
再开发调试阶段和OLAP环境中，外键是可以建立的。新版本中加入了SETNULL和SETDEFAULT属性，能够提供能好的级联设置。

作者: admin 时间: 2015-3-8 17:07
另一个是把SQL语句写到服务器端,就是所谓的SP(存储过程);

作者: 柔情似水 时间: 2015-3-16 07:20
sqlserver的痛苦之处在于有用文档的匮乏，很多只是表明的东西

作者: 再见西城 时间: 2015-3-22 21:09
换言之，只有在不断的失败中尝试成功，而关于失败的总结却是很少的

作者: 分手快乐 时间: 2015-3-22 21:09
个人感觉没有case直观。而且默认的第三字段（还可能更多）作为groupby字段很容易造成新手的错误。

作者: 小魔女 时间: 2015-3-22 21:09
还不是性能有问题！否则面向对象的数据库早就实现了！建议使用CLR的地方一般是和应用的复杂程度或操作系统环境有很高的耦合度的场景。如你想构建复杂的算法，并且用到了大量的指针和高级数据模型。

欢迎光临仓酷云 (http://www.ckuyun.com/)