存儲過程語法、概念、介紹；如何處理並記錄複雜存儲過程中發生的錯誤 ...
創建於2016-12-24 16:12:19
存儲過程
概念：
1.存儲過程是在資料庫管理系統中保存的、預先編譯的、能實現某種功能的SQL程式，它是資料庫應用中運用比較廣泛的
一種數據對象。
2.存儲過程是SQL語句和控制語句的預編譯集合，保存在資料庫里可由應用程式調用執行，而且允許用戶聲明變數、邏輯
控制語句及其他強大的編程功能。
3.存儲過程可包含邏輯控制語句和數據操作語句，它可以接收參數、輸出參數、返回單個或多個結果集及返回值。
4.存儲過程可以只包含一條select語句，也可以包含一系列使用控制流的SQL語句。存儲過程可以包含個別或全部的控制
流語句。
特點：
1.模塊化程式設計：
只需創建一次存儲過程並將其存儲在資料庫中，以後即可在程式中反覆調用該存儲過程。
2.執行速度快，效率高：
如果某操作需要大量的T-SQL代碼或需要重覆執行，則存儲過程將比T-SQL批處理代碼的執行速度更快。因為存儲
過程只在創建的時候編譯一次，而批處理代碼每次執行的時候都需要編譯。
3.減少網路流量：
使用存儲過程後，一個需要數百行T-SQL代碼的操作，由一條執行過程代碼的單獨語句即可實現，而不需要再網路
中發送數百行代碼。
4.具有良好的安全性：
即使對於沒有直接執行存儲過程中語句的許可權的用戶，也可授予他們執行該存儲過程的許可權。不同的用戶使用不同
的存儲過程。
5.存儲過程分為兩類：
系統存儲過程和用戶自定義的存儲過程
系統存儲過程
概括：SQL Server提供系統存儲過程，它們是一組預編譯的T-SQL語句。系統存儲過程提供了管理資料庫和更新表的機制，並
充當從系統表中檢索信息的快捷方式。
常用的系統存儲過程：
SQL Server的系統存儲過程的名稱以“sp_”開頭，並存放在Resource資料庫中。下麵列出一些常用的系統存儲過程↓
| 系統存儲過程 | 說明 |
| sp_databases | 列出伺服器上的所有資料庫信息，包括資料庫名稱和資料庫大小 |
| sp_ helpdb | 報告有關指定資料庫或所有資料庫的信息 |
| sp_renamedb | 更改資料庫的名稱 |
| sp_ tables | 返回當前環境下可查詢的表或視圖的信息 |
| sp_ columns | 返回某個表或視圖的列信息，包括列的數據類型和長度等 |
| sp_help | 查看某個資料庫對象的信息，如列名、主鍵、約束、外鍵、索引等 |
| sp_helpconstraint | 查看某個表的約束 |
| sp_helpindex | 查看某個表的索引 |
| sp_stored_procedures | 顯示存儲過程的列表 |
| sp_password | 添加或修改登錄賬戶的密碼 |
| sp_helptext | 顯示預設值、未加密的存儲過程、用戶定義的存儲過程、 觸發器和視圖的實際文本 |
調用執行存儲過程：
語法： exec[ute] [返回變數=] 存儲過程名
[@參數1=]參數值1 [output] | [default],
......,
[@參數n=]參數值n [output] | [default]
其中，exec是execute的簡寫
如果執行存儲過程的語句時批處理中的第一個語句，則可以省略execute關鍵字。
如果有返回值的話，可以用一個變數接收。但是在存儲過程內return只能返回數值類型。
output表明參數是輸出參數，default表示參數的預設值。
如果不按照參數順序傳遞參數值，則要指定參數名。
一旦某個參數按照"@參數名=參數值"格式傳遞數據，那麼該參數之後的其他參數都必須以同樣的格式傳遞參數
值。
常用的擴展存儲過程：
擴展存儲過程是SQL Server提供的各類系統存儲過程中的一類，允許使用其他編程語言創建外部存儲過程，為資料庫用
戶提供從SQL Server實例到外部程式的介面，以便進行各種維護活動。通常以"xp_"作為首碼，以DLL形式單獨存在。
語法： exec xp_cmdshell DOS命令 [no_output]
其中，exec表示調用存儲過程，no_output為可選參數，設置執行DOS命令後是否輸出返回信息。
用戶自定義的存儲過程
組成：
1.輸入參數和輸出參數。
2.在存儲過程中執行的T-SQL語句。
3.存儲過程的返回值。
語法：
1.創建語法：
create proc[edure] 存儲過程名
[{@參數1 數據類型} [=預設值] [output],
......,
{@參數n 數據類型} [=預設值] [output]
]
as
SQL語句
2.刪除語法：
drop proc[edure] 存儲過程名
3.判斷存儲過程是否存在，如果存在，則刪除
if exists(select * from sysobjects where name = 存儲過程名)
drop proc[edure] 存儲過程名
go
語法釋義：
1.創建：
proc是procedure的簡寫，參數的預設值可選，不寫則沒有預設值，調用執行時必須帶有此參數。output
表示是輸出參數，即執行存儲過程後把返回值存放在輸出參數中。就像C#中的引用傳遞一樣。輸出參數也
可以設置預設值。不寫output則為輸入參數，就像C#中的值傳遞一樣。
另外需註意，存儲過程中參數的預設值不能是系統函數。比如參數是時間類型，預設值不能是
GETDATE()
處理錯誤信息
概念：在存儲過程中，可以使用print語句顯式用戶定義的錯誤信息，但這些是臨時的，並不保存錯誤信息。使用raiserror語句
可以指定嚴重級別並設置系統變數記錄所發生的錯誤。
raiserror語句：
語法：
raiserror ({msg_id | msg_str} {, severity, state} [with option [...n]])
語法釋義：
msg_id： 在sysmessages系統表中指定的用戶定義錯誤信息。
msg_str： 用戶定義的特定信息，最長為255個字元。
severity： 與特定信息關聯，表示用戶定義的嚴重性級別。用戶可使用的級別為0~18級；19~25級是
為sysadmin固定角色的成員預留的，並且需要指定with log選項；20~25級被認為是致命
錯誤。
state： 表示錯誤的狀態，是1~255中的值。
option： 錯誤的自定義選項，可以是下列任一值
a. log： 在Microsoft SQL Server資料庫引擎實例的錯誤日誌和應用程式日誌中記
錄錯誤。
b. nowait： 將消息立即發送給客戶端。
c. seterror： 將@@error值和error_nummber值設置為msg_id或50000，不用考慮
嚴重級別。
