什么是存儲過程？

q存儲過程（procedure）類似于C語言中的函數

q用來執行管理任務或應用復雜的業務規則

q存儲過程可以帶參數，也可以返回結果

q存儲過程可以包含數據操縱語句、變量、邏輯控制語句等

存儲過程的優點

(1)執行速度快。

存儲過程創建是就已經通過語法檢查和性能優化，在執行時無需每次編譯。

存儲在數據庫服務器，性能高。

(2)允許模塊化設計。

只需創建存儲過程一次并將其存儲在數據庫中，以后即可在程序中調用該過程任意次。存儲過程可由在數據庫編程方面有專長的人員創建，并可獨立于程序源代碼而單獨修改。

(3)提高系統安全性。

可將存儲過程作為用戶存取數據的管道。可以限制用戶對數據表的存取權限，建立特定的存儲過程供用戶使用，完成對數據的訪問。

存儲過程的定義文本可以被加密，使用戶不能查看其內容。

(4)減少網絡流量：

一個需要數百行Transact-SQL代碼的操作由一條執行過程代碼的單獨語句就可實現，而不需要在網絡中發送數百行代碼。

存儲過程的分類

q系統存儲過程

q由系統定義，存放在master數據庫中

q類似C語言中的系統函數

q系統存儲過程的名稱都以“sp_”開頭或”xp_”開頭

q用戶自定義存儲過程

q由用戶在自己的數據庫中創建的存儲過程

q類似C語言中的用戶自定義函數

常用的系統存儲過程

系統存儲過程	說明
sp_databases	列出服務器上的所有數據庫。
sp_helpdb	報告有關指定數據庫或所有數據庫的信息
sp_renamedb	更改數據庫的名稱
sp_tables	返回當前環境下可查詢的對象的列表
sp_columns	回某個表列的信息
sp_help	查看某個表的所有信息
sp_helpconstraint	查看某個表的約束
sp_helpindex	查看某個表的索引
sp_stored_procedures	列出當前環境中的所有存儲過程。
sp_password	添加或修改登錄帳戶的密碼。
sp_helptext	顯示默認值、未加密的存儲過程、用戶定義的存儲過程、觸發器或視圖的實際文本。

EXEC sp_databases /*列出當前系統中的數據庫*/

EXECsp_renamedb 'Northwind','Northwind1' /*修改數據庫的名稱(單用戶訪問,最簡單的辦法就是執行SQL語句時關掉企業管理器)*/

USE stuDB

GO

EXEC sp_tables /*當前數據庫中查詢的對象的列表*/

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/*查看當前數據庫中的存儲過程*/

常用的擴展存儲過程

q常用的擴展存儲過程：xp_cmdshell

q可以執行DOS命令下的一些的操作

q以文本行方式返回任何輸出

q調用語法：

qEXEC xp_cmdshell DOS命令[NO_OUTPUT]

USE master

GO

EXEC xp_cmdshell 'mkdir d:/bank', NO_OUTPUT /*創建文件夾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*/

如何創建存儲過程？

q定義存儲過程的語法

CREATEPROC[EDURE]存儲過程名

@參數1數據類型=默認值,

…… ,

@參數n數據類型OUTPUT

AS

SQL語句

GO

q和C語言的函數一樣，參數可選

q參數分為輸入參數、輸出參數

q輸入參數允許有默認值

創建不帶參數的存儲過程

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 '本班考試成績：優秀'/*顯示考試成績的等級*/

ELSE

print '本班考試成績：較差'

print '--------------------------------------------------'

print '參加本次考試沒有通過的學員：'

SELECT stuName,stuInfo.stuNo,writtenExam,labExam /*顯示未通過的學員*/

FROMstuInfoINNER JOIN stuMarks ON

stuInfo.stuNo=stuMarks.stuNo

WHERE writtenExam<60 OR labExam<60

GO

調用存儲過程

qEXECUTE（執行）語句用來調用存儲過程

q調用的語法

EXEC過程名[參數]

創建帶參數的存儲過程

q存儲過程的參數分兩種：

q輸入參數

q輸出參數

q輸入參數：

用于向存儲過程傳入值，類似C語言的按值傳遞;

q輸出參數：

用于在調用存儲過程后，

返回結果，類似C語言的

按引用傳遞;

帶輸入參數的存儲過程

問題：

修改上例：由于每次考試的難易程度不一樣，每次筆試和機試的及格線可能隨時變化（不再是60分），這導致考試的評判結果也相應變化。

分析：

在述存儲過程添加2個輸入參數：

@writtenPass筆試及格線

@labPass機試及格線

CREATE PROCEDURE proc_stu

@writtenPass int = 60,/*輸入參數：筆試及格線*/

@labPass int = 60/*輸入參數：機試及格線*/

AS

print '--------------------------------------------------'

print '參加本次考試沒有通過的學員：'

SELECT stuName,stuInfo.stuNo,writtenExam, /*查詢沒有通過考試的學員*/

labExamFROMstuInfo

INNER JOIN stuMarks ON

stuInfo.stuNo=stuMarks.stuNo

WHERE writtenExam<@writtenPass

OR labExam<@labPass

GO

q調用帶參數的存儲過程

假定本次考試機試偏難，機試的及格線定為55分，筆試及格線定為60分

EXEC proc_stu 60,55

--或這樣調用：

EXEC proc_stu @labPass=55,@writtenPass=60

帶輸出參數的存儲過程

q如果希望調用存儲過程后，返回一個或多個值，這時就需要使用輸出（OUTPUT）參數了

問題：

修改上例，返回未通過考試的學員人數。

CREATE PROCEDURE proc_stu

@notpassSum int OUTPUT, /*輸出（返回）參數：表示沒有通過的人數*/

@writtenPass int=60,/*推薦將默認參數放在最后*/

@labPass int=60

AS

……

SELECT stuName,stuInfo.stuNo,writtenExam, /*統計并返回沒有通過考試的學員人數*/

labExam FROMstuInfoINNER JOIN stuMarks

ON stuInfo.stuNo=stuMarks.stuNo

WHERE writtenExam<@writtenPass

OR labExam<@labPass

SELECT @notpassSum=COUNT(stuNo)

FROM stuMarksWHERE writtenExam<@writtenPass

OR labExam<@labPass

GO

q調用帶輸出參數的存儲過程

/*---調用存儲過程----*/

DECLARE @sum int /*調用時必須帶OUTPUT關鍵字，返回結果將存放在變量@sum中*/

EXEC proc_stu @sum OUTPUT ,64

print '--------------------------------------------------'

IF @sum>=3 /*后續語句引用返回結果*/

print '未通過人數：'+convert(varchar(5),@sum)+ '人,

超過60%,及格分數線還應下調'

ELSE

print '未通過人數：'+convert(varchar(5),@sum)+ '人,

已控制在60%以下，及格分數線適中'

GO

注意：調用時也必須跟隨關鍵字OUTPUT，否則SQL Server將視為輸入參數。

處理存儲過程中的錯誤

q可以使用PRINT語句顯示錯誤信息，但這些信息是臨時的，只能顯示給用戶

qRAISERROR顯示用戶定義的錯誤信息時

q可指定嚴重級別，

q設置系統變量@@ERROR

q記錄所發生的錯誤等

qRAISERROR語句的用法如下：

RAISERROR (msg_id | msg_str,severity,

state WITH option[,...n]])

?msg_id：在sysmessages系統表中指定用戶定義錯誤信息

?msg_str：用戶定義的特定信息，最長255個字符

?severity：定義嚴重性級別。用戶可使用的級別為0–18級

?state：表示錯誤的狀態，1至127之間的值

?option：指示是否將錯誤記錄到服務器錯誤日志中

RAISERROR語句每個參數的詳細講解，可以查閱SQL幫助!

問題：

完善上例，當用戶調用存儲過程時，傳入的及格線參數不

在0～100之間時，將彈出錯誤警告，終止存儲過程的執行。

CREATE PROCEDURE proc_stu

@notpassSum int OUTPUT, --輸出參數

@writtenPass int=60,--默認參數放后

@labPass int=60--默認參數放后

AS

IF (NOT @writtenPass BETWEEN 0 AND 100)

OR (NOT @labPass BETWEEN 0 AND 100)

/*引發系統錯誤，指定錯誤的嚴重級別16，調用狀態為1（默認）,并影響@@ERROR系統變量的值*/

BEGIN

RAISERROR (‘及格線錯誤，請指定0－100之間的分

數，統計中斷退出',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/*如果執行了RAISERROR，系統全局@@ERROR將不等于0，表示出現錯誤*/

RETURN--退出批處理，后續語句不再執行

print '--------------------------------------------------'

IF @sum>=3

print '未通過人數：'+convert(varchar(5),@sum)+ '人,超過60%,及格分數線還應下調'

ELSE

print '未通過人數：'+convert(varchar(5),@sum)+ '人,已控制在60%以下，及格分數線適中'

GO

好，我們來做個總結：

?存儲過程是一組預編譯的SQL語句，它可以包含數據操縱語句、變量、邏輯控制語句等

?存儲過程允許帶參數，參數分為：

–輸入參數

–輸出參數

其中，輸入參數可以有默認值。

?輸入參數：可以在調用時向存儲過程傳遞參數，此類參數可用來向存儲過程中傳入值

?輸出參數從存儲過程中返回（輸出）值，后面跟隨OUTPUT關鍵字

?RAISERROR語句用來向用戶報告錯誤

總結

以上是生活随笔為你收集整理的SQL Server创建存储过程（转载）的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。