SQL Server 存儲過程
by:授客 QQ：1033553122
什麼是存儲過程
存儲過程是資料庫中，一組為了完成特定功能，預編譯的SQL語句集
1. 存儲過程類似于C語言中的函數，可以帶參數，可傳回結果
int sum(int a, int b)
{
int s;
s = a + b;
return s;
}
2.
用來執行管理任務或應用複雜的業務規則
存儲過程的分類
1.
系統存儲過程
a)
由系統定義，存放在master資料庫中
b)
類似C語言中的系統函數
c)
系統存儲過程中的名稱都以“sp_”或“xp_”開頭
d)
傳回0(表成功)，1(表失敗)
使用者自定義存儲過程
由使用者在自己的資料庫中建立的存儲過程
類似C語言中的使用者自定義函數
常用的系統存儲過程
舉例：
#列出目前系統中的資料庫
exec
sp_databases
#修改資料庫的名稱(單使用者通路)
exec sp_renamedb
‘Northwind’,’Northwind1’
#檢視目前資料庫中查詢的對象的清單
exec sp_tables
#傳回某個表列的資訊
exec sp_columns
stuinfo
#檢視表stuinfo的資訊
exec sp_help
#檢視表stuinfo的限制
exec sp_helpconstraint
#檢視表stuMarks的索引
exec sp_helpindex
stuMarks
#檢視視圖的語句文本
exec help_text
‘view_stuinfo_stuMarks’
#檢視目前資料庫中的存儲過程
sp_stored_procedures
常用的存儲過程擴充：xp_cmdshell
1．可以執行dos指令下的一些操作
2．以文本方式傳回任何輸出
3．調用文法：exec
xp_cmdshell Dos指令[NO_OUTPUT]
例子：檢視資料庫檔案（先建立資料庫bankDB，儲存在D:\bank）
exec xp_cmdshell ‘dir
d:\bank\’
如何建立存儲過程
定義存儲過程
create proc[edure]
存儲過程名
@參數1
資料類型=預設值output,
……,
@參數n
資料類型=預設值output
as
sql語句
go
說明：
1.參數分為輸入參數input，輸出參數output，參數可選
2.參數允許有預設值
例子：建立存儲過程，檢視本次考試平均分以及未通過考試的學員名單
create procedure
proc_stu
AS
#聲明平均分和機試平均分變量
DECLARE @wirttenAvg
float,@labAvg flot
SELECT
@writtenAvg=AVG(writtenExam),@labAvg=AVG(labExam) FROM
print ‘筆試平均分：’
+ convert(varchar(5),@writtenAvg)
print ’機試平均分：’
+ convert(varchar(5),@labAvg)
IF(@writtenAvg>70 AND
@labAvg>70)
print ‘本班考試成績：優秀’
ELSE
print’本班考試成績：較差’
print’-------------------------------------------------’
print ‘參加本次考試沒有通過的學員：’
stuName,stuInfo.stuNo,writtenExam,labExam
FROM
FROM stuInfo INNER JOIN
stuMarks ON stuInfo.stuNo=stuMarks.stuNO
WHERE writtenExam<60 OR
labExam<60
調用存儲過程
execute語句來執行存儲過程
文法：exec
過程名 [參數]
exec proc_stu;
存儲過程的參數
輸入參數
用于向存儲過程傳入值，類似C語言的按值傳遞，如圖a,b；
輸出參數
用于在調用存儲過程後，傳回結果，類似C語言的按引用傳遞,如圖中s
例子：修改上例，由于每次考試的難易程度不一樣，每次
筆試和機試的及格線可能随時變化（不再是60分），這導緻考試的評判結果也相應變化
分析：
在述存儲過程添加2個輸入參數：
@writtenPass
筆試及格線
@labPass
機試及格線
CREATE PROCEDURE
@writtenPass int,
#輸入參數：筆試及格線
@labPass int
#輸入參數：機試及格線
‘------------------------------------------------’
print ‘參加本次考試沒通過的學員’
stuName,stuInfo.stuNo,WrittenExam,
labExam FROM
stuInfo
INNER JOIN stuMarks
ON
stuInofo.stuNo=stuMarks.stuNo
WHERE
writtenExam<@writtenPass OR labExam<@labPass
GO
調用帶參數的存儲過程
假定本次考試機試偏難，機試的及格線定為55分，筆試及格線定為60分
EXEC proc_stu
60,55
或EXEC
proc_stu @labPass=55,@wirttenPass=60
建立輸入參數帶預設值的存儲過程
如果試卷的難易程度合适，則調用者還是必須如此調用： EXEC proc_stu
60,60，比較麻煩
這樣調用就比較合理：
EXEC proc_stu 55
#筆試及格線55分，機試及格線預設為60分
EXEC proc_stu
#筆試和機試及格線都預設為标準的60分
@writtenPass
int=60,
@labPass int=60
print’------------------------------------------’
print’參加本次考試沒通過的學員’
stuName,stuInfo.stuNO,writtenExam,labExam
FROM stuInfo
INNER JOIN stuMarks ON
stuInfo.stuNo=stuMarks.stuNO
writtenExam<@writtenPass OR labExam<@labExam
說明：inner
join
等同于等值連接配接=，，僅傳回符合join
on後面的條件的記錄
調用輸入參數帶預設值的存儲過程
EXEC proc_stu
--都采用預設值
64
--機試采用預設值
60,55
--都不采用預設值
注意這裡有個參數的先後順序：也就是說，帶有預設值參數預設放在最後面
--錯誤的調用方式：希望筆試采用預設值，機試及格線55分
EXEC proc_stu ,55
--正确的方法：
@labPass=55
建立帶輸出參數的存儲過程
如果希望調用存儲過程後，傳回一個或多個值，這時就需要使用輸出(output)參數了
例子：修改上例，傳回未通過考試的學員人數
@notpassSum int output
#輸出(傳回)參數，表示沒有通過的人數
@labPass int=60
#推薦将預設參數放後
……
select
from stuInfo INNER JOIN
on
writtenExam<@writtenPass
or
labExam<@labPass
@notpassSum=count(stuNo)
FROM stuMarks where
writtenExam<@writtenPass or labExam<@labPass
調用帶輸出參數的存儲過程
declare @sum int
exec proc_stu @sum
output,64 #調用時必須帶output關鍵詞，傳回結構存放在變量@sum中
print’-----------------------------------------------’
if @sum>=3
print’未通過人數’+convert(varchar(5),@sum)+’人’,’超過60%，及格分數線還應該下調’
else
print’未通過人數:’+convert(varchar(5),@sum)+’人’,’已控制在60%以下，及格分數線适中’
處理存儲過程中的錯誤
1，可以使用print語句顯示錯誤資訊，但這些資訊是臨時的，隻能顯示給使用者
2，可以使用raiserror顯示使用者定義的錯誤資訊時
可以指定嚴重級别
設定系統變量@@error
記錄所反生的錯誤
raiserror用法
raiserror(msg_id|msg_str,severity,state
with option[,…n]])
msg_id:在sysmessages系統表中指定使用者定義的錯誤資訊
msg_str:使用者定義的特定資訊，最長255個字元
severity:定義嚴重性級别，使用者可以使用的級别為0-18級
state：表示錯誤狀态，1-127之間的值
option：訓示是否将錯誤記錄到伺服器錯誤日志中
例子：完善上述例子，當使用者調用存儲過程時，傳入的及格參數不在0-100之間時，将彈出錯誤警告，終止存儲過程的執行。
@notpassSum int
output,
if(not @writtenPass between
0 and 100) or(not @labPass between 0 and 100)
begin
raiserror(‘及格線錯誤,請指定0-100之間的分數,統計中斷退出’,16,1)
return –立即傳回，退出存儲過程
end
…… --其它語句
declare @sum int,@t
int
output,604
set @t=@@ERROR
print ‘錯誤号：’+convert(varcha(5),@t)
if @t<>0
return
print’--------------------------------------------------------’
print ‘未通過人數：’+convert(varchar(5),@sum)+’人’
,超過60%,及格分數線還應下調'
'未通過人數：'+convert(varchar(5),@sum)+
'人,已控制在60%以下，及格分數線适中'
存儲過程的優點
1．執行速度更快
存儲過程建立是就已經通過文法檢查和性能優化，在執行時無需每次編譯。
存儲在資料庫伺服器，性能高。
2．允許子產品化程式設計
隻需建立存儲過程一次并将其存儲在資料庫中，以後即可在程式中調用該過程任意次。存儲過程可由在資料庫程式設計方面有專長的人員建立，并可獨立于程式源代碼而單獨修改
。
3．提高系統安全性
可将存儲過程作為使用者存取資料的管道。可以限制使用者對資料表的存取權限，建立特定的存儲過程供使用者使用，完成對資料的通路。
存儲過程的定義文本可以被加密，使使用者不能檢視其内容
4．減少網絡流量
一個需要數百行Transact-SQL代碼的操作由一條執行過程代碼的單獨語句就可實作，而不需要在網絡中發送數百行代碼。
作者：授客
微信/QQ：1033553122
全國軟體測試QQ交流群：7156436
Git位址：https://gitee.com/ishouke
友情提示：限于時間倉促，文中可能存在錯誤，歡迎指正、評論！
作者五行缺錢，如果覺得文章對您有幫助，請掃描下邊的二維碼打賞作者，金額随意，您的支援将是我繼續創作的源動力，打賞後如有任何疑問，請聯系我!!!
微信打賞
支付寶打賞 全國軟體測試交流QQ群