【正文】 第 10章 存儲過程、觸發(fā)器 主講教師：衛(wèi)琳 ? 存儲過程、觸發(fā)器和游標是 SQL Server數(shù)據(jù)庫的三個重要組成部分。 SQL Server 2022使用它們從不同方面提高數(shù)據(jù)處理能力。 ? 在 SQL Server 2022中，可以像其他程序設計語言一樣定義子程序，稱為存儲過程。存儲過程是 SQL Server 2022提供的最強大的工具之一。理解并運用它，可以創(chuàng)建健壯、安全且具有良好性能的數(shù)據(jù)庫，可以為用戶實現(xiàn)最復雜的商業(yè)事務。 ? 觸發(fā)器是一種特殊類型的存儲過程：它通過事件觸發(fā)而被自動執(zhí)行。自動執(zhí)行意味著更少的手工操作以及更小的出錯機率。觸發(fā)器用于強制復雜的完整性檢查，審核更改，維護不規(guī)范的數(shù)據(jù)等等。 SQL Server 2022允許 DML語句和 DDL語句創(chuàng)建觸發(fā)器，可以引發(fā) AFTER或者 INSTEAD OF觸發(fā)事件。 ? 游標主要用于實現(xiàn)一些不能使用面向集合的語句實現(xiàn)的操作。通過游標， SQL Server提供了一個對結果集進行逐行處理的能力?？梢园延螛丝礊橐环N特殊的指針，它可以指向結果集中的任意位置，在查詢數(shù)據(jù)的同時對數(shù)據(jù)進行處理。 本章學習目標： ? 了解存儲過程、觸發(fā)器和游標的基本概念與特點 ? 掌握存儲過程的基本類型和相關操作 ? 掌握觸發(fā)器的類型與相關操作 存儲過程 ? 通過前面的學習，我們能夠編寫并運行 TSQL程序以完成各種不同的應用。保存 TSQL程序的方法有兩種：一種是在本地保存程序的源文件，運行時先打開源文件再執(zhí)行程序；另一種方法即將程序存儲為存儲過程，運行時調(diào)用存儲過程執(zhí)行。 ? 因為存儲過程是由一組 TSQL語句構成的，要使用存儲過程，我們必需熟悉前面幾章所討論的基本的 TSQL語句，并且需要了解掌握一些關于函數(shù)、過程的概念。 存儲過程的基本概念 ? 存儲過程是事先編好的、存儲在數(shù)據(jù)庫中一組被編譯了的 TSQL命令集合，這些命令用來完成對數(shù)據(jù)庫的指定操作：存儲過程可以接收用戶的輸入?yún)?shù)、向客戶端返回表格或標量結果和消息、調(diào)用數(shù)據(jù)定義語言 (DDL) 和數(shù)據(jù)操作語言 (DML) 語句，然后返回輸出參數(shù)。 ? 通過定義可以看到，存儲過程起到了我們在其他語言中所說的子程序的作用，我們可以將經(jīng)常執(zhí)行的管理任務或者復雜的業(yè)務規(guī)則，預先用 TSQL語句寫好并保存為存儲過程 , 當需要數(shù)據(jù)庫提供與該存儲過程的功能相同的服務時，只需要使用 EXECUTE命令，即可調(diào)用存儲過程完成命令。 存儲過程的優(yōu)點： ? 1. 減少網(wǎng)絡流量：存儲過程在數(shù)據(jù)庫服務器端執(zhí)行，只向客戶端返回執(zhí)行結果。因此可以將在網(wǎng)絡中要發(fā)送的數(shù)百行代碼，編寫為一條存儲過程，這樣客戶端只需要提交存儲過程的名稱和參數(shù)，即可實現(xiàn)相應功能，節(jié)省了網(wǎng)絡流量，提高了執(zhí)行的效率。此外，由于所有的操作都在服務器端完成，避免了在客戶端和服務器端之間的多次往返。存儲過程只需要將最終結果通過網(wǎng)絡傳輸?shù)娇蛻舳恕? ? 2. 提高系統(tǒng)性能：一般 TSQL語句每執(zhí)行一次就需要編譯一次，而存儲過程只在創(chuàng)建時進行編譯，被編譯后存放在數(shù)據(jù)庫服務器的過程高速緩存中，當使用時，服務器不必再重新分析和編譯它們。因此，當對數(shù)據(jù)庫進行復雜操作時（如對多個表進行 UPDATE、 INSERT或 DELETE操作時），可將這些復雜操作用存儲過程封裝起來與數(shù)據(jù)庫提供的事務處理結合一起使用，節(jié)省了分析、解析和優(yōu)化代碼所需的 CPU資源和時間。 ? 3. 安全性高：使用存儲過程可以完成所有數(shù)據(jù)庫操作，并且可授予沒有直接執(zhí)行存儲過程中語句的權限的用戶，也可執(zhí)行該存儲過程的權限。另外可以防止用戶直接訪問表，強制用戶使用存儲過程執(zhí)行特定的任務。 ? 4. 可重用性：存儲過程只需創(chuàng)建并存儲在數(shù)據(jù)庫中，以后即可任意在程序中調(diào)用該過程。存儲過程可獨立于程序源代碼而單獨修改，減少數(shù)據(jù)庫開發(fā)人員的工作量。 ? 5. 可自動完成需要預先執(zhí)行的任務：存儲過程可以在系統(tǒng)啟動時自動執(zhí)行，完成一些需要預先執(zhí)行的任務，而不必在系統(tǒng)啟動后再進行人工操作。 存儲過程的類型 1. 系統(tǒng)存儲過程 2. 擴展存儲過程 ? 用戶存儲過程在用戶數(shù)據(jù)庫中創(chuàng)建，通常與數(shù)據(jù)庫對象進行交互，用于完成特定數(shù)據(jù)庫操作任務，可以接受和返回用戶提供的參數(shù)，名稱不能以 sp_為前綴。 ? 在 SQL Server 2022中，用戶存儲過程有兩種類型：TransactSQL存儲過程和 clr存儲過程。 ? TransactSQL存儲過程保存 TSQL語句的集合，可以接受和返回用戶提供的參數(shù)，也可以從數(shù)據(jù)庫向客戶端應用程序返回數(shù)據(jù)； ? CLR存儲過程是指對 Framework公共語言運行時方法的引用，可以接受和返回用戶提供的參數(shù)。它們在 .NET Framework程序集中是作為類的公共靜態(tài)方法實現(xiàn)的 use test go create procedure student_info as select ,sname,ame,grade from student,sc,course where = and = go 示例：創(chuàng)建一個存儲過程，返回學生學號，姓名，課程名，成績信息。 存儲過程創(chuàng)建與執(zhí)行 use test go create procedure student_info2 @xm varchar(8),@kcm varchar(50) as select ,sname,ame,grade from student,sc,course where = and = and sname=@xm and ame=@kcm go 示例：創(chuàng)建一個存儲過程，返回學生學號，姓名，課程名，成績信息。接受與傳遞參數(shù)精確匹配的值。 1) 按位置傳送參數(shù)值 exec student_info2 39。李明 39。,39。高等數(shù)學 39。 2)使用參數(shù)名傳送 exec student_info2 @xm=39。李明 39。,@kcm=39。高等數(shù)學 39。 exec student_info2 @kcm=39。高等數(shù)學 39。,@xm=39。李明 39。 存儲過程調(diào)用形式 示例：新建一個存儲過程，該存儲過程定義了兩個日期時間類型的輸入?yún)?shù)和一個字符型輸入?yún)?shù)，返回所有出生日期在兩個輸入日期之間，性別與輸入的字符型參數(shù)相同的學生信息，其中字符型輸入?yún)?shù)指定的默認值為“女”。 use test go create procedure stud_info3 @startdate date,@enddate date,@sex char(2)=39。女 39。 as if (@startdate