《數(shù)據(jù)庫原理及應用》課件第9章

第9章存儲過程9.1存儲過程概述9.2創(chuàng)建和執(zhí)行存儲過程9.3存儲過程的返回值9.4管理存儲過程9.5系統(tǒng)存儲過程和擴展存儲過程9.6存儲過程的應用

【技能目標】

理解存儲過程的作用；學會創(chuàng)建和管理存儲過程；學會根據(jù)實際需要設計存儲過程。

【知識目標】

理解存儲過程的概念和作用；了解存儲過程的分類；掌握創(chuàng)建存儲過程的方法；掌握執(zhí)行存儲過程的方法；掌握在存儲過程中定義和使用輸入、輸出參數(shù)的方法；掌握存儲過程返回值的用法；掌握查看、修改和刪除存儲過程的方法和步驟；了解系統(tǒng)存儲過程和擴展存儲過程；掌握存儲過程的應用。

9.1.1存儲過程的概念

存儲過程(StoredProcedure)是獨立存在于表之外的數(shù)據(jù)庫對象。它是一組完成特定功能的SQL語句集，經編譯后存儲在數(shù)據(jù)庫中。存儲過程可由應用程序通過一個調用來執(zhí)行。同時，它可以接收和輸出參數(shù)，返回執(zhí)行存儲過程的狀態(tài)值，也可以嵌套調用。9.1存儲過程概述

SQLServer中的存儲過程與其他編程語言中的過程類似，原因有以下幾點。

(1)存儲過程可以接受輸入?yún)?shù)并以輸出參數(shù)的形式將多個值返回至調用過程或批

處理。

(2)存儲過程可以包含執(zhí)行數(shù)據(jù)庫操作(包括調用其他過程)的編程語句。

(3)存儲過程可以向調用過程或批處理返回狀態(tài)值，以表明成功或失敗(以及失敗原因)。

(4)存儲過程可以使用EXECUTE語句調用執(zhí)行。

9.1.2存儲過程的優(yōu)點

利用SQLServer2005創(chuàng)建一個應用程序時，可以選用兩種方法：一是在本地存儲Transact-SQL程序，創(chuàng)建應用程序向SQLServer服務器發(fā)送命令來對結果進行處理；二是使用存儲過程，創(chuàng)建應用程序來調用存儲過程執(zhí)行。通常第二種方法更好一些，即在SQLServer中使用存儲過程而不是在客戶計算機上調用Transact-SQL編寫的一段程序。存儲過程具有以下優(yōu)點：

(1)允許標準組件式編程。存儲過程創(chuàng)建好后，就可以在程序中被多次調用，而不必重新編寫該存儲過程的Transact-SQL語句。而且數(shù)據(jù)庫專業(yè)人員可隨時對存儲過程進行修改，且對應用程序源代碼毫無影響，因為應用程序源代碼只包含存儲過程的調用語句，從而極大地提高了程序的可移植性。

(2)執(zhí)行速度快。存儲過程在創(chuàng)建的時候就被編譯和優(yōu)化了。程序調用一次存儲過程后，相關信息就保存在內存中，下次調用時可以直接執(zhí)行，因而執(zhí)行速度快。

(3)能夠有效降低網絡流量。使用存儲過程可將調用數(shù)百行Transact-SQL語句的操作通過一條執(zhí)行存儲過程的語句來完成，而不需要在網絡中發(fā)送數(shù)百行代碼，因此有效地降低了網絡流量。

(4)提高數(shù)據(jù)庫的安全性。數(shù)據(jù)庫管理員通過對執(zhí)行某一存儲過程的權限進行限制，能夠實現(xiàn)對相應數(shù)據(jù)訪問權限的限制，避免非授權用戶對數(shù)據(jù)的訪問，保證數(shù)據(jù)的安全。

9.1.3存儲過程的種類

SQLServer2005支持系統(tǒng)存儲過程、用戶自定義存儲過程、擴展存儲過程、臨時存儲過程、遠程存儲過程和CLR過程。

1)系統(tǒng)存儲過程

系統(tǒng)存儲過程是由系統(tǒng)創(chuàng)建的，它存儲在master數(shù)據(jù)庫中，并以sp_為前綴。系統(tǒng)存儲過程主要從系統(tǒng)表中獲取信息，從而為系統(tǒng)管理員管理SQLServer提供支持。通過系統(tǒng)存儲過程，SQLServer中的許多管理性或信息性的活動(如了解數(shù)據(jù)庫對象、數(shù)據(jù)庫信息)都可以被順利有效地完成。

2)用戶自定義存儲過程

用戶自定義存儲過程是在用戶數(shù)據(jù)庫中創(chuàng)建，完成特定數(shù)據(jù)庫任務的存儲過程。一般所說的存儲過程就是指用戶自定義存儲過程。強烈建議用戶自定義存儲過程命名時不要以sp_作為前綴，因為SQLServer使用該前綴來標識系統(tǒng)存儲過程。

3)擴展存儲過程

擴展存儲過程是在SQLServer環(huán)境之外執(zhí)行的動態(tài)鏈接庫DLL。它們在SQLServer的早期版本中用于擴展產品的功能。SQLServer2005支持擴展存儲過程只是為了兼容，在以后的SQLServer版本中將不再支持該功能。

4)臨時存儲過程

臨時存儲過程是一種特殊的本地存儲過程。如果在本地存儲過程名稱前面有一個“#”號，這種存儲過程稱為局部臨時存儲過程，它只能在一個用戶會話中使用；如果在本地存儲過程名稱前面有兩個“#”號，那么該存儲過程稱為全局臨時存儲過程，它可以在所有用戶會話中使用。

5)遠程存儲過程

遠程存儲過程指遠程服務器上的存儲過程，也就是非本地服務器上的存儲過程。

6)

CLR過程

CLR過程是SQLServer2005新增的存儲過程，可以使用某一種

.NET語言創(chuàng)建。CLR存儲過程將替換現(xiàn)存的擴展存儲過程。

9.2.1創(chuàng)建存儲過程

1.重名的存儲過程檢測

創(chuàng)建存儲過程前要確保沒有重名的存儲過程。具體操作方法是：檢查系統(tǒng)表sysobjects，若在系統(tǒng)表中有要創(chuàng)建的存儲過程名，并且類型為“P”，說明該對象為存儲過程，則應將已經存在的存儲過程刪除。創(chuàng)建存儲過程之前可執(zhí)行以下語句：

9.2創(chuàng)建和執(zhí)行存儲過程

USE操作數(shù)據(jù)庫

IFEXISTS(SELECTnameFROMsysobjectsWHEREname='要創(chuàng)建的存儲過程名'ANDtype='P')

DROPPROCEDURE要創(chuàng)建的存儲過程名

2.創(chuàng)建存儲過程語法

在SQLServer2005中使用CREATEPROCEDURE語句創(chuàng)建存儲過程。

創(chuàng)建存儲過程的語法格式如下：

CREATEPROC[EDURE]procedure_name[;number]

[{@parameterdata_type}[VARYING][=default][OUTPUT]][,...n]

[WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]

AS

sql_statement參數(shù)說明如下：

●

procedure_name：存儲過程的名稱。

●

number：可選的整數(shù)，用來對同名的過程分組，以便用一條DROPPROCEDURE語句將同組的過程一起刪除。

●

@parameter：過程中的參數(shù)。可以聲明一個或多個參數(shù)，多個參數(shù)之間用逗號隔開。

●

data_type：參數(shù)的數(shù)據(jù)類型。

●

VARYING：指定作為輸出參數(shù)支持的結果集。僅適用于游標參數(shù)。

●

default：參數(shù)的默認值。如果定義了默認值，則不必指定該參數(shù)的值即可執(zhí)行過程。默認值必須是常量或NULL。

●

OUTPUT：參數(shù)是輸出參數(shù)。該選項的值可以返回給EXEC[UTE]。

●

RECOMPILE|ENCRYPTION|RECOMPILE，ENCRYPTION：RECOMPILE表明該過程將在運行時重新編譯；ENCRYPTION表示對創(chuàng)建的存儲過程加密。

●

AS：指定過程要執(zhí)行的操作。

●

sql_statement：過程中要包含的任意數(shù)目和類型的Transact-SQL語句。

說明要創(chuàng)建局部臨時過程，可以在procedure_name前面加一個編號符(#procedure_name)；要創(chuàng)建全局臨時過程，可以在procedure_name前面加兩個編號符(##procedure_name)。完整的名稱(包括#或##)不能超過128個字符。

【例9-1】在SCMS數(shù)據(jù)庫中創(chuàng)建一個名為p_stu的存儲過程，該存儲過程返回所有的性別為“男”的學生信息。

CREATEPROCp_stu

AS

SELECT*

FROMstudent

WHEREssex='男'

執(zhí)行以上語句，p_stu存儲過程即可創(chuàng)建。存儲過程一旦創(chuàng)建好了，可以在對象資源管理器窗口查看該存儲過程的屬性。具體操作步驟是：在“對象資源管理器”窗口中展開“數(shù)據(jù)庫|SCMS|可編程性|存儲過程”節(jié)點，可以看到當前數(shù)據(jù)庫的全部存儲過程，右擊選中p_stu存儲過程，在彈出的快捷菜單中，選擇“屬性”選項，打開“存儲過程屬性”對話框，如圖9-1所示。可以查看存儲過程相關信息，包括創(chuàng)建者、創(chuàng)建時間、是否加密等信息。

圖9-1“存儲過程屬性”對話框9.2.2執(zhí)行存儲過程

使用EXECUTE(也可以寫為EXEC)語句調用執(zhí)行存儲過程。其語法格式如下：

EXECprocedure_name[@parameter=]{value|@variable[OUTPUT]|[DEFAULT]}[,…n]

參數(shù)說明如下：

●

@parameter：表示該存儲過程定義的參數(shù)。

●

value：提供給參數(shù)的值。如果參數(shù)名稱沒有指定，參數(shù)值必須以在存儲過程中定義的順序提供。

●

@variable：用來存儲參數(shù)或返回參數(shù)的變量。

●

OUTPUT：指定存儲過程返回一個參數(shù)。該存儲過程的匹配參數(shù)也必須使用關鍵字OUTPUT創(chuàng)建。

●

DEFAULT：參數(shù)有默認值。

【例9-2】執(zhí)行例9-1創(chuàng)建的存儲過程。

EXECp_stu

執(zhí)行結果如圖9-2所示。

圖9-2執(zhí)行p_stu存儲過程結果9.2.3存儲過程的參數(shù)

存儲過程提供的參數(shù)，為存儲過程的應用帶來了許多靈活性。給存儲過程設定參數(shù)的主要目的是通過參數(shù)向存儲過程輸入和輸出信息來擴展存儲過程的功能。存儲過程的參數(shù)分為輸入?yún)?shù)和輸出參數(shù)。

1.帶輸入?yún)?shù)的存儲過程

輸入?yún)?shù)是指由調用程序向存儲過程傳遞的參數(shù)。創(chuàng)建存儲過程時需要定義輸入?yún)?shù)的名稱和類型，執(zhí)行存儲過程時需要給出相應的參數(shù)值。

輸入?yún)?shù)的語法格式如下：

@parameterdata_type[=default]

【例9-3】創(chuàng)建一個存儲過程p_stuNew，該過程根據(jù)給定的性別返回對應的student表中的記錄。

分析：在例9-1中，執(zhí)行存儲過程p_stu，只能返回男生的相關信息，而在存儲過程p_stuNew中，根據(jù)要求，有一個輸入?yún)?shù)，用來向存儲過程傳入指定的性別，實現(xiàn)按指定性別進行信息查詢。

CREATEPROCp_stuNew

@ssexchar(2)

AS

SELECT*

FROMstudent

WHEREssex=@ssex

【例9-4】創(chuàng)建一個存儲過程p_studentPara，該過程根據(jù)給定的班級號返回對應的student表中的記錄。

分析：根據(jù)要求，存儲過程p_studentPara需要一個輸入?yún)?shù)，用來向存儲過程傳入指定的班級號。

CREATEPROCp_studentPara

@classnovarchar(8)

AS

SELECT*

FROMstudent

WHEREclassno=@classno

【例9-5】創(chuàng)建一個存儲過程p_studentParaNew，該存儲過程可以根據(jù)給定的班級號和學生姓名返回對應的student表中的記錄。

分析：根據(jù)要求，存儲過程p_studentParaNew需要兩個輸入?yún)?shù)，分別用來向存儲過程傳入用戶指定的班級號和姓名。

CREATEPROCp_studentParaNew

@classnochar(8),@snamechar(10)

AS

SELECT*

FROMstudent

WHEREclassno=@classnoANDsnameLIKE@sname

2.使用默認值參數(shù)

創(chuàng)建存儲過程時，可以為輸入?yún)?shù)提供一個默認值，默認值必須為常量或NULL。

【例9-6】創(chuàng)建一個存儲過程p_studentDefault，該過程根據(jù)給定的性別和班級號返回對應的student表中的記錄，其中班級號默認值為09011011。

分析：對于使用帶默認值參數(shù)的存儲過程，在執(zhí)行存儲過程時，若用戶為參數(shù)指定值，就將指定值傳給存儲過程，若未指定值，則用默認值代替。在該例中，若用戶執(zhí)行時未指定班級號，則默認查詢09011011班的學生信息。

CREATEPROCp_studentDefault

@ssexvarchar(2),@classnovarchar(8)='09011011'

AS

SELECT*

FROMstudent

WHEREssex=@ssexANDclassno=@classno

3.執(zhí)行帶參數(shù)的存儲過程

執(zhí)行帶參數(shù)的存儲過程時，需要向存儲過程傳遞參數(shù)。傳遞參數(shù)有兩種方法：一種是順序法，另一種是提示法。

1)順序法

順序法是在執(zhí)行存儲過程時按照輸入?yún)?shù)的順序直接給出參數(shù)的傳遞值。傳遞參數(shù)時，值的順序必須與創(chuàng)建存儲過程語句中定義的參數(shù)的順序一致。對于使用默認值的參數(shù)可以用DEFAULT代替。

【例9-7】執(zhí)行存儲過程

p_stuNew，查詢所有女生的信息。

EXECp_stunew'女'

【例9-8】執(zhí)行存儲過程p_studentParaNew，返回09011011班李銳同學的信息。

EXECp_studentParaNew'09011011','李銳'

【例9-9】執(zhí)行存儲過程p_studentDefault，返回09011011班所有男生的信息。

EXECp_studentDefault'男','09011011'

或者

EXECp_studentDefault'男',DEFAULT

2)提示法

提示法是在執(zhí)行存儲過程時傳遞參數(shù)采用如“@classno='09011011'”的形式。使用提示法傳遞參數(shù)時，各個參數(shù)的順序可以任意排列。

【例9-10】執(zhí)行存儲過程p_studentParaNew，返回09011011班李銳同學的信息。

EXECp_studentParaNEW@classno='09011011',@sname='李銳'

或者

EXECp_studentParaNEW@sname='李銳',@classno='09011011'

4.帶輸出參數(shù)的存儲過程

在創(chuàng)建存儲過程時可以定義輸出參數(shù)。執(zhí)行存儲過程時，可以將結果返回給輸出參數(shù)。使用輸出參數(shù)類似于使用返回值。但是輸出參數(shù)具有兩個重要的優(yōu)勢：一是可以使用輸出參數(shù)從存儲過程傳遞出varchar、int、money或任何其他數(shù)據(jù)類型的值，而返回值則只能返回整數(shù)值；二是在一個存儲過程中可以有多個輸出參數(shù)，一個存儲過程可以包含1024個參數(shù)(包括輸入和輸出參數(shù))，但是只能包含一個返回值。輸出參數(shù)的語法格式如下：

@parameterdata_type[=default]OUTPUT

【例9-11】創(chuàng)建一個存儲過程，該過程可以根據(jù)給定的班級號返回相應班級的人數(shù)。

分析：根據(jù)要求，存儲過程執(zhí)行結束后需返回班級人數(shù)，在程序中可以用返回的人數(shù)參與其他語句的處理。不能在存儲過程中通過print語句輸出人數(shù)值，而應通過輸出參數(shù)將計算出來的人數(shù)值返回。試比較下面兩個存儲過程的不同。

(1)帶輸出參數(shù)的存儲過程：

CREATEPROCp_classNum

@classnochar(8),@classnumintOUTPUT

AS

SET@classnum=

(SELECTcount(*)

FROMstudent

WHEREclassno=@classno)

(2)不帶輸出參數(shù)的存儲過程：

CREATEPROCp_classNumNew

@classnochar(8)

AS

DECLARE@classnumint

SELECT@classnum=count(*)

FROMstudent

WHEREclassno=@classno

PRINT@classno+'班級的人數(shù)為：'+CAST(@classnumASvarchar(2))

執(zhí)行帶輸出參數(shù)的存儲過程如下：

DECLARE@classnochar(8),@classnumint

SET@classno='09031012'

EXECp_classNum@classno,@classnumOUTPUT

IF(@classnum>0)

PRINT@classno+'班級的人數(shù)為：'+CAST(@classnumASvarchar(2))

ELSE

PRINT@classno+'班級的人數(shù)為0。'

執(zhí)行結果如圖9-3所示。圖9-3執(zhí)行p_classNum存儲過程執(zhí)行不帶輸出參數(shù)的存儲過程如下：

EXECp_classNumNew'09031012'

執(zhí)行結果如圖9-4所示。圖9-4執(zhí)行p_classNumNew存儲過程

分析：雖然兩個存儲過程的執(zhí)行得到了相同的結果，但兩個存儲過程有區(qū)別。其區(qū)別在于：帶輸出參數(shù)的存儲過程中的輸出參數(shù)可以參與到Transact-SQL流程語句中，即可以將存儲過程中執(zhí)行的結果參與其他流程語句處理；不帶輸出參數(shù)的存儲過程只能在存儲過程內部使用存儲過程執(zhí)行的結果，一旦存儲過程結束，該值將不能參與其他處理。

5.使用WITHRECOMPILE選項

如果為過程提供的參數(shù)不是典型的參數(shù)，并且新的執(zhí)行計劃不應高速緩存或存儲在內存中，則可以使用WITHRECOMPILE選項。

【例9-12】創(chuàng)建一個存儲過程，該過程可以根據(jù)給定的學號返回學生的選課信息。指定該存儲過程在運行時進行編譯。

CREATEPROCp_snoScore

@snochar(12)

WITHRECOMPILE

AS

SELECTcno,result

FROMsc

WHEREsno=@sno

執(zhí)行該存儲過程如下：

EXECp_snoScore@sno='200903101102'

該存儲過程每次執(zhí)行時，系統(tǒng)都要對存儲過程語句進行編譯，然后再執(zhí)行。

6.使用WITHENCRYPTION選項

WITHENCRYPTION選項指示SQLServer將CREATEPROCEDURE語句的原始文本轉換為模糊格式。WITHENCRYPTION選項對用戶隱藏存儲過程的文本。

【例9-13】創(chuàng)建一個存儲過程，該過程可以根據(jù)給定的學號返回學生的基本信息。指定該存儲過程只能使用，不能查看。

CREATEPROCp_stuInformation

@snochar(12)

WITHENCRYPTION

AS

SELECTstudent.sno,sname,cno,result

FROMstudentINNERJOINscONstudent.sno=sc.sno

WHEREstudent.sno=@sno

該存儲過程創(chuàng)建后，用戶可以執(zhí)行存儲過程，但不能通過系統(tǒng)存儲過程sp_helptext查看存儲過程定義文本。

說明創(chuàng)建存儲過程時，系統(tǒng)僅僅檢查存儲過程的定義語句是否滿足語法規(guī)則，并不檢查存儲過程中涉及的對象是否存在。只有當存儲過程執(zhí)行時，才會去檢查數(shù)據(jù)對象是否存在。

9.3.1使用RETURN語句傳回值

存儲過程可以返回一個整數(shù)值(稱為“返回代碼”)指示過程的執(zhí)行狀態(tài)，可使用RETURN語句指定存儲過程的返回代碼。若成功執(zhí)行，則返回0，否則返回-1～99之間的數(shù)值。也可以通過使用RETURN語句返回某個指定的值，在調用程序時使用返回代碼值。

9.3存儲過程的返回值

RETURN語句的語法格式如下：

RETURN[integer_expression]

其中integer_expression是返回的整數(shù)值。

1.使用RETURN語句

在存儲過程中使用RETURN語句，程序將停止執(zhí)行，回到調用程序中的下一條語句，RETURN可回傳整數(shù)值。

【例9-14】創(chuàng)建一個存儲過程，該過程可以根據(jù)性別顯示學生信息。

CREATEPROCp_stusex

@sexvarchar(10)

AS

IF@sex='男'OR@sex='女'

BEGIN

SELECTsno,sname

FROMstudent

WHEREssex=@sex

RETURN

END

ELSE

BEGIN

PRINT'請輸入正確的性別作為存儲過程的參數(shù)??！'

END

執(zhí)行存儲過程，給定正確的參數(shù)值。

EXECp_stusex@sex='男'

執(zhí)行結果如圖9-5所示。圖9-5執(zhí)行p_stusex存儲過程

2.在調用程序中使用RETURN傳回值

可以使用RETURN傳回值到調用程序，傳回值可以是常量或變量，但必須為整數(shù)。變量必須聲明后才可以在調用程序中使用。

【例9-15】創(chuàng)建一個存儲過程，如果學生數(shù)據(jù)庫原理及應用課程的成績在90分以上，則返回1，否則返回0。

CREATEPROCp_databaseScore

@snochar(12)

AS

IF(SELECTresult

FROMstudentJOINscONstudent.sno=sc.snoJOINcourseONo=o

WHEREcname='數(shù)據(jù)庫原理及應用'ANDstudent.sno=@sno)<=90

RETURN0

ELSE

RETURN1執(zhí)行該存儲過程前，應該在程序中聲明變量并將存儲過程的傳回值賦予該變量。

DECLARE@iint

EXEC@i=p_databaseScore'200903101102'

IF(@i=1)

PRINT'恭喜，數(shù)據(jù)庫原理及應用成績優(yōu)秀！'

ELSE

PRINT'請繼續(xù)努力!'

執(zhí)行結果如圖9-6所示。圖9-6使用RETURN返回值9.3.2使用SELECT語句傳回值

在存儲過程中可以使用SELECT語句傳回值。

【例9-16】創(chuàng)建功能如例9-15的存儲過程，使用SELECT語句傳回值。

CREATEPROCp_databaseScoreNew@snochar(10)

AS

DECLARE@bianliangvarchar(50)

IF(SELECTresult

FROMstudentJOINscONstudent.sno=sc.snoJOINcourseONo=o

WHEREcname='數(shù)據(jù)庫原理及應用'ANDstudent.sno=@sno)<90

SET@bianliang='成績低于90，請繼續(xù)努力！'

ELSE

SET@bianliang='恭喜，數(shù)據(jù)庫原理及應用成績優(yōu)秀！'

SELECT'成績是否優(yōu)秀'=@bianliang執(zhí)行該存儲過程：

EXECp_databaseScoreNew'200903101101'

執(zhí)行結果如圖9-7所示。圖9-7使用SELECT返回值

說明使用SELECT傳回值和使用RETURN傳回值的區(qū)別是：調用RETURN語句傳回值時，存儲過程執(zhí)行結束；使用SELECT語句傳回值時，存儲過程不結束，繼續(xù)執(zhí)行SELECT后面的語句。

9.4.1查看存儲過程

存儲過程被創(chuàng)建后，它的名字會存儲在系統(tǒng)表sysobjects中，源代碼會存放在系統(tǒng)表syscomments中?？梢酝ㄟ^SQLServer提供的系統(tǒng)存儲過程查看創(chuàng)建的存儲過程的信息。

1.使用sp_help查看存儲過程的信息

使用sp_help查看存儲過程的信息的語法格式如下：9.4管理存儲過程

sp_helpprocedure_name

【例9-17】查看用戶定義存儲過程p_stusex的參數(shù)及數(shù)據(jù)類型。

EXECsp_helpp_stusex

執(zhí)行結果如圖9-8所示。

圖9-8執(zhí)行sp_help存儲過程

2.使用sp_helptext查看存儲過程的定義代碼

使用sp_helptext查看存儲過程的定義代碼語法格式如下：

sp_helptextprocedure_name

存儲過程必須在當前數(shù)據(jù)庫中。

【例9-18】查看用戶定義存儲過程p_snoScore的定義代碼。

EXECsp_helptextp_snoScore

執(zhí)行結果如圖9-9所示。圖9-9執(zhí)行sp_helptext查看未加密存儲過程

說明如果定義存儲過程中使用了WITHENCRYPTION選項，則無法用sp_helptext查看存儲過程定義語句。

【例9-19】查看用戶定義存儲過程p_stuInformation的定義代碼。

EXECsp_helptextp_stuInformation

執(zhí)行結果如圖9-10所示。

圖9-10執(zhí)行sp_helptext查看加密存儲過程

3.使用sp_depends顯示有關數(shù)據(jù)庫對象相關性的信息

使用sp_depends顯示有關數(shù)據(jù)庫對象相關性的信息的語法格式如下：

sp_dependsprocedure_name

【例9-20】查看用戶定義存儲過程p_snoScore的依賴關系。

EXECsp_dependsp_snoScore

執(zhí)行結果如圖9-11所示。圖9-11執(zhí)行sp_depends查看存儲過程9.4.2修改存儲過程

如果需要更改存儲過程中的語句或參數(shù)，可以先刪除存儲過程，再重新創(chuàng)建該存儲過程，也可以使用SQLServerManagementStudio或ALTERPROCEDURE語句修改存儲過程。修改存儲過程時，過程或參數(shù)定義會更改，但為該存儲過程定義的權限將保留。

1.使用SQLServerManagementStudio修改存儲過程

【例9-21】存儲過程p_studentPara可以根據(jù)給定的班級號返回對應的student表中的記錄。修改該存儲過程，要求為班級號提供一個默認值09031021。

具體操作步驟如下：

(1)在“對象資源管理器”窗口中展開“數(shù)據(jù)庫|SCMS|可編程性|存儲過程”節(jié)點。

(2)選擇p_studentPara存儲過程，單擊右鍵，在彈出的快捷菜單中選擇“修改”選項。

(3)在打開的查詢編輯器窗口中對代碼做修改，如圖9-12所示。圖9-12修改存儲過程p_studentPara

2.使用ALTERPROCEDURE語句修改存儲過程

使用ALTERPROCEDURE語句修改存儲過程的語法格式如下：

ALTERPROC[EDURE]procedure_name[;number]

[{@parameterdata_type}[VARYING][=default][OUTPUT]][,...n]

[WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]

[FORREPLICATION]

ASsql_statement

其參數(shù)同創(chuàng)建存儲過程。

【例9-22】用ALTERPROC語句修改p_studentPara存儲過程。

ALTERPROCp_studentPara

@classnovarchar(8)='09031021'--為班級號添加默認值

AS

SELECT*

FROMstudent

WHEREclassno=@classno9.4.3刪除存儲過程

如果不再需要使用一個存儲過程，可以把它從數(shù)據(jù)庫中刪除?？梢允褂肧QLServerManagementStudio或DROPPROCEDURE語句刪除存儲過程。

1.使用SQLServerManagementStudio刪除存儲過程

【例9-23】刪除存儲過程p_studentPara。

具體操作步驟如下：

(1)在“對象資源管理器”窗口中展開“數(shù)據(jù)庫|SCMS|可編程性|存儲過程”節(jié)點。

(2)選擇p_studentPara存儲過程，單擊右鍵，在彈出的快捷菜單中選擇“刪除”選項。

(3)在打開的“刪除對象”對話框中選擇“確定”，即可刪除p_studentPara存儲過程。

2.使用DROPPROCEDURE語句刪除存儲過程

使用DROPPROCEDURE語句刪除存儲過程的語法格式如下：

DROP{PROC|PROCEDURE}procedure_name[,...n]

【例9-24】刪除存儲過程p_studentPara。

DROPPROCp_studentPara

9.4.4重命名存儲過程

可使用SQLServerManagementStudio或調用系統(tǒng)存儲過程sp_rename對存儲過程重命名。

1.使用SQLServerManagementStudio重命名存儲過程

使用SQLServerManagementStudio重命名存儲過程的主要操作步驟如下：

(1)在“對象資源管理器”窗口中展開“數(shù)據(jù)庫|SCMS|可編程性|存儲過程”節(jié)點。

(2)選擇要重命名的存儲過程，單擊右鍵，在彈出的快捷菜單中選擇“重命名”即可。

2.調用系統(tǒng)存儲過程sp_rename重命名存儲過程

調用系統(tǒng)存儲過程sp_rename重命名存儲過程的語法格式如下：

sp_rename'object_name','new_name'

【例9-25】將存儲過程p_snoScore重新命名為p_學生成績。

EXEXsp_renamep_snoscore,p_學生成績

執(zhí)行該命令后系統(tǒng)將進行存儲過程更名操作。

說明在查詢結果顯示窗口將顯示下列信息：“警告：更改對象名的任一部分都可能會破壞腳本和存儲過程”。系統(tǒng)在這里告訴用戶，對數(shù)據(jù)庫對象的更名操作有可能會影響其他數(shù)據(jù)庫對象的操作。例如有存儲過程p1，如果使用了剛才重命名的存儲過程，就必須修改p1中使用p_snoScore的語句，換為新的名字“p_學生成績”，否則p1在執(zhí)行時就會出錯。

9.5.1系統(tǒng)存儲過程

在SQLServer2005中，許多管理和信息活動可以通過系統(tǒng)存儲過程執(zhí)行。系統(tǒng)存儲過程就是系統(tǒng)創(chuàng)建的存儲過程，其目的在于能夠方便地從系統(tǒng)表中查詢信息或完成與更新數(shù)據(jù)庫表相關的管理任務或其他的系統(tǒng)管理任務。系統(tǒng)存儲過程以“sp_”開頭，在master數(shù)據(jù)庫中創(chuàng)建并保存在該數(shù)據(jù)庫中，為數(shù)據(jù)庫管理者所有。9.5系統(tǒng)存儲過程和擴展存儲過程一些系統(tǒng)存儲過程只能由系統(tǒng)管理員使用，而有些系統(tǒng)存儲過程通過授權可以被其他用戶所使用。SQLServer系統(tǒng)存儲過程數(shù)量眾多，可以幫助用戶查看或管理數(shù)據(jù)庫。下面對一些常用系統(tǒng)存儲過程進行簡單介紹。

1.查看系統(tǒng)對象

●

sp_helpserver：查看所有數(shù)據(jù)庫服務器信息，如：名稱、狀態(tài)。

●

sp_helpdb：查看所有數(shù)據(jù)庫信息，如：名稱、大小。

●

sp_helplogins：查看所有數(shù)據(jù)庫用戶登錄信息，如：登錄用戶名稱、ID編號。

●

sp_helpindex：查看當前數(shù)據(jù)庫下某個數(shù)據(jù)對象的索引信息，如：索引名稱、類型。

●

sp_validname：檢查SQLServer標識符名稱的有效性。

2.數(shù)據(jù)庫維護類

●

sp_rename：更改數(shù)據(jù)庫對象名稱。

●

sp_renamedb：更改數(shù)據(jù)庫名稱。

●

sp_changedbowner：更改數(shù)據(jù)庫對象的所有者。

●

sp_recompile：若調整了表結構或索引，存儲過程和觸發(fā)器在下次運行時重新編譯。

●

sp_bindrule：將規(guī)則綁定到列或用戶自定義數(shù)據(jù)類型。

說明調用以“sp_”打頭的存儲過程時，SQLServer默認先去master數(shù)據(jù)庫中查找，如果不存在，再到當前數(shù)據(jù)庫中去找。這就是為什么當前庫中沒有以上過程，卻仍然可以直接運行的原因。9.5.2擴展存儲過程

擴展存儲過程是以C語言等編程語言編寫的外部程序，以動態(tài)鏈接庫(DLL)形式存儲在服務器上，SQLServer可以動態(tài)地裝載并執(zhí)行它們。編寫好擴展存儲過程后，固定服務器角色(sysadamin)成員即可在SQLServer服務器上注冊該擴展存儲過程，并將它們的執(zhí)行權限授權給其他用戶。擴展存儲過程與普通存儲過程一樣，執(zhí)行方法也相同?？蓪?shù)傳遞給擴展存儲過程，擴展存儲過程可返回結果，也可返回狀態(tài)。擴展存儲過程常以“xp_”打頭。常用擴展存儲過程如下：

●

xp_cmdshell：用來運行平常從命令提示符下執(zhí)行的程序，例如，MD(創(chuàng)建文件夾)和DIR(顯示文件夾中文件列表)命令等。

●

xp_sscanf：將數(shù)據(jù)從字符串讀入時每個格式參數(shù)所指定的參數(shù)位置。

●

xp_sprintf：設置一系列字符和值的格式并將其存儲到字符串輸出參數(shù)中。每個格式參數(shù)都用相應的參數(shù)替換。

【例9-26】返回當前目錄下的java.exe文件名及其屬性。

分析：可以使用擴展存儲過程xp_cmdshell，其調用語法格式如下：

xp_cmdshell{'command_string'}[,no_output]

參數(shù)說明如下：

●

command_string：在操作系統(tǒng)命令行解釋器上執(zhí)行的命令字符串。

●

no_output：可選參數(shù)，表示不向客戶端返回任何輸出。

在查詢編輯器窗口中執(zhí)行如下語句：

EXECxp_cmdshell'dirjava.exe'

執(zhí)行結果如圖9-13所示。不同機器上演示結果不同。圖9-13顯示當前目錄下的指定文件

【例9-27】在D盤上建立文件夾，文件夾名為“在SQL中創(chuàng)建的文件夾”。

在查詢編輯器窗口中執(zhí)行如下語句：

EXECxp_cmdshell'mdd:\在SQL中創(chuàng)建的文件夾',NO_OUTPUT

執(zhí)行該語句后在D盤出現(xiàn)一個名為“在SQL中創(chuàng)建的文件夾”的文件夾。

擴展存儲過程默認在SQLServer2005中是被關閉的。啟用xp_cmdshell可以通過配置外圍應用配置器來啟動。配置外圍應用配置器的操作步驟如下：

(1)打開“開始”菜單，選擇“程序|MicrosoftSQLServer2005|配置工具|SQLServer2005外圍應用配置器”命令。

(2)打開“SQLServer2005外圍應用配置器”對話框，如圖9-14所示。選擇“功能的外圍應用配置器”選項。

(3)打開“功能的外圍應用配置器”對話框，如圖9-15所示。展開“MSSQLSERVER|DatabaseEngine”節(jié)點，選擇xp_cmdshell選項，選中“啟用xp_cmdshell”復選框，單擊“應用”按鈕。

圖9-14SQLServer2005外圍應用配置器

圖9-15啟動xp_cmdshell擴展存儲過程9.5.3CLR集成

MicrosoftSQLServer的后續(xù)版本將刪除擴展存儲過程功能，而改用CLR過程。通過宿主Microsoft.NETFramework2.0公共語言運行庫(CLR)，SQLServer2005顯著地增強了數(shù)據(jù)庫編程模型的功能。它支持用任何CLR語言(特別是MicrosoftVisualC#.NET、MicrosoftVisualBasic.NET和MicrosoftVisualC++)編寫過程、觸發(fā)器和函數(shù)。同時，它還提供了擴展數(shù)據(jù)庫系統(tǒng)中的類型系統(tǒng)和聚合函數(shù)的功能，方法是允許應用程序創(chuàng)建它們自己的數(shù)據(jù)類型和聚合函數(shù)。在SQLServer2005中可使用通用語言運行庫(CLR)過程，這意味著在SQLServer實例中提供對于.NETFramework編程模型的訪問是有必要的。為此，在SQLServer2005中引入了程序集的概念。程序集是

.NET編譯和托管的DLL文件。SQLServer使用程序集部署對象，例如存儲過程、用戶定義類型、觸發(fā)器和用戶定義函數(shù)。這些對象通常使用Transact-SQL編寫，但是如今使用多種受管語言代碼(例如VB.NET或者C#)也能夠創(chuàng)建和編寫這些對象。SQLServer2005的這種新功能還提供了在數(shù)據(jù)庫對象(例如存儲過程、函數(shù)和類型)中，訪問經過改進的

.NETFramework編程模型的能力。.NETFramework2.0版本具有多方面的增強功能，這些經過改進的功能有很多已經可以為CLR集成使用。

默認情況下，SQLServer2005禁用CLR集成功能，必須啟用CLR集成才能在SQLServer中訪問

.NET對象。啟用CLR集成的方法有兩種，即通過SQLServer外圍應用配置器工具或執(zhí)行系統(tǒng)存儲過程sp_configure。

用戶可以在任何使用Transact-SQL語句的地方使用存儲過程。在學生成績管理系統(tǒng)數(shù)據(jù)庫SCMS中大量采用了存儲過程來完成數(shù)據(jù)庫的日常管理功能，包括數(shù)據(jù)庫中對數(shù)據(jù)表的操作和數(shù)據(jù)庫安全管理。9.6存儲過程的應用9.6.1數(shù)據(jù)表的操作

在數(shù)據(jù)庫SCMS中，可以使用存儲過程對數(shù)據(jù)表進行插入、修改和刪除等數(shù)據(jù)操作。

【例9-28】創(chuàng)建一個存儲過程，實現(xiàn)對student表的信息的添加。

分析：在創(chuàng)建實現(xiàn)插入語句的存儲過程時，可以選擇帶輸入?yún)?shù)或者不帶輸入?yún)?shù)的存儲過程。在該例中我們將帶輸入?yún)?shù)的和不帶輸入?yún)?shù)的存儲過程都寫出來，目的是通過比較表明帶參數(shù)的存儲過程在實際使用時更加靈活和有效。在存儲過程中使用了SELECT語句，目的是在執(zhí)行存儲過程后將插入表中的數(shù)據(jù)顯示出來。

(1)創(chuàng)建不帶輸入?yún)?shù)的存儲過程p_insert1。

CREATEPROCp_insert1

AS

BEGIN

INSERTINTOstudent(sno,sname,ssex,classno)

VALUES('200903101123','吳剛','男','09031011')

SELECTsno,sname,ssex,classno

FROMstudent

END

執(zhí)行該存儲過程：

EXECp_insert1

運行結果如圖9-16所示。圖9-16執(zhí)行p_insert1存儲過程

(2)創(chuàng)建帶輸入?yún)?shù)的存儲過程p_insert2。

CREATEPROCp_insert2

@snochar(12),@snamechar(10),@ssexchar(2),@classnochar(8)

AS

BEGIN

INSERTINTOstudent(sno,sname,ssex,classno)

VALUES(@sno,@sname,@ssex,@classno)

SELECTsno,sname,ssex,classno

FROMstudent

END

執(zhí)行該存儲過程：

EXECp_insert2'200903101124','王曉麗','女','09031011'

執(zhí)行結果如圖9-17所示。圖9-17執(zhí)行p_insert2存儲過程通過比較上面兩個存儲過程可以看出，對存儲過程p_insert1，用戶執(zhí)行只能插入固定的一條學生信息。如果要多次插入，只能創(chuàng)建多個存儲過程。而對存儲過程p_insert2，用戶可以在執(zhí)行存儲過程時通過輸入?yún)?shù)插入多條數(shù)據(jù)。帶輸入?yún)?shù)的存儲過程可以提供更靈活的應用。

【例9-29】創(chuàng)建存儲過程，要求根據(jù)用戶輸入的課程號修改相應課程的授課學時。

CREATEPROCp_update

@cnoint,@modulusfloat

AS

BEGIN

UPDATEcourse

SEThours=hours*@modulus

WHEREcno=@cno

END存儲過程p_update有兩個輸入?yún)?shù)：@cno用來獲取用戶想要修改的課程號；@modulus用來獲取用戶要修改的課程學時的系數(shù)。

執(zhí)行存儲過程，將課程編號為03105的課程的授課學時增加20%：

EXECp_update'03105',1.2

為了顯示存儲過程執(zhí)行前后course表中數(shù)據(jù)的變化，在執(zhí)行存儲過程前查看course表中的數(shù)據(jù)，執(zhí)行完后再查看course表中的數(shù)據(jù)，以驗證存儲過程執(zhí)行是否正確。執(zhí)行結果如圖9-18所