什么是存儲過程？
q 存儲過程（procedure）類似于C語言中的函數(shù)
q 用來執(zhí)行管理任務或應用復雜的業(yè)務規(guī)則
q 存儲過程可以帶參數(shù)，也可以返回結果
q 存儲過程可以包含數(shù)據(jù)操縱語句、變量、邏輯 控制語句等
存儲過程的優(yōu)點
(1)執(zhí)行速度快。
存儲過程創(chuàng)建是就已經(jīng)通過語法檢查和性能優(yōu)化，在執(zhí)行時無需每次編譯。
存儲在數(shù)據(jù)庫服務器，性能高。
(2)允許模塊化設計。
只需創(chuàng)建存儲過程一次并將其存儲在數(shù)據(jù)庫中，以后即可在程序中調用該過程任意次。存儲過程可由在數(shù)據(jù)庫編程方面有專長的人員創(chuàng)建，并可獨立于程序源代碼而單獨修改 。
(3)提高系統(tǒng)安全性。
可將存儲過程作為用戶存取數(shù)據(jù)的管道。可以限制用戶對數(shù)據(jù)表的存取權限，建立特定的存儲過程供用戶使用，完成對數(shù)據(jù)的訪問。
存儲過程的定義文本可以被加密，使用戶不能查看其內容。
(4)減少網(wǎng)絡流量：
一個需要數(shù)百行Transact-SQL代碼的操作由一條執(zhí)行過程代碼的單獨語句就可實現(xiàn)，而不需要在網(wǎng)絡中發(fā)送數(shù)百行代碼。
存儲過程的分類
q 系統(tǒng)存儲過程
q 由系統(tǒng)定義，存放在master數(shù)據(jù)庫中
q 類似C語言中的系統(tǒng)函數(shù)
q 系統(tǒng)存儲過程的名稱都以“sp_”開頭或”xp_”開頭
q 用戶自定義存儲過程
q 由用戶在自己的數(shù)據(jù)庫中創(chuàng)建的存儲過程
q 類似C語言中的用戶自定義函數(shù)
常用的系統(tǒng)存儲過程
系統(tǒng)存儲過程
說明
sp_databases
列出服務器上的所有數(shù)據(jù)庫。
sp_helpdb
報告有關指定數(shù)據(jù)庫或所有數(shù)據(jù)庫的信息
sp_renamedb
更改數(shù)據(jù)庫的名稱
sp_tables
返回當前環(huán)境下可查詢的對象的列表
sp_columns
回某個表列的信息
sp_help
查看某個表的所有信息
sp_helpconstraint
查看某個表的約束
sp_helpindex
查看某個表的索引
sp_stored_procedures
列出當前環(huán)境中的所有存儲過程。
sp_password
添加或修改登錄帳戶的密碼。
sp_helptext
顯示默認值、未加密的存儲過程、用戶定義的存儲過程、觸發(fā)器或視圖的實際文本。
EXEC sp_databases /*列出當前系統(tǒng)中的數(shù)據(jù)庫*/
EXEC sp_renamedb 'Northwind','Northwind1' /*修改數(shù)據(jù)庫的名稱(單用戶訪問, 最簡單的辦法就是執(zhí)行SQL語句時關掉企業(yè)管理器)*/
USE stuDB
GO
EXEC sp_tables /*當前數(shù)據(jù)庫中查詢的對象的列表*/
EXEC sp_columns stuInfo /*返回某個表列的信息*/
EXEC sp_help stuInfo /*查看表stuInfo的信息*/
EXEC sp_helpconstraint stuInfo /*查看表stuInfo的約束*/
EXEC sp_helpindex stuMarks /*查看表stuMarks的索引*/
EXEC sp_helptext 'view_stuInfo_stuMarks' /*查看視圖的語句文本*/
EXEC sp_stored_procedures /*查看當前數(shù)據(jù)庫中的存儲過程*/
常用的擴展存儲過程
q 常用的擴展存儲過程：xp_cmdshell
q 可以執(zhí)行DOS命令下的一些的操作
q 以文本行方式返回任何輸出
q 調用語法：
q EXEC xp_cmdshell DOS命令 [NO_OUTPUT]
USE master
GO
EXEC xp_cmdshell 'mkdir d:\bank', NO_OUTPUT /*創(chuàng)建文件夾D:\bank*/
IF EXISTS(SELECT * FROM sysdatabases
WHERE name='bankDB')
DROP DATABASE bankDB
GO
CREATE DATABASE bankDB
(
…
)
GO
EXEC xp_cmdshell 'dir D:\bank\' --查看文件 /*查看文件夾D:\bank*/
如何創(chuàng)建存儲過程？
q 定義存儲過程的語法
CREATE PROC[EDURE] 存儲過程名
@參數(shù)1 數(shù)據(jù)類型 = 默認值,
…… ,
@參數(shù)n 數(shù)據(jù)類型 OUTPUT
AS
SQL語句
GO
q 和C語言的函數(shù)一樣，參數(shù)可選
q 參數(shù)分為輸入?yún)?shù)、輸出參數(shù)
q 輸入?yún)?shù)允許有默認值
創(chuàng)建不帶參數(shù)的存儲過程
CREATE PROCEDURE proc_stu /* proc_stu為存儲過程的名稱*/
AS
DECLARE @writtenAvg float,@labAvg float /* 筆試平均分和機試平均分變量 */
SELECT @writtenAvg=AVG(writtenExam),
@labAvg=AVG(labExam) FROM stuMarks
print '筆試平均分：'+convert(varchar(5),@writtenAvg)
print '機試平均分：'+convert(varchar(5),@labAvg)
IF (@writtenAvg>70 AND @labAvg>70)
print '本班考試成績：優(yōu)秀' /* 顯示考試成績的等級 */
ELSE
print '本班考試成績：較差'
print '--------------------------------------------------'
print ' 參加本次考試沒有通過的學員：'
SELECT stuName,stuInfo.stuNo,writtenExam,labExam /* 顯示未通過的學員 */
FROM stuInfo INNER JOIN stuMarks ON
stuInfo.stuNo=stuMarks.stuNo
WHERE writtenExam<60 OR labExam<60
GO
調用存儲過程
q EXECUTE（執(zhí)行）語句用來調用存儲過程
q 調用的語法
EXEC 過程名 [參數(shù)]
創(chuàng)建帶參數(shù)的存儲過程
q 存儲過程的參數(shù)分兩種：
q 輸入?yún)?shù)
q 輸出參數(shù)
q 輸入?yún)?shù)：
用于向存儲過程傳入值，類似C語言的按值傳遞;
q 輸出參數(shù)：
用于在調用存儲過程后，
返回結果，類似C語言的
按引用傳遞;
帶輸入?yún)?shù)的存儲過程
問題：
修改上例：由于每次考試的難易程度不一樣，每次 筆試和機試的及格線可能隨時變化（不再是60分），這導致考試的評判結果也相應變化。
分析：
在述存儲過程添加2個輸入?yún)?shù)：
@writtenPass 筆試及格線
@labPass 機試及格線
CREATE PROCEDURE proc_stu
@writtenPass int = 60, /*輸入?yún)?shù)：筆試及格線*/
@labPass int = 60 /*輸入?yún)?shù)：機試及格線*/
AS
print '--------------------------------------------------'
print ' 參加本次考試沒有通過的學員：'
SELECT stuName,stuInfo.stuNo,writtenExam, /*查詢沒有通過考試的學員*/
labExam FROM stuInfo
INNER JOIN stuMarks ON
stuInfo.stuNo=stuMarks.stuNo
WHERE writtenExam<@writtenPass
OR labExam<@labPass
GO
q 調用帶參數(shù)的存儲過程
假定本次考試機試偏難，機試的及格線定為55分，筆試及格線定為60分
EXEC proc_stu 60,55
--或這樣調用：
EXEC proc_stu @labPass=55,@writtenPass=60
帶輸出參數(shù)的存儲過程
q 如果希望調用存儲過程后，返回一個或多個值，這時就需要使用輸出（OUTPUT）參數(shù)了
問題：
修改上例，返回未通過考試的學員人數(shù)。
CREATE PROCEDURE proc_stu
@notpassSum int OUTPUT, /*輸出（返回）參數(shù)：表示沒有通過的人數(shù)*/
@writtenPass int=60, /*推薦將默認參數(shù)放在最后*/
@labPass int=60
AS
……
SELECT stuName,stuInfo.stuNo,writtenExam, /*統(tǒng)計并返回沒有通過考試的學員人數(shù)*/
labExam FROM stuInfo INNER JOIN stuMarks
ON stuInfo.stuNo=stuMarks.stuNo
WHERE writtenExam<@writtenPass
OR labExam<@labPass
SELECT @notpassSum=COUNT(stuNo)
FROM stuMarks WHERE writtenExam<@writtenPass
OR labExam<@labPass
GO
q 調用帶輸出參數(shù)的存儲過程
/*---調用存儲過程----*/
DECLARE @sum int /*調用時必須帶OUTPUT關鍵字 ，返回結果將存放在變量@sum中*/
EXEC proc_stu @sum OUTPUT ,64
print '--------------------------------------------------'
IF @sum>=3 /*后續(xù)語句引用返回結果*/
print '未通過人數(shù)：'+convert(varchar(5),@sum)+ '人,
超過60%,及格分數(shù)線還應下調'
ELSE
print '未通過人數(shù)：'+convert(varchar(5),@sum)+ '人,
已控制在60%以下，及格分數(shù)線適中'
GO
注意：調用時也必須跟隨關鍵字OUTPUT，否則SQL Server將視為輸入?yún)?shù)。
處理存儲過程中的錯誤
q 可以使用PRINT語句顯示錯誤信息，但這 些信息是臨時的，只能顯示給用戶
q RAISERROR 顯示用戶定義的錯誤信息時
q 可指定嚴重級別，
q 設置系統(tǒng)變量@@ERROR
q 記錄所發(fā)生的錯誤等
q RAISERROR語句的用法如下：
RAISERROR (msg_id | msg_str,severity,
state WITH option[,...n]])
• msg_id：在sysmessages系統(tǒng)表中指定用戶定義錯誤信息
• msg_str：用戶定義的特定信息，最長255個字符
• severity：定義嚴重性級別。用戶可使用的級別為0–18級
• state：表示錯誤的狀態(tài)，1至127之間的值
• option：指示是否將錯誤記錄到服務器錯誤日志中
RAISERROR 語句每個參數(shù)的詳細講解，可以查閱SQL幫助!
問題：
完善上例，當用戶調用存儲過程時，傳入的及格線參數(shù)不
在0～100之間時，將彈出錯誤警告，終止存儲過程的執(zhí)行。
CREATE PROCEDURE proc_stu
@notpassSum int OUTPUT, --輸出參數(shù)
@writtenPass int=60, --默認參數(shù)放后
@labPass int=60 --默認參數(shù)放后
AS
IF (NOT @writtenPass BETWEEN 0 AND 100)
OR (NOT @labPass BETWEEN 0 AND 100)
/*引發(fā)系統(tǒng)錯誤，指定錯誤的嚴重級別16，調用狀態(tài)為1（默認）,并影響@@ERROR系統(tǒng)變量的值 */
BEGIN
RAISERROR (‘及格線錯誤，請指定0－100之間的分
數(shù)，統(tǒng)計中斷退出',16,1)
RETURN ---立即返回，退出存儲過程
END
…..其他語句同上例，略
GO
/*---調用存儲過程，測試RAISERROR語句----*/
DECLARE @sum int, @t int
EXEC proc_stu @sum OUTPUT ,604 /*筆試及格線誤輸入604分*/
SET @t=@@ERROR
print '錯誤號：'+convert(varchar(5),@t )
IF @t<>0 /*如果執(zhí)行了RAISERROR，系統(tǒng)全局@@ERROR將不等于0，表示出現(xiàn)錯誤*/
RETURN --退出批處理，后續(xù)語句不再執(zhí)行
print '--------------------------------------------------'
IF @sum>=3
print '未通過人數(shù)：'+convert(varchar(5),@sum)+ '人,超過60%,及格分數(shù)線還應下調'
ELSE
print '未通過人數(shù)：'+convert(varchar(5),@sum)+ '人,已控制在60%以下，及格分數(shù)線適中'
GO
好，我們來做個總結：
• 存儲過程是一組預編譯的SQL語句，它可以包含數(shù)據(jù)操縱語句、變量、邏輯控制語句等
• 存儲過程允許帶參數(shù)，參數(shù)分為：
– 輸入?yún)?shù)
– 輸出參數(shù)
其中，輸入?yún)?shù)可以有默認值。
• 輸入?yún)?shù)：可以在調用時向存儲過程傳遞參數(shù)，此類參數(shù)可用來向存儲過程中傳入值
• 輸出參數(shù)從存儲過程中返回（輸出）值，后面跟隨OUTPUT關鍵字
• RAISERROR語句用來向用戶報告錯誤
本文來自CSDN博客，轉載請標明出處：http://blog.csdn.net/lenotang/archive/2008/11/18/3329593.aspx