1、第8章 存儲(chǔ)過程,存儲(chǔ)過程,存儲(chǔ)過程是為了實(shí)現(xiàn)某個(gè)特定任務(wù)，由一組預(yù)先編譯好的SQL語(yǔ)句組成，將其放在服務(wù)器上，由用戶通過指定存儲(chǔ)過程的名字來(lái)執(zhí)行的一種數(shù)據(jù)庫(kù)對(duì)象。 存儲(chǔ)過程類型 系統(tǒng)存儲(chǔ)過程 擴(kuò)展存儲(chǔ)過程 用戶自定義存儲(chǔ)過程,系統(tǒng)存儲(chǔ)過程存儲(chǔ)以SP_為前綴,是由SQL Server2005自己創(chuàng)建、管理和使用的一種特殊的存儲(chǔ)過程，不能對(duì)其進(jìn)行修改或刪除。如Sp_helpdb、Sp_renamedb等。,由用戶自行創(chuàng)建的存儲(chǔ)過程，可以輸入?yún)?shù)、向客戶端返回表格或結(jié)果、消息等，也可以返回輸出函數(shù)。,第8章 存儲(chǔ)過程,8.1 創(chuàng)建管理簡(jiǎn)單存儲(chǔ)過程 8.2 創(chuàng)建管理帶參的存儲(chǔ)過程,第8章 存儲(chǔ)過程,

2、8.1 創(chuàng)建管理簡(jiǎn)單存儲(chǔ)過程 8.2 創(chuàng)建管理帶參的存儲(chǔ)過程,8.1 創(chuàng)建管理簡(jiǎn)單的存儲(chǔ)過程,8.1.1 無(wú)參數(shù)存儲(chǔ)過程的創(chuàng)建 8.1.2 無(wú)參數(shù)存儲(chǔ)過程的執(zhí)行 8.1.3 查看存儲(chǔ)過程 8.1.4 修改存儲(chǔ)過程 8.1.5 編譯存儲(chǔ)過程 8.1.6 刪除存儲(chǔ)過程,8.1.1 無(wú)參數(shù)存儲(chǔ)過程的創(chuàng)建,1）語(yǔ)法格式： CREATE PROC EDURE procedure_name AS sql_statement .n ,2）語(yǔ)法注釋： procedure_name：新建存儲(chǔ)過程的名稱，其名稱必須符合標(biāo)識(shí)符命名規(guī)則，且對(duì)于數(shù)據(jù)庫(kù)及其所有者必須唯一。 sql_statement：指存儲(chǔ)過程中的任意

3、數(shù)目和類型的Transact-SQL語(yǔ)句。,8.1.1 無(wú)參存儲(chǔ)過程的創(chuàng)建,例1：在student數(shù)據(jù)庫(kù)中，創(chuàng)建一個(gè)名稱為pr_searchScore的存儲(chǔ)過程，該存儲(chǔ)過程將查詢出選修表中“降龍十八掌”課程的成績(jī)信息。 代碼如下： CREATE PROC AS SELECT a.Lid, Sid, score FROM T_opt a , T_lesson b WHERE a.Lid=b.Lid and lname= 降龍十八掌,pr_searchScore,創(chuàng)建存儲(chǔ)過程的注意事項(xiàng),1) 存儲(chǔ)過程是數(shù)據(jù)庫(kù)對(duì)象，其名稱必須遵守標(biāo)識(shí)符命名規(guī)則。 2) 不能將 CREATE PROCEDURE 語(yǔ)句

4、與其它 SQL 語(yǔ)句組合到單個(gè)批處理中。 3) 創(chuàng)建存儲(chǔ)過程時(shí)，應(yīng)指定所有輸入?yún)?shù)和向調(diào)用過程或批處理返回的輸出參數(shù)、執(zhí)行數(shù)據(jù)庫(kù)操作的編程語(yǔ)句和返回至調(diào)用過程或批處理以表明成功或失敗的狀態(tài)值。,8.1 創(chuàng)建管理簡(jiǎn)單的存儲(chǔ)過程,8.1.1 無(wú)參數(shù)存儲(chǔ)過程的創(chuàng)建 8.1.2 無(wú)參數(shù)存儲(chǔ)過程的執(zhí)行 8.1.3 查看存儲(chǔ)過程 8.1.4 修改存儲(chǔ)過程 8.1.5 編譯存儲(chǔ)過程 8.1.6 刪除存儲(chǔ)過程,8.1.2 無(wú)參數(shù)存儲(chǔ)過程的執(zhí)行,對(duì)存儲(chǔ)在服務(wù)器上的存儲(chǔ)過程，可以使用EXECUTE命令或其名稱執(zhí)行它，語(yǔ)法格式： EXEC UTE procedure_name 對(duì)上例的存儲(chǔ)過程pr_searchSc

5、ore的執(zhí)行語(yǔ)句如下： EXEC pr_searchScore 或者 pr_searchScore,8.1 創(chuàng)建管理簡(jiǎn)單的存儲(chǔ)過程,8.1.1 無(wú)參數(shù)存儲(chǔ)過程的創(chuàng)建 8.1.2 無(wú)參數(shù)存儲(chǔ)過程的執(zhí)行 8.1.3 查看存儲(chǔ)過程 8.1.4 修改存儲(chǔ)過程 8.1.5 編譯存儲(chǔ)過程 8.1.6 刪除存儲(chǔ)過程,8.1.3 查看存儲(chǔ)過程,1.使用對(duì)象資源管理器查看存儲(chǔ)過程 2.使用系統(tǒng)存儲(chǔ)過程查看存儲(chǔ)過程信息 1）使用sp_helptext查看存儲(chǔ)過程的文本信息： sp_helptext 存儲(chǔ)過程名 2）使用sp_depends查看存儲(chǔ)過程的相關(guān)性： sp_depends 存儲(chǔ)過程名 3）使用sp_he

6、lp查看存儲(chǔ)過程的一般信息. sp_help 存儲(chǔ)過程名,8.1 創(chuàng)建管理簡(jiǎn)單的存儲(chǔ)過程,8.1.1 無(wú)參數(shù)存儲(chǔ)過程的創(chuàng)建 8.1.2 無(wú)參數(shù)存儲(chǔ)過程的執(zhí)行 8.1.3 查看存儲(chǔ)過程 8.1.4 修改存儲(chǔ)過程 8.1.5 編譯存儲(chǔ)過程 8.1.6 刪除存儲(chǔ)過程,8.1.4 修改存儲(chǔ)過程,修改存儲(chǔ)過程語(yǔ)法格式為： ALTER PROCDURE procedure_name parameter data_type =defaultOUTPUT,n WITH RECOMPILE | ENCRYPTION | RECOMPILE,ENCRYPTION AS Sql_statement,8.1.4 修改

7、存儲(chǔ)過程,例2：修改存儲(chǔ)過程pr_searchScore,查詢出成績(jī)表中降龍十八掌課程所有低于100分的成績(jī)。 ALTER PROC As SELECT a.Lid, Sid, score FROM T_opt a , T_lesson b WHERE a.Lid=b.Lid and lname= 降龍十八掌 and score 100,Pr_searchScore,8.1 創(chuàng)建管理簡(jiǎn)單的存儲(chǔ)過程,8.1.1 無(wú)參數(shù)存儲(chǔ)過程的創(chuàng)建 8.1.2 無(wú)參數(shù)存儲(chǔ)過程的執(zhí)行 8.1.3 查看存儲(chǔ)過程 8.1.4 修改存儲(chǔ)過程 8.1.5 編譯存儲(chǔ)過程 8.1.6 刪除存儲(chǔ)過程,8.1.5 編譯存儲(chǔ)過程,

8、在使用了一次存儲(chǔ)過程后，可能會(huì)因?yàn)槟承┰?，必須向表中新增加?shù)據(jù)列或者為表新添加索引，從而改變了數(shù)據(jù)庫(kù)的邏輯結(jié)構(gòu)。這時(shí)，需要對(duì)存儲(chǔ)過程進(jìn)行重新編譯。,8.1.5 編譯存儲(chǔ)過程,SQL Server提供三種重新編譯存儲(chǔ)過程的方法 ： 1)在建立存儲(chǔ)過程時(shí)設(shè)定重新編譯 語(yǔ)法格式：CREATE PROCEDURE procedure_name WITH RECOMPILE AS sql_statement 2)在執(zhí)行存儲(chǔ)過程時(shí)設(shè)定重編譯 語(yǔ)法格式：EXECUTE procedure_name WITH RECOMPILE 3)通過使用系統(tǒng)存儲(chǔ)過程設(shè)定重編譯 語(yǔ)法格式為： EXEC sp_recomp

9、ile procedure_name,8.1 創(chuàng)建管理簡(jiǎn)單的存儲(chǔ)過程,8.1.1 無(wú)參數(shù)存儲(chǔ)過程的創(chuàng)建 8.1.2 無(wú)參數(shù)存儲(chǔ)過程的執(zhí)行 8.1.3 查看存儲(chǔ)過程 8.1.4 修改存儲(chǔ)過程 8.1.5 編譯存儲(chǔ)過程 8.1.6 刪除存儲(chǔ)過程,8.1.6 刪除存儲(chǔ)過程,1).使用SSMS對(duì)象資源管理器刪除存儲(chǔ)過程 2).使用DROP PROCEDURE語(yǔ)句刪除存儲(chǔ)過程 DROP PROCEDURE語(yǔ)句可以一次從當(dāng)前數(shù)據(jù)庫(kù)中將一個(gè)或多個(gè)存儲(chǔ)過程或過程組刪除： DROP PROCEDURE 存儲(chǔ)過程名稱,n 例：刪除存儲(chǔ)過程pr_searchScore DROP PROCEDURE pr_searc

10、hScore,第8章 存儲(chǔ)過程,8.1 創(chuàng)建管理簡(jiǎn)單存儲(chǔ)過程 8.2 創(chuàng)建管理帶參數(shù)的存儲(chǔ)過程,8.2 創(chuàng)建管理帶參數(shù)的存儲(chǔ)過程,8.2.1 創(chuàng)建帶參數(shù)存儲(chǔ)過程的語(yǔ)法 8.2.2 帶輸入?yún)?shù)的存儲(chǔ)過程 8.2.3 帶輸出參數(shù)的存儲(chǔ)過程 8.2.4 存儲(chǔ)過程的返回值,8.2.1創(chuàng)建帶參數(shù)存儲(chǔ)過程的語(yǔ)法,1）語(yǔ)法： CREATE PROCDURE procedure_name parameter data_type =defaultOUTPUT,n WITH RECOMPILE | ENCRYPTION | RECOMPILE,ENCRYPTION AS Sql_statement,8.1.1創(chuàng)建

11、帶參數(shù)存儲(chǔ)過程的語(yǔ)法,2）語(yǔ)法注釋： parameter：存儲(chǔ)過程中的輸入和輸出參數(shù)。 data_type：參數(shù)的數(shù)據(jù)類型。 OUTPUT：表明參數(shù)是輸出參數(shù)。該選項(xiàng)的值可以返回給EXECUTE。,8.2 創(chuàng)建管理帶參數(shù)的存儲(chǔ)過程,8.2.1 創(chuàng)建帶參數(shù)存儲(chǔ)過程的語(yǔ)法 8.2.2 帶輸入?yún)?shù)的存儲(chǔ)過程 8.2.3 帶輸出參數(shù)的存儲(chǔ)過程 8.2.4 存儲(chǔ)過程的返回值,8.2.2 帶輸入?yún)?shù)的存儲(chǔ)過程,例3：在student數(shù)據(jù)庫(kù)中創(chuàng)建一個(gè)存儲(chǔ)過程pr_searchStuScore，查詢出指定的某位學(xué)生的所有成績(jī)。 CREATE PROC pr_searchStuScore As Select L

12、id, a.Sid, Score From T_opt a, T_student b Where a.Sid=b.Sid,and sname=,sname nchar(10),sname,8.2.2 帶輸入?yún)?shù)的存儲(chǔ)過程,執(zhí)行帶輸入?yún)?shù)的存儲(chǔ)過程： EXEC UTE return_status = procedure_name parameter = value | variable OUTPUT | DEFAULT ,.n WITH RECOMPILE 執(zhí)行存儲(chǔ)過程pr_searchStuScore ： EXECUTE pr_searchStuScore 黃蓉 EXEC pr_searchS

13、tuScore sname = 黃蓉,8.2.2 帶輸入?yún)?shù)的存儲(chǔ)過程,執(zhí)行帶輸入?yún)?shù)的存儲(chǔ)過程： EXEC UTE return_status = procedure_name parameter = value | variable OUTPUT | DEFAULT ,.n WITH RECOMPILE 執(zhí)行存儲(chǔ)過程pr_searchStuScore ： declare Sname nchar(10) set Sname=黃蓉 EXEC pr_searchStuScore Sname,8.2.2 帶輸入?yún)?shù)的存儲(chǔ)過程,例4：創(chuàng)建存儲(chǔ)過程pr_searchStuLesson ，根據(jù)指定的學(xué)生

14、姓名和課程名查詢成績(jī) 。 CREATE PROC pr_searchStuLesson AS SELECT Lname, Sname,score FROM T_opt a,T_student b,T_lesson c WHERE a.Sid=b.Sid and b.Lid=c.Lid,sname nchar(10),and b.Sname=,sname,and c.Lname =,lname, lname nchar(20),8.2.2 帶輸入?yún)?shù)的存儲(chǔ)過程,1) 按位置傳遞參數(shù)值 在執(zhí)行存儲(chǔ)過程的語(yǔ)句中，直接給出參數(shù)的值。當(dāng)有多個(gè)參數(shù)時(shí)，給出的參數(shù)的順序與創(chuàng)建執(zhí)行存儲(chǔ)過程的語(yǔ)句中的參數(shù)的書寫

15、順序一致。 2) 通過參數(shù)名傳遞參數(shù)值 在執(zhí)行存儲(chǔ)過程的語(yǔ)句中，使用【參數(shù)名=參數(shù)值】的形式給出參數(shù)值。其優(yōu)點(diǎn)是參數(shù)可以以任意順序給出。,8.2.2 帶輸入?yún)?shù)的存儲(chǔ)過程,3) 在輸入?yún)?shù)中使用默認(rèn)值 在執(zhí)行有參數(shù)的存儲(chǔ)過程時(shí)，如果沒有指定參數(shù)，則系統(tǒng)運(yùn)行就會(huì)出錯(cuò)；此時(shí)如果希望在執(zhí)行時(shí)不給出參數(shù)也能正確運(yùn)行，則在創(chuàng)建存儲(chǔ)過程時(shí)給輸入?yún)?shù)指定默認(rèn)值。,8.2.2 帶輸入?yún)?shù)的存儲(chǔ)過程,按位置傳遞參數(shù)值 exec pr_searchStuLesson 黃蓉,打狗棒法 通過參數(shù)名傳遞參數(shù)值 exec pr_searchStuLesson sname=黃蓉, lname=打狗棒法 或 exec pr_

16、searchClaLesson lname=打狗棒法, sname=黃蓉,8.2.2 帶輸入?yún)?shù)的存儲(chǔ)過程,例5：對(duì)存儲(chǔ)過程pr_searchStuLesson進(jìn)行修改，指定課程默認(rèn)值為打狗棒法，指定學(xué)生默認(rèn)值為黃蓉 。 ALTER PROC pr_searchStuLesson sname nchar(10)=黃蓉, lname nchar(20)=打狗棒法 AS SELECT Lname, Sname,score FROM T_opt a,T_student b,T_lesson c WHERE a.Sid=b.Sid and b.Lid=c.Lid and b.Sname=sname a

17、nd c.Lname=lname GO EXEC pr_searchStuLesson,8.2 創(chuàng)建管理帶參的存儲(chǔ)過程,8.2.1 創(chuàng)建帶參存儲(chǔ)過程的語(yǔ)法 8.2.2 帶輸入?yún)?shù)的存儲(chǔ)過程 8.2.3 帶輸出參數(shù)的存儲(chǔ)過程 8.2.4 存儲(chǔ)過程的返回值,8.2.3 帶輸出參數(shù)的存儲(chǔ)過程,例6：在student數(shù)據(jù)庫(kù)中創(chuàng)建一個(gè)存儲(chǔ)過程pr_avgScore,輸出某門課程的平均成績(jī)。 CREATE PROC pr_avgScore lname nchar(20) As Select avg(Score) from T_opt a, T_lesson b Where a.Lid=b.Lid and

18、lname=lname 執(zhí)行: declare result decimal(3,1) exec pr_avgScore 九陰真經(jīng), select result as 平均成績(jī), ageScore decimal(3,1) output,ageScore =,result output,8.2.3 帶輸出參數(shù)的存儲(chǔ)過程,例7：創(chuàng)建一個(gè)存儲(chǔ)過程pr_getTopStudent，查詢平均成績(jī)最高的學(xué)生姓名。 CREATE PROC pr_getTopStudent name nchar(10) OUTPUT AS SELECT top 1 sname FROM T_student a join T

19、_opt b on a.Sid=b.Sid group by Sname order by avg(Score) DESC GO,name=,8.2.3 帶輸出參數(shù)的存儲(chǔ)過程,執(zhí)行: DECLARE result nchar(10) EXEC pr_getTopStudent result OUTPUT SELECT 平均成績(jī)最高的學(xué)生是：+ result,8.2 創(chuàng)建管理帶參的存儲(chǔ)過程,8.2.1 創(chuàng)建帶參存儲(chǔ)過程的語(yǔ)法 8.2.2 帶輸入?yún)?shù)的存儲(chǔ)過程 8.2.3 帶輸出參數(shù)的存儲(chǔ)過程 8.2.4 存儲(chǔ)過程的返回值,8.2.4 存儲(chǔ)過程的返回值,存儲(chǔ)過程在執(zhí)行后都會(huì)返回一個(gè)整型值。如果執(zhí)行

20、成功，則返回0；否則返回-1到-99之間的隨機(jī)數(shù)，也可以使用RETURN語(yǔ)句來(lái)指定一個(gè)存儲(chǔ)過程的返回值。,8.2.4 存儲(chǔ)過程的返回值,例8：在student數(shù)據(jù)庫(kù)中創(chuàng)建一個(gè)存儲(chǔ)過程，返回學(xué)生人數(shù)。,create proc pr_numStudent as declare fanhuizhi int select fanhuizhi =count(sid) from T_student return fanhuizhi,create proc pr_numStudent fanhuizhi int output as select fanhuizhi =count(sid) from T_st

21、udent,8.2.4 存儲(chǔ)過程的返回值,接收返回值必須要用變量來(lái)接收 declare jieshouzhi int exec jieshouzhi=pr_ numStudent print jieshouzhi,8.2.4 存儲(chǔ)過程的返回值,接收output的返回值也必須要用變量，如： declare jieshouzhi int exec pr_ numStudent_2 jieshouzhi output print jieshouzhi 注意： 1) return返回的是整數(shù)，output可以返回任何數(shù)據(jù)。 2)如果讓return返回非整數(shù)值，在創(chuàng)建存儲(chǔ)過程時(shí)不會(huì)出錯(cuò)，但是運(yùn)行存儲(chǔ)過程

22、時(shí)將會(huì)出錯(cuò)。,練習(xí)1,在student數(shù)據(jù)庫(kù)中創(chuàng)建一個(gè)帶參數(shù)的存儲(chǔ)過程pr_SelectStu，查詢出學(xué)生表中是否存在XXX(姓名)同學(xué)，如果不存在，則返回值為1，否則查詢?cè)搶W(xué)生的班級(jí)是否XX班（班級(jí)編號(hào)），如是，則返回值為2，否則返回值為0。,練習(xí)1,create proc pr_SelectStu sname nchar(10),sclass nchar(10) as if sname not in (select Sname from T_student) return(1) else if exists (select * from T_student where sname=snam

23、e and Cid=sclass) return(2) else return(0),練習(xí)1,執(zhí)行存儲(chǔ)過程語(yǔ)句如下: Declare fanhui int Exec fanhui= pr_SelectStu 黃蓉,10010911 If fanhui=0 Print 黃蓉是10010911班的學(xué)生 If fanhui=1 Print 沒有黃蓉這個(gè)學(xué)生 If fanhui=2 Print 黃蓉不是10010911班的學(xué)生,練習(xí)2,例：建立一個(gè)帶參的存儲(chǔ)過程，用于向student數(shù)據(jù)庫(kù)中的T_student表中添加一條記錄，在添加記錄前先查看是否存在此學(xué)生的學(xué)號(hào)，如不存在則插入該學(xué)生信息，存儲(chǔ)過程返回值為0，如已存在，則不進(jìn)行插入記錄操作，存儲(chǔ)過程返回值為-1。,練習(xí)2,實(shí)現(xiàn)的T-SQL語(yǔ)句: CREATE PROC pr_InsertStu Id char(10), Name nchar(10), class nchar(10)， group nchar(20),練習(xí)2,AS IF EXISTS (SELECT * FROM