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

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

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

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

4、 存儲過程的分類系統(tǒng)存儲過程。存儲在master數(shù)據(jù)庫中。又分為一般系統(tǒng)存儲過程（以sp_為前綴）和擴展存儲過程（以xp_為前綴）。用戶存儲過程。5.1.4 創(chuàng)建存儲過程CREATE PROCEDURE 存儲過程名 AS ……5.1.4.1不帶參數(shù)的存儲過程如：從數(shù)據(jù)庫的student, student_course,course表中查詢，返回學(xué)生學(xué)號、姓名、課程號、成績、學(xué)分。,數(shù)據(jù)庫應(yīng)用技術(shù),5.1 存儲過程,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 存儲過程,5.1.4.2 帶參數(shù)的存儲過程如：從數(shù)據(jù)庫的三個表中查詢某人指定課程

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 存儲過程,5.1.4.3 帶默認值參數(shù)的存儲過程如：從三個表的連接中返回制定學(xué)生的學(xué)號、姓名、所選課程名稱及該課程的成績 ，如果沒有提供參數(shù)，則使用默認值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 存儲過程,5.1.4.4 通過參數(shù)返回數(shù)據(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 存儲過程,5.1.5 使用存儲過程的注意事項存儲過程所用的查詢只在編譯時進行優(yōu)化。當(dāng)對數(shù)據(jù)庫表進行了索引等影響數(shù)據(jù)

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

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

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

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

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

15、創(chuàng)建全局變量。1、局部變量 使用一個局部變量之前，需要聲明該變量。,數(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)自動將它們初始化為NULL。若要為變量賦值可使用SET語句或SELECT語句。 SET語句賦值的語法格式： SET @變量名=表達式,數(shù)據(jù)庫應(yīng)用技術(shù),,例3：是一個SET語句的示例，在語句中聲明了兩個變量，并為它們賦值，然后將它們應(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支持兩種類型的注釋字符 第一種：--（兩個減號），用于單行注釋 第二種：/*…….*/，用于注釋多行 2、BEGIN…END 用戶可以使用BEGIN…END將多條SQL語句封裝起來,構(gòu)成一個單獨的執(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表達式用于多分支

27、的選擇結(jié)構(gòu)，雖然在這種情況下使用IF…ELSE語句也可以實現(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為條件判斷表達式，when_exp

28、ression用于與input_expression比較，當(dāng)與input_expression的值相等時則執(zhí)行該分支的result_expression語句。當(dāng)沒有一個when_expression與input_expression的值相等時執(zhí)行else_result_expression語句。5、WHILE、 CONTINUE 、BREAK語法格式(WHILE實現(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