數(shù)據(jù)庫 SQL觸發(fā)器實(shí)例講解

1、.SQL觸發(fā)器實(shí)例講解1SQL觸發(fā)器實(shí)例1定義： 何為觸發(fā)器.在SQL Server里面也就是對某一個(gè)表的一定的操作，觸發(fā)某種條件，從而執(zhí)行的一段程序。觸發(fā)器是一個(gè)特殊的存儲過程。 常見的觸發(fā)器有三種：分別應(yīng)用于Insert , Update , Delete 事件。 我為什么要使用觸發(fā)器.比如，這么兩個(gè)表： Create Table Student( -學(xué)生表 StudentID int primary key, -學(xué)號 . ) Create Table BorrowRecord( -學(xué)生借書記錄表 BorrowRecord int identity(1,1), -流水號 StudentID

2、 int , -學(xué)號 BorrowDate datetime, -借出時(shí)間 ReturnDAte Datetime, -歸還時(shí)間 . ) 用到的功能有: 1.如果我更改了學(xué)生的學(xué)號,我希望他的借書記錄仍然與這個(gè)學(xué)生相關(guān)(也就是同時(shí)更改借書記錄表的學(xué)號); 2.如果該學(xué)生已經(jīng)畢業(yè)，我希望刪除他的學(xué)號的同時(shí)，也刪除它的借書記錄。 等等。 這時(shí)候可以用到觸發(fā)器。對于1，創(chuàng)建一個(gè)Update觸發(fā)器： Create Trigger truStudent On Student -在Student表中創(chuàng)建觸發(fā)器 for Update -為什么事件觸發(fā) As -事件觸發(fā)后所要做的事情 if Update(St

3、udentID) begin Update BorrowRecord Set StudentID=i.StudentID From BorrowRecord br , Deleted d ,Inserted i -Deleted和Inserted臨時(shí)表 Where br.StudentID=d.StudentID end 理解觸發(fā)器里面的兩個(gè)臨時(shí)的表：Deleted , Inserted 。注意Deleted 與Inserted分別表示觸發(fā)事件的表“舊的一條記錄”和“新的一條記錄”。 一個(gè)數(shù)據(jù)庫系統(tǒng)中有兩個(gè)虛擬表用于存儲在表中記錄改動的信息，分別是： 虛擬表Inserted 虛擬表Delete

4、d 在表記錄新增時(shí) 存放新增的記錄 不存儲記錄 修改時(shí) 存放用來更新的新記錄 存放更新前的記錄 刪除時(shí) 不存儲記錄 存放被刪除的記錄 一個(gè)Update 的過程可以看作為：生成新的記錄到Inserted表，復(fù)制舊的記錄到Deleted表，然后刪除Student記錄并寫入新紀(jì)錄。 對于2，創(chuàng)建一個(gè)Delete觸發(fā)器 Create trigger trdStudent On Student for Delete As Delete BorrowRecord From BorrowRecord br , Delted d Where br.StudentID=d.StudentID 從這兩個(gè)例子我們可

5、以看到了觸發(fā)器的關(guān)鍵：A.2個(gè)臨時(shí)的表；B.觸發(fā)機(jī)制。 SQL觸發(fā)器實(shí)例2/* 建立虛擬測試環(huán)境，包含：表卷煙庫存表，表卷煙銷售表。 請大家注意跟蹤這兩個(gè)表的數(shù)據(jù)，體會觸發(fā)器到底執(zhí)行了什么業(yè)務(wù)邏輯，對數(shù)據(jù)有什么影響。 為了能更清晰的表述觸發(fā)器的作用，表結(jié)構(gòu)存在數(shù)據(jù)冗余，且不符合第三范式，這里特此說明。 */ USE Master GO IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE XTYPE = U AND NAME = 卷煙庫存表) DROP TABLE 卷煙庫存表 GO IF EXISTS (SELECT NAME FROM SYSOBJECTS

6、 WHERE XTYPE = U AND NAME = 卷煙銷售表) DROP TABLE 卷煙銷售表 GO -業(yè)務(wù)規(guī)則：銷售金額 = 銷售數(shù)量 * 銷售單價(jià) 業(yè)務(wù)規(guī)則。 CREATE TABLE 卷煙銷售表 ( 卷煙品牌 VARCHAR(40) PRIMARY KEY NOT NULL, 購貨商 VARCHAR(40) NULL, 銷售數(shù)量 INT NULL, 銷售單價(jià) MONEY NULL, 銷售金額 MONEY NULL ) GO -業(yè)務(wù)規(guī)則：庫存金額 = 庫存數(shù)量 * 庫存單價(jià) 業(yè)務(wù)規(guī)則。 CREATE TABLE 卷煙庫存表 ( 卷煙品牌 VARCHAR(40) PRIMARY KE

7、Y NOT NULL, 庫存數(shù)量 INT NULL, 庫存單價(jià) MONEY NULL, 庫存金額 MONEY NULL ) GO -創(chuàng)建觸發(fā)器，示例1 /* 創(chuàng)建觸發(fā)器T_INSERT_卷煙庫存表，這個(gè)觸發(fā)器較簡單。 說明： 每當(dāng)卷煙庫存表發(fā)生 INSERT 動作，則引發(fā)該觸發(fā)器。 觸發(fā)器功能： 強(qiáng)制執(zhí)行業(yè)務(wù)規(guī)則，保證插入的數(shù)據(jù)中，庫存金額 = 庫存數(shù)量 * 庫存單價(jià)。 注意： INSERTED、DELETED為系統(tǒng)表，不可創(chuàng)建、修改、刪除，但可以調(diào)用。 重要： 這兩個(gè)系統(tǒng)表的結(jié)構(gòu)同插入數(shù)據(jù)的表的結(jié)構(gòu)。 */ IF EXISTS (SELECT NAME FROM SYSOBJECTS WHE

8、RE XTYPE = TR AND NAME = T_INSERT_卷煙庫存表) DROP TRIGGER T_INSERT_卷煙庫存表 GO CREATE TRIGGER T_INSERT_卷煙庫存表 ON 卷煙庫存表 FOR INSERT AS -提交事務(wù)處理 BEGIN TRANSACTION -強(qiáng)制執(zhí)行下列語句，保證業(yè)務(wù)規(guī)則 UPDATE 卷煙庫存表 SET 庫存金額 = 庫存數(shù)量 * 庫存單價(jià) WHERE 卷煙品牌 IN (SELECT 卷煙品牌 from INSERTED) COMMIT TRANSACTION GO /* 針對卷煙庫存表，插入測試數(shù)據(jù)： 注意，第一條數(shù)據(jù)（紅塔山新

9、勢力）中的數(shù)據(jù)符合業(yè)務(wù)規(guī)則， 第二條數(shù)據(jù)（紅塔山人為峰）中，庫存金額空，不符合業(yè)務(wù)規(guī)則， 第三條數(shù)據(jù)（云南映像）中，庫存金額不等于庫存數(shù)量乘以庫存單價(jià)，不符合業(yè)務(wù)規(guī)則。 第四條數(shù)據(jù)庫存數(shù)量為0。 請注意在插入數(shù)據(jù)后，檢查卷煙庫存表中的數(shù)據(jù)是否 庫存金額 = 庫存數(shù)量 * 庫存單價(jià)。 */ INSERT INTO 卷煙庫存表(卷煙品牌,庫存數(shù)量,庫存單價(jià),庫存金額) SELECT 紅塔山新勢力,100,12,1200 UNION ALL SELECT 紅塔山人為峰,100,22,NULL UNION ALL SELECT 云南映像,100,60,500 UNION ALL SELECT ,0,3

10、0,0 GO -查詢數(shù)據(jù) SELECT * FROM 卷煙庫存表 GO /* 結(jié)果集 RecordId 卷煙品牌 庫存數(shù)量 庫存單價(jià) 庫存金額 - - - - - 1 紅塔山新勢力 100 12.0000 1200.0000 2 紅塔山人為峰 100 22.0000 2200.0000 3 云南映像 100 60.0000 6000.0000 4 0 30.0000 .0000 （所影響的行數(shù)為 4 行） */ -觸發(fā)器示例2 /* 創(chuàng)建觸發(fā)器T_INSERT_卷煙銷售表，該觸發(fā)器較復(fù)雜。 說明: 每當(dāng)卷煙庫存表發(fā)生 INSERT 動作，則引發(fā)該觸發(fā)器。 觸發(fā)器功能： 實(shí)現(xiàn)業(yè)務(wù)規(guī)則。 業(yè)務(wù)規(guī)則

11、: 如果銷售的卷煙品牌不存在庫存或者庫存為零，則返回錯(cuò)誤。 否則則自動減少卷煙庫存表中對應(yīng)品牌卷煙的庫存數(shù)量和庫存金額。 */ IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE XTYPE = TR AND NAME = T_INSERT_卷煙銷售表) DROP TRIGGER T_INSERT_卷煙銷售表 GO CREATE TRIGGER T_INSERT_卷煙銷售表 ON 卷煙銷售表 FOR INSERT AS BEGIN TRANSACTION -檢查數(shù)據(jù)的合法性：銷售的卷煙是否有庫存，或者庫存是否大于零 IF NOT EXISTS ( SELE

12、CT 庫存數(shù)量 FROM 卷煙庫存表 WHERE 卷煙品牌 IN (SELECT 卷煙品牌 FROM INSERTED) ) BEGIN -返回錯(cuò)誤提示 RAISERROR(錯(cuò)誤！該卷煙不存在庫存，不能銷售。,16,1) -回滾事務(wù) ROLLBACK RETURN END IF EXISTS ( SELECT 庫存數(shù)量 FROM 卷煙庫存表 WHERE 卷煙品牌 IN (SELECT 卷煙品牌 FROM INSERTED) AND 庫存數(shù)量 = 0 ) BEGIN -返回錯(cuò)誤提示 RAISERROR(錯(cuò)誤！該卷煙庫存小于等于0，不能銷售。,16,1) -回滾事務(wù) ROLLBACK RETURN

13、 END -對合法的數(shù)據(jù)進(jìn)行處理 -強(qiáng)制執(zhí)行下列語句，保證業(yè)務(wù)規(guī)則 UPDATE 卷煙銷售表 SET 銷售金額 = 銷售數(shù)量 * 銷售單價(jià) WHERE 卷煙品牌 IN (SELECT 卷煙品牌 FROM INSERTED) DECLARE 卷煙品牌 VARCHAR(40) SET 卷煙品牌 = (SELECT 卷煙品牌 FROM INSERTED) DECLARE 銷售數(shù)量 MONEY SET 銷售數(shù)量 = (SELECT 銷售數(shù)量 FROM INSERTED) UPDATE 卷煙庫存表 SET 庫存數(shù)量 = 庫存數(shù)量 - 銷售數(shù)量, 庫存金額 = (庫存數(shù)量 - 銷售數(shù)量)*庫存單價(jià) WHE

14、RE 卷煙品牌 = 卷煙品牌 COMMIT TRANSACTION GO -請大家自行跟蹤卷煙庫存表和卷煙銷售表的數(shù)據(jù)變化。 -針對卷煙銷售表，插入第一條測試數(shù)據(jù)，該數(shù)據(jù)是正常的。 INSERT INTO 卷煙銷售表(卷煙品牌,購貨商,銷售數(shù)量,銷售單價(jià),銷售金額) SELECT 紅塔山新勢力,某購貨商,10,12,1200 GO -針對卷煙銷售表，插入第二條測試數(shù)據(jù)，該數(shù)據(jù) 銷售金額 不等于 銷售單價(jià) * 銷售數(shù)量。 -觸發(fā)器將自動更正數(shù)據(jù)，使 銷售金額 等于 銷售單價(jià) * 銷售數(shù)量。 INSERT INTO 卷煙銷售表(卷煙品牌,購貨商,銷售數(shù)量,銷售單價(jià),銷售金額) SELECT 紅塔山

15、人為峰,某購貨商,10,22,2000 GO -針對卷煙銷售表，插入第三條測試數(shù)據(jù)，該數(shù)據(jù)中的卷煙品牌在 卷煙庫存表中找不到對應(yīng)。 -觸發(fā)器將報(bào)錯(cuò)。 INSERT INTO 卷煙銷售表(卷煙品牌,購貨商,銷售數(shù)量,銷售單價(jià),銷售金額) SELECT 紅河V8,某購貨商,10,60,600 GO /* 結(jié)果集 服務(wù)器: 消息 50000，級別 16，狀態(tài) 1，過程 T_INSERT_卷煙銷售表，行 15 錯(cuò)誤！該卷煙不存在庫存，不能銷售。 */ -針對卷煙銷售表，插入第三條測試數(shù)據(jù)，該數(shù)據(jù)中的卷煙品牌在 卷煙庫存表中庫存為0。 -觸發(fā)器將報(bào)錯(cuò)。 INSERT INTO 卷煙銷售表(卷煙品牌,購貨

16、商,銷售數(shù)量,銷售單價(jià),銷售金額) SELECT ,某購貨商,10,30,300 GO /* 結(jié)果集 服務(wù)器: 消息 50000，級別 16，狀態(tài) 1，過程 T_INSERT_卷煙銷售表，行 29 錯(cuò)誤！該卷煙庫存小于等于0，不能銷售。 */ -查詢數(shù)據(jù) SELECT * FROM 卷煙庫存表 SELECT * FROM 卷煙銷售表 GO /* 補(bǔ)充： 1、本示例主要通過一個(gè)簡單的業(yè)務(wù)規(guī)則實(shí)現(xiàn)來進(jìn)行觸發(fā)器使用的說明，具體的要根據(jù)需要靈活處理； 2、關(guān)于觸發(fā)器要理解并運(yùn)用好 INSERTED ，DELETED 兩個(gè)系統(tǒng)表； 3、本示例創(chuàng)建的觸發(fā)器都是 FOR INSERT ,具體的語法可參考：

17、Trigger語法 CREATE TRIGGER trigger_name ON table | view WITH ENCRYPTION -用于加密觸發(fā)器 FOR | AFTER | INSTEAD OF INSERT , UPDATE WITH APPEND NOT FOR REPLICATION AS IF UPDATE ( column ) AND | OR UPDATE ( column ) .n | IF ( COLUMNS_UPDATED ( ) bitwise_operator updated_bitmask ) comparison_operator column_bitma

18、sk .n sql_statement .n 4、關(guān)于觸發(fā)器，還應(yīng)該注意 (1)、DELETE 觸發(fā)器不能捕獲 TRUNCATE TABLE 語句。 (2)、觸發(fā)器中不允許以下 Transact-SQL 語句： ALTER DATABASE CREATE DATABASE DISK INIT DISK RESIZE DROP DATABASE LOAD DATABASE LOAD LOG RECONFIGURE RESTORE DATABASE RESTORE LOG (3)、觸發(fā)器最多可以嵌套 32 層。 */ -修改觸發(fā)器 -實(shí)質(zhì)上，是將 CREATE TRIGGER . 修改為 ALTE

19、R TRIGGER .即可。 -刪除觸發(fā)器 DROP TRIGGER *x GO -刪除測試環(huán)境 DROP TABLE 卷煙庫存表 GO DROP TABLE 卷煙銷售表 GO DROP TRIGGER T_INSERT_卷煙庫存表 GO DROP TRIGGER T_INSERT_卷煙銷售表 GO * 觸發(fā)器的基礎(chǔ)知識和例子 ：create trigger tr_name on table/view for | after | instead of update,insert,delete with encryption as batch | if update (col_name) and

20、|or update (col_name) 說明： 1 tr_name ：觸發(fā)器名稱 2 on table/view ：觸發(fā)器所作用的表。一個(gè)觸發(fā)器只能作用于一個(gè)表 3 for 和after ：同義 4 after 與instead of :sql 2000新增項(xiàng)目afrer 與 instead of 的區(qū)別 After 在觸發(fā)事件發(fā)生以后才被激活,只可以建立在表上 Instead of 代替了相應(yīng)的觸發(fā)事件而被執(zhí)行,既可以建立在表上也可以建立在視圖上 5 insert、update、delete：激活觸發(fā)器的三種操作，可以同時(shí)執(zhí)行，也可選其一 6 if update (col_name)：表

21、明所作的操作對指定列是否有影響，有影響，則激活觸發(fā)器。此外，因?yàn)閐elete 操作只對行有影響， 所以如果使用delete操作就不能用這條語句了(雖然使用也不出錯(cuò)，但是不能激活觸發(fā)器，沒意義)。 7 觸發(fā)器執(zhí)行時(shí)用到的兩個(gè)特殊表：deleted ,inserted deleted 和inserted 可以說是一種特殊的臨時(shí)表，是在進(jìn)行激活觸發(fā)器時(shí)由系統(tǒng)自動生成的，其結(jié)構(gòu)與觸發(fā)器作用的表結(jié)構(gòu)是一 樣的，只是存放 的數(shù)據(jù)有差異。 續(xù) 下面表格說明deleted 與inserted 數(shù)據(jù)的差異 deleted 與inserted 數(shù)據(jù)的差異 Inserted 存放進(jìn)行insert和update 操作

22、后的數(shù)據(jù) Deleted 存放進(jìn)行delete 和update操作前的數(shù)據(jù) 注意：update 操作相當(dāng)于先進(jìn)行delete 再進(jìn)行insert ,所以在進(jìn)行update操作時(shí)，修改前的數(shù)據(jù)拷貝一條到deleted 表中，修改后 的數(shù)據(jù)在存到觸發(fā)器作用的表的同時(shí)，也同時(shí)生成一條拷貝到insered表中接(一)-觸發(fā)器示例2/*創(chuàng)建觸發(fā)器T_INSERT_卷煙銷售表，該觸發(fā)器較復(fù)雜。說明: 每當(dāng)卷煙庫存表發(fā)生 INSERT 動作，則引發(fā)該觸發(fā)器。觸發(fā)器功能： 實(shí)現(xiàn)業(yè)務(wù)規(guī)則。業(yè)務(wù)規(guī)則: 如果銷售的卷煙品牌不存在庫存或者庫存為零，則返回錯(cuò)誤。否則則自動減少卷煙庫存表中對應(yīng)品牌卷煙的庫存數(shù)量和庫存金額

23、。*/IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE XTYPE = TR AND NAME = T_INSERT_卷煙銷售表)DROP TRIGGER T_INSERT_卷煙銷售表GOCREATE TRIGGER T_INSERT_卷煙銷售表ON 卷煙銷售表FOR INSERTASBEGIN TRANSACTION-檢查數(shù)據(jù)的合法性：銷售的卷煙是否有庫存，或者庫存是否大于零IF NOT EXISTS (SELECT 庫存數(shù)量FROM 卷煙庫存表WHERE 卷煙品牌 IN (SELECT 卷煙品牌 FROM INSERTED)BEGIN-返回錯(cuò)誤提示R

24、AISERROR(錯(cuò)誤！該卷煙不存在庫存，不能銷售。,16,1)-回滾事務(wù)ROLLBACKRETURNENDIF EXISTS (SELECT 庫存數(shù)量FROM 卷煙庫存表WHERE 卷煙品牌 IN (SELECT 卷煙品牌 FROM INSERTED) AND庫存數(shù)量 = 0)BEGIN-返回錯(cuò)誤提示RAISERROR(錯(cuò)誤！該卷煙庫存小于等于0，不能銷售。,16,1)-回滾事務(wù)ROLLBACKRETURNEND-對合法的數(shù)據(jù)進(jìn)行處理-強(qiáng)制執(zhí)行下列語句，保證業(yè)務(wù)規(guī)則UPDATE 卷煙銷售表SET 銷售金額 = 銷售數(shù)量 * 銷售單價(jià)WHERE 卷煙品牌 IN (SELECT 卷煙品牌 FRO

25、M INSERTED)DECLARE 卷煙品牌 VARCHAR(40)SET 卷煙品牌 = (SELECT 卷煙品牌 FROM INSERTED)DECLARE 銷售數(shù)量 MONEYSET 銷售數(shù)量 = (SELECT 銷售數(shù)量 FROM INSERTED)UPDATE 卷煙庫存表SET 庫存數(shù)量 = 庫存數(shù)量 - 銷售數(shù)量,庫存金額 = (庫存數(shù)量 - 銷售數(shù)量)*庫存單價(jià)WHERE 卷煙品牌 = 卷煙品牌COMMIT TRANSACTIONGO-請大家自行跟蹤卷煙庫存表和卷煙銷售表的數(shù)據(jù)變化。-針對卷煙銷售表，插入第一條測試數(shù)據(jù)，該數(shù)據(jù)是正常的。INSERT INTO 卷煙銷售表(卷煙品牌

26、,購貨商,銷售數(shù)量,銷售單價(jià),銷售金額)SELECT 紅塔山新勢力,某購貨商,10,12,1200GO-針對卷煙銷售表，插入第二條測試數(shù)據(jù)，該數(shù)據(jù) 銷售金額 不等于 銷售單價(jià) * 銷售數(shù)量。-觸發(fā)器將自動更正數(shù)據(jù)，使 銷售金額 等于 銷售單價(jià) * 銷售數(shù)量。INSERT INTO 卷煙銷售表(卷煙品牌,購貨商,銷售數(shù)量,銷售單價(jià),銷售金額)SELECT 紅塔山人為峰,某購貨商,10,22,2000GOSQL 擁有很多可用于計(jì)數(shù)和計(jì)算的內(nèi)建函數(shù)。函數(shù)的語法內(nèi)建 SQL 函數(shù)的語法是：SELECT function(列) FROM 表函數(shù)的類型在 SQL 中，基本的函數(shù)類型和種類有若干種。函數(shù)的基

27、本類型是： Aggregate 函數(shù) Scalar 函數(shù) 合計(jì)函數(shù)（Aggregate functions）Aggregate 函數(shù)的操作面向一系列的值，并返回一個(gè)單一的值。注釋：如果在 SELECT 語句的項(xiàng)目列表中的眾多其它表達(dá)式中使用 SELECT 語句，則這個(gè) SELECT 必須使用 GROUP BY 語句！Persons table (在大部分的例子中使用過)NameAgeAdams, John38Bush, George33Carter, Thomas28MS Access 中的合計(jì)函數(shù)函數(shù)描述AVG(column)返回某列的平均值COUNT(column)返回某列的行數(shù)（不包括

28、NULL 值）COUNT(*)返回被選行數(shù)FIRST(column)返回在指定的域中第一個(gè)記錄的值LAST(column)返回在指定的域中最后一個(gè)記錄的值MAX(column)返回某列的最高值MIN(column)返回某列的最低值STDEV(column)STDEVP(column)SUM(column)返回某列的總和VAR(column)VARP(column)在 SQL Server 中的合計(jì)函數(shù)函數(shù)描述AVG(column)返回某列的行數(shù)BINARY_CHECKSUMCHECKSUMCHECKSUM_AGGCOUNT(column)返回某列的行數(shù)（不包括NULL值）COUNT(*)返回被

29、選行數(shù)COUNT(DISTINCT column)返回相異結(jié)果的數(shù)目FIRST(column)返回在指定的域中第一個(gè)記錄的值（SQLServer2000 不支持）LAST(column)返回在指定的域中最后一個(gè)記錄的值（SQLServer2000 不支持）MAX(column)返回某列的最高值MIN(column)返回某列的最低值STDEV(column)STDEVP(column)SUM(column)返回某列的總和VAR(column)VARP(column)SQLServer 游標(biāo)簡介與使用說明游標(biāo)（Cursor）是處理數(shù)據(jù)的一種方法，為了查看或者處理結(jié)果集中的數(shù)據(jù)，游標(biāo)提供了在結(jié)果集中

30、一次以行或者多行前進(jìn)或向后瀏覽數(shù)據(jù)的能力。我們可以把游標(biāo)當(dāng)作一個(gè)指針，它可以指定結(jié)果中的任何位置，然后允許用戶對指定位置的數(shù)據(jù)進(jìn)行處理。1.游標(biāo)的組成游標(biāo)包含兩個(gè)部分：一個(gè)是游標(biāo)結(jié)果集、一個(gè)是游標(biāo)位置。游標(biāo)結(jié)果集：定義該游標(biāo)得SELECT語句返回的行的集合。游標(biāo)位置：指向這個(gè)結(jié)果集某一行的當(dāng)前指針。2.游標(biāo)的分類游標(biāo)共有3類：API服務(wù)器游標(biāo)、Transaction-SQL游標(biāo)和API客戶端游標(biāo)。其中前兩種游標(biāo)都是運(yùn)行在服務(wù)器上的，所以又叫做服務(wù)器游標(biāo)。API服務(wù)器游標(biāo)API服務(wù)器游標(biāo)主要應(yīng)用在服務(wù)上，當(dāng)客戶端的應(yīng)用程序調(diào)用API游標(biāo)函數(shù)時(shí)，服務(wù)器會對API函數(shù)進(jìn)行處理。使用API函數(shù)和方法可

31、以實(shí)現(xiàn)如下功能：（）打開一個(gè)連接。（）設(shè)置定義游標(biāo)特征的特性或?qū)傩?，API自動將游標(biāo)影射到每個(gè)結(jié)果集。（）執(zhí)行一個(gè)或多個(gè)Transaction-SQL語句。（）使用API函數(shù)或方法提取結(jié)果集中的行。API服務(wù)器游標(biāo)包含以下四種：靜態(tài)游標(biāo)、動態(tài)游標(biāo)、只進(jìn)游標(biāo)、鍵集驅(qū)動游標(biāo)（Primary key）靜態(tài)游標(biāo)的完整結(jié)果集將打開游標(biāo)時(shí)建立的結(jié)果集存儲在臨時(shí)表中，（靜態(tài)游標(biāo)始終是只讀的）。靜態(tài)游標(biāo)具有以下特點(diǎn)：總是按照打開游標(biāo)時(shí)的原樣顯示結(jié)果集；不反映數(shù)據(jù)庫中作的任何修改，也不反映對結(jié)果集行的列值所作的更改；不顯示打開游標(biāo)后在數(shù)據(jù)庫中新插入的行；組成結(jié)果集的行被其他用戶更新，新的數(shù)據(jù)值不會顯示在靜態(tài)游標(biāo)

32、中；但是靜態(tài)游標(biāo)會顯示打開游標(biāo)以后從數(shù)據(jù)庫中刪除的行。動態(tài)游標(biāo)與靜態(tài)游標(biāo)相反，當(dāng)滾動游標(biāo)時(shí)動態(tài)游標(biāo)反映結(jié)果集中的所有更改。結(jié)果集中的行數(shù)據(jù)值、順序和成員每次提取時(shí)都會改變。只進(jìn)游標(biāo)不支持滾動，它只支持游標(biāo)從頭到尾順序提取數(shù)據(jù)行。注意：只進(jìn)游標(biāo)也反映對結(jié)果集所做的所有更改。鍵集驅(qū)動游標(biāo)同時(shí)具有靜態(tài)游標(biāo)和動態(tài)游標(biāo)的特點(diǎn)。當(dāng)打開游標(biāo)時(shí)，該游標(biāo)中的成員以及行的順序是固定的，鍵集在游標(biāo)打開時(shí)也會存儲到臨時(shí)工作表中，對非鍵集列的數(shù)據(jù)值的更改在用戶游標(biāo)滾動的時(shí)候可以看見，在游標(biāo)打開以后對數(shù)據(jù)庫中插入的行是不可見的，除非關(guān)閉重新打開游標(biāo)。Transaction-SQL游標(biāo)該游標(biāo)是基于Declare Curso

33、r 語法，主要用于Transaction-SQL腳本、存儲過程以及觸發(fā)器中。Transaction-SQL游標(biāo)在服務(wù)器處理由客戶端發(fā)送到服務(wù)器的Transaction-SQL語句。在存儲過程或觸發(fā)器中使用Transaction-SQL游標(biāo)的過程為：（）聲明Transaction-SQL變量包含游標(biāo)返回的數(shù)據(jù)。為每個(gè)結(jié)果集列聲明一個(gè)變量。聲明足夠大的變量來保存列返回的值，并聲明變量的類型為可從數(shù)據(jù)類型隱式轉(zhuǎn)換得到的數(shù)據(jù)類型。（）使用Declare Cursor語句將Transaction-SQL游標(biāo)與Select語句相關(guān)聯(lián)。還可以利用Declare Cursor定義游標(biāo)的只讀、只進(jìn)等特性。（）使

34、用Open語句執(zhí)行Select語句填充游標(biāo)。（）使用Fetch Into語句提取單個(gè)行，并將每列中得數(shù)據(jù)移至指定的變量中。注意：其他Transaction-SQL語句可以引用那些變量來訪問提取的數(shù)據(jù)值。Transaction-SQL游標(biāo)不支持提取行塊。（）使用Close語句結(jié)束游標(biāo)的使用。注意：關(guān)閉游標(biāo)以后，該游標(biāo)還是存在，可以使用Open命令打開繼續(xù)使用，只有調(diào)用Deallocate語句才會完全釋放?？蛻舳擞螛?biāo)該游標(biāo)將使用默認(rèn)結(jié)果集把整個(gè)結(jié)果集高速緩存在客戶端上，所有的游標(biāo)操作都在客戶端的高速緩存中進(jìn)行。注意：客戶端游標(biāo)只支持只進(jìn)和靜態(tài)游標(biāo)。不支持其他游標(biāo)。3.游標(biāo)的生命周期游標(biāo)的生命周期包

35、含有五個(gè)階段：聲明游標(biāo)、打開游標(biāo)、讀取游標(biāo)數(shù)據(jù)、關(guān)閉游標(biāo)、釋放游標(biāo)。聲明游標(biāo)是為游標(biāo)指定獲取數(shù)據(jù)時(shí)所使用的Select語句，聲明游標(biāo)并不會檢索任何數(shù)據(jù)，它只是為游標(biāo)指明了相應(yīng)的Select語句。Declare 游標(biāo)名稱 Cursor 參數(shù)聲明游標(biāo)的參數(shù)（）Local與Global:Local表示游標(biāo)的作用于僅僅限于其所在的存儲過程、觸發(fā)器以及批處理中、執(zhí)行完畢以后游標(biāo)自動釋放。Global表示的是該游標(biāo)作用域是整個(gè)會話層。由連接執(zhí)行的任何存儲過程、批處理等都可以引用該游標(biāo)名稱，僅在斷開連接時(shí)隱性釋放。（）Forward_only與Scroll:前者表示為只進(jìn)游標(biāo)，后者表示為可以隨意定位。默認(rèn)為

36、前者。（）Static、Keyset與Dynamic: 第一個(gè)表示定義一個(gè)游標(biāo)，其數(shù)據(jù)存放到一個(gè)臨時(shí)表內(nèi)，對游標(biāo)的所有請求都從臨時(shí)表中應(yīng)答，因此，對該游標(biāo)進(jìn)行提取操作時(shí)返回的數(shù)據(jù)不反映對基表所作的修改，并且該游標(biāo)不允許修改。Keyset表示的是，當(dāng)游標(biāo)打開時(shí)，鍵集驅(qū)動游標(biāo)中行的身份與順序是固定的，并把其放到臨時(shí)表中。Dynamic表示的是滾動游標(biāo)時(shí)，動態(tài)游標(biāo)反映對結(jié)果集內(nèi)所有數(shù)據(jù)的更改。（）Read_only、Scroll_Locks與Optimistic：第一個(gè)表示的是只讀游標(biāo)，第二個(gè)表示的是在使用的游標(biāo)結(jié)果集數(shù)據(jù)上放置鎖，當(dāng)行讀取到游標(biāo)中然后對它們進(jìn)行修改時(shí)，數(shù)據(jù)庫將鎖定這些行，以保證數(shù)據(jù)

37、的一致性。Optimistic的含義是游標(biāo)將數(shù)據(jù)讀取以后，如果這些數(shù)據(jù)被更新了，則通過游標(biāo)定位進(jìn)行的更新與刪除操作將不會成功。標(biāo)準(zhǔn)游標(biāo)：Declare MyCursor CursorFor Select * From Master_Goods只讀游標(biāo)Declare MyCusror CursorFor Select * From Master_GoodsFor Read Only可更新游標(biāo)Declare MyCusror CursorFor Select * From Master_GoodsFor UpDate打開游標(biāo)使用Open語句用于打開Transaction-SQL服務(wù)器游標(biāo)，執(zhí)行Op

38、en語句的過程中就是按照Select語句進(jìn)行填充數(shù)據(jù)，打開游標(biāo)以后游標(biāo)位置在第一行。打開游標(biāo)全局游標(biāo)：Open Global MyCursor局部游標(biāo): Open MyCursor讀取游標(biāo)數(shù)據(jù)：在打開游標(biāo)以后，使用Fetch語句從Transaction-SQL服務(wù)器游標(biāo)中檢索特定的一行。使用Fetch操作，可以使游標(biāo)移動到下一個(gè)記錄，并將游標(biāo)返回的每個(gè)列得數(shù)據(jù)分別賦值給聲明的本地變量。Fetch Next | Prior | First | Last | Absoluten| Relativen From MyCursor Into GoodsID,GoodsName其中:Next表示返回結(jié)果

39、集中當(dāng)前行的下一行記錄，如果第一次讀取則返回第一行。默認(rèn)的讀取選項(xiàng)為NextPrior表示返回結(jié)果集中當(dāng)前行的前一行記錄，如果第一次讀取則沒有行返回，并且把游標(biāo)置于第一行之前。First表示返回結(jié)果集中的第一行，并且將其作為當(dāng)前行。 Last表示返回結(jié)果集中的最后一行，并且將其作為當(dāng)前行。Absoluten如果n為正數(shù)，則返回從游標(biāo)頭開始的第n行，并且返回行變成新的當(dāng)前行。如果n為負(fù)，則返回從游標(biāo)末尾開始的第n行，并且返回行為新的當(dāng)前行，如果n為0，則返回當(dāng)前行。 Relativen如果n為正數(shù)，則返回從當(dāng)前行開始的第n行，如果n為負(fù),則返回從當(dāng)前行之前的第n行，如果為0，則返回當(dāng)前行。關(guān)閉游

40、標(biāo)調(diào)用的是Close語句，方式如下：Close Global MyCursor Close MyCursor釋放游標(biāo)調(diào)用的是Deallocate語句，方法如下：Deallocate Glboal MyCursorDeallocate MyCursor游標(biāo)實(shí)例:Declare MyCusror Cursor ScrollFor Select * From Master_Goods Order By GoodsIDOpen MyCursorFetch next From MyCursorInto GoodsCode,GoodsNameWhile(Fetch_Status = 0)BeginBegi

41、nSelect GoodsCode = Convert(Char(20),GoodsCode)Select GoodsName = Convert(Char(20),GoodsName)PRINT GoodsCode + : + GoodsNameEndFetch next From MyCursorInto GoodsCode,GoodsNameEndClose MyCursorDeallocate MyCursor修改當(dāng)前游標(biāo)的數(shù)據(jù)方法如下:UpDateMaster_Goods Set GoodsName = yangyang8848 Where Current Of MyCursor;刪

42、除當(dāng)前游標(biāo)行數(shù)據(jù)的方法如下:Delete From Master_Goods Where Current Of MyCursorSelect CURSOR_ROWS 可以得到當(dāng)前游標(biāo)中存在的數(shù)據(jù)行數(shù)。注意：此變量為一個(gè)連接上的全局變量，因此只對應(yīng)最后一次打開的游標(biāo)。SQLServer 存儲過程簡介與使用方法Sql Server的存儲過程是一個(gè)被命名的存儲在服務(wù)器上的Transacation-Sql語句集合,是封裝重復(fù)性工作的一種方法,它支持用戶聲明的變量、條件執(zhí)行和其他強(qiáng)大的編程功能。存儲過程相對于其他的數(shù)據(jù)庫訪問方法有以下的優(yōu)點(diǎn)：（1）重復(fù)使用。存儲過程可以重復(fù)使用，從而可以減少數(shù)據(jù)庫開發(fā)人

43、員的工作量。（2）提高性能。存儲過程在創(chuàng)建的時(shí)候就進(jìn)行了編譯，將來使用的時(shí)候不用再重新編譯。一般的SQL語句每執(zhí)行一次就需要編譯一次，所以使用存儲過程提高了效率。（3）減少網(wǎng)絡(luò)流量。存儲過程位于服務(wù)器上，調(diào)用的時(shí)候只需要傳遞存儲過程的名稱以及參數(shù)就可以了，因此降低了網(wǎng)絡(luò)傳輸?shù)臄?shù)據(jù)量。（4）安全性。參數(shù)化的存儲過程可以防止SQL注入式的攻擊，而且可以將Grant、Deny以及Revoke權(quán)限應(yīng)用于存儲過程。存儲過程一共分為了三類：用戶定義的存儲過程、擴(kuò)展存儲過程以及系統(tǒng)存儲過程。其中，用戶定義的存儲過程又分為Transaction-SQL和CLR兩種類型。Transaction-SQL 存儲過程

44、是指保存的Transaction-SQL語句集合，可以接受和返回用戶提供的參數(shù)。CLR存儲過程是指對.Net Framework公共語言運(yùn)行時(shí)(CLR)方法的引用，可以接受和返回用戶提供的參數(shù)。他們在.Net Framework程序集中是作為類的公共靜態(tài)方法實(shí)現(xiàn)的。（本文就不作介紹了）創(chuàng)建存儲過程的語句如下：CodeCREATEPROC|PROCEDUREschema_cedure_name;numberparametertype_schema_name.data_typeVARYING=defaultOUTPUT,nWITH,nFORREPLICATIONAS;n|;:=EN

45、CRYPTIONRECOMPILEEXECUTE_AS_Clause:=BEGINstatementsEND:=EXTERNALNAMEassembly_name.class_name.method_nameschema_name: 代表的是存儲過程所屬的架構(gòu)的名稱例如：Create Schema yangyang8848GoCreate Proc yangyang8848.AllGoodsAs Select * From Master_GoodsGo執(zhí)行：Exec AllGoods 發(fā)生錯(cuò)誤。執(zhí)行：Exec yangyang8848.AllGoods 正確執(zhí)行。；Number: 用于對同名過

46、程進(jìn)行分組的可選整數(shù)。使用一個(gè) DROP PROCEDURE 語句可將這些分組過程一起刪除。例如：Create Proc S1 ;1ASSelect * From Master_GoodsGoCreate Proc S1 ;2AsSelect * From Master_LocationGo創(chuàng)建完畢了兩個(gè)存儲過程。它們在同一個(gè)組S1里，如果執(zhí)行Exec S1 則存儲過程默認(rèn)執(zhí)行 Exec S1 ;1 。如果我們想得到所有據(jù)點(diǎn)信息則需要執(zhí)行Exec S1 ;2。當(dāng)我們要刪除存儲過程的時(shí)候，只能執(zhí)行Drop Exec S1 則該組內(nèi)所有的存儲過程被刪除。 parameter: 存儲過程中的參數(shù)，除

47、非將參數(shù)定義的時(shí)候有默認(rèn)值或者將參數(shù)設(shè)置為等于另一個(gè)參數(shù)，否則用戶必須在調(diào)用存儲過程的時(shí)候?yàn)閰?shù)賦值。存儲過程最多有2100個(gè)參數(shù)。例如：Create Proc yangyang8848.OneGoodsGoodsCode varchar(10)AsSelect * From Master_Goods Where GoodsCode = GoodsCodeGo調(diào)用的代碼：Declare Code varchar(10)Set Code = 0004Exec yangyang8848.OneGoods Code在參數(shù)的后邊加入Output 表明該參數(shù)為輸出參數(shù)。Create Proc yangya