目錄
什么是存儲過程屎媳？
T-SQL中的存儲過程，非常類似于net語言中的方法论巍，它可以重復調用烛谊。當存儲過程執(zhí)行一次后，可以將語句緩存中嘉汰，這樣下次執(zhí)行的時候直接使用緩存中的語句丹禀。
這樣就可以提高存儲過程的性能。
- 存儲過程Procedure是一組為了完成特定功能的SQL語句集合鞋怀，經編譯后存儲在數據庫中双泪，用戶通過指定存儲過程的名稱并給出參數來執(zhí)行。
- 存儲過程中可以包含邏輯控制語句和數據操縱語句密似，它可以接受參數焙矛、輸出參數、返回單個或多個結果集以及返回值残腌。
- 由于存儲過程在創(chuàng)建時即在數據庫服務器上進行了編譯并存儲在數據庫中村斟，所以存儲過程運行要比單個的SQL語句塊要快。
- 同時由于在調用時只需用提供存儲過程名和必要的參數信息抛猫，所以在一定程度上也可以減少網絡流量蟆盹、簡單網絡負擔。
存儲過程的優(yōu)點
1邑滨、存儲過程允許標準組件式編程
存儲過程創(chuàng)建后可以在程序中被多次調用執(zhí)行日缨，而不必重新編寫該存儲過程的SQL語句。
而且數據庫專業(yè)人員可以隨時對存儲過程進行修改掖看，但對應用程序源代碼卻毫無影響匣距，從而極大的提高了程序的可移植性。
2哎壳、存儲過程能夠實現較快的執(zhí)行速度
如果某一操作包含大量的T-SQL語句代碼毅待，分別被多次執(zhí)行，那么存儲過程要比批處理的執(zhí)行速度快得多归榕。
因為存儲過程是預編譯的尸红，在首次運行一個存儲過程 時，查詢優(yōu)化器對其進行分析刹泄、優(yōu)化外里，并給出最終被存在系統表中的存儲計劃。
而批處理的T-SQL語句每次運行都需要預編譯和優(yōu)化特石，所以速度就要慢一些盅蝗。
3、存儲過程減輕網絡流量
對于同一個針對數據庫對象的操作姆蘸，如果這一操作所涉及到的T-SQL語句被組織成一存儲過程墩莫，
那么當在客戶機上調用該存儲過程時，網絡中傳遞的只是該調用語句逞敷，否則將會是多條SQL語句狂秦。
從而減輕了網絡流量，降低了網絡負載推捐。
4裂问、存儲過程可被作為一種安全機制來充分利用
系統管理員可以對執(zhí)行的某一個存儲過程進行權限限制，從而能夠實現對某些數據訪問的限制牛柒，避免非授權用戶對數據的訪問愕秫，保證數據的安全。
存儲過程的缺點
1焰络、運行速度
對于很簡單的sql戴甩，存儲過程運行速度沒有什么優(yōu)勢。
2闪彼、代碼可讀性差甜孤，不易于維護
存儲過程的開發(fā)調試要比一般程序困難（老版本DB2還只能用C寫存儲過程，更是一個災難）畏腕。
代碼可讀性差缴川，不易于難維護。
3描馅、可移植性差
由于存儲過程將應用程序綁定到SQLServer把夸，因此使用存儲過程封裝業(yè)務邏輯將限制應用程序的可移植性。
如果應用程序的可移植性在您的環(huán)境中非常重要铭污，則將業(yè)務邏輯封裝在不特定于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
循環(huán)語句：
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膀篮、系統存儲過程
系統存儲過程是系統創(chuàng)建的存儲過程，目的在于能夠方便的從系統表中查詢信息或完成與更新數據庫表相關的管理任務或其他的系統管理任務岂膳。
系統存儲過程主要存 儲在master數據庫中誓竿，以“sp”下劃線開頭的存儲過程。
盡管這些系統存儲過程在master數據庫中谈截，但我們在其他數據庫還是可以調用系統存儲過 程筷屡。
有一些系統存儲過程會在創(chuàng)建新的數據庫的時候被自動創(chuàng)建在當前數據庫中。
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自行編輯的語句集合扼倘，在用戶自定義的過程中可以有輸入參數，返回的輸出參數及返回至客戶端的信息與結果 诽表。
如果在存儲過程名稱前加了“##”符號唉锌，表示創(chuàng)建的存儲過程是臨時的全局性的；
如果前面的為“#”符號竿奏，表示所創(chuàng)建的存儲過程是臨時的局部的袄简，該存儲過程只能在創(chuàng)建它的會話中使用。
以上兩種存儲過程創(chuàng)建后都存放在tempdb數據庫中泛啸。
用戶自定義存儲過程還可以細分為t-sql語言存儲過程和CLR存儲過程绿语。CLR存儲過程是指利用.NET框架公共語言編輯的存儲過程，既可以接受用戶提供的參數又可以返回存儲過程的運行結果候址，通常用作某個類的公共靜態(tài)方法吕粹。
2.1、創(chuàng)建不帶參數存儲過程
--創(chuàng)建一個返回結果集的存儲過程（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 --創(chuàng)建存儲過程
as
select * from student; --結果集
--調用執(zhí)行存儲過程岗仑，得到返回集(exec或者execute均可)
exec proc_get_student;
2.2匹耕、修改存儲過程
--修改存儲過程
alter proc proc_get_student
as
select * from student; --修改后的SQL語句
--調用執(zhí)行存儲過程，得到返回集(exec或者execute均可)
exec proc_get_student;
2.3荠雕、帶參數存儲過程
--創(chuàng)建一個返回結果集的存儲過程（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
--調用執(zhí)行存儲過程稳其，2,4為參數
exec proc_find_stu 2, 4;
2.4、帶通配符參數存儲過程
--創(chuàng)建一個返回結果集的存儲過程（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
--調用執(zhí)行存儲過程
exec proc_findStudentByName;
exec proc_findStudentByName '%o%', 't%';
2.5炸卑、帶輸出參數存儲過程
--創(chuàng)建一個返回結果集的存儲過程（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
--調用執(zhí)行存儲過程
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系統外通過執(zhí)行動態(tài)鏈接庫盖文，即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實例運行時登陸的安全配置
