1、第第8章章存儲(chǔ)過程、觸發(fā)器和函數(shù)存儲(chǔ)過程、觸發(fā)器和函數(shù) 本章學(xué)習(xí)目標(biāo)本章學(xué)習(xí)目標(biāo) 掌握存儲(chǔ)過程的基本概念、特點(diǎn)和類型掌握存儲(chǔ)過程的基本概念、特點(diǎn)和類型 掌握存儲(chǔ)過程的創(chuàng)建和執(zhí)行方式掌握存儲(chǔ)過程的創(chuàng)建和執(zhí)行方式 掌握存儲(chǔ)過程的管理方式掌握存儲(chǔ)過程的管理方式 掌握觸發(fā)器的基本概念、特點(diǎn)和類型掌握觸發(fā)器的基本概念、特點(diǎn)和類型 掌握觸發(fā)器的創(chuàng)建和管理方式掌握觸發(fā)器的創(chuàng)建和管理方式 了解常用的聚合函數(shù)、日期和時(shí)間函數(shù)、數(shù)了解常用的聚合函數(shù)、日期和時(shí)間函數(shù)、數(shù)學(xué)函數(shù)、字符串函數(shù)學(xué)函數(shù)、字符串函數(shù)本章本章主要內(nèi)容主要內(nèi)容 存儲(chǔ)過程概述存儲(chǔ)過程概述 創(chuàng)建和執(zhí)行存儲(chǔ)過程創(chuàng)建和執(zhí)行存儲(chǔ)過程 管理存儲(chǔ)過程管理存儲(chǔ)過

2、程 創(chuàng)建觸發(fā)器創(chuàng)建觸發(fā)器 管理觸發(fā)器管理觸發(fā)器 常用函數(shù)常用函數(shù)1. 存儲(chǔ)過程的定義2. 存儲(chǔ)過程的特點(diǎn)3. 存儲(chǔ)過程的類型8.1 存儲(chǔ)過程概述存儲(chǔ)過程概述 存儲(chǔ)過程是一組為了完成特定任務(wù)的SQL語句集，是利用SQL Server所提供的Transact-SQL語言所編寫的程序。 存儲(chǔ)過程經(jīng)編譯和優(yōu)化后存儲(chǔ)在數(shù)據(jù)庫服務(wù)器中。 存儲(chǔ)過程可以由應(yīng)用程序通過一個(gè)調(diào)用來執(zhí)行，而且允許用戶聲明變量。 存儲(chǔ)過程可以接收和輸出參數(shù)、返回執(zhí)行存儲(chǔ)過程的狀態(tài)值。1. 存儲(chǔ)過程的定義 存儲(chǔ)過程允許用戶模塊化程序設(shè)計(jì)，大大提高程序的設(shè)計(jì)效率。 存儲(chǔ)過程可以強(qiáng)制應(yīng)用程序的安全性，可以防止SQL嵌入式攻擊。 由于存儲(chǔ)過

3、程在服務(wù)器端存儲(chǔ)和運(yùn)行，并且第一次執(zhí)行后在內(nèi)存中保留，以后調(diào)用時(shí)不需要再次從磁盤裝載，能夠?qū)崿F(xiàn)更快的執(zhí)行速度。 用戶執(zhí)行存儲(chǔ)過程時(shí)，只需在客戶端通過指明所調(diào)用的存儲(chǔ)過程的名稱來調(diào)用存儲(chǔ)過程，可大大較少網(wǎng)絡(luò)通信流量。 存儲(chǔ)過程具有安全特性和所有權(quán)鏈接，可以執(zhí)行所有的權(quán)限管理。2.存儲(chǔ)過程的特點(diǎn) 用戶自定義存儲(chǔ)過程 擴(kuò)展存儲(chǔ)過程 系統(tǒng)存儲(chǔ)過程。 3.存儲(chǔ)過程的類型1. 在圖形界面下創(chuàng)建存儲(chǔ)過程2. 用Transact-SQL語句創(chuàng)建不帶參數(shù)的存儲(chǔ)過程 3. 用Transact-SQL語句創(chuàng)建帶參數(shù)的存儲(chǔ)過程 4. 在圖形界面下執(zhí)行存儲(chǔ)過程5. 使用Transact-SQL語句執(zhí)行存儲(chǔ)過程8.2 創(chuàng)

4、建和執(zhí)行存儲(chǔ)過程創(chuàng)建和執(zhí)行存儲(chǔ)過程 1. 在圖形界面下創(chuàng)建存儲(chǔ)過程2. 用Transact-SQL語句創(chuàng)建不帶參數(shù)的存儲(chǔ)過程 語法：CREATE PROCEDURE procedure_nameASsql_statement【例8-1】 CREATE PROCEDURE hotel_proc2ASSELECT 身份證號(hào),姓名,電話 FROM 客戶信息3. 用Transact-SQL語句創(chuàng)建帶參數(shù)的存儲(chǔ)過程 語法：CREATE PROCEDURE procedure_name ;numberparameter data_type VARYING=defaultOUTPUT,.n WITH REC

5、OMPILE | ENCRYPTION | RECOMPILE , ENCRYPTIONFOR REPLICATION AS sql_statement .n 【例8-2】 CREATE PROCEDURE hotel_proc3name varchar(12)ASSELECT * FROM 客戶信息WHERE 姓名=name4. 在圖形界面下執(zhí)行存儲(chǔ)過程5.使用Transact-SQL語句執(zhí)行存儲(chǔ)過程EXECUTE procedure_name【例8-4】DECLARE temp varchar(12)SET temp=李銘EXECUTE hotel_proc3 temp1. 使用“對(duì)象資源

6、管理器”查看存儲(chǔ)過程 2. 使用系統(tǒng)存儲(chǔ)過程查看存儲(chǔ)過程3. 使用“對(duì)象資源管理器”修改存儲(chǔ)過程4. 使用Transact-SQL語句修改存儲(chǔ)過程5. 使用“對(duì)象資源管理器”刪除存儲(chǔ)過程6. 使用Transact-SQL語句刪除存儲(chǔ)過程 8.3管理存儲(chǔ)過程管理存儲(chǔ)過程1、使用“對(duì)象資源管理器”查看存儲(chǔ)過程 用 戶 可 以 先 用 系 統(tǒng) 存 儲(chǔ) 過 程sp_stored_procedures列出當(dāng)前環(huán)境中所有的存儲(chǔ)過程， 然后使用另一個(gè)系統(tǒng)存儲(chǔ)過程sp_helptext查看某存儲(chǔ)過程的正文信息。2、使用系統(tǒng)存儲(chǔ)過程查看存儲(chǔ)過程3、使用“對(duì)象資源管理器”修改存儲(chǔ)過程ALTER PROCEDURE

7、 procedure_name ;numberparameter data_type VARYING=defaultOUTPUT,.nWITH RECOMPILE | ENCRYPTION | RECOMPILE,ENCRYPTIONFOR REPLICATIONAS sql_statement .n4、使用Transact-SQL語句修改存儲(chǔ)過程5、使用“對(duì)象資源管理器”刪除存儲(chǔ)過程DROP PROCEDURE procedure_name ,number6、使用Transact-SQL語句刪除存儲(chǔ)過程1.觸發(fā)器定義觸發(fā)器定義2.觸發(fā)器的特點(diǎn)觸發(fā)器的特點(diǎn)3.觸發(fā)器的分類觸發(fā)器的分類4.創(chuàng)建創(chuàng)

8、建DML觸發(fā)器觸發(fā)器5.創(chuàng)建創(chuàng)建DDL觸發(fā)器觸發(fā)器 6.查看觸發(fā)器查看觸發(fā)器7.修改修改DML觸發(fā)器觸發(fā)器 8.修改修改DDL觸發(fā)器觸發(fā)器9.重命名觸發(fā)器重命名觸發(fā)器 10. 刪除刪除DML觸發(fā)器觸發(fā)器11. 刪除刪除DDL觸發(fā)器觸發(fā)器12. 禁用啟用觸發(fā)器禁用啟用觸發(fā)器8.4創(chuàng)建和管理觸發(fā)器創(chuàng)建和管理觸發(fā)器 l觸發(fā)器是一種特殊的存儲(chǔ)過程，它包括了大量的Transact-SQL語句。l但是觸發(fā)器又與一般的存儲(chǔ)過程有著顯著的區(qū)別，一般的儲(chǔ)過程可以由用戶直接調(diào)用執(zhí)行，但是觸發(fā)器不能被直接調(diào)用執(zhí)行，它只能由事件觸發(fā)而自動(dòng)執(zhí)行。 1、觸發(fā)器定義觸發(fā)器是自動(dòng)執(zhí)行的，當(dāng)用戶對(duì)表中數(shù)據(jù)作了某些操作之后立即被

9、觸發(fā)。觸發(fā)器可通過數(shù)據(jù)庫中的相關(guān)表實(shí)現(xiàn)級(jí)聯(lián)更改，實(shí)現(xiàn)多個(gè)表之間數(shù)據(jù)的一致性和完整性。觸發(fā)器可以強(qiáng)制比用 CHECK 約束定義的約束更為復(fù)雜的約束。與 CHECK 約束不同，觸發(fā)器可以引用其它表中的列。觸發(fā)器也可以評(píng)估數(shù)據(jù)修改前后的表狀態(tài)，并根據(jù)其差異采取對(duì)策。2、觸發(fā)器的特點(diǎn)觸發(fā)器的特點(diǎn)lDML觸發(fā)器lDDL觸發(fā)器3、觸發(fā)器的分類觸發(fā)器的分類CREATE TRIGGER schema_name.trigger_nameON table | viewWITH ENCRYPTIONFOR | AFTER | INSTEAD OFINSERT , UPDATE , DELETEWITH APPEND

10、NOT FOR REPLICATIONAS sql_statement ; ,.n | EXTERNAL NAME 4、創(chuàng)建、創(chuàng)建DML觸發(fā)器觸發(fā)器CREATE TRIGGER trigger_name ON ALL SERVER | DATABASEWITH ENCRYPTIONFOR | AFTER event_type | event_group ,.nAS sql_statement ; ,.n | EXTERNAL NAME ;5、創(chuàng)建、創(chuàng)建DDL觸發(fā)器觸發(fā)器sp_help trigger_name：用于查看觸發(fā)器的名稱、屬性、類型、創(chuàng)建時(shí)間等一般信息。sp_helptext tri

11、gger_name：用于查看觸發(fā)器的正文信息。sp_depends trigger_name | table_name：用于查看觸發(fā)器所引用的表或表所涉及的觸發(fā)器。6、查看觸發(fā)器查看觸發(fā)器ALTER TRIGGER schema_name.trigger_nameON table | viewWITH ENCRYPTIONFOR | AFTER | INSTEAD OFDELETE , INSERT , UPDATENOT FOR REPLICATIONASsql_statement ; .n | EXTERNAL NAME ;7、修改修改DML觸發(fā)器觸發(fā)器 ALTER TRIGGER tri

12、gger_nameON DATABASE | ALL SERVERWITH ENCRYPTIONFOR | AFTERevent_type ,.n | event_groupASsql_statement ; | EXTERNAL NAME ; 8、修改修改DDL觸發(fā)器觸發(fā)器sp_rename old_name,new_name9、重命名觸發(fā)器、重命名觸發(fā)器DROP TRIGGER schema_name.trigger_name ,.n ;10、刪除刪除DML觸發(fā)器觸發(fā)器DROP TRIGGER trigger_name ,.n ON DATABASE | ALL SERVER ;11、觸發(fā)器定義禁用DISABLE TRIGGER schema_name.trigger_name ,.n | ALLON object_name | DATABASE | ALL SERVER ;啟用ENABLE TRIGGER schema_name.trigger_name ,.n | AL