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

1、select * from 成績表select * from 學(xué)生信息表1 存儲過程的定義· 存儲過程（procedure）類似于C語言中的函數(shù) · 用來執(zhí)行管理任務(wù)或應(yīng)用復(fù)雜的業(yè)務(wù)規(guī)則· 存儲過程可以帶參數(shù)，也可以返回結(jié)果· 存儲過程可以包含數(shù)據(jù)操縱語句、變量、邏輯 控制語句等 2 存儲過程的優(yōu)點· 執(zhí)行速度更快· 允許模塊化程序設(shè)計· 提高系統(tǒng)安全性· 減少網(wǎng)絡(luò)流通量3、存儲過程的分類3.1 系統(tǒng)存儲過程· 由系統(tǒng)定義，存放在master數(shù)據(jù)庫中· 類似C語言中的系統(tǒng)函數(shù)· 系統(tǒng)存

2、儲過程的名稱都以“sp_”開頭或”xp_”開頭3.2 用戶自定義存儲過程· 由用戶在自己的數(shù)據(jù)庫中創(chuàng)建的存儲過程· 類似C語言中的用戶自定義函數(shù)4、常用的系統(tǒng)存儲過程4.1 系統(tǒng)存儲過程列表系統(tǒng)存儲過程說明sp_databases列出服務(wù)器上的所有數(shù)據(jù)庫。sp_helpdb報告有關(guān)指定數(shù)據(jù)庫或所有數(shù)據(jù)庫的信息sp_renamedb更改數(shù)據(jù)庫的名稱sp_tables返回當(dāng)前環(huán)境下可查詢的對象的列表sp_columns回某個表列的信息sp_help查看某個表的所有信息sp_helpconstraint查看某個表的約束sp_helpindex查看某個表的索引sp_stored_p

3、rocedures列出當(dāng)前環(huán)境中的所有存儲過程。sp_password添加或修改登錄帳戶的密碼。sp_helptext顯示默認(rèn)值、未加密的存儲過程、用戶定義的存儲過程、觸發(fā)器或視圖的實際文本。4.2 調(diào)用常用的系統(tǒng)存儲過程EXEC sp_databases-列出當(dāng)前系統(tǒng)中的數(shù)據(jù)庫EXEC sp_renamedb 'Northwind','Northwind1' -修改數(shù)據(jù)庫的名稱(單用戶訪問)USE stuDB -當(dāng)前數(shù)據(jù)庫中查詢的對象的列表GOEXEC sp_tables -返回某個表列的信息EXEC sp_columns stuInfo -返回某個表列的信息

4、EXEC sp_help stuInfo -查看表stuInfo的信息EXEC sp_helpconstraint stuInfo -查看表stuInfo的約束EXEC sp_helpindex stuMarks -查看表stuMarks的索引EXEC sp_helptext 'view_stuInfo_stuMarks' -查看視圖的語句文本EXEC sp_stored_procedures -查看當(dāng)前數(shù)據(jù)庫中的存儲過程4.3 調(diào)用常用的擴展存儲過程：xp_cmdshell· 可以執(zhí)行DOS命令下的一些的操作,以文本行方式返回任何輸出 · 調(diào)用語法：EXE

5、C xp_cmdshell DOS命令NO_OUTPUT【案例分析】創(chuàng)建數(shù)據(jù)庫bankDB，要求保存在D:bankUSE masterGOEXEC xp_cmdshell 'mkdir d:bank', no_output -創(chuàng)建文件夾D:bankIF EXISTS(SELECT * FROM sysdatabases WHERE name='bankDB') DROP DATABASE bankDBGOCREATE DATABASE bankDB ( )GO -查看文件夾D:bankEXEC xp_cmdshell 'dir D:bank' -

6、查看文件5、創(chuàng)建存儲過程5.1 定義存儲過程的語法 create procedure 存儲過程名 參數(shù) 數(shù)據(jù)類型= 默認(rèn)值output, , 參數(shù)n 數(shù)據(jù)類型= 默認(rèn)值output AS SQL語句 GO· 和C語言的函數(shù)一樣，參數(shù)可選· 參數(shù)分為輸入?yún)?shù)、輸出參數(shù)· 輸入?yún)?shù)允許有默認(rèn)值5.2 創(chuàng)建不帶參數(shù)的存儲過程· 【問題】請創(chuàng)建存儲過程，查看本次考試平均分以及未通過考試的學(xué)員名單說明：筆試和機試都通過了60分才算通過。-創(chuàng)建不帶參數(shù)的存儲過程create procedure proc_stuASdeclare avgwri float,avgla

7、b floatselect avgwri=avg(筆試成績),avglab=avg(上機成績) from 成績表 -筆試平均分和機試平均分變量print '筆試成績分?jǐn)?shù)='+convert(varchar(10),avgwri)print '上機成績分?jǐn)?shù)='+convert(varchar(10),avglab)if avgwri>70 and avglab>70 -顯示考試成績的等級print '本班成績：優(yōu)秀'elseprint '本班成績：較差' -顯示未通過的學(xué)員print '-'print &

8、#39; 參加考試不及格的學(xué)生 'select a.學(xué)生姓名,a.學(xué)號,b.筆試成績,b.上機成績from 學(xué)生信息表as a inner join 成績表as b on a.學(xué)號=b.學(xué)號where b.筆試成績<60 or b.上機成績<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

9、語言的按引用傳遞; 【問題】修改上例：由于每次考試的難易程度不一樣，每次筆試和機試的及格線可能隨時變化（不再是分），這導(dǎo)致考試的評判結(jié)果也相應(yīng)變化。說明：根據(jù)試卷的難度，我們希望筆試和機試的及格線應(yīng)該是隨時變化的，而不是固定的60分?！痉治觥吭谑龃鎯^程添加個輸入?yún)?shù)：writtenPass 筆試及格線labPass 機試及格線-帶輸入?yún)?shù)的存儲過程create procedure proc_stu2 writtenPass int, -輸入?yún)?shù)：筆試及格線 labPass int -輸入?yún)?shù)：機試及格線 ASprint '='print '參加考試不及格的學(xué)生'

10、;select a.學(xué)生姓名,a.學(xué)號,b.筆試成績,b.上機成績 from 學(xué)生信息表 as a -查詢沒有通過考試的學(xué)員 inner join 成績表as b on a.學(xué)號=b.學(xué)號 where b.筆試成績<writtenpass or b.上機成績<labpass GO· 調(diào)用帶參數(shù)的存儲過程-假定本次考試機試偏難，機試的及格線定為分，筆試及格線定為分-機試及格線降分后，李斯文（分）成為“漏網(wǎng)之魚”了exec proc_stu2 60,55-或這樣調(diào)用：EXEC proc_stu2 labPass=55,writtenPass=605.5 帶輸入?yún)?shù)的默認(rèn)值問題

11、：如果試卷的難易程度合適，則調(diào)用者還是必須如此調(diào)用：EXEC proc_stu2 60,60，比較麻煩這樣調(diào)用就比較合理：EXEC proc_stu2 55-筆試及格線分，機試及格線默認(rèn)為分EXEC proc_stu2-筆試和機試及格線都默認(rèn)為標(biāo)準(zhǔn)的分create procedure proc_stu3 writtenPass int=60, -筆試及格線：默認(rèn)為分 labPass int=60 -機試及格線：默認(rèn)為分 AS print '=' print '參加本次考試沒有通過的學(xué)員：'select a.學(xué)生姓名,a.學(xué)號,b.筆試成績,b.上機成績 from

12、 學(xué)生信息表 as a-查詢沒有通過考試的學(xué)員 inner join 成績表 as b ON a.學(xué)號=b.學(xué)號 WHERE 筆試成績<writtenPass OR 上機成績<labPassGO· 調(diào)用帶參數(shù)默認(rèn)值的存儲過程EXEC proc_stu -都采用默認(rèn)值EXEC proc_stu 64 -機試采用默認(rèn)值EXEC proc_stu 60,55 -都不采用默認(rèn)值-錯誤的調(diào)用方式：希望筆試采用默認(rèn)值，機試及格線分EXEC proc_stu ,55 -正確的調(diào)用方式：EXEC proc_stu labPass=55 5.6 帶輸出參數(shù)的存儲過程如果希望調(diào)用存儲過程后，

13、返回一個或多個值，這時就需要使用輸出（OUTPUT）參數(shù)了 問題：修改上例，返回未通過考試的學(xué)員人數(shù)。CREATE PROCEDURE proc_stu4 notpassSum int OUTPUT, -輸出（返回）參數(shù)：表示沒有通過的人數(shù) writtenPass int=60, labPass int=60 AS -推薦將默認(rèn)參數(shù)放后 select a.學(xué)生姓名,a.學(xué)號,b.筆試成績,b.上機成績 from 學(xué)生信息表 as a -統(tǒng)計并返回沒有通過考試的學(xué)員人數(shù) inner join 成績表 as b on a.學(xué)號=b.學(xué)號 where b.筆試成績<writtenpass or

14、 b.上機成績<labpass select notpassnum=count(學(xué)號) from 成績表 where 筆試成績<writtenpass or 上機成績<labpassGO強調(diào)：1.默認(rèn)值放在參數(shù)的數(shù)據(jù)類型后面，而不是放在參數(shù)變量的后面。2.為了調(diào)用的方便，推薦將默認(rèn)參數(shù)放置在參數(shù)列表的最后 · 調(diào)用帶輸出參數(shù)的存儲過程/*-調(diào)用存儲過程-*/DECLARE sum int EXEC proc_stu sum OUTPUT ,64 -調(diào)用時必須帶OUTPUT關(guān)鍵字，返回結(jié)果將存放在變量sum中 print '='IF sum>=3

15、 -后續(xù)語句引用返回結(jié)果 print '未通過人數(shù)：'+convert(varchar(5),sum)+ '人,超過%,及格分?jǐn)?shù)線還應(yīng)下調(diào)'ELSE print '未通過人數(shù)：'+convert(varchar(5),sum)+ '人,已控制在%以下，及格分?jǐn)?shù)線適中'GO強調(diào)：1.調(diào)用時也必須跟隨關(guān)鍵字OUTPUT，否則SQL Server將視為輸入?yún)?shù)。5.7 處理存儲過程中的錯誤· 可以使用print語句顯示錯誤信息，但這 些信息是臨時的，只能顯示給用戶 · raiserror 顯示用戶定義的錯誤信息時&#

16、183; 可指定嚴(yán)重級別，· 設(shè)置系統(tǒng)變量ERROR· 記錄所發(fā)生的錯誤等 5.7.1 使用raiserror 語句raiserror語句的用法如下： raiserror (msg_id | msg_str,severity,state WITH option,.n)· msg_id：在sysmessages系統(tǒng)表中指定用戶定義錯誤信息· msg_str：用戶定義的特定信息，最長255個字符· severity：定義嚴(yán)重性級別。用戶可使用的級別為018級· state：表示錯誤的狀態(tài)，1至127之間的值· option：指示

17、是否將錯誤記錄到服務(wù)器錯誤日志中 問題：完善上例，當(dāng)用戶調(diào)用存儲過程時，傳入的及格線參數(shù)不在之間時，將彈出錯誤警告，終止存儲過程的執(zhí)行。說明：筆試和機試都通過了60分才算通過。CREATE PROCEDURE proc_stu5 notpassSum int OUTPUT, -輸出參數(shù) writtenPass int=60, -默認(rèn)參數(shù)放后 labPass int=60 -默認(rèn)參數(shù)放后 AS -錯誤處理 IF (NOT writtenPass BETWEEN 0 AND 100) OR (NOT labPass BETWEEN 0 AND 100) BEGINraiserror ('及

18、格線錯誤，請指定之間的分?jǐn)?shù)，統(tǒng)計中斷退出',16,1) RETURN -立即返回，退出存儲過程 END .其他語句同上例，略GO -引發(fā)系統(tǒng)錯誤，指定錯誤的嚴(yán)重級別，調(diào)用狀態(tài)為（默認(rèn)）,并影響ERROR系統(tǒng)變量的值/*-調(diào)用存儲過程，測試RAISERROR語句-*/DECLARE sum int, t intEXEC proc_stu sum OUTPUT ,604 -筆試及格線誤輸入分SET t=ERROR print '錯誤號：'+convert(varchar(5),t )IF t<>0 RETURN -退出批處理，后續(xù)語句不再執(zhí)行print '

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

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

21、ame ( parameter_name AS scalar_parameter_data_type = default ,.n ) returns scalar_return_data_type as begin function_body retunrn scalar_expression endl function_name：指用戶自定義函數(shù)的名稱。其名稱必須符合標(biāo)識符的命名規(guī)則，并且對其所有者來說，該名稱在數(shù)據(jù)庫中必須唯一。l parameter_name：用戶自定義函數(shù)的參數(shù)，其可以是一個或多個。每個函數(shù)的參數(shù)僅用于該函數(shù)本身；相同的參數(shù)名稱可以用在其它函數(shù)中。參數(shù)只能代替常量；而不

22、能用于代替表名、列名或其它數(shù)據(jù)庫對象的名稱。函數(shù)執(zhí)行時每個已聲明參數(shù)的值必須由用戶指定，除非該參數(shù)的默認(rèn)值已經(jīng)定義。如果函數(shù)的參數(shù)有默認(rèn)值，在調(diào)用該函數(shù)時必須指定"default"關(guān)鍵字才能獲得默認(rèn)值。l scalar_parameter_data_type：參數(shù)的數(shù)據(jù)類型。l scalar_return_data_type：是用戶定義函數(shù)的返回值。可以是 SQL Server 支持的任何標(biāo)量數(shù)據(jù)類型（text、ntext、image 和 timestamp 除外）。l function_body：位于begin和end之間的一系列 Transact-SQL 語句，其只用于

23、標(biāo)量函數(shù)和多語句表值函數(shù)。l scalar_expression：用戶自定義函數(shù)中返回值的表達式。6.2 標(biāo)量函數(shù)例：在stuDB庫中創(chuàng)建一個用戶自定義標(biāo)量值函數(shù)xuefen，該函數(shù)通過輸入成績來判斷是否取得學(xué)分，當(dāng)成績大于等于60時，返回取得學(xué)分，否則，返回未取得學(xué)分。代碼如下：USE stuDBGOCREATE FUNCTION xuefen(chengji int) RETURNS nvarchar(10) BEGIN declare returnsxuefen nvarchar(10) if chengji>60 set returnsxuefen='取得學(xué)分'

24、else set returnsxuefen='不能取得學(xué)分' RETURN returnsxuefenENDGO使用剛才定義的xuefen函數(shù)來查看課程號為“”的課程，學(xué)生獲得學(xué)分的情況。在查詢編輯器中輸入如下代碼：USE stuDBGOSELECT 學(xué)號,成績=(筆試成績+上機成績)/2,dbo.xuefen(筆試成績+上機成績)/2) AS 學(xué)分情況FROM 成績表 WHERE 課程號='GO6.3 表值函數(shù)表值函數(shù)遵循的原則：· RETURNS子句僅包含關(guān)鍵字table。不必定義返回變量的格式，因為它由RETURN 子句中的 SELECT 語句的結(jié)果集

25、的格式設(shè)置。· FUNCTION BODY 不由BEGIN和END分隔。· RETURN子句在括號中包含單個SELECT語句。SELECT語句的結(jié)果集構(gòu)成函數(shù)所返回的表。例：在stuDB庫中創(chuàng)建一個內(nèi)嵌表值函數(shù)XUESHENG，該函數(shù)可以根據(jù)輸入的系部代碼返回該系學(xué)生的基本信息。其代碼如下：CREATE FUNCTION XUESHENG(inputdep nvarchar(4) RETURNS table AS RETURN ( SELECT 學(xué)號, 姓名 FROM 學(xué)生WHERE 所屬院系=inputdep)GO建立好該內(nèi)嵌表值函數(shù)后，就可以象使用表或視圖一樣來使用它：

26、SELECT * FROM DBO.XUESHENG(')GO6.4 多語句表值函數(shù)多語句函數(shù)的主體中允許使用以下語句：· 賦值語句· DECLARE 語句，該語句定義函數(shù)局部的數(shù)據(jù)變量和游標(biāo)。· SELECT 語句，該語句包含帶有表達式的選擇列表，其中的表達式將值賦予函數(shù)的局部變量。· 游標(biāo)操作，該操作引用在函數(shù)中聲明、打開、關(guān)閉和釋放的局部游標(biāo)。只允許使用以INTO子句向局部變量賦值的FETCH語句；不允許使用將數(shù)據(jù)返回到客戶端的FETCH語句。· INSERT、UPDATE和DELETE語句，這些語句修改函數(shù)的局部table變量。

27、· EXECUTE語句調(diào)用擴展存儲過程。6.5 多語句表值函數(shù)案例：在stuDB庫中創(chuàng)建一個多語句表值函數(shù)chengji，該函數(shù)可以根據(jù)輸入的課程名稱返回選修該課程的學(xué)生姓名和成績。代碼如下：USE stuDBGOCREATE FUNCTION chengji( inputkm as char(20) )/*為chengji 函數(shù)定義的表結(jié)構(gòu)，名稱變量為cji*/RETURNS cj TABLE (科目編號varchar(10), 姓名varchar(10), 成績int )ASBEGIN INSERT cj /*該變量是上面定義的表名稱變量*/ select b.科目編號,a.學(xué)生姓名,(b.筆試成績+b.上機成績)/2 from 學(xué)生信息表as a inner join 成績表as b on a.學(xué)號=b.學(xué)號where b.科目編號=inputkm RETURNENDGO· 在查詢編輯器中輸入以下查詢命令：· SELECT * FROM dbo.chengji(9001)6.6 查看、修改和刪除自定義函數(shù)1.查看用戶自定義函數(shù)的屬性在SQL Server中，根據(jù)不同需要，可以使用sp_helptext、sp_help等系統(tǒng)存儲過程來查看用戶自定義函數(shù)的不同信息。每個系統(tǒng)存儲過程的