SqlServer2008CH7創(chuàng)建和管理存儲(chǔ)過程

1、精選ppt1創(chuàng)建和管理存儲(chǔ)過程創(chuàng)建和管理存儲(chǔ)過程精選ppt2目錄目錄n 批處理批處理 n 存儲(chǔ)過程存儲(chǔ)過程n創(chuàng)建存儲(chǔ)過程創(chuàng)建存儲(chǔ)過程n執(zhí)行存儲(chǔ)過程執(zhí)行存儲(chǔ)過程n 修改與刪除存儲(chǔ)過程修改與刪除存儲(chǔ)過程n 自定義函數(shù)自定義函數(shù)n 創(chuàng)建自定義函數(shù)創(chuàng)建自定義函數(shù)n 執(zhí)行自定義函數(shù)執(zhí)行自定義函數(shù)n 修改與刪除自定義函數(shù)修改與刪除自定義函數(shù)精選ppt3NO:1 NO:1 上節(jié)回顧精選ppt4 上節(jié)回顧上節(jié)回顧n 批量批量:n 變量變量: 局部變量局部變量 全局變量全局變量n 打印消息打印消息:n 控制流語言控制流語言: if else case while精選ppt5NO:1 NO:1 存儲(chǔ)過程精選ppt

2、6存儲(chǔ)過程存儲(chǔ)過程n 存儲(chǔ)過程是服務(wù)器上一組預(yù)先定義并編譯好的存儲(chǔ)過程是服務(wù)器上一組預(yù)先定義并編譯好的SQL語句語句n 它可以接受參數(shù)、返回狀態(tài)值和參數(shù)值，并且可它可以接受參數(shù)、返回狀態(tài)值和參數(shù)值，并且可以嵌套調(diào)用以嵌套調(diào)用n 使用存儲(chǔ)過程可以改變使用存儲(chǔ)過程可以改變SQL語句的運(yùn)行性能，提高語句的運(yùn)行性能，提高其執(zhí)行效率其執(zhí)行效率n 存儲(chǔ)過程還可以用作一種安全機(jī)制，使用戶通過存儲(chǔ)過程還可以用作一種安全機(jī)制，使用戶通過它訪問未經(jīng)授權(quán)的表或視圖它訪問未經(jīng)授權(quán)的表或視圖n 類似于高級(jí)語言中的子程序類似于高級(jí)語言中的子程序精選ppt7存儲(chǔ)過程存儲(chǔ)過程 -存儲(chǔ)過程分類存儲(chǔ)過程分類n 系統(tǒng)存儲(chǔ)過程（系統(tǒng)

3、存儲(chǔ)過程（sp_）：存放在）：存放在master數(shù)據(jù)庫中，提供存數(shù)據(jù)庫中，提供存儲(chǔ)系統(tǒng)表信息的快捷方式，可在任何數(shù)據(jù)庫執(zhí)行儲(chǔ)系統(tǒng)表信息的快捷方式，可在任何數(shù)據(jù)庫執(zhí)行n 局部（本地）存儲(chǔ)過程：在各個(gè)用戶數(shù)據(jù)庫中創(chuàng)建局部（本地）存儲(chǔ)過程：在各個(gè)用戶數(shù)據(jù)庫中創(chuàng)建n 臨時(shí)存儲(chǔ)過程：可以是局部的（臨時(shí)存儲(chǔ)過程：可以是局部的（#），也可以是全局的），也可以是全局的（#），局部臨時(shí)存儲(chǔ)過程在單個(gè)用戶任務(wù)中有效，全局臨），局部臨時(shí)存儲(chǔ)過程在單個(gè)用戶任務(wù)中有效，全局臨時(shí)存儲(chǔ)過程在所有用戶任務(wù)中有效時(shí)存儲(chǔ)過程在所有用戶任務(wù)中有效n 遠(yuǎn)程存儲(chǔ)過程：從連接到不同服務(wù)器的遠(yuǎn)程服務(wù)器或客戶遠(yuǎn)程存儲(chǔ)過程：從連接到不同服務(wù)器

4、的遠(yuǎn)程服務(wù)器或客戶機(jī)調(diào)用的存儲(chǔ)過程，早期版本使用，現(xiàn)由分布查詢支持機(jī)調(diào)用的存儲(chǔ)過程，早期版本使用，現(xiàn)由分布查詢支持n 擴(kuò)展存儲(chǔ)過程（擴(kuò)展存儲(chǔ)過程（xp_）：在）：在SQL環(huán)境外部執(zhí)行的環(huán)境外部執(zhí)行的DLL精選ppt8存儲(chǔ)過程存儲(chǔ)過程 -優(yōu)點(diǎn)優(yōu)點(diǎn)n 共享應(yīng)用程序邏輯：用戶使用相同的存儲(chǔ)過程保共享應(yīng)用程序邏輯：用戶使用相同的存儲(chǔ)過程保證一致的數(shù)據(jù)修改證一致的數(shù)據(jù)修改n 提供安全機(jī)制：用戶可以被授予執(zhí)行存儲(chǔ)過程的提供安全機(jī)制：用戶可以被授予執(zhí)行存儲(chǔ)過程的權(quán)限，及時(shí)無權(quán)訪問存儲(chǔ)過程引用的表或視圖權(quán)限，及時(shí)無權(quán)訪問存儲(chǔ)過程引用的表或視圖n 自動(dòng)執(zhí)行：能在啟動(dòng)時(shí)自動(dòng)執(zhí)行自動(dòng)執(zhí)行：能在啟動(dòng)時(shí)自動(dòng)執(zhí)行n 提高

5、性能：第一次執(zhí)行后，執(zhí)行計(jì)劃存放在高速提高性能：第一次執(zhí)行后，執(zhí)行計(jì)劃存放在高速緩存中緩存中n 減少網(wǎng)絡(luò)流量：通過發(fā)送一條語句來執(zhí)行一個(gè)復(fù)減少網(wǎng)絡(luò)流量：通過發(fā)送一條語句來執(zhí)行一個(gè)復(fù)雜的操作，減少了在服務(wù)器和客戶機(jī)之間傳遞的請(qǐng)求雜的操作，減少了在服務(wù)器和客戶機(jī)之間傳遞的請(qǐng)求數(shù)數(shù)精選ppt9存儲(chǔ)過程存儲(chǔ)過程 -創(chuàng)建存儲(chǔ)過程創(chuàng)建存儲(chǔ)過程n 語法：語法：create procedure procedure_nameparameter data_type =default outputAsbeginsql_statement,nend說明：說明：nProcedure_name：存儲(chǔ)過程名：存儲(chǔ)過程名np

6、arameter：局部變量名：局部變量名noutput：指定局部變量是否為輸出參數(shù)：指定局部變量是否為輸出參數(shù)精選ppt10存儲(chǔ)過程存儲(chǔ)過程 -創(chuàng)建存儲(chǔ)過程的幾個(gè)注意創(chuàng)建存儲(chǔ)過程的幾個(gè)注意n create proc所創(chuàng)建的存儲(chǔ)過程可以為永久對(duì)象，所創(chuàng)建的存儲(chǔ)過程可以為永久對(duì)象，也可為臨時(shí)對(duì)象，由名字決定也可為臨時(shí)對(duì)象，由名字決定n create proc語句不可以與其他語句不可以與其他SQL語句一起使用語句一起使用在過程定義中不能使用在過程定義中不能使用create語句創(chuàng)建視圖、默認(rèn)、語句創(chuàng)建視圖、默認(rèn)、規(guī)則、觸發(fā)器、存儲(chǔ)過程。在過程中創(chuàng)建了以外的對(duì)規(guī)則、觸發(fā)器、存儲(chǔ)過程。在過程中創(chuàng)建了以外的

7、對(duì)象，之后又刪除它們，在該過程中不能再創(chuàng)建它們的象，之后又刪除它們，在該過程中不能再創(chuàng)建它們的同名對(duì)象同名對(duì)象n 在存儲(chǔ)過程嵌套調(diào)用時(shí)，被調(diào)用的存儲(chǔ)過程可以在存儲(chǔ)過程嵌套調(diào)用時(shí)，被調(diào)用的存儲(chǔ)過程可以訪問調(diào)用這所建立的除臨時(shí)表以外的所有數(shù)據(jù)庫對(duì)象訪問調(diào)用這所建立的除臨時(shí)表以外的所有數(shù)據(jù)庫對(duì)象精選ppt11存儲(chǔ)過程存儲(chǔ)過程 -創(chuàng)建存儲(chǔ)過程的幾個(gè)注意創(chuàng)建存儲(chǔ)過程的幾個(gè)注意(續(xù)續(xù))n 嵌套調(diào)用只能在嵌套調(diào)用只能在32個(gè)以內(nèi)，當(dāng)前調(diào)用級(jí)別個(gè)以內(nèi)，當(dāng)前調(diào)用級(jí)別存儲(chǔ)在存儲(chǔ)在nestlevle中中n 要?jiǎng)?chuàng)建存儲(chǔ)過程必須時(shí)系統(tǒng)管理員角色、要?jiǎng)?chuàng)建存儲(chǔ)過程必須時(shí)系統(tǒng)管理員角色、數(shù)據(jù)庫所有者角色或數(shù)據(jù)定義語言管理角色

8、之?dāng)?shù)據(jù)庫所有者角色或數(shù)據(jù)定義語言管理角色之一，或者被授予一，或者被授予create procedure許可許可n 根據(jù)可使用的內(nèi)存，存儲(chǔ)過程最大的尺寸根據(jù)可使用的內(nèi)存，存儲(chǔ)過程最大的尺寸為為128M精選ppt12存儲(chǔ)過程存儲(chǔ)過程 - ( (示例示例1)1)n 建立一個(gè)不帶參數(shù)的存儲(chǔ)過程建立一個(gè)不帶參數(shù)的存儲(chǔ)過程. create proc simpleProcDemo as select * from student_table精選ppt13存儲(chǔ)過程存儲(chǔ)過程 -執(zhí)行存儲(chǔ)過程執(zhí)行存儲(chǔ)過程n 語法：語法： execute return_status=pro_name parameter=value

9、outputreturn_status：返回狀態(tài)（即錯(cuò)誤代號(hào)），：返回狀態(tài)（即錯(cuò)誤代號(hào)），為為0時(shí)證明執(zhí)行成功，其他值為失敗時(shí)證明執(zhí)行成功，其他值為失敗精選ppt14存儲(chǔ)過程存儲(chǔ)過程 -執(zhí)行存儲(chǔ)過程執(zhí)行存儲(chǔ)過程n 示例示例: 執(zhí)行存儲(chǔ)過程有三種方式執(zhí)行存儲(chǔ)過程有三種方式. execute simpleProcDemo exec simpleProcDemo simpleProcDemo精選ppt15存儲(chǔ)過程存儲(chǔ)過程 -存儲(chǔ)過程的參數(shù)傳遞存儲(chǔ)過程的參數(shù)傳遞n execute語句中可使用兩種方法傳遞參數(shù)，語句中可使用兩種方法傳遞參數(shù)，執(zhí)行過程中可以隨意使用，當(dāng)兩種不可混用執(zhí)行過程中可以隨意使用，當(dāng)

10、兩種不可混用nparameer_value1,parameter_value2.在這種方式下，參數(shù)順序要嚴(yán)格按定義時(shí)的順在這種方式下，參數(shù)順序要嚴(yán)格按定義時(shí)的順序，默認(rèn)值可省略，但要按照順序序，默認(rèn)值可省略，但要按照順序nparameter_name1=value,.在這種方式在這種方式下，參數(shù)名與參數(shù)值唯一對(duì)應(yīng)，順序可任意排下，參數(shù)名與參數(shù)值唯一對(duì)應(yīng)，順序可任意排列列精選ppt16存儲(chǔ)過程存儲(chǔ)過程 -存儲(chǔ)過程的參數(shù)傳遞存儲(chǔ)過程的參數(shù)傳遞n 示例示例1:create proc procWithparameter(temp int)as print temp-檢驗(yàn)檢驗(yàn)execute procWi

11、thparameter 1精選ppt17存儲(chǔ)過程存儲(chǔ)過程 -存儲(chǔ)過程的參數(shù)傳遞存儲(chǔ)過程的參數(shù)傳遞n 示例示例2:create proc procWithParameter_1vInputClassNum int as begin select * from student_table where class_num=vInputClassNum end-檢驗(yàn)檢驗(yàn)execute procWithParameter_1 2精選ppt18存儲(chǔ)過程存儲(chǔ)過程 -修改和刪除存儲(chǔ)過程修改和刪除存儲(chǔ)過程n 修改存儲(chǔ)過程修改存儲(chǔ)過程n語法：語法：alter proc proc_name as sql_state

12、mentn 刪除存儲(chǔ)過程刪除存儲(chǔ)過程n語法：語法：drop proc proc_name精選ppt19存儲(chǔ)過程存儲(chǔ)過程 - 通知執(zhí)行成功通知執(zhí)行成功/* 通知過程執(zhí)行是否成功通知過程執(zhí)行是否成功 */create proc procWithReturn(temp int)as if(temp=0) begin print temp=0,執(zhí)行成功 return 0 end else begin print temp=1,執(zhí)行失敗 return 1 end 精選ppt20存儲(chǔ)過程存儲(chǔ)過程 - 通知執(zhí)行成功通知執(zhí)行成功/* 通知過程執(zhí)行是否成功通知過程執(zhí)行是否成功 */create proc pro

13、cWithReturn(temp int)as if(temp=0) begin print temp=0,執(zhí)行成功 return 0 end else begin print temp=1,執(zhí)行失敗 return 1 end 精選ppt21存儲(chǔ)過程存儲(chǔ)過程 -通知執(zhí)行成功通知執(zhí)行成功-檢驗(yàn)檢驗(yàn) declare returnNum int execute returnNum=procWithReturn 0 print returnNum 精選ppt22存儲(chǔ)過程存儲(chǔ)過程 -過程調(diào)用過程過程調(diào)用過程/* 過程調(diào)用過程 */create proc procDemo1(intClassNum int

14、)as select Name from student_table where class_num=intClassNumcreate proc procDemo2(cAddress char(5)as select Name from student_table where address=cAddress精選ppt23存儲(chǔ)過程存儲(chǔ)過程 -過程調(diào)用過程過程調(diào)用過程create proc procDemo3(intClassNum int,cAddress char(5)as exec procDemo1 intClassNum exec procDemo2 cAddress-檢驗(yàn)exec

15、procDemo3 1,B1 精選ppt24存儲(chǔ)過程存儲(chǔ)過程 -返回多個(gè)輸出值返回多個(gè)輸出值/* 返回多個(gè)輸出值 */create proc procDemo4( intID int, vcName varchar(20) output, cAddress char(2) output )as select vcName=name,cAddress=address from student_table where ID=intID return 0 精選ppt25存儲(chǔ)過程存儲(chǔ)過程 - 返回多個(gè)輸出值返回多個(gè)輸出值-多個(gè)輸出值過程調(diào)用多個(gè)輸出值過程調(diào)用create proc procDemo5(

16、intID int) as declare vcName varchar(20) declare cAddress char(2) declare invokeFlag int -調(diào)用過程是否成功標(biāo)志調(diào)用過程是否成功標(biāo)志 exec invokeFlag=procDemo4 intID,vcName output,cAddress output if(invokeFlag=0) begin print name= +vcName+ address= +cAddress end else begin print procedure: procDemo4 invoke error end精選ppt2

17、6存儲(chǔ)過程存儲(chǔ)過程 - 返回多個(gè)輸出值返回多個(gè)輸出值-檢驗(yàn)檢驗(yàn) declare tempInvokeFlag int exec tempInvokeFlag=procDemo5 1 If(tempInvokeFlag=0) print procDemo5 調(diào)用成功調(diào)用成功 else print procDemo5 調(diào)用失敗調(diào)用失敗精選ppt27存儲(chǔ)過程存儲(chǔ)過程 - 使用使用recompile /* 使用recompile */create proc tempProc with recompileas print ok精選ppt28NO:2 NO:2 自定義函數(shù)自定義函數(shù)精選ppt29自定義函數(shù)

18、n 用戶自定義函數(shù)類似于存儲(chǔ)過程，是由一用戶自定義函數(shù)類似于存儲(chǔ)過程，是由一個(gè)或多個(gè)個(gè)或多個(gè)T-SQL語句構(gòu)成的子程序。它把這些語句構(gòu)成的子程序。它把這些語句封裝起來，構(gòu)成一個(gè)可以重復(fù)使用的程序語句封裝起來，構(gòu)成一個(gè)可以重復(fù)使用的程序單元，用戶自定義函數(shù)與存儲(chǔ)過程的不同之處單元，用戶自定義函數(shù)與存儲(chǔ)過程的不同之處在于函數(shù)必須返回?cái)?shù)值在于函數(shù)必須返回?cái)?shù)值n 允許創(chuàng)建用戶定義函數(shù)是從允許創(chuàng)建用戶定義函數(shù)是從SQL 2000開始開始的新增功能，在之前的版本中用戶只能調(diào)用的新增功能，在之前的版本中用戶只能調(diào)用SQL內(nèi)置的函數(shù)內(nèi)置的函數(shù)精選ppt30自定義函數(shù) -用戶自定義函數(shù)的種類用戶自定義函數(shù)的種類

19、n 標(biāo)量函數(shù)：標(biāo)量函數(shù)只能返回單個(gè)數(shù)值，標(biāo)量函數(shù)：標(biāo)量函數(shù)只能返回單個(gè)數(shù)值，其返回值可為其返回值可為timestame、text、next、image、table和和cursor之外的所有系統(tǒng)數(shù)據(jù)之外的所有系統(tǒng)數(shù)據(jù)類型，但不能為用戶自定義數(shù)據(jù)類型類型，但不能為用戶自定義數(shù)據(jù)類型n 表值函數(shù)：表值函數(shù)的返回值為表值函數(shù)：表值函數(shù)的返回值為table數(shù)據(jù)數(shù)據(jù)類型。分為兩類：內(nèi)嵌表值函數(shù)和多語句表值類型。分為兩類：內(nèi)嵌表值函數(shù)和多語句表值函數(shù)函數(shù)精選ppt31自定義函數(shù) -創(chuàng)建用戶自定義函數(shù)創(chuàng)建用戶自定義函數(shù)語法：語法： create functionowner, function_name(par

20、ameter type=default.) returns type with option. as begin function_body return parameter end精選ppt32自定義函數(shù) - 示例示例 - 建立函數(shù)建立函數(shù),返回指定學(xué)號(hào)的名稱返回指定學(xué)號(hào)的名稱,建立此函數(shù)建立此函數(shù)create function functionDemo2(id int)returns varchar(20)as begin declare vName varchar(20)select vName=Namefrom student_tablewhere id=id return vName end精選ppt33自定義函數(shù) -調(diào)用自定義函數(shù)調(diào)用自定義函數(shù)-驗(yàn)證驗(yàn)證select id,student name=dbo.functionDemo2(id)from student_table精選ppt34自定義函數(shù) -刪除和修改自定義函數(shù)刪除和修改自定義函數(shù)修改修改nalter function dbo.funtion_name 新的定義新的定義n和定義語句一樣和定義語句一樣n保留原有的權(quán)限保留原有的權(quán)限刪除刪除ndrop