《SQL數(shù)據(jù)庫》PPT課件.ppt

《《SQL數(shù)據(jù)庫》PPT課件.ppt》由會(huì)員分享，可在線閱讀，更多相關(guān)《《SQL數(shù)據(jù)庫》PPT課件.ppt（26頁珍藏版）》請(qǐng)?jiān)谘b配圖網(wǎng)上搜索。

1、在大型數(shù)據(jù)庫系統(tǒng)中，存儲(chǔ)過程和觸發(fā)器具有很重要的作用。無論是存儲(chǔ)過程還是觸發(fā)器，都是SQL語句和流程控制語句的集合。就本質(zhì)而言，觸發(fā)器也是一種存儲(chǔ)過程，它在特定語言事件發(fā)生時(shí)自動(dòng)執(zhí)行。存儲(chǔ)過程在運(yùn)算時(shí)生成執(zhí)行代碼，所以，以后對(duì)其再運(yùn)行時(shí)其執(zhí)行效率很高。SQL Server 2005不僅提供了用戶自定義存儲(chǔ)過程的功能，而且也提供了許多可作為工具使用的系統(tǒng)存儲(chǔ)過程。 本章主要介紹存儲(chǔ)過程和觸發(fā)器的概念和分類以及它們的使用方法。,,第10章 存儲(chǔ)過程和觸發(fā)器,10.1 存儲(chǔ)過程 10.2 觸發(fā)器,,第10章 存儲(chǔ)過程和觸發(fā)器,10.1.1 存儲(chǔ)過程概述 存儲(chǔ)過程（Stored Procedure）是

2、一組完成特定功能的SQL語句集，經(jīng)編譯后存儲(chǔ)在數(shù)據(jù)庫中。用戶通過指定存儲(chǔ)過程的名字并給出參數(shù)（如果該存儲(chǔ)過程帶有參數(shù)）來執(zhí)行存儲(chǔ)過程。 利用SQL Server創(chuàng)建一個(gè)應(yīng)用程序時(shí)，SQL是主要的編程語言。使用SQL進(jìn)行編程，有兩種方法。其一是，在本地存儲(chǔ)SQL程序，并創(chuàng)建應(yīng)用程序向SQL Server發(fā)送命令來對(duì)結(jié)果進(jìn)行處理。其二是，可以把部分用SQL編寫的程序作為存儲(chǔ)過程存儲(chǔ)在SQL Server中，然后創(chuàng)建應(yīng)用程序來調(diào)用存儲(chǔ)過程，對(duì)數(shù)據(jù)結(jié)果進(jìn)行處理。,,10.1 存儲(chǔ)過程,存儲(chǔ)過程具有以下優(yōu)點(diǎn)： 1. 存儲(chǔ)過程允許標(biāo)準(zhǔn)組件式編程 2. 存儲(chǔ)過程能夠?qū)崿F(xiàn)較快的執(zhí)行速度 3. 存儲(chǔ)過程能夠減少

3、網(wǎng)絡(luò)流量 4. 存儲(chǔ)過程可被作為一種安全機(jī)制來充分利用,,10.1 存儲(chǔ)過程,10.1.2 存儲(chǔ)過程的類型 在SQL Server 2005中有多種可用的存儲(chǔ)過程。在SQL Server 2005中存儲(chǔ)過程分為三類：系統(tǒng)提供的存儲(chǔ)過程、用戶自定義存儲(chǔ)過程和擴(kuò)展存儲(chǔ)過程。 1. 系統(tǒng)存儲(chǔ)過程 SQL Server 2005中的許多管理活動(dòng)都是通過一種特殊的存儲(chǔ)過程執(zhí)行的，這種存儲(chǔ)過程被稱為系統(tǒng)存儲(chǔ)過程。系統(tǒng)過程主要存儲(chǔ)在master數(shù)據(jù)庫中并以sp_為前綴，并且系統(tǒng)存儲(chǔ)過程主要是從系統(tǒng)表中獲取信息，從而為數(shù)據(jù)庫系統(tǒng)管理員管理SQL Server提供支持。通過系統(tǒng)存儲(chǔ)過程，SQL Server中的

4、許多管理性或信息性的活動(dòng)（如獲取數(shù)據(jù)庫和數(shù)據(jù)庫對(duì)象的信息）都可以被順利有效地完成。,,10.1 存儲(chǔ)過程,2. 用戶自定義的存儲(chǔ)過程 用戶自定義存儲(chǔ)過程是由用戶創(chuàng)建并能完成某一特定功能（如查詢用戶所需數(shù)據(jù)信息）的存儲(chǔ)過程，是封裝了可重用代碼的SQL語句模塊。存儲(chǔ)過程可以接受輸入?yún)?shù)、向客戶端返回表格或標(biāo)量結(jié)果和消息、調(diào)用數(shù)據(jù)定義語言（DDL）和數(shù)據(jù)操作語言（DML）語句，以及返回輸出參數(shù)。在SQL Server中，用戶自定義的存儲(chǔ)過程有兩種類型：T-SQL存儲(chǔ)過程或CLR存儲(chǔ)過程。本書所提到的用戶定義的存儲(chǔ)過程主要指T-SQL存儲(chǔ)過程。 3. 擴(kuò)展存儲(chǔ)過程 擴(kuò)展存儲(chǔ)過程允許使用高級(jí)編程語言（例

5、如C）創(chuàng)建應(yīng)用程序的外部例程，從而使得SQL Server的實(shí)例可以動(dòng)態(tài)加載和運(yùn)行DLL。擴(kuò)展存儲(chǔ)過程直接在SQL Server實(shí)例的地址空間中運(yùn)行。,,10.1 存儲(chǔ)過程,10.1.3 存儲(chǔ)過程的設(shè)計(jì)規(guī)則 使用SQL語句CREATE PROCEDURE可以創(chuàng)建存儲(chǔ)過程。其語法格式如下： CREATE PROC | PROCEDURE schema_name. procedure_name ; number parameter type_schema_name. data_type VARYING = default OUT PUT ,...n WITH ,...n FO

6、R REPLICATION AS ; ...n | ;,,10.1 存儲(chǔ)過程,10.1.4 使用存儲(chǔ)過程 當(dāng)創(chuàng)建存儲(chǔ)過程時(shí)，需要確定存儲(chǔ)過程的三個(gè)組成部分： 1) 所有的輸入?yún)?shù)以及傳給調(diào)用者的輸出參數(shù)。 2) 被執(zhí)行的針對(duì)數(shù)據(jù)庫的操作語句，包括調(diào)用其它存儲(chǔ)過程的語句。 3) 返回給調(diào)用者的狀態(tài)值，以指明調(diào)用是成功還是失敗。 在SQL Server 2005中，創(chuàng)建存儲(chǔ)過程有兩種方法：一種是使用SQL Server Management Studio，另一種是使用T-SQL命令CREATE PROCEDURE。,,10.1 存儲(chǔ)過程,【例1】用SQL命令窗口創(chuàng)建存儲(chǔ)過程 CREATE PROC

7、EDURE GetStudent number varchar(40) AS SELECT * FROM student WHERE sno= number go EXECUTE GetStudent 95001,,10.1 存儲(chǔ)過程,【例2】創(chuàng)建帶有通配符參數(shù)的存儲(chǔ)過程 下面的存儲(chǔ)過程只從學(xué)生表中返回指定的一些學(xué)生（提供名字和姓氏）的信息。該存儲(chǔ)過程對(duì)傳遞的參數(shù)進(jìn)行模式匹配，如果沒有提供參數(shù)，則返回所有學(xué)生的信息。 CREATE PROCEDURE GetStudentByName name varchar(40) = % AS SELECT * FROM student W

8、HERE Sname LIKE name; EXECUTE GetStudentByName; 或者 EXECUTE GetStudentByName 劉%;,,10.1 存儲(chǔ)過程,【例3】創(chuàng)建使用output參數(shù)的存儲(chǔ)過程 下面的存儲(chǔ)過程從SC表中返回指定系的學(xué)生的平均成績。 CREATE PROCEDURE GetStudentAvgGradeByDept dept varchar(40),avggrade int out AS SET avggrade= (SELECT AVG(Grade) FROM SC WHERE sno in (SELECT SNO FROM stude

9、nt WHERE Sdept=dept)); GO DECLARE avggrade int EXEC GetStudentAvgGradeByDept cs,avggrade OUT PRINT avggrade,,10.1 存儲(chǔ)過程,【例4】使用sp_helptext查看存儲(chǔ)過程的源代碼 sp_helptext GetStudentAvgGradeByDept 說明：如果存儲(chǔ)過程使用WITH ENCRYPTION，其源代碼無法查看。 【例5】使用WITH ENCRYPTION創(chuàng)建存儲(chǔ)過程 CREATE PROCEDURE GetStudent1 number varchar(40) WI

10、TH ENCRYPTION AS SELECT * FROM student WHERE sno= number go,,10.1 存儲(chǔ)過程,10.1.5 重新編譯存儲(chǔ)過程 在執(zhí)行諸如添加索引或更改索引列中的數(shù)據(jù)等操作更改了數(shù)據(jù)庫時(shí)，應(yīng)重新編譯訪問數(shù)據(jù)庫表的原始查詢計(jì)劃以對(duì)其重新優(yōu)化。在SQL Server 2005重新啟動(dòng)后，第一次運(yùn)行存儲(chǔ)過程時(shí)自動(dòng)執(zhí)行此優(yōu)化。當(dāng)存儲(chǔ)過程使用的基礎(chǔ)表發(fā)生變化時(shí)，也會(huì)自動(dòng)執(zhí)行此優(yōu)化。但如果添加了存儲(chǔ)過程可能從中受益的新索引，將不會(huì)自動(dòng)執(zhí)行優(yōu)化，直到下一次SQL Server重新啟動(dòng)并再運(yùn)行該存儲(chǔ)過程時(shí)為止。在這種情況下，強(qiáng)制在下次執(zhí)行存儲(chǔ)過程時(shí)對(duì)其重新編

11、譯會(huì)很有用。,,10.1 存儲(chǔ)過程,SQL Server中，強(qiáng)制重新編譯存儲(chǔ)過程的方式有三種： 1) sp_recompile系統(tǒng)存儲(chǔ)過程強(qiáng)制在下次執(zhí)行存儲(chǔ)過程時(shí)對(duì)其重新編譯。 2) 創(chuàng)建存儲(chǔ)過程時(shí)在其定義中指定WITH RECOMPILE選項(xiàng)，可以指明SQL Server將不為該存儲(chǔ)過程緩存計(jì)劃，在每次執(zhí)行該存儲(chǔ)過程時(shí)對(duì)其重新編譯。當(dāng)存儲(chǔ)過程的參數(shù)值在各次執(zhí)行間都有較大差異，導(dǎo)致每次均需創(chuàng)建不同的執(zhí)行計(jì)劃時(shí)，可使用WITH RECOMPILE選項(xiàng)。 3) 可以在執(zhí)行存儲(chǔ)過程時(shí)指定WITH RECOMPILE選項(xiàng)，強(qiáng)制對(duì)其重新編譯。,,10.1 存儲(chǔ)過程,觸發(fā)器是一種特殊的存儲(chǔ)過程，它在特定語

12、言事件發(fā)生時(shí)自動(dòng)執(zhí)行。 這一節(jié)主要介紹觸發(fā)器的概念、作用以及其使用方法。,,10.2 觸發(fā)器,10.2.1 觸發(fā)器概述 觸發(fā)器是一種特殊類型的存儲(chǔ)過程，它不同于前面介紹過的存儲(chǔ)過程，主要是通過事件進(jìn)行觸發(fā)而被執(zhí)行的，而存儲(chǔ)過程可以通過存儲(chǔ)過程名字而被直接調(diào)用。當(dāng)對(duì)某一表進(jìn)行諸如UPDATE、INSERT、DELETE這些操作時(shí)，SQL Server就會(huì)自動(dòng)執(zhí)行觸發(fā)器所定義的SQL語句，從而確保對(duì)數(shù)據(jù)的處理必須符合由這些SQL語句所定義的規(guī)則。,,10.2 觸發(fā)器,觸發(fā)器的主要作用就是實(shí)現(xiàn)由主鍵和外鍵所不能保證的復(fù)雜的參照完整性和數(shù)據(jù)一致性。除此之外，觸發(fā)器還有其它許多不同的功能： 1. 強(qiáng)化約

13、束（Enforce restriction） 2. 跟蹤變化（Auditing changes） 3. 級(jí)聯(lián)運(yùn)行（Cascaded operation） 4. 存儲(chǔ)過程的調(diào)用（Stored procedure invocation）,,10.2 觸發(fā)器,10.2.2 觸發(fā)器的類型 SQL Server 包括兩大類觸發(fā)器：DML觸發(fā)器和DDL觸發(fā)器。 1. DML觸發(fā)器 當(dāng)數(shù)據(jù)庫中發(fā)生數(shù)據(jù)操作語言（DML）事件時(shí)將調(diào)用DML觸發(fā)器。DML事件包括在指定表或視圖中修改數(shù)據(jù)的INSERT語句、UPDATE語句或DELETE語句。DML觸發(fā)器可以查詢其他表，還可以包含復(fù)雜的T-SQL語句。系統(tǒng)將觸發(fā)器

14、和觸發(fā)它的語句作為可在觸發(fā)器內(nèi)回滾的單個(gè)事務(wù)對(duì)待，如果檢測到錯(cuò)誤（例如，磁盤空間不足），則整個(gè)事務(wù)即自動(dòng)回滾。,,10.2 觸發(fā)器,SQL Server 2005的DML觸發(fā)器分為兩類:,After觸發(fā)器 這類觸發(fā)器是在記錄已經(jīng)改變完之后（after），才會(huì)被激活執(zhí)行，它主要是用于記錄變更后的處理或檢查，一旦發(fā)現(xiàn)錯(cuò)誤，可以用Rollback Transaction語句來回滾本次的操作。 只能在表上定義。 Instead Of觸發(fā)器 INSTEAD OF 觸發(fā)器用來代替通常的觸發(fā)動(dòng)作，即當(dāng)對(duì)表進(jìn)行INSERT、UPDATE 或 DELETE 操作時(shí)，系統(tǒng)不是直接對(duì)表執(zhí)行這些操作，而是把操作內(nèi)容交

15、給觸發(fā)器，讓觸發(fā)器檢查所進(jìn)行的操作是否正確。如正確才進(jìn)行相應(yīng)的操作。因此，INSTEAD OF 觸發(fā)器的動(dòng)作要早于表的約束處理。 可以在視圖上定義。,2. DDL觸發(fā)器 DDL觸發(fā)器是SQL Server 2005的新增功能。像常規(guī)觸發(fā)器一樣，DDL觸發(fā)器將激發(fā)存儲(chǔ)過程以響應(yīng)事件。但與DML觸發(fā)器不同的是，它們不會(huì)為響應(yīng)針對(duì)表或視圖的UPDATE、INSERT或DELETE語句而激發(fā)，相反，它們會(huì)為響應(yīng)多種數(shù)據(jù)定義語言 (DDL)語句而激發(fā)。這些語句主要是以CREATE、ALTER和DROP開頭的語句。DDL觸發(fā)器可用于管理任務(wù)，例如審核和控制數(shù)據(jù)庫操作。,,10.2 觸發(fā)器,10.2.3 觸

16、發(fā)器的設(shè)計(jì)規(guī)則 使用T-SQL語句CREATE TRIGGER可以創(chuàng)建觸發(fā)器。其語法格式如下： CREATE TRIGGER schema_name . trigger_name ON table | view WITH ,...n FOR | AFTER | INSTEAD OF INSERT , UPDATE , DELETE WITH APPEND NOT FOR REPLICATION AS sql_statement ; ...n | EXTERNAL NAME ,,10.2 觸發(fā)器,在SQL Server 2005里，為每個(gè)DML觸發(fā)器都定義了兩個(gè)特殊的表，一個(gè)

17、是插入表(inserted)，一個(gè)是刪除表(deleted)。這兩個(gè)表是建在數(shù)據(jù)庫服務(wù)器的內(nèi)存中的，是由系統(tǒng)管理的邏輯表，而不是真正存儲(chǔ)在數(shù)據(jù)庫中的物理表。對(duì)于這兩個(gè)表，用戶只有讀取的權(quán)限，沒有修改的權(quán)限。這兩個(gè)表的結(jié)構(gòu)與觸發(fā)器所在數(shù)據(jù)表的結(jié)構(gòu)是完全一致的，當(dāng)觸發(fā)器的工作完成之后，這兩個(gè)表也將會(huì)從內(nèi)存中刪除。 插入表(inserted)里存放的是更新后的記錄：對(duì)于插入記錄操作來說，插入表里存放的是要插入的數(shù)據(jù)；對(duì)于更新記錄操作來說，插入表里存放的是要更新的記錄。 刪除表(deleted)里存放的是更新前的記錄：對(duì)于更新記錄操作來說，刪除表里存放的是更新前的記錄（更新完后即被刪除）；對(duì)于刪除記錄

18、操作來說，刪除表里存入的是被刪除的舊記錄。,,10.2 觸發(fā)器,DELETE觸發(fā)器是當(dāng)對(duì)表執(zhí)行DELETE操作時(shí)刪除元組，將刪除的元組放入deleted表中。檢查deleted表中的數(shù)據(jù)，確定該如何處理。 INSERT觸發(fā)器在對(duì)執(zhí)行插入數(shù)據(jù)操作時(shí),將插入表中的數(shù)據(jù)拷貝并送入inserted表中，根據(jù)inserted表中的值決定如何處理。 UPDATE觸發(fā)器僅在更新數(shù)據(jù)操作時(shí)將要被更新的原數(shù)據(jù)移入deleted表中，將更新后的數(shù)據(jù)備份送入inserted表中，對(duì)deleted和inserted表進(jìn)行檢查，并決定如何處理。,,10.2 觸發(fā)器,10.2.4 使用觸發(fā)器 上面介紹了觸發(fā)器的概念、作用

19、和一些基本問題，下面將通過實(shí)例介紹在SQL Server 2005中如何使用觸發(fā)器。 【例6】創(chuàng)建DDL觸發(fā)器。 CREATE TRIGGER safety ON DATABASE FOR DROP_TABLE AS PRINT 不能修改表結(jié)構(gòu) ROLLBACK,,10.2 觸發(fā)器,【例7】創(chuàng)建DML觸發(fā)器。定義觸發(fā)器，要求學(xué)生已經(jīng)選修3門后，不能再選修課程 create trigger 選修限制 on SC after insert,update as if (select count(*) from SC,inserted where inserted.sno=SC.sno)=4 begi

20、n rollback transaction print 不能再選修課程 end go,,10.2 觸發(fā)器,【例8】創(chuàng)建DML觸發(fā)器，實(shí)現(xiàn)對(duì)學(xué)號(hào)的修改，注意表間無外鍵約束。 create trigger updateSno on student after update as declare oldSno char(5),newSno char(5) set oldSno=(select sno from deleted); set newSno=(select sno from inserted); Update SC set sno=newSno where sno=oldSno; gogo,,10.2 觸發(fā)器,