T-SQL 語句（五） —— 存儲過程

2019-11-08 20:31:05

字體：大中小

供稿：網(wǎng)友

T-SQL 語句（五） —— 存儲過程

一、存儲過程簡介

1、存儲過程：一組預(yù)先寫好的能實現(xiàn)某種功能的T-SQL 程序，指定一個程序名后編譯后并將其存在SQL Server 中，以后要實現(xiàn)該功能可調(diào)用這個程序來完成。 2、存儲過程的優(yōu)點：　　ａ、執(zhí)行速度快、效率高，存儲過程會被編譯成二進(jìn)制可執(zhí)行代碼，運行存儲過程時不需要編譯，可加快執(zhí)行速度。　　b、模塊化編程，創(chuàng)建后可被多次調(diào)用而不必重新編寫該T-SQL語句，一次修過所有調(diào)用該存儲過程的程序多得到的結(jié)果都會被修改，提高了程序的可移植性。　　c、減少網(wǎng)絡(luò)流量：客戶端調(diào)用時只需指定存儲過程名稱和參數(shù)，而不用傳送整個T-SQL語句，利于減少網(wǎng)絡(luò)流量，提高運行速度。　　d、安全性，可以作為一種安全機制為不同權(quán)限的用戶使用不同的存儲過程。 3、存儲過程的類型：　　a、系統(tǒng)存儲過程：系統(tǒng)存儲過程一般以“sp_”為前綴，從物理意義上講系統(tǒng)存儲過程存儲在 Resource 數(shù)據(jù)庫中　　b、擴展存儲過程：通常以“xp_”為前綴，是由其他編程語言（如C#）創(chuàng)建的外部存儲過程，內(nèi)容不存儲在SQL Server 中而是以 DLL 形式存在。　　c、用戶自定義存儲過程：用戶自定義存儲過程分為 T-SQL 存儲過程和 CLR 存儲過程兩種。T-SQL 存儲過程保存 T-SQL 語句集合，可以接受和返回用戶提供的參數(shù)；CLR 存儲過程時針對 CLR 方法的引用，可以接受和返回用戶提供的參數(shù)，CLR 存儲過程在.NET Framework 程序中是作為公共靜態(tài)方法實現(xiàn)的。

二、創(chuàng)建存儲過程

CREATE PROC pr_employee_all AS SELECT * FROM employeeGO -- 一個存儲過程就是一個批處理，在遇到 GO 時查詢編輯器會認(rèn)為存儲過程代碼已經(jīng)結(jié)束EXEC pr_employee_allCREATE PROC pr_getbyid_employee @id INTAS SELECT * FROM employee WHERE id = @idGOEXEC pr_getbyid_employee 5CREATE PROC pr_getbyname_employee @name NVARCHAR(20) = '默認(rèn)'AS SELECT * FROM employee WHERE name LIKE '%' + @name + '%'GOEXEC pr_getbyname_employee -- 返回沒有記錄EXEC pr_getbyname_employee default -- 傳遞默認(rèn)值，返回“默認(rèn)”EXEC pr_getbyname_employee @name = '張三' -- 指定參數(shù)名，返回“張三”CREATE PROC pr_sumage_employee AS RETURN SELECT sum(age) FROM employeeGOCREATE PROC #tmp_proc AS SELECT * FROM employee WHERE id > 8 GOEXEC #tmp_proc

三、修改存儲過程

ALTER PROC pr_employee_all AS -- 除了 ALTER PROC 修改存儲過程的語句語法與創(chuàng)建存儲過程的語法代碼相同 SELECT * FROM employee ORDER BY id DESCGOEXEC pr_employee_all

四、存儲過程的返回值

1、存儲過程以“return n”的形式返回一個整數(shù)值。 2、存儲過程指定一個 OUTPUT 的返回參數(shù)以返回值。 3、存儲過程執(zhí)行 T-SQL 語句返回數(shù)據(jù)集，如 SELECT 語句

CREATE PROC pr_count_product_amount AS DECLARE @count INT SELECT @count = sum(id) FROM employee RETURN @count -- 使用 Return 返回數(shù)字GODECLARE @count INT @count = EXEC pr_count_product_amount -- 執(zhí)行求商品數(shù)量總計的存儲過程PRINT @countCREATE PROC pr_count_order_amount AS DECLARE @count INT output -- 使用 output 返回數(shù)據(jù) SELECT @count = count(id) FROM order RETURN @count -- 使用 Return 返回數(shù)字GODECLARE @count INT EXEC pr_count_order_amount @count output -- 執(zhí)行求訂單數(shù)量總計的存儲過程PRINT @count--SET NOCOUNT ON 關(guān)閉顯示受影響行數(shù)

五、存儲過程的嵌套調(diào)用

存儲過程可以多次嵌套調(diào)用，調(diào)用最多層次可以嵌套到32層，可以使用@@NESTLEVEL 來查看當(dāng)前正在執(zhí)行的存儲過程的嵌套層數(shù)

ALTER PROC pr_nest -- 嵌套調(diào)用 @i INT = 0 AS BEGIN PRINT 'This is pr_nest,Level' PRINT @@NESTLEVEL SET @i = @i + 1 IF @i < 3 -- 設(shè)定退出條件 EXEC pr_nest @i END GO EXEC pr_nest default

六、加密存儲過程

CREATE PROC pc_test WITH ENCRYPTION AS PRINT 'Hello World' -- 跟視圖加密類似使用戶無法查看存儲過程的內(nèi)容

七、查看存儲過程源碼

EXEC sp_helptext pr_test -- 查看源碼，加密存儲過程則無法查看

八、使用存儲過程組

CREATE PROC pr_group;1 WITH ENCRYPTION AS PRINT '存儲過程組，編號1'GOCREATE PROC pr_group;2 WITH ENCRYPTION AS PRINT '存儲過程組，編號2'GOEXEC pr_group;1EXEC pr_group;2

九、CLR 存儲過程

1、開啟 SQL Server 對 CLR 存儲過程的支持

EXEC sp_configure 'clr','1' -- 開啟SQL Server 對 CLR 存儲過程的支持GORECONFIGUREGO

2、編寫 CLR 存儲過程代碼

using System;using System.Data;using System.Data.SqlClient;using System.Data.SqlTypes;using Microsoft.SqlServer.Server;namespace CLR_SP{ public partial class StoredProcedures { [Microsoft.SqlServer.Server.SqlProcedure] public static void OrderQtySum(out SqlInt16 value)--存儲過程返回參數(shù)使用 out關(guān)鍵字 { //設(shè)置連接 SqlConnection conn = new SqlConnection(); conn.ConnectionString = "Context Connection=true"; conn.Open(); //設(shè)置命令 SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandText = "SELECT TOP 10 OrderQty FROM OrderDetail"; //以只進(jìn)方式讀取 SQL Server 中的數(shù)據(jù) value = 0; SqlDataReader reader = cmd.ExecuteReader(); while(reader.Reader()) { value += reader.GetSqlInt16(0);//獲取指定列的值 } } [Microsoft.SqlServer.Server.SqlProcedure] public static void ExecuteToClient(SqlInt32 orderID) { SqlConnection conn = new SqlConnection(); conn.ConnectionString = "Context Connection=true"; conn.Open(); SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandText = "SELECT ProductID,OrderQty,UnitPrice FROM OrderItem WHERE OrderID=@orderID"; cmd.Parameters.AddWithValue("@orderID",orderID); SqlContext.Pipe.ExecuteAndSend(cmd); } }}

3、編譯 CLR 存儲過程代碼成為程序集并注冊到 SQL Server

CREATE ASSEMBLY CLRFunFROM 'C:/MyProject/CLRFun/CLRFun.dll';GO

4、創(chuàng)建CLR 存儲過程的引用

CREATE PROC CLR_OrderQtySum @value INTAS EXTERNAL NAME CLR_SP.StoredProcedures.OrderQtySumGO

5、執(zhí)行 CLR 存儲過程

DECLARE @value INTEXEC CLRSP @value outputPRINT @value

上一篇：Redis 3.0集群搭建測試（一）

下一篇：Windows-MySQL安裝命令