1、數(shù)據(jù)庫原理與應(yīng)用教程SQL Server,第11章存儲過程、觸發(fā)器和游標(biāo),第11章存儲過程、觸發(fā)器和游標(biāo),存儲過程 觸發(fā)器 游標(biāo),第11章存儲過程、觸發(fā)器和游標(biāo),11.1存儲過程 SQL Server 2005的存儲過程（Stored Procedure）包含一些T-SQL語句并以特定的名稱存儲在數(shù)據(jù)庫中。 可以在存儲過程中聲明變量、有條件地執(zhí)行以及其他各項強(qiáng)大的程序設(shè)計功能。,第11章存儲過程、觸發(fā)器和游標(biāo),11.1.1 存儲過程概述 一種數(shù)據(jù)庫對象 存儲過程可以接受輸入?yún)?shù)、輸出參數(shù)，返回單個或多個結(jié)果集以及返回值，由應(yīng)用程序通過調(diào)用執(zhí)行。 SQL Server支持存儲過程和系統(tǒng)過程。存儲

2、過程是獨立存在于表之外的數(shù)據(jù)對象?？梢杂煽蛻粽{(diào)用，也可以從另一個過程或觸發(fā)器調(diào)用，參數(shù)可以被傳遞和返回，出錯代碼也可以被檢驗。,第11章存儲過程、觸發(fā)器和游標(biāo),11.1.1 存儲過程概述 在性能方面,存儲過程有如下優(yōu)點: (1)預(yù)編譯:存儲過程預(yù)先編譯好放在數(shù)據(jù)庫內(nèi)，減少編譯語句所花的時間。 (2)緩存:編譯好的存儲過程會進(jìn)入緩存，所以對于經(jīng)常執(zhí)行的存儲過程，除了第一次執(zhí)行外，其他次執(zhí)行的速度會有明顯提高。 (3)減少網(wǎng)絡(luò)傳輸:特別對于處理一些數(shù)據(jù)的存儲過程，不必像直接用T-SQL語句實現(xiàn)那樣多次傳送數(shù)據(jù)到客戶端。 (4)更好的利用服務(wù)器內(nèi)存:特別對于處理中間數(shù)據(jù)量不大的情況，存儲過程中可以利

3、用存放在內(nèi)存的表變量。,第11章存儲過程、觸發(fā)器和游標(biāo),11.1.1 存儲過程概述 一般來講，應(yīng)使用SQL Server中的存儲過程而不使用存儲在客戶計算機(jī)本地的 T-SQL 程序，其優(yōu)勢主要表現(xiàn)在： (1)允許模塊化程序設(shè)計。 (2)允許更快速地執(zhí)行。 (3)減少網(wǎng)絡(luò)流量。 (4)可作為安全機(jī)制使用。,第11章存儲過程、觸發(fā)器和游標(biāo),11.1.2存儲過程的類型 1.系統(tǒng)存儲過程 主要存儲在master數(shù)據(jù)庫中并以sp_為前綴，并且系統(tǒng)存儲過程主要是從系統(tǒng)表中獲取信息，從而為數(shù)據(jù)庫系統(tǒng)管理員管理SQL Server提供支持。 通過系統(tǒng)存儲過程，SQL Server中的許多管理性或信息性的活動（

4、如獲取數(shù)據(jù)庫和數(shù)據(jù)庫對象的信息）都可以被順利有效地完成。,第11章存儲過程、觸發(fā)器和游標(biāo),11.1.2存儲過程的類型 2本地存儲過程 本地存儲過程也就是用戶自行創(chuàng)建并存儲在用戶數(shù)據(jù)庫中的存儲過程，一般所說的存儲過程指的就是本地存儲過程。 用戶創(chuàng)建的存儲過程是由用戶創(chuàng)建并能完成某一特定功能(如查詢用戶所需的數(shù)據(jù)信息)的存儲過程。,第11章存儲過程、觸發(fā)器和游標(biāo),11.1.2存儲過程的類型 3臨時存儲過程 臨時存儲過程可分為以下兩種： (1)本地臨時存儲過程 不論哪一個數(shù)據(jù)庫是當(dāng)前數(shù)據(jù)庫，如果在創(chuàng)建存儲過程時，其名稱以“#”號開頭，則該存儲過程將成為一個存放在tempdb數(shù)據(jù)庫中的本地臨時存儲過程

5、。 (2)全局臨時存儲過程 不論哪一個數(shù)據(jù)庫是當(dāng)前數(shù)據(jù)庫，只要所創(chuàng)建的存儲過程名稱是以兩個“#”號開頭，則該存儲過程將成為一個存儲在tempdb數(shù)據(jù)庫中的全局臨時存儲過程。,第11章存儲過程、觸發(fā)器和游標(biāo),11.1.3 創(chuàng)建存儲過程 1在SQL Server Management Studio中創(chuàng)建存儲過程 步驟如下： (1)打開SQL Server Management Studio，展開要創(chuàng)建存儲過程的數(shù)據(jù)庫，展開“可編程性”選項，可以看到存儲過程列表中系統(tǒng)自動為數(shù)據(jù)庫創(chuàng)建的系統(tǒng)存儲過程。右鍵單擊“存儲過程”選項，選“新建存儲過程”命令。 (2)出現(xiàn)創(chuàng)建存儲過程的T-SQL命令，編輯相關(guān)的

6、命令即可。 (3)命令編輯成功后，進(jìn)行語法檢查，然后單擊“確定”按鈕，至此一個新的存儲過程建立成功。,第11章存儲過程、觸發(fā)器和游標(biāo),11.1.3 創(chuàng)建存儲過程 2利用T-SQL語句創(chuàng)建存儲過程 CREATE PROCEDURE創(chuàng)建存儲過程，語法格式如下： CREATE PROC | PROCEDURE procedure_name ; number parameter data_type VARYING = default OUT PUT ,.n WITH RECOMPILE | ENCRYPTION| RECOMPILE , ENCRYPTION ,.n FOR REPLICATION A

7、S sql_statement .n ,第11章存儲過程、觸發(fā)器和游標(biāo),11.1.3 創(chuàng)建存儲過程 在創(chuàng)建存儲過程時，應(yīng)當(dāng)注意以下幾點。 (1)存儲過程最大不能超過128MB。 (2)用戶定義的存儲過程只能在當(dāng)前數(shù)據(jù)庫中創(chuàng)建，但是臨時存儲過程通常是在tempdb數(shù)據(jù)庫中創(chuàng)建的。 (3)在一條T-SQL語句中CREATE PROCEDURE不能與其他T-SQL 句一起使用。 (4)SQL Server允許在存儲過程創(chuàng)建時引用一個不存在的對象，在創(chuàng)建的時候，系統(tǒng)只檢查創(chuàng)建存儲過程的語法。,第11章存儲過程、觸發(fā)器和游標(biāo),11.1.3 創(chuàng)建存儲過程 例11-1在教學(xué)庫創(chuàng)建無參存儲過程，查詢每個同學(xué)各

8、門功課的平均成績。 CREATE PROCEDURE student_avg AS SELECT 學(xué)生號, avg(成績) as 平均分 FROM 選課 GROUP BY 學(xué)生號,第11章存儲過程、觸發(fā)器和游標(biāo),11.1.3 創(chuàng)建存儲過程 例11-2在教學(xué)庫創(chuàng)建帶參數(shù)的存儲過程，查詢某個同學(xué)的基本信息。 CREATE PROCEDURE GetStudent number char(7) AS SELECT * FROM 學(xué)生 WHERE 學(xué)生號= number,第11章存儲過程、觸發(fā)器和游標(biāo),11.1.3 創(chuàng)建存儲過程 例11-3在教學(xué)庫創(chuàng)建帶有參數(shù)和默認(rèn)值（通配符）的存儲過程，從學(xué)生表中返

9、回指定的學(xué)生（提供姓名）的信息。該存儲過程對傳遞的參數(shù)進(jìn)行模式匹配，如果沒有提供參數(shù)，則返回所有學(xué)生的信息。 CREATE PROCEDURE Student_Name name varchar(40) = % AS SELECT * FROM 學(xué)生 WHERE 姓名 LIKE name,第11章存儲過程、觸發(fā)器和游標(biāo),11.1.3 創(chuàng)建存儲過程 例11-4在倉庫庫存數(shù)據(jù)庫創(chuàng)建帶OUTPUT參數(shù)的存儲過程，用于計算指定的商品的平均價格，存儲過程中使用一個輸入?yún)?shù)（商品名）和一個輸出參數(shù)（平均價格）。 CREATE PROCEDURE Pname p_n varchar(20), aveage

10、int OUTPUT AS SELECT aveage= avg(單價) FROM 商品 WHERE 商品名稱=p_n,第11章存儲過程、觸發(fā)器和游標(biāo),11.1.4執(zhí)行存儲過程 EXECUTE命令。 如果被調(diào)用的存儲過程需要參數(shù)輸入時，在存儲過程名后逐一給定，每一個參數(shù)用逗號隔開，不必使用括號。 如果沒有使用參數(shù)名=default這種方式傳入值，則參數(shù)的排列必須和建立存儲過程所定義的次序?qū)?yīng)。 用來接受輸出值的參數(shù)則必須加上OUTPUT。,第11章存儲過程、觸發(fā)器和游標(biāo),11.1.4執(zhí)行存儲過程 EXECUTE語句的語法格式為： EXECUTEUTE return_status= proced

11、ure_name;number parameter=value | parameter= variable OUTPUT WITH RECOMPILE 例11-5執(zhí)行存儲過程student_avg。 EXECUTE student_avg,第11章存儲過程、觸發(fā)器和游標(biāo),11.1.4執(zhí)行存儲過程 例11-6執(zhí)行帶參數(shù)的存儲過程GetStudent，查詢學(xué)號為01010001的學(xué)生的基本信息。 EXECUTE GetStudent 01010001 例11-7執(zhí)行帶有參數(shù)和默認(rèn)值（通配符）的存儲過程Student_Name。 (1)顯示所有學(xué)生的信息： EXECUTE Student_Name

12、(2)顯示“王小明”所有學(xué)生的信息： EXECUTE Student_Name 王小明 例11-8執(zhí)行帶有輸入和輸出參數(shù)的存儲過程Pname。 Declare avgage int EXECUTE Pname 冰箱,avgage OUTPUT Print 冰箱的平均價格：+str(avgage),.NET中調(diào)用存儲過程功能：返回銷售額和插入的記錄SN字段值,SqlConnection conn = new SqlConnection(); conn.ConnectionString = Data Source=.;Initial Catalog=CPXS;Integrated Security

13、=True; if (conn.State = ConnectionState.Closed) conn.Open(); SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandText = 銷售InsertOne; cmd.CommandType = CommandType.StoredProcedure; / cmd.CommandText = INSERT INTO CPXS.dbo.銷售(產(chǎn)品編號,客戶編號,銷售日期,數(shù)量,銷售額) + /VALUES(產(chǎn)品編號,客戶編號,銷售日期,數(shù)量,銷售額); /

14、cmd.CommandType = CommandType.Text; cmd.Parameters.AddWithValue(產(chǎn)品編號,DropDownList1.SelectedValue); cmd.Parameters.AddWithValue(客戶編號,DropDownList2.SelectedValue); cmd.Parameters.AddWithValue(銷售日期,Convert.ToDateTime(TextBox1.Text.Trim(); cmd.Parameters.AddWithValue(數(shù)量,Convert.ToInt32(TextBox2.Text.Tri

15、m(); cmd.Parameters.AddWithValue(銷售額, 0); cmd.Parameters銷售額.Direction=ParameterDirection.InputOutput; /SqlParameter ret = cmd.Parameters.Add(ret, SqlDbType.Int); /ret.Direction = ParameterDirection.ReturnValue; cmd.Parameters.AddWithValue(ret, 0);/返回值類型參數(shù)的名稱可以是存儲過程參數(shù)中定義的參數(shù) cmd.Parametersret.Directio

16、n = ParameterDirection.ReturnValue; cmd.ExecuteNonQuery(); int retvalue = Convert.ToInt32(cmd.Parametersret.Value); decimal xse = Convert.ToDecimal(cmd.Parameters銷售額.Value); GridView1.DataBind();,CREATE PROCEDURE 銷售InsertOne 產(chǎn)品編號CHAR(6) ,客戶編號CHAR(6) ,銷售日期DATETIME = NULL ,數(shù)量INT = NULL ,銷售額DECIMAL(18,

17、0) = NULL OUTPUT AS SET NOCOUNT ON SELECT 銷售額=數(shù)量*價格 FROM dbo.產(chǎn)品 WHERE 產(chǎn)品編號=產(chǎn)品編號 INSERT 銷售(產(chǎn)品編號, 客戶編號, 銷售日期, 數(shù)量, 銷售額) VALUES(產(chǎn)品編號, 客戶編號, 銷售日期, 數(shù)量, 銷售額) RETURN IDENTITY SET NOCOUNT OFFGO,第11章存儲過程、觸發(fā)器和游標(biāo),11.1.5查看、修改和刪除存儲過程 1查看存儲過程 可以執(zhí)行系統(tǒng)存儲過程sp_helptext，用于查看創(chuàng)建存儲過程的命令語句；也可以執(zhí)行系統(tǒng)存儲過程sp_help，用于查看存儲過程的名稱、擁有者

18、、類型、創(chuàng)建時間，以及存儲過程中所使用的參數(shù)信息。其語法格式分別為： sp_helptext 存儲過程名稱 sp_help 存儲過程名稱,第11章存儲過程、觸發(fā)器和游標(biāo),11.1.5查看、修改和刪除存儲過程 1查看存儲過程 例11-9查看存儲過程Pname的相關(guān)信息。 (1) sp_helptext Pname 執(zhí)行結(jié)果如圖所示。,(2) sp_help Pname 執(zhí)行結(jié)果如圖所示。,第11章存儲過程、觸發(fā)器和游標(biāo),11.1.5查看、修改和刪除存儲過程 2修改存儲過程 修改存儲過程可以在SQL Server Management Studio中鼠標(biāo)右擊要修改的存儲過程，選擇“修改”命令進(jìn)行，

19、與創(chuàng)建時的步驟基本相同；也可以通過T-SQL中的ALTER語句來完成。語法格式如下： ALTER PROC | PROCEDURE procedure_name ; number parameter data_type VARYING = default OUT PUT ,.n WITH RECOMPILE | ENCRYPTION| RECOMPILE , ENCRYPTION ,.n FOR REPLICATION AS sql_statement .n ,第11章存儲過程、觸發(fā)器和游標(biāo),11.1.5查看、修改和刪除存儲過程 2修改存儲過程 例11-10修改存儲過程Pname，除了用于計算

20、指定的商品的平均價格外，還用于計算此類商品的庫存總數(shù)量，存儲過程中使用一個輸入?yún)?shù)（商品名）和兩個輸出參數(shù)（平均價格和總數(shù)量）。 ALTER PROCEDURE Pname p_n varchar(20), aveage int OUTPUT,sum int OUTPUT AS SELECT aveage= avg(單價),sum=sum(數(shù)量) FROM 商品as s,庫存情況as k WHERE 商品名稱=p_n and s.商品編號=k.商品編號,第11章存儲過程、觸發(fā)器和游標(biāo),11.1.5查看、修改和刪除存儲過程 3刪除存儲過程 對于不需要的存儲過程可以SQL Server Manag

21、ement Studio中鼠標(biāo)右擊要刪除的存儲過程，選擇“刪除”命令將其刪除，也可以使用T-SQL語句中的DROP PROCEDURE命令將其刪除。 刪除存儲過程的T-SQL語句的語法格式為： DROP PROCEDURE procedure_name ,n procedure_name指要刪除的存儲過程或存儲過程組的名稱。 例11-12刪除存儲過程Pname DROP PROCEDURE Pname,存儲過程VS函數(shù),相同點 數(shù)據(jù)庫對象 完成特定功能，可以有輸入?yún)?shù)和返回值 返回的結(jié)果集可以插入表中 insert #t1 select * from 函數(shù)名稱 (實參) insert #t1

22、EXEC 存儲過程名稱 實參 區(qū)別 存儲過程可以直接獨立執(zhí)行，函數(shù)必須嵌在SQL語句中 標(biāo)量函數(shù)可以用于表達(dá)式中，內(nèi)嵌表值函數(shù)與多語句表值函數(shù)可以數(shù)據(jù)表形式用于FROM后面。 存儲過程不能。,第11章存儲過程、觸發(fā)器和游標(biāo),11.2觸發(fā)器 11.2.1觸發(fā)器概述 存儲過程和觸發(fā)器都是SQL語句和流程控制語句的集合。 特殊存儲過程，它是一種在基本表被修改時自動執(zhí)行的內(nèi)嵌過程，主要通過事件進(jìn)行觸發(fā)而被執(zhí)行。 UPDATE INSERT DELETE,第11章存儲過程、觸發(fā)器和游標(biāo),11.2.1觸發(fā)器概述 觸發(fā)器的優(yōu)點： 由于在觸發(fā)器中可以包含復(fù)雜的處理邏輯，應(yīng)該將觸發(fā)器用來保持低級的數(shù)據(jù)的完整性，

23、而不是返回大量的查詢結(jié)果。 使用觸發(fā)器主要可以實現(xiàn)以下操作： (1)強(qiáng)制比CHECK約束更復(fù)雜的數(shù)據(jù)的完整性。 (2)使用自定義的錯誤提示信息 (3)實現(xiàn)數(shù)據(jù)庫中多張表的級聯(lián)修改 (4)比較數(shù)據(jù)庫修改前后數(shù)據(jù)的狀態(tài) (5)調(diào)用更多的存儲過程 (6)維護(hù)規(guī)范化數(shù)據(jù),第11章存儲過程、觸發(fā)器和游標(biāo),11.2.2觸發(fā)器的分類 1DML觸發(fā)器 當(dāng)數(shù)據(jù)庫服務(wù)器中發(fā)生數(shù)據(jù)操作語言(DML)事件時會自動執(zhí)行的存儲過程。 DML事件 INSERT語句 UPDATE語句 DELETE語句 系統(tǒng)將觸發(fā)器和觸發(fā)它的語句作為可在觸發(fā)器內(nèi)回滾的單個事務(wù)對待，如果檢測到錯誤（例如，磁盤空間不足），則整個事務(wù)即自動回滾。,

24、第11章存儲過程、觸發(fā)器和游標(biāo),11.2.2觸發(fā)器的分類 1DML觸發(fā)器 SQL Server 2005的DML觸發(fā)器分為兩類： (1) AFTER觸發(fā)器：這類觸發(fā)器是在記錄已經(jīng)改變完之后，才會被激活執(zhí)行，它主要是用于記錄變更后的處理或檢查，一旦發(fā)現(xiàn)錯誤，也可以用ROLLBACK TRANSACTION語句來回滾本次的操作。 (2) INSTEAD OF觸發(fā)器：與AFTER觸發(fā)器不同，這類觸發(fā)器一般是用來取代原本的操作，在記錄變更之前發(fā)生的，它并不去執(zhí)行原來SQL語句里的操作（UPDATE、INSERT、DELETE），而去執(zhí)行觸發(fā)器本身所定義的操作。,第11章存儲過程、觸發(fā)器和游標(biāo),11.2

25、.2觸發(fā)器的分類 2DDL 觸發(fā)器 SQL Server 2005新增的一個觸發(fā)器類型，是一種特殊的觸發(fā)器，它在響應(yīng)數(shù)據(jù)定義語言（DDL）語句時觸發(fā)，一般用于數(shù)據(jù)庫中執(zhí)行管理任務(wù)。 添加，刪除或修改數(shù)據(jù)庫的對象，一旦誤操作，可能會導(dǎo)致大麻煩，需要一個數(shù)據(jù)庫管理員或開發(fā)人員對相關(guān)可能受影響的實體進(jìn)行代碼的重寫。為了在數(shù)據(jù)庫結(jié)構(gòu)發(fā)生變動而出現(xiàn)問題時，能夠跟蹤問題和定位問題的根源，我們可以利用DDL觸發(fā)器來記錄類似“用戶建立表”這種變化的操作，這樣可以大大減輕跟蹤和定位數(shù)據(jù)庫模式的變化的繁瑣程度。（系統(tǒng)操作日志）,第11章存儲過程、觸發(fā)器和游標(biāo),11.2.2觸發(fā)器的分類 2DDL 觸發(fā)器 一般來說，

26、在以下幾種情況下可以使用DDL觸發(fā)器： (1)數(shù)據(jù)庫里的庫架構(gòu)或數(shù)據(jù)表架構(gòu)很重要（如系統(tǒng)配置信息） ，不允許被修改。 (2)防止數(shù)據(jù)庫或數(shù)據(jù)表被誤操作刪除。 (3)在修改某個數(shù)據(jù)表結(jié)構(gòu)的同時修改另一個數(shù)據(jù)表的相應(yīng)的結(jié)構(gòu)。 (4)要記錄對數(shù)據(jù)庫結(jié)構(gòu)操作的事件。,第11章存儲過程、觸發(fā)器和游標(biāo),11.2.3創(chuàng)建觸發(fā)器 在創(chuàng)建觸發(fā)器前，需要注意以下一些問題。 (1)CREATE TRIGGER語句必須是批處理中的第一個語句，而且只能用于一個表或視圖。 (2)創(chuàng)建觸發(fā)器的權(quán)限默認(rèn)為表的所有者，不能將該權(quán)限轉(zhuǎn)給其他用戶。 (3)雖然觸發(fā)器可以引用當(dāng)前數(shù)據(jù)庫以外的對象，但只能在當(dāng)前數(shù)據(jù)庫中創(chuàng)建觸發(fā)器。 (

27、4)雖然不能在臨時表或系統(tǒng)表上創(chuàng)建觸發(fā)器，但是觸發(fā)器可以引用臨時表。不應(yīng)引用系統(tǒng)表，而應(yīng)使用信息架構(gòu)視圖。 (5)在含有用DELETE或UPDATE操作定義的外鍵的表中，不能定義INSTEAD OF和INSTEAD OF UPDATE觸發(fā)器。 (6)雖然TRUNCATE TABLE語句類似于沒有WHERE子句(用于刪除行)的DELETE語句，但不會激發(fā)DELETE觸發(fā)器，因為TRUNCATE TABLE語句沒有記錄日志。,第11章存儲過程、觸發(fā)器和游標(biāo),11.2.3創(chuàng)建觸發(fā)器 1在SQL Server Management Studio中創(chuàng)建DML觸發(fā)器 步驟如下： (1)打開SQL Serv

28、er Management Studio，展開要創(chuàng)建DML觸發(fā)器的數(shù)據(jù)庫和其中的表或視圖（如學(xué)生表），右鍵單擊“觸發(fā)器”選項，選擇“新建觸發(fā)器”命令。 (2)出現(xiàn)創(chuàng)建觸發(fā)器的T-SQL命令，編輯相關(guān)的命令即可。 (3)命令編輯成功后，進(jìn)行語法檢查，然后單擊“確定”按鈕，至此一個DML觸發(fā)器建立成功。,第11章存儲過程、觸發(fā)器和游標(biāo),11.2.3創(chuàng)建觸發(fā)器 2利用T-SQL語句創(chuàng)建觸發(fā)器 SQL Server 2005提供了CREATE TRIGGER創(chuàng)建觸發(fā)器。其語法格式如下： CREATE TRIGGER trigger_name ON table_name | view WITH ENCR

29、YPTION FOR | AFTER | INSTEAD OF INSERT DELETE UPDATE NOT FOR REPLICATION AS sql_statement .n ,第11章存儲過程、觸發(fā)器和游標(biāo),11.2.3創(chuàng)建觸發(fā)器 例11-13使用DDL觸發(fā)器limited來防止數(shù)據(jù)庫中的任一表被修改或刪除。 CREATE TRIGGER limited ON database FOR DROP_TABLE, ALTER_TABLE AS PRINT 名為limited的觸發(fā)器不允許您執(zhí)行對表的修改或刪除操作! ROLLBACK,第11章存儲過程、觸發(fā)器和游標(biāo),11.2.3創(chuàng)建觸發(fā)

30、器 例11-15為學(xué)生表創(chuàng)建一個簡單DML觸發(fā)器，在插入和修改數(shù)據(jù)時，都會自動顯示提示信息。 CREATE TRIGGER reminder ON 學(xué)生 FOR INSERT , UPDATE AS print 你在插入或修改學(xué)生表的數(shù)據(jù),第11章存儲過程、觸發(fā)器和游標(biāo),11.2.3創(chuàng)建觸發(fā)器 例11-17為學(xué)生表創(chuàng)建一個DML觸發(fā)器，在插入和修改數(shù)據(jù)時，都會自動顯示所有學(xué)生的信息。 CREATE TRIGGER print_table ON 學(xué)生 FOR INSERT, UPDATE AS select * from 學(xué)生,第11章存儲過程、觸發(fā)器和游標(biāo),11.2.3創(chuàng)建觸發(fā)器 例11-19

31、在學(xué)生表上創(chuàng)建一個DELETE類型的觸發(fā)器，刪除數(shù)據(jù)時，顯示刪除學(xué)生的個數(shù)。 CREATE TRIGGER del_count ON 學(xué)生 FOR DELETE AS DECLARE count varchar(50) SELECT count=STR(ROWCOUNT)+個學(xué)生被刪除 SELECT count RETURN,第11章存儲過程、觸發(fā)器和游標(biāo),11.2.3創(chuàng)建觸發(fā)器 在SQL Server 2005里，為每個DML觸發(fā)器都定義了兩個特殊的表：Inserted，Deleted。 用戶只有讀取的權(quán)限，沒有修改的權(quán)限。 SQL Server建立和管理這兩個臨時表。 這兩個表的結(jié)構(gòu)與觸發(fā)

32、器所在數(shù)據(jù)表的結(jié)構(gòu)是完全一致的，其中包含了在激發(fā)觸發(fā)器的操作中插入或刪除的所有記錄。 當(dāng)觸發(fā)器的工作完成之后，這兩個表也將會從內(nèi)存中刪除。,第11章存儲過程、觸發(fā)器和游標(biāo),11.2.3創(chuàng)建觸發(fā)器 在用戶執(zhí)行INSERT語句時，所有被添加的記錄都會存儲在Inserted表中； 在用戶執(zhí)行DELETE語句時，從觸發(fā)程序表中被刪除的行會發(fā)送到Deleted表； 對于UPDATE語句，SQL Server先將要進(jìn)行修改的記錄存儲到Deleted表中，然后再將修改后的數(shù)據(jù)復(fù)制到Inserted表以及觸發(fā)程序表。,第11章存儲過程、觸發(fā)器和游標(biāo),11.2.3創(chuàng)建觸發(fā)器 先創(chuàng)建一個dept數(shù)據(jù)庫（屬性默認(rèn)）

33、，和兩個表dept,gongcheng，其結(jié)構(gòu)如下。 Create table dept ( dno char(5) primary key, dname varchar(20), leader varchar(10) ) CREATE table gongcheng ( gno char(5) primary key, gname varchar(20), Leader varchar(10) ),第11章存儲過程、觸發(fā)器和游標(biāo),11.2.3創(chuàng)建觸發(fā)器 例11-21為dept表創(chuàng)建一個名為d_tr的觸發(fā)器，當(dāng)執(zhí)行添加、更新或刪除時，激活該觸發(fā)器。通過此例，了解Inserted表和Delete

34、d表的功能。 CREATE TRIGGER d_tr ON dept FOR INSERT, UPDATE, DELETE AS SELECT * FROM inserted SELECT * FROM deleted,第11章存儲過程、觸發(fā)器和游標(biāo),11.2.3創(chuàng)建觸發(fā)器 例11-23為gongcheng表創(chuàng)建一個名為g_tr的觸發(fā)器，實現(xiàn)參照完整性。 CREATE TRIGGER g_tr ON gongcheng FOR insert AS if not exists (select * from dept where leader=(SELECT leader FROM inserte

35、d) begin declare lead varchar(10) set lead=(SELECT leader FROM inserted) print 你在gongcheng表中要插入的記錄， 在dept表中不存在這樣的leader:+lead rollback end,第11章存儲過程、觸發(fā)器和游標(biāo),11.2.3創(chuàng)建觸發(fā)器 例11-25為dept表創(chuàng)建一個名為d_tr1的實現(xiàn)級聯(lián)更新的update觸發(fā)器，當(dāng)執(zhí)行更新(leader列)時，激活該觸發(fā)器同時更新gongcheng表中相應(yīng)記錄。 CREATE TRIGGER d_tr1 ON dept FOR UPDATE AS update

36、 gongcheng set leader=(SELECT leader FROM inserted) where leader=(SELECT leader FROM deleted),第11章存儲過程、觸發(fā)器和游標(biāo),11.2.3創(chuàng)建觸發(fā)器 例11-27 觸發(fā)器中調(diào)用存儲過程。 首先創(chuàng)建一個存儲過程p1如下： CREATE PROC p1 AS Select * from 學(xué)生 然后，為學(xué)生表創(chuàng)建一個觸發(fā)器tr1，在插入、修改或刪除數(shù)據(jù)時，都會調(diào)用存儲過程p1。 CREATE TRIGGER tr1 on 學(xué)生 for insert, update, delete AS EXEC p1,第1

37、1章存儲過程、觸發(fā)器和游標(biāo),11.2.4查看觸發(fā)器信息及修改觸發(fā)器 1在SQL Server Management Studio中查看觸發(fā)器,第11章存儲過程、觸發(fā)器和游標(biāo),11.2.4查看觸發(fā)器信息及修改觸發(fā)器 2使用系統(tǒng)存儲過程查看觸發(fā)器 系統(tǒng)存儲過程sp_help和sp_helptext分別提供有關(guān)觸發(fā)器的不同信息。 (1)通過sp_help系統(tǒng)存儲過程，可以了解觸發(fā)器的一般信息，包括名字、擁有者名稱、類型、創(chuàng)建時間。 (2)通過sp_helptext能夠查看觸發(fā)器的定義信息。 還可以通過使用系統(tǒng)存儲過程sp_helptrigger來查看某張?zhí)囟ū砩洗嬖诘挠|發(fā)器的某些相關(guān)信息。,第11章

38、存儲過程、觸發(fā)器和游標(biāo),11.2.4查看觸發(fā)器信息及修改觸發(fā)器 例11-31修改教學(xué)庫中的學(xué)生表上的觸發(fā)器reminder，使得在用戶執(zhí)行添加或修改操作時，自動給出錯誤提示信息，撤銷此次操作。 ALTER TRIGGER reminder ON 學(xué)生 INSTEAD OF INSERT , UPDATE AS print 你執(zhí)行的添加或修改操作無效！,第11章存儲過程、觸發(fā)器和游標(biāo),11.2.5禁止、啟用和刪除觸發(fā)器 禁用觸發(fā)器與刪除觸發(fā)器不同 1禁止和啟用觸發(fā)器 在使用觸發(fā)器時，用戶可能會遇到需要禁止某個觸發(fā)器起作用的場合，例如，在某些表上不允許批量更新操作，使用的是用觸發(fā)器，里面根據(jù)ROW

39、COUNT來進(jìn)行判斷，如果ROWCOUNT大于預(yù)設(shè)的值就不允許更新，但是作為數(shù)據(jù)庫管理員難免有批量更新的要求，此時就需要讓觸發(fā)器不起作用，即禁止。 在系統(tǒng)剛上線時，需要大量加載歷史數(shù)據(jù)，應(yīng)該禁用觸發(fā)器。,第11章存儲過程、觸發(fā)器和游標(biāo),11.2.5禁止、啟用和刪除觸發(fā)器 當(dāng)一個觸發(fā)器被禁止，該觸發(fā)器仍然存在于表上，只是觸發(fā)器的動作將不再執(zhí)行，直到該觸發(fā)器被重新啟用。ALTER TABLE可以禁止和啟用一個表上的一個或者全部的觸發(fā)器，禁止和啟用觸發(fā)器的語法格式如下： ALTER TABLE table_name ENABLE | DISABLE TRIGGER ALL | trigger_nam

40、e ,.n 例11-32禁止學(xué)生表上創(chuàng)建的所有觸發(fā)器。 ALTER TABLE 學(xué)生 DISABLE TRIGGER ALL,第11章存儲過程、觸發(fā)器和游標(biāo),11.2.5禁止、啟用和刪除觸發(fā)器 2刪除觸發(fā)器 DROP TRIGGER trigger_ name 例11-33使用DROP TRIGGER命令刪除學(xué)生表上的del_count觸發(fā)器。 DROP TRIGGER del_count,第11章存儲過程、觸發(fā)器和游標(biāo),11.3游標(biāo)（Cursor） 適用情況：從某一結(jié)果集中逐一地讀取一條記錄，逐一進(jìn)行處理。 比如：自動對所有數(shù)據(jù)庫進(jìn)行備份。 學(xué)習(xí)目標(biāo)： 了解游標(biāo)的概念及基本用法、適用場合。,

41、第11章存儲過程、觸發(fā)器和游標(biāo),11.3.1游標(biāo)概述 關(guān)系數(shù)據(jù)庫管理系統(tǒng)實質(zhì)是面向集合的 在SQL Server 中并沒有一種描述表中單一一行的表達(dá)形式，除非使用WHERE 子句來限制只有一行記錄被選中。而應(yīng)用程序，特別是交互式聯(lián)機(jī)應(yīng)用程序，并不總能將整個結(jié)果集作為一個單元來有效地處理，這些應(yīng)用程序需要一種機(jī)制以便每次處理一行或一部分行。,第11章存儲過程、觸發(fā)器和游標(biāo),11.3.1游標(biāo)概述 游標(biāo)通過以下方式擴(kuò)展結(jié)果處理： (1)允許定位在結(jié)果集的特定行。 (2)從結(jié)果集的當(dāng)前位置檢索一行或多行。 (3)支持對結(jié)果集中當(dāng)前位置的行進(jìn)行數(shù)據(jù)修改。 (4)為由其他用戶對顯示在結(jié)果集中的數(shù)據(jù)庫數(shù)據(jù)所

42、做的更改提供不同級別的可見性支持。 (5)提供腳本、存儲過程和觸發(fā)器中使用的訪問結(jié)果集中的數(shù)據(jù)的T-SQL語句。,第11章存儲過程、觸發(fā)器和游標(biāo),11.3.2游標(biāo)的類型 SQL Server 支持三種類型的游標(biāo)：T-SQL 游標(biāo)，API 服務(wù)器游標(biāo)和客戶游標(biāo)。 1. T-SQL 游標(biāo) T-SQL 游標(biāo)是由DECLARE CURSOR 語法定義、主要用在T-SQL 腳本、存儲過程和觸發(fā)器中。T-SQL 游標(biāo)主要用在服務(wù)器上，由從客戶端發(fā)送給服務(wù)器的T-SQL 語句或是批處理、存儲過程、觸發(fā)器中的T-SQL 進(jìn)行管理。 2. API 游標(biāo) API 游標(biāo)支持在OLE DB， ODBC 以及DB_li

43、brary 中使用游標(biāo)函數(shù)，主要用在服務(wù)器上。 3. 客戶游標(biāo) 客戶游標(biāo)主要是當(dāng)在客戶機(jī)上緩存結(jié)果集時才使用。在客戶游標(biāo)中，有一個缺省的結(jié)果集被用來在客戶機(jī)上緩存整個結(jié)果集。,第11章存儲過程、觸發(fā)器和游標(biāo),11.3.2游標(biāo)的類型 服務(wù)器游標(biāo)包含以下四種：靜態(tài)游標(biāo)、動態(tài)游標(biāo)、只進(jìn)游標(biāo)、鍵集驅(qū)動游標(biāo)。 (1) 靜態(tài)游標(biāo)的完整結(jié)果集將打開游標(biāo)時建立的結(jié)果集存儲在臨時表中。靜態(tài)游標(biāo)始終是只讀的，總是按照打開游標(biāo)時的原樣顯示結(jié)果集 (2)動態(tài)游標(biāo)與靜態(tài)游標(biāo)相反，當(dāng)滾動游標(biāo)時動態(tài)游標(biāo)反映結(jié)果集中的所有更改。結(jié)果集中的行數(shù)據(jù)值、順序和成員每次提取時都會改變。 (3)只進(jìn)游標(biāo)不支持滾動，它只支持游標(biāo)從頭到尾

44、順序提取數(shù)據(jù)行。只進(jìn)游標(biāo)也反映對結(jié)果集所做的所有更改。 (4) 鍵集驅(qū)動游標(biāo)同時具有靜態(tài)游標(biāo)和動態(tài)游標(biāo)的特點。,第11章存儲過程、觸發(fā)器和游標(biāo),11.3.3游標(biāo)的操作 操作游標(biāo)有五種基本的步驟:聲明游標(biāo)、打開游標(biāo)、提取數(shù)據(jù)、關(guān)閉游標(biāo)、釋放游標(biāo)。 1. 聲明游標(biāo) 和使用其它類型的變量一樣，使用一個游標(biāo)之前，首先應(yīng)當(dāng)聲明它。游標(biāo)的聲明包括兩個部分：游標(biāo)的名稱和這個游標(biāo)所用到的SQL語句。其語法格式如下： DECLARE cursor_name INSENSITIVE SCROLL CURSOR FOR select_statement FOR READ ONLY | UPDATE OF colum

45、n_name ,.n,第11章存儲過程、觸發(fā)器和游標(biāo),11.3.3游標(biāo)的操作 1. 聲明游標(biāo) 例11-34 聲明一個名為S_Cursor的游標(biāo)，用以查詢計算機(jī)專業(yè)的所有學(xué)生的信息，可以編寫如下代碼: DECLARE S_Cursor CURSOR FOR SELECT * FROM 學(xué)生 WHERE 專業(yè)=計算機(jī),第11章存儲過程、觸發(fā)器和游標(biāo),11.3.3游標(biāo)的操作 2. 打開游標(biāo) 聲明了游標(biāo)后在作其它操作之前,必須打開它。打開一個T-SQL服務(wù)器游標(biāo)使用OPEN 命令，其語法規(guī)則為： OPEN GLOBAL cursor_name | cursor_variable_name 例11-36

46、 打開例11-34聲明的游標(biāo)。 OPEN S_Cursor,第11章存儲過程、觸發(fā)器和游標(biāo),11.3.3游標(biāo)的操作 3. 讀取游標(biāo) 當(dāng)游標(biāo)被成功打開以后，就可以從游標(biāo)中逐行地讀取數(shù)據(jù)，以進(jìn)行相關(guān)處理。從游標(biāo)中讀取數(shù)據(jù)主要使用FETCH 命令。其語法格式如下： FETCH NEXT | PRIOR | FIRST | LAST | ABSOLUTE n | nvar| RELATIVE n | nvar FROM GLOBAL cursor_name | cursor_variable_name INTO variable_name ,n 例11-37 從例11-34聲明的游標(biāo)中讀取數(shù)據(jù)。 FE

47、TCH NEXT FROM S_ Cursor,處理當(dāng)前行,修改當(dāng)前游標(biāo)的數(shù)據(jù)方法如下: UpDateMaster_Goods SetGoodsName=php100 WhereCurrentOfMyCursor 刪除當(dāng)前游標(biāo)行數(shù)據(jù)的方法如下: DeleteFromMaster_Goods WhereCurrentOfMyCursor,第11章存儲過程、觸發(fā)器和游標(biāo),11.3.3游標(biāo)的操作 4. 關(guān)閉游標(biāo) 在處理完游標(biāo)中數(shù)據(jù)之后必須關(guān)閉游標(biāo)來釋放數(shù)據(jù)結(jié)果集和定位于數(shù)據(jù)記錄上的鎖。可以使用CLOSE 語句關(guān)閉游標(biāo)，但此語句不釋放游標(biāo)占用的數(shù)據(jù)結(jié)構(gòu)。其關(guān)閉游標(biāo)的語法格式如下： CLOSE GLOBAL cursor_name | cursor_variable_name 例11-38 關(guān)閉S_ Cursor游標(biāo)。