存儲(chǔ)過(guò)程、觸發(fā)器和游標(biāo)課件

1、存儲(chǔ)過(guò)程、觸發(fā)器和游標(biāo)數(shù)據(jù)庫(kù)原理與應(yīng)用教程SQL Server第第1111章存儲(chǔ)過(guò)程、觸發(fā)器和游標(biāo)章存儲(chǔ)過(guò)程、觸發(fā)器和游標(biāo)存儲(chǔ)過(guò)程、觸發(fā)器和游標(biāo)引言引言 在在SQL Server 2005應(yīng)用操作中，存儲(chǔ)過(guò)程、觸發(fā)器和游應(yīng)用操作中，存儲(chǔ)過(guò)程、觸發(fā)器和游標(biāo)都扮演著相當(dāng)重要的角色。標(biāo)都扮演著相當(dāng)重要的角色。 存儲(chǔ)過(guò)程是存儲(chǔ)過(guò)程是SQL語(yǔ)句和可選流程控制語(yǔ)句的預(yù)編譯集合，語(yǔ)句和可選流程控制語(yǔ)句的預(yù)編譯集合，它以一個(gè)名稱存儲(chǔ)并作為一個(gè)單元處理，能夠提高系統(tǒng)的它以一個(gè)名稱存儲(chǔ)并作為一個(gè)單元處理，能夠提高系統(tǒng)的應(yīng)用效率和執(zhí)行速度。應(yīng)用效率和執(zhí)行速度。 觸發(fā)器是一種特殊類(lèi)型的存儲(chǔ)過(guò)程。當(dāng)有操作影響到觸發(fā)觸發(fā)

2、器是一種特殊類(lèi)型的存儲(chǔ)過(guò)程。當(dāng)有操作影響到觸發(fā)器保護(hù)的數(shù)據(jù)時(shí)，觸發(fā)器就會(huì)自動(dòng)觸發(fā)執(zhí)行。它可以擴(kuò)展器保護(hù)的數(shù)據(jù)時(shí)，觸發(fā)器就會(huì)自動(dòng)觸發(fā)執(zhí)行。它可以擴(kuò)展SQL Server完整性約束默認(rèn)值對(duì)象和規(guī)則的完整性檢查邏完整性約束默認(rèn)值對(duì)象和規(guī)則的完整性檢查邏輯，實(shí)施更為復(fù)雜的數(shù)據(jù)完整性約束。輯，實(shí)施更為復(fù)雜的數(shù)據(jù)完整性約束。 游標(biāo)提供了一種對(duì)從表中檢索出的數(shù)據(jù)進(jìn)行操作的靈活手游標(biāo)提供了一種對(duì)從表中檢索出的數(shù)據(jù)進(jìn)行操作的靈活手段，就本質(zhì)而言，游標(biāo)實(shí)際上是一種能從包括多條數(shù)據(jù)記段，就本質(zhì)而言，游標(biāo)實(shí)際上是一種能從包括多條數(shù)據(jù)記錄的結(jié)果集中每次提取一條記錄的機(jī)制。錄的結(jié)果集中每次提取一條記錄的機(jī)制。存儲(chǔ)過(guò)程、觸

3、發(fā)器和游標(biāo)內(nèi)容內(nèi)容11.1 存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程11.2 觸發(fā)器觸發(fā)器11.3 游標(biāo)游標(biāo)存儲(chǔ)過(guò)程、觸發(fā)器和游標(biāo)11.1 存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程1. 概述概述2. 存儲(chǔ)過(guò)程的類(lèi)別存儲(chǔ)過(guò)程的類(lèi)別3. 創(chuàng)建和執(zhí)行存儲(chǔ)過(guò)程創(chuàng)建和執(zhí)行存儲(chǔ)過(guò)程4. 查看、修改和刪除存儲(chǔ)過(guò)程查看、修改和刪除存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程、觸發(fā)器和游標(biāo)客戶客戶/服務(wù)器模型服務(wù)器模型服務(wù)器服務(wù)器客戶客戶A客戶客戶B 客戶客戶A需要查詢部門(mén)需要查詢部門(mén)1的銷(xiāo)售額的銷(xiāo)售額SELECT SUM(總金額總金額)FROM 訂單信息訂單信息 A INNER JOIN 銷(xiāo)售人員銷(xiāo)售人員 BON A.銷(xiāo)售工號(hào)銷(xiāo)售工號(hào)= B. 工號(hào)工號(hào)WHERE 部門(mén)號(hào)部門(mén)號(hào)=1有大

4、量數(shù)據(jù)在服務(wù)器和客戶端之間流動(dòng)！有大量數(shù)據(jù)在服務(wù)器和客戶端之間流動(dòng)！1. 概述概述11.1存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程、觸發(fā)器和游標(biāo)1.概述概述-什么是存儲(chǔ)過(guò)程什么是存儲(chǔ)過(guò)程 存儲(chǔ)過(guò)程的概念存儲(chǔ)過(guò)程的概念一組編譯在單個(gè)執(zhí)行計(jì)劃中的一組編譯在單個(gè)執(zhí)行計(jì)劃中的T-SQL語(yǔ)句，它將一語(yǔ)句，它將一些固定的操作集中起來(lái)交給些固定的操作集中起來(lái)交給SQL Server數(shù)據(jù)庫(kù)服數(shù)據(jù)庫(kù)服務(wù)器完成，以完成某個(gè)任務(wù)。（務(wù)器完成，以完成某個(gè)任務(wù)。（管理任務(wù)或應(yīng)用復(fù)管理任務(wù)或應(yīng)用復(fù)雜的業(yè)務(wù)規(guī)則）雜的業(yè)務(wù)規(guī)則）。 是存儲(chǔ)在服務(wù)器上的是存儲(chǔ)在服務(wù)器上的 T-SQL 語(yǔ)句的命名集合語(yǔ)句的命名集合 是封裝重復(fù)性任務(wù)的方法是封裝重

5、復(fù)性任務(wù)的方法 支持用戶聲明變量、條件執(zhí)行以及其他強(qiáng)有力的支持用戶聲明變量、條件執(zhí)行以及其他強(qiáng)有力的編程特性編程特性存儲(chǔ)過(guò)程、觸發(fā)器和游標(biāo) 存儲(chǔ)過(guò)程（存儲(chǔ)過(guò)程（procedure）類(lèi)似于）類(lèi)似于C語(yǔ)言中的函數(shù)語(yǔ)言中的函數(shù) 用來(lái)執(zhí)行管理任務(wù)或應(yīng)用復(fù)雜的業(yè)務(wù)規(guī)則用來(lái)執(zhí)行管理任務(wù)或應(yīng)用復(fù)雜的業(yè)務(wù)規(guī)則 存儲(chǔ)過(guò)程可以帶參數(shù)，也可以返回結(jié)果存儲(chǔ)過(guò)程可以帶參數(shù)，也可以返回結(jié)果int sum(int a,int b) int s; s =a+b; return s ;存儲(chǔ)過(guò)程相存儲(chǔ)過(guò)程相當(dāng)于當(dāng)于C語(yǔ)言語(yǔ)言中的函數(shù)中的函數(shù)1.概述概述-什么是存儲(chǔ)過(guò)程什么是存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程、觸發(fā)器和游標(biāo) SQL Server 中

6、的存儲(chǔ)過(guò)程可以中的存儲(chǔ)過(guò)程可以包含執(zhí)行數(shù)據(jù)庫(kù)操作（包括調(diào)用其他過(guò)程）的包含執(zhí)行數(shù)據(jù)庫(kù)操作（包括調(diào)用其他過(guò)程）的編程語(yǔ)句編程語(yǔ)句接受輸入?yún)?shù)接受輸入?yún)?shù)向調(diào)用過(guò)程或批處理返回狀態(tài)值，以表明成功向調(diào)用過(guò)程或批處理返回狀態(tài)值，以表明成功或失敗（以及失敗原因）或失?。ㄒ约笆≡颍┮暂敵鰠?shù)的形式將多個(gè)值返回至調(diào)用過(guò)程或以輸出參數(shù)的形式將多個(gè)值返回至調(diào)用過(guò)程或批處理批處理1.概述概述-什么是存儲(chǔ)過(guò)程什么是存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程、觸發(fā)器和游標(biāo)-單個(gè)單個(gè) SELECT 語(yǔ)句語(yǔ)句SELECT 語(yǔ)句塊語(yǔ)句塊SELECT語(yǔ)句與邏輯語(yǔ)句與邏輯控制語(yǔ)句控制語(yǔ)句可以包含可以包含q 存儲(chǔ)過(guò)程可以包含數(shù)據(jù)操縱語(yǔ)句、變量、邏輯存

7、儲(chǔ)過(guò)程可以包含數(shù)據(jù)操縱語(yǔ)句、變量、邏輯 控制語(yǔ)句控制語(yǔ)句等等1.概述概述-什么是存儲(chǔ)過(guò)程什么是存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程、觸發(fā)器和游標(biāo)安全機(jī)制安全機(jī)制：只給用戶訪問(wèn)存儲(chǔ)過(guò)程的權(quán)限，而不授予用戶：只給用戶訪問(wèn)存儲(chǔ)過(guò)程的權(quán)限，而不授予用戶訪問(wèn)表和視圖的權(quán)限。訪問(wèn)表和視圖的權(quán)限。改良了執(zhí)行性能改良了執(zhí)行性能：只在第一次執(zhí)行時(shí)進(jìn)行編譯，以后執(zhí)行：只在第一次執(zhí)行時(shí)進(jìn)行編譯，以后執(zhí)行無(wú)需重新編譯，而一般無(wú)需重新編譯，而一般SQLSQL語(yǔ)句每執(zhí)行一次就編譯一次。語(yǔ)句每執(zhí)行一次就編譯一次。減少網(wǎng)絡(luò)流量減少網(wǎng)絡(luò)流量：存儲(chǔ)過(guò)程存在于服務(wù)器上，調(diào)用時(shí)，只需：存儲(chǔ)過(guò)程存在于服務(wù)器上，調(diào)用時(shí)，只需傳遞執(zhí)行存儲(chǔ)過(guò)程的執(zhí)行命令和返回

8、結(jié)果。傳遞執(zhí)行存儲(chǔ)過(guò)程的執(zhí)行命令和返回結(jié)果。模塊化的程序設(shè)計(jì)模塊化的程序設(shè)計(jì)：增強(qiáng)了代碼的可重用性，提高了開(kāi)發(fā)：增強(qiáng)了代碼的可重用性，提高了開(kāi)發(fā)效率。效率。1. 概述概述-存儲(chǔ)過(guò)程優(yōu)點(diǎn)存儲(chǔ)過(guò)程優(yōu)點(diǎn)存儲(chǔ)過(guò)程、觸發(fā)器和游標(biāo)2.存儲(chǔ)過(guò)程的類(lèi)型存儲(chǔ)過(guò)程的類(lèi)型 用戶定義的存儲(chǔ)過(guò)程用戶定義的存儲(chǔ)過(guò)程：用戶定義的存儲(chǔ)過(guò)程是：用戶定義的存儲(chǔ)過(guò)程是用戶根據(jù)需要，為完成某一特定功能，在自己用戶根據(jù)需要，為完成某一特定功能，在自己的普通數(shù)據(jù)庫(kù)中創(chuàng)建的存儲(chǔ)過(guò)程。的普通數(shù)據(jù)庫(kù)中創(chuàng)建的存儲(chǔ)過(guò)程。 系統(tǒng)存儲(chǔ)過(guò)程系統(tǒng)存儲(chǔ)過(guò)程：系統(tǒng)存儲(chǔ)過(guò)程以：系統(tǒng)存儲(chǔ)過(guò)程以sp_為前綴，為前綴，主要用來(lái)從系統(tǒng)表中獲取信息，為系統(tǒng)管理員主要用來(lái)

9、從系統(tǒng)表中獲取信息，為系統(tǒng)管理員管理管理SQL Server提供幫助，為用戶查看數(shù)據(jù)庫(kù)提供幫助，為用戶查看數(shù)據(jù)庫(kù)對(duì)象提供方便。比如用來(lái)查看數(shù)據(jù)庫(kù)對(duì)象信息對(duì)象提供方便。比如用來(lái)查看數(shù)據(jù)庫(kù)對(duì)象信息的系統(tǒng)存儲(chǔ)過(guò)程的系統(tǒng)存儲(chǔ)過(guò)程sp_help。從物理意義上講，。從物理意義上講，系統(tǒng)存儲(chǔ)過(guò)程存儲(chǔ)在資源數(shù)據(jù)庫(kù)中。從邏輯意系統(tǒng)存儲(chǔ)過(guò)程存儲(chǔ)在資源數(shù)據(jù)庫(kù)中。從邏輯意義上講，系統(tǒng)存儲(chǔ)過(guò)程出現(xiàn)在每個(gè)系統(tǒng)定義數(shù)義上講，系統(tǒng)存儲(chǔ)過(guò)程出現(xiàn)在每個(gè)系統(tǒng)定義數(shù)據(jù)庫(kù)和用戶定義數(shù)據(jù)庫(kù)的據(jù)庫(kù)和用戶定義數(shù)據(jù)庫(kù)的sys構(gòu)架中。構(gòu)架中。存儲(chǔ)過(guò)程、觸發(fā)器和游標(biāo)n擴(kuò)展存儲(chǔ)過(guò)程擴(kuò)展存儲(chǔ)過(guò)程：指：指SQL Server的實(shí)例動(dòng)態(tài)加載和運(yùn)的實(shí)例動(dòng)態(tài)

10、加載和運(yùn)行的行的DLL，這些，這些DLL通常是用編程語(yǔ)言（例如：通常是用編程語(yǔ)言（例如：C）創(chuàng)建的。擴(kuò)展存儲(chǔ)過(guò)程以創(chuàng)建的。擴(kuò)展存儲(chǔ)過(guò)程以xp_為前綴。為前綴。n臨時(shí)存儲(chǔ)過(guò)程臨時(shí)存儲(chǔ)過(guò)程：以：以“#”和和“#”為前綴的過(guò)程，為前綴的過(guò)程，“#”表示本地臨時(shí)存儲(chǔ)過(guò)程，表示本地臨時(shí)存儲(chǔ)過(guò)程，“#”表示全局臨時(shí)存儲(chǔ)表示全局臨時(shí)存儲(chǔ)過(guò)程，它們存儲(chǔ)在過(guò)程，它們存儲(chǔ)在tempdb數(shù)據(jù)庫(kù)中。數(shù)據(jù)庫(kù)中。n遠(yuǎn)程存儲(chǔ)過(guò)程遠(yuǎn)程存儲(chǔ)過(guò)程：是在遠(yuǎn)程服務(wù)器的數(shù)據(jù)庫(kù)中創(chuàng)建和：是在遠(yuǎn)程服務(wù)器的數(shù)據(jù)庫(kù)中創(chuàng)建和存儲(chǔ)的過(guò)程。這些存儲(chǔ)過(guò)程可被各種服務(wù)器訪問(wèn)，存儲(chǔ)的過(guò)程。這些存儲(chǔ)過(guò)程可被各種服務(wù)器訪問(wèn)，向具有相應(yīng)許可權(quán)限的用戶提供服務(wù)。

11、向具有相應(yīng)許可權(quán)限的用戶提供服務(wù)。存儲(chǔ)過(guò)程、觸發(fā)器和游標(biāo)2.存儲(chǔ)過(guò)程的類(lèi)型存儲(chǔ)過(guò)程的類(lèi)型-常用的系統(tǒng)存儲(chǔ)過(guò)程常用的系統(tǒng)存儲(chǔ)過(guò)程系統(tǒng)存儲(chǔ)過(guò)程系統(tǒng)存儲(chǔ)過(guò)程說(shuō)明說(shuō)明sp_databases列出服務(wù)器上的所有數(shù)據(jù)庫(kù)。列出服務(wù)器上的所有數(shù)據(jù)庫(kù)。sp_helpdb報(bào)告有關(guān)指定數(shù)據(jù)庫(kù)或所有數(shù)據(jù)庫(kù)的信息報(bào)告有關(guān)指定數(shù)據(jù)庫(kù)或所有數(shù)據(jù)庫(kù)的信息sp_renamedb更改數(shù)據(jù)庫(kù)的名稱更改數(shù)據(jù)庫(kù)的名稱sp_tables返回當(dāng)前環(huán)境下可查詢的對(duì)象的列表返回當(dāng)前環(huán)境下可查詢的對(duì)象的列表sp_columns回某個(gè)表列的信息回某個(gè)表列的信息sp_help查看某個(gè)表的所有信息查看某個(gè)表的所有信息sp_helpconstraint

12、查看某個(gè)表的約束查看某個(gè)表的約束sp_helpindex查看某個(gè)表的索引查看某個(gè)表的索引sp_stored_procedures列出當(dāng)前環(huán)境中的所有存儲(chǔ)過(guò)程。列出當(dāng)前環(huán)境中的所有存儲(chǔ)過(guò)程。sp_password添加或修改登錄帳戶的密碼。添加或修改登錄帳戶的密碼。sp_helptext顯示默認(rèn)值、未加密的存儲(chǔ)過(guò)程、用戶定義的存顯示默認(rèn)值、未加密的存儲(chǔ)過(guò)程、用戶定義的存儲(chǔ)過(guò)程、觸發(fā)器或視圖的實(shí)際文本。儲(chǔ)過(guò)程、觸發(fā)器或視圖的實(shí)際文本。存儲(chǔ)過(guò)程、觸發(fā)器和游標(biāo)EXEC sp_databasesEXEC sp_renamedb Northwind,Northwind1USE stuDBGOEXEC sp_

13、tablesEXEC sp_columns stuInfo EXEC sp_help stuInfoEXEC sp_helpconstraint stuInfoEXEC sp_helpindex stuMarksEXEC sp_helptext view_stuInfo_stuMarks EXEC sp_stored_procedures 常用的系統(tǒng)存儲(chǔ)過(guò)程常用的系統(tǒng)存儲(chǔ)過(guò)程修改數(shù)據(jù)庫(kù)的名稱修改數(shù)據(jù)庫(kù)的名稱(單用戶訪問(wèn)單用戶訪問(wèn))列出當(dāng)前系統(tǒng)中的數(shù)據(jù)庫(kù)列出當(dāng)前系統(tǒng)中的數(shù)據(jù)庫(kù)當(dāng)前數(shù)據(jù)庫(kù)中查詢的對(duì)象的列表當(dāng)前數(shù)據(jù)庫(kù)中查詢的對(duì)象的列表返回某個(gè)表列的信息返回某個(gè)表列的信息查看表查看表stuInfo的信

14、息的信息查看表查看表stuInfo的約束的約束查看表查看表stuMarks的索引的索引查看視圖的語(yǔ)句文本查看視圖的語(yǔ)句文本查看當(dāng)前數(shù)據(jù)庫(kù)中的存儲(chǔ)過(guò)程查看當(dāng)前數(shù)據(jù)庫(kù)中的存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程、觸發(fā)器和游標(biāo)3.創(chuàng)建、執(zhí)行存儲(chǔ)過(guò)程創(chuàng)建、執(zhí)行存儲(chǔ)過(guò)程使用使用SSMS管理存儲(chǔ)過(guò)程管理存儲(chǔ)過(guò)程創(chuàng)建創(chuàng)建執(zhí)行執(zhí)行 查看查看修改修改刪除刪除存儲(chǔ)過(guò)程、觸發(fā)器和游標(biāo)3.創(chuàng)建、執(zhí)行存儲(chǔ)過(guò)程創(chuàng)建、執(zhí)行存儲(chǔ)過(guò)程 利用利用T-SQLT-SQL語(yǔ)句創(chuàng)建存儲(chǔ)過(guò)程語(yǔ)句創(chuàng)建存儲(chǔ)過(guò)程CREATE PROCEDURE 存儲(chǔ)過(guò)程名存儲(chǔ)過(guò)程名 ;num 參數(shù)參數(shù)1 數(shù)據(jù)類(lèi)型數(shù)據(jù)類(lèi)型 =默認(rèn)值默認(rèn)值 OUTPUT , 其他參數(shù)其他參數(shù) WITH

15、RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION ASBEGIN SQL語(yǔ)句系列語(yǔ)句系列END存儲(chǔ)過(guò)程、觸發(fā)器和游標(biāo)5.2.2 存儲(chǔ)過(guò)程創(chuàng)建和執(zhí)行存儲(chǔ)過(guò)程創(chuàng)建和執(zhí)行;num;num：可選的整數(shù)。用來(lái)對(duì)同名若干過(guò)程編號(hào)，如：可選的整數(shù)。用來(lái)對(duì)同名若干過(guò)程編號(hào)，如 proc_s;1, proc_s;1, proc_s;2, proc_s;3proc_s;2, proc_s;3等。等。采用相同名字加編號(hào)區(qū)別若干過(guò)程的采用相同名字加編號(hào)區(qū)別若干過(guò)程的優(yōu)點(diǎn)優(yōu)點(diǎn)是刪除時(shí)方便，如，是刪除時(shí)方便，如，DROP PROCEDURE proc_sDROP PROCED

16、URE proc_s一條命令把一條命令把3 3個(gè)過(guò)程都刪了。個(gè)過(guò)程都刪了。defaultdefault：參數(shù)的默認(rèn)值。如果定義了默認(rèn)值，不必指定該參參數(shù)的默認(rèn)值。如果定義了默認(rèn)值，不必指定該參數(shù)的值就可調(diào)用。默認(rèn)值必須是常量或數(shù)的值就可調(diào)用。默認(rèn)值必須是常量或NULLNULL。如果過(guò)程將對(duì)該參。如果過(guò)程將對(duì)該參數(shù)使用數(shù)使用LIKELIKE關(guān)鍵字，那么默認(rèn)值中可以包含通配符關(guān)鍵字，那么默認(rèn)值中可以包含通配符(%(%、_ _、)。OUTPUTOUTPUT：表示該參數(shù)是返回參數(shù)。參數(shù)可將信息返回給調(diào)用過(guò)表示該參數(shù)是返回參數(shù)。參數(shù)可將信息返回給調(diào)用過(guò)程。程。TextText、ntextntext、Im

17、ageImage參數(shù)可用作參數(shù)可用作OUTPUTOUTPUT參數(shù)。參數(shù)。RECOMPILERECOMPILE：表明該過(guò)程在運(yùn)行時(shí)重新編譯。表明該過(guò)程在運(yùn)行時(shí)重新編譯。 ENCRYPTION ENCRYPTION ：表示系統(tǒng)加密表示系統(tǒng)加密syscommentssyscomments表中包含表中包含CREATE CREATE PROCEDUREPROCEDURE語(yǔ)句文本的條目。語(yǔ)句文本的條目。 加密后即使是過(guò)程創(chuàng)建者加密后即使是過(guò)程創(chuàng)建者 本人也無(wú)法查看過(guò)程定義文本。本人也無(wú)法查看過(guò)程定義文本。說(shuō)明說(shuō)明:存儲(chǔ)過(guò)程、觸發(fā)器和游標(biāo)3.創(chuàng)建、執(zhí)行存儲(chǔ)過(guò)程創(chuàng)建、執(zhí)行存儲(chǔ)過(guò)程 定義存儲(chǔ)過(guò)程的語(yǔ)法定義存儲(chǔ)過(guò)

18、程的語(yǔ)法 CREATE PROCEDURE 存儲(chǔ)過(guò)程名存儲(chǔ)過(guò)程名 參數(shù)參數(shù)1 數(shù)據(jù)類(lèi)型數(shù)據(jù)類(lèi)型 = 默認(rèn)值默認(rèn)值 OUTPUT, , 參數(shù)參數(shù)n 數(shù)據(jù)類(lèi)型數(shù)據(jù)類(lèi)型 = 默認(rèn)值默認(rèn)值 OUTPUT AS SQL語(yǔ)句語(yǔ)句 GO和和C語(yǔ)言的函數(shù)一樣，參數(shù)可選語(yǔ)言的函數(shù)一樣，參數(shù)可選參數(shù)分為輸入?yún)?shù)、輸出參數(shù)參數(shù)分為輸入?yún)?shù)、輸出參數(shù) 輸入?yún)?shù)允許有默認(rèn)值輸入?yún)?shù)允許有默認(rèn)值存儲(chǔ)過(guò)程、觸發(fā)器和游標(biāo) 創(chuàng)建不帶參數(shù)的存儲(chǔ)過(guò)程創(chuàng)建不帶參數(shù)的存儲(chǔ)過(guò)程問(wèn)題：?jiǎn)栴}：請(qǐng)創(chuàng)建存儲(chǔ)過(guò)程，查看本次考試平均分以及未通過(guò)考試的學(xué)員請(qǐng)創(chuàng)建存儲(chǔ)過(guò)程，查看本次考試平均分以及未通過(guò)考試的學(xué)員名單名單存儲(chǔ)過(guò)程、觸發(fā)器和游標(biāo)創(chuàng)建不帶參數(shù)

19、的存儲(chǔ)過(guò)程創(chuàng)建不帶參數(shù)的存儲(chǔ)過(guò)程CREATE PROCEDURE proc_stu AS DECLARE writtenAvg float,labAvg float SELECT writtenAvg=AVG(writtenExam), labAvg=AVG(labExam) FROM stuMarks print 筆試平均分：筆試平均分：+convert(varchar(5),writtenAvg) print 機(jī)試平均分：機(jī)試平均分：+convert(varchar(5),labAvg) IF (writtenAvg70 AND labAvg70) print 本班考試成績(jī)：優(yōu)秀本班考試成

20、績(jī)：優(yōu)秀 ELSE print 本班考試成績(jī)：較差本班考試成績(jī)：較差 print - print 參加本次考試沒(méi)有通過(guò)的學(xué)員：參加本次考試沒(méi)有通過(guò)的學(xué)員： SELECT stuName,stuInfo.stuNo,writtenExam,labExam FROM stuInfo INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo WHERE writtenExam60 OR labExam60 GOproc_stu為存儲(chǔ)過(guò)程的名稱為存儲(chǔ)過(guò)程的名稱筆試平均分和機(jī)筆試平均分和機(jī)試平均分變量試平均分變量 顯示考試成績(jī)的等級(jí)顯示考試成績(jī)的等級(jí) 顯示未

21、通過(guò)的學(xué)員顯示未通過(guò)的學(xué)員 存儲(chǔ)過(guò)程、觸發(fā)器和游標(biāo) 調(diào)用存儲(chǔ)過(guò)程調(diào)用存儲(chǔ)過(guò)程 EXECUTE（執(zhí)行）（執(zhí)行）語(yǔ)句用來(lái)調(diào)用存儲(chǔ)過(guò)程語(yǔ)句用來(lái)調(diào)用存儲(chǔ)過(guò)程 調(diào)用的語(yǔ)法調(diào)用的語(yǔ)法EXEC 過(guò)程名過(guò)程名 參數(shù)參數(shù)EXEC proc_stu存儲(chǔ)過(guò)程、觸發(fā)器和游標(biāo) 存儲(chǔ)過(guò)程的參數(shù)分兩種：存儲(chǔ)過(guò)程的參數(shù)分兩種：輸入?yún)?shù)輸入?yún)?shù)輸出參數(shù)輸出參數(shù) 輸入?yún)?shù)：輸入?yún)?shù)：用于向存儲(chǔ)過(guò)程用于向存儲(chǔ)過(guò)程傳入傳入值，類(lèi)似值，類(lèi)似C語(yǔ)言的按值傳遞語(yǔ)言的按值傳遞; 輸出參數(shù)：輸出參數(shù)：用于在調(diào)用存儲(chǔ)過(guò)程后，用于在調(diào)用存儲(chǔ)過(guò)程后，返回返回結(jié)果，類(lèi)似結(jié)果，類(lèi)似C語(yǔ)言的語(yǔ)言的按引用傳遞按引用傳遞; 調(diào)用存儲(chǔ)過(guò)程調(diào)用存儲(chǔ)過(guò)程int s

22、um (int a, int b) int s; s=a+b; return s;c=sum（5, 8）傳入?yún)魅雲(yún)?shù)值數(shù)值返回結(jié)果返回結(jié)果存儲(chǔ)過(guò)程、觸發(fā)器和游標(biāo) 傳遞參數(shù)的方式傳遞參數(shù)的方式使用參數(shù)位置使用參數(shù)位置形式：不顯式指出參數(shù)名形式：不顯式指出參數(shù)名順序要求：嚴(yán)格按照定義的順序順序要求：嚴(yán)格按照定義的順序使用參數(shù)名使用參數(shù)名形式：形式：參數(shù)名參數(shù)值參數(shù)名參數(shù)值順序要求：可以不按順序指定參數(shù)值順序要求：可以不按順序指定參數(shù)值存儲(chǔ)過(guò)程、觸發(fā)器和游標(biāo) 帶輸入?yún)?shù)的存儲(chǔ)過(guò)程帶輸入?yún)?shù)的存儲(chǔ)過(guò)程問(wèn)題：?jiǎn)栴}：修改上例：由于每次考試的難易程度不一樣，每次修改上例：由于每次考試的難易程度不一樣，每次

23、 筆試和筆試和機(jī)試的及格線機(jī)試的及格線可能隨時(shí)變化（不再是可能隨時(shí)變化（不再是6060分），這導(dǎo)致考試的分），這導(dǎo)致考試的評(píng)判結(jié)果也相應(yīng)變化。評(píng)判結(jié)果也相應(yīng)變化。分析：分析：在述存儲(chǔ)過(guò)程添加在述存儲(chǔ)過(guò)程添加2個(gè)輸入?yún)?shù)：個(gè)輸入?yún)?shù)：writtenPass 筆試及格線筆試及格線 labPass 機(jī)試及格線機(jī)試及格線 存儲(chǔ)過(guò)程、觸發(fā)器和游標(biāo) 帶輸入?yún)?shù)的存儲(chǔ)過(guò)程帶輸入?yún)?shù)的存儲(chǔ)過(guò)程CREATE PROCEDURE proc_stu writtenPass int, labPass int AS print - print 參加本次考試沒(méi)有通過(guò)的學(xué)員：參加本次考試沒(méi)有通過(guò)的學(xué)員： SELECT st

24、uName,stuInfo.stuNo,writtenExam, labExam FROM stuInfo INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo WHERE writtenExamwrittenPass OR labExamlabPass GO輸入?yún)?shù)：筆試及格線輸入?yún)?shù)：筆試及格線輸入?yún)?shù)：機(jī)試及格線輸入?yún)?shù)：機(jī)試及格線查詢沒(méi)有通過(guò)查詢沒(méi)有通過(guò)考試的學(xué)員考試的學(xué)員存儲(chǔ)過(guò)程、觸發(fā)器和游標(biāo) 帶輸入?yún)?shù)的存儲(chǔ)過(guò)程帶輸入?yún)?shù)的存儲(chǔ)過(guò)程EXEC proc_stu 60,55 調(diào)用帶參數(shù)的存儲(chǔ)過(guò)程調(diào)用帶參數(shù)的存儲(chǔ)過(guò)程 假定本次考試機(jī)試偏難

25、，機(jī)試的及格線定為假定本次考試機(jī)試偏難，機(jī)試的及格線定為55分，筆試及格線定為分，筆試及格線定為60分分-或這樣調(diào)用：或這樣調(diào)用：EXEC proc_stu labPass=55,writtenPass=60機(jī)試及格線降分后，機(jī)試及格線降分后，李斯文李斯文（59分）成為分）成為“漏網(wǎng)之魚(yú)漏網(wǎng)之魚(yú)”了了存儲(chǔ)過(guò)程、觸發(fā)器和游標(biāo) 輸入?yún)?shù)的默認(rèn)值輸入?yún)?shù)的默認(rèn)值 帶參數(shù)的存儲(chǔ)過(guò)程確實(shí)比較方便，調(diào)用者可根據(jù)帶參數(shù)的存儲(chǔ)過(guò)程確實(shí)比較方便，調(diào)用者可根據(jù)試卷的難易度，隨時(shí)修改每次考試的及格線試卷的難易度，隨時(shí)修改每次考試的及格線問(wèn)題：?jiǎn)栴}：如果試卷的難易程度合適，則調(diào)用者還是必須如果試卷的難易程度合適，則調(diào)

26、用者還是必須如此調(diào)用：如此調(diào)用： EXEC proc_stu 60,60，比較麻煩，比較麻煩這樣調(diào)用就比較合理：這樣調(diào)用就比較合理：EXEC proc_stu 55EXEC proc_stu 筆試及格線筆試及格線55分，機(jī)試及格線默認(rèn)分，機(jī)試及格線默認(rèn)為為60分分 筆試和機(jī)試及格線都默認(rèn)為標(biāo)準(zhǔn)的筆試和機(jī)試及格線都默認(rèn)為標(biāo)準(zhǔn)的60分分存儲(chǔ)過(guò)程、觸發(fā)器和游標(biāo)CREATE PROCEDURE proc_stu writtenPass int=60, labPass int=60 AS print - print 參加本次考試沒(méi)有通過(guò)的學(xué)員：參加本次考試沒(méi)有通過(guò)的學(xué)員： SELECT stuName,

27、stuInfo.stuNo,writtenExam, labExam FROM stuInfo INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo WHERE writtenExamwrittenPass OR labExamlabPass GO筆試及格線：默認(rèn)為筆試及格線：默認(rèn)為60分分機(jī)試及格線：默認(rèn)為機(jī)試及格線：默認(rèn)為60分分查詢沒(méi)有通過(guò)考試的學(xué)員查詢沒(méi)有通過(guò)考試的學(xué)員 輸入?yún)?shù)的默認(rèn)值輸入?yún)?shù)的默認(rèn)值存儲(chǔ)過(guò)程、觸發(fā)器和游標(biāo)輸入?yún)?shù)的默認(rèn)值輸入?yún)?shù)的默認(rèn)值EXEC proc_stu -都采用默認(rèn)值都采用默認(rèn)值 EXEC proc_stu

28、 64 -機(jī)試采用默認(rèn)值機(jī)試采用默認(rèn)值 EXEC proc_stu 60,55 -都不采用默認(rèn)值都不采用默認(rèn)值 調(diào)用帶參數(shù)默認(rèn)值的存儲(chǔ)過(guò)程調(diào)用帶參數(shù)默認(rèn)值的存儲(chǔ)過(guò)程-錯(cuò)誤的調(diào)用方式：希望筆試采用默認(rèn)值，機(jī)試及格線錯(cuò)誤的調(diào)用方式：希望筆試采用默認(rèn)值，機(jī)試及格線55分分EXEC proc_stu ,55 -正確的調(diào)用方式：正確的調(diào)用方式：EXEC proc_stu labPass=55 存儲(chǔ)過(guò)程、觸發(fā)器和游標(biāo) 帶輸出參數(shù)的存儲(chǔ)過(guò)程帶輸出參數(shù)的存儲(chǔ)過(guò)程 如果希望調(diào)用存儲(chǔ)過(guò)程后，返回一個(gè)或多個(gè)值，這時(shí)就需如果希望調(diào)用存儲(chǔ)過(guò)程后，返回一個(gè)或多個(gè)值，這時(shí)就需要使用輸出（要使用輸出（OUTPUT）參數(shù)了）參

29、數(shù)了 問(wèn)題：?jiǎn)栴}：修改上例，返回未通過(guò)考試的學(xué)員人數(shù)。修改上例，返回未通過(guò)考試的學(xué)員人數(shù)。存儲(chǔ)過(guò)程、觸發(fā)器和游標(biāo)CREATE PROCEDURE proc_stu notpassSum int OUTPUT, writtenPass int=60, labPass int=60 AS SELECT stuName,stuInfo.stuNo,writtenExam, labExam FROM stuInfo INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo WHERE writtenExamwrittenPass OR labExamlabPass SELECT notpassSum=COUNT(stuNo) FROM stuMarks WHERE writtenEx