第6章SQLServer存儲(chǔ)過程.

1、第9章 存儲(chǔ)過程lSQL語(yǔ)句與前后臺(tái)交互：前前臺(tái)臺(tái)后臺(tái)數(shù)據(jù)庫(kù)后臺(tái)數(shù)據(jù)庫(kù) 執(zhí)行執(zhí)行select 命令，并將結(jié)果返回命令，并將結(jié)果返回商品查詢界面根據(jù)關(guān)鍵詞寫出根據(jù)關(guān)鍵詞寫出select 語(yǔ)句語(yǔ)句lSQL編寫好的語(yǔ)句與前后臺(tái)交互：前前臺(tái)臺(tái)后臺(tái)數(shù)據(jù)庫(kù)后臺(tái)數(shù)據(jù)庫(kù) 自動(dòng)執(zhí)行已編寫好的命令，將結(jié)果返回自動(dòng)執(zhí)行已編寫好的命令，將結(jié)果返回商品查詢界面根據(jù)關(guān)鍵詞寫出調(diào)用語(yǔ)句根據(jù)關(guān)鍵詞寫出調(diào)用語(yǔ)句什么是存儲(chǔ)過程4q存儲(chǔ)過程（procedure）類似于C語(yǔ)言中的函數(shù)q用來執(zhí)行管理任務(wù)或應(yīng)用復(fù)雜的業(yè)務(wù)規(guī)則q存儲(chǔ)過程可以帶參數(shù)，也可以返回結(jié)果int sum(int a,int b) int s; s =a+b; ret

2、urn s ;存儲(chǔ)過程相當(dāng)于C語(yǔ)言中的函數(shù)l 存儲(chǔ)過程（Stored Procedure）是一組完成特定功能的SQL語(yǔ)句集，經(jīng)編譯后存儲(chǔ)在數(shù)據(jù)庫(kù)中。用戶通過指定存儲(chǔ)過程的名字并給出參數(shù)（如果該存儲(chǔ)過程帶有參數(shù)）來執(zhí)行存儲(chǔ)過程。l 利用SQL Server創(chuàng)建一個(gè)應(yīng)用程序時(shí)，SQL是主要的編程語(yǔ)言。使用SQL進(jìn)行編程，有兩種方法:l 其一是，在本地存儲(chǔ)SQL程序，并創(chuàng)建應(yīng)用程序向SQL Server發(fā)送命令來對(duì)結(jié)果進(jìn)行處理。l 其二是，可以把部分用SQL編寫的程序作為存儲(chǔ)過程存儲(chǔ)在SQL Server中，然后創(chuàng)建應(yīng)用程序來調(diào)用存儲(chǔ)過程，對(duì)數(shù)據(jù)結(jié)果進(jìn)行處理。6存儲(chǔ)過程-單個(gè) SELECT 語(yǔ)句SE

3、LECT 語(yǔ)句塊SELECT語(yǔ)句與邏輯控制語(yǔ)句可以包含可以包含q存儲(chǔ)過程可以包含數(shù)據(jù)操縱語(yǔ)句、變量、邏輯 控制語(yǔ)句等存儲(chǔ)過程的優(yōu)點(diǎn)7q 執(zhí)行速度更快：q SQL Server 2005會(huì)事先把存儲(chǔ)過程編譯成二進(jìn)制可執(zhí)行代碼，無(wú)需對(duì)其進(jìn)行編譯q 允許模塊化程序設(shè)計(jì) ：q 存儲(chǔ)過程在創(chuàng)建完畢之后可以在程序中多次被調(diào)用而不必重新編寫該T-SQL語(yǔ)句q 提高系統(tǒng)安全性:q 當(dāng)用戶訪問一個(gè)或多個(gè)數(shù)據(jù)表，但沒有存取權(quán)限時(shí)可以設(shè)計(jì)一個(gè)存儲(chǔ)過程來存取這些表中的數(shù)據(jù)q 減少網(wǎng)絡(luò)流通量：在客戶端調(diào)用時(shí)只使用存儲(chǔ)過程名及參數(shù)即可，網(wǎng)絡(luò)傳送流量比完整的T-SQL程序小得多存儲(chǔ)過程的分類l 在SQL Server 20

4、05中有多種可用的存儲(chǔ)過程。在SQL Server 2005中存儲(chǔ)過程分為三類：系統(tǒng)提供的存儲(chǔ)過程、用戶自定義存儲(chǔ)過程和擴(kuò)展存儲(chǔ)過程。l 1. 1. 系統(tǒng)存儲(chǔ)過程系統(tǒng)存儲(chǔ)過程l SQL Server 2005中的許多管理活動(dòng)都是通過一種特殊的存儲(chǔ)過程執(zhí)行的，這種存儲(chǔ)過程被稱為系統(tǒng)存儲(chǔ)過程。系統(tǒng)過程主要存儲(chǔ)在master數(shù)據(jù)庫(kù)中并以sp_為前綴，并且系統(tǒng)存儲(chǔ)過程主要是從系統(tǒng)表中獲取信息，從而為數(shù)據(jù)庫(kù)系統(tǒng)管理員管理SQL Server提供支持。通過系統(tǒng)存儲(chǔ)過程，SQL Server中的許多管理性或信息性的活動(dòng)（如獲取數(shù)據(jù)庫(kù)和數(shù)據(jù)庫(kù)對(duì)象的信息）都可以被順利有效地完成。l 系統(tǒng)存儲(chǔ)過程的名稱都以系統(tǒng)存

5、儲(chǔ)過程的名稱都以“sp_”sp_”開頭開頭存儲(chǔ)過程的分類l 2. 2. 用戶自定義的存儲(chǔ)過程用戶自定義的存儲(chǔ)過程l 用戶自定義存儲(chǔ)過程是由用戶創(chuàng)建并能完成某一特定功能（如查詢用戶所需數(shù)據(jù)信息）的存儲(chǔ)過程，是封裝了可重用代碼的SQL語(yǔ)句模塊。存儲(chǔ)過程可以接受輸入?yún)?shù)、向客戶端返回表格或標(biāo)量結(jié)果和消息、調(diào)用數(shù)據(jù)定義語(yǔ)言（DDL）和數(shù)據(jù)操作語(yǔ)言（DML）語(yǔ)句，以及返回輸出參數(shù)。在SQL Server中，用戶自定義的存儲(chǔ)過程有兩種類型：T-SQL存儲(chǔ)過程或CLR存儲(chǔ)過程。我們所提到的用戶定義的存儲(chǔ)過程主要指T-SQL存儲(chǔ)過程。l 3. 3. 擴(kuò)展存儲(chǔ)過程擴(kuò)展存儲(chǔ)過程l 擴(kuò)展存儲(chǔ)過程允許使用高級(jí)編程語(yǔ)言

6、（例如C）創(chuàng)建應(yīng)用程序的外部例程，從而使得SQL Server的實(shí)例可以動(dòng)態(tài)加載和運(yùn)行DLL。擴(kuò)展存儲(chǔ)過程直接在SQL Server實(shí)例的地址空間中運(yùn)行。l 擴(kuò)展存儲(chǔ)過程的名稱都以擴(kuò)展存儲(chǔ)過程的名稱都以“xp_”xp_”開頭開頭常用的系統(tǒng)存儲(chǔ)過程10系統(tǒng)存儲(chǔ)過程說明sp_databases列出服務(wù)器上的所有數(shù)據(jù)庫(kù)。sp_helpdb報(bào)告有關(guān)指定數(shù)據(jù)庫(kù)或所有數(shù)據(jù)庫(kù)的信息sp_renamedb更改數(shù)據(jù)庫(kù)的名稱sp_tables返回當(dāng)前環(huán)境下可查詢的對(duì)象的列表sp_columns回某個(gè)表列的信息sp_help查看某個(gè)表的所有信息sp_helpconstraint查看某個(gè)表的約束sp_helpinde

7、x查看某個(gè)表的索引sp_stored_procedures列出當(dāng)前環(huán)境中的所有存儲(chǔ)過程。sp_password添加或修改登錄帳戶的密碼。sp_helptext顯示默認(rèn)值、未加密的存儲(chǔ)過程、用戶定義的存儲(chǔ)過程、觸發(fā)器或視圖的實(shí)際文本。l 用SQL語(yǔ)句創(chuàng)建帶參數(shù)的存儲(chǔ)過程語(yǔ)法格式如下：l CREATE PROC EDURE procedure_name ;number l parameter data_type = default OUTPUT ,.n l WITH RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION l AS sql_statement

8、 .n 和和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)值l procedure_name：新建存儲(chǔ)過程的名稱，其名稱必須符合標(biāo)識(shí)符命名規(guī)則，且對(duì)于數(shù)據(jù)庫(kù)及其所有者必須唯一。l ;number：該參數(shù)是可選的整數(shù)，用來對(duì)同名的過程分組，以便用一條DROP PROCEDURE 語(yǔ)句即可將同組的過程一起刪除。例如，名為orders的應(yīng)用程序使用的過程可以命名為orderproc;1、orderproc;2等。DROP PROCEDURE orderproc 語(yǔ)句將刪除整個(gè)組。l parameter：

9、存儲(chǔ)過程中的輸入和輸出參數(shù)。l data_type：參數(shù)的數(shù)據(jù)類型。l Default：指參數(shù)的默認(rèn)值，必須是常量或NULL。如果定義了默認(rèn)值，不必指定該參數(shù)的值也可執(zhí)行過程。l OUTPUT：表明參數(shù)是返回參數(shù)。該選項(xiàng)的值可以返回給EXECUTE。使用 OUTPUT參數(shù)可將信息返回給調(diào)用過程。l RECOMPILE：表明SQL Server不保存存儲(chǔ)過程的計(jì)劃，該過程將在運(yùn)行時(shí)重新編譯。l ENCRYPTION：表示SQL Server加密syscomments 表中包含語(yǔ)句文本的條目。l sql_statement：指存儲(chǔ)過程中的任意數(shù)目和類型的Transact-SQL語(yǔ)句。l 在存儲(chǔ)過程

10、中使用參數(shù)，可以擴(kuò)展存儲(chǔ)過程的功能。使用輸入?yún)?shù)，可以將外部信息傳入到存儲(chǔ)過程；使用輸出參數(shù)，可以將存儲(chǔ)過程內(nèi)的信息傳出到外部。l sql_statement：指存儲(chǔ)過程中的任意數(shù)目和類型的Transact-SQL語(yǔ)句。l 但是存儲(chǔ)過程的內(nèi)容有如下限制：l 1不能包含下列語(yǔ)句：l a)Create View b)Create Trigger l c)Create Default d)Create Procedurel e)Create Rule (創(chuàng)建規(guī)則)l 2不能再同一存儲(chǔ)過程中創(chuàng)建，刪除，重建同一對(duì)象名l 3不能引用尚未存在的對(duì)象l 4雖然可以引用臨時(shí)表，但臨時(shí)表數(shù)據(jù)清空，容易報(bào)錯(cuò)，因此

11、謹(jǐn)慎使用臨時(shí)表。l 2使用使用SQL Server Management Studio建立存儲(chǔ)過程：建立存儲(chǔ)過程：l 選擇本地?cái)?shù)據(jù)庫(kù)-可編程性-存儲(chǔ)過程-新建存儲(chǔ)過程，然后彈出創(chuàng)建存儲(chǔ)過程的查詢編輯器窗口，其中加入了一些創(chuàng)建存儲(chǔ)過程的代碼。l 然后單擊菜單欄查詢指定模板參數(shù)的值選項(xiàng)：l其中Author為存儲(chǔ)過程的作者lCreate date：創(chuàng)建存儲(chǔ)過程的時(shí)間lDescription：存儲(chǔ)過程說明l以上三項(xiàng)為參數(shù)的可選項(xiàng)，內(nèi)容可以為空l(shuí)param1為第一個(gè)輸入?yún)?shù)名默認(rèn)為p1lDatatype_For_Param1為第一個(gè)輸入?yún)?shù)的類型默認(rèn)為intlDatatype_For_Param2為第一

12、個(gè)輸入?yún)?shù)的類型默認(rèn)為intl設(shè)置完指定模板參數(shù)值選項(xiàng)后單擊確定，返回到創(chuàng)建存儲(chǔ)過程查詢編輯器窗口，這是該窗口內(nèi)容已經(jīng)改變l在“Insert statements for procedure here”下輸入T-SQL代碼：l 然后單擊“執(zhí)行”按鈕完成操作，使用SQL Server Management Studio建立存儲(chǔ)過程歸根結(jié)底與直接使用T-SQL語(yǔ)言創(chuàng)建過程相差不多，只不過有些參數(shù)可以用模板來添加而已，但是要設(shè)計(jì)強(qiáng)大的存儲(chǔ)過程還是需要熟悉Create Procedure語(yǔ)句。19創(chuàng)建不帶參數(shù)的存儲(chǔ)過程創(chuàng)建不帶參數(shù)的存儲(chǔ)過程q創(chuàng)建存儲(chǔ)過程，查看本次考試平均分以及未通過考試的學(xué)員名單20

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

14、差 print - print 參加本次考試沒有通過的學(xué)員： SELECT stuName,stuInfo.stuNo,writtenExam,labExam FROM stuInfo INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo WHERE wrrttenExam60 OR labExam60 GOproc_stu為存儲(chǔ)過程的名稱筆試平均分和機(jī)試平均分變量 顯示考試成績(jī)的等級(jí) 顯示未通過的學(xué)員 調(diào)用存儲(chǔ)過程21qEXECUTE（執(zhí)行）語(yǔ)句用來調(diào)用存儲(chǔ)過程q調(diào)用的語(yǔ)法EXEC 過程名 參數(shù)EXEC proc_stu創(chuàng)建帶參數(shù)的存儲(chǔ)過程2

15、2q存儲(chǔ)過程的參數(shù)分兩種：q 輸入?yún)?shù)q 輸出參數(shù)int sum (int a, int b) int s; s=a+b; return s;c=sum（5, 8）傳入?yún)?shù)值q 輸入?yún)?shù)： 用于向存儲(chǔ)過程傳入值，類似C語(yǔ)言的按值傳遞;q 輸出參數(shù)： 用于在調(diào)用存儲(chǔ)過程后， 返回結(jié)果，類似C語(yǔ)言的 按引用傳遞; 返回結(jié)果帶輸入?yún)?shù)的存儲(chǔ)過程23問題：?jiǎn)栴}：修改上例：由于每次考試的難易程度不一樣，每次 筆試和機(jī)試的及格線可能隨時(shí)變化（不再是60分），這導(dǎo)致考試的評(píng)判結(jié)果也相應(yīng)變化。分析：分析：在述存儲(chǔ)過程添加2個(gè)輸入?yún)?shù)：writtenPass 筆試及格線 labPass 機(jī)試及格線 帶輸入?yún)?shù)的

16、存儲(chǔ)過程24CREATE PROCEDURE proc_stu writtenPass int, labPass int AS print - print 參加本次考試沒有通過的學(xué)員： SELECT stuName,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ù)：機(jī)試及格線查詢沒有通過考試的學(xué)員帶輸入?yún)?shù)的存儲(chǔ)過程25

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

18、 55EXEC proc_stu 筆試及格線55分，機(jī)試及格線默認(rèn)為60分 筆試和機(jī)試及格線都默認(rèn)為標(biāo)準(zhǔn)的60分27CREATE PROCEDURE proc_stu writtenPass int=60, labPass int=60 AS print - print 參加本次考試沒有通過的學(xué)員： SELECT stuName,stuInfo.stuNo,writtenExam, labExam FROM stuInfo INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo WHERE writtenExamwrittenPass OR la

19、bExamlabPass GO筆試及格線：默認(rèn)為60分機(jī)試及格線：默認(rèn)為60分查詢沒有通過考試的學(xué)員輸入?yún)?shù)的默認(rèn)值28EXEC proc_stu -都采用默認(rèn)值 EXEC proc_stu 64 -機(jī)試采用默認(rèn)值 EXEC proc_stu 60,55 -都不采用默認(rèn)值 q調(diào)用帶參數(shù)默認(rèn)值的存儲(chǔ)過程-錯(cuò)誤的調(diào)用方式：希望筆試采用默認(rèn)值，機(jī)試及格線55分EXEC proc_stu ,55 -正確的調(diào)用方式：EXEC proc_stu labPass=55 輸入?yún)?shù)的默認(rèn)值帶輸出參數(shù)的存儲(chǔ)過程29q如果希望調(diào)用存儲(chǔ)過程后，返回一個(gè)或多個(gè)值，這時(shí)就需要使用輸出（OUTPUT）參數(shù)了 問題：修改上例，返回未通過考試的學(xué)員人數(shù)。帶輸出參數(shù)的存儲(chǔ)過程30CREATE PROCEDURE proc_stu notpassSum int OUTPUT, wri