SQL_存儲過程學(xué)習(xí)總結(jié)

1、學(xué)號學(xué)生姓名1snai2小紅1存儲過程的定義-存儲過程(procedure)類似于C語言中的函數(shù) 用來執(zhí)行管理任務(wù)或應(yīng)用復(fù)雜的業(yè)務(wù)規(guī)則 存儲過程可以帶參數(shù)，也可以返回結(jié)果存儲過程相 當(dāng)于 C 語言中 的函數(shù)存儲過程可以包含數(shù)據(jù)操縱語句、變量、邏輯控制語句等2存儲過程的優(yōu)點(diǎn)執(zhí)行速度更快存儲過程只在創(chuàng)造時(shí)進(jìn)行編譯即可，以后每次執(zhí)行存儲過程都不需再重 新編譯，而我們通常使用的SQI語句每執(zhí)行一次就編譯一次，所以使用存儲過 程可提高數(shù)據(jù)庫執(zhí)行速度。允許模塊化程序設(shè)計(jì)當(dāng)對數(shù)據(jù)庫進(jìn)行復(fù)雜操作時(shí)(如對多個(gè)表進(jìn)行Updatensert,Query,Delete時(shí))，可將此復(fù)雜操作用存儲過程封裝起來與數(shù)據(jù)庫提供

2、的事務(wù)處理結(jié)合一起使用?？梢詷O大的提高數(shù)據(jù)庫的使用效率,減少程序的執(zhí)行時(shí)間，這一點(diǎn)在較大數(shù)據(jù)量的數(shù)據(jù)庫的操作中是非常重要的。提高系統(tǒng)安全性可設(shè)定只有某此用戶才具有對指定存儲過程的使用權(quán)存儲過程 減少網(wǎng)絡(luò)流通量select * from成績表select * from1結(jié)果J消息學(xué)號筆試成鹼上機(jī)成績1 505160592S0&27175學(xué)生信息表int sum(i nt a,i ntb) int s;s =a+b; returns ;3、存儲過程的分類3.1系統(tǒng)存儲過程由系統(tǒng)定義，存放在master數(shù)據(jù)庫中也 系統(tǒng)存儲過程的名稱都以“sp_”開頭或”xp_”開頭3.2用戶自定義存儲過程由

3、用戶在自己的數(shù)據(jù)庫中創(chuàng)建的存儲過程4、常用的系統(tǒng)存儲過程4.1系統(tǒng)存儲過程列表系統(tǒng)存儲過程說明sp_databases列出服務(wù)器上的所有數(shù)據(jù)庫。sp_helpdb報(bào)告有關(guān)指疋數(shù)據(jù)庫或所有數(shù)據(jù)庫的信息sp_re namedb更改數(shù)據(jù)庫的名稱sp_tables返回當(dāng)前環(huán)境下可查詢的對象的列表sp_colu mns回某個(gè)表列的信息sp_help查看某個(gè)表的所有信息sp_helpc on stra in t查看某個(gè)表的約束sp_help in dex查看某個(gè)表的索引sp_stored_proced ures列出當(dāng)前環(huán)境中的所有存儲過程。sp_password添加或修改登錄帳戶的密碼。sp_helpte

4、xt顯示默認(rèn)值、未加密的存儲過程、用戶定義的存儲 過程、觸發(fā)器或視圖的實(shí)際文本。4.2調(diào)用常用的系統(tǒng)存儲過程EXECsp_databases -列出當(dāng)前系統(tǒng)中的數(shù)據(jù)庫EXECsp_renamedb Northwind，Northwind1-修改數(shù)據(jù)庫的名稱（單用戶訪問）USE stuDB -當(dāng)前數(shù)據(jù)庫中查詢的對象的列表GOEXECsp_tables -返回某個(gè)表列的信息EXECsp_columns stuInfo-返回某個(gè)表列的信息EXECsp_help stuInfo-查看表stuInfo的信息EXECsp_helpconstraintstuInfo -查看表stuInfo的約束EXECsp

5、_helpindexstuMarks -查看表stuMarks的索引EXECsp_helptext view_stulnfo_stuMarks-查看視圖的語句文本EXECsp_stored_procedures -查看當(dāng)前數(shù)據(jù)庫中的存儲過程4.3調(diào)用常用的擴(kuò)展存儲過程：xp_cmdshell可以執(zhí)行DOS命令下的一些的操作，以文本行方式返回任何輸出調(diào)用語法：EXECxp_cmdshell DOS命令NO_OUTPUT【案例分析】創(chuàng)建數(shù)據(jù)庫bankDB，要求保存在D:bankUSE masterGOEXECxp_cmdshell mkdir d:ba nk,no_output -創(chuàng)建文件夾D:b

6、a nkIF EXISTS( SELECT* FROMsysdatabasesWHEREnam=bankDB)DROPDATABASEDa nkDBGOCREATEDATABASEba nkDB()GO-查看文件夾D:bankEXECxp_cmdshell dir D:bank-查看文件驅(qū)動器I中的卷沒有標(biāo)簽.暮曲序列號是Z3OC-1AFENULLD:的目錄NULL2OD7-LC-O51：17小工 O2007-丄口一0衛(wèi)s：丄?Y工2007-10-03XG : 173,145,728 ankDBid at亙.md f200?-丄心一0占丄右： 丄*?! axikDE_丄o g8-2個(gè)文件字韋7

7、2個(gè)目錄丄湘全占“4口可用宇節(jié)NULL5、創(chuàng)建存儲過程5.1定義存儲過程的語法createproc edure存儲過程名參數(shù)數(shù)據(jù)類型=默認(rèn)值outputJ參數(shù)n數(shù)據(jù)類型=默認(rèn)值outputASSQL語句GO參數(shù)可選參數(shù)分為輸入?yún)?shù)、輸出參數(shù)輸入?yún)?shù)允許有默認(rèn)值5.2創(chuàng)建不帶參數(shù)的存儲過程數(shù)【問題】請創(chuàng)建存儲過程，查看本次考試平均分以及未通過考試的學(xué)員名單說明：筆試和機(jī)試都通過了60分才算通過。-創(chuàng)建不帶參數(shù)的存儲過程createprocedure proc_stuASdeclare avgwri floatdeclare avglab floatselect avgwri =avg (筆試成績

8、), avglab =avg (上機(jī)成績)from成績表-筆試平均分和機(jī)試平均分變量print 筆試成績分?jǐn)?shù)=+convert ( varchar (10), avgwri )print 上機(jī)成績分?jǐn)?shù)=+convert (varchar ( 10), avglab )if avgwri 70 and avglab 70-顯示考試成績的等級print 本班成績：優(yōu)秀elseprint 本班成績：較差-顯示未通過的學(xué)員print - print 參加考試不及格的學(xué)生select a .學(xué)生姓名，a.學(xué)號,b.筆試成績，b.上機(jī)成績from學(xué)生信息表as ainnerjoin成績表as b on a

9、 .學(xué)號=b.學(xué)號where b .筆試成績60or b .上機(jī)成績60GO5.3調(diào)用存儲過程execute(執(zhí)行)語句用來調(diào)用存儲過程調(diào)用的語法：EXEC過程名參數(shù)EXEC proc stu5.4帶參數(shù)的存儲過程存儲過程的參數(shù)分兩種：輸入?yún)?shù)、輸出參數(shù)輸入?yún)?shù)：用于向存儲過程 傳入值，類似C語言的按值傳遞；輸出參數(shù)：用于在調(diào)用存儲過程后，返回結(jié)果，類似C語言的按引【問題】修改上例：由于每次考試的難易程度不一樣，每次筆試和機(jī)試的及格線可能隨時(shí) 變化（不再是分），這導(dǎo)致考試的評判結(jié)果也相應(yīng)變化。說明：根據(jù)試卷的難度，我們希望筆試和機(jī)試的及格線應(yīng)該是隨時(shí)變化的，而不是固定的60分?！痉治觥吭谑龃鎯?/p>

10、過程添加個(gè)輸入?yún)?shù)：writte nPass筆試及格線labPass機(jī)試及格線-帶輸入?yún)?shù)的存儲過程createprocedureproc stu2writtenPassint ,-輸入?yún)?shù)：筆試及格線labPass int -輸入?yún)?shù)：機(jī)試及格線ASprint =print 參加考試不及格的學(xué)生select a.學(xué)生姓名，a.學(xué)號,b.筆試成績，b.上機(jī)成績from學(xué)生信息表as a-查詢沒有通過考試的學(xué)員innerjoin成績表as b on a .學(xué)號=b.學(xué)號where b .筆試成績writtenpassor b .上機(jī)成績labpassGO調(diào)用帶參數(shù)的存儲過程-假定本次考試機(jī)試偏難

11、，機(jī)試的及格線定為分，筆試及格線定為分-機(jī)試及格線降分后，李斯文（分）成為漏網(wǎng)之魚”了exec proc_stu2 60, 55-或這樣調(diào)用：EXEC proc stu2 labPass=55, writtenPass =60 5.5 帶輸入?yún)?shù)的默認(rèn)值問題：如果試卷的難易程度合適，則調(diào)用者還是必須如此調(diào)用:EXEC proc_stu2 60這樣調(diào)用就比較合理：,60，比較麻煩EXEC proc_stu2 55-筆試及格線分，機(jī)試及格線默認(rèn)為分EXEC proc_stu2-筆試和機(jī)試及格線都默認(rèn)為標(biāo)準(zhǔn)的分createprocedure proc_stu3writtenPassint =60,-

12、筆試及格線：默認(rèn)為分labPass int =60-機(jī)試及格線：默認(rèn)為分ASprint =print 參加本次考試沒有通過的學(xué)員：select a .學(xué)生姓名,a.學(xué)號,b.筆試成績,b.上機(jī)成績from學(xué)生信息表as a -查詢沒有通過考試的學(xué)員innerjoin成績表as b ONa .學(xué)號=b.學(xué)號WHERE筆試成績writtenPassOR上機(jī)成績labPassGO調(diào)用帶參數(shù)默認(rèn)值的存儲過程EXEC proc_stu-都采用默認(rèn)值EXEC proc_stu 64-機(jī)試采用默認(rèn)值EXEC proc_stu 60,55-都不米用默認(rèn)值-錯(cuò)誤的調(diào)用方式：希望筆試采用默認(rèn)值，機(jī)試及格線分EXE

13、C proc stu, 55-止確的調(diào)用方式：EXEC proc stu labPass=555.6帶輸出參數(shù)的存儲過程如果希望調(diào)用存儲過程后，返回一個(gè)或多個(gè)值，這時(shí)就需要使用輸出（OUTPUT參數(shù)了問題：修改上例，返回未通過考試的學(xué)員人數(shù)。CREATEPROCEDURproc_stu4notpassSumintOUTPUT ,-輸出（返回）參數(shù)：表示沒有通過的人數(shù)writtenPassint =60 ,labPass int =60AS-推薦將默認(rèn)參數(shù)放后select a.學(xué)生姓名,a.學(xué)號,b.筆試成績,b.上機(jī)成績from學(xué)生信息表as a-統(tǒng)計(jì)并返回沒有通過考試的學(xué)員人數(shù)innerjo

14、in成績表as b on a .學(xué)號=b.學(xué)號where b .筆試成績vwrittenpass or b .上機(jī)成績labpassselect notpassnum =count (學(xué)號)from成績表where筆試成績writtenpass or上機(jī)成績=3-后續(xù)語句引用返回結(jié)果print 未通過人數(shù)：+convert (varchar (5), sun)+ 人，超過%,及格分?jǐn)?shù)線還應(yīng)下調(diào)ELSEprint 未通過人數(shù)：+convert ( varchar ( 5), sum)+ 人，已控制在以下，及格分?jǐn)?shù)線適中GO強(qiáng)調(diào)1.調(diào)用時(shí)也必須跟隨關(guān)鍵字OUTPUT否則SQL Server將視為輸

15、入?yún)?shù)。5.7處理存儲過程中的錯(cuò)誤可以使用print語句顯示錯(cuò)誤信息，但這 些信息是臨時(shí)的，只能顯示給用戶數(shù)raiserror顯示用戶定義的錯(cuò)誤信息時(shí)可指定嚴(yán)重級別，設(shè)置系統(tǒng)變量ERROR記錄所發(fā)生的錯(cuò)誤等5.7.1使用raiserror語句raiserror語句的用法如下：raiserror (msg_id|msg_str , severity , stateWITHoptio n ,. n)粵msg_id:在sysmessages系統(tǒng)表中指定用戶定義錯(cuò)誤信息粵msg_str:用戶定義的特定信息，最長255個(gè)字符粵severity:定義嚴(yán)重性級別。用戶可使用的級別為08級御state:表示錯(cuò)

16、誤的狀態(tài)，1至127之間的值option:指示是否將錯(cuò)誤記錄到服務(wù)器錯(cuò)誤日志中問題：完善上例，當(dāng)用戶調(diào)用存儲過程時(shí)，傳入的及格線參數(shù)不 在之間時(shí)，將彈出錯(cuò)誤警告，終止存儲過程的執(zhí)行。:也皓栗|十肖息 tuuuii,級 SHULr狀態(tài) 丄過程 proc第 LLI 行叢怡紅牯 i*指 IT一 WON司酌血放.沅胡中 EtJi 退岀I_說明：筆試和機(jī)試都通過了60分才算通過。CREATEPROCEDURproc_stu5notpassSumintOUTPUT ,-輸出參數(shù)writtenPassint =60 ,-默認(rèn)參數(shù)放后labPass int =60-默認(rèn)參數(shù)放后AS-錯(cuò)誤處理IF (NOT w

17、rittenPassBETWEEN0 AND100 )OR(NOT labPass BETWEEN0 AND 100 )BEGINraiserror (及格線錯(cuò)誤，請指定一之間的分?jǐn)?shù)，統(tǒng)計(jì)中斷退出,16, 1）RETURN-立即返回，退出存儲過程END.其他語句同上例，略GO-引發(fā)系統(tǒng)錯(cuò)誤，指定錯(cuò)誤的嚴(yán)重級別，調(diào)用狀態(tài)為（默認(rèn)），并影響 系統(tǒng)變量的值/*-調(diào)用存儲過程，測試RAISERROR語句-*/DECLAREsum int , t intEXEC proc_stu sum OUTPUT 604 -筆試及格線誤輸入分SET t=ERRORprint 錯(cuò)誤號：+convert (varcha

18、r (5), t )IF t 0-不等于0RETURN-退出批處理，后續(xù)語句不再執(zhí)行print =IF sum=3-如果執(zhí)行了RAISERROR語句，系統(tǒng)全局ERROR不等于，表示出現(xiàn)了錯(cuò)誤print 未通過人數(shù)：+convert (varchar ( 5), sum)+ 人，超過%,及格分?jǐn)?shù)線還應(yīng)下調(diào)ELSEprint 未通過人數(shù)：+convert ( varchar ( 5), sum)+ 人，已控制在以下,及格分?jǐn)?shù)線適中GO6用戶自定義函數(shù)在SQL Server中,用戶不僅可以使用標(biāo)準(zhǔn)的內(nèi)置函數(shù)，也可以使用自己定義的函數(shù)來實(shí)現(xiàn)一些特殊的功能。-用戶自定義函數(shù)可以在企業(yè)管理器中創(chuàng)建，也可以

19、使用create function語句創(chuàng)建。在創(chuàng)建時(shí)需要注意：函數(shù)名在數(shù)據(jù)庫中必須唯一，其可以有參數(shù)，也可以沒有參數(shù)，其參數(shù)只能是輸入?yún)?shù)，最多可以有1024參數(shù)。標(biāo)量函數(shù)：返回單個(gè)數(shù)據(jù)值。加 表值函數(shù)：返回值是一個(gè)記錄集合- 表。在此函數(shù)中，return語句包含一條單獨(dú)的select語句。多語句表值函數(shù)：返回值是由選擇的結(jié)果構(gòu)成的記錄集。6.1使用create function語句創(chuàng)建用戶自定義函數(shù)使用createfu nctio n創(chuàng)建用戶自定義函數(shù)，其語法格式如下：createf unction owner_n ame. functioname( parameter_ name AS s

20、calar_parameter_data_type =default ,. n )returns scalar_return_data_typeas beg infunction _bodyret unrn scalar_expressi onendfunction_name:指用戶自定義函數(shù)的名稱。其名稱必須符合標(biāo)識符的命 名規(guī)則，并且對其所有者來說，該名稱在數(shù)據(jù)庫中必須唯一。parameter_name：用戶自定義函數(shù)的參數(shù)，其可以是一個(gè)或多個(gè)。每 個(gè)函數(shù)的參數(shù)僅用于該函數(shù)本身；相同的參數(shù)名稱可以用在其它函數(shù)中。 參數(shù)只能代替常量；而不能用于代替表名、列名或其它數(shù)據(jù)庫對象的名稱。 函數(shù)執(zhí)行

21、時(shí)每個(gè)已聲明參數(shù)的值必須由用戶指定，除非該參數(shù)的默認(rèn)值已經(jīng)定義。如果函數(shù)的參數(shù)有默認(rèn)值，在調(diào)用該函數(shù)時(shí)必須指定default關(guān)鍵字才能獲得默認(rèn)值。scalar_parameter_data_type:參數(shù)的數(shù)據(jù)類型。scalar_return_data_type：是用戶定義函數(shù)的返回值。 可以是SQL Server支 持的任何標(biāo)量數(shù)據(jù)類型(text、ntextimage和timestamp除外)。function_body:位于begin和end之間的一系列Transact-SQL語句，其只 用于標(biāo)量函數(shù)和多語句表值函數(shù)。scalar_expression用戶自定義函數(shù)中返回值的表達(dá)式。6.2

22、標(biāo)量函數(shù)例:在stuDB庫中創(chuàng)建一個(gè)用戶自定義標(biāo)量值函數(shù)xuefen,該函數(shù)通過輸入成績 來判斷是否取得學(xué)分，當(dāng)成績大于等于60時(shí)，返回取得學(xué)分，否則，返回未取 得學(xué)分。代碼如下：USE stuDBGOCREATEFUNCTIONxuefen ( chengjiint ) RETURNSnvarchar (10)BEGINdeclareretur nsxuefe nnv archar (10)if che ngji 60set returnsxuefen=取得學(xué)分elseset returnsxuefen=不能取得學(xué)分RETURNreturnsxuefe nENDGO使用剛才定義的xuefen

23、函數(shù)來查看課程號為“”的課程，學(xué)生獲得學(xué)分的情況。 在查詢編輯器中輸入如下代碼：USE stuDBGOSELECT學(xué)號,成績=(筆試成績+上機(jī)成績)/2,dbo.xuefen(筆試成績+上機(jī)成績)/2) AS學(xué)分情況FROM成績表WHERE課程號=GO6.3表值函數(shù)表值函數(shù)遵循的原則：-RETURNS?句僅包含關(guān)鍵字table。不必定義返回變量的格式，因?yàn)樗蒖ETURN子句中的SELECT語句的結(jié)果集的格式設(shè)置。FUNCTION BODY不由BEGIN和END分隔。RETURN?句在括號中包含單個(gè)SELECT!句。SELECTS句的結(jié)果集構(gòu)成函 數(shù)所返回的表。例：在stuDB庫中創(chuàng)建一個(gè)內(nèi)嵌

24、表值函數(shù)XUESHENG該函數(shù)可以根據(jù)輸入 的系部代碼返回該系學(xué)生的基本信息。其代碼如下：CREATEFUNCTIONXUESHENGf inputdep_nvarchar_(4) RETURNStable ASRETURN(SELECT學(xué)號,姓名FROM學(xué)生WHERE所屬院系=inputdep )GO建立好該內(nèi)嵌表值函數(shù)后，就可以象使用表或視圖一樣來使用它：SELECT* FROM DBO . XUESHENG )GO6.4多語句表值函數(shù)多語句函數(shù)的主體中允許使用以下語句：豈賦值語句DECLARE語句，該語句定義函數(shù)局部的數(shù)據(jù)變量和游標(biāo)。SELECT語句，該語句包含帶有表達(dá)式的選擇列表，其中

25、的表達(dá)式將值賦 予函數(shù)的局部變量。游標(biāo)操作，該操作引用在函數(shù)中聲明、打開、關(guān)閉和釋放的局部游標(biāo)。只 允許使用以INTO子句向局部變量賦值的FETCH語句；不允許使用將數(shù)據(jù) 返回到客戶端的FETCH語句。INSERT UPDATEDELET語句，這些語句修改函數(shù)的局部table變量。EXECUTE句調(diào)用擴(kuò)展存儲過程。6.5多語句表值函數(shù)案例：在stuDB庫中創(chuàng)建一個(gè)多語句表值函數(shù)chengji,該函數(shù)可以根據(jù)輸入的課 程名稱返回選修該課程的學(xué)生姓名和成績。代碼如下：USE stuDBGOCREATEFUNCTIONchengji( inputkm aschar (20)/*為chengji函數(shù)定

26、義的表結(jié)構(gòu)，名稱變量為cji*/RETURNScj TABLE(科目編號varchar ( 10),姓名varchar (10),成績int )ASBEGININSERT cj /*該變量是上面定義的表名稱變量*/select b .科目編號，a.學(xué)生姓名,(b.筆試成績+b.上機(jī)成績)/2from學(xué)生信息表as a innerjoin成績表as bon a .學(xué)號=b.學(xué)號where b .科目編號=鬥putkmRETURNENDGO-在查詢編輯器中輸入以下查詢命令：SELECT * FROM dbo.che ngji(9001)6.6查看、修改和刪除自定義函數(shù)1.查看用戶自定義函數(shù)的屬性在SQL Server中，根據(jù)不同需要，可以使用sp_helptext、sp_help等系統(tǒng)存儲過程來查看用戶自定義函數(shù)的不同信息。每個(gè)系統(tǒng)存儲過程的具體作用和