--維護數據庫--
--存儲過程(procedure)--
--概述：
SQl Serve的存儲過程是由一個或多個T-SQL語句組成的一個集合。常用的程序代碼段通常被創建成存儲過程，一次創建多次調用，這樣既簡化程序員的工作也減少與服務器交互的網絡通信流量。存儲過程中可以包含數據庫中執行操作的程序語句，也包括調用其他過程。存儲過程可以接收和輸出參數，向調用它的程序返回值。存儲過程被調用後，會返回給調用它的程序狀態值，以表明調用成功或者調用失敗以及調用失敗的原因。
--使用存儲過程的優點：
1）減少網絡流量。在客戶端和服務器的交互中，T-SQL語言中的每個代碼行在執行時都是要利用網絡發送的，代碼被封裝成存儲過程，只有對執行存儲過程語句的調用時才會利用網絡發送。
2）增強安全性。在客戶端和服務器之間調用存儲過程時，只有執行存儲過程的語句是可見的，用戶無法看到或訪問到存儲過程所涉及的數據庫對象，便無法破壞這些對象，使用為存儲過程加密也能保障存儲過程的安全。
3）提升編程效率。存儲過程對常用代碼的封裝采用了消除重復代碼的編寫操作，降低代碼的不一致性，並允許擁有權限的用戶訪問和執行代碼，提升代碼編程效率。
4）提高執行效率。系統默認下，只有首次執行存儲過程時需要編譯存儲過程，創建一個執行計劃，今後在執行該存儲過程時則無需在編譯，節省程序處理時間，提高執行效率。如果存儲過程引用的數據發生變化明，也無需重新編寫存儲過程，系統提供了重新編譯存儲過程來幫助存儲過程正確執行。
--分類：（在SQL Server 中存儲過程分為用戶自定義存儲過程、系統存儲過程和拓展存儲過程三種類型）
1）用戶自定義存儲過程。該存儲過程封裝了用戶所需的功能代碼，可以單純實現一段程序代碼，可以通過輸入參數接收用戶輸入的值(帶輸入參數的存儲過程)，可以使用輸出參數將存儲過程執行後的結果返回給調用它的語句(帶輸出參數的存儲過程)。
2）系統存儲過程。以sp_開頭的存儲過程是系統存儲過程。是系統封裝好的程序代碼。例如：sp_helptext表示查看數據庫對象信息；sp_helpindex表示查看表中索引信息。
3）拓展存儲過程。以xp_開頭的存儲過程是拓展存儲過程。由系統提供，用來在SQL Server和外部程序之間提供一個接口，以實現各種系統的維護活動。例如：xp_loogininfo表示返回Windows用戶和Windows組的相關信息。
--存儲過程管理：
--存儲過程的創建和執行（注：創建存儲過程的語句要存在於同一個批處理中。）
--不帶參數的存儲過程
♦創建：
create procedure 存儲過程名 --create procedure命令表示創建存儲過程，procedure允許只寫前四個字母proc。
[with encryption] --with encryption是可選項，可以為存儲過程的創建文本加密。
[with recompile] --with recompile是可選項，使存儲過程在執行時不保存執行計劃，在每次執行時重新編譯，以防止覆蓋已存在於內存中的執行計劃。
as
T-SQL語句
例：(創建名為“p_客戶信息表_地址”的存儲過程，用來實現在“商品管理數據庫”的“客戶信息表”中查詢“遼寧沈陽”的客戶信息)
use 商品管理數據庫
go
select*from 客戶信息表 where 地址='遼寧沈陽' --創建前可以先查詢一下要封裝的記錄
use 商品管理數據庫
go
create proc p_客戶信息表_地址 --創建存儲過程
as
select*from 客戶信息表 where 地址='遼寧沈陽'
♦執行：
execute 存儲過程名 --execute表示執行存儲過程，可以簡寫exec。
例：
use 商品管理數據庫
go
exec p_客戶信息表_地址
--帶參數的存儲過程（帶輸入參數的存儲過程 和 帶輸出參數的存儲過程）
--1）帶輸入參數的存儲過程
♦創建：
create procedure 存儲過程名 --create procedure命令表示創建存儲過程，procedure允許只寫前四個字母proc。
@參數名 數據類型[(長度)] [, ......] --參數名，應聲明在as之前
[with encryption] --with encryption是可選項，可以為存儲過程的創建文本加密。
[with recompile] --with recompile是可選項，是存儲過程在執行時不保存執行計劃，在每次執行時重新編譯，以防止覆蓋已存在於內存中的執行計劃。
as
T-SQL語句
例：(創建名為“p_客戶信息表_地址x”的存儲過程，用來實現在“商品管理數據庫”的“客戶信息表”中查找指定地址的客戶信息)
use 商品管理數據庫
go
create proc p_客戶信息表_地址x
@address nvarchar(30)
as
select*from 客戶信息表 where 地址=@address
♦執行：
execute 存儲過程名 [@參數名=] 參數值 [, ...] --“參數值”的數據類型必須與被賦值參數的數據類型兼容
例：(執行存儲過程“P_客戶信息表_地址x”，查找出地址“遼寧沈陽”的客戶信息)
use 商品管理數據庫
go
exec p_客戶信息表_地址x @address='遼寧沈陽'
exec p_客戶信息表_地址x '遼寧大連' --省略參數名
--2）帶輸出參數的存儲過程
♦創建：
create procedure 存儲過程名 --create procedure命令表示創建存儲過程，procedure允許只寫前四個字母proc。
[@參數名 數據類型[(長度)][ , ...... , ] ]
@參數名 數據類型[(長度)] output [ , ...... ]
[with encryption]
[with recompile]
as
T-SQl語句
--允許創建只帶輸入參數的存儲過程，也允許創建既帶輸出參數又帶輸入參數的存儲過程。輸出參數要在聲明參數後加上output關鍵字來指明。通常再被封裝在存儲過程中的T-SQL語句中為輸出參數賦值。
例：(創建名為“p_客戶信息表_姓名”的存儲過程，將查詢“客戶信息表”中客戶編號為20130001的客戶信息姓名，並將客戶姓名賦值給一個輸出參數的查詢代碼封裝到該存儲過程中)
create proc p_客戶信息表_姓名
@name nvarchar(5) output
as
set @name=(select 客戶姓名 from 客戶信息表 where 客戶編號='20130001')
--select @name=客戶姓名 from 客戶信息表 where 客戶編號='20130001'
♦執行：
declare @參數名 數據類型[(長度)][, ...] --首先使用declare命令聲明執行過程中所涉及的參數，包括輸入參數和輸出參數
[set @參數=值1] ... --如果使用了輸入參數，用set語句為輸入參數賦值
execute 存儲過程名 [@參數[, ... ,]] @參數 output [, ...]
例：(執行名為“p_客戶信息表_姓名”的存儲過程，要求將查詢“客戶信息表”中的客戶編號為20130001的客戶姓名顯示出來)
use 商品管理數據庫
go
declare @name nvarchar(5)
exec p_客戶信息表_姓名 @name output
print '客戶編號為20130001的客戶姓名為：'+@name
例：帶輸入參數的存儲過程、帶輸出參數的存儲過程 的創建和執行的總例：(創建名為“p_客戶信息表_編號_姓名”的存儲過程，要求將查詢“客戶信息表”中指定客戶編號的客戶姓名顯示出來。並執行存儲過程查看結果) create proc p_客戶信息表_編號_姓名 @num nchar(8),@name nvarchar(5) output --定義了一個輸入參數@num和一個輸出參數@name，數據類型和取值范圍與“客戶信息表”中的“客戶編號”字段和“客戶姓名”字段一致 as select @name=客戶姓名 from 客戶信息表 where 客戶編號=@num use 商品管理數據庫 go declare @num nchar(8),@name nvarchar(5) --定義了@num和@name兩個用來與存儲過程中的參數傳遞和接收值。 set @num='20130001' --使用set命令為變量@num賦值，以便將值傳給輸入參數@num。 exec p_客戶信息表_編號_姓名 @num,@name output --exec執行存儲過程時按順序將兩個變量帶入到存儲過程中。 print '客戶編號為'+@num+'的客戶的姓名為：'+@name --print語句使用了字符串連接運算，輸出詳細結果。理解例子
--查看存儲過程
注：可以使用系統存儲過程sp_helptext、sp_help和sp_depends查看存儲過程的創建信息和創建文本。
exec 系統存儲過程 用戶自定義存儲過程名
說明：sp_helptext顯示被查看的存儲過程的創建文本信息，創建時被加密的存儲過程的創建文本信息不能被查看。
sp_help顯示被查看的而存儲過程的所有者、類型、創建時間及包含哪些參數等信息。
sp_depends顯示被查看的存儲過程所關聯的數據表和字段信息。
例：
use 商品管理數據庫
go
exec sp_depends p_客戶信息表_姓名
exec sp_help p_客戶信息表_姓名
exec sp_helptext p_客戶信息表_姓名
--修改存儲過程
注：在管理數據庫過程中，可以根據需要修改已創建的用戶自定義存儲過程。對於已經加密的存儲過程，不能使用管理器方式修改其內容，擁有權限的用戶可以使用T-SQl語言修改其創建內容，同時將加密屬性去掉，當實質上是刪除原來的加密的存儲過程，重新創建一個新的存儲過程，使用時須謹慎使用。
alter procedure 存儲過程名 ---alter命令表示修改，“存儲過程名”必須是已存在的用戶自定義的存儲過程。procedure允許只寫前四個字母proc。
[@參數名 數據類型[(長度)][, ... ,]] @參數名 數據類型[(長度)] output [, ......]
[with encryption]
[with recompile]
as
T-SQL語句
例：(將名為“p_客戶信息表_姓名”的存儲過程 的功能修改為查找客戶編號為20130003的客戶信息，並將結果傳遞給一個參數)
use 商品管理數據庫
alter proc p_客戶信息表_姓名
@name nvarchar(5) output
as
set @name=(select 客戶姓名 from 客戶信息表 where 客戶編號='20130003')
--刪除存儲過程
注：對於不再使用的存儲過程可以將其刪除，以節省磁盤空間。刪除存儲過程不會對它所涉及的數據表等數據庫對象產生影響，但調用它的語句再次使用時會產生錯誤。
drop procedure 存儲過程名 --drop 命令表示刪除命令，可以刪除加碼和不加密的存儲過程，刪除時並沒有確認是否刪除的提示，需謹慎使用。procedure允許只寫前四個字母proc。
例：(刪除名為“p_進貨信息表_商品編號”的已加密的存儲過程)
use 商品管理數據庫
go
drop proc p_進貨信息表_商品編號
--重編譯存儲過程
注：由於存儲過程執行一次後會把編譯過程記錄到內存中，當再次執行同一個存儲過程時則無需編譯而直接執行。隨著用戶對系統的操作，數據庫中的數據隨時可能發生變化，如果變化涉及了存儲過程所關聯的數據表，那麼存儲過程需要重新編譯，以得到正確的執行結果。重新編譯存儲過程有三種方法。
1）創建存儲過程的同時重新編譯
create procedure 存儲過程名
with recompile --with recompile命令使存儲過程在執行時不保存執行計劃，在每次執行時重新編譯，以防止覆蓋已存在於內存中的執行計劃。
as
T-SQL語句
2）執行存儲過程的同時重新編譯
exec 存儲過程名 with recompile --在執行存儲過程的同時使用with recompile語句，可以使存儲過程在執行時重新編譯。
3）使用命令方式預設存儲過程重新編譯
exec sp_recompile 存儲過程名 --可以為已創建的存儲過程預設重新編譯，即從預設語句執行結束後，下一次執行存儲過程時為存儲過程重新編譯。
例：(使用系統存儲過程sp_recompile為存儲過程“p_客戶信息表_姓名”預設重新編譯)
use 商品管理數據庫
go
exec sp_recompile 'P_客戶信息表_姓名'
注："--"可看成說明或者注釋文本
