中職 數(shù)據(jù)庫應(yīng)用基礎(chǔ)（SQL Server 2016）項(xiàng)目8教學(xué)課件

,單擊此處編輯母版標(biāo)題樣式,單擊此處編輯母版文本樣式,第二級(jí),第三級(jí),第四級(jí),第五級(jí),21/8/22 Sun,#,單擊此處編輯母版標(biāo)題樣式,單擊此處編輯母版文本樣式,第二級(jí),第三級(jí),第四級(jí),第五級(jí),中職 數(shù)據(jù)庫應(yīng)用基礎(chǔ)（SQL Server 2016）項(xiàng)目8教學(xué)課件工信版,項(xiàng)目,8,創(chuàng)建存儲(chǔ)過程和觸發(fā)器,理解存儲(chǔ)過程的概念和類型,掌握創(chuàng)建、調(diào)用和管理存儲(chǔ)過程的方法,理解觸發(fā)器的概念和類型,掌握設(shè)計(jì)、實(shí)現(xiàn)和管理觸發(fā)器的方法,項(xiàng)目目標(biāo),8.1.1,存儲(chǔ)過程的類型,1.,用戶定義存儲(chǔ)過程。,用戶定義存儲(chǔ)過程是指封裝了可重用代碼的模塊或例程，可以接受輸入?yún)?shù)、向客戶端返回表或標(biāo)量結(jié)果和消息、調(diào)用數(shù)據(jù)定義語言和數(shù)據(jù)操作語言語句，然后返回輸出參數(shù)。,2.,臨時(shí)存儲(chǔ)過程。,臨時(shí)存儲(chǔ)過程是用戶定義存儲(chǔ)過程的一種形式。臨時(shí)存儲(chǔ)過程與永久過程相似，只是臨時(shí)存儲(chǔ)過程存儲(chǔ)于,tempdb,中。,3.,系統(tǒng)存儲(chǔ)過程。,系統(tǒng)存儲(chǔ)過程是指由,SQL Server,提供的存儲(chǔ)過程，可以用來管理,SQL Server,和顯示有關(guān)數(shù)據(jù)庫和用戶的信息。,4.,擴(kuò)展的用戶定義存儲(chǔ)過程,擴(kuò)展過程允許你使用編程語言（例如,C,）創(chuàng)建外部例程。這些過程是指,SQL Server,的實(shí)例可以動(dòng)態(tài)加載和運(yùn)行的,DLL,。,任務(wù),8.1,創(chuàng)建存儲(chǔ)過程,8.1.2,使用存儲(chǔ)過程的好處,1.,減少了服務(wù)器,/,客戶端網(wǎng)絡(luò)流量,存儲(chǔ)過程中的命令作為代碼的單個(gè)批處理執(zhí)行。這可以顯著減少服務(wù)器與客戶端之間的網(wǎng)絡(luò)流量，因?yàn)橹挥袑?duì)執(zhí)行過程的調(diào)用才會(huì)跨網(wǎng)絡(luò)發(fā)送。如果沒有過程提供的代碼封裝，每個(gè)單獨(dú)的代碼行都不得不通過網(wǎng)絡(luò)發(fā)送。,2.,更強(qiáng)的安全性,多個(gè)用戶和客戶端程序可以通過過程對(duì)基礎(chǔ)數(shù)據(jù)庫對(duì)象執(zhí)行操作，即使用戶和程序?qū)@些基礎(chǔ)對(duì)象沒有直接權(quán)限。存儲(chǔ)過程控制執(zhí)行哪些進(jìn)程和活動(dòng)，并且保護(hù)基礎(chǔ)數(shù)據(jù)庫對(duì)象。這消除在了單獨(dú)的對(duì)象級(jí)別授予權(quán)限的要求，并且簡(jiǎn)化了安全層。,3.,代碼的重復(fù)使用,任何重復(fù)的數(shù)據(jù)庫操作的代碼都非常適合于在過程中進(jìn)行封裝。這消除了不必要地重復(fù)編寫相同的代碼、降低了代碼不一致性，并且允許擁有所需權(quán)限的任何用戶或應(yīng)用程序訪問和執(zhí)行代碼。,4.,更容易維護(hù),在客戶端應(yīng)用程序調(diào)用過程并且將數(shù)據(jù)庫操作保持在數(shù)據(jù)層中時(shí)，對(duì)于基礎(chǔ)數(shù)據(jù)庫中的任何更改，只有過程是必須更新的。應(yīng)用程序?qū)颖３知?dú)立，并且不必知道對(duì)數(shù)據(jù)庫布局、關(guān)系或進(jìn)程的任何更改的情況。,5.,改進(jìn)的性能,默認(rèn)情況下，在首次執(zhí)行過程時(shí)將編譯過程，并且創(chuàng)建一個(gè)執(zhí)行計(jì)劃，供以后的執(zhí)行重復(fù)使用。因?yàn)椴樵兲幚砥鞑槐貏?chuàng)建新計(jì)劃，所以，它通常用更少的時(shí)間來處理過程。,任務(wù),8.1,創(chuàng)建存儲(chǔ)過程,8.1.3,創(chuàng)建用戶定義存儲(chǔ)過程,CREATE OR ALTER PROC|PROCEDURE,架構(gòu)名稱,.,過程名稱,;,數(shù)字,參數(shù),類型架構(gòu)名稱,.,數(shù)據(jù)類型,VARYING=,默認(rèn)值,OUT|OUTPUT|READONLY,.,WITH,.,FOR REPLICATION,AS BEGIN SQL,語句,;.END,;,:=,ENCRYPTION RECOMPILE EXECUTE AS,子句,任務(wù),8.1,創(chuàng)建存儲(chǔ)過程,8.1.5,執(zhí)行字符串,EXECUTE,語句的主要用途是執(zhí)行存儲(chǔ)過程。但也可以預(yù)先將,Transact-SQL,語句放在字符串變量中，然后使用,EXECUTE,語句來執(zhí)行這個(gè)字符串，語法格式如下。,EXEC|EXECUTE,(,字符串變量,|N,常量字符串,+.),AS LOGIN|USER=,登錄名,;,其中,字符串變量指定一個(gè)局部變量的名稱，該局部變量可以是任意,char,、,varchar,、,nchar,或,nvarchar,數(shù)據(jù)類型，其中包括,(max),數(shù)據(jù)類型。,N,常量字符串,表示常量字符串，該字符串可以是,nvarchar,或,varchar,數(shù)據(jù)類型。如果包含字母,N,，則字符串將解釋為,nvarchar,數(shù)據(jù)類型。,LOGIN,指定要模擬的上下文是登錄名。模擬范圍為服務(wù)器。,USER,指定要模擬的上下文是當(dāng)前數(shù)據(jù)庫中的用戶。模擬范圍只限于當(dāng)前數(shù)據(jù)庫。對(duì)數(shù)據(jù)庫用戶的上下文切換不會(huì)繼承該用戶的服務(wù)器級(jí)別權(quán)限。,執(zhí)行字符串時(shí)，數(shù)據(jù)庫上下文的更改只在,EXECUTE,語句結(jié)束前有效。,任務(wù),8.1,創(chuàng)建存儲(chǔ)過程,8.1.6,管理存儲(chǔ)過程,1.,查看存儲(chǔ)過程信息,查看過程名稱的列表：使用,sys.objects,目錄視圖。,顯示存儲(chǔ)過程的定義：使用,sys.sql_modules,目錄視圖。,查看存儲(chǔ)過程的定義：使用,sp_helptext,系統(tǒng)存儲(chǔ)過程。,查看存儲(chǔ)過程包含的參數(shù)：使用,sp_help,系統(tǒng)存儲(chǔ)過程。,查看存儲(chǔ)過程的相關(guān)性：使用,sp_depends,系統(tǒng)存儲(chǔ)過程。,任務(wù),8.1,創(chuàng)建存儲(chǔ)過程,8.1.6,管理存儲(chǔ)過程,2.,修改存儲(chǔ)過程,若要對(duì)現(xiàn)有的用戶定義存儲(chǔ)過程進(jìn)行修改，可以使用,ALTER PROCEDURE,語句來實(shí)現(xiàn)。在對(duì)象資源管理器中可以針對(duì)指定的存儲(chǔ)過程快速生成所需的,ALTER PROCEDURE,語句。,3.,重命名存儲(chǔ)過程,若要重命名存儲(chǔ)過程，可在對(duì)象資源管理器中右鍵單擊該存儲(chǔ)過程并選擇“重命名”，然后輸入新的過程名稱。此外，也可使用系統(tǒng)存儲(chǔ)過程,sp_rename,對(duì)用戶定義存儲(chǔ)過程進(jìn)行重命名。,4.,刪除存儲(chǔ)過程,可以使用,SSMS,對(duì)象資源管理器將存儲(chǔ)過程從數(shù)據(jù)庫中刪除。也可以使用,DROP PROCEDURE,語句從當(dāng)前數(shù)據(jù)庫中刪除一個(gè)或多個(gè)存儲(chǔ)過程或存儲(chǔ)過程組，語法格式如下。,DROP PROC|PROCEDURE IF EXISTS,構(gòu)架名稱,.,過程,.,任務(wù),8.1,創(chuàng)建存儲(chǔ)過程,8.2.1,理解觸發(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ā)器可以用于強(qiáng)制業(yè)務(wù)規(guī)則和數(shù)據(jù)完整性、查詢其他表并包括復(fù)雜的,Transact-SQL,語句。觸發(fā)器和觸發(fā)它的語句可以作為在觸發(fā)器內(nèi)回滾的單個(gè)事務(wù)對(duì)待。,如果檢測(cè)到錯(cuò)誤（例如磁盤空間不足），則整個(gè)事務(wù)即自動(dòng)回滾。,按照觸發(fā)器事件的不同，,DML,觸發(fā)器分為三種類型，即,INSERT,觸發(fā)器、,UPDATE,觸發(fā)器以及,DELETE,觸發(fā)器。,2.DDL,觸發(fā)器,當(dāng)服務(wù)器或數(shù)據(jù)庫中發(fā)生數(shù)據(jù)定義語言（,DDL,）事件時(shí)將調(diào)用,DDL,發(fā)器。像常規(guī),DML,觸發(fā)器一樣，,DDL,觸發(fā)器將激發(fā)存儲(chǔ)過程以響應(yīng)事件。但與,DML,觸發(fā)器不同的是，它們不是為響應(yīng)針對(duì)表或視圖的,UPDATE,、,INSERT,或,DELETE,語句而激發(fā)，而是為響應(yīng)多種數(shù)據(jù)定義語言（,DDL,）語句而激發(fā)。這些語句主要是以,CREATE,、,ALTER,和,DROP,開頭的語句。,DDL,觸發(fā)器可以用于管理任務(wù)，例如審核和控制數(shù)據(jù)庫操作。,任務(wù),8.2,創(chuàng)建觸發(fā)器,8.2.1,理解觸發(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ā)器可以用于強(qiáng)制業(yè)務(wù)規(guī)則和數(shù)據(jù)完整性、查詢其他表并包括復(fù)雜的,Transact-SQL,語句。觸發(fā)器和觸發(fā)它的語句可以作為在觸發(fā)器內(nèi)回滾的單個(gè)事務(wù)對(duì)待。,如果檢測(cè)到錯(cuò)誤（例如磁盤空間不足），則整個(gè)事務(wù)即自動(dòng)回滾。,按照觸發(fā)器事件的不同，,DML,觸發(fā)器分為三種類型，即,INSERT,觸發(fā)器、,UPDATE,觸發(fā)器以及,DELETE,觸發(fā)器。,2.DDL,觸發(fā)器,當(dāng)服務(wù)器或數(shù)據(jù)庫中發(fā)生數(shù)據(jù)定義語言（,DDL,）事件時(shí)將調(diào)用,DDL,發(fā)器。像常規(guī),DML,觸發(fā)器一樣，,DDL,觸發(fā)器將激發(fā)存儲(chǔ)過程以響應(yīng)事件。但與,DML,觸發(fā)器不同的是，它們不是為響應(yīng)針對(duì)表或視圖的,UPDATE,、,INSERT,或,DELETE,語句而激發(fā)，而是為響應(yīng)多種數(shù)據(jù)定義語言（,DDL,）語句而激發(fā)。這些語句主要是以,CREATE,、,ALTER,和,DROP,開頭的語句。,DDL,觸發(fā)器可以用于管理任務(wù)，例如審核和控制數(shù)據(jù)庫操作。,任務(wù),8.2,創(chuàng)建觸發(fā)器,8.2.2,實(shí)現(xiàn),DML,觸發(fā)器,1.DML,觸發(fā)器的類型,（,1,）,AFTER,觸發(fā)器：這種類型的觸發(fā)器在執(zhí)行了,INSERT,、,UPDATE,或,DELETE,語句操作之后執(zhí)行，它僅適用于表。,AFTER,觸發(fā)器可以分為以下三種類型：,INSERT,觸發(fā)器、,UPDATE,觸發(fā)器和,DELETE,觸發(fā)器。,（,2,）,INSTEAD OF,觸發(fā)器：執(zhí)行這種類型的觸發(fā)器可以代替通常的觸發(fā)動(dòng)作，還可以為帶有一個(gè)或多個(gè)基表的視圖定義,INSTEAD OF,觸發(fā)器，通過這些觸發(fā)器能夠擴(kuò)展視圖可支持的更新類型。,2.,創(chuàng)建,DML,觸發(fā)器,DML,觸發(fā)器可以使用,CREATE TRIGGER,語句來創(chuàng)建，基本語法格式如下。,CREATE OR ALTER TRIGGER ,架構(gòu)名稱,.,觸發(fā)器名稱,ON,表,|,視圖,WITH ENCRYPTION,FOR|AFTER|INSTEAD OF,INSERT,UPDATE,DELETE,AS.,任務(wù),8.2,創(chuàng)建觸發(fā)器,8.2.2,實(shí)現(xiàn),DML,觸發(fā)器,1.DML,觸發(fā)器的類型,（,1,）,AFTER,觸發(fā)器：這種類型的觸發(fā)器在執(zhí)行了,INSERT,、,UPDATE,或,DELETE,語句操作之后執(zhí)行，它僅適用于表。,AFTER,觸發(fā)器可以分為以下三種類型：,INSERT,觸發(fā)器、,UPDATE,觸發(fā)器和,DELETE,觸發(fā)器。,（,2,）,INSTEAD OF,觸發(fā)器：執(zhí)行這種類型的觸發(fā)器可以代替通常的觸發(fā)動(dòng)作，還可以為帶有一個(gè)或多個(gè)基表的視圖定義,INSTEAD OF,觸發(fā)器，通過這些觸發(fā)器能夠擴(kuò)展視圖可支持的更新類型。,2.,創(chuàng)建,DML,觸發(fā)器,DML,觸發(fā)器可以使用,CREATE TRIGGER,語句來創(chuàng)建，基本語法格式如下。,CREATE OR ALTER TRIGGER ,架構(gòu)名稱,.,觸發(fā)器名稱,ON,表,|,視圖,WITH ENCRYPTION,FOR|AFTER|INSTEAD OF,INSERT,UPDATE,DELETE,AS.,任務(wù),8.2,創(chuàng)建觸發(fā)器,8.2.3,實(shí)現(xiàn),DDL,觸發(fā)器,1.,理解,DDL,觸發(fā)器,設(shè)計(jì),DDL,觸發(fā)器之前，必須了解,DDL,觸發(fā)器的作用域并確定觸發(fā)觸發(fā)器的,Transact-SQL,語句或語句組。,在響應(yīng)當(dāng)前數(shù)據(jù)庫或服務(wù)器中處理的,Transact-SQL,事件時(shí)，都會(huì)激發(fā),DDL,觸發(fā)器。觸發(fā)器的作用域取決于事件。例如，每當(dāng)數(shù)據(jù)庫中發(fā)生,CREATE TABLE,事件時(shí)，都會(huì)觸發(fā)為響應(yīng),CREATE TABLE,事件創(chuàng)建的,DDL,觸發(fā)器。每當(dāng)服務(wù)器中發(fā)生,CREATE LOGIN,事件時(shí)，都會(huì)觸發(fā)為響應(yīng),CREATE LOGIN,事件。,2.,創(chuàng)建,DDL,觸發(fā)器,DDL,觸發(fā)器可以使用,CREATE TRIGGER,語句來創(chuàng)建，基本語法格式如下。,CREATE OR ALTER TRIGGER,觸發(fā)器名稱,ON ALL SERVER|DATABASE,WITH ENCRYPTION,FOR|AFTER,事件類型,|,事件組,.,AS SQL,語句,.,任務(wù),8.2,創(chuàng)建觸發(fā)器,8.2.4,管理觸發(fā)器,1.,修改觸發(fā)器,使用,ALTER TRIGGER,可以更改以前使用,CREATE TRIGGER,語句創(chuàng)建的,DML,或,DDL,觸發(fā)器的定義。除了以,ALTER,關(guān)鍵字開頭之外，,ALTER TRIGGER,的語法組成與,CREATE TRIGGER,是相同的。,2.,重命名觸發(fā)器,若要重命名觸發(fā)器，可使用,sp_rename,系統(tǒng)存儲(chǔ)過程來實(shí)現(xiàn)。重命名觸發(fā)器并不會(huì)更改它在觸發(fā)器定義文本中的名稱。要在定義中更改觸發(fā)器的名稱，應(yīng)直接修改觸發(fā)器。,若