一、存儲過程

? ? ??存儲過程(procedure)類似于C語言中的函數(shù)，用來執(zhí)行管理任務(wù)或應(yīng)用復(fù)雜的業(yè)務(wù)規(guī)則，存儲過程可以帶參數(shù)，也可以返回結(jié)果。存儲過程可以包含數(shù)據(jù)操縱語句、變量、邏輯控制語句等，比如:單個select語句，select語句塊，select語句與邏輯控制塊。

? ? ? 存儲過程優(yōu)點(diǎn):

? ? ? ? ? ? ?執(zhí)行速度更快

? ? ? ? ? ? ?允許模塊化程序設(shè)計

? ? ? ? ? ? ?提高系統(tǒng)安全性

? ? ? ? ? ? ?減少網(wǎng)絡(luò)流通量

二、存儲過程分類

? ? ? 2.1 系統(tǒng)存儲過程

? ? ? ? ? ? 由系統(tǒng)定義，存放在master數(shù)據(jù)庫中

? ? ? ? ? ? 類似C語言中的系統(tǒng)函數(shù)

? ? ? ? ? ? 系統(tǒng)存儲過程的名稱都以"sp_"開頭或"xp_" 開頭

? ? ? 2.2 用戶自定義存儲過程

? ? ? ? ? ? 由用戶在自己的數(shù)據(jù)庫中創(chuàng)建的存儲過程

? ? ? ? ? ? 類似C語言中的用戶自定義函數(shù)

? ? ? 常用的系統(tǒng)存儲過程如下表所示:

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

? ? ? ? 存儲過程都是exec調(diào)用，調(diào)用上述表中存儲過程如下:

EXEC sp_databases EXEC sp_renamedb 'Northwind','Northwind1' USE stuDB GO EXEC sp_tables EXEC sp_columns stuInfo EXEC sp_help stuInfo EXEC sp_helpconstraint stuInfo EXEC sp_helpindex stuMarks EXEC sp_helptext 'view_stuInfo_stuMarks' EXEC sp_stored_procedures

? ? ? ? ?常用的擴(kuò)展存儲過程:xp_cmdshell,可以執(zhí)行DOS命令下的一些操作，以文本行方式返回任何輸出

? ? ? ? ?調(diào)用語法:

EXEC xp_cmdshell DOS命令 [NO_OUTPUT]

? ? ? ? ?擴(kuò)展存儲過程舉例:

USE master GO EXEC xp_cmdshell 'mkdir d:\bank', NO_OUTPUT IF EXISTS(SELECT * FROM sysdatabases WHERE name='bankDB')DROP DATABASE bankDB GO CREATE DATABASE bankDB {... --創(chuàng)建數(shù)據(jù)庫語法略 } GO EXEC xp_cmdshell 'dir D:\bank\' --查看文件

三、創(chuàng)建存儲過程

? ? ? ?定義存儲過程的語法

CREATE PROC[EDURE] 存儲過程名@參數(shù)1 數(shù)據(jù)類型 = 默認(rèn)值 OUTPUT,......,@參數(shù)n 數(shù)據(jù)類型 = 默認(rèn)值 OUTPUTASSQL語句 GO

? ? ? ?存儲過程和C語言的函數(shù)一樣，參數(shù)可選，參數(shù)分為輸入?yún)?shù)、輸出參數(shù)，輸入?yún)?shù)允許有默認(rèn)值。舉例 :

? ? ? ? 請創(chuàng)建存儲過程，查看本次考試平均分以及未通過考試的學(xué)員名單

CREATE PROCEDURE proc_stuASDECLARE @writtenAvg float,@labAvg float SELECT @writtenAvg=AVG(writtenExam),@labAvg=AVG(labExam) FROM stuMarksprint '筆試平均分:'+convert(varchar(5),@writtenAvg)print '機(jī)試平均分:'+convert(varchar(5),@labAvg)IF (@writtenAvg>70 AND @labAvg>70)print '本班考試成績:優(yōu)秀'ELSEprint '本班考試成績:較差'print '-------------------------------------'print ' 參加本次考試沒有通過的學(xué)員:'SELECT stuName,stuInfo.stuNo,writtenExam,labExamFROM stuInfo INNET JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNoWHERE writtenExam<60 OR labExam<60 GO

四、存儲過程的參數(shù)分為兩種:輸入?yún)?shù)和輸出參數(shù)

? 4.1 輸入?yún)?shù)

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

? ? ? ? 例:

CREATE PROCEDURE proc_stu@writtenPass int,@labPass intASprint '---------------------------'print ' 參加本次考試沒有通過的學(xué)員:'SELECT stuName,stuInfo.stuNo,writtenExam,labExamFROM stuInfoINNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNoWHERE writtenExam<@writtenPass OR labExam<@labPass GO

? ? ? ? 調(diào)用帶輸入?yún)?shù)存儲過程:

EXEC proc_stu 60,55 或者:EXEC proc_stu @labPass=55,@writtenPass=60

? ? ? ? 同樣，還可以設(shè)置默認(rèn)值，如下:

CREATE PROCEDURE proc_stu@writtenPass int=60,@labPass int=60ASprint '-----------------------------'print ' 參加本次考試沒有通過的學(xué)員:'SELECT stuName,stuInfo.stuNo,writtenExam,labExamFROM stuInfoINNET JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNoWHERE writtenExam<@writtenPass OR labExam<@labPass GO

? ? ? ? 調(diào)用帶默認(rèn)參數(shù)的存儲過程:

EXEC proc_stu --都采用默認(rèn)值 EXEC proc_stu 64 --機(jī)試采用默認(rèn)值 EXEC proc_stu 60,55 --都不采用默認(rèn)值 或者:EXEC proc_stu @labPass=55

? ? 4.2 輸出參數(shù)

? ? ? ? ?如果參數(shù)調(diào)用存儲過程后，返回一個或多個值，這時就需要使用輸出(OUTPUT)參數(shù)了

? ? ? ? ?例:

CREATE PROCEDURE proc_stu@notpassSum int OUTPUT,@writtenPass int=60,@labPass int=60AS......SELECT stuName,stuInfo.stuNo,writtenExam,labExamFROM stuInfoINNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNoWHERE writtenExam<@writtenPass OR labExam<@labPassSELECT @notpassSum=COUNT(stuNo)FROM stuMarks WHERE writtenExam<@writtenPass OR labExam<@labPass GO

? ? ? ? 調(diào)用帶輸出參數(shù)的存儲過程

DECLARE @sum int EXEC proc_stu @sum OUTPUT, 64 print '---------------------------------' IF @sum>=3print '未通過人數(shù):'+convert(varchar(5),@sum)+'人，超過60%,及格分?jǐn)?shù)線還應(yīng)下調(diào)' ELSEprint '未通過人數(shù):'+convert(varchar(5),@sum)+'人，已控制在60%以下，及格分?jǐn)?shù)線適中' GO

? ? ? ? 注意接收存儲過程返回值時必須加output關(guān)鍵字

? ? ? ? 刪除存儲過程:drop proc p_test4

五、處理存儲過程中的錯誤

RAISERROR顯示用戶定義的錯誤信息時，可指定嚴(yán)重級別，設(shè)置系統(tǒng)變量@@ERROR,記錄所發(fā)生的錯誤等。

語法如下:

?RAISERROR (msg_id | msg_str,severity,state WITH option[,...n])

msg_id:在sysmessages系統(tǒng)表中指定用戶定義錯誤信息

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

? severity:定義嚴(yán)重性級別。用戶可使用的級別為0-18級

? state:表示錯誤的狀態(tài)，1至127之間的值

? option:指示是否將錯誤記錄到服務(wù)器錯誤日志中

例:

CREATE PROCEDURE proc_stu@notpassSum int OUTPUT, --輸出參數(shù)@writtenPass int=60, --默認(rèn)參數(shù)放后@labPass int=60 --默認(rèn)參數(shù)放后AS IF (NOT @writtenPass BETWEEN 0 AND 100)OR (NOT @labPass BETWEEN 0 AND 100)BEGINRAISERROR ('及格線錯誤，請指定0-100之間的分?jǐn)?shù)，統(tǒng)計中斷退出',16,1)RETURN ---立即返回，退出存儲過程END...其他語句同上例，略 GO

? 調(diào)用該存儲過程:

DECLARE @sum int, @t int EXEC proc_stu @sum OUTPUT, 604 --及格線設(shè)置為604出錯 SET @t=@@ERROR print '錯誤號:'+convert(varchar(5), @t) IF @t<>0RETURN --退出批處理，后續(xù)語句不再執(zhí)行 print '------------------------------------' IF @sum>=3print '未通過人數(shù):'+convert(varchar(5),@sum)+ '人，超過60%,及格分?jǐn)?shù)線還應(yīng)下調(diào)' ELSEprint '未通過人數(shù):'+convert(varchar(5),@sum)+'人，已控制在60%以下，及格分?jǐn)?shù)線適中' GO

創(chuàng)作挑戰(zhàn)賽新人創(chuàng)作獎勵來咯，堅持創(chuàng)作打卡瓜分現(xiàn)金大獎

總結(jié)

以上是生活随笔為你收集整理的记录SQL server学习的存储过程的摘录的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。