MSSQL存儲(chǔ)過(guò)程學(xué)習(xí)筆記一 關(guān)于存儲(chǔ)過(guò)程
發(fā)布日期：2022-01-26 15:43 | 文章來(lái)源：CSDN
在寫筆記之前，首先需要整理好這些概念性的東西，否則的話，就會(huì)在概念上產(chǎn)生陌生或者是混淆的感覺(jué)。
概念：將常用的或者是很復(fù)雜的工作，預(yù)先利用SQL語(yǔ)句寫好并用一個(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注入操作。
種類：存儲(chǔ)過(guò)程分為三類，分別為系統(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)。
版權(quán)聲明：本站文章來(lái)源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請(qǐng)保持原文完整并注明來(lái)源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非www.sddonglingsh.com所屬的服務(wù)器上建立鏡像，否則將依法追究法律責(zé)任。本站部分內(nèi)容來(lái)源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來(lái)，僅供學(xué)習(xí)參考，不代表本站立場(chǎng)，如有內(nèi)容涉嫌侵權(quán)，請(qǐng)聯(lián)系alex-e#qq.com處理。
相關(guān)文章
關(guān)注官方微信