數(shù)據(jù)庫(kù)系統(tǒng)項(xiàng)目實(shí)踐第六講：存儲(chǔ)過(guò)程與觸發(fā)器

數(shù)據(jù)庫(kù)系統(tǒng)項(xiàng)目實(shí)踐ProjectspracticeOfDatabaseSystem第六講存儲(chǔ)過(guò)程與觸發(fā)器ProjectspracticeOfDatabaseSystem主要內(nèi)容了解存儲(chǔ)過(guò)程以及觸發(fā)器的概念了解觸發(fā)器的功能和類型掌握如何使用企業(yè)管理器創(chuàng)建和管理存儲(chǔ)過(guò)程和觸發(fā)器掌握如何使用T-SQL語(yǔ)句創(chuàng)建和管理存儲(chǔ)過(guò)程和觸發(fā)器ProjectspracticeOfDatabaseSystem

存儲(chǔ)過(guò)程（StoredProcedure）

存放在SQLSEVER中的預(yù)先編譯好了的SQL程序。由于存儲(chǔ)過(guò)程是預(yù)先編譯好了的，因此他們具有各種類型查詢的最好性能。1、存儲(chǔ)過(guò)程是以一個(gè)名稱存儲(chǔ)在數(shù)據(jù)庫(kù)中，可以作為一個(gè)獨(dú)立的數(shù)據(jù)對(duì)象，也可以作為一個(gè)單元在數(shù)據(jù)庫(kù)中被用戶調(diào)用。2、存儲(chǔ)過(guò)程可以接收和輸出數(shù)據(jù)、參數(shù)以及返回執(zhí)行存儲(chǔ)過(guò)程的狀態(tài)值，還可以嵌套使用。3、存儲(chǔ)過(guò)程提供了標(biāo)準(zhǔn)的SQL語(yǔ)言所沒(méi)有的高級(jí)特性，其傳遞參數(shù)和執(zhí)行邏輯表達(dá)式的功能，有助于應(yīng)用程序設(shè)計(jì)者處理復(fù)雜的數(shù)據(jù)任務(wù)。4、存儲(chǔ)過(guò)程是工作在服務(wù)器上的，從而有效的減少C/S頻繁訪問(wèn)的數(shù)據(jù)流量，減少數(shù)據(jù)操作所需要的網(wǎng)絡(luò)帶寬和數(shù)據(jù)流量5、存儲(chǔ)過(guò)程使得開(kāi)發(fā)者不必在客戶端開(kāi)發(fā)大量的程序代碼，同時(shí)在數(shù)據(jù)庫(kù)的安全性上面得到提高。存儲(chǔ)過(guò)程的基本概念ProjectspracticeOfDatabaseSystem管理平臺(tái)創(chuàng)建存儲(chǔ)過(guò)程1、創(chuàng)建存儲(chǔ)過(guò)程ProjectspracticeOfDatabaseSystemT-SQL管理存儲(chǔ)過(guò)程請(qǐng)參考聯(lián)機(jī)幫助文檔ProjectspracticeOfDatabaseSystem執(zhí)行存儲(chǔ)過(guò)程T-SQL執(zhí)行存儲(chǔ)過(guò)程語(yǔ)法：[EXECUTE]{[@return_status=]procedure_name[;number]}/@return_status是整型局部變量，用于保存存儲(chǔ)過(guò)程的返回值//指定保存的存儲(chǔ)過(guò)程名稱//指定該存儲(chǔ)過(guò)程與其他同名的存儲(chǔ)過(guò)程同組時(shí)候的標(biāo)號(hào)/[@parameter=]{value|@variable[OUTPUT|[DEFAULT]}][,…N]/@parameter是在創(chuàng)建的時(shí)候定義的過(guò)程參數(shù)。調(diào)用者向存儲(chǔ)過(guò)程所傳遞的參數(shù)值由value參數(shù)或@variable變量提供/[WITHRECOMPILE]/指定實(shí)行存儲(chǔ)過(guò)程的時(shí)候重新編譯執(zhí)行計(jì)劃/存儲(chǔ)過(guò)程返回值作為insert語(yǔ)句的輸入ProjectspracticeOfDatabaseSystem參數(shù)賦值第一種：根據(jù)參數(shù)名稱給輸入?yún)?shù)值

@parameter_name=value第二種：根據(jù)參數(shù)定義時(shí)候的順序值

parametervalue1，value2，…….ProjectspracticeOfDatabaseSystem查看、刪除存儲(chǔ)過(guò)程查看存儲(chǔ)過(guò)程的文本

EXECsp_helptext[proc_name]查看存儲(chǔ)過(guò)程的相關(guān)性

EXECsp_depends[proc_name]查看存儲(chǔ)過(guò)程的所有者、類型、創(chuàng)建日期和參數(shù)

EXECsp_help[proc_name]刪除存儲(chǔ)過(guò)程

Dropprocedureproc_nameProjectspracticeOfDatabaseSystem修改存儲(chǔ)過(guò)程ALTERprocedureprocedure_name[;number]]/指定的存儲(chǔ)過(guò)程名稱[{@parameterdata_type}]}/指定存儲(chǔ)過(guò)程的參數(shù)名稱以及類型/[VARYING][=default][OUTPUT]/指定輸出參數(shù)支持的結(jié)果集（僅適合于帶游標(biāo)的參數(shù)）//默認(rèn)數(shù)值//用來(lái)指定參數(shù)是可以返回的，可以將該信息返回給調(diào)用的過(guò)程/[WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}][FORREPLICATION]/指定每執(zhí)行一次都要重新編譯//SQL需要加密syscomments表中的內(nèi)容//該存儲(chǔ)過(guò)程只能夠在數(shù)據(jù)數(shù)據(jù)復(fù)制的時(shí)候使用，本選項(xiàng)不能夠和withrecompile聯(lián)合使用/ASsql_statement[…n]

ProjectspracticeOfDatabaseSystem自定義函數(shù)自定義函數(shù)是由一個(gè)或多個(gè)Transact-SQL語(yǔ)句組成的子程序，可用于封裝代碼以便重復(fù)使用。標(biāo)量函數(shù)內(nèi)嵌表值函數(shù)多語(yǔ)句表值函數(shù)

自定義函數(shù)的輸入?yún)?shù)可以為零個(gè)或最多1024個(gè)，輸入?yún)?shù)能夠是除了時(shí)間戳(timestamp)、游標(biāo)(cursor)和表(table)以外的其他變量。ProjectspracticeOfDatabaseSystem標(biāo)量函數(shù)createfunction所有者.自定義函數(shù)名([參數(shù)[…n]])returns返回參數(shù)的類型asbegin函數(shù)體return函數(shù)返回的值endProjectspracticeOfDatabaseSystem標(biāo)量函數(shù)use教學(xué)成績(jī)管理數(shù)據(jù)庫(kù)gocreatefunctionis_chinese_str(@strnchar(50))returnsnchar(1)asbegindeclare@Itinyint,@Jtinyintset@I=len(@str)set@J=1while(@J<=@I)beginif(unicode(substring(@str,@J,1))<51)return'否'set@J=@J+1endreturn'是'endselectis_chinese_str('計(jì)算機(jī)系')selectis_chinese_str('網(wǎng)絡(luò)1班')selectis_chinese_str('I''mfromChina')ProjectspracticeOfDatabaseSystem內(nèi)嵌表值函數(shù)createfunction[所有者].自定義函數(shù)名([參數(shù)[,…n]])returnstableasreturn(select查詢語(yǔ)句)內(nèi)嵌表值函數(shù)返回一個(gè)select語(yǔ)句查詢結(jié)果的表。內(nèi)嵌表值函數(shù)可用于實(shí)現(xiàn)參數(shù)化視圖(查詢)的功能。ProjectspracticeOfDatabaseSystem內(nèi)嵌表值函數(shù)use教學(xué)成績(jī)管理數(shù)據(jù)庫(kù)gocreatefunction[某班學(xué)生信息表](@classvarchar(15))returnstableasreturn(select*from學(xué)生信息表視圖where班級(jí)=@class)select*fromdbo.某班學(xué)生信息表('03軟件技術(shù)')select*fromdbo.某班學(xué)生信息表('03電子商務(wù)')select*fromdbo.某班學(xué)生信息表('03網(wǎng)絡(luò)工程')ProjectspracticeOfDatabaseSystem多語(yǔ)句表值函數(shù)請(qǐng)參考聯(lián)機(jī)幫助文檔ProjectspracticeOfDatabaseSystem觸發(fā)器觸發(fā)器是一種特殊類型的存儲(chǔ)過(guò)程，當(dāng)使用update,insert或者delete命令時(shí)，觸發(fā)器將自動(dòng)執(zhí)行以維護(hù)數(shù)據(jù)的完整性。觸發(fā)器的功能：1、級(jí)聯(lián)修改數(shù)據(jù)庫(kù)中的相關(guān)表。2、執(zhí)行比完整性約束更為復(fù)雜的約束操作。3、拒絕或回滾違反引用完整性的操作。4、修改前后數(shù)據(jù)之間的差別5、維護(hù)規(guī)范化數(shù)據(jù)ProjectspracticeOfDatabaseSystem觸發(fā)器類型和觸發(fā)操作觸發(fā)器有五種類型：Update,insert,delete,insteadof,after類型觸發(fā)器1、在表上進(jìn)行更新操作時(shí)候?qū)⒓ぐl(fā)update觸發(fā)器。2、在表上進(jìn)行插入操作時(shí)候?qū)⒓ぐl(fā)insert觸發(fā)器。3、在表上進(jìn)行刪除操作時(shí)候?qū)⒓ぐl(fā)delete觸發(fā)器。4、不做插入、更新或刪除動(dòng)作時(shí)候，將激發(fā)insteadof觸發(fā)器。5、after觸發(fā)器則在一個(gè)觸發(fā)動(dòng)作發(fā)生并完成之后，并提供一種機(jī)制以便控制多個(gè)觸發(fā)器的執(zhí)行順序。ProjectspracticeOfDatabaseSystem創(chuàng)建觸發(fā)器Createtriggertrigger_nameOntable/view{forafter/insteadof}{insert/update/delete}AsSql_statement請(qǐng)參考聯(lián)機(jī)幫助文檔ProjectspracticeOfDatabaseSystem觸發(fā)器實(shí)現(xiàn)數(shù)據(jù)完整性注意：關(guān)于inserted表和deleted表觸發(fā)器中使用了兩種特殊的表inserted表和deleted表Inserted表：用于存儲(chǔ)insert和update語(yǔ)句所影響的副本Deleted表：用于存儲(chǔ)deleted和update語(yǔ)句所影響的副本,在執(zhí)行deleted時(shí)候，行被刪除的時(shí)候會(huì)將信息傳輸?shù)絛eleted表。實(shí)驗(yàn)案例：首先我們知道，如果一個(gè)學(xué)生的信息已經(jīng)從student中被刪除了，那么這個(gè)學(xué)生的成績(jī)信息就不應(yīng)該再在score表中存在了。作為關(guān)系性數(shù)據(jù)庫(kù)，應(yīng)該從這層邏輯層面上解決：請(qǐng)?jiān)趕tudent表中建立存儲(chǔ)過(guò)程：CREATEtriggerscore_stuonstudentfordeleteasdeletescorefromdeletedwheredeleted.sno=score.snoProjectspracticeOfDatabaseSystem觸發(fā)器實(shí)現(xiàn)數(shù)據(jù)完整性下面試試刪除：useschooldeletefromstudentwheresno=108例子2：?jiǎn)栴}：如果插入score表中沒(méi)有student表中的sno或者course表中的cno，就違反了關(guān)系的完整性了。下面的例子就說(shuō)明了這個(gè)問(wèn)題。ProjectspracticeOfDatabaseSystem觸發(fā)器實(shí)現(xiàn)數(shù)據(jù)完整性在score中建立觸發(fā)器：useschoolifexists(selectnamefromsysobjectswherename='check_string')droptriggercheck_stringgocreatetriggercheck_stringonscoreforinsertasifexists(select*frominsertedawherea.sno

notin(selectb.snofromstudentb)onotin(ofromcoursec))beginraiserror('您所插入的學(xué)號(hào)或課程號(hào)并不存在！',16,1)rollbacktransactionEndelseprint'插入數(shù)據(jù)成功！'ProjectspracticeOfDatabaseSystem觸發(fā)器實(shí)現(xiàn)數(shù)據(jù)完整性插入一條數(shù)據(jù)：insertintoscore(sno,cno,degree)values('103','6-166',78)ProjectspracticeOfDatabaseSystem查看、修改觸發(fā)器查看觸發(fā)器sp_helptri