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

第9章存儲(chǔ)過程SQL語句與前后臺(tái)交互：前臺(tái)后臺(tái)數(shù)據(jù)庫執(zhí)行select…命令，并將結(jié)果返回<傳遞>商品查詢界面根據(jù)關(guān)鍵詞寫出select…語句<返回>SQL編寫好的語句與前后臺(tái)交互：前臺(tái)后臺(tái)數(shù)據(jù)庫自動(dòng)執(zhí)行已編寫好的命令，將結(jié)果返回<調(diào)用>商品查詢界面根據(jù)關(guān)鍵詞寫出調(diào)用語句<返回>什么是存儲(chǔ)過程4存儲(chǔ)過程（procedure）類似于C語言中的函數(shù)用來執(zhí)行管理任務(wù)或應(yīng)用復(fù)雜的業(yè)務(wù)規(guī)則存儲(chǔ)過程可以帶參數(shù)，也可以返回結(jié)果intsum(inta,intb){ints;s=a+b;returns;}存儲(chǔ)過程相當(dāng)于C語言中的函數(shù)存儲(chǔ)過程（StoredProcedure）是一組完成特定功能的SQL語句集，經(jīng)編譯后存儲(chǔ)在數(shù)據(jù)庫中。用戶通過指定存儲(chǔ)過程的名字并給出參數(shù)（如果該存儲(chǔ)過程帶有參數(shù)）來執(zhí)行存儲(chǔ)過程。利用SQLServer創(chuàng)建一個(gè)應(yīng)用程序時(shí)，SQL是主要的編程語言。使用SQL進(jìn)行編程，有兩種方法:其一是，在本地存儲(chǔ)SQL程序，并創(chuàng)建應(yīng)用程序向SQLServer發(fā)送命令來對(duì)結(jié)果進(jìn)行處理。其二是，可以把部分用SQL編寫的程序作為存儲(chǔ)過程存儲(chǔ)在SQLServer中，然后創(chuàng)建應(yīng)用程序來調(diào)用存儲(chǔ)過程，對(duì)數(shù)據(jù)結(jié)果進(jìn)行處理。6存儲(chǔ)過程------------------------單個(gè)SELECT語句SELECT語句塊SELECT語句與邏輯控制語句可以包含存儲(chǔ)過程可以包含數(shù)據(jù)操縱語句、變量、邏輯控制語句等存儲(chǔ)過程的優(yōu)點(diǎn)7執(zhí)行速度更快：SQLServer2005會(huì)事先把存儲(chǔ)過程編譯成二進(jìn)制可執(zhí)行代碼，無需對(duì)其進(jìn)行編譯允許模塊化程序設(shè)計(jì)：存儲(chǔ)過程在創(chuàng)建完畢之后可以在程序中多次被調(diào)用而不必重新編寫該T-SQL語句提高系統(tǒng)安全性:當(dāng)用戶訪問一個(gè)或多個(gè)數(shù)據(jù)表，但沒有存取權(quán)限時(shí)可以設(shè)計(jì)一個(gè)存儲(chǔ)過程來存取這些表中的數(shù)據(jù)減少網(wǎng)絡(luò)流通量：在客戶端調(diào)用時(shí)只使用存儲(chǔ)過程名及參數(shù)即可，網(wǎng)絡(luò)傳送流量比完整的T-SQL程序小得多存儲(chǔ)過程的分類在SQLServer2005中有多種可用的存儲(chǔ)過程。在SQLServer2005中存儲(chǔ)過程分為三類：系統(tǒng)提供的存儲(chǔ)過程、用戶自定義存儲(chǔ)過程和擴(kuò)展存儲(chǔ)過程。1.系統(tǒng)存儲(chǔ)過程SQLServer2005中的許多管理活動(dòng)都是通過一種特殊的存儲(chǔ)過程執(zhí)行的，這種存儲(chǔ)過程被稱為系統(tǒng)存儲(chǔ)過程。系統(tǒng)過程主要存儲(chǔ)在master數(shù)據(jù)庫中并以sp_為前綴，并且系統(tǒng)存儲(chǔ)過程主要是從系統(tǒng)表中獲取信息，從而為數(shù)據(jù)庫系統(tǒng)管理員管理SQLServer提供支持。通過系統(tǒng)存儲(chǔ)過程，SQLServer中的許多管理性或信息性的活動(dòng)（如獲取數(shù)據(jù)庫和數(shù)據(jù)庫對(duì)象的信息）都可以被順利有效地完成。系統(tǒng)存儲(chǔ)過程的名稱都以“sp_”開頭存儲(chǔ)過程的分類2.用戶自定義的存儲(chǔ)過程用戶自定義存儲(chǔ)過程是由用戶創(chuàng)建并能完成某一特定功能（如查詢用戶所需數(shù)據(jù)信息）的存儲(chǔ)過程，是封裝了可重用代碼的SQL語句模塊。存儲(chǔ)過程可以接受輸入?yún)?shù)、向客戶端返回表格或標(biāo)量結(jié)果和消息、調(diào)用數(shù)據(jù)定義語言（DDL）和數(shù)據(jù)操作語言（DML）語句，以及返回輸出參數(shù)。在SQLServer中，用戶自定義的存儲(chǔ)過程有兩種類型：T-SQL存儲(chǔ)過程或CLR存儲(chǔ)過程。我們所提到的用戶定義的存儲(chǔ)過程主要指T-SQL存儲(chǔ)過程。3.擴(kuò)展存儲(chǔ)過程擴(kuò)展存儲(chǔ)過程允許使用高級(jí)編程語言（例如C）創(chuàng)建應(yīng)用程序的外部例程，從而使得SQLServer的實(shí)例可以動(dòng)態(tài)加載和運(yùn)行DLL。擴(kuò)展存儲(chǔ)過程直接在SQLServer實(shí)例的地址空間中運(yùn)行。擴(kuò)展存儲(chǔ)過程的名稱都以“xp_”開頭常用的系統(tǒng)存儲(chǔ)過程10系統(tǒng)存儲(chǔ)過程說明sp_databases列出服務(wù)器上的所有數(shù)據(jù)庫。sp_helpdb報(bào)告有關(guān)指定數(shù)據(jù)庫或所有數(shù)據(jù)庫的信息sp_renamedb更改數(shù)據(jù)庫的名稱sp_tables返回當(dāng)前環(huán)境下可查詢的對(duì)象的列表sp_columns回某個(gè)表列的信息sp_help查看某個(gè)表的所有信息sp_helpconstraint查看某個(gè)表的約束sp_helpindex查看某個(gè)表的索引sp_stored_procedures列出當(dāng)前環(huán)境中的所有存儲(chǔ)過程。sp_password添加或修改登錄帳戶的密碼。sp_helptext顯示默認(rèn)值、未加密的存儲(chǔ)過程、用戶定義的存儲(chǔ)過程、觸發(fā)器或視圖的實(shí)際文本。用SQL語句創(chuàng)建帶參數(shù)的存儲(chǔ)過程語法格式如下：

CREATEPROC[EDURE]procedure_name[;number][{@parameterdata_type}[=default][OUTPUT]][,...n][WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION]ASsql_statement[...n]和C#語言的函數(shù)一樣，參數(shù)可選參數(shù)分為輸入?yún)?shù)、輸出參數(shù)輸入?yún)?shù)允許有默認(rèn)值procedure_name：新建存儲(chǔ)過程的名稱，其名稱必須符合標(biāo)識(shí)符命名規(guī)則，且對(duì)于數(shù)據(jù)庫及其所有者必須唯一。;number：該參數(shù)是可選的整數(shù)，用來對(duì)同名的過程分組，以便用一條DROPPROCEDURE語句即可將同組的過程一起刪除。例如，名為orders的應(yīng)用程序使用的過程可以命名為orderproc;1、orderproc;2等。DROPPROCEDUREorderproc語句將刪除整個(gè)組。parameter：存儲(chǔ)過程中的輸入和輸出參數(shù)。data_type：參數(shù)的數(shù)據(jù)類型。Default：指參數(shù)的默認(rèn)值，必須是常量或NULL。如果定義了默認(rèn)值，不必指定該參數(shù)的值也可執(zhí)行過程。OUTPUT：表明參數(shù)是返回參數(shù)。該選項(xiàng)的值可以返回給EXEC[UTE]。使用OUTPUT參數(shù)可將信息返回給調(diào)用過程。RECOMPILE：表明SQLServer不保存存儲(chǔ)過程的計(jì)劃，該過程將在運(yùn)行時(shí)重新編譯。ENCRYPTION：表示SQLServer加密syscomments表中包含語句文本的條目。sql_statement：指存儲(chǔ)過程中的任意數(shù)目和類型的Transact-SQL語句。在存儲(chǔ)過程中使用參數(shù)，可以擴(kuò)展存儲(chǔ)過程的功能。使用輸入?yún)?shù)，可以將外部信息傳入到存儲(chǔ)過程；使用輸出參數(shù)，可以將存儲(chǔ)過程內(nèi)的信息傳出到外部。sql_statement：指存儲(chǔ)過程中的任意數(shù)目和類型的Transact-SQL語句。但是存儲(chǔ)過程的內(nèi)容有如下限制：1>不能包含下列語句：a)CreateViewb)CreateTriggerc)CreateDefaultd)CreateProceduree)CreateRule(創(chuàng)建規(guī)則)2>不能再同一存儲(chǔ)過程中創(chuàng)建，刪除，重建同一對(duì)象名3>不能引用尚未存在的對(duì)象4>雖然可以引用臨時(shí)表，但臨時(shí)表數(shù)據(jù)清空，容易報(bào)錯(cuò)，因此謹(jǐn)慎使用臨時(shí)表。2>使用SQLServerManagementStudio建立存儲(chǔ)過程：選擇本地?cái)?shù)據(jù)庫->可編程性-->存儲(chǔ)過程-->新建存儲(chǔ)過程，然后彈出創(chuàng)建存儲(chǔ)過程的查詢編輯器窗口，其中加入了一些創(chuàng)建存儲(chǔ)過程的代碼。然后單擊菜單欄查詢指定模板參數(shù)的值選項(xiàng)：其中Author為存儲(chǔ)過程的作者Createdate：創(chuàng)建存儲(chǔ)過程的時(shí)間Description：存儲(chǔ)過程說明以上三項(xiàng)為參數(shù)的可選項(xiàng)，內(nèi)容可以為空@param1為第一個(gè)輸入?yún)?shù)名默認(rèn)為@p1Datatype_For_Param1為第一個(gè)輸入?yún)?shù)的類型默認(rèn)為intDatatype_For_Param2為第一個(gè)輸入?yún)?shù)的類型默認(rèn)為int設(shè)置完指定模板參數(shù)值選項(xiàng)后單擊確定，返回到創(chuàng)建存儲(chǔ)過程查詢編輯器窗口，這是該窗口內(nèi)容已經(jīng)改變?cè)凇癐nsertstatementsforprocedurehere”下輸入T-SQL代碼：然后單擊“執(zhí)行”按鈕完成操作，使用SQLServerManagementStudio建立存儲(chǔ)過程歸根結(jié)底與直接使用T-SQL語言創(chuàng)建過程相差不多，只不過有些參數(shù)可以用模板來添加而已，但是要設(shè)計(jì)強(qiáng)大的存儲(chǔ)過程還是需要熟悉CreateProcedure語句。19創(chuàng)建不帶參數(shù)的存儲(chǔ)過程創(chuàng)建存儲(chǔ)過程，查看本次考試平均分以及未通過考試的學(xué)員名單20創(chuàng)建不帶參數(shù)的存儲(chǔ)過程CREATEPROCEDUREproc_stu

ASDECLARE@writtenAvgfloat,@labAvgfloat

SELECT@writtenAvg=AVG(writtenExam),

@labAvg=AVG(labExam)

FROMstuMarksprint'筆試平均分：'+convert(varchar(10),@writtenAvg)print'機(jī)試平均分：'+convert(varchar(10),@labAvg)IF(@writtenAvg>70AND@labAvg>70)print‘本班考試成績：優(yōu)秀'ELSEprint‘本班考試成績：較差'print'--------------------------------------------------'print‘參加本次考試沒有通過的學(xué)員：'SELECTstuName,stuInfo.stuNo,writtenExam,labExam

FROMstuInfoINNERJOINstuMarksON

stuInfo.stuNo=stuMarks.stuNo

WHEREwrrttenExam<60ORlabExam<60GOproc_stu為存儲(chǔ)過程的名稱筆試平均分和機(jī)試平均分變量顯示考試成績的等級(jí)顯示未通過的學(xué)員調(diào)用存儲(chǔ)過程21EXECUTE（執(zhí)行）語句用來調(diào)用存儲(chǔ)過程調(diào)用的語法

EXEC

過程名[參數(shù)]

EXECproc_stu創(chuàng)建帶參數(shù)的存儲(chǔ)過程22存儲(chǔ)過程的參數(shù)分兩種：輸入?yún)?shù)輸出參數(shù)

intsum(inta,intb){ints;s=a+b;returns;}c=sum（5,8）傳入?yún)?shù)值輸入?yún)?shù)：用于向存儲(chǔ)過程傳入值，類似C語言的按值傳遞;

輸出參數(shù)：用于在調(diào)用存儲(chǔ)過程后，

返回結(jié)果，類似C語言的按引用傳遞;

返回結(jié)果帶輸入?yún)?shù)的存儲(chǔ)過程23問題：修改上例：由于每次考試的難易程度不一樣，每次筆試和機(jī)試的及格線可能隨時(shí)變化（不再是60分），這導(dǎo)致考試的評(píng)判結(jié)果也相應(yīng)變化。分析：在述存儲(chǔ)過程添加2個(gè)輸入?yún)?shù)：@writtenPass筆試及格線@labPass機(jī)試及格線帶輸入?yún)?shù)的存儲(chǔ)過程24CREATEPROCEDUREproc_stu

@writtenPassint,

@labPassint

ASprint'--------------------------------------------------'print'參加本次考試沒有通過的學(xué)員：'SELECTstuName,stuInfo.stuNo,writtenExam,labExamFROMstuInfoINNERJOINstuMarksONstuInfo.stuNo=stuMarks.stuNoWHEREwrittenExam<@writtenPass

ORlabExam<@labPass

GO輸入?yún)?shù)：筆試及格線輸入?yún)?shù)：機(jī)試及格線查詢沒有通過考試的學(xué)員帶輸入?yún)?shù)的存儲(chǔ)過程25EXECproc_stu60,55調(diào)用帶參數(shù)的存儲(chǔ)過程

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

@labPassint=60

，則調(diào)用者還是必須如此調(diào)用：EXECproc_stu60,60，比較麻煩這樣調(diào)用就比較合理：EXECproc_stu55EXECproc_stu

筆試及格線55分，機(jī)試及格線默認(rèn)為60分筆試和機(jī)試及格線都默認(rèn)為標(biāo)準(zhǔn)的60分27CREATEPROCEDUREproc_stu

@writtenPassint=60,

@labPassint=60

ASprint'--------------------------------------------------'print'參加本次考試沒有通過的學(xué)員：'SELECTstuName,stuInfo.stuNo,writtenExam,labExamFROMstuInfoINNERJOINstuMarksONstuInfo.stuNo=stuMarks.stuNoWHEREwrittenExam<@writtenPass

ORlabExam<@labPass

GO筆試及格線：默認(rèn)為60分機(jī)試及格線：默認(rèn)為60分查詢沒有通過考試的學(xué)員輸入?yún)?shù)的默認(rèn)值28EXECproc_stu--都采用默認(rèn)值EXECproc_stu64--機(jī)試采用默認(rèn)值EXECproc_stu60,55--都不采用默認(rèn)值調(diào)用帶參數(shù)默認(rèn)值的存儲(chǔ)過程--錯(cuò)誤的調(diào)用方式：希望筆試采用默認(rèn)值，機(jī)試及格線55分EXECproc_stu,55

--正確的調(diào)用方式：EXECproc_stu@labPass=55輸入?yún)?shù)的默認(rèn)值帶輸出參數(shù)的存儲(chǔ)過程29如果希望調(diào)用存儲(chǔ)過程后，返回一個(gè)或多個(gè)值，這時(shí)就需要使用輸出（OUTPUT）參數(shù)了問題：修改上例，返回未通過考試的學(xué)員人數(shù)。帶輸出參數(shù)的存儲(chǔ)過程30CREATEPROCEDUREproc_stu

@notpassSumintOUTPUT,@writtenPassint=60,@labPassint=60ASSELECTstuName,stuInfo.stuNo,writtenExam,labExamFROMstuInfoINNERJOINstuMarksONstuInfo.stuNo=stuMarks.stuNoWHEREwrittenExam<@writtenP