SqlServer專題二：數(shù)據(jù)庫(kù)主要對(duì)象

SqlServer專題?：數(shù)據(jù)庫(kù)主要對(duì)象存儲(chǔ)過(guò)程(1)減少?絡(luò)通信量。調(diào)??個(gè)?數(shù)不多的存儲(chǔ)過(guò)程與直接調(diào)?sql語(yǔ)句的?絡(luò)通信量可能不會(huì)有很?的差別，可是如果存儲(chǔ)過(guò)程包含上百?sql語(yǔ)句，那么其性能絕對(duì)??條?條的調(diào)?sql語(yǔ)句要?得多。(2)執(zhí)?速度更快。有兩個(gè)原因：?先，在存儲(chǔ)過(guò)程創(chuàng)建的時(shí)候，數(shù)據(jù)庫(kù)已經(jīng)對(duì)其進(jìn)?了?次解析和優(yōu)化。其次，存儲(chǔ)過(guò)程?旦執(zhí)?，在內(nèi)存中就會(huì)保留?份這個(gè)存儲(chǔ)過(guò)程，這樣下次再執(zhí)?同樣的存儲(chǔ)過(guò)程時(shí)，可以從內(nèi)存中直接調(diào)?。(3)更強(qiáng)的適應(yīng)性：由于存儲(chǔ)過(guò)程對(duì)數(shù)據(jù)庫(kù)的訪問(wèn)是通過(guò)存儲(chǔ)過(guò)程來(lái)進(jìn)?的，因此數(shù)據(jù)庫(kù)開發(fā)?員可以在不改動(dòng)存儲(chǔ)過(guò)程接?的情況下對(duì)數(shù)據(jù)庫(kù)進(jìn)?任何改動(dòng)，?這些改動(dòng)不會(huì)對(duì)應(yīng)?程序造成影響。(4)布?作：應(yīng)?程序和數(shù)據(jù)庫(kù)的?作可以別?進(jìn)?，?不會(huì)相互壓制。存儲(chǔ)過(guò)程可以?臨時(shí)表,函數(shù)不能?臨時(shí)表存儲(chǔ)過(guò)程可以使?Update,函數(shù)不能使?Update存儲(chǔ)過(guò)程可以?Getdate()等函數(shù),函數(shù)不能使?Getdate()這些函數(shù)?、使?存儲(chǔ)過(guò)程要注意?些問(wèn)題1、在as??即跟上?個(gè)setnocounton?句，這樣會(huì)減少很?開銷(相對(duì)于過(guò)程中存在DML操作的時(shí)候)2、創(chuàng)建和引?過(guò)程的時(shí)候加上架構(gòu)名3、避免在返回許多?數(shù)據(jù)的SELECT語(yǔ)句中使?標(biāo)量函數(shù)(getdate())。因?yàn)闃?biāo)量函數(shù)必須應(yīng)?于每??。4、避免使?SELECT*。?是應(yīng)指定所需的列名稱。5、避免處理或返回”過(guò)多”的數(shù)據(jù)，?些沒(méi)必要的數(shù)據(jù)就不要了6、通過(guò)使?BEGIN/ENDTRANSACTION來(lái)使?顯式事務(wù)并且保留盡可能短的事務(wù)。更長(zhǎng)的事務(wù)意味著更長(zhǎng)的記錄鎖定和更?的死鎖風(fēng)險(xiǎn)7、在LIKE?句中避免使?通配符作為前導(dǎo)字符，例如LIKE‘%a%?！?yàn)榈?個(gè)字符是不確定的，所以，查詢處理器?法使?可?的索引。應(yīng)改?LIKE‘a(chǎn)%?！?、使?Transact-SQLTRY…CATCH功能進(jìn)?過(guò)程內(nèi)的錯(cuò)誤處理9、創(chuàng)建/修改Table時(shí)給列?個(gè)Default值10、使?IFEXISTS(SELECTTOP1FROMtable_name)來(lái)代替IFEXISTS(SELECT*FROMtable_name)。11、使?UNIONALL運(yùn)算符來(lái)代替UNION或OR運(yùn)算符12、創(chuàng)建存儲(chǔ)過(guò)程時(shí)在其定義中指定WITHRECOMPILE選項(xiàng)，表明SQLServer將不對(duì)該存儲(chǔ)過(guò)程計(jì)劃進(jìn)??速緩存；該存儲(chǔ)過(guò)程將在每次執(zhí)?時(shí)都重新編譯。當(dāng)存儲(chǔ)過(guò)程的參數(shù)值在各次執(zhí)?間都有較?差異(個(gè)?覺(jué)得應(yīng)該是動(dòng)態(tài)創(chuàng)建存儲(chǔ)過(guò)程時(shí)，使得很可能有時(shí)候參數(shù)不同)，導(dǎo)致每次均需創(chuàng)建不同的執(zhí)?計(jì)劃時(shí)，可使?WITHRECOMPILE選項(xiàng)。此選項(xiàng)并不常?，因?yàn)槊看螆?zhí)?存儲(chǔ)過(guò)程時(shí)都必須對(duì)其進(jìn)?重新編譯，這樣會(huì)使存儲(chǔ)過(guò)程的執(zhí)?變慢，?、知識(shí)點(diǎn)：1、SQLServer啟動(dòng)時(shí)可以?動(dòng)執(zhí)??個(gè)或多個(gè)過(guò)程。這些過(guò)程必須由系統(tǒng)管理員在master數(shù)據(jù)庫(kù)中創(chuàng)建，并以sysadmin固定服務(wù)器??作為后臺(tái)進(jìn)程執(zhí)?。這些過(guò)程不能有任何輸?或輸出參數(shù)。2、過(guò)程可以嵌套，并且最多可以嵌套32級(jí)。3、過(guò)程可以引?尚不存在的表。在創(chuàng)建時(shí)，只進(jìn)?語(yǔ)法檢查。直到第?次執(zhí)?該過(guò)程時(shí)才對(duì)其進(jìn)?編譯。只有在編譯過(guò)程中才解析過(guò)程中引?的所有對(duì)象。加密存儲(chǔ)過(guò)程CreateprocedureFirstPWITHENCRYPTIONassetnocounton

select*fromCgoEXECsp_helptext'dbo.FirstP'存儲(chǔ)過(guò)程實(shí)現(xiàn)遞歸select*fromtborderbycasewhenISNUMERIC(col)=1then''elseleft(col,1)end,cast(casewhenISNUMERIC(col)=0thenstuff(col,1,1,'')elsecolendasint)droptabledbo.ExamplecreatetableExample(IDint,ParentIDint)insertExampleselect0,nullunionselect1,0unionselect2,0unionselect3,1unionselect4,6unionselect5,2--取其某個(gè)ID為?節(jié)點(diǎn)的樹結(jié)構(gòu)的SQL如下：createproceduredisplay(@IDint)asbeginWITHExample_Table(ID,ParentID,lv)AS(取根--節(jié)點(diǎn)放?臨時(shí)表SELECTID,ParentID,0FROMExampleWHEREID=@ID根據(jù)--已取到的數(shù)據(jù)遞歸取其字節(jié)點(diǎn)的數(shù)據(jù)UNIONALLSELECTA.ID,A.ParentID,B.lv+1FROMExampleAINNERJOINExample_TableBONA.ParentID=B.ID)SELECT*FROMExample_Tableenddroptabletbgo?定義函數(shù)?戶定義?定義函數(shù)像內(nèi)置函數(shù)?樣返回標(biāo)量值，也可以將結(jié)果集?表格變量返回?戶?定義函數(shù)的類型:標(biāo)量函數(shù):返回?個(gè)標(biāo)量值

表格值函數(shù){內(nèi)聯(lián)表格值函數(shù)、多表格值函數(shù)}:返回?集（即返回多個(gè)值）1、標(biāo)量函數(shù)Createfunction函數(shù)名（參數(shù)）Returns返回值數(shù)據(jù)類型[with{Encryption|Schemabinding}][as]beginSQL語(yǔ)句(必須有return變量或值)EndSchemabinding:將函數(shù)綁定到它引?的對(duì)象上（注：函數(shù)?旦綁定，則不能刪除、修改，除?刪除綁定）CreatefunctionAvgResult(@scodevarchar(10))ReturnsrealAsBeginDeclare@avgrealDeclare@codevarchar(11)Set@code=@scode+‘%’Select@avg=avg(result)fromLearnResult_baijialiWherescodelike@codeReturn@avgEnd執(zhí)??戶?定義函數(shù)select?戶名。函數(shù)名as字段別名selectdbo.AvgResult(‘s0002’)asresult?戶?定義函數(shù)返回值可放到局部變量中，?set,select,exec賦值declare@avg1real,@avg2real,@avg3realselect@avg1=dbo.AvgResult(‘s0002’)set@avg2=dbo.AvgResult(‘s0002’)exec@avg3=dbo.AvgResult‘s0002’select@avg1asavg1,@avg2asavg2,@avg3asavg3函數(shù)引?createfunctioncode(@scodevarchar(10))returnsvarchar(10)asbegindeclare@ccodevarchar(10)set@scode=@scode+‘%’select@ccode=ccodefromcmessage

whereccodelike@scodereturn@ccodeendselectnamefromclasswhereccode=dbo.code(‘c001’)2、表格值函數(shù)a、內(nèi)聯(lián)表格值函數(shù)格式：createfunction函數(shù)名（參數(shù)）returnstable[with{Encryption|Schemabinding}]asreturn(?條SQL語(yǔ)句)createfunctiontabcmess(@codevarchar(10))returnstableasreturn(selectccode,scodefromcmessagewhereccodelike@ccode)b、多句表格值函數(shù)createfunction函數(shù)名（參數(shù)）returns表格變量名table表(格變量定義)[with{Encryption|Schemabinding}]asbeginSQL語(yǔ)句end多句表格值函數(shù)包含多條SQL語(yǔ)句，?少有?條在表格變量中填上數(shù)據(jù)值表格變量格式returns@變量名table(column定義|約束定義[,…])對(duì)表格變量中的?可執(zhí)?select,insert,update,delete，但selectinto和insert語(yǔ)句的結(jié)果集是從存儲(chǔ)過(guò)程插?。Createfunctiontabcmessalot(@codevarchar(10))Returns@ctabletable(codevarchar(10)null,cnamevarchar(100)null)AsBeginInsert@ctableSelectccode,explainfromcmessageWherescodelike@codereturnEnd

Select*fromtabcmessalot(‘s0003’)觸發(fā)器?:觸發(fā)器是?種特殊的存儲(chǔ)過(guò)程﹐它不能被顯式地調(diào)?﹐?是在往表中插?記錄﹑更新記錄或者刪除記錄時(shí)被?動(dòng)地激活。所以觸發(fā)器可以?來(lái)實(shí)現(xiàn)對(duì)表實(shí)施復(fù)雜的完整性約束。?:SQLServer為每個(gè)觸發(fā)器都創(chuàng)建了兩個(gè)專?表:Inserted表和Deleted表。這兩個(gè)表由系統(tǒng)來(lái)維護(hù)﹐它們存在于內(nèi)存中?不是在數(shù)據(jù)庫(kù)中。這兩個(gè)表的結(jié)構(gòu)總是與被該觸發(fā)器作?的表的結(jié)構(gòu)相同。觸發(fā)器執(zhí)?完成后﹐與該觸發(fā)器相關(guān)的這兩個(gè)表也被刪除。Deleted表存放由于執(zhí)?Delete或Update語(yǔ)句?要從表中刪除的所有?。Inserted表存放由于執(zhí)?Insert或Update語(yǔ)句?要向表中插?的所有?。三:Insteadof和After觸發(fā)器SQLServer2000提供了兩種觸發(fā)器:Insteadof和After觸發(fā)器。這兩種觸發(fā)器的差別在于他們被激活的同:Insteadof觸發(fā)器?于替代引起觸發(fā)器執(zhí)?的T-SQL語(yǔ)句。除表之外﹐Insteadof觸發(fā)器也可以?于視圖﹐?來(lái)擴(kuò)展視圖可以?持的更新操作。After觸發(fā)器在?個(gè)Insert,Update或Deleted語(yǔ)句之后執(zhí)?﹐進(jìn)?約束檢查等動(dòng)作都在After觸發(fā)器被激活之發(fā)?。After觸發(fā)器只能?于表。?個(gè)表或視圖的每?個(gè)修改動(dòng)作(insert,update和delete)都可以有?個(gè)insteadof觸發(fā)器﹐?個(gè)表的每個(gè)修改動(dòng)作都可以有多個(gè)After觸發(fā)器。四:觸發(fā)器的執(zhí)?過(guò)程如果?個(gè)Insert﹑update或者delete語(yǔ)句違反了約束﹐?After觸發(fā)器不會(huì)執(zhí)?﹐因?yàn)閷?duì)約束的檢查是在After觸發(fā)器被激動(dòng)之發(fā)?的。所以After觸發(fā)器不能超越約束。Insteadof觸發(fā)器可以取代激發(fā)它的操作來(lái)執(zhí)?。它在Inserted表和Deleted表建?﹐其它任何操作還有發(fā)?時(shí)被執(zhí)?。因?yàn)镮nsteadof觸發(fā)器在約束之前執(zhí)?﹐所以它可以對(duì)約束進(jìn)??些預(yù)處理。五:使?T-SQL語(yǔ)句來(lái)創(chuàng)建觸發(fā)器基本語(yǔ)句如下:createtriggertrigger_nameon{table_name|view_name}{for|After|Insteadof}[insert,update,delete]assql_statement六:刪除觸發(fā)器：基本語(yǔ)句如下:droptriggertrigger_name七：查看數(shù)據(jù)庫(kù)中已有觸發(fā)器：--查看數(shù)據(jù)庫(kù)已有觸發(fā)器usejxcSoftwaregoselect*fromsysobjectswherextype='TR'--查看單個(gè)觸發(fā)器execsp_helptext觸'發(fā)器名'?:修改觸發(fā)器：基本語(yǔ)句如下:altertriggertrigger_nameon{table_name|view_name}{for|After|Insteadof}[insert,update,delete]assql_statement九:相關(guān)?例:1:在Orders表中建?觸發(fā)器﹐當(dāng)向Orders表中插??條訂單記錄時(shí)﹐檢查goods表的貨品狀態(tài)status是否為1(正在整理)﹐是﹐則不能往Orders表加?該訂單。createtriggerorderinsertonordersafterinsertasif(selectstatusfromgoods,=inserted.goodsname)=1beginprint'thegoodsisbeingprocessed'print'theordercannotbecommitted'rollbacktransaction回滾--﹐避免加?end2:在Orders表建??個(gè)插?觸發(fā)器﹐在添加?條訂單時(shí)﹐減少Goods表相應(yīng)的貨品記錄中的庫(kù)存。createtriggerorderinsert1onordersafterinsertasupdategoodssetstorage=storage-inserted.quantity

fromgoods,=inserted.goodsname3:在Goods表建?刪除觸發(fā)器﹐實(shí)現(xiàn)Goods表和Orders表的級(jí)聯(lián)刪除。createtriggergoodsdeleteongoodsafterdeleteasdeletefromorderswheregoodsnamein(selectnamefromdeleted)4:在Orders表建??個(gè)更新觸發(fā)器﹐監(jiān)視Orders表的訂單?期(OrderDate)列﹐使其不能??修改.createtriggerorderdateupdateonordersafterupdateasifupdate(orderdate)beginraiserror('orderdatecannotbemodified',10,1)rollbacktransactionend5:在Orders表建??個(gè)插?觸發(fā)器﹐保證向Orders表插?的貨品名必須要在Goods表中?定存在。createtriggerorderinsert3onordersafterinsertasif(selectcount(*)fromgoods,=inserted.goodsname)=0beginprint'noentryingoodsforthisorder'rollbacktransactionend6：Orders表建??個(gè)插?觸發(fā)器，保證向Orders表插?的貨品信息要在Order表中添加altertriggeraddOrderonOrdersforinsertasinsertintoOrderselectinserted.Id,inserted.goodName,inserted.Numberfrominserted游標(biāo)SQlServer的游標(biāo)在你需要對(duì)記錄集進(jìn)?單條處理時(shí)很有?處。select返回?個(gè)記錄集，但是你想根據(jù)每條記錄的不同情況進(jìn)?單條處理，這時(shí)游標(biāo)的?處就顯出來(lái)了。?個(gè)游標(biāo)(cursor)可以被看作指向結(jié)果集(asetofrows)中??的