版權(quán)說(shuō)明:本文檔由用戶(hù)提供并上傳,收益歸屬內(nèi)容提供方,若內(nèi)容存在侵權(quán),請(qǐng)進(jìn)行舉報(bào)或認(rèn)領(lǐng)
文檔簡(jiǎn)介
1、數(shù)據(jù)庫(kù)應(yīng)用技術(shù),存儲(chǔ)過(guò)程觸發(fā)器事務(wù)和鎖,第5章 T-SQL程序設(shè)計(jì),數(shù)據(jù)庫(kù)應(yīng)用技術(shù),5.1 存儲(chǔ)過(guò)程,5.1.1 存儲(chǔ)過(guò)程的概念一組已被編輯在一起的,存儲(chǔ)在服務(wù)器上,執(zhí)行某種功能的預(yù)編譯SQL語(yǔ)句。存儲(chǔ)過(guò)程是一段在服務(wù)器上執(zhí)行的程序,他在服務(wù)器端對(duì)數(shù)據(jù)庫(kù)記錄進(jìn)行處理,再把結(jié)果返回到客戶(hù)端。5.1.2 存儲(chǔ)過(guò)程的優(yōu)點(diǎn)加快查詢(xún)的執(zhí)行速度。由于存儲(chǔ)過(guò)程第一次被執(zhí)行后,其執(zhí)行規(guī)劃就存儲(chǔ)在高速緩存中。在以后的操作中,只需從高速
2、緩存中調(diào)用編譯好的存儲(chǔ)過(guò)程的二進(jìn)制代碼執(zhí)行即可。,數(shù)據(jù)庫(kù)應(yīng)用技術(shù),5.1 存儲(chǔ)過(guò)程,減少網(wǎng)絡(luò)的數(shù)據(jù)流量對(duì)于需要很多條T-SQL語(yǔ)句才能完成任務(wù)的操作,只需要在網(wǎng)絡(luò)間傳送單獨(dú)指令就可實(shí)現(xiàn),省去了逐條發(fā)送指令所需的網(wǎng)絡(luò)流量。另一方面避免把大量的數(shù)據(jù)從服務(wù)器下載到客戶(hù)端,減少網(wǎng)絡(luò)上傳輸量,服務(wù)器只需將計(jì)算結(jié)果傳給客戶(hù)端即可。因此可以提高客戶(hù)端的工作效率。 提供了一種安全機(jī)制只要用戶(hù)被授予執(zhí)行存儲(chǔ)過(guò)程的權(quán)限,則不管用戶(hù)是否被授予了存儲(chǔ)
3、過(guò)程操作過(guò)程中涉及的權(quán)限(如修改表),他都可以毫無(wú)限制的執(zhí)行它。,數(shù)據(jù)庫(kù)應(yīng)用技術(shù),5.1 存儲(chǔ)過(guò)程,允許程序模塊化設(shè)計(jì)客戶(hù)端不需要知道復(fù)雜的數(shù)據(jù)結(jié)構(gòu)或業(yè)務(wù)邏輯過(guò)程,只需把查詢(xún)指令通過(guò)存儲(chǔ)過(guò)程發(fā)給服務(wù)器,服務(wù)器就能把需要的數(shù)據(jù)返回給用戶(hù)。因此可以把大量的業(yè)務(wù)邏輯用存儲(chǔ)過(guò)程封裝起來(lái),當(dāng)數(shù)據(jù)結(jié)構(gòu)發(fā)生變化時(shí),只需要改動(dòng)組成過(guò)程的算法。使存儲(chǔ)過(guò)程可獨(dú)立于程序源代碼而單獨(dú)修改,提高了程序的可重用性。,數(shù)據(jù)庫(kù)應(yīng)用技術(shù),5.1 存儲(chǔ)過(guò)程,5.1.3
4、 存儲(chǔ)過(guò)程的分類(lèi)系統(tǒng)存儲(chǔ)過(guò)程。存儲(chǔ)在master數(shù)據(jù)庫(kù)中。又分為一般系統(tǒng)存儲(chǔ)過(guò)程(以sp_為前綴)和擴(kuò)展存儲(chǔ)過(guò)程(以xp_為前綴)。用戶(hù)存儲(chǔ)過(guò)程。5.1.4 創(chuàng)建存儲(chǔ)過(guò)程CREATE PROCEDURE 存儲(chǔ)過(guò)程名 AS ……5.1.4.1不帶參數(shù)的存儲(chǔ)過(guò)程如:從數(shù)據(jù)庫(kù)的student, student_course,course表中查詢(xún),返回學(xué)生學(xué)號(hào)、姓名、課程號(hào)、成績(jī)、學(xué)分。,數(shù)據(jù)庫(kù)應(yīng)用技術(shù),5.1 存儲(chǔ)過(guò)程,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ù)庫(kù)應(yīng)用技術(shù),5.1 存儲(chǔ)過(guò)程,5.1.4.2 帶參數(shù)的存儲(chǔ)過(guò)程如:從數(shù)據(jù)庫(kù)的三個(gè)表中查詢(xún)某人指定課程
6、的成績(jī)和學(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ù)庫(kù)應(yīng)用技術(shù),5.1 存儲(chǔ)過(guò)程,5.1.4.3 帶默認(rèn)值參數(shù)的存儲(chǔ)過(guò)程如:從三個(gè)表的連接中返回制定學(xué)生的學(xué)號(hào)、姓名、所選課程名稱(chēng)及該課程的成績(jī) ,如果沒(méi)有提供參數(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ù)庫(kù)應(yīng)用技術(shù),5.1 存儲(chǔ)過(guò)程,5.1.4.4 通過(guò)參數(shù)返回?cái)?shù)據(jù)的存儲(chǔ)過(guò)程 如:計(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ù)庫(kù)應(yīng)用技術(shù),5.1 存儲(chǔ)過(guò)程,5.1.5 使用存儲(chǔ)過(guò)程的注意事項(xiàng)存儲(chǔ)過(guò)程所用的查詢(xún)只在編譯時(shí)進(jìn)行優(yōu)化。當(dāng)對(duì)數(shù)據(jù)庫(kù)表進(jìn)行了索引等影響數(shù)據(jù)
10、庫(kù)統(tǒng)計(jì)的修改后,已編譯的存儲(chǔ)過(guò)程可能會(huì)失去效率。這時(shí)必須重新編譯存儲(chǔ)過(guò)程,以重新優(yōu)化查詢(xún)。使用系統(tǒng)存儲(chǔ)過(guò)程sp_recompile對(duì)存儲(chǔ)過(guò)程進(jìn)行重新編譯。如EXEC sp_recompile company將對(duì)基于表company的存儲(chǔ)過(guò)程重新編譯。在存儲(chǔ)過(guò)程中創(chuàng)建的臨時(shí)表,只能用于該存儲(chǔ)過(guò)程,當(dāng)存儲(chǔ)過(guò)程執(zhí)行完畢后,臨時(shí)表自動(dòng)銷(xiāo)毀。,數(shù)據(jù)庫(kù)應(yīng)用技術(shù),5.1 存儲(chǔ)過(guò)程,5.1.6 調(diào)用存儲(chǔ)過(guò)程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ù)名稱(chēng)賦值除外)?;蛘呤褂肈EFAULT調(diào)用,即在省略參數(shù)的
12、地方用DEFAULT代替。,,數(shù)據(jù)庫(kù)應(yīng)用技術(shù),5.1 存儲(chǔ)過(guò)程,根據(jù)參數(shù)名稱(chēng)賦值例如: 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ù)庫(kù)應(yīng)用技術(shù),5.1 存儲(chǔ)過(guò)程,5.1.6.3 返回值的使用用于保存存儲(chǔ)過(guò)程中使用return語(yǔ)句返回的值。該返回值一般用于記錄存儲(chǔ)過(guò)程執(zhí)行過(guò)程中的狀態(tài),如是否出錯(cuò)等。例如:declare @status intexec @status = getinfo--getinfo是一個(gè)存儲(chǔ)過(guò)程if @status 0 print “存儲(chǔ)過(guò)程getinfo執(zhí)行出錯(cuò)!”,數(shù)據(jù)庫(kù)應(yīng)用技術(shù),變量定義和流程控制,變量的
14、定義和使用 SQL Srever中有兩種類(lèi)型的變量, 局部變量和全局變量.它們的區(qū)別是作用域不同. 局部變量是用戶(hù)自定義的變量, 它的使用范圍局限于定義它的批語(yǔ)句或過(guò)程體內(nèi).在程序中,通常用局部變量保存臨時(shí)數(shù)據(jù)或從存儲(chǔ)過(guò)程返回的值.全局變量是由SQLServer系統(tǒng)提供并負(fù)責(zé)維護(hù)的一些變量。它們用來(lái)保存SQLServer系統(tǒng)的某些參數(shù)值和性能統(tǒng)計(jì)數(shù)據(jù)。全局變量可以在任何地方被任何程序取用。用戶(hù)一般不能隨意地
15、創(chuàng)建全局變量。1、局部變量 使用一個(gè)局部變量之前,需要聲明該變量。,數(shù)據(jù)庫(kù)應(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語(yǔ)句或SELECT語(yǔ)句。 SET語(yǔ)句賦值的語(yǔ)法格式: SET @變量名=表達(dá)式,數(shù)據(jù)庫(kù)應(yīng)用技術(shù),,例3:是一個(gè)SET語(yǔ)句的示例,在語(yǔ)句中聲明了兩個(gè)變量,并為它們賦值,然后將它們應(yīng)用到SELECT語(yǔ)句的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ù)庫(kù)應(yīng)用技術(shù),,例4:使用SELECT語(yǔ)句為局部變量賦值 USE Northwind Go Declear @EmpNameVar VARCHAR(20) SELECT @EmpNameVar=Fi
19、rstName FROM Employees WHERE EmployeeID=9,數(shù)據(jù)庫(kù)應(yīng)用技術(shù),,二、流程控制語(yǔ)句 1、注釋 SQLSERVER支持兩種類(lèi)型的注釋字符 第一種:--(兩個(gè)減號(hào)),用于單行注釋 第二種:/*…….*/,用于注釋多行 2、BEGIN…END 用戶(hù)可以使用BEGIN…END將多條SQL語(yǔ)句封裝起來(lái),構(gòu)成一個(gè)單獨(dú)的執(zhí)行單元.
20、 語(yǔ)法格式: BEGIN SQL語(yǔ)句或語(yǔ)句塊 END,數(shù)據(jù)庫(kù)應(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ù)庫(kù)應(yīng)用技術(shù),,3、IF…ELSE 語(yǔ)法格式: IF 條件 SQL語(yǔ)句或語(yǔ)句塊 ELSE SQL語(yǔ)句或語(yǔ)句塊,數(shù)據(jù)庫(kù)應(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ù)庫(kù)應(yīng)用技術(shù),,IF…END語(yǔ)句的嵌套例: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ù)庫(kù)應(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語(yǔ)句也可以實(shí)現(xiàn),但使用CASE語(yǔ)句可使程序的結(jié)構(gòu)更加精煉。 語(yǔ)法格式:CASE input_expression WHEN WHEN_expression THEN RESULT_expression[…n] [ELSE else_result_expression],數(shù)據(jù)庫(kù)應(yīng)用技術(shù),,其中,input_expression為條件判斷表達(dá)式,when_exp
28、ression用于與input_expression比較,當(dāng)與input_expression的值相等時(shí)則執(zhí)行該分支的result_expression語(yǔ)句。當(dāng)沒(méi)有一個(gè)when_expression與input_expression的值相等時(shí)執(zhí)行else_result_expression語(yǔ)句。5、WHILE、 CONTINUE 、BREAK語(yǔ)法格式(WHILE實(shí)現(xiàn)代碼的重復(fù)執(zhí)行) WHILE Boolean_ex
29、pression { sql語(yǔ)句或語(yǔ)句塊},數(shù)據(jù)庫(kù)應(yīng)用技術(shù),,例6:USE pubsGOWHILE(SELECT AVG(price) FROM titles)$50 BREAK ELSE CONTINUEENDPRINT ‘Too much for the market to bear’,數(shù)據(jù)庫(kù)應(yīng)用技術(shù),,6、GOTO語(yǔ)句的使用例7
溫馨提示
- 1. 本站所有資源如無(wú)特殊說(shuō)明,都需要本地電腦安裝OFFICE2007和PDF閱讀器。圖紙軟件為CAD,CAXA,PROE,UG,SolidWorks等.壓縮文件請(qǐng)下載最新的WinRAR軟件解壓。
- 2. 本站的文檔不包含任何第三方提供的附件圖紙等,如果需要附件,請(qǐng)聯(lián)系上傳者。文件的所有權(quán)益歸上傳用戶(hù)所有。
- 3. 本站RAR壓縮包中若帶圖紙,網(wǎng)頁(yè)內(nèi)容里面會(huì)有圖紙預(yù)覽,若沒(méi)有圖紙預(yù)覽就沒(méi)有圖紙。
- 4. 未經(jīng)權(quán)益所有人同意不得將文件中的內(nèi)容挪作商業(yè)或盈利用途。
- 5. 眾賞文庫(kù)僅提供信息存儲(chǔ)空間,僅對(duì)用戶(hù)上傳內(nèi)容的表現(xiàn)方式做保護(hù)處理,對(duì)用戶(hù)上傳分享的文檔內(nèi)容本身不做任何修改或編輯,并不能對(duì)任何下載內(nèi)容負(fù)責(zé)。
- 6. 下載文件中如有侵權(quán)或不適當(dāng)內(nèi)容,請(qǐng)與我們聯(lián)系,我們立即糾正。
- 7. 本站不保證下載資源的準(zhǔn)確性、安全性和完整性, 同時(shí)也不承擔(dān)用戶(hù)因使用這些下載資源對(duì)自己和他人造成任何形式的傷害或損失。
最新文檔
- 存儲(chǔ)過(guò)程與觸發(fā)器
- 實(shí)驗(yàn)八 存儲(chǔ)過(guò)程和觸發(fā)器
- 實(shí)驗(yàn)十存儲(chǔ)過(guò)程和觸發(fā)器答案
- 存儲(chǔ)過(guò)程和觸發(fā)器(數(shù)據(jù)庫(kù)實(shí)驗(yàn)5)
- sql 2005 的存儲(chǔ)過(guò)程和觸發(fā)器調(diào)試大法(原創(chuàng))
- sql server存儲(chǔ)過(guò)程和觸發(fā)器操作實(shí)訓(xùn)講義
- 實(shí)驗(yàn)3(86) 視圖、存儲(chǔ)過(guò)程與觸發(fā)器
- 基本rs觸發(fā)器和d觸發(fā)器
- 第6章 存儲(chǔ)過(guò)程、觸發(fā)器及游標(biāo)
- 第五章 鎖存器和觸發(fā)器
- openbase4.0存儲(chǔ)過(guò)程和觸發(fā)器的設(shè)計(jì)與實(shí)現(xiàn)
- 實(shí)驗(yàn)六 rs觸發(fā)器和d觸發(fā)器
- 實(shí)驗(yàn)七觸發(fā)器與事務(wù)設(shè)計(jì)
- 主從rs觸發(fā)器,jk觸發(fā)器
- 實(shí)驗(yàn)四 基本rs觸發(fā)器和d觸發(fā)器2
- d觸發(fā)器&雙d觸發(fā)器
- 觸發(fā)器語(yǔ)句
- 觸發(fā)器總結(jié)
- ppt觸發(fā)器
- 邏輯門(mén)電路和觸發(fā)器
評(píng)論
0/150
提交評(píng)論