任務：了解T-SQL的變量和運算符。學習存儲過程的定義及其特點，創建存儲過程的方法，如何執行存儲過程，查看、修改、刪除存儲過程。

7.1認識T-SQL的變量和運算7.2認識存儲過程7.3創建存儲過程7.3創建存儲過程7.4管理存儲過程7.5知識進階項目實訓任務七提高數據操作速度

__在SQLServer2005中創建和應用存儲過程了解T-SQL的變量和運算符了解什么是存儲過程及其特點掌握創建存儲過程的方法掌握如何執行存儲過程學會查看、修改、刪除存儲過程學習目標創建和應用存儲過程7.1認識T-SQL的變量和運算任務：定義、使用T-SQL的局部變量，了解T-SQL的運算符。7.1.1T-SQL的變量7.1.2T-SQL的運算符創建和應用存儲過程T-SQL語言的變量分為局部變量和全局變量。1．局部變量局部變量使用DECLARE語句聲明，在聲明時它的默認值為NULL，使用SET語句為其賦值。局部變量僅生存于聲明它批中，如存儲過程。處理結束后，局部變量中的信息將丟失。SQLServer使用SELECT語句或PRINT語句來將變量的值提交給用戶，而存儲過程則將變量作為一個Output參數返回?；菊Z法如下：

DECLARE@variable_namedatatype[，@variable_namedatatype][，…n]7.1.1T-SQL的變量其中：

@variable_name局部變量的名稱。以@開始，必須遵循SQL標識符和對象的命名規范，且名字不能使用保留字。

@datatype指定局部變量的數據類型。該數據類型可以是系統數據類型或用戶自定義數據類型。使用SET語句給局部變量賦值：SET@variable_name=expression[,@variable_name=expression]…創建和應用存儲過程【例7-1】定義整型局部變量和一個字符型局部變量并賦值。代碼如下：DECLARE@variable_intint,@variable_charchar(15)SET@variable_int=60SELECT@variable_char='HELLOCHINA'SELECT@variable_intSELECT@variable_char如果被賦值的字符串長度超過DECLARE語句中定義的長度，賦值時超出的部分將被截去。7.1.1T-SQL的變量創建和應用存儲過程2．全局變量全局變量是SQLServer系統所提供并賦值的變量。全局變量的名字以@@開頭。大多數全局變量的值是報告用戶當前會話信息。表7-1給出了SQLServer的常見全局變量?？梢允褂肧ELECT顯示局部變量和全局變量的值（必須首先定義變量）。基本語法如下：顯示局部變量：SELECT@variable_name

顯示全局變量：SELECT@@variable_name7.1.1T-SQL的變量創建和應用存儲過程在SQLServer2005中有一元運算符、賦值運算符、算術運算符、比較運算符、連接運算符、邏輯運算符等。1.一元運算符包括3種：＋（正）、－（負）和～（按位取反）。其中～（按位取反）只可用于整型數據類型的表達式。2.賦值運算符賦值運算符，即等號（=），主要用于為變量賦值。3.算數運算符包括：加（＋）、減（－）、乘（×）、除（÷）和取模（℅）。可以操作的數據類型是int、smallint、tinyint、float、real、money或者smallmoney；加和減運算符可用于對datetime及smalldatetime值執行算數運算；取模（℅）運算操作數的數據類型是int、smallint、tinyint。7.1.2T-SQL的運算符創建和應用存儲過程4.比較運算符包含=、＞、＞＝、＜、＜＝、＜＞（不等于）、()（優先級控制符）?？捎糜诔藅ext、ntext或image數據類型以外的所有的表達式。比較運算符的結果為布爾數據類型，包含true、false兩種值。5.連接運算符連接運算符（＋）用于兩個字符串的連接。字符串連接運算符用來連接char、varchar和text數據類型。6.邏輯運算符包括：AND（與）、OR（或）、NOT（非）邏輯運算符也稱為位運算符。使用邏輯運算符可以把多個條件合并起來，邏輯運算返回的數據類型為布爾型。7.1.2T-SQL的運算符創建和應用存儲過程7.1.2T-SQL的運算符

（）（括號）～（按位取反）正、負乘、除、取模：*、/、%加、連接符、減比較運算符：=、>、<、>=、<=、<>NOT(非)AND(與)OR(或)=（賦值）高低SQLServer中各種運算符的優先級順序創建和應用存儲過程

7.2認識存儲過程任務：了解存儲過程的定義，分析“高職院校教學管理系統”的存儲過程需求。7.2.1存儲過程的概念及特點7.2.2分析“高職院校教學管理系統”的存儲過程需求創建和應用存儲過程7.2.1存儲過程的概念及特點1.什么是存儲過程存儲過程是一組預先編譯好的、能實現特定數據操作功能的SQL代碼集。它與特定數據庫相關聯，一起被存儲在SQLServer服務器上?？蛻舳送ㄟ^應用程序調用執行，可以象使用函數一樣重復使用這此代碼集，實現所定義的操作。存儲過程分為以下3類：⑴系統存儲過程安裝SQLServer時由系統創建的存儲過程。存儲在master數據庫中，其前綴為sp_。⑵用戶自定義存儲過程用戶在用戶數據庫中創建的存儲過程。⑶擴展存儲過程是SQLServer可以動態裝載并執行的動態鏈接庫（DLL），其前綴為xp_，它允許用戶使用DLL訪問SQLServer。用戶使用其他編程語言（如C或C++等）編寫。創建和應用存儲過程7.2.1存儲過程的概念及特點2．存儲過程的特點存儲過程需要先編譯后運行，而再次調用該存儲過程時，不需再次編譯。存儲過程能夠接受參數、嵌套調用另一存儲過程、返回狀態值和調用結果，還可以被遠程數據庫服務器執行。有以下特點：⑴能極大增強SQL語句的功能、效率和靈活性⑵更好地保證數據的完整性和安全性⑶能降低網絡的通信流量⑷能提高應用的開發效率和系統的可維護性創建和應用存儲過程7.2.2分析“高職院校教學管理系統”的存儲過程需求⑴存儲、檢索、維護學生的信息，如查詢有關學生的基本信息、在學生信息表中增加記錄、查詢有關學生的學習成績，平均成績及總成績。⑵存儲、檢索、維護教師的信息，如查詢有關教師的信息、在教師表中增加或刪除教師信息。⑶存儲、檢索、維護班級信息，如查詢某個班的平均成績，查詢某門課程的不及格人數。⑷存儲、檢索、維護課程的信息，如查詢某門課程由哪個教師任教，查詢該課程的平均成績。創建和應用存儲過程7.3創建存儲過程任務：創建帶輸入、輸出參數的存儲過程，應用輸入參數帶入查詢條件，輸出參數帶出統計結果。

7.3.1創建和執行存儲過程的步驟7.3.2創建和調用不帶參數的存儲過程7.3.3創建和調用帶參數的存儲過程創建和應用存儲過程7.3.1創建和執行存儲過程的步驟用戶自定義存儲過程應遵循以下原則：存儲過程的名稱不能使用sp_作前綴。不要為只運行一次的T-SQL命令組構建存儲過程。1．創建存儲過程的模板代碼可使用SSMS創建存儲過程，操作步驟略。2．創建存儲過程的CREATEPROCEDURE語句基本語法如下：CREATEPROC[EDURE]procedure_name[(@parameterdata_type[VARYING][=default][OUTPUT])][,...n][WITH]RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION]ASsql_statment其中：

procedure_name指定存儲過程名稱。

@parameter存儲過程的輸入或輸出參數?？梢月暶饕粋€或多個參數，分為輸入參數和輸出參數。輸入參數負責向存儲過程帶入值，輸出參數將存儲過程的返回值傳遞給變量，向客戶端輸出。

OUTPUT保留字，指定參數為輸出參數，其定義位于所有輸入參數之后。返回值是存儲過程執行完成時參數的當前值。Default為參數指定默認值。WITHRECOMPILE重編譯選項，表明SQLServer不會保存該存儲過程的執行計劃，要求每次執行存儲過程都要重新編譯和優化，并創建新的查詢計劃。ENCRYPTION加密選項。AS指定該存儲過程要執行的操作。通過使用(@)符號作為第一個字符來指定參數名，必須符合有關標識符的規則。一個存儲過程最多可定義2,100個參數創建和應用存儲過程使用T-SQL最常用的執行存儲過程的方法，是使用EXECUTE語句（系統存儲過程可不使用EXECUTE關鍵字）。基本語法如下：[[EXEC[UTE]]procedure_name[value_list]|[@parameter_vari1bleOUTPUT][,...n][withrecompile]其中：

①procedure_name執行的存儲過程名。②value_list輸入參數要帶入存儲過程的參數值列表。③parameter_vari1bleoutput參數變量。若存儲過程返回一個輸出參數，則把其值交給某一參數變量，帶回客戶端。④withrecompile強制每次運行都先重新編譯存儲過程。7.3.1創建和執行存儲過程的步驟創建和應用存儲過程7.3.2創建和調用不帶參數的存儲過程根據“高職院校教學管理系統”中的存儲過程需求分析，創建并調用存儲過程來查詢學生基本信息?！纠?-2】創建存儲過程proc_stu_info，要求該存儲過程返回學生基本信息。代碼如下：

USEJXGLGOCREATEPROCEDUREproc_stu_infoASSELECT*FROMstudent_infoGO

提示：每個存儲過程應該完成一項單獨的工作，這也符合軟件工程的思想。為防止其他用戶看到自己所編寫的存儲過程腳本，創建存儲過程時使用參數WITHENCRYPTION加密存儲過程的定義。創建和應用存儲過程7.3.3創建和調用帶參數的存儲過程用戶創建帶參數的存儲過程，通過參數與調用程序進行通信，輸入參數向該存儲過程傳遞值，由輸出參數將值返回至調用程序。1．創建和調用帶輸入參數的存儲過程【例7-3】創建存儲過程proc_stud_info2，查詢某個學生信息。USEJXGLGOCREATEPROCEDUREproc_stud_info2@s_namenvarchar(20)ASSELECT*FROMstudent_infoWHEREstud_name=@s_nameGO

其中：“N'孫晨湄'”是為輸入參數@s_name指定的值，它將被帶入存儲過程，并出現在應用該參數的位置“stud_name=@s_name”，達到為字段stud_name賦值的目的。創建和應用存儲過程2．創建和調用帶輸出參數的存儲過程在創建存儲過程中定義輸出參數，使存儲過程返回一個或多個值?！纠?-4】創建存儲過程proc_aver_score，查詢某名學生的平均成績。CREATEPROCEDUREproc_aver_score@s_idchar(10),@aver_scorenumeric(5,2)OUTPUTASSELECT@aver_score=avg(grade)FROMstudent_gradeWHEREstud_id=@s_idGO7.3.3創建和調用帶參數的存儲過程創建和應用存儲過程調用以上存儲過程，代碼如下：DECLARE@sidchar(10)--定義參數變量DECLARE@averscorenumeric(5,2)--定義參數變量SET@sid='D06010209'--為參數變量賦值--將參數變量@sid的值傳遞給輸入參數，指定參數變量@averscore將輸出參數的值帶回客戶端EXECproc_aver_score@sid,@averscoreOUTPUTPRINT N'學號為'+CONVERT(CHAR(10),@sid)+ N'的同學的平均成績是：'+CONVERT(char(10),@averscore)

其中：PRINT語句只能輸出字符串，因此在該例中使用轉換函數CONVERT()將數字轉換為字符輸出。7.3.3創建和調用帶參數的存儲過程創建和應用存儲過程【例7-5】創建存儲過程aaddb，實現求任意兩個整數的和。代碼如下：CREATEPROCEDUREaaddb@aINT,@bINT,@cINTOUTPUTASSELECT@c=@a+@bGO執行以下代碼調用存儲過程aaddb。DECLARE@aINTDECLARE@bINTDECLARE@cINTSET@a=130SET@b=6SET@c=50EXECaaddb@a,@b,@cOUTPUTPRINTCONVERT(CHAR(5),@a)+N'與'+CONVERT(CHAR(5),@b)+N'的和等于:'+CONVERT(CHAR(5),@c)GO

其中：在PRIINT語句中的@a、@b、@c均指參數變量，而非存儲過程參數。7.3.3創建和調用帶參數的存儲過程創建和應用存儲過程7.4管理存儲過程任務：使用SSMS及T-SQL兩種方法，管理存儲過程或重新編譯存儲過程。創建“高職院校教學管理系統”需要的存儲過程。7.4.1查看、修改和刪除存儲過程7.4.2重新編譯存儲過程創建和應用存儲過程7.4.1查看、修改和刪除存儲過程1．查看存儲過程存儲過程被創建以后，它的名字存放在當前數據庫的系統表sysobjects中，源代碼存放在系統表syscomments中。（1）通過系統存儲過程查看用戶存儲過程。使用系統存儲過程sp_helptext查看未加密的存儲過程的源代碼?；菊Z法如下：

sp_helptext[@objname=]'name‘使用系統存儲過程sp_help可以查看相關存儲過程的信息。基本語法如下：sp_helpproc_name

（2）使用SSMS查看存儲過程，操作步驟略。創建和應用存儲過程7.4.1查看、修改和刪除存儲過程2．修改存儲過程兩種方法：一種是把舊的存儲過程刪除，然后重新創建該存儲過程；另一種是用單個的步驟修改存儲過程，如更改存儲過程或參數定義，但該存儲過程定義的權限將保留。（1）使用T-SQL語句修改存儲過程。使用ALTERPROCEDURE語句修改已經創建的存儲過程，不會更改權限，也不影響相關的存儲過程或觸發器。

ALTERPROC[EDURE]procedure_name[(@parameterdata_type[VARYING][=default][OUTPUT])][,...n][WITH]RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION]ASsql_statment創建和應用存儲過程3．刪除存儲過程刪除存儲過程可以使用SQL語句，也可以使用SSMS來完成。（1）使用SQL語句刪除存儲過程。使用DROPPROCEDUR語句從當前數據庫中刪除一個或多個存儲過程，基本語法如下：DROPPROCEDUREprocedure_name[,…n]執行代碼：DROPPROCaaddb,proc_stud_info。將存儲過程aaddb和proc_stud_info一起刪除。再次刷新“存儲過程”節點時，這兩個存儲過程在存儲過程列表中已不再存在。（2）使用SSMS刪除存儲過程，操作步驟略。7.4.1查看、修改和刪除存儲過程創建和應用存儲過程7.4.2重新編譯存儲過程為使存儲過程能夠根據數據庫的改變重新變化，要求SQLServer在每次執行存儲過程時都對它重新編譯。1．創建存儲過程時設置重編譯選項

CREATEPROCEDURE...[WITHRECOMPILE]

在每次執行時，SQLServer都對該存儲過程重新編譯和優化，并創建新的查詢計劃?！纠?-6】為JXGL數據庫創建一個帶重編譯選項的存儲過程，用于查詢某學生的成績信息。代碼如下：

CREATEPROCproc_stud_course@s_idchar(10)WITHRECOMPILEASSELECT*FROMstudent_gradeWHEREstud_id=@s_id創建和應用存儲過程7.4.2重新編譯存儲過程為使存儲過程能夠根據數據庫的改變重新變化，要求SQLServer在每次執行存儲過程時都對它重新編譯。1．創建存儲過程時設置重編譯選項

CREATEPROCEDURE...[WITHRECOMPILE]

在每次執行時，SQLServer都對該存儲過程重新編譯和優化，并創建新的查詢計劃?！纠?-6】為JXGL數據庫創建一個帶重編譯選項的存儲過程，用于查詢某學生的成績信息。代碼如下：

CREATEPROCproc_stud_course@s_idchar(10)WITHRECOMPILEASSELECT*FROMstudent_gradeWHEREstud_id=@s_id創建和應用存儲過程7.4.2重新編譯存儲過程2．執行存儲過程時重編譯在EXECUTE中使用WITHRECOMPILE選項，執行存儲過程時，先重新編譯?；菊Z法如下：EXECUTEprocedure_name[parameter][WITHRECOMPILE]【例7-7】帶重新編譯選項執行存儲過程proc_aver_score，查詢某同學的平均成績。DECLARE@sidchar(10)DECLARE@averscorenumeric(5,2)SET@sid='D06010209'EXECproc_aver_score@sid,@averscoreOUTPUTWITHRECOMPILEPRINTN'學號為'+CONVERT(CHAR(10),@sid)+N'的同學的平均成績是:'+CONVERT(char(10),@averscore)創建和應用存儲過程7.4.2重新編譯存儲過程3．使用sp_recompile系統存儲過程指定表的存儲過程進行重編譯。基本語法如下：

sp_recompiletable_name如EXECsp_recompilestudent_info，將強制表student_info的所有存儲過程在下一次運行時重新編譯創建和應用存儲過程7.5知識進階任務：認識擴展存儲過程的概念，以及如何注冊擴展存儲過程到SQL