1、正版可修改PPT課件（中職）計算機組裝與維護A0701教學課件SQL Server數據庫項目教程教學課件項目7：創建和管理存儲過程我們畢業啦其實是答辯的標題地方任務1 創建并執行存儲過程 在“學生成績系統”數據庫中使用T-SQL創建一個存儲過程。要求該存儲過程實現功能為：輸入學生名字返回學生所在的班級名字，要求如下：（1）該存儲過程命名為Proc_StuClass。（2）要求有判斷功能，如果輸入的學生不存在，則輸出該學生不是本校學生。（3）執行該存儲過程，分別輸入學生姓名“王偉”，“常山”測試結果。任務概述 本任務可以使用2種方法完成：（1）使用SSMS圖形界面方式創建并執行存儲過程。（2）使

2、用T-SQL語句創建并執行存儲過程。在使用CREATE PROCEDURE語法時需要對輸入參數、輸出參數進行聲明。任務分析知識準備CONTANTS2. 使用SSMS創建并執行存儲過程3. 使用T-SQL創建并執行存儲過程思維導圖知識準備1.1 存儲過程的分類存儲過程是一組具有特定功能的T-SQL語句的集合，經編譯后存儲在數據庫中，是封裝重復性工作的一種方法。下面主要介紹系統存儲過程、擴展存儲過程和用戶自定義存儲過程。系統存儲過程： 由系統自動創建，存儲在master和msdb數據庫中，并以sp 為前綴。系統存儲過程主要是從系統表中獲取信息，為系統管理員管理SQL Server 2019提供支持

3、。擴展存儲過程： 以在SQL Server 2019環境外執行的動態鏈接庫（Dynamic-Link Libraries, DLL）來實現。一般以xp_為前綴標識。用戶自定義存儲過程：用戶自定義存儲過程是由用戶編寫，是指封裝了可重用代碼的模板或者例程。1.2 存儲過程的優點1324存儲過程只在創造時進行編譯，以后每次執行都不需再重新編譯。可以被多次調用執行,可減少數據庫開發人員的工作量。安全性高,可實現對某些數據訪問的限制，保證數據的安全。減少網絡通信量。1.3 存儲過程的缺點1324可移植性差。由于存儲過程將應用程序綁定到 SQL Server，因此使用存儲過程封裝業務邏輯將限制應用程序的可

4、移植性。代碼可讀性較差，影響維護。不支持集群。不支持面向對象的設計。無法采用面向對象的方式將邏輯業務進行封裝，甚至形成通用的可支持服務的業務邏輯框架。1.4 創建存儲過程的語法格式CREATE PROCEDURE schema_name. procedure_name ;numberparameter data_typeVARYING = default OUTPUT ,n WITH ENCRYPTION | RECOMPILE | RECOMPILE, ENCRYPTION FOR REPLICATIONAS sql_statementn1.41 創建語法參數說明（1）procedure_n

5、ame:用于指定存儲過程的名稱。（2）number:用于指定對同名的過程分組。（3）parameter:用于指定存儲過程中的參數。（4）data_type:用于指定參數的數據類型。（5）VARYING：指定作為輸出參數支持的結果集，僅適用于游標參數。（6）default:用于指定參數的默認值。1.42 創建語法參數說明（7）OUTPUT：指定參數是輸出參數。（8）RECOMPILE:指定數據庫引擎不緩存該過程的計劃，該過程在運行時編譯。（9）ENCRYPTION:指定SQL Server 加密 syscomments表中包含CREATE PROCEDURE語句文本的條目。（10）FOR REP

6、LICATION:指定不能在訂閱服務器上執行為復制創建的存儲過程。（11）:要包含在過程中的一個或多個T-SQL語句。1.5 執行存儲過程的語法格式 EXECU | EXECUTE return_status = module_name ;number | module_name_var parameter = value | variable OUTPUT | DEFAULT ,n WITH RECOMPILE 1.51 執行語法參數說明（1）return_status: 可選的整型變量，存儲模塊的返回狀態。（2）module_name: 是要調用的存儲過程的完全限定或者不完全限定的名稱。（

7、3）number: 可選整數，用于對同名的過程分組。（4）module_name_var: 是局部定義的變量名，代表模塊名稱。（5）parameter: 存儲過程中使用的參數，與在模塊中定義的相同。參數名稱前必須加上符號。1.52 執行語法參數說明（6）value: 傳遞給模塊或傳遞命令的參數值。（7）variable: 是用來存儲參數或返回參數的變量。（8）OUTPUT: 指定模塊或命令字符串返回一個參數。（9）DEFAULT: 根據模塊的定義，提供參數的默認值。（10）WITH RECOMPILE: 執行模塊后，強制編譯、使用和放棄新計劃。建議盡量少使用該選項，因為它消耗較多的資源系統。2

8、.使用SSMS創建并執行存儲過程2.1 實施步驟（1）打開SSMS窗口，選擇“數據庫”“dbStudentScore”“可編程性”“存儲過程”結點。右擊“存儲過程”，在彈出的快捷菜單中選擇“新建存儲過程”菜單命令，如圖7-1所示。圖7-1“新建”“存儲過程”菜單命令任務配備微課視頻2.1 實施步驟（2）在創建存儲過程的窗口中選擇“查詢”“指定模板參數的值”菜單命令，如圖7-2所示。圖7-2“指定模板參數的值”菜單命令2.1 實施步驟（3）彈出“指定模板參數的值”對話框，將Procedure_Name參數對應的名稱修改為“Proc_StuClass1”單擊“確定”按鈕，即可關閉此對話框，如圖7-

9、3所示。圖7-3“指定模板參數的值”對話框2.1 實施步驟（4）在編輯窗口，輸入對應代碼。把一些暫時無關的代碼刪除，整理代碼，效果如下圖7-4所示，單擊執行完成創建。圖7-4輸入代碼創建存儲過程2.1 實施步驟（5）執行存儲過程。在左邊節點刷新后，右擊剛才新建立的存儲過程“Proc_StuClass1”。在彈出快捷菜單中選擇“執行存儲過程”菜單命令，如圖7-5所示。圖7-5選擇“執行存儲過程”菜單命令2.1 實施步驟（6）打開“執行存儲過程”窗口，輸入參數值“王偉”，單擊“確定”，執行結果如圖7-6所示。（7）結果如圖7-7所示。圖7-6輸入參數值“王偉”圖7-7“王偉”查詢結果”2.1 實施

10、步驟（8）再次執行存儲過程“Proc_StuClass1”，輸入參數值“常山”，單擊“確定”，執行結果如圖7-8所示。圖7-8“常山”查詢結果3.使用T-SQL創建并執行存儲過程3.1 實施步驟（1）點擊“新建查詢”按鈕，就可以在下面的命令界面中輸入代碼，如圖7-9所示。圖7-9查詢界面任務配備微課視頻3.1 實施步驟（2）輸入以下代碼。USE dbStudentScore;GOCREATE PROCEDURE Proc_StuClassStuName Nvarchar(20),-學生名字ClaName Nvarchar(20) OUTPUT -班級名字，輸出變量AS-根據輸入的學生名字查詢學

11、生IDDECLARE StuID char(10)SELECT StuID=StudentIDFROM tbStudent WHERE StudentName=StuName3.1 實施步驟（2）輸入以下代碼。-判斷學生是否屬于該學校，如查StuID為空，則該生不屬于學校學生，否則屬于學校學生IF StuID IS NullBEGINPrint 對不起，該學生不是本校學生RETURN -后面代碼不執行，退出存儲過程END-查詢該學生的班級名字SELECT StuName=StudentName,ClaName=ClassNameFROM tbStudent inner join tbClass on tbStudent.ClassID=tbClass.ClassIDWHERE StudentID=StuIDGO3.1 實施步驟（3）點擊執行代碼。刷新左邊目錄樹后，可以看到存儲過程Proc_StuClass已經創建成功。（4）執行存儲過程Proc_StuClass，以學生“王偉”為例，將“王偉”賦值給StuName，并將返回結果保存在ClaName中。USE dbStudentScore ; GODECLARE StuName Nvarchar(20)=王偉,-聲明變量，將學生姓名賦值給變量ClaName Nvarcha