快捷導(dǎo)航

MSSQL存儲(chǔ)過(guò)程學(xué)習(xí)筆記一關(guān)于存儲(chǔ)過(guò)程

更新時(shí)間：2011年05月04日 00:19:38 作者：

在寫(xiě)筆記之前，首先需要整理好這些概念性的東西，否則的話，就會(huì)在概念上產(chǎn)生陌生或者是混淆的感覺(jué)。

一、存儲(chǔ)過(guò)程的概念，優(yōu)點(diǎn)，語(yǔ)法
在寫(xiě)筆記之前，首先需要整理好這些概念性的東西，否則的話，就會(huì)在概念上產(chǎn)生陌生或者是混淆的感覺(jué)。
概念：將常用的或者是很復(fù)雜的工作，預(yù)先利用SQL語(yǔ)句寫(xiě)好并用一個(gè)指定的名稱存儲(chǔ)起來(lái)，那么以后要是調(diào)用這些SQL語(yǔ)句的時(shí)候，只需要利用Execute/Exec執(zhí)行以下，即可。
優(yōu)點(diǎn)：當(dāng)然了，使用存儲(chǔ)過(guò)程的優(yōu)點(diǎn)是很多的，下面來(lái)一一說(shuō)明。
1、存儲(chǔ)過(guò)程只是在創(chuàng)造的時(shí)候進(jìn)行編譯，以后每次執(zhí)行的時(shí)候，就不需要編譯了，但是直接利用SQL的話，需要每次運(yùn)行的時(shí)候都重新編譯一次，所以使用存儲(chǔ)過(guò)程可以提高數(shù)據(jù)庫(kù)的執(zhí)行速度。
2、當(dāng)對(duì)數(shù)據(jù)庫(kù)進(jìn)行復(fù)雜操作的時(shí)候，利用存儲(chǔ)過(guò)程進(jìn)行封裝，可以減少代碼出錯(cuò)的幾率，并且MSSQL本身具有代碼調(diào)試能力，可以很容易的定位到出錯(cuò)的語(yǔ)句。
3、存儲(chǔ)過(guò)程可以重復(fù)使用，可以提高開(kāi)發(fā)人員的開(kāi)發(fā)效率。
4、安全性高，可以設(shè)定只有特定權(quán)限的用戶對(duì)存儲(chǔ)過(guò)程進(jìn)行操作；也可以在一定的程度上預(yù)防SQL注入操作。
種類(lèi)：存儲(chǔ)過(guò)程分為三類(lèi)，分別為系統(tǒng)存儲(chǔ)過(guò)程、擴(kuò)展存儲(chǔ)過(guò)程、用戶自定義存儲(chǔ)過(guò)程。
1、系統(tǒng)存儲(chǔ)過(guò)程：就是以SP_開(kāi)頭的存儲(chǔ)過(guò)程，用來(lái)進(jìn)行系統(tǒng)的各種設(shè)定，取得信息，進(jìn)行相關(guān)的管理工作等等；如：sp_help就是取得指定對(duì)象的相關(guān)信息。
2、擴(kuò)展存儲(chǔ)過(guò)程：就是以XP_開(kāi)頭的，用來(lái)調(diào)用操作系統(tǒng)提供的功能。以下為引用的內(nèi)容：exec master..xp_cmdshell ‘ping 127.0.0.1'
3、用戶自定義的存儲(chǔ)過(guò)程：
常用格式如下：

復(fù)制代碼代碼如下:

 
Create procedure procedue_name 
　　[@parameter data_type][output] 
　　[with]{recompile|encryption} 
　　as 
　　sql_statement 

需要說(shuō)明的就是:
output：表明此參數(shù)是可以回傳的。
[with]{recompile|encryption}中的recompile：表明每次執(zhí)行此存儲(chǔ)過(guò)程的時(shí)候，都重新編譯一次（默認(rèn)情況下只有在創(chuàng)建的時(shí)候才進(jìn)行編譯）。
encryption：所創(chuàng)建的存儲(chǔ)過(guò)程的內(nèi)容會(huì)被加密。
小技巧：在這里需要說(shuō)明的是，如果我們有時(shí)候要在數(shù)據(jù)庫(kù)中查找所有包含A關(guān)鍵字的表的列的名稱，那么該如何尋找呢？可以利用下面的語(yǔ)句：

復(fù)制代碼代碼如下:

 
select table_name,column_name from INFORMATION_SCHEMA.COLUMNSwhere COLUMN_NAME like '%A%'; --查看那些表含有包含A的列 

但是如果想在存儲(chǔ)過(guò)程找存在表“B”的存儲(chǔ)過(guò)程的名稱，該如何做呢，可以利用下面的語(yǔ)句來(lái)進(jìn)行：

復(fù)制代碼代碼如下:

 
select routine_name, routine_definition from information_schema.routines 
where routine_definition like '%B%' 
and routine_type='procedure' 

當(dāng)然了，我們其實(shí)還可以利用SQL中的syscomments,sysobjects,sysdepends來(lái)查看具體的數(shù)據(jù)信息，這個(gè)和oracle中的dba_objects等很像：

復(fù)制代碼代碼如下:

 
select * from syscomments; --查看標(biāo)注 
select * from sysobjects; --查看數(shù)據(jù)庫(kù)對(duì)象 
select * from sysdepends; --查看依賴關(guān)系 

二、存儲(chǔ)過(guò)程進(jìn)階
當(dāng)然了，說(shuō)先來(lái)說(shuō)明下存儲(chǔ)過(guò)程的格式語(yǔ)法規(guī)則：

復(fù)制代碼代碼如下:

 
Create Procedure Procedure-name ( Input parameters , Output Parameters (If required))AsBegin Sql statement used in the stored procedureEnd 

在這里我們利用一個(gè)普通的例子來(lái)說(shuō)明:

復(fù)制代碼代碼如下:

 
/* Getstudentname is the name of the stored procedure*/ 
Create PROCEDURE Getstudentname( 
@studentid INT --Input parameter , Studentid of the student 
) 
AS 
BEGIN 
SELECT Firstname+' '+Lastname FROM tbl_Students WHERE studentid=@studentid 
END 

當(dāng)然了，這里的@studentid參數(shù)只是一個(gè)傳入的參數(shù)，但是如果想回傳一個(gè)值，那么就需要利用到out參數(shù)來(lái)實(shí)現(xiàn)，具體的實(shí)現(xiàn)代碼如下：

復(fù)制代碼代碼如下:

 
/* 
GetstudentnameInOutputVariable is the name of the stored procedure which 
uses output variable @Studentname to collect the student name returns by the 
stored procedure 
*/ 
Create PROCEDURE GetstudentnameInOutputVariable 
( 
@studentid INT, --Input parameter , Studentid of the student 
@studentname VARCHAR(200) OUT -- Out parameter declared with the help of OUT keyword 
) 
AS 
BEGIN 
SELECT @studentname= Firstname+' '+Lastname FROM tbl_Students WHERE studentid=@studentid 
END 

從上面的代碼，可以看出out參數(shù)的具體用法，但是如果想在SQL服務(wù)器端執(zhí)行這段代碼，那該如何進(jìn)行呢？
其實(shí)，一說(shuō)到這，稍微麻煩一點(diǎn)，如果是只有in參數(shù)，那么只需要利用execute/exec 后面加上存儲(chǔ)過(guò)程的名稱，里面給參數(shù)賦值即可；但是如果不僅有in參數(shù)，而且有out參數(shù)，這個(gè)該怎么來(lái)弄呢？
下面通過(guò)一個(gè)具體的實(shí)例來(lái)詳細(xì)的描述用法：

復(fù)制代碼代碼如下:

 
Alter PROCEDURE GetstudentnameInOutputVariable 
( 
@studentid INT, --Input parameter , Studentid of the student 
@studentname VARCHAR (200) OUT, -- Output parameter to collect the student name 
@StudentEmail VARCHAR (200)OUT -- Output Parameter to collect the student email 
) 
AS 
BEGIN 
SELECT @studentname= Firstname+' '+Lastname, 
@StudentEmail=email FROM tbl_Students WHERE studentid=@studentid 
END 

可以看出，上面的存儲(chǔ)過(guò)程有三個(gè)參數(shù)，其中第一個(gè)是IN參數(shù)，而后兩個(gè)是OUT參數(shù)，從過(guò)程主體可以看出，第一個(gè)out參數(shù)就是得到學(xué)生全名，第二個(gè)則是得到email的。
那么如何在服務(wù)器端查看執(zhí)行后得到的結(jié)果呢？

復(fù)制代碼代碼如下:

 
Declare @Studentname as nvarchar(200) -- 申明第一個(gè)輸出參數(shù) 
Declare @Studentemail as nvarchar(50) -- 申明第二個(gè)輸出參數(shù) 
Execute GetstudentnameInOutputVariable 1 , @Studentname output, @Studentemail output 
Select @Studentname,@Studentemail --“select”語(yǔ)句可以查看結(jié)果 

好了，上面就是存儲(chǔ)過(guò)程方面的一些知識(shí)，后續(xù)文章將會(huì)將游標(biāo)，自定義錯(cuò)誤，事物運(yùn)行給包括進(jìn)來(lái)，希望SQL在一步一步的學(xué)習(xí)中，堅(jiān)實(shí)的成長(zhǎng)起來(lái)。

您可能感興趣的文章: