TransactSQL簡(jiǎn)介存儲(chǔ)過(guò)程和觸發(fā)器課件

第六章Transact-SQL簡(jiǎn)介、存儲(chǔ)過(guò)程和觸發(fā)器6.1Transact-SQL簡(jiǎn)介

6.2存儲(chǔ)過(guò)程

6.3觸發(fā)器6.1Transact-SQL簡(jiǎn)介

6.1.1批處理、腳本和注釋6.1.2常量和變量6.1.3流程控制語(yǔ)句1批處理 建立批處理如同編寫SQL語(yǔ)句，區(qū)別在于它是多條語(yǔ)句同時(shí)執(zhí)行的，用GO語(yǔ)句作為一個(gè)批處理的結(jié)束。2腳本 腳本是批處理的存在方式，將一個(gè)或多個(gè)批處理組織到一起就是一個(gè)腳本。 腳本可以在查詢分析器中執(zhí)行，查詢分析器是編輯、調(diào)試和使用腳本的最好環(huán)境。

3注釋1)單行注釋：使用兩個(gè)連在一起的減號(hào)“––”作為注釋符語(yǔ)法格式為：––注釋文本

2)塊注釋：使用“/**/”作為注釋符塊注釋的語(yǔ)法格式為：/*注釋文本*/或：/*注釋文本*/6.1.2常量和變量

常量和變量是程序設(shè)計(jì)中不可缺少的元素。變量又分為局部變量和全局變量，局部變量是一個(gè)能夠保存特定數(shù)據(jù)類型實(shí)例的對(duì)象，是程序中各種類型數(shù)據(jù)的臨時(shí)存儲(chǔ)單元，用在批處理內(nèi)SQL語(yǔ)句之間傳遞數(shù)據(jù)。全局變量是系統(tǒng)給定的特殊變量。1常量Transact-SQL的常量主要有以下幾種。字符串常量數(shù)值常量日期常量3局部變量 局部變量是用戶在程序中定義的變量，一次只能保存一個(gè)值，它僅在定義的批處理范圍內(nèi)有效。局部變量可以臨時(shí)存儲(chǔ)數(shù)值。局部變量名總是以@符號(hào)開(kāi)始，最長(zhǎng)為128個(gè)字符。 使用DECLARE語(yǔ)句聲明局部變量，定義局部變量的名字、數(shù)據(jù)類型，有些還需要確定變量的長(zhǎng)度。4變量的聲明與賦值聲明變量的語(yǔ)句格式：DECLARE@局部變量名數(shù)據(jù)類型注：不能把局部變量指定為text或image類型，使用DECLARE聲明一個(gè)局部變量后，這個(gè)變量的值將被初始化為null。變量的賦值語(yǔ)句格式為：SET@局部變量名=值|表達(dá)式注：表達(dá)式可以是任意的SQLSERVER表達(dá)式。6.1.3流程控制語(yǔ)句流程控制語(yǔ)句是組織較復(fù)雜Transact-SQL語(yǔ)句的語(yǔ)法元素，在批處理、存儲(chǔ)過(guò)程、腳本和特定的檢索中使用。它們包括條件控制語(yǔ)句、無(wú)條件轉(zhuǎn)移語(yǔ)句和循環(huán)語(yǔ)句等。

主要的流程控制語(yǔ)句：BEGIN…END：定義語(yǔ)句塊IF…ELSE：若指定條件為真，執(zhí)行一個(gè)分支，否則執(zhí)行另一個(gè)分支WHILE：當(dāng)指定條件為真時(shí)重復(fù)一些語(yǔ)句CASE：允許表達(dá)式按照條件返回不同的值BREAK：退出最內(nèi)層的WHILE循環(huán)RETURN：重新開(kāi)始WHILE循環(huán)WAITFOR：為語(yǔ)句的執(zhí)行設(shè)置延遲1BEGIN…END語(yǔ)句塊BEGIN和END用來(lái)定義語(yǔ)句塊，必須成對(duì)出現(xiàn)。它將多個(gè)SQL語(yǔ)句括起來(lái)，相當(dāng)于一個(gè)單一語(yǔ)句，其語(yǔ)法格式如下。BEGIN

語(yǔ)句1或語(yǔ)句塊1 語(yǔ)句2或語(yǔ)句塊2 …END3WHILE語(yǔ)句 WHILE語(yǔ)句用來(lái)實(shí)現(xiàn)循環(huán)結(jié)構(gòu)，其語(yǔ)法格式如下：WHILE邏輯表達(dá)式語(yǔ)句塊 當(dāng)邏輯表達(dá)式為真時(shí)，執(zhí)行循環(huán)體，直到邏輯表達(dá)式為假。 BREAK語(yǔ)句退出WHILE循環(huán)，CONTINUE語(yǔ)句跳過(guò)語(yǔ)句塊中的所有其他語(yǔ)句，開(kāi)始下一次循環(huán)。例：若IF條件為真或?yàn)榧贂r(shí)要執(zhí)行的語(yǔ)句只有一條（默認(rèn)時(shí)，一條語(yǔ)句就是一個(gè)語(yǔ)句塊），則可以不使用BEGIN…END。DECLARE@xint,@yint,@zintSET@x=40SET@y=30IF(@x>@y)SET@z=@x-@yELSESET@z=@y-@xSET@x=0PRINT@xPRINT@yPRINT@z執(zhí)行結(jié)果為

03010例：若希望條件為假時(shí)，在ELSE語(yǔ)句中執(zhí)行其后續(xù)的兩條語(yǔ)句，則必須使用BEGIN…END將這兩條語(yǔ)句包括起來(lái)，使其成為一個(gè)語(yǔ)句塊。DECLARE@xint,@yint,@zintSET@x=40SET@y=30IF(@x>@y)SET@z=@x-@yELSEBEGINSET@z=@y-@xSET@x=0END例：計(jì)算1+2+3+…+100的和DECLARE@iint，@sumintSET@i=1SET@sum=0WHILE@i<=100BEGINSET@sum=@sum+@iSET@i=@i+1ENDPRINT@sum6.2存儲(chǔ)過(guò)程

存儲(chǔ)過(guò)程（storedprocedure）是一組事先編譯好的Transact-SQL代碼。存儲(chǔ)過(guò)程作為一個(gè)獨(dú)立的數(shù)據(jù)庫(kù)對(duì)象，可以作為一個(gè)單元被用戶的應(yīng)用程序調(diào)用。由于存儲(chǔ)過(guò)程是已經(jīng)編譯好的代碼，所以執(zhí)行的時(shí)候不必再次進(jìn)行編譯，從而提高了程序的運(yùn)行效率。使用存儲(chǔ)過(guò)程的好處：1、執(zhí)行速度快。2、模塊化的程序設(shè)計(jì)。3、減少網(wǎng)絡(luò)通信量。4、保證系統(tǒng)的安全性。Default：表示參數(shù)的默認(rèn)值。Output：表明參數(shù)是輸出參數(shù)。執(zhí)行存儲(chǔ)過(guò)程的SQL語(yǔ)句是EXECUTE，其語(yǔ)法格式為：[EXECUTE]存儲(chǔ)過(guò)程名[實(shí)參[，OUTPUT][，…n]]

存儲(chǔ)過(guò)程可以嵌套，SQLServer2000最多可以允許嵌套32層存儲(chǔ)過(guò)程。例1：帶有復(fù)雜查詢的存儲(chǔ)過(guò)程：查詢計(jì)算機(jī)系學(xué)生的考試情況，列出學(xué)生的姓名、課程名和考試成績(jī)。CREATEPROCEDURES_grade1ASSELECTsname,cname,gradeFROMStudentsJOINscONs.sno=sc.snoJOINcoursecONo=oWHERESdept=‘計(jì)算機(jī)系’執(zhí)行此存儲(chǔ)過(guò)程：EXECS_grade1例3、帶有多個(gè)輸入?yún)?shù)并有默認(rèn)值的存儲(chǔ)過(guò)程：查詢某個(gè)學(xué)生某門課程的考試成績(jī)，若沒(méi)有指定課程，則默認(rèn)為‘?dāng)?shù)據(jù)庫(kù)’。CREATEPROCS_GRADE2@Snamechar(20)，@Cnamechar(20)=‘?dāng)?shù)據(jù)庫(kù)’ASSELECTSname,Cname,GradeFROMStudent,Scourse,CourseWHERESname=@SnameandCname=@CnameandStudent.Sno=Scourse.SnoAndCourse.Cno=Scourse.Cno

執(zhí)行帶多個(gè)參數(shù)的存儲(chǔ)過(guò)程時(shí)，參數(shù)的傳遞方式有兩種：1、按參數(shù)位置傳遞：執(zhí)行存儲(chǔ)過(guò)程的EXEC語(yǔ)句中的實(shí)參的排列順序必須與定義存儲(chǔ)過(guò)程時(shí)定義的參數(shù)的順序一致EXECS_GRADE2’張三’，’VB’2、按參數(shù)名傳遞：執(zhí)行存儲(chǔ)過(guò)程的EXEC語(yǔ)句中要指明定義存儲(chǔ)過(guò)程時(shí)定義的參數(shù)的名字以及此參數(shù)的值，而不關(guān)心參數(shù)的定義順序EXECS_GRADE2@Sname=’張三’，@Cname

=’VB’例4、帶有多個(gè)輸入?yún)?shù)并均指定默認(rèn)值的存儲(chǔ)過(guò)程：查詢指定系，指定性別的學(xué)生中年齡大于等于指定年齡的學(xué)生的情況。系的默認(rèn)值為‘計(jì)算機(jī)’，默認(rèn)的性別為‘男’，默認(rèn)的年齡為20。CREATEPROCS_GRADE3@deptchar(20)=‘計(jì)算機(jī)’

，@sexchar(2)=‘男’，@ageint=20

ASSELECT*FROMStudentWHERESdept=@deptandSsex=@sexandSage>=@age

例5、帶有輸出參數(shù)的存儲(chǔ)過(guò)程：計(jì)算兩個(gè)數(shù)得到積，將結(jié)果作為輸出參數(shù)返回給調(diào)用者。CREATEPROCEDUREPROC1@var1int,@var2int,@var3intoutputASSET@var3=@var1*@var2執(zhí)行此存儲(chǔ)過(guò)程的示例：DECLARE@resintEXECPROC15,7,@resoutputPRINT@res結(jié)果為：35例6、創(chuàng)建一個(gè)存儲(chǔ)過(guò)程，向?qū)W生表中插入數(shù)據(jù)。

CREATEPROCinsert_student@snochar(10),@snamechar(10),@ssexchar(4),@sageint,@sdeptchar(20)asinsertintostudent,@sage,@sdept)執(zhí)行此存儲(chǔ)過(guò)程execinsert_student‘1001’,’李明’,’男’,’20’,’計(jì)算機(jī)系’6.2.2查看存儲(chǔ)過(guò)程信息1．使用T-SQL語(yǔ)句查看存儲(chǔ)過(guò)程（1）可以使用sp_helpText命令查看創(chuàng)建存儲(chǔ)過(guò)程的文本信息。例如：usemastergosp_helptextsp_whogo系統(tǒng)返回信息是：（2）可以使用sp_help查看存儲(chǔ)過(guò)程的一般信息。例如：usemastergosp_helpproc1go6.2.3修改存儲(chǔ)過(guò)程

SQLServer提供了在不改變存儲(chǔ)過(guò)程使用許可和名字的情況下，對(duì)存儲(chǔ)過(guò)程進(jìn)行修改的語(yǔ)句。語(yǔ)法格式為：ALTERPROC[EDURE]存儲(chǔ)過(guò)程名[{@參數(shù)名數(shù)據(jù)類型}[=default][OUTPUT]][,…n]ASSQL語(yǔ)句注：也可以使用企業(yè)管理器進(jìn)行系統(tǒng)存儲(chǔ)過(guò)程的修改。例6、將例1定義的存儲(chǔ)過(guò)程修改，使之能查詢?nèi)魏沃付ㄏ档膶W(xué)生的修課情況。ALTERPROCS_Grade1@deptchar(20)ASSELECTSname,Cname,GradeFROMStudentJOINScourseONStudent.Sno=Scourse.SnoJOINCourseONCourse.Cno=Scourse.CnoWHERESdept=@dept6.2.5刪除存儲(chǔ)過(guò)程1．使用T-SQL語(yǔ)句刪除存儲(chǔ)過(guò)程刪除存儲(chǔ)過(guò)程使用DROPPROCEDURE語(yǔ)句。語(yǔ)法格式為：DROPPROCEDURE存儲(chǔ)過(guò)程名[,…n]例如：usestudentgodropprocedureS_grade1go6.3觸發(fā)器

觸發(fā)器是一種特殊的存儲(chǔ)過(guò)程，他不需要由用戶調(diào)用執(zhí)行，而是當(dāng)用戶對(duì)表中的數(shù)據(jù)進(jìn)行UPDATE、INSERT或DELETE操作時(shí)自動(dòng)觸發(fā)執(zhí)行的，觸發(fā)器通常用于保證業(yè)務(wù)規(guī)則的數(shù)據(jù)完整性，其主要優(yōu)點(diǎn)是用戶可以用編程的方法來(lái)實(shí)現(xiàn)復(fù)雜的處理邏輯和商業(yè)規(guī)則，增強(qiáng)了數(shù)據(jù)完整性約束規(guī)則。6.3.1創(chuàng)建觸發(fā)器1．使用T-SQL語(yǔ)句創(chuàng)建觸發(fā)器創(chuàng)建觸發(fā)器使用CREATETRIGGER語(yǔ)句。語(yǔ)法格式如下：CREATETRIGGER觸發(fā)器名ON表名[WITHENCRYPTION]{FOR|AFTER|INSTEADOF}{[DELETE][,][INSERT][,][UPDATE]}ASSQL語(yǔ)句其中：“觸發(fā)器名稱”必須是唯一的ON子句用于指定在其上執(zhí)行觸發(fā)器的表

AFTER：指定觸發(fā)器只有在引發(fā)觸發(fā)器執(zhí)行的SQL語(yǔ)句指定的操作都已成功執(zhí)行，并且所有的引用級(jí)聯(lián)操作和約束檢查也成功完成后，才執(zhí)行此觸發(fā)器，也叫后觸發(fā)性觸發(fā)器

FOR如果僅指定FOR關(guān)鍵字，則AFTER是默認(rèn)設(shè)置INSTEADOF：指定執(zhí)行觸發(fā)器而不是執(zhí)行引發(fā)觸發(fā)器執(zhí)行的SQL語(yǔ)句，從而替代觸發(fā)語(yǔ)句的操作。（注：只有SQLServer2000才支持）也叫前觸發(fā)器例1：創(chuàng)建帶有提示信息的觸發(fā)器，每當(dāng)用戶在course表中執(zhí)行插入操作時(shí)，向用戶產(chǎn)生一條提示信息。createtriggertri_insert_courseoncourseforinsertasprint’在course表中插入數(shù)據(jù)’——只是為了提示信息用執(zhí)行如下語(yǔ)句測(cè)試觸發(fā)器的作用：Insertintocourse(cno,cname)values(‘d01’,‘?dāng)?shù)據(jù)庫(kù)')例2、創(chuàng)建一個(gè)觸發(fā)器，當(dāng)向表student中修改一條記錄時(shí)，自動(dòng)顯示student表中的記錄。CREATETRIGGERchange_displayONstudentFORupdateASSELECT*FROMstudent例3、創(chuàng)建限制刪除的觸發(fā)器，限制刪除SC表中不及格學(xué)生的成績(jī)記錄。createtriggertri_del_gradeonSCfordeleteasifexists(select*fromDELETEDwheredeleted.grade<60)ROLLBACK在CREATETRIGGER語(yǔ)句中不能使用SELECT語(yǔ)句返回對(duì)表格查詢的數(shù)據(jù)，因?yàn)橛|發(fā)器不接受用戶應(yīng)用程序傳遞的參數(shù)，從而也無(wú)法向用戶應(yīng)用程序返回查詢表格數(shù)據(jù)所得到的結(jié)果。在創(chuàng)建觸發(fā)器的語(yǔ)句中，禁止使用下列T-SQL語(yǔ)句：ALTERDATABASE ALTERPROCEDUREALTERTABLE ALTERTRIGGERALTERVIEW CREATEDATABASECREATEDEFAULT CREATEINDEXCREATE