存儲過程用戶自定義函數與觸發(fā)器課件

1、第十三章 存儲過程、用戶自定義函數與觸發(fā)器計算中心本章內容13.1 存儲過程概述13.2 通過企業(yè)管理器創(chuàng)建、修改和刪除存儲過程13.3 存儲過程的執(zhí)行與參數傳遞13.4 用戶自定義函數13.5 觸發(fā)器及其作用、效果演示13.1 存儲過程概述存儲過程（Stored Procedure）是什么？簡單的說：存儲過程是將常用的或很復雜的工作，預先用T-SQL語句寫好并用一個指定的名稱存儲起來的語句集合。課本的定義：是SQL Server服務器上一組預編譯的T-SQL語句，用于完成某項任務，它可以接受參數、返回狀態(tài)值和參數值，并且可以嵌套調用。舉例13.1 存儲過程概述為什么要使用存儲過程？存儲過程在

2、創(chuàng)建時即在服務器上進行編譯，所以執(zhí)行起來比SQL語句快，且能減少網絡通信的負擔?？梢栽趩蝹€存儲過程中執(zhí)行一系列SQL語句，完成復雜的操作。 存儲過程可以重復使用，減少數據庫開發(fā)人員的工作量 。安全性高，可設定只有某些用戶才具有對指定存儲過程的使用權。13.1 存儲過程概述存儲過程的類型系統存儲過程例如：EXEC sp_helpdb用戶定義存儲過程：由用戶創(chuàng)建并能完成某一特定功能的存儲過程。臨時存儲過程擴展存儲過程例如：EXEC xp_cmdshell dir d:13.1 存儲過程概述存儲過程的功能(1)接收輸入參數并以輸出參數的形式為調用過程或批處理返回多個值。(2)包含執(zhí)行數據庫操作的編程

3、語句，包括調用其他過程。(3)為調用過程或批處理返回一個狀態(tài)值，以表示成功或失敗(及失敗原因)。存儲過程特點13.2 創(chuàng)建、修改和刪除存儲過程創(chuàng)建存儲過程的指導原則避免出現存儲過程的擁有者和底層對象的擁有者不同的情況，建議由dbo用戶擁有數據庫中所有對象每個存儲過程完成單個任務命名本地存儲過程的時候，避免使用“sp_”前綴盡量少使用臨時存儲過程，以避免頻繁連接 tempdb 里的系統表不要直接從 syscomments 系統表里刪除項13.2 創(chuàng)建、修改和刪除存儲過程創(chuàng)建存儲過程(1) 啟動企業(yè)管理器，登錄到要使用的服務器。(2) 選擇要創(chuàng)建存儲過程的數據庫，在左窗格中單擊“存儲過程”文件夾，

4、此時在右窗格中顯式該數據庫的所有存儲過程，如圖13-1所示。圖13-1 企業(yè)管理器中顯示的存儲過程信息13.2 創(chuàng)建、修改和刪除存儲過程(3) 右擊“存儲過程”文件夾，在彈出菜單中選擇【新建存儲過程】選項，打開創(chuàng)建存儲過程對話框，如下圖。圖13-2 創(chuàng)建存儲過程對話框13.2 創(chuàng)建、修改和刪除存儲過程(4) 在“文本”編輯框中輸入存儲過程正文。(5) 單擊“檢查語法”按鈕，檢查語法是否正確。(6) 單擊“確定”按鈕，保存存儲過程。圖13-3 輸入存儲過程內容輸入內容區(qū)域提示：新創(chuàng)建存儲過程名字包含在CREATE PROCEDURE語句中，不在保存時輸入。13.2 創(chuàng)建、修改和刪除存儲過程修改存

5、儲過程(1) 在企業(yè)管理器中展開服務器組，再展開服務器。(2) 展開“數據庫”文件夾，再展開要修改存儲過程的數據庫。(3) 在要修改的存儲過程上右擊，并在彈出的快捷菜單中選擇【屬性】項，或雙擊該存儲過程，彈出“存儲過程屬性”對話框。圖13-4 控制臺目錄13.2 創(chuàng)建、修改和刪除存儲過程刪除存儲過程類似于刪除表操作，在存儲過程顯示列表中選擇要刪除的存儲過程（可以用ctrl或shift選多個）。右鍵單擊選中的存儲過程，在彈出的快捷菜單中選擇【刪除】項，打開“除去對象”對話框，如下圖，單擊【全部除去】按鈕，完成刪除。圖13-5 “除去對象”對話框顯示與該存儲過程相關的對象13.2 創(chuàng)建、修改和刪除

6、存儲過程創(chuàng)建存儲過程時，需要確定存儲過程的三個組成部分： 參數，所有的輸入參數以及傳給調用者的輸出參數。 過程體，被執(zhí)行的針對數據庫的操作語句，包括調用其它存儲過程的語句； 返回狀態(tài)，返回給調用者的狀態(tài)值，以指明調用是成功還是失敗。13.3 存儲過程的執(zhí)行與參數傳遞參數存儲過程和調用者之間需要通過參數來交換數據，可以按輸入的參數執(zhí)行，也可由參數輸出執(zhí)行結果。 例如：查詢學號為s2008001的c01課程的成績。輸入參數：學號（s2008001）和課程號（c01）輸出參數：成績SQL Server支持這兩類參數。13.3 存儲過程的執(zhí)行與參數傳遞輸入參數輸入參數允許調用程序為存儲過程傳送數據值。

7、定義存儲過程的輸入參數必須在CREATE PROCEDURE語句中聲明一個或多個變量及數據類型。例13-1：創(chuàng)建帶參數的存儲過程，輸入學生學號，返回學生姓名、性別等個人信息。CREATE PROCEDURE dbo.查詢指定學生信息 學號 varchar(10) ASselect * from 學生表 where 學號=學號提示：定義參數的數據類型需和表內字段類型一致。13.3 存儲過程的執(zhí)行與參數傳遞存儲過程的執(zhí)行語法格式：EXECUTE return_status= procedure_name ;number|procedure_name_var parameter= value|var

8、iable OUTPUT|DEFAULT ,.n WITH RECOMPILE 13.3 存儲過程的執(zhí)行與參數傳遞執(zhí)行帶輸入參數的存儲過程在查詢分析器中執(zhí)行例13-1： exec 查詢指定學生信息 s2008001 exec執(zhí)行（execute） 查詢指定學生信息存儲過程名 s2008001輸入參數運行結果：提示：需要根據輸入參數的數據類型加定界符。13.3 存儲過程的執(zhí)行與參數傳遞執(zhí)行時，參數可以由位置標識，也可以由名字標識。例如，定義一個具有3個參數的存儲過程：CREATE PROC myproc val1 int, val2 int, val3 intAS .參數以位置傳遞：EXEC m

9、yproc 10,20,15參數以名字傳遞，每個值由對應的參數名引導：EXEC myproc val2=20,val1=10,val3=15按名字傳遞參數比按位置傳遞參數具有更大的靈活性。但是，按位置傳遞參數卻具有更快的速度。13.3 存儲過程的執(zhí)行與參數傳遞輸出參數輸出參數允許存儲過程將數據值傳回調用程序。用OUTPUT關鍵字指出能返回到調用它的存儲過程。例13-2：創(chuàng)建存儲過程，查詢指定學生的某門課程成績。CREATE PROCEDURE dbo.查詢成績 學號 varchar(10), 課程號 varchar(10),score int output ASselect score=成績f

10、rom 選課表 where 學號=學號 and 課程號=課程號 13.3 存儲過程的執(zhí)行與參數傳遞執(zhí)行帶輸出參數的存儲過程在查詢分析器中執(zhí)行例13-2：declare 學生成績 intexec 查詢成績 s2008001,c01,學生成績 outputselect 學生成績運行結果：提示：輸出參數（返回值）需要提前聲明，數據類型應同輸出參數的數據類型相匹配。EXEC語句需要關鍵字OUTPUT以允許參數值返回變量。13.3 存儲過程的執(zhí)行與參數傳遞返回存儲過程的狀態(tài)用RETURN語句定義返回值存儲過程可以返回整型狀態(tài)值，表示過程是否成功執(zhí)行，或者過程失敗的原因。如果存儲過程沒有顯式設置返回代碼的

11、值，則SQL Server返回代碼為 0，表示成功執(zhí)行；若返回-1-99之間的整數，表示沒有成功執(zhí)行。也可以使用RETURN語句，用大于0或小于-99的整數來定義自己的返回狀態(tài)值，以表示不同的執(zhí)行結果。13.3 存儲過程的執(zhí)行與參數傳遞例13-3：創(chuàng)建存儲過程，輸入課程號，返回課程名稱。在存儲過程中，用值15表示用戶沒有提供參數；值-101表示沒有輸入課程號；值0表示過程運行沒有出錯。CREATE PROCEDURE dbo.查詢課程名 課程號 as varchar(10)=nullASif 課程號=nullreturn 15if not exists(select * from 課程表 wh

12、ere 課程號=課程號)return -101select 課程名 from 課程表 where 課程號=課程號13.3 存儲過程的執(zhí)行與參數傳遞捕獲返回狀態(tài)值在執(zhí)行過程時，要正確接收返回的狀態(tài)值，必須使用語句：EXECUTE status_var=procedure_name 參數例13-3的存儲過程查詢課程名執(zhí)行時使用以下語句：DECLARE return_status intEXEC return_status=查詢課程名 c01IF return_status=15 SELECT 語法錯誤，未輸入參數!ELSE IF return_status=-101 SELECT 沒有找到該課程號

13、. 執(zhí)行時，對不同的輸入值返回不同的狀態(tài)值。13.4 用戶自定義函數用戶自定義函數概述根據函數返回值形式的不同將用戶定義函數分為3種類型。(1) 標量函數標量函數返回一個確定類型的標量值，其函數值類型為SQL Server的系統數據類型（除text、ntext、image、cursor、timestamp、table類型外）。函數體語句定義在BEGINEND語句內。(2) 內嵌表值函數內嵌表值函數返回的函數值為一個表。內嵌表值函數的函數體不使用BEGINEND語句，其返回的表是RETURN子句中的SELECT命令查詢的結果集，其功能相當于一個參數化的視圖。(3) 多語句表值函數多語句表值函數可

14、以看作標量函數和內嵌表值函數的結合體。其函數值也是一個表，但函數體也用BEGINEND語句定義，返回值的表中的數據由函數體中的語句插入。13.4 用戶自定義函數創(chuàng)建用戶自定義函數圖13-6 創(chuàng)建用戶自定義函數對話框13.4 用戶自定義函數例13-4：創(chuàng)建一個用戶定義函數DatetoQuarter，將輸入的日期數據轉換為該日期對應的季度值。如輸入2006-8-5，返回3Q2006，表示2006年3季度。CREATE FUNCTION DatetoQuarter(dqdate datetime)RETURNS char(6)ASBEGIN RETURN(datename(q,dqdate)+Q+d

15、atename(yyyy,dqdate)END13.4 用戶自定義函數例13-5：通過自定義函數根據輸入課程號，返回對應的課程名。CREATE FUNCTION F課程名 (courseID char(10) RETURNS char(20) AS BEGIN declare courseName char(20)select courseName=課程名 from 課程表 where 課程號=courseIDreturn courseNameEND13.4 用戶自定義函數例13-6：創(chuàng)建一個表值函數stuinfo，輸入學生學號，返回學生姓名及各科成績。CREATE FUNCTION stui

16、nfo (xh varchar(10) RETURNS table AS return(select 姓名,課程號,成績from 學生表 inner join 選課表 on 學生表.學號=選課表.學號where 選課表.學號=xh)13.4 用戶自定義函數修改和刪除用戶自定義函數用企業(yè)管理器修改用戶定義函數，選擇要修改函數，雙擊或單擊右鍵，從快捷菜單中選擇“屬性”選項，打開圖13-6所示的“用戶定義函數屬性”對話框。在該對話框中可以修改用戶定義函數的函數體、參數等。從快捷菜單中選擇“刪除”選項，打開“除去對象”對話框，則可刪除用戶自定義函數。13.4 用戶自定義函數用戶自定義函數的使用當調用標

17、量值函數時，必須加上“所有者”，通常是dbo（但不是絕對，可以在企業(yè)管理器中的“用戶定義函數”中查看所有者）當調用表值函數時，可以只使用函數名。例13-7：調用例13-4函數，返回當前日期對應的季度值。 select dbo.DatetoQuarter(getdate()例13-8：調用例13-6函數，返回學號為s2008001的學生姓名和各科成績。 select * from stuinfo(s2008001)13.5 觸發(fā)器及其作用、效果演示觸發(fā)器是一種特殊類型的存儲過程。觸發(fā)器主要是通過事件進行觸發(fā)而被執(zhí)行的，而存儲過程可以通過過程名字直接調用。當對某一表進行UPDATE、INSERT、DELETE操作時，SQL Server就會自動執(zhí)行觸發(fā)器所定義的SQL語句，從而確保對數據的處理必須符合由這些SQL語句所定義的規(guī)則。觸發(fā)器的主要作用就是能夠實現由主鍵和外鍵所不能保證的參照完整性和數據