什麼是儲存過程？

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

顯示預設值、未加密的儲存過程、使用者定義的儲存過程、觸發器或檢視的實際文字。

EXECsp_databases/*列出當前系統中的資料庫*/

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

USEstuDB

GO

EXECsp_tables/*當前資料庫中查詢的物件的列表*/

EXECsp_columnsstuInfo/*返回某個表列的資訊*/

EXECsp_helpstuInfo/*查看錶stuInfo的資訊*/

EXECsp_helpconstraintstuInfo/*查看錶stuInfo的約束*/

EXECsp_helpindexstuMarks/*查看錶stuMarks的索引*/

EXECsp_helptext"view_stuInfo_stuMarks"/*檢視檢視的語句文字*/

EXECsp_stored_procedures/*檢視當前資料庫中的儲存過程*/

常用的擴充套件儲存過程

q常用的擴充套件儲存過程：xp_cmdshell

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

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

q呼叫語法：

qEXECxp_cmdshellDOS命令[NO_OUTPUT]

USEmaster

GO

EXECxp_cmdshell"mkdird:/bank",NO_OUTPUT/*建立資料夾D:/bank*/

IFEXISTS(SELECT*FROMsysdatabases

WHEREname="bankDB")

DROPDATABASEbankDB

GO

CREATEDATABASEbankDB

(

…

)

GO

EXECxp_cmdshell"dirD:/bank/"--檢視檔案/*檢視資料夾D:/bank*/

如何建立儲存過程？

q定義儲存過程的語法

CREATEPROC[EDURE]儲存過程名

@引數1資料型別=預設值,

……,

@引數n資料型別OUTPUT

AS

SQL語句

GO

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

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

q輸入引數允許有預設值

建立不帶引數的儲存過程

CREATEPROCEDUREproc_stu/*proc_stu為儲存過程的名稱*/

AS

DECLARE@writtenAvgfloat,@labAvgfloat/*筆試平均分和機試平均分變數*/

SELECT@writtenAvg=AVG(writtenExam),

@labAvg=AVG(labExam)FROMstuMarks

print"筆試平均分："+convert(varchar(5),@writtenAvg)

print"機試平均分："+convert(varchar(5),@labAvg)

IF(@writtenAvg>70AND@labAvg>70)

print"本班考試成績：優秀"/*顯示考試成績的等級*/

ELSE

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

print"--------------------------------------------------"

print"參加本次考試沒有透過的學員："

SELECTstuName,stuInfo.stuNo,writtenExam,labExam/*顯示未透過的學員*/

FROMstuInfoINNERJOINstuMarksON

stuInfo.stuNo=stuMarks.stuNo

WHEREwrittenExam<60ORlabExam<60

GO

呼叫儲存過程

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

q呼叫的語法

EXEC過程名[引數]

建立帶引數的儲存過程

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

q輸入引數

q輸出引數

q輸入引數：

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

q輸出引數：

用於在呼叫儲存過程後，

返回結果，類似C語言的

按引用傳遞;

帶輸入引數的儲存過程

問題：

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

分析：

在述儲存過程新增2個輸入引數：

@writtenPass筆試及格線

@labPass機試及格線

CREATEPROCEDUREproc_stu

@writtenPassint=60,/*輸入引數：筆試及格線*/

@labPassint=60/*輸入引數：機試及格線*/

AS

print"--------------------------------------------------"

print"參加本次考試沒有透過的學員："

SELECTstuName,stuInfo.stuNo,writtenExam,/*查詢沒有透過考試的學員*/

labExamFROMstuInfo

INNERJOINstuMarksON

stuInfo.stuNo=stuMarks.stuNo

WHEREwrittenExam<@writtenPass

ORlabExam<@labPass

GO

q呼叫帶引數的儲存過程

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

EXECproc_stu60,55

--或這樣呼叫：

EXECproc_stu@labPass=55,@writtenPass=60

帶輸出引數的儲存過程

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

問題：

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

CREATEPROCEDUREproc_stu

@notpassSumintOUTPUT,/*輸出（返回）引數：表示沒有透過的人數*/

@writtenPassint=60,/*推薦將預設引數放在最後*/

@labPassint=60

AS

……

SELECTstuName,stuInfo.stuNo,writtenExam,/*統計並返回沒有透過考試的學員人數*/

labExamFROMstuInfoINNERJOINstuMarks

ONstuInfo.stuNo=stuMarks.stuNo

WHEREwrittenExam<@writtenPass

ORlabExam<@labPass

SELECT@notpassSum=COUNT(stuNo)

FROMstuMarksWHEREwrittenExam<@writtenPass

ORlabExam<@labPass

GO

q呼叫帶輸出引數的儲存過程

/*---呼叫儲存過程----*/

DECLARE@sumint/*呼叫時必須帶OUTPUT關鍵字，返回結果將存放在變數@sum中*/

EXECproc_stu@sumOUTPUT,64

print"--------------------------------------------------"

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

print"未透過人數："+convert(varchar(5),@sum)+"人,

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

ELSE

print"未透過人數："+convert(varchar(5),@sum)+"人,

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

GO

注意：呼叫時也必須跟隨關鍵字OUTPUT，否則SQLServer將視為輸入引數。

處理儲存過程中的錯誤

q可以使用PRINT語句顯示錯誤資訊，但這些資訊是臨時的，只能顯示給使用者

qRAISERROR顯示使用者定義的錯誤資訊時

q可指定嚴重級別，

q設定系統變數@@ERROR

q記錄所發生的錯誤等

qRAISERROR語句的用法如下：

RAISERROR(msg_id|msg_str,severity,

stateWITHoption[,...n]])

?msg_id：在sysmessages系統表中指定使用者定義錯誤資訊

?msg_str：使用者定義的特定資訊，最長255個字元

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

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

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

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

問題：

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

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

CREATEPROCEDUREproc_stu

@notpassSumintOUTPUT,--輸出引數

@writtenPassint=60,--預設引數放後

@labPassint=60--預設引數放後

AS

IF(NOT@writtenPassBETWEEN0AND100)

OR(NOT@labPassBETWEEN0AND100)

/*引發系統錯誤，指定錯誤的嚴重級別16，呼叫狀態為1（預設）,並影響@@ERROR系統變數的值*/

BEGIN

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

數，統計中斷退出",16,1)

RETURN---立即返回，退出儲存過程

END

…..其他語句同上例，略

GO

/*---呼叫儲存過程，測試RAISERROR語句----*/

DECLARE@sumint,@tint

EXECproc_stu@sumOUTPUT,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關鍵字