數(shù)據(jù)庫原理及應(yīng)用.ppt

《數(shù)據(jù)庫原理及應(yīng)用.ppt》由會員分享，可在線閱讀，更多相關(guān)《數(shù)據(jù)庫原理及應(yīng)用.ppt（120頁珍藏版）》請在裝配圖網(wǎng)上搜索。

1、1,第五章 T-SQL,本章要求： 了解T-SQL的基礎(chǔ)知識 了解存儲過程的使用 了解觸發(fā)器的作用及其使用方法 了解游標(biāo)的作用及其使用方法 理解事務(wù)的概念和基本操作,2,第五章 T-SQL,本章內(nèi)容：,T-SQL基礎(chǔ),1,,,3,T-SQL基礎(chǔ),T-SQL定義、功能、組成 定義 Transact-SQL（簡寫為T-SQL）是SQL Server對標(biāo)準(zhǔn)SQL功能的增強(qiáng)與擴(kuò)充 功能 T-SQL可以完成數(shù)據(jù)庫上的各種操作，而且可以編制復(fù)雜的例行程序 組成,系統(tǒng)內(nèi)置函數(shù),變量 顯示和輸出語句 流程控制語句 CASE表達(dá)式,標(biāo)識符 注 釋 批處理 運算符,4,T-SQL基礎(chǔ),標(biāo)識符 定義 標(biāo)識符是指用戶

2、在SQL Server中定義的服務(wù)器、數(shù)據(jù)庫、數(shù)據(jù)庫對象、變量和列等對象名稱 分類 常規(guī)標(biāo)識符 定界標(biāo)識符,5,T-SQL基礎(chǔ),常規(guī)標(biāo)識符 命名原則 標(biāo)識符長度可以為1128個字符。 標(biāo)識符的首字符必須為Unicode 2.0標(biāo)準(zhǔn)所定義的字母或_、、符號。 標(biāo)識符第一個字符后面的字符可以為Unicode Standard 2.0所定義的字符、數(shù)字或、#、$、_符號 標(biāo)識符內(nèi)不能嵌入空格和特殊字符。 標(biāo)識符不能與SQL Server中的保留關(guān)鍵字同名,SELECT * FROM information,,,此為常規(guī)標(biāo)識符,6,T-SQL基礎(chǔ),定界標(biāo)識符 實質(zhì) 包含在雙引號 (“) 或者方括號 (

3、 ) 內(nèi)的標(biāo)識符號 示例 【例5-1】,CREATE table table （ column1 CHAR（10） NOT NULL PRIMARY KEY， column2 INT ）,表名Table與T-SQL保留字相同，用方括號來分隔,7,T-SQL基礎(chǔ),注釋 定義 注釋是程序代碼中不執(zhí)行的文本字符串。 分類 --- 用于單行注釋 /* */ 用于多行注釋,8,【例5-2】使用單行與多行注釋,,T-SQL基礎(chǔ),USE School GO SELECT * FROM Student- -從表Student中查詢信息 GO /*下面SQL語句 查詢學(xué)生信息*/ SELECT *

4、FROM Student GO,9,T-SQL基礎(chǔ),批處理 說明 多條語句作為一個批處理執(zhí)行時，其語句之間用GO分隔 【例5-3】批處理語句 USE School GO CREATE VIEW v_Student AS SELECT * FROM Student GO SELECT * FROM v_Student GO,10,T-SQL基礎(chǔ),運算符 算術(shù)運算符 位運算符 比較運算符 邏輯運算符,賦值運算符 字符串連接符 單目運算符 運算符優(yōu)先級,11,T-SQL基礎(chǔ),算術(shù)運算符 加（+） 減（-） 乘（*） 除（/） 取模（%）,12,T-SQL基礎(chǔ),位運算符 對整數(shù)或二進(jìn)制數(shù)據(jù)進(jìn)行按位邏輯

5、運算 與（ SELECT birthday =Birthday FROM Student WHERE SName=張三 PRINT 張三的生日是：+Convert（CHAR（20），birthday）,46,T-SQL基礎(chǔ),字符串函數(shù),47,T-SQL基礎(chǔ),【例5-18】使用 xxx 替換 abcdefghi 中的字符串 cde,SELECT REPLACE（abcdefghi，cde，xxx）,【例5-19】使用LTRIM消除字符串左邊的空格,SELECT 消除左邊空格的結(jié)果：+LTRIM（ 1234 ）,48,T-SQL基礎(chǔ),日期和時間函數(shù),49,T-SQL基礎(chǔ),【例5-20】取得系統(tǒng)當(dāng)

6、前日期,SELECT 當(dāng)前日期：+convert(CHAR(50),GETDATE()),【例5-21】取得系統(tǒng)當(dāng)前日期中的月份,SELECT 月份為：+ convert(CHAR(10), DATEPART(month， GETDATE())),50,T-SQL基礎(chǔ),聚合函數(shù),【例5-22】取得數(shù)據(jù)庫所在的計算機(jī)名,SELECT HOST_NAME（）,51,T-SQL基礎(chǔ),數(shù)學(xué)函數(shù),52,T-SQL基礎(chǔ),【例5-23】顯示使用 CEILING 函數(shù)的正數(shù)、負(fù)數(shù)和零值,SELECT CEILING（123.45）， CEILING（-123.45）， CEILING（0.0

7、）,結(jié)果：124 -123 0,53,第五章 T-SQL,本章內(nèi)容：,T-SQL基礎(chǔ),1,,,54,存儲過程,簡介 定義： SQL Server提供的一種方法，它可以將一些固定的操作集中起來由SQL Server數(shù)據(jù)庫服務(wù)器來完成，以實現(xiàn)某個任務(wù) 分類 系統(tǒng)存儲過程 本地存儲過程 臨時存儲過程 遠(yuǎn)程存儲過程 擴(kuò)展存儲過程 CLR過程,55,存儲過程,系統(tǒng)存儲過程 系統(tǒng)存儲過程就是系統(tǒng)創(chuàng)建的存儲過程，是在SQL Server安裝成功后，就已經(jīng)存儲在系統(tǒng)數(shù)據(jù)庫中了，目的在于能夠方便地從系統(tǒng)表中查詢信息或完成與更新數(shù)據(jù)庫表相關(guān)的管理任務(wù)或其它的系統(tǒng)管理任務(wù) 系統(tǒng)過程以“sp_”為開頭，并存放在“sy

8、s”構(gòu)架中，為數(shù)據(jù)庫管理者所有。,56,存儲過程,本地存儲過程 本地存儲過程是在用戶數(shù)據(jù)庫中創(chuàng)建的存儲過程。本地存儲過程實際上就是用戶存儲過程，在以后的數(shù)據(jù)庫開發(fā)中，涉及到的存儲過程幾乎都是本地存儲過程。 這種存儲過程是用戶自己創(chuàng)建的普通數(shù)據(jù)庫，完成特定數(shù)據(jù)庫操作任務(wù)，其名稱不能以“sp_”為前綴。,57,存儲過程,臨時存儲過程 屬于本地存儲過程。我們在創(chuàng)建本地存儲過程時，如果在本地存儲過程的名稱前有一個“#”，該存儲過程就被稱為局部臨時存儲過程，只能在一個用戶會話中使用；如果在本地存儲過程的名稱前有兩個“##”，該存儲過程就被稱為全局臨時存儲過程，可以在所有用戶會話中使用。 遠(yuǎn)程存儲過程 指

9、從遠(yuǎn)程服務(wù)器上調(diào)用的存儲過程，也就是非本地服務(wù)器上的存儲過程。,58,存儲過程,擴(kuò)展存儲過程 是指在SQL Server環(huán)境之外執(zhí)行的動態(tài)鏈接庫DLL。SQL Server可以動態(tài)裝載并執(zhí)行它們。擴(kuò)展存儲過程只能添加到Master數(shù)據(jù)庫。 CLR過程 是SQL Server 2005新增的存儲過程，是針對微軟的.NET Framework建立的，可以接受和返回用戶提供的參數(shù)。CLR存儲過程要在Microsoft Visual Studio 2005中來實現(xiàn),59,存儲過程,存儲過程優(yōu)點 存儲過程因為SQL語句已經(jīng)預(yù)編繹過了，因此運行的速度比較快，平均要比SQL語句執(zhí)行速度快上210倍。 存儲過

10、程可以接受輸入?yún)?shù)、輸出參數(shù)、返回單個或多個結(jié)果集以及返回值?？梢韵虺绦蚍祷劐e誤原因。 存儲過程可以包含程序流、邏輯以及對數(shù)據(jù)庫的查詢。同時隱藏了數(shù)據(jù)邏輯，提高系統(tǒng)安全性 存儲過程運行比較穩(wěn)定，不會有太多的錯誤。只要一次成功，以后都會按這個程序運行。 存儲過程主要是在服務(wù)器上運行，減少對客戶機(jī)的壓力,60,存儲過程,T-SQL創(chuàng)建和執(zhí)行存儲過程 基本語句 創(chuàng)建： CREATE PROCEDURE 存儲過程名 參數(shù)1 數(shù)據(jù)類型 = 默認(rèn)值 OUTPUT， ， 參數(shù)n 數(shù)據(jù)類型 = 默認(rèn)值 OUTPUT AS SQL語句 執(zhí)行： EXEC procedure_name Value_List,

11、61,存儲過程,【例5-24】創(chuàng)建一存儲過程，檢索軟件學(xué)院學(xué)生的記錄,USE student GO CREATE PROCEDURE pr_student AS SELECT SNO, SName ，Ssex FROM Student WHERE SDept=軟件學(xué)院,62,存儲過程,【例5-25】執(zhí)行一個名為pr_student的存儲過程,USE student GO pr_studnet,63,存儲過程,【例5-26】創(chuàng)建一存儲過程，插入一個課程信息,CREATE PROC AddCourse (c_no VARCHAR(12), c_name VARCHAR(20), c_period

12、INT, c_credit INT ) AS INSERT INTO Course VALUES(c_no, c_name, c_period,c_credit ),64,存儲過程,【例5-27】調(diào)用AddCourse存儲過程插入一條課程號為gdsx、課程名為高等數(shù)學(xué)、學(xué)時80、學(xué)分3的記錄,EXEC AddCourse gdsx,高等數(shù)學(xué),80,3,65,存儲過程,修改和刪除存儲過程 基本語句格式 修改 ALTER PROCEDURE 存儲過程名 參數(shù)1 數(shù)據(jù)類型 = 默認(rèn)值 OUTPUT， ， 參數(shù)n 數(shù)據(jù)類型 = 默認(rèn)值 OUTPUT AS SQL語句 刪除：D

13、ROP PROCEDURE 存儲過程名,66,存儲過程,【例5-28】修改存儲過程,USE student GO --創(chuàng)建存儲過程p_1 CREATE proc p_1 As SELECT * FROM course GO --修改存儲過程p_1 ALTER PROC p_1 AS SELECT SName，SDept FROM Student GO,67,存儲過程,常用系統(tǒng)存儲過程,68,存儲過程,EXEC sp_databases EXEC sp_renamedb Northwind,Northwind1 USE stuDB GO EXEC sp_tables EXEC sp_column

14、s stuInfo EXEC sp_help stuInfo EXEC sp_helpconstraint stuInfo EXEC sp_helpindex stuMarks EXEC sp_helptext view_stuInfo_stuMarks EXEC sp_stored_procedures,修改數(shù)據(jù)庫的名稱(單用戶訪問),列出當(dāng)前系統(tǒng)中的數(shù)據(jù)庫,當(dāng)前數(shù)據(jù)庫中查詢的對象的列表,返回某個表列的信息,查看表stuInfo的信息,查看表stuInfo的約束,查看表stuMarks的索引,查看視圖的語句文本,查看當(dāng)前數(shù)據(jù)庫中的存儲過程,【例5-29】系統(tǒng)存儲過程,69,第五章 T-SQL

15、,本章內(nèi)容：,T-SQL基礎(chǔ),1,,,70,觸發(fā)器,為什么需要觸發(fā)器,帳戶信息表bank,交易信息表transInfo,張三取錢200 問題： 沒有自動修改張三的余額,,,,最優(yōu)的解決方案就是采用觸發(fā)器： 它是一種特殊的存儲過程 也具備事務(wù)的功能 它能在多表之間執(zhí)行特殊的業(yè)務(wù)規(guī)則,張三開戶1000元,李四開戶1元,71,觸發(fā)器,什么是觸發(fā)器,趙二,,插入,,刪除,觸發(fā)器觸發(fā),趙二退休,趙二,員工表,退休員工表,72,觸發(fā)器,什么是觸發(fā)器 觸發(fā)器是在對表進(jìn)行插入、更新或刪除操作時自動執(zhí)行的存儲過程 觸發(fā)器通常用于強(qiáng)制業(yè)務(wù)規(guī)則 觸發(fā)器是一種高級約束，可以定義比用CHECK 約束更為復(fù)雜的約束 可執(zhí)

16、行復(fù)雜的SQL語句（if/while/case） 可引用其它表中的列,73,觸發(fā)器,什么是觸發(fā)器 觸發(fā)器定義在特定的表上，與表相關(guān) 自動觸發(fā)執(zhí)行 不能直接調(diào)用 是一個事務(wù)（可回滾）,74,觸發(fā)器,觸發(fā)器的類型 AFTER觸發(fā)器 DELETE 觸發(fā)器 INSERT 觸發(fā)器 UPDATE 觸發(fā)器 INSTEAD OF 觸發(fā)器,75,觸發(fā)器,Inserted 和Deleted表 觸發(fā)器觸發(fā)時： 系統(tǒng)自動在內(nèi)存中創(chuàng)建deleted表或inserted表 只讀，不允許修改；觸發(fā)器執(zhí)行完成后自動刪除 Inserted 表 臨時保存插入或更新后的記錄行 可從Inserted表中檢查插入的數(shù)據(jù)是否滿足業(yè)務(wù)需求

17、。如不滿足，向用戶報告錯誤消息，并回滾插入操作 Deleted 表 臨時保存了刪除或更新前的記錄行 可從Deleted表中檢查被刪除的數(shù)據(jù)是否滿足業(yè)務(wù)需求。如不滿足，向用戶報告錯誤消息，并回滾插入操作,76,觸發(fā)器,Inserted 和Deleted表,77,觸發(fā)器,創(chuàng)建觸發(fā)器 語法： CREATE TRIGGER trigger_name ON table_name WITH ENCRYPTION FOR|AFTER|INSTEAD OF DELETE, INSERT, UPDATE AS T-SQL語句 GO WITH ENCRYPTION表示加密觸發(fā)器定義的SQL文本 DELETE, I

18、NSERT, UPDATE指定觸發(fā)器的類型 AFTER：表示只有執(zhí)行了指定的操作之后，觸發(fā)器才被激活，執(zhí)行觸發(fā)器中的SQL 語句。 FOR：表示為AFTER 觸發(fā)器 INSTEAD OF：指定觸發(fā)器為INSTEAD OF 觸發(fā)器,78,觸發(fā)器,INSERT觸發(fā)器,,,插入記錄行,觸發(fā)insert觸發(fā)器。向inserted表中插入新行的副本,觸發(fā)器檢查inserted表中插入的新行數(shù)據(jù)，確定是否需要回滾或執(zhí)行其他操作,,79,觸發(fā)器,【例5-30】解決上述的銀行取款問題：當(dāng)向交易信息表(transInfo)中插入一條交易信息時，我們應(yīng)自動更新對應(yīng)帳戶的余額,分析： 在交易信息表上創(chuàng)建INSERT

19、觸發(fā)器 從inserted臨時表中獲取插入的數(shù)據(jù)行 根據(jù)交易類型(transType)字段的值是存入/支取， 增加/減少對應(yīng)帳戶的余額。,80,觸發(fā)器,-------關(guān)鍵代碼------ CREATE TRIGGER trig_transInfo ON transInfo FOR INSERT AS DECLARE type char(4),outMoney MONEY DECLARE myCardID char(10),balance MONEY SELECT type=transType,outMoney=transMoney， myCardID=cardID FROM insert

20、ed IF (type=支取) UPDATE bank SET currentMoney=currentMoney-outMoney WHERE cardID=myCardID ELSE UPDATE bank SET currentMoney=currentMoney+outMoney WHERE cardID=myCardID .. GO,從inserted表中獲取交易類型、教員金額等,根據(jù)交易類型，減少或增加對應(yīng)卡號的余額,,81,觸發(fā)器,DELETE觸發(fā)器,,,刪除記錄行,觸發(fā)delete觸發(fā)器向deleted表中插入被刪除的副本,觸發(fā)器檢查deleted表中

21、被刪除的數(shù)據(jù)，決定是否需要回滾或執(zhí)行其他操作,,82,觸發(fā)器,【例5-31】當(dāng)刪除交易信息表時，要求自動備份被刪除的數(shù)據(jù)到表backupTable中,分析： 在交易信息表上創(chuàng)建DELETE觸發(fā)器 被刪除的數(shù)據(jù)可以從deleted表中獲取,83,觸發(fā)器,-------關(guān)鍵代碼------ CREATE TRIGGER trig_delete_transInfo ON transInfo FOR DELETE AS print 開始備份數(shù)據(jù)，請稍后...... IF NOT EXISTS(SELECT * FROM sysobjects WHERE name=backupTable)

22、 SELECT * INTO backupTable FROM deleted ELSE INSERT INTO backupTable SELECT * FROM deleted print 備份數(shù)據(jù)成功，備份表中的數(shù)據(jù)為: SELECT * FROM backupTable GO,從deleted表中獲取被刪除的交易記錄,84,觸發(fā)器,UPDATE觸發(fā)器,,,刪除記錄行,向deleted表中插入被刪除的副本,檢查deleted和inserted表中的數(shù)據(jù)，確定是否需要回滾或執(zhí)行其他操作,,向inserted表中插入被添加的副本,,,更新記錄行,85,觸發(fā)器,【例5-32】跟蹤用

23、戶的交易，交易金額超過20000元，則取消交易，并給出錯誤提示,分析： 在bank表上創(chuàng)建UPDATE觸發(fā)器 修改前的數(shù)據(jù)可以從deleted表中獲取 修改后的數(shù)據(jù)可以從inserted表中獲取,86,觸發(fā)器,-------關(guān)鍵代碼------ CREATE TRIGGER trig_update_bank ON bank FOR UPDATE AS DECLARE beforeMoney MONEY,afterMoney MONEY SELECT beforeMoney=currentMoney FROM deleted SELECT afterMoney=currentMoney

24、 FROM inserted IF ABS(afterMoney-beforeMoney)20000 BEGIN print 交易金額:+convert(varchar(8), ABS(afterMoney-beforeMoney)) RAISERROR (每筆交易不能超過2萬元，交易失敗,16,1) ROLLBACK TRANSACTION END GO,從deleted表中獲取交易前的余額，從inserted表中獲取交易后的余額,,交易金額是否2萬,回滾事務(wù)，撤銷交易,87,觸發(fā)器,列級UPDATE觸發(fā)器 UPDATE觸發(fā)器除了跟蹤數(shù)據(jù)的變化(修改)外

25、，還可以檢查是否修改了某列的數(shù)據(jù) 使用UPDATE(列)函數(shù)檢測是否修改了某列,88,觸發(fā)器,【例5-33】交易日期一般由系統(tǒng)自動產(chǎn)生，默認(rèn)為當(dāng)前日期。為了安全起見，一般禁止修改，以防舞弊,分析： UPDATE（列名）函數(shù)可以檢測是否修改了某列,89,觸發(fā)器,-------關(guān)鍵代碼------ CREATE TRIGGER trig_update_transInfo ON transInfo FOR UPDATE AS IF UPDATE(transDate) BEGIN print 交易失敗..... RAISERROR (安全警告：交易日期不能修改， 由系統(tǒng)自

26、動產(chǎn)生,16,1) ROLLBACK TRANSACTION END GO,檢查是否修改了交易日期列transDate,回滾事務(wù)，撤銷交易,90,觸發(fā)器,INSTEAD OF 觸發(fā)器 INSTEAD OF 觸發(fā)器表示并不執(zhí)行其所定義的操作（INSERT、UPDATE、DELETE），而僅是執(zhí)行觸發(fā)器本身。 既可在表上定義INSTEAD OF 觸發(fā)器，也可以在視圖上定INSTEADOF 觸發(fā)器，但對同一操作只能定義一個INSTEAD OF 觸發(fā)器 INSTEAD OF 觸發(fā)器的優(yōu)點是使不能被更新的視圖支持更新操作,91,觸發(fā)器,【例5-34】假設(shè)有3 個表（Stu97、Stu98、S

27、tu99），分別存儲97 級、98 級和99 級的學(xué)生信息，3個表具有相同的結(jié)構(gòu)。而視圖Stu_View 則包含了這3 個表的所有學(xué)生信息?，F(xiàn)為視圖Stu_View 創(chuàng)建INSTEAD OF 觸發(fā)器Stu_Instead，實現(xiàn)能夠直接向視圖Stu_View 中插入數(shù)據(jù)。,1創(chuàng)建表Stu97、Stu98 和Stu99 CREATE TABLE Stu97 ( Sno char(5), Sname CHAR(8), Age INT ),2創(chuàng)建視圖Stu_View CREATE VIEW Stu_View AS SELECT * FROM Stu97 UNION ALL SELECT * FROM

28、Stu98 UNION ALL SELECT * FROM Stu99,92,觸發(fā)器,CREATE TRIGGER Stu_Instead ON Stu_View INSTEAD OF INSERT AS BEGIN DECLARE S_NO CHAR(2)/*該變量用于存放插入數(shù)據(jù)的學(xué)號Sno 的 前兩位，以判斷插入記錄屬于哪張表*/ SELECT S_NO= SUBSTRING(Sno,1,2) FROM INSERTED IF S_NO = 97---由學(xué)號判斷該學(xué)生屬于97 級學(xué)生，插入Stu97 表 BEGIN INSERT INTO Stu9

29、7 SELECT Sno, Sname, Age FROM INSERTED RETURN END IF S_NO = 98 ---由學(xué)號判斷該學(xué)生屬于98 級學(xué)生，插入Stu98 表 BEGIN INSERT INTO Stu98,93,觸發(fā)器,SELECT Sno, Sname, Age FROM INSERTED RETURN END IF S_NO = 99 ---由學(xué)號判斷該學(xué)生屬于99級學(xué)生，插入Stu99 表 BEGIN INSERT INTO Stu99 SELECT Sno, Sname, Age FROM INSERTED

30、END ELSE BEGIN ROLLBACK TRANSACTION RAISERROR(插入記錄的學(xué)號信息不正確,16,1) END END,94,觸發(fā)器,小結(jié) 觸發(fā)器是在對表進(jìn)行插入、更新或刪除操作時自動執(zhí)行的存儲過程，通常用于強(qiáng)制業(yè)務(wù)規(guī)則 觸發(fā)器是一個特殊的事務(wù)單元，當(dāng)出現(xiàn)錯誤時，可以執(zhí)行ROLLBACK TRANSACTION回滾操作 觸發(fā)器一般需要使用臨時表：deleted和inserted，它們存放了被刪除或插入的記錄行副本 觸發(fā)器類型： INSERT觸發(fā)器 UPDATE觸發(fā)器 DELETE觸發(fā)器 INSTEAD OF 觸發(fā)器,95,第五章 T-SQL,本章內(nèi)容：,

31、T-SQL基礎(chǔ),1,,,96,游標(biāo),什么是游標(biāo) SELECT語句一次返回很多元組，但有時需要對每個元組進(jìn)行不同的處理。游標(biāo)是為了處理SELECT語句的結(jié)果集。 游標(biāo)（Cursor）是系統(tǒng)為用戶開設(shè)的一個數(shù)據(jù)緩沖區(qū)，存放SQL語句的執(zhí)行結(jié)果。 游標(biāo)有一個名字，用戶可以通過游標(biāo)逐一獲取記錄，并交付給主變量，交由主語言進(jìn)一步處理。,97,游標(biāo),什么是游標(biāo) 可以把游標(biāo)看成一種指針，它既可指向當(dāng)前位置，也可指向結(jié)果集中的任意位置 它允許用戶對指定位置的數(shù)據(jù)進(jìn)行處理，可以把結(jié)果集中的數(shù)據(jù)放在數(shù)組、應(yīng)用程序或其它地方 游標(biāo)的組成 游標(biāo)結(jié)果集（Cursor Result Set） ：定義游標(biāo)的SELECT語句

32、返回的行的集合。 游標(biāo)的位置（Cursor Position） 指向這個集合中某一行的指針。,98,游標(biāo),游標(biāo)的優(yōu)點 允許定位在結(jié)果集的特定行。 從結(jié)果集的當(dāng)前位置檢索一行或多行。 支持對結(jié)果集中當(dāng)前位置的行進(jìn)行數(shù)據(jù)修改 可在腳本、存儲過程和觸發(fā)器中使用游標(biāo)訪問結(jié)果集中的數(shù)據(jù)。,99,游標(biāo),游標(biāo)的使用,100,游標(biāo),游標(biāo)的創(chuàng)建 DECLARE cursor_name INSENSITIVE SCROLL CURSOR FOR select_statement FOR READ ONLY | UPDATE OF column_name_list INSENSITIVE 取出來的數(shù)據(jù)放到TE

33、MPDB中，對基本表的改動不會反映到游標(biāo)中 SCROLL 可以做更多的操作：FIRST、LAST、NEXT、RELATIVE和ABSOLUTE READ ONLY 不修改游標(biāo)指向的數(shù)據(jù) UPDATE OF column_name_list 修改游標(biāo)指向的數(shù)據(jù)，可以只修改某些列，也可以修改所有的列。,101,游標(biāo),【例5-35】創(chuàng)建一個游標(biāo),DECLARE student_cursor CURSOR FOR SELECT SNO,SName FROM Student WHERE SCore =500 ORDER BY SNO FOR READ ONLY,102,游標(biāo),游標(biāo)的管理 打開游標(biāo)。

34、 OPEN 關(guān)閉游標(biāo)。 CLOSE ； CLOSE語句關(guān)閉已打開的游標(biāo)，之后不能對游標(biāo)進(jìn)行讀取等操作，但可以使用OPEN語句再次打開該游標(biāo)。 釋放游標(biāo)。 DEALLOCATE 刪除定義游標(biāo)的數(shù)據(jù)結(jié)構(gòu)，刪除后不可再用,103,游標(biāo),利用游標(biāo)取數(shù) 游標(biāo)打開后，游標(biāo)位置位于結(jié)果集的第一行前，此時可從結(jié)果集中提取(FETCH)行。SQL Server將沿著結(jié)果集一行或多行向下移動游標(biāo)位置，不斷提取結(jié)果集中的數(shù)據(jù)，并修改和保存游標(biāo)當(dāng)前的位置，直到結(jié)果集中的行全部被提取 語法 FETCH NEXT | PRIOR|FIRST|LAST|ABSOLUTE N|V|RELATIVE N|VFROM Curs

35、or_nameINTO variale,,,,,104,游標(biāo),利用游標(biāo)取數(shù) 游標(biāo)的移動方向 NEXT：取下一行數(shù)據(jù) PRIOR：取前一行數(shù)據(jù) FIRST：取第一行數(shù)據(jù) LAST：取最后一行數(shù)據(jù) ABSOLUTE：按絕對位置取數(shù)據(jù) RELATIVE：按相對位置取數(shù)據(jù) 存放被提取的列數(shù)據(jù)的變量清單的個數(shù)、數(shù)據(jù)類型、順序必須與定義該游標(biāo)的select_statement中列出的列清單相匹配,105,游標(biāo),利用游標(biāo)取數(shù) 與游標(biāo)相關(guān)的兩個全局變量 FETCH_STATUS 保存著最后FETCH語句執(zhí)行后的狀態(tài)信息，其值和含義如下： 0 ：表示成功完成FETCH 語句。 -1：表示FETCH語句執(zhí)行有錯誤

36、，或者當(dāng)前游標(biāo)位置已在結(jié)果集中的最后一行，結(jié)果集中不再有數(shù)據(jù)。 -2：提取的行不存在。 rowcount保存著自游標(biāo)打開后的第一個FETCH語句，直到最近一次的FETCH語句為止，已從游標(biāo)結(jié)果集中提取的行數(shù)。一旦結(jié)果集中所有行都被提取，那么rowcount的值就是該結(jié)果集的總行數(shù)。關(guān)閉游標(biāo)時，該rowcount變量也被刪除。,106,游標(biāo),【例5-36】使用student_cursor游標(biāo)，將學(xué)生的學(xué)號和姓名打印出來,DECLARE vsno char(9) ,vsname varchar(20) OPEN student_cursor FETCH NEXT FROM student_curs

37、or INTO vsno, vsname WHILE FETCH_STATUS=0BEGIN PRINT 學(xué)號：+vsno + 姓名:+vsname FETCH NEXT FROM student_cursor INTO vsno, vsname ENDCLOSE student_cursor DEALLOCATE student_cursor,107,游標(biāo),使用游標(biāo)修改數(shù)據(jù) 更新數(shù)據(jù) 通過在UPDATE語句中使用游標(biāo)可以更新表或視圖中的行。被更新的行依賴于游標(biāo)位置的當(dāng)前值 語法 UPDATE table_name|view_name SET column_name = new

38、_value ...n WHERE CURRENT OF cursor_name table_name| view_name：要更新的表名或視圖名。必須是聲明該游標(biāo)的SELECT語句中的表名或視圖名 column_name：要更新的列名。必須是聲明游標(biāo)的SELECT語句中UPDATE OF column_name_list的子集 WHERE CURRENT OF：使SQL Server只更新由指定游標(biāo)的當(dāng)前位置確定的行,108,游標(biāo),使用游標(biāo)修改數(shù)據(jù) 更新數(shù)據(jù) 注意 使用UPDATE...CURRENT OF語句一次只能更新當(dāng)前游標(biāo)位置確定的那一行，OPEN語句將游標(biāo)位置定位在結(jié)果

39、集第一行前，可以使用FETCH語句把游標(biāo)位置定位在要被更新的數(shù)據(jù)行處 用UPDATE...WHERE CURRENT OF語句更新表中的行時，不會移動游標(biāo)位置，被更新的行可以再次被修改，直到下一個FETCH語句的執(zhí)行。 UPDATE...WHERE CURRENT OF語句可以更新多表視圖或被連接的多表，但只能更新其中一個表的行，即所有被更新的列都來自同一個表。,109,游標(biāo),【例5-37】使用游標(biāo)，將第三個學(xué)生轉(zhuǎn)到軟件學(xué)院,USE School GO DECLARE student_update SCROLL CURSOR FOR SELECT SNO,SDept FROM STUDENT

40、ORDER BY SNO FOR UPDATE OF SDept OPEN student_update FETCH ABSOLUTE 3 FROM student_update UPDATE Student SET SDept=軟件學(xué)院 WHERE CURRENT OF student_update CLOSE student_update DEALLOCATE student_update,110,游標(biāo),使用游標(biāo)修改數(shù)據(jù) 刪除數(shù)據(jù) 通過在DELETE語句中使用游標(biāo)來刪除表或視圖中的行。被刪除的行依賴于游標(biāo)的當(dāng)前位置 語法 DELETE FROM table_name|view_name

41、 WHERE CURRENT OF cursor_name table_name|view_name：為要從其中刪除行的表名或視圖名。它必須是定義該游標(biāo)的SELECT語句中的表名或視圖名。 WHERE CURRENT OF：它使SQL Server只刪除由指定游標(biāo)的當(dāng)前位置確定的行。,111,游標(biāo),使用游標(biāo)修改數(shù)據(jù) 刪除數(shù)據(jù) 注意 使用DELETE語句，一次只能刪除當(dāng)前游標(biāo)位置確定的那一行。OPEN語句將游標(biāo)位置定位在結(jié)果集第一行之前，可以用FETCH語句把游標(biāo)位置定位在要被刪除的行處 在DELETE語句中使用的游標(biāo)必須聲明為FOR UPDATE方式而且聲明游標(biāo)的SELECT語句中不能

42、含有連接操作或涉及多表視圖，否則即使聲明中指明了FOR UPDATE方式，也不能刪除其中的行 對使用游標(biāo)刪除行的表，要求有一個唯一索引 使用DELETE語句，刪除一行后將游標(biāo)位置向前移動一行,112,第五章 T-SQL,本章內(nèi)容：,T-SQL基礎(chǔ),1,,,113,事務(wù),什么是事務(wù) 所謂事務(wù)(Transaction ),是指一個操作序列，這些操作序列要么都被執(zhí)行，要么都不被執(zhí)行，它是一個不可分割的工作單元 事務(wù)中任何一個語句執(zhí)行時出錯，系統(tǒng)都會返回到事務(wù)開始前的狀態(tài) 事務(wù)是并發(fā)控制的基本單元，是數(shù)據(jù)庫維護(hù)數(shù)據(jù)一致性的單位。在每個事務(wù)結(jié)束時，都能保持?jǐn)?shù)據(jù)一致性。,114,事務(wù),事務(wù)的特點 一致性：

43、它保證并發(fā)用戶讀取數(shù)據(jù)的一致性。 隔離性：事務(wù)內(nèi)部的操作及使用的數(shù)據(jù)對并發(fā)的其他事務(wù)是隔離的。 可恢復(fù)性：DBMS利用事務(wù)日志能對事務(wù)進(jìn)行自動恢復(fù)。 SQL Server對事務(wù)的管理包含三個方面： 事務(wù)控制語句：控制事務(wù)執(zhí)行的語句。將一系列操作定義為一個工作單元來處理 鎖機(jī)制：封鎖正被一個事務(wù)修改的數(shù)據(jù)，防止其他用戶訪問到“不一致”的數(shù)據(jù) 事務(wù)日志：使事務(wù)具有可恢復(fù)性,115,事務(wù),為了盡可能避免死鎖的出現(xiàn)，應(yīng)注意： 在所有的事務(wù)中都按同一順序來訪問各個表。盡可能利用存儲過程來完成一個事務(wù)，以保證對各表的訪問次序都是一致的。 事務(wù)應(yīng)該盡量小且應(yīng)盡快提交。 避免人工輸入操作出現(xiàn)在事務(wù)中。 避免并

44、發(fā)地執(zhí)行像INSERT、UPDATE、DELETE這類數(shù)據(jù)修改語句。,116,事務(wù),事務(wù)控制語句 在SQL Server中，對事務(wù)的管理是通過事務(wù)控制語句和幾個全局變量結(jié)合起來實現(xiàn)的,117,事務(wù),事務(wù)控制語句 控制語句 BEGIN TRAN tran_name：標(biāo)識一個用戶定義的事務(wù)的開始。tran_name為事務(wù)的名字，標(biāo)識一個事務(wù)開始。 COMMIT TRAN tran_name：表示提交事務(wù)中的一切操作，結(jié)束一個用戶定義的事務(wù)。使得對數(shù)據(jù)庫的改變生效。 ROLLBACK TRAN tran_name|save_name：回退一個事務(wù)到事務(wù)的開頭或一個保存點。表示要撤消該事務(wù)已做的操作，

45、回滾到事務(wù)開始前或保存點前的狀態(tài)。 SAVE TRAN save_name：在事務(wù)中設(shè)置一個保存點。它可以使一個事務(wù)內(nèi)的部分操作回退。,118,事務(wù),事務(wù)控制語句 兩個可用于事務(wù)管理的全局變量 error：給出最近一次執(zhí)行的出錯語句引發(fā)的錯誤號，error為0表示未出錯。 rowcount：給出受事務(wù)中已執(zhí)行語句所影響的數(shù)據(jù)行數(shù)。,119,事務(wù),事務(wù)控制語句 事務(wù)中不能包含的語句 CREATE DATABASE ALTER DATABASE BACKUP LOG DROP DATABASE RECONFIGURE RESTORE DATABASE RESTORE LOG UPDATE STAT

46、ISTICS,120,游標(biāo),【例5-38】使用事務(wù)向Course表中插入數(shù)據(jù),USE School GO BEGIN TRAN tran_course INSERT INTO Course VALUES(C0009,微機(jī)原理,50,3) SAVE TRAN right_point INSERT INTO Course VALUES(C0010,云計算,30,2) GO INSERT INTO Course(CNO,Period,Credit) VALUES(C0011, 40,4) GO IF error0 ROLLBACK TRAN right_point GO COMMIT TRAN tran_course GO,