TSQL存儲(chǔ)過(guò)程概念與案例實(shí)戰(zhàn)

存儲(chǔ)過(guò)程

存儲(chǔ)過(guò)程由一個(gè)或多個(gè) T-SQL 語(yǔ)句或?qū)?NET公共語(yǔ)言運(yùn)行時(shí) (CLR) 方法的引用所構(gòu)成的一組程序塊。這里的T-SQL語(yǔ)句包括執(zhí)行DDL、DML語(yǔ)句、應(yīng)用臨時(shí)表、動(dòng)態(tài)SQL、定義異常處理等。但是相比于函數(shù)，它不能嵌套在查詢里，但它可以調(diào)用其它的存儲(chǔ)過(guò)程，即存儲(chǔ)過(guò)程可以相互調(diào)用。

存儲(chǔ)過(guò)程和常見(jiàn)的程序語(yǔ)言類似，可以指定輸入和輸出參數(shù)。SQL Server通過(guò)緩存存儲(chǔ)過(guò)程的執(zhí)行計(jì)劃進(jìn)而達(dá)到節(jié)約時(shí)間、降低CPU、內(nèi)存的目的。

存儲(chǔ)過(guò)程相對(duì)于在應(yīng)用端實(shí)現(xiàn)業(yè)務(wù)邏輯有以下好處：

1 通過(guò)封裝實(shí)現(xiàn)重用性和邏輯復(fù)雜性的隱藏。僅需要通過(guò)存儲(chǔ)過(guò)程的修改(alter procedure)就能應(yīng)用新的邏輯。

2 減少網(wǎng)絡(luò)的傳輸，這是因?yàn)榇鎯?chǔ)過(guò)程即存儲(chǔ)在數(shù)據(jù)庫(kù)里，而如果是應(yīng)用程序方式訪問(wèn)數(shù)據(jù)庫(kù)識(shí)別有網(wǎng)絡(luò)傳輸?shù)某杀尽?/p>

3 存儲(chǔ)過(guò)程可以提供對(duì)數(shù)據(jù)庫(kù)進(jìn)行安全的訪問(wèn)，即賦予了執(zhí)行存儲(chǔ)過(guò)程的用戶不能直接訪問(wèn)底層的數(shù)據(jù)庫(kù)對(duì)象，這種隔離對(duì)數(shù)據(jù)庫(kù)提供了保障。

補(bǔ)注：盡管存儲(chǔ)過(guò)程有這些優(yōu)點(diǎn)，但實(shí)際應(yīng)用程序封裝在Service(服務(wù)層)，其對(duì)應(yīng)的是某些單獨(dú)的SQL邏輯，而如果都放在存儲(chǔ)過(guò)程則會(huì)出現(xiàn)靈活性差、代碼難維護(hù)的情況。

普通存儲(chǔ)過(guò)程

假設(shè)我們有一張tb_user表，有兩個(gè)字段id,name，如果我想通過(guò)傳參數(shù)的形式給這張表插入數(shù)據(jù)，那么我們可以通過(guò)存儲(chǔ)過(guò)程來(lái)實(shí)現(xiàn)。這里id需要沿著tb_user的最大的值往下自增，自增間隔是1。

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[usp_useradd_MS] (@username VARCHAR(100)) AS DECLARE @max_id int=0 DECLARE @has int=0 BEGIN SELECT @max_id=max(id) FROM tb_user ; SELECT @has=1 FROM tb_user WHERE name = @username;IF @has =0BEGININSERT INTO tb_user VALUES(@max_id+1,@username);END;END; GO--調(diào)用，可選用下列任一方式執(zhí)行 exec usp_useradd_MS 'Mike' exec usp_useradd_MS @username='John' --驗(yàn)證結(jié)果 SELECT * FROM tb_user;

存儲(chǔ)過(guò)程與動(dòng)態(tài)SQL

所謂動(dòng)態(tài)SQL即SQL的內(nèi)容是靈活的，是通過(guò)字符串拼接出來(lái)的，可以理解是不固定的。比如有一個(gè)需求，我們想通過(guò)查詢指定表的字段名并以列的形式展示出來(lái)，這里就可以通過(guò)存儲(chǔ)過(guò)程結(jié)合動(dòng)態(tài)SQL來(lái)實(shí)現(xiàn)。因?yàn)槊看蝹鬟^(guò)來(lái)的參數(shù)即表名是不固定的，所以需要一個(gè)字符串變量拿到參數(shù)里的值再拼接成最終的sql(相對(duì)于翻譯一下)，再去數(shù)據(jù)庫(kù)里執(zhí)行。

CREATE PROCEDURE [dbo].[usp_getColumnsBycolumn]( @tabname VARCHAR(100)) AS DECLARE @sql VARCHAR(8000) DECLARE @STRING VARCHAR(500) BEGIN SELECT @sql= ISNULL(@sql+',','')+'['+CAST(COLID AS VARCHAR(12))+']' FROM SYSCOLUMNS WHERE ID = OBJECT_ID(@tabname) GROUP BY COLID SET @STRING='SELECT * FROM (SELECT NAME,COLID FROM SYSCOLUMNS WHERE ID=OBJECT_ID('''+@tabname+'''))A pivot (MAX(NAME) for COLID in('+@sql+'))t' EXEC(@STRING) END GO --調(diào)用及結(jié)果 exec usp_getColumnsBycolumn 'tb_user'

存儲(chǔ)過(guò)程內(nèi)調(diào)用存儲(chǔ)過(guò)程

存儲(chǔ)過(guò)程里是可以調(diào)用其它存儲(chǔ)過(guò)程的，有時(shí)一些復(fù)雜的邏輯需要多個(gè)存儲(chǔ)過(guò)程結(jié)合一起才能實(shí)現(xiàn)最終效果。

CREATE PROCEDURE [dbo].[usp_getColumnsByonecolumn]( @tabname VARCHAR(100)) AS DECLARE @tab Table (colid varchar(100),colname varchar(20)) BEGIN INSERT INTO @tab exec usp_getColumnsBycolumn @tabname SELECT colid FROM @tab END --調(diào)用 exec usp_getColumnsByonecolumn 'tb_user' -- 結(jié)果

注意：我們不可以把存儲(chǔ)過(guò)程包裹在子查詢里，即如下方式查詢存儲(chǔ)過(guò)程的結(jié)果是不支持的。

SELECT?*?FROM(exec?usp_getColumnsBycolumn?'tb_user')A

系統(tǒng)函數(shù)查看存儲(chǔ)過(guò)程的定義

可以通過(guò)內(nèi)置的系統(tǒng)函數(shù)sp_helptext查看存儲(chǔ)過(guò)程的文本定義。

sp_helptext @objname = N'usp_getColumnsByonecolumn' #結(jié)果,存儲(chǔ)過(guò)程的文本定義見(jiàn)下： CREATE PROCEDURE [dbo].[usp_getColumnsByonecolumn]( @tabname VARCHAR(100)) AS DECLARE @tab Table(colid varchar(100), colname varchar(20)) BEGIN INSERT INTO @tab exec usp_getColumnsBycolumn @tabname SELECT colid FROM @tab END

存儲(chǔ)過(guò)程的安全性

存儲(chǔ)過(guò)程跟表、視圖一樣首先用戶和角色，賦予EXECUTE權(quán)限后才能執(zhí)行。特別的，如果過(guò)程里用到非dbo的schema(架構(gòu))那么在賦予存儲(chǔ)過(guò)程權(quán)限后也要將該架構(gòu)下表的訪問(wèn)權(quán)限賦予用戶，否則存儲(chǔ)可以執(zhí)行但是會(huì)報(bào)底層表訪問(wèn)權(quán)限的錯(cuò)誤。

CREATE LOGIN db_loginuser WITH PASSWORD = 'D#2$3)5b'; GO CREATE USER db_user FOR LOGIN db_loginuser; GO EXECUTE AS LOGIN = 'db_loginuser'; SELECT SUSER_NAME() AS [login], USER_NAME() AS [user];

exec usp_useradd_MS 'Alice'

?

REVERT; SELECT SUSER_NAME() AS [login], USER_NAME() AS [user];

GRANT EXEC ON dbo.usp_useradd_MS TO db_user; EXECUTE AS LOGIN = 'db_loginuser'; SELECT SUSER_NAME() AS [login], USER_NAME() AS [user]; exec usp_useradd_MS 'Alice'

?

REVERT; SELECT * FROM tb_user

注:針對(duì)sa、dbo無(wú)法執(zhí)行，即無(wú)法通過(guò)下列的語(yǔ)句切換到sa用戶

EXECUTE?AS?LOGIN?=?'sa';

EXECUTE?AS?LOGIN?=?'dbo';

可通過(guò)REVERT;語(yǔ)句切換到上一次的用戶執(zhí)行環(huán)境。

存儲(chǔ)過(guò)程與事務(wù)

事務(wù)是一個(gè)工作整體，它包含一個(gè)或多個(gè)操作數(shù)據(jù)(可能還包括數(shù)據(jù)結(jié)構(gòu))的活動(dòng)。比如我們建表的DDL語(yǔ)句就是一個(gè)事務(wù)，要么建表成功要么失敗，不存在中間狀態(tài)。事務(wù)具有ACID性，其中A(atomicity)即原子性、C(consistency)即一致性、I（isolation）隔離性、D（durability）持久性。

SQL Server事務(wù)分為隱性事務(wù)和顯性事務(wù)的，如果是隱性的可通過(guò)SET IMPLICIT_TRANSACTIONS ON;開關(guān)打開該選項(xiàng)，在該模式下只需要在要操作的事務(wù)的尾部編寫commit或者rollback即可提交或者回滾事務(wù)，而不用在事務(wù)開頭寫begin tran。相對(duì)的顯式事務(wù)需要在事務(wù)前后定義諸如BEGIN TRAN … COMMIT TRAN（ROLLBACK TRAN）

隱式事務(wù)的例子：

SET IMPLICIT_TRANSACTIONS ON select @@TRANCOUNT INSERT INTO tb_user VALUES(110,'Philips') select @@TRANCOUNT -- 注：以上代碼需要一起執(zhí)行。 -- 查看插入的數(shù)據(jù) SELECT * FROM tb_user A WHERE A.name = 'Philips' --這里如果回滾則數(shù)據(jù)不存在。 ROLLBACK

以回滾為例演示在存儲(chǔ)過(guò)程里如何使用事務(wù)。如果傳入的參數(shù)(用戶名)是'Lily'則回滾，其它則插入并提交事務(wù)。

CREATE PROCEDURE [dbo].[usp_useradd_MS_trans](@username VARCHAR(100)) AS BEGIN print @username; if @username='Lily' BEGIN BEGIN TRAN exec usp_useradd_MS @username ROLLBACK TRAN END ELSE BEGIN exec usp_useradd_MS @username END END GO -- 執(zhí)行與結(jié)果 EXEC usp_useradd_MS_trans 'Lily' EXEC usp_useradd_MS_trans 'Tim'

SELECT?*?FROM?tb_user?A?WHERE?A.name?=?'Lily'

SELECT?*?FROM?tb_user?A?WHERE?A.name?=?'Tim'

總結(jié)

以上是生活随笔為你收集整理的数据库工程开发秘籍之TSQL 存储过程user stored procedure的概念与案例实战的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。