數據庫技術與應用第5章(TransactSQL、存儲過程等)

1、 數據庫技術及應用數據庫技術及應用db1本章主要內容本章主要內容: t-sql的基本語法的基本語法 存儲過程存儲過程(難點難點) 觸發(fā)器觸發(fā)器(難點難點)第第5章章 transact-sql、 存儲過程和觸發(fā)器存儲過程和觸發(fā)器25.1 transact-sql語言語言5.1.1 數據類型數據類型數據類型見表數據類型見表5-1。下面做一些必要的說明。下面做一些必要的說明。1. 整型整型(bigint、int、smallint、tinyint)整型數參與任何算術運算的結果只保留數值的整數部整型數參與任何算術運算的結果只保留數值的整數部分。分。declare n1 int , n2 int , n3

2、 int -變量的聲明變量的聲明set n1=14set n2=3set n3=n1/n2 -n3=4select n1 as n1, n2 as n2 , n3 as n3 -起別名起別名35.1.1 數據類型數據類型2. 浮點型浮點型(float、real 、decimal、numerc) float和和real屬于近似數據類型。特點是表示范屬于近似數據類型。特點是表示范圍大圍大,但任何一個數都僅是一個近似值。適合用但任何一個數都僅是一個近似值。適合用于科學計算。于科學計算。 decimal和和numerc是精確數值類型，表示的是是精確數值類型，表示的是一個精確值。適合用于財務金融（如：工

3、資）。一個精確值。適合用于財務金融（如：工資）。5.1.1 數據類型數據類型45.1.1 數據類型數據類型decimal和和numeric的表示范圍和精度及所占內的表示范圍和精度及所占內存都一樣，為與存都一樣，為與ansi標準兼容標準兼容sql server支持支持這兩種表示。這兩種表示。decimal(p , s ): p表示總位數表示總位數(不包括小數點不包括小數點)，介于介于138之間，默認值為之間，默認值為18。 s表示小數點后表示小數點后的位數，默認值是的位數，默認值是0。參數間關系：。參數間關系：0=s0) print 有事務在運行有事務在運行 else print 無事務在運行無

4、事務在運行203. 運算符運算符運算符的類型：運算符的類型： 算術運算符算術運算符 比較運算符比較運算符 字符串連接運算符字符串連接運算符 邏輯運算符邏輯運算符5.1.2 標識符、變量和運算符標識符、變量和運算符5.1.2 標識符、變量和運算符標識符、變量和運算符21(1) 算術運算符算術運算符：+、-、*、/、%(求余求余)(2) 比較運算符比較運算符: =、(不等于不等于)、=、=、!=(不等于不等于, 非非sql92標準標準)、!5.1.2 標識符、變量和運算符標識符、變量和運算符(3) 字符串連接運算符：字符串連接運算符：。 例如例如： print asd + dfg + 1234 結

5、果為：結果為： asddfg1234 空串空串( ( ) )作為單個空格處理。作為單個空格處理。 例如例如： abc + + efg ，結果為，結果為abc egf .5.1.2 標識符、變量和運算符標識符、變量和運算符225.1.2 標識符、變量和運算符標識符、變量和運算符運算符運算符含義含義 all(集合集合)如果一系列的比較都為如果一系列的比較都為true，則為，則為true。and邏輯與邏輯與 any(集合集合)如果一系列的比較中任何一個為如果一系列的比較中任何一個為true，則為則為true。between如果操作數在某個范圍之內，則為真如果操作數在某個范圍之內，則為真(4)邏輯運算

6、符（見表邏輯運算符（見表5-6 ）5.1.2 標識符、變量和運算符標識符、變量和運算符235.1.2 標識符、變量和運算符標識符、變量和運算符運算符運算符含義含義exists如果子查詢不為空，則為如果子查詢不為空，則為truein(集合集合)如果操作數在集合中，則為真如果操作數在集合中，則為真like如果操作數與某個字符串匹配，則為真如果操作數與某個字符串匹配，則為真not邏輯非邏輯非or邏輯或邏輯或some與與any一樣一樣表表5-6 (續(xù)續(xù))5.1.2 標識符、變量和運算符標識符、變量和運算符24(5) 運算符優(yōu)先級運算符優(yōu)先級 括號括號算術運算符算術運算符(字符串連接符字符串連接符) 比

7、較運算符比較運算符邏輯運算符邏輯運算符賦值（由高到賦值（由高到低）低）5.1.2 標識符、變量和運算符標識符、變量和運算符5.1.2 標識符、變量和運算符標識符、變量和運算符25 sql server提供了非常豐富的函數供用戶使提供了非常豐富的函數供用戶使用，同時也允許用戶定義自己的函數。用，同時也允許用戶定義自己的函數。5.1.3 函數函數查詢分析器中可查詢分析器中可以查看到系統(tǒng)提以查看到系統(tǒng)提供的各種函數。供的各種函數。5.1.3 函數函數261. transact-sql提供的系統(tǒng)函數有：提供的系統(tǒng)函數有：5.1.3 函數函數用戶自定義函數5.1.3 函數函數27函數分為三大類：函數分為

8、三大類： 行集函數（也稱表值函數）行集函數（也稱表值函數） 可以用在可以用在sql語句中語句中“表表”出現的任何地方出現的任何地方引用行集函數。引用行集函數。 聚合函數聚合函數 (第第4 章介紹過章介紹過)：也稱計合函數。：也稱計合函數。5.1.3 函數函數 標量值函數：標量值函數： 最常用的一類函數。通過有參或無參調用它，最常用的一類函數。通過有參或無參調用它，可以使其執(zhí)行并返回單個值?？梢允蛊鋱?zhí)行并返回單個值。5.1.3 函數函數285.1.3 函數函數系統(tǒng)提供的函數，參考教材。不再例舉。系統(tǒng)提供的函數，參考教材。不再例舉。5.1.3 函數函數295.1.3 函數函數2. 用戶自定義函數用

9、戶自定義函數用戶可以根據應用需要定義自己的函數。用戶可以根據應用需要定義自己的函數。自定義自定義函數分三種：標量函數函數分三種：標量函數、內嵌表值函內嵌表值函數數、多語句表值函數多語句表值函數。下面我們介紹前。下面我們介紹前2種。種。(1) 標量函數標量函數標量函數指函數返回單個值標量函數指函數返回單個值(字符串字符串數值等數值等).語法格式語法格式： 見下頁。見下頁。5.1.3 函數函數305.1.3 函數函數create function 函數名函數名 （形式參數定義表）（形式參數定義表）returns 函數返回值數據類型函數返回值數據類型asbegin 函數體函數體 return 返回值

10、表達式返回值表達式 end5.1.3 函數函數315.1.3 函數函數例例3 創(chuàng)建一個標量函數，返回某學生的平均分創(chuàng)建一個標量函數，返回某學生的平均分數。數。 學號作為函數參數。學號作為函數參數。5.1.3 函數函數create function get_avg ( sno char(6) )returns int asbegin declare temp int select temp =avg(成績成績) from 選課選課 where 學號學號= sno return temp end325.1.3 函數函數可以用下列語句調用可以用下列語句調用get_avg函數：函數：select db

11、o.get_avg(030101) as 030101平均成績平均成績注意注意：調用時必須給出用戶名：調用時必須給出用戶名dbo。它是函數。它是函數創(chuàng)建者。創(chuàng)建者。(2) 內嵌表值函數內嵌表值函數 內嵌表值函數指內嵌表值函數指 函數返回值是一個表函數返回值是一個表.語法格式語法格式：見下頁。：見下頁。5.1.3 函數函數335.1.3 函數函數create function 函數名函數名 （形式參數定義表）（形式參數定義表）returns table asreturn ( select查詢語句查詢語句 ) 函數返回值是一個查詢表。函數返回值是一個查詢表。5.1.3 函數函數345.1.3 函數

12、函數例例4 創(chuàng)建一個表值函數，返回平均分數大于或等于創(chuàng)建一個表值函數，返回平均分數大于或等于指定分數的學生學號和平均分數。函數參數為給定指定分數的學生學號和平均分數。函數參數為給定的一個的一個“分數分數”。5.1.3 函數函數create function get_all_avg ( score int )returns table asreturn select 學號學號, avg(成績成績) as 平均成績平均成績 from 選課選課 group by 學號學號 having avg(成績成績)= score注意注意：表達式：表達式avg( (成績成績) ) 必須給出別名。必須給出別名。3

13、55.1.3 函數函數函數的調用函數的調用：查詢平均成績大于：查詢平均成績大于80分的學生信息。分的學生信息。select 學生學生.學號學號, 姓名姓名 , 平均成績平均成績from get_all_avg(80) , 學生學生 where get_all_avg.學號學號 = 學生學生.學號學號 5.1.3 函數函數365.1.3 函數函數(3) 刪除用戶自定義函數刪除用戶自定義函數 drop function 函數名函數名(4) 修改用戶自定義函數修改用戶自定義函數 alter function 函數名函數名 參數定義與代碼參數定義與代碼5.1.3 函數函數37 流程控制語句用于控制流程

14、控制語句用于控制sql語句、語句塊、語句、語句塊、存儲過程或觸發(fā)器的執(zhí)行流程。存儲過程或觸發(fā)器的執(zhí)行流程。 主要的流程控制語句有：見下頁表。主要的流程控制語句有：見下頁表。5.1.4 流程控制語句流程控制語句5.1.4 流程控制語句流程控制語句385.1.4 流程控制語句流程控制語句語句語句功能功能beginend定義語句塊定義語句塊break退出循環(huán)退出循環(huán)continue重新開始循環(huán)重新開始循環(huán)goto label轉移到標號轉移到標號label處繼續(xù)執(zhí)行處繼續(xù)執(zhí)行ifelse選擇語句選擇語句return無條件返回語句無條件返回語句waitfor為語句執(zhí)行設置延遲時間為語句執(zhí)行設置延遲時間w

15、hile循環(huán)語句循環(huán)語句case 表達式表達式實現多分支實現多分支5.1.4 流程控制語句流程控制語句39 其他其他transact-sql語句還有語句還有：(1) /*/: 注釋語句注釋語句, 用于多行注釋用于多行注釋(2) - -(注釋語句注釋語句):用于單行或嵌套注釋用于單行或嵌套注釋(3)declare：變量聲明語句。：變量聲明語句。(4)execute：存儲過程執(zhí)行語句。：存儲過程執(zhí)行語句。(5) print: 終端輸出語句終端輸出語句5.1.4 流程控制語句流程控制語句5.1.4 流程控制語句流程控制語句401. begin end語句語句 該語句將多條該語句將多條sql語句封裝在

16、一起，構成一語句封裝在一起，構成一個語句塊。主要語句塊就可以在個語句塊。主要語句塊就可以在if/else、while等語句中作為一個整體來執(zhí)行。等語句中作為一個整體來執(zhí)行。 語法格式：語法格式：5.1.4 流程控制語句流程控制語句begin 若干若干sql語句語句end 5.1.4 流程控制語句流程控制語句415.1.4 流程控制語句流程控制語句2. ifelse語句語句 語法格式語法格式：if 條件條件 語句語句 | 語句塊語句塊1 else 語句語句 | 語句塊語句塊2 5.1.4 流程控制語句流程控制語句425.1.4 流程控制語句流程控制語句例例5 查詢選修查詢選修0001課的學生成績

17、，如有大于課的學生成績，如有大于90分以上的，則將其姓名顯示出來；若無人大分以上的，則將其姓名顯示出來；若無人大于于90分，則顯示分，則顯示“成績優(yōu)秀者為成績優(yōu)秀者為0個個”。if exists( select * from 選課選課 where 課號課號=0001 and 成績成績=90 ) select 姓名姓名 from 學生學生 join 選課選課 on 學生學生.學號學號=選課選課.學號學號 where 課號課號=0001 and 成績成績=90else print 成績優(yōu)秀者為成績優(yōu)秀者為0個個 5.1.4 流程控制語句流程控制語句433. while語句語句語法格式：語法格式：5

18、.1.4 流程控制語句流程控制語句while 條件條件 sql語句語句| 語句塊語句塊 break sql語句語句| 語句塊語句塊 continue 循環(huán)體循環(huán)體5.1.4 流程控制語句流程控制語句445.1.4 流程控制語句流程控制語句例例6 引用已建函數引用已建函數get_all_avg，分別求出平，分別求出平均成績大于均成績大于60、70、80、90的學生成績信的學生成績信息。息。5.1.4 流程控制語句流程控制語句455.1.4 流程控制語句流程控制語句declare j intset j=60while j100begin select *, j as 平均成績平均成績 大于大于 f

19、rom get_all_avg(j) set j=j+10end5.1.4 流程控制語句流程控制語句46*4. waitfor語句語句waitfor語句可以指定在某一時間點或時間語句可以指定在某一時間點或時間間隔后執(zhí)行間隔后執(zhí)行sql語句、語句塊、存儲過程或語句、語句塊、存儲過程或事務。事務。5.1.4 流程控制語句流程控制語句語法格式語法格式： waitfor delay time| time timetime 格式為：格式為：hh:mm:ss 。5.1.4 流程控制語句流程控制語句47例例7 對學生選課關系對學生選課關系(1) 設置在設置在9:00執(zhí)行一次查詢操執(zhí)行一次查詢操作；作；(2)

20、再設置在再設置在2小時以后再執(zhí)行一次查詢操作，小時以后再執(zhí)行一次查詢操作，查看學生選課情況。查看學生選課情況。5.1.4 流程控制語句流程控制語句begin waitfor time 9:00 - 等到等到9點點 select 學號學號, 課號課號 from 選課選課endgobegin waitfor delay 2:00 - 延遲延遲2小時小時 select 學號學號, 課號課號 from 選課選課end5.1.4 流程控制語句流程控制語句485. return語句語句 return無條件退出語句?？稍谌魏螘r候用無條件退出語句。可在任何時候用于從過程、批處理或語句塊中退出。不執(zhí)行于從過程、

21、批處理或語句塊中退出。不執(zhí)行位于位于 return 之后的語句。之后的語句。5.1.4 流程控制語句流程控制語句格式：格式：return 整數表達式整數表達式 return語句可以返回整數值。一般用于表示存儲過程或應語句可以返回整數值。一般用于表示存儲過程或應用程序的執(zhí)行狀態(tài)。如，所有用程序的執(zhí)行狀態(tài)。如，所有系統(tǒng)存儲過程系統(tǒng)存儲過程返回返回 0 值表示成值表示成功，返回非零值則表示失敗。功，返回非零值則表示失敗。注意：當用于存儲過程時，注意：當用于存儲過程時，return 不能返回空值。不能返回空值。5.1.4 流程控制語句流程控制語句496. case 表達式表達式 case 表達式用于多

22、分支結構，有兩種語法格式。表達式用于多分支結構，有兩種語法格式。(1) 簡單簡單case表達式表達式5.1.4 流程控制語句流程控制語句case 表達式表達式when 表達式表達式1 then 結果表達式結果表達式1 when 表達式表達式n then 結果表達式結果表達式nelse 結果表達式結果表達式n+1end5.1.4 流程控制語句流程控制語句50說明：說明： 可以是常量、屬性名、函數、子查可以是常量、屬性名、函數、子查詢和算術運算符、字符串運算符等組合的有詢和算術運算符、字符串運算符等組合的有意義的式子。意義的式子。 是在多個是在多個中選擇一中選擇一個表達式，它的值作為個表達式，它的

23、值作為“case表達式表達式”的結果值。的結果值。 當當所有匹配都不成立，所有匹配都不成立， case則返回則返回null。 可可以只有以只有 一個一個when子句。子句。 是一個表達式，可以用在是一個表達式，可以用在可以出現的任何地方?？梢猿霈F的任何地方。5.1.4 流程控制語句流程控制語句5.1.4 流程控制語句流程控制語句515.1.4 流程控制語句流程控制語句case 表達式表達式=表達式表達式1表達式表達式n結果表達式結果表達式1結果表達式結果表達式n結果表達式結果表達式n+1endelseelsewhenwhen5.1.4 流程控制語句流程控制語句52例例8 將百分制分數按優(yōu)、良、

24、中、及格和不及將百分制分數按優(yōu)、良、中、及格和不及格五個等級記分制的形式輸出。格五個等級記分制的形式輸出。5.1.4 流程控制語句流程控制語句select 學號學號, 課號課號, 成績成績 =case 成績成績/10when 6 then 及格及格when 7 then 中中when 8 then 良良when 9 then 優(yōu)優(yōu)when 10 then 優(yōu)優(yōu)else 不及格不及格endfrom 選課選課說明：說明：整數除整數整數除整數得到整數得到整數（系統(tǒng)（系統(tǒng)自動取整）自動取整）驗證：驗證： select 10/25.1.4 流程控制語句流程控制語句53(2) 搜索型搜索型case表達式表

25、達式5.1.4 流程控制語句流程控制語句casewhen 條件表達式條件表達式1 then 結果表達式結果表達式1when 條件表達式條件表達式n then 結果表達式結果表達式nelse 結果表達式結果表達式n+1end功能功能：得到一個滿足條件的值。如下圖所示。：得到一個滿足條件的值。如下圖所示。 5.1.4 流程控制語句流程控制語句545.1.4 流程控制語句流程控制語句條件表達式條件表達式n結果表達式結果表達式1結果表達式結果表達式n結果表達式結果表達式n+1endelseelsewhenwhen條件表達式條件表達式1case5.1.4 流程控制語句流程控制語句55例例9 將百分制分數

26、按優(yōu)、良、中、及格和不及將百分制分數按優(yōu)、良、中、及格和不及格五個等級記分制的形式輸出。格五個等級記分制的形式輸出。5.1.4 流程控制語句流程控制語句select 學號學號, 課號課號, 成績成績= case when 成績成績/10=6 then 及格及格 when 成績成績/10=7 then 中中 when 成績成績/10=8 then 良良 when 成績成績/10=9 then 優(yōu)優(yōu) when 成績成績/10=10 then 優(yōu)優(yōu) when 成績成績/10 is null then 無成績無成績 else 不及格不及格 endfrom 選課選課5.1.4 流程控制語句流程控制語句5

27、67. 批處理和腳本批處理和腳本(1)批處理批處理批處理批處理是作為一個組一起提交并執(zhí)行的若干是作為一個組一起提交并執(zhí)行的若干transact-sql語句語句。 批處理批處理可以交互地運行，或作為腳本的一部可以交互地運行，或作為腳本的一部分運行。一個腳本可以包含多個分運行。一個腳本可以包含多個transact-sql批處理。批處理。5.1.4 流程控制語句流程控制語句5.1.4 流程控制語句流程控制語句57 使用使用go語句定義一個語句定義一個批處理批處理。 使用使用go語句表示一個語句表示一個批處理批處理的結束。的結束。 go 不是通用的不是通用的transact-sql語句，語句，是只有是

28、只有sql server查詢分析器查詢分析器和和osql實用程序實用程序接受接受的語句的語句。5.1.4 流程控制語句流程控制語句5.1.4 流程控制語句流程控制語句58 sql server如何處理如何處理批處理批處理 sql server統(tǒng)一優(yōu)化、編譯和執(zhí)行一個統(tǒng)一優(yōu)化、編譯和執(zhí)行一個批處批處理理中的語句，但是，這些語句并不構成一個中的語句，但是，這些語句并不構成一個事務，不必作為一個可恢復單元執(zhí)行。事務，不必作為一個可恢復單元執(zhí)行。 用戶定義的變量作用域限制在一個用戶定義的變量作用域限制在一個批處理批處理，所，所以變量不能在以變量不能在go語句后引用。語句后引用。5.1.4 流程控制語句

29、流程控制語句例如例如 use 教學數據庫教學數據庫 select * from 學生學生 select * from 課程課程 go一個批處理一個批處理5.1.4 流程控制語句流程控制語句59例如例如：use 教學數據庫教學數據庫gocreate view 成績成績_v1(學號學號,平均成績平均成績 ) as select 學號學號, avg(成績成績) from 選課選課 group by 學號學號 goselect * from 成績成績_v1go5.1.4 流程控制語句流程控制語句5.1.4 流程控制語句流程控制語句60 批處理規(guī)則批處理規(guī)則 create procedure、creat

30、e view 、create trigger、create rule、create default這些語句不能在一個批這些語句不能在一個批處理中聯合使用，并且也不能與其他語句聯處理中聯合使用，并且也不能與其他語句聯合使用。即在一個批處理中，只能有一個合使用。即在一個批處理中，只能有一個create語句，其他語句都只能視為該語句語句，其他語句都只能視為該語句的的一部分。的的一部分。5.1.4 流程控制語句流程控制語句5.1.4 流程控制語句流程控制語句61 不能在一個批中修改表結構，然后在同一個不能在一個批中修改表結構，然后在同一個批中引用剛修改的列。批中引用剛修改的列。 如果如果execute

31、語句是批處理的第語句是批處理的第1句，則句，則可以省略，否則執(zhí)行任何不是批處理中第可以省略，否則執(zhí)行任何不是批處理中第1句的存儲過程都必須包含句的存儲過程都必須包含execute關鍵字。關鍵字。 一個批處理中用到的變量必須在其中定義，一個批處理中用到的變量必須在其中定義，不能引用其他批處理中定義的變量。不能引用其他批處理中定義的變量。5.1.4 流程控制語句流程控制語句5.1.4 流程控制語句流程控制語句62(2) 腳本腳本 腳本腳本是作為一個文件保存的一個或多個是作為一個文件保存的一個或多個transact-sql語句。語句。 腳步腳步可以在可以在sql server分析器或任何文本編分析器

32、或任何文本編輯器中編寫和保存，以輯器中編寫和保存，以.sql為擴展名。為擴展名。 腳本腳本必須在必須在sql server分析器中執(zhí)行。分析器中執(zhí)行。 腳本主要用于：重新創(chuàng)建數據庫或數據對象，腳本主要用于：重新創(chuàng)建數據庫或數據對象，或重復執(zhí)行一些語句?；蛑貜蛨?zhí)行一些語句。5.1.4 流程控制語句流程控制語句5.1.4 流程控制語句流程控制語句635.2.1 存儲過程的概念存儲過程的概念1.1.存儲過程的定義存儲過程的定義存儲過程存儲過程是存放在服務器上的是存放在服務器上的預先編譯預先編譯好的一組完好的一組完成特定功能的成特定功能的transact-sql語句集。語句集。5.2 存儲過程存儲過程

33、5.2.1 5.2.1 存儲過程的概念存儲過程的概念645.2.1 存儲過程的概念存儲過程的概念2.2.存儲過程的特點存儲過程的特點 使用存儲過程可以減少網絡數據流量。使用存儲過程可以減少網絡數據流量。 用戶不是在網絡上發(fā)送幾百條用戶不是在網絡上發(fā)送幾百條sql語句，而是通過語句，而是通過發(fā)送一條調用過程語句來執(zhí)行一個復雜的操作，這樣發(fā)送一條調用過程語句來執(zhí)行一個復雜的操作，這樣減少了服務器和客戶機之間傳遞的請求數。減少了服務器和客戶機之間傳遞的請求數。5.2.1 存儲過程的概念存儲過程的概念 增強代碼的重用性和共享性。增強代碼的重用性和共享性。 存儲過程可以被多個應用程序多次調用，實行代存儲

34、過程可以被多個應用程序多次調用，實行代碼的重用性和共享性。碼的重用性和共享性。655.2.1 存儲過程的概念存儲過程的概念 使用存儲過程可以加快系統(tǒng)的運行速度。使用存儲過程可以加快系統(tǒng)的運行速度。 存儲過程第一次執(zhí)行后，執(zhí)行計劃就被存放在高存儲過程第一次執(zhí)行后，執(zhí)行計劃就被存放在高速緩存中。以后執(zhí)行時，不必再執(zhí)行優(yōu)化處理，從速緩存中。以后執(zhí)行時，不必再執(zhí)行優(yōu)化處理，從而加快了系統(tǒng)運行速度。而加快了系統(tǒng)運行速度。5.2.1 存儲過程的概念存儲過程的概念 存儲過程提高了數據的安全性。存儲過程提高了數據的安全性。 系統(tǒng)管理員可以只授權用戶訪問存儲過程的權限，系統(tǒng)管理員可以只授權用戶訪問存儲過程的權限

35、，而不授予用戶訪問存儲過程中涉及的到的表的權限。而不授予用戶訪問存儲過程中涉及的到的表的權限。從而有效地保護了數據表中存儲過程涉及不到的數從而有效地保護了數據表中存儲過程涉及不到的數據的安全性。據的安全性。665.2.1 存儲過程的概念存儲過程的概念3.3.存儲過程的分類存儲過程的分類 系統(tǒng)存儲過程系統(tǒng)存儲過程 系統(tǒng)存儲過程系統(tǒng)存儲過程(由前綴由前綴sp_標識標識)存放在存放在master數據庫數據庫中?？梢宰鳛槊钪苯訄?zhí)行。中?？梢宰鳛槊钪苯訄?zhí)行。其中大部分存儲過程都可以在任何用戶數據庫其中大部分存儲過程都可以在任何用戶數據庫中執(zhí)行。中執(zhí)行。 用戶存儲過程用戶存儲過程：用戶自定義的存儲過程

36、。：用戶自定義的存儲過程。5.2.1 存儲過程的概念存儲過程的概念675.2.1 存儲過程的概念存儲過程的概念 擴展存儲過程擴展存儲過程 擴展存儲過程擴展存儲過程(主要由前綴主要由前綴xp標識標識)是指在是指在sql server環(huán)境外部執(zhí)行的環(huán)境外部執(zhí)行的dll。但是，它。但是，它們可以被加載到們可以被加載到sql server系統(tǒng)中，并按照系統(tǒng)中，并按照存儲過程方式存儲過程方式執(zhí)行。執(zhí)行。 遠程存儲過程遠程存儲過程遠程存儲過程是指從遠程服務器上調用的存儲遠程存儲過程是指從遠程服務器上調用的存儲過程。過程。5.2.1 存儲過程的概念存儲過程的概念685.2.2 存儲過程創(chuàng)建和執(zhí)行存儲過程創(chuàng)建

37、和執(zhí)行1. 創(chuàng)建存儲過程語句創(chuàng)建存儲過程語句create procedure 存儲過程名存儲過程名 ;num 參數參數1 數據類型數據類型 =默認值默認值 output , 其他參數其他參數 with recompile | encryption | recompile , encryption asbegin sql語句系列語句系列end5.2.2 存儲過程創(chuàng)建和執(zhí)行存儲過程創(chuàng)建和執(zhí)行695.2.2 存儲過程創(chuàng)建和執(zhí)行存儲過程創(chuàng)建和執(zhí)行說明說明;num：可選的整數。用來對同名若干過程編：可選的整數。用來對同名若干過程編號，如號，如 proc_s;1, proc_s;2, proc_s;3pr

38、oc_s;1, proc_s;2, proc_s;3等。等。采用相同名字加編號區(qū)別若干過程的采用相同名字加編號區(qū)別若干過程的優(yōu)點優(yōu)點是刪是刪除時方便，如，除時方便，如，drop procedure proc_sdrop procedure proc_s一條命一條命令把令把3個過程都刪了。個過程都刪了。5.2.2 存儲過程創(chuàng)建和執(zhí)行存儲過程創(chuàng)建和執(zhí)行705.2.2 存儲過程創(chuàng)建和執(zhí)行存儲過程創(chuàng)建和執(zhí)行default：參數的默認值。如果定義了默認：參數的默認值。如果定義了默認值，不必指定該參數的值就可調用。默認值必值，不必指定該參數的值就可調用。默認值必須是常量或須是常量或null。如果過程將對該

39、參數使用如果過程將對該參數使用like關鍵字，那么默認值中可以包含通配符關鍵字，那么默認值中可以包含通配符(%、_、)。output：表示該參數是返回參數。參數可：表示該參數是返回參數。參數可將信息返回給調用過程。將信息返回給調用過程。text、ntext、image參數可用作參數可用作output參數。參數。5.2.2 存儲過程創(chuàng)建和執(zhí)行存儲過程創(chuàng)建和執(zhí)行715.2.2 存儲過程創(chuàng)建和執(zhí)行存儲過程創(chuàng)建和執(zhí)行 recompile | encryption | recompile , encryption recompile：表明該過程在運行時重新編譯。：表明該過程在運行時重新編譯。 encry

40、ption ：表示系統(tǒng)加密：表示系統(tǒng)加密syscomments表中表中包含包含create procedure語句文本的條目。語句文本的條目。 加密后即使是過程創(chuàng)建者加密后即使是過程創(chuàng)建者 本人也無法查看過本人也無法查看過程定義文本。程定義文本。5.2.2 存儲過程創(chuàng)建和執(zhí)行存儲過程創(chuàng)建和執(zhí)行725.2.2 存儲過程創(chuàng)建和執(zhí)行存儲過程創(chuàng)建和執(zhí)行例如例如：use 教學數據庫教學數據庫gocreate proc c_sasselect * from 學生學生 where 所屬系所屬系 like 計算機計算機%go 5.2.2 存儲過程創(chuàng)建和執(zhí)行存儲過程創(chuàng)建和執(zhí)行735.2.2 存儲過

41、程創(chuàng)建和執(zhí)行存儲過程創(chuàng)建和執(zhí)行2.2.執(zhí)行存儲過程語句執(zhí)行存儲過程語句executereturn_status= 存儲過程名存儲過程名;number |存儲過程名變量存儲過程名變量參數參數1=值值1, 參數參數n=值值n |變量變量 output|default with recompile 5.2.2 存儲過程創(chuàng)建和執(zhí)行存儲過程創(chuàng)建和執(zhí)行745.2.2 存儲過程創(chuàng)建和執(zhí)行存儲過程創(chuàng)建和執(zhí)行參數說明參數說明: : return_status, 為一整型變量為一整型變量, 用于保存用于保存存儲過程的返回狀態(tài)值。存儲過程的返回狀態(tài)值。存儲過程名變量存儲過程名變量：表示存儲過程名稱的變量。：表示存儲

42、過程名稱的變量。5.2.2 存儲過程創(chuàng)建和執(zhí)行存儲過程創(chuàng)建和執(zhí)行 變量變量 output default:用來保存過程中的用來保存過程中的 output參數的返回值。參數的返回值。default提供參提供參數的默認值數的默認值755.2.2 存儲過程創(chuàng)建和執(zhí)行存儲過程創(chuàng)建和執(zhí)行3. 創(chuàng)建無參數的存儲過程創(chuàng)建無參數的存儲過程例例10 創(chuàng)建一個存儲過程。完成顯示所有學生創(chuàng)建一個存儲過程。完成顯示所有學生的平均成績。的平均成績。create proc sc_proc;1 as select 學號學號, avg(成績成績) as 平均成績平均成績 from 選課選課 group by 學號學號5.2.

43、2 存儲過程創(chuàng)建和執(zhí)行存儲過程創(chuàng)建和執(zhí)行765.2.2 存儲過程創(chuàng)建和執(zhí)行存儲過程創(chuàng)建和執(zhí)行4. 創(chuàng)建帶有輸入參數的存儲過程創(chuàng)建帶有輸入參數的存儲過程例例11 創(chuàng)建一個存儲過程。顯示指定學號的學創(chuàng)建一個存儲過程。顯示指定學號的學生的平均成績。學號作為參數生的平均成績。學號作為參數. create proc sc_proc;2sno char(6) as select 學號學號, avg(成績成績) as 平均成績平均成績 from 選課選課where 學號學號 like sno group by 學號學號5.2.2 存儲過程創(chuàng)建和執(zhí)行存儲過程創(chuàng)建和執(zhí)行775.2.2 存儲過程創(chuàng)建和執(zhí)行存儲過程

44、創(chuàng)建和執(zhí)行 調用調用存儲過程存儲過程sc_proc;2方法方法1:declare temp1 char(6) set temp1=030101 exec 教學數據庫教學數據庫.dbo.sc_proc;2 temp1方法方法2: exec sc_proc;2 0301015.2.2 存儲過程創(chuàng)建和執(zhí)行存儲過程創(chuàng)建和執(zhí)行785.2.2 存儲過程創(chuàng)建和執(zhí)行存儲過程創(chuàng)建和執(zhí)行5. 創(chuàng)建帶有輸出參數的存儲過程創(chuàng)建帶有輸出參數的存儲過程例例12 創(chuàng)建存儲過程。完成求指定學號的學生的創(chuàng)建存儲過程。完成求指定學號的學生的平均成績，并將該成績保存在變量平均成績，并將該成績保存在變量avg中。中。create p

45、roc sc_proc;3sno char(6), avg int output asselect avg= avg(成績成績) from 選課選課where 學號學號= sno group by 學號學號5.2.2 存儲過程創(chuàng)建和執(zhí)行存儲過程創(chuàng)建和執(zhí)行795.2.2 存儲過程創(chuàng)建和執(zhí)行存儲過程創(chuàng)建和執(zhí)行 執(zhí)行執(zhí)行存儲過程存儲過程sc_proc;3 :declare temp char(6), avg_out intset temp=030101exec sc_proc;3 temp,avg_out outputprint 030101的平均成績?yōu)榈钠骄煽優(yōu)?+ cast(avg_out a

46、s char(3) 過程中輸出過程中輸出參數參數_avg外部變量外部變量avg_out5.2.2 存儲過程創(chuàng)建和執(zhí)行存儲過程創(chuàng)建和執(zhí)行805.2.2 存儲過程創(chuàng)建和執(zhí)行存儲過程創(chuàng)建和執(zhí)行6.返回返回存儲過程的執(zhí)行狀態(tài)存儲過程的執(zhí)行狀態(tài)每個存儲過程的執(zhí)行每個存儲過程的執(zhí)行,都將自動返回一個整數狀都將自動返回一個整數狀態(tài)值態(tài)值,用于告訴用戶調用存儲過程的執(zhí)行情況。用于告訴用戶調用存儲過程的執(zhí)行情況。調用程序可以根據返回值作相應的處理。調用程序可以根據返回值作相應的處理。用戶可以用大于用戶可以用大于0或或-1-99的來定義自己的返的來定義自己的返回狀態(tài)值，以表示不同的執(zhí)行結果?；貭顟B(tài)值，以表示不同的

47、執(zhí)行結果。一般而言，一般而言，0表示存儲過程執(zhí)行成功，表示存儲過程執(zhí)行成功，-1-99之間的數表示過程執(zhí)行失敗。之間的數表示過程執(zhí)行失敗。5.2.2 存儲過程創(chuàng)建和執(zhí)行存儲過程創(chuàng)建和執(zhí)行815.2.2 存儲過程創(chuàng)建和執(zhí)行存儲過程創(chuàng)建和執(zhí)行例例1313 檢查給定學號的學生有無不及格的記錄，檢查給定學號的學生有無不及格的記錄，有有則返回則返回5,5,無無則返回則返回0 0并輸出該學生的并輸出該學生的學習記錄。如果沒有提供參數學號，則返回學習記錄。如果沒有提供參數學號，則返回15.15.create proc sc_proc;4sno char(6)=nullasif sno is null ret

48、urn 15 /*未給出該生學號未給出該生學號*/ 創(chuàng)建返回執(zhí)行狀態(tài)的存儲過程創(chuàng)建返回執(zhí)行狀態(tài)的存儲過程5.2.2 存儲過程創(chuàng)建和執(zhí)行存儲過程創(chuàng)建和執(zhí)行825.2.2 存儲過程創(chuàng)建和執(zhí)行存儲過程創(chuàng)建和執(zhí)行else if exists(select * from 選課選課 where 學號學號= sno and 成績成績60) return 5 /*該生有不及格成績時該生有不及格成績時*/else begin select * from 選課選課 where 學號學號=sno return 0 /*該生無不及格，返回其選課信息該生無不及格，返回其選課信息 */ endelse if exists

49、(select * from 選課選課 where 學號學號= sno and 成績成績60) return 5 /*該生有不及格成績時該生有不及格成績時*/else begin select * from 選課選課 where 學號學號=sno return 0 /*該生無不及格，返回其選課信息該生無不及格，返回其選課信息 */ endelse if exists(select * from 選課選課 where 學號學號= sno and 成績成績60) return 5 /*該生有不及格成績時該生有不及格成績時*/else begin select * from 選課選課 where 學

50、號學號=sno return 0 /*該生無不及格，返回其選課信息該生無不及格，返回其選課信息 */ end5.2.2 存儲過程創(chuàng)建和執(zhí)行存儲過程創(chuàng)建和執(zhí)行835.2.2 存儲過程創(chuàng)建和執(zhí)行存儲過程創(chuàng)建和執(zhí)行執(zhí)行：執(zhí)行： （學號取學號取030101或或060111，各執(zhí)行，各執(zhí)行一次；學號未給出，再執(zhí)行一次）一次；學號未給出，再執(zhí)行一次）declare return_status intexec return_status=sc_proc;4 030101if return_status=15 print 缺少輸入參數缺少輸入參數-學號學號else if return_status=5 pri

51、nt 該生有不及格成績記錄該生有不及格成績記錄! else print 該生無不及格成績該生無不及格成績! 5.2.2 存儲過程創(chuàng)建和執(zhí)行存儲過程創(chuàng)建和執(zhí)行845.2.3 存儲過程存儲過程的處理的處理編譯后的計劃放在編譯后的計劃放在過程高速緩存中過程高速緩存中創(chuàng)建創(chuàng)建語法分析語法分析優(yōu)化優(yōu)化編譯編譯執(zhí)行執(zhí)行(first time 或或recompile)進入進入sysobjects和和syscomments表表存儲過程的處理存儲過程的處理5.2.3 存儲過程的處理存儲過程的處理855.2.3 存儲過程存儲過程的處理的處理 存儲過程的初次處理包括存儲過程的初次處理包括：創(chuàng)建存儲過程和創(chuàng)建存儲過程

52、和預編譯。預編譯。1.創(chuàng)建存儲過程：創(chuàng)建存儲過程：存儲過程被創(chuàng)建時，查詢處存儲過程被創(chuàng)建時，查詢處理器對該過程進行語法分析，檢查是否合乎理器對該過程進行語法分析，檢查是否合乎語法規(guī)范，通過語法檢查后將該存儲過程的語法規(guī)范，通過語法檢查后將該存儲過程的源代碼存放到當前數據庫的系統(tǒng)表源代碼存放到當前數據庫的系統(tǒng)表syscomments中，同時在中，同時在sysobjects中登中登記該存儲過程的名字。記該存儲過程的名字。注：絕不要直接刪除系統(tǒng)表注：絕不要直接刪除系統(tǒng)表syscommentssyscomments中的項。中的項。5.2.3 存儲過程的處理存儲過程的處理865.2.3 存儲過程存儲過程

53、的處理的處理2.預編譯預編譯 (或再編譯或再編譯) 存儲過程存儲過程第第1次執(zhí)行時，次執(zhí)行時，sql server查詢優(yōu)查詢優(yōu)化器分析存儲過程中的化器分析存儲過程中的sql語句，并創(chuàng)建含語句，并創(chuàng)建含有快速訪問數據的計劃。有快速訪問數據的計劃。 在查詢優(yōu)化器把編譯的計劃放在存儲過程高在查詢優(yōu)化器把編譯的計劃放在存儲過程高速緩存中后，系統(tǒng)執(zhí)行存儲過程。速緩存中后，系統(tǒng)執(zhí)行存儲過程。5.2.3 存儲過程的處理存儲過程的處理875.2.3 存儲過程存儲過程的處理的處理重編譯重編譯 在某些應用中在某些應用中, , 數據庫的邏輯結構可能發(fā)生改數據庫的邏輯結構可能發(fā)生改變（如屬性列被修改）或新增了索引等，

54、這樣存變（如屬性列被修改）或新增了索引等，這樣存儲過程的查詢計劃就可能不是最優(yōu)的，有必要重儲過程的查詢計劃就可能不是最優(yōu)的，有必要重新編譯，以便獲得新的優(yōu)化的查詢計劃。新編譯，以便獲得新的優(yōu)化的查詢計劃。重編譯有三種方法。重編譯有三種方法。(1)在創(chuàng)建存儲過程時采用在創(chuàng)建存儲過程時采用recompile create proc with recompile這樣，每次執(zhí)行時，系統(tǒng)都會重新編譯優(yōu)化。這樣，每次執(zhí)行時，系統(tǒng)都會重新編譯優(yōu)化。5.2.3 存儲過程的處理存儲過程的處理885.2.3 存儲過程存儲過程的處理的處理(2)在執(zhí)行存儲過程時采用在執(zhí)行存儲過程時采用recompile exec 存

55、儲過程名存儲過程名 參數表參數表 with recompile 在執(zhí)行存儲過程時，重新編譯優(yōu)化。在執(zhí)行存儲過程時，重新編譯優(yōu)化。(3)調用系統(tǒng)存儲過程調用系統(tǒng)存儲過程sp_recompile 命令命令: sp_recompile 表名表名運行該命令的結果是指定表的存儲過程在下一運行該命令的結果是指定表的存儲過程在下一次運行時將被重新編譯次運行時將被重新編譯. .5.2.3 存儲過程的處理存儲過程的處理895.2.4 存儲過程存儲過程的查看、修改和刪除的查看、修改和刪除1. 查看存儲過程查看存儲過程方法方法1：可以通過系統(tǒng)存儲：可以通過系統(tǒng)存儲過程來查看用戶定義的存儲過程來查看用戶定義的存儲過程

56、。過程。 sp_helptext 存儲過程名存儲過程名5.2.4 查看、修改和刪除存儲過程查看、修改和刪除存儲過程905.2.4 存儲過程存儲過程的查看、修改和刪除的查看、修改和刪除方法方法2 ：查詢分析器中，找到所要查看的存儲查詢分析器中，找到所要查看的存儲過程，單擊鼠標右鍵，打開快捷菜單，選擇過程，單擊鼠標右鍵，打開快捷菜單，選擇“編輯編輯”菜單命令。菜單命令。5.2.4 查看、修改和刪除存儲過程查看、修改和刪除存儲過程915.2.4 存儲過程存儲過程的查看、修改和刪除的查看、修改和刪除方法方法3：如果想查看存儲過程的一般信息，可：如果想查看存儲過程的一般信息，可使用：使用： sp_hel

57、p 存儲過程名存儲過程名5.2.4查看、修改和刪除存儲過程查看、修改和刪除存儲過程925.2.4 存儲過程存儲過程的查看、修改和刪除的查看、修改和刪除2. 修改存儲過程（修改存儲過程（建議可視化操作完成建議可視化操作完成）在查詢分析器中使用下列命令：在查詢分析器中使用下列命令： alter proc owner.存儲過程名存儲過程名 可視化操作可視化操作：選定存儲過程，在其上打開快：選定存儲過程，在其上打開快捷菜單，選擇捷菜單，選擇“編輯編輯” 菜單命令，打開編輯菜單命令，打開編輯窗口。修改完成后，再重新執(zhí)行一次即可。窗口。修改完成后，再重新執(zhí)行一次即可?；蚧?.2.4查看、修改和刪除存儲過程

58、查看、修改和刪除存儲過程93* 5.2.5 系統(tǒng)系統(tǒng)存儲過程存儲過程常用的系統(tǒng)存儲過程如下表常用的系統(tǒng)存儲過程如下表存儲過程存儲過程功能功能sp_help 對象名對象名提供指定數據庫對象的信息提供指定數據庫對象的信息sp_helptext 對象名對象名查看指定數據庫對象查看指定數據庫對象(視圖視圖存存儲過程儲過程觸發(fā)器等觸發(fā)器等)的文本信息的文本信息sp_helpdb 數據庫名數據庫名查看數據庫信息查看數據庫信息sp_recompile 表名表名在存儲過程在存儲過程(與表相關的與表相關的)下次下次執(zhí)行時，重新編譯。執(zhí)行時，重新編譯。5.2.5 系統(tǒng)存儲過程系統(tǒng)存儲過程94*5.2.5 系統(tǒng)系統(tǒng)

59、存儲過程存儲過程存儲過程存儲過程功能功能sp_depends 對象名對象名查看指定數據庫對象查看指定數據庫對象(視圖視圖存存儲過程儲過程觸發(fā)器等觸發(fā)器等)所引用的對所引用的對象信息象信息sp_bindrule 規(guī)則名規(guī)則名, 對象名對象名將規(guī)則綁定到指定對象將規(guī)則綁定到指定對象sp_unbindrule 對象名對象名將規(guī)則從指定對象上松綁將規(guī)則從指定對象上松綁5.2.5 系統(tǒng)存儲過程系統(tǒng)存儲過程95*5.2.5 系統(tǒng)系統(tǒng)存儲過程存儲過程例例：查看與：查看與選課選課表相關的所有對象。表相關的所有對象。 sp_depends 選課選課表值函數標量值函數5.2.5 系統(tǒng)存儲過程系統(tǒng)存儲過程965.3

60、 觸發(fā)器觸發(fā)器sql server提供了兩種主要機制來強制實施提供了兩種主要機制來強制實施數據庫的完整性：約束和觸發(fā)器。數據庫的完整性：約束和觸發(fā)器。觸發(fā)器可以實現比觸發(fā)器可以實現比check約束更為復雜的完整約束更為復雜的完整性約束。性約束。5.3 觸發(fā)器觸發(fā)器5.3 觸發(fā)器觸發(fā)器97觸發(fā)器觸發(fā)器：是一種特殊的存儲過程，在用戶試圖：是一種特殊的存儲過程，在用戶試圖更新觸發(fā)器保護的數據時自動執(zhí)行。更新觸發(fā)器保護的數據時自動執(zhí)行。觸發(fā)器觸發(fā)器:(1) 總是與一個表相連?？偸桥c一個表相連。觸發(fā)器是在單個表或視圖上定義，這個表稱為觸發(fā)器是在單個表或視圖上定義，這個表稱為觸發(fā)器表。每個表上可以建若干觸