什么是存盤程序？

T-SQL中的存盤程序，非常類似于net語言中的方法，它可以重復呼叫，當存盤程序執行一次后，可以將陳述句快取中，這樣下次執行的時候直接使用快取中的陳述句，

這樣就可以提高存盤程序的性能，

存盤程序Procedure是一組為了完成特定功能的SQL陳述句集合，經編譯后存盤在資料庫中，用戶通過指定存盤程序的名稱并給出引數來執行，

存盤程序中可以包含邏輯控制陳述句和資料操縱陳述句，它可以接受引數、輸出引數、回傳單個或多個結果集以及回傳值，

由于存盤程序在創建時即在資料庫服務器上進行了編譯并存盤在資料庫中，所以存盤程序運行要比單個的SQL陳述句塊要快，

同時由于在呼叫時只需用提供存盤程序名和必要的引數資訊，所以在一定程度上也可以減少網路流量、簡單網路負擔，

存盤程序的優點

1、存盤程序允許標準組件式編程

存盤程序創建后可以在程式中被多次呼叫執行，而不必重新撰寫該存盤程序的SQL陳述句，

而且資料庫專業人員可以隨時對存盤程序進行修改，但對應用程式源代碼卻毫無影響，從而極大的提高了程式的可移植性，

2、存盤程序能夠實作較快的執行速度

如果某一操作包含大量的T-SQL陳述句代碼，分別被多次執行，那么存盤程序要比批處理的執行速度快得多，

因為存盤程序是預編譯的，在首次運行一個存盤程序時，查詢優化器對其進行分析、優化，并給出最終被存在系統表中的存盤計劃，

而批處理的T-SQL陳述句每次運行都需要預編譯和優化，所以速度就要慢一些，

3、存盤程序減輕網路流量

對于同一個針對資料庫物件的操作，如果這一操作所涉及到的T-SQL陳述句被組織成一存盤程序，

那么當在客戶機上呼叫該存盤程序時，網路中傳遞的只是該呼叫陳述句，否則將會是多條SQL陳述句，

從而減輕了網路流量，降低了網路負載，

4、存盤程序可被作為一種安全機制來充分利用

系統管理員可以對執行的某一個存盤程序進行權限限制，從而能夠實作對某些資料訪問的限制，避免非授權用戶對資料的訪問，保證資料的安全，

存盤程序的缺點

1、運行速度

對于很簡單的sql，存盤程序運行速度沒有什么優勢，

2、代碼可讀性差，不易于維護

存盤程序的開發除錯要比一般程式困難（老版本DB2還只能用C寫存盤程序，更是一個災難），

代碼可讀性差，不易于難維護，

3、可移植性差

由于存盤程序將應用程式系結到SQLServer，因此使用存盤程序封裝業務邏輯將限制應用程式的可移植性，

如果應用程式的可移植性在您的環境中非常重要，則將業務邏輯封裝在不特定于RDBMS的中間層中可能是一個更佳的選擇，

存盤程序的基本語法

變數的宣告：
宣告變數時必須在變數前加@符號
declare @num int

變數的賦值：
變數賦值時變數前必須加set
set @num= 30

宣告多個變數：
declare @name varchar(10),@num int

if陳述句的使用：

declare @d int
set @d = 1
IF @d = 1
BEGIN
    PRINT '正確' 
END 
ELSE BEGIN 
    PRINT '錯誤' 
END

多條件選擇陳述句：

declare @today int
declare @week nvarchar(3)
set @today=3
set @week= case
     when @today=1 then '星期一'
     when @today=2 then '星期二'
     when @today=3 then '星期三'
     when @today=4 then '星期四'
     when @today=5 then '星期五'
     when @today=6 then '星期六'
     when @today=7 then '星期日'
     else '值錯誤'
end
print @week

回圈陳述句：

DECLARE @i INT
SET @i = 1
WHILE @i<1000000 BEGIN
set @i=@i+1
END

定義游標：

DECLARE @cur1 CURSOR FOR SELECT .........

OPEN @cur1
FETCH NEXT FROM @cur1 INTO 變數
WHILE(@@FETCH_STATUS=0)
BEGIN
處理.....
FETCH NEXT FROM @cur1 INTO 變數
END
CLOSE @cur1
DEALLOCATE @cur1

存盤程序的分類

1、系統存盤程序

系統存盤程序是系統創建的存盤程序，目的在于能夠方便的從系統表中查詢資訊或完成與更新資料庫表相關的管理任務或其他的系統管理任務，

系統存盤程序主要存儲在master資料庫中，以“sp”下劃線開頭的存盤程序，

盡管這些系統存盤程序在master資料庫中，但我們在其他資料庫還是可以呼叫系統存盤過程，

有一些系統存盤程序會在創建新的資料庫的時候被自動創建在當前資料庫中，

1.1、系統存盤程序sql示例

--表重命名
exec sp_rename 'stu', 'stud';--列重命名
exec sp_rename 'stud.name', 'sName', 'column';
exec sp_help 'stud';
--重命名索引
exec sp_rename N'student.idx_cid', N'idx_cidd', N'index';
exec sp_help 'student';

--查詢所有存盤程序
select * from sys.objects where type = 'P';
select * from sys.objects where type_desc like '%pro%' and name like 'sp%';

2、自定義存盤程序

所謂自定義存盤程序，是指為了完成某一段特定的功能需求，在用戶資料庫中利用t-sql自行編輯的陳述句集合，在用戶自定義的程序中可以有輸入引數，回傳的輸出引數及回傳至客戶端的資訊與結果，

如果在存盤程序名稱前加了“##”符號，表示創建的存盤程序是臨時的全域性的；

如果前面的為“#”符號，表示所創建的存盤程序是臨時的區域的，該存盤程序只能在創建它的會話中使用，

以上兩種存盤程序創建后都存放在tempdb資料庫中，

用戶自定義存盤程序還可以細分為t-sql語言存盤程序和CLR存盤程序，CLR存盤程序是指利用.NET框架公共語言編輯的存盤程序，既可以接受用戶提供的引數又可以回傳存盤程序的運行結果，通常用作某個類的公共靜態方法，

2.1、創建不帶引數存盤程序

--創建一個回傳結果集的存盤程序（proc或者procedure均可）
if (object_id('proc_get_student', 'P') is not null)--判斷存盤程序是否存在 另外一種 if (exists (select * from sys.objects where name = 'proc_get_student'))//
drop proc proc_get_student --洗掉存盤程序
go
create proc proc_get_student --創建存盤程序
as
select * from student; --結果集

--呼叫執行存盤程序，得到回傳集(exec或者execute均可)
exec proc_get_student;

2.2、修改存盤程序

--修改存盤程序
alter proc proc_get_student
as
select * from student;  --修改后的SQL陳述句

--呼叫執行存盤程序，得到回傳集(exec或者execute均可)
exec proc_get_student;

2.3、帶引數存盤程序

--創建一個回傳結果集的存盤程序（proc或者procedure均可）
if (object_id('proc_find_stu', 'P') is not null)--判斷存盤程序是否存在
drop proc proc_find_stu
go
create proc proc_find_stu(@startId int, @endId int)--兩個引數
as
select * from student where id between @startId and @endId   --查詢陳述句
go

--呼叫執行存盤程序，2,4為引數
exec proc_find_stu 2, 4;

2.4、帶通配符引數存盤程序

--創建一個回傳結果集的存盤程序（proc或者procedure均可）
if (object_id('proc_findStudentByName', 'P') is not null)
drop proc proc_findStudentByName
go
create proc proc_findStudentByName(@name varchar(20) = '%j%', @nextName varchar(20) = '%')
as
select * from student where name like @name and name like @nextName;
go

--呼叫執行存盤程序
exec proc_findStudentByName;
exec proc_findStudentByName '%o%', 't%';

2.5、帶輸出引數存盤程序

--創建一個回傳結果集的存盤程序（proc或者procedure均可）
if (object_id('proc_getStudentRecord', 'P') is not null)
drop proc proc_getStudentRecord
go
create proc proc_getStudentRecord(
@id int, --默認輸入引數
@name varchar(20) out, --輸出引數
@age varchar(20) output--輸入輸出引數
)
as
select @name = name, @age = age from student where id = @id and sex = @age;
go

--呼叫執行存盤程序
declare @id int,
@name varchar(20),
@temp varchar(20);
set @id = 7;
set @temp = 1;
exec proc_getStudentRecord @id, @name out, @temp output;
select @name, @temp;
print @name '#' @temp;

3、擴展存盤程序

通常以“xp_”為前綴標識，在sql server系統外通過執行元件，即DLL檔案，來實作的功能，該存盤程序經常使用API介面進行編輯，可以加載到sql server實體的地址空間里試試運行，

在sql server常見的擴展存盤程序有：

xp_enumgroups 指定WINDOWS本地組串列在WINDOWS域中定義的全域組表

xp_findnextmsg 接受輸入的郵件ID號，回傳輸出的郵件ID號

xp_grantlogin 給用戶分配對sql server2012系統的權限

xp_logevent 把用戶自定義訊息輸入到sql server日志檔案或WINDOWS系統事件查看器中

xp_loginconfig 顯示sql server 2012實體運行時登陸的安全配置

好了，我們就介紹到這里吧，

拜拜，我們下次見，

歡迎關注訂閱我的微信公眾平臺【熊澤有話說】，更多好玩易學知識等你來取
作者：熊澤-學習中的苦與樂
公眾號：熊澤有話說
出處： https://www.cnblogs.com/xiongze520/p/14595601.html
創作不易，任何人或團體、機構全部轉載或者部分轉載、摘錄，請在文章明顯位置注明作者和原文鏈接，

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/271179.html

標籤：其他

上一篇：達夢體系結構與運維管理

下一篇：MySQL編程日記：匯入資料

SqlServer存盤程序的創建與使用