存儲(chǔ)過程觸發(fā)器事務(wù)和鎖

1、數(shù)據(jù)庫應(yīng)用技術(shù),存儲(chǔ)過程觸發(fā)器事務(wù)和鎖,第5章 T-SQL程序設(shè)計(jì),數(shù)據(jù)庫應(yīng)用技術(shù),5.1 存儲(chǔ)過程,5.1.1 存儲(chǔ)過程的概念一組已被編輯在一起的，存儲(chǔ)在服務(wù)器上，執(zhí)行某種功能的預(yù)編譯SQL語句。存儲(chǔ)過程是一段在服務(wù)器上執(zhí)行的程序，他在服務(wù)器端對(duì)數(shù)據(jù)庫記錄進(jìn)行處理，再把結(jié)果返回到客戶端。5.1.2 存儲(chǔ)過程的優(yōu)點(diǎn)加快查詢的執(zhí)行速度。由于存儲(chǔ)過程第一次被執(zhí)行后，其執(zhí)行規(guī)劃就存儲(chǔ)在高速緩存中。在以后的操作中，只需從高速

2、緩存中調(diào)用編譯好的存儲(chǔ)過程的二進(jìn)制代碼執(zhí)行即可。,數(shù)據(jù)庫應(yīng)用技術(shù),5.1 存儲(chǔ)過程,減少網(wǎng)絡(luò)的數(shù)據(jù)流量對(duì)于需要很多條T-SQL語句才能完成任務(wù)的操作，只需要在網(wǎng)絡(luò)間傳送單獨(dú)指令就可實(shí)現(xiàn)，省去了逐條發(fā)送指令所需的網(wǎng)絡(luò)流量。另一方面避免把大量的數(shù)據(jù)從服務(wù)器下載到客戶端，減少網(wǎng)絡(luò)上傳輸量，服務(wù)器只需將計(jì)算結(jié)果傳給客戶端即可。因此可以提高客戶端的工作效率。 提供了一種安全機(jī)制只要用戶被授予執(zhí)行存儲(chǔ)過程的權(quán)限，則不管用戶是否被授予了存儲(chǔ)

3、過程操作過程中涉及的權(quán)限（如修改表），他都可以毫無限制的執(zhí)行它。,數(shù)據(jù)庫應(yīng)用技術(shù),5.1 存儲(chǔ)過程,允許程序模塊化設(shè)計(jì)客戶端不需要知道復(fù)雜的數(shù)據(jù)結(jié)構(gòu)或業(yè)務(wù)邏輯過程，只需把查詢指令通過存儲(chǔ)過程發(fā)給服務(wù)器，服務(wù)器就能把需要的數(shù)據(jù)返回給用戶。因此可以把大量的業(yè)務(wù)邏輯用存儲(chǔ)過程封裝起來，當(dāng)數(shù)據(jù)結(jié)構(gòu)發(fā)生變化時(shí)，只需要改動(dòng)組成過程的算法。使存儲(chǔ)過程可獨(dú)立于程序源代碼而單獨(dú)修改，提高了程序的可重用性。,數(shù)據(jù)庫應(yīng)用技術(shù),5.1 存儲(chǔ)過程,5.1.3

4、 存儲(chǔ)過程的分類系統(tǒng)存儲(chǔ)過程。存儲(chǔ)在master數(shù)據(jù)庫中。又分為一般系統(tǒng)存儲(chǔ)過程（以sp_為前綴）和擴(kuò)展存儲(chǔ)過程（以xp_為前綴）。用戶存儲(chǔ)過程。5.1.4 創(chuàng)建存儲(chǔ)過程CREATE PROCEDURE 存儲(chǔ)過程名 AS ……5.1.4.1不帶參數(shù)的存儲(chǔ)過程如：從數(shù)據(jù)庫的student, student_course,course表中查詢，返回學(xué)生學(xué)號(hào)、姓名、課程號(hào)、成績、學(xué)分。,數(shù)據(jù)庫應(yīng)用技術(shù),5.1 存儲(chǔ)過程,Cre

5、ate procedure student_infoAsSelect a.sno, sname, cno, sgrade, creditFrom Student a inner join student_course bOn a.sno=b.sno inner join course tOn b.cno=t.cnogo,數(shù)據(jù)庫應(yīng)用技術(shù),5.1 存儲(chǔ)過程,5.1.4.2 帶參數(shù)的存儲(chǔ)過程如：從數(shù)據(jù)庫的三個(gè)表中查詢某人指定課程

6、的成績和學(xué)分Create procedure student_info1@name char(8),@cname char(16)AsSelect a.sno, sname, cno, sgrade, creditFrom student a inner join student_course bOn a.sno=b.sno inner join course tOn b.cno=t.cnoWhere a.sname=@

7、name and t.cname=@cname,數(shù)據(jù)庫應(yīng)用技術(shù),5.1 存儲(chǔ)過程,5.1.4.3 帶默認(rèn)值參數(shù)的存儲(chǔ)過程如：從三個(gè)表的連接中返回制定學(xué)生的學(xué)號(hào)、姓名、所選課程名稱及該課程的成績 ，如果沒有提供參數(shù)，則使用默認(rèn)值Create procedure st_info@name varchar(30)=’劉%’AsSelect a.sno, a.sname, c.cno, b.sgradeFrom student a

8、inner join student_course bOn a.sno=b.sno inner join course tOn b.cno=t.cnoWhere sname like @name,數(shù)據(jù)庫應(yīng)用技術(shù),5.1 存儲(chǔ)過程,5.1.4.4 通過參數(shù)返回?cái)?shù)據(jù)的存儲(chǔ)過程 如：計(jì)算指定學(xué)生的總學(xué)分。 Create procedure totalcredit@name varchar(40), @total int outp

9、utAsSelect @total=sum(credit)From student, student_course, courseWhere student.name=@name and student.sno=student_course.snoGroup by student.sno,數(shù)據(jù)庫應(yīng)用技術(shù),5.1 存儲(chǔ)過程,5.1.5 使用存儲(chǔ)過程的注意事項(xiàng)存儲(chǔ)過程所用的查詢只在編譯時(shí)進(jìn)行優(yōu)化。當(dāng)對(duì)數(shù)據(jù)庫表進(jìn)行了索引等影響數(shù)據(jù)

10、庫統(tǒng)計(jì)的修改后，已編譯的存儲(chǔ)過程可能會(huì)失去效率。這時(shí)必須重新編譯存儲(chǔ)過程，以重新優(yōu)化查詢。使用系統(tǒng)存儲(chǔ)過程sp_recompile對(duì)存儲(chǔ)過程進(jìn)行重新編譯。如EXEC sp_recompile company將對(duì)基于表company的存儲(chǔ)過程重新編譯。在存儲(chǔ)過程中創(chuàng)建的臨時(shí)表，只能用于該存儲(chǔ)過程，當(dāng)存儲(chǔ)過程執(zhí)行完畢后，臨時(shí)表自動(dòng)銷毀。,數(shù)據(jù)庫應(yīng)用技術(shù),5.1 存儲(chǔ)過程,5.1.6 調(diào)用存儲(chǔ)過程Exec stored_procedure

11、_name parameters5.1.6.1 輸入?yún)?shù)的使用根據(jù)定義時(shí)的聲明順序賦值例如：1、EXEC student_info1 ‘張三’, ‘大學(xué)物理’2、EXEC st_info--省略默認(rèn)值3、EXEC st_info ‘張三’--未省略默認(rèn)值注意：若有多個(gè)默認(rèn)值，則在調(diào)用時(shí)只能從右往左省略帶默認(rèn)值參數(shù)，不能跳躍著省略帶默認(rèn)值參數(shù)（根據(jù)參數(shù)名稱賦值除外）?；蛘呤褂肈EFAULT調(diào)用，即在省略參數(shù)的

12、地方用DEFAULT代替。,,數(shù)據(jù)庫應(yīng)用技術(shù),5.1 存儲(chǔ)過程,根據(jù)參數(shù)名稱賦值例如： EXEC student_info1 @name=‘張三’, @cname= ‘大學(xué)物理’5.1.6.2 輸出參數(shù)的使用在調(diào)用時(shí)，必須在要保存輸出參數(shù)值的變量處指定OUTPUT關(guān)鍵字例如：declare @total_credit intexec totalcredit ‘張三’, @total_credit output

13、,數(shù)據(jù)庫應(yīng)用技術(shù),5.1 存儲(chǔ)過程,5.1.6.3 返回值的使用用于保存存儲(chǔ)過程中使用return語句返回的值。該返回值一般用于記錄存儲(chǔ)過程執(zhí)行過程中的狀態(tài)，如是否出錯(cuò)等。例如：declare @status intexec @status = getinfo--getinfo是一個(gè)存儲(chǔ)過程if @status 0 print “存儲(chǔ)過程getinfo執(zhí)行出錯(cuò)！”,數(shù)據(jù)庫應(yīng)用技術(shù),變量定義和流程控制,變量的

14、定義和使用 SQL Srever中有兩種類型的變量, 局部變量和全局變量.它們的區(qū)別是作用域不同. 局部變量是用戶自定義的變量, 它的使用范圍局限于定義它的批語句或過程體內(nèi).在程序中,通常用局部變量保存臨時(shí)數(shù)據(jù)或從存儲(chǔ)過程返回的值.全局變量是由SQLServer系統(tǒng)提供并負(fù)責(zé)維護(hù)的一些變量。它們用來保存SQLServer系統(tǒng)的某些參數(shù)值和性能統(tǒng)計(jì)數(shù)據(jù)。全局變量可以在任何地方被任何程序取用。用戶一般不能隨意地

15、創(chuàng)建全局變量。1、局部變量 使用一個(gè)局部變量之前，需要聲明該變量。,數(shù)據(jù)庫應(yīng)用技術(shù),,DECLARE @variable_name datatype [,@variable_name datatype]……注：變量名前必須為@例1 DECLEAR @MyCounter INT例2 DECLEAR @last_name VARCHAR(30),@FNAME

16、 VARCHAR(20), @STATE VARCHAR(2)注：局部變量在被聲明后，系統(tǒng)自動(dòng)將它們初始化為NULL。若要為變量賦值可使用SET語句或SELECT語句。 SET語句賦值的語法格式： SET @變量名=表達(dá)式,數(shù)據(jù)庫應(yīng)用技術(shù),,例3：是一個(gè)SET語句的示例，在語句中聲明了兩個(gè)變量，并為它們賦值，然后將它們應(yīng)用到SELECT語句的WHERE子句中。 USE Northwind GO

17、 DECLARE @FirstNameVariable VARCHAR(20), @RegionVaribable VARCHAR(30) SET @FirstNameVariable=‘Anne’ SET @RegionVariable=‘WA’ SELECT LastName, FirstName,Title FROM Employees

18、 WHERE FirstName=@FirstNameVariable OR Region=@RegionVariableGO,數(shù)據(jù)庫應(yīng)用技術(shù),,例4：使用SELECT語句為局部變量賦值 USE Northwind Go Declear @EmpNameVar VARCHAR(20) SELECT @EmpNameVar=Fi

19、rstName FROM Employees WHERE EmployeeID=9,數(shù)據(jù)庫應(yīng)用技術(shù),,二、流程控制語句 1、注釋 SQLSERVER支持兩種類型的注釋字符 第一種：--（兩個(gè)減號(hào)），用于單行注釋 第二種：/*…….*/，用于注釋多行 2、BEGIN…END 用戶可以使用BEGIN…END將多條SQL語句封裝起來,構(gòu)成一個(gè)單獨(dú)的執(zhí)行單元.

20、 語法格式: BEGIN SQL語句或語句塊 END,數(shù)據(jù)庫應(yīng)用技術(shù),,例5: USE pubs GO CREATE TRIGGER deltitle ON titles FOR delete AS IF (SELECT COUNT(*)

21、 FROM deleted, sales WHERE sales.title_id=deleted.title_id)>0 BEGIN ROLLBACK TRANSACTION PRINT ‘You can’t delete a title with sales.’ END

22、,數(shù)據(jù)庫應(yīng)用技術(shù),,3、IF…ELSE 語法格式： IF 條件 SQL語句或語句塊 ELSE SQL語句或語句塊,數(shù)據(jù)庫應(yīng)用技術(shù),,例6：USE pubsIF（SELECT AVG(price) FROM titles WHERE type=‘mod_cook’）

23、<$15BEGIN PRINT ’The Following titles are excellent mod_cook books:’ PRINT ‘ ’ SELECT SUBSTRING(title,1,35) AS Title FROM titles WHERE type=‘mod_cook’ENDELSE PRI

24、NT ‘Average title price is more than $15.’,數(shù)據(jù)庫應(yīng)用技術(shù),,IF…END語句的嵌套例：USE pubsIF（SELECT AVG(price) FROM titles WHERE type=‘mod_cook’）<$15BEGIN PRINT ’The Following titles are excellent m

25、od_cook books:’ PRINT ‘ ’ SELECT SUBSTRING(title,1,35) AS Title FROM titles WHERE type=‘mod_cook’ENDELSE IF（SELECT AVG(price) FROM titles WHERE type=‘mod_cook’）<$15,數(shù)據(jù)庫應(yīng)用

26、技術(shù),,BEGIN PRINT ’The Following titles are excellent mod_cook books:’ PRINT ‘ ’ SELECT SUBSTRING(title,1,35) AS Title FROM titles WHERE type=‘mod_cook’END4、CASE CASE表達(dá)式用于多分支

27、的選擇結(jié)構(gòu)，雖然在這種情況下使用IF…ELSE語句也可以實(shí)現(xiàn)，但使用CASE語句可使程序的結(jié)構(gòu)更加精煉。 語法格式：CASE input_expression WHEN WHEN_expression THEN RESULT_expression[…n] [ELSE else_result_expression],數(shù)據(jù)庫應(yīng)用技術(shù),,其中，input_expression為條件判斷表達(dá)式，when_exp

28、ression用于與input_expression比較，當(dāng)與input_expression的值相等時(shí)則執(zhí)行該分支的result_expression語句。當(dāng)沒有一個(gè)when_expression與input_expression的值相等時(shí)執(zhí)行else_result_expression語句。5、WHILE、 CONTINUE 、BREAK語法格式(WHILE實(shí)現(xiàn)代碼的重復(fù)執(zhí)行) WHILE Boolean_ex

29、pression { sql語句或語句塊},數(shù)據(jù)庫應(yīng)用技術(shù),,例6：USE pubsGOWHILE(SELECT AVG(price) FROM titles)$50 BREAK ELSE CONTINUEENDPRINT ‘Too much for the market to bear’,數(shù)據(jù)庫應(yīng)用技術(shù),,6、GOTO語句的使用例7