信息系統(tǒng)與數(shù)據(jù)庫技術(shù)：4.4_6_SQL Server存儲(chǔ)過程、觸發(fā)器

1、1. 標(biāo)識(shí)符、常量和變量 （1）標(biāo)識(shí)符：由用戶定義的名稱，用來標(biāo)識(shí)各種對象如服務(wù)器、數(shù)據(jù)庫、數(shù)據(jù)庫對象、變量等。 如果標(biāo)志符中包含空格，要用雙引號(hào)（“”）或方括號(hào)()擴(kuò)起來。如：Student Name不合法,必須將其表示為Student Name或Student Name。4.3.4 T-SQL的運(yùn)算功能和控制流程 T-SQL雖然和高級語言不同，但它也有運(yùn)算、控制等功能，以支持復(fù)雜的數(shù)據(jù)檢索和集合操縱 （2）常量：在程序運(yùn)行過程中值不變的量。 字符串常量、日期時(shí)間常量要用單引號(hào)擴(kuò)起，例如Li Ping ，1990-05-04。常量可分為字符串常量、整型常量、實(shí)型常量、日期時(shí)間常量、貨幣常量和

2、唯一標(biāo)識(shí)常量。單引號(hào)中的字符串包含單引號(hào)，可以使用兩個(gè)單引號(hào)表示嵌入的單引號(hào)。 （3）變量：在程序運(yùn)行過程中其值可以被改變的量 。1)變量的分類 局部變量：由用戶定義和使用，其名稱前有一個(gè)符號(hào)。 全局變量：全局變量由系統(tǒng)定義和維護(hù)，其名稱前有兩個(gè)符號(hào)。 2）局部變量的定義和賦值 定義格式： DECLARE 局部變量名 數(shù)據(jù)類型【例】DECLARE x float, var char(8) 該定義語句定義了變量x是浮點(diǎn)實(shí)型，變量var是長度為8的定長字符數(shù)據(jù)類型。 如在一條語句中聲明多個(gè)變量，各變量之間用“,”分隔。 局部變量被定義后其初始值為NULL。 局部變量賦值的基本語法格式：SET 局部

3、變量名=表達(dá)式SELECT 局部變量名=表達(dá)式 【例4-53】定義變量c_code為長度為6的定長字符串，score為精度為1的浮點(diǎn)型，并分別用SET和SELECT語句為它們賦值，然后用這兩個(gè)變量查詢T_Grade表中課程代碼為“110006”且成績低于90分的記錄。DECLARE c_code char(6), score Numeric(3,1)SET c_code=130001SELECT score=80 SELECT * FROM T_Grade WHERE CourseCode=c_code AND Grade=3 BEGIN SET text=你選了+CAST(cn AS cha

4、r(2) /* CAST函數(shù)將cn的值轉(zhuǎn)換為長度為2的字符數(shù)據(jù)*/ SET text=text+門課。很好，你完成了任務(wù)！ ENDELSE BEGIN SET text=你選了+CAST(cn AS char(2) SET text=text+門課。選課太少，加油！ ENDSELECT text AS 選課提示圖4-53 統(tǒng)計(jì)選課數(shù)目結(jié)果（3）WHILE語句實(shí)現(xiàn)一條SQL語句或SQL語句塊重復(fù)執(zhí)行。 語法格式：WHILE 條件表達(dá)式 SQL語句1 BREAK SQL語句2 CONTINUE說明：該語句計(jì)算條件表達(dá)式的值，如果為TRUE，則執(zhí)行WHILE后的語句塊。 BREAK為從本層WHILE

5、循環(huán)中退出，當(dāng)存在多層循環(huán)嵌套時(shí)，使用BREAK語句只能退出其所在的內(nèi)層循環(huán)，然后重新開始外層的循環(huán)。CONTINUE為結(jié)束本次循環(huán)，開始下一次循環(huán)的判斷。 【例4-56】計(jì)算10！。 DECLARE p int, i smallint, text varchar(100)SET p=1SET i=1WHILE i=90 THEN A WHEN AVG(Grade)=80 THEN B WHEN AVG(Grade)=70 THEN C WHEN AVG(Grade)=60 THEN D WHEN AVG(Grade)60 THEN E END AS 等級 FROM T_Grade GROUP

6、 BY StudentCodeSELECT圖4-56 成績等級部分結(jié)果5注釋T-SQL中提供兩種注釋方法，分別用于單行和多行注釋。（1）單行注釋（-）語法格式：- 注釋文本內(nèi)容文本字符串與要執(zhí)行的代碼可同處一行，也可另起一行。（2）多行注釋（/* */） 語法格式： /* 注釋文本內(nèi)容*/ 例如：USE School -打開School數(shù)據(jù)庫.SELECT * FROM T_Student /*執(zhí)行一個(gè)SELECT語句，顯示學(xué)生表中所有學(xué)生信息*/4.4 視圖(1) 視圖是一個(gè)虛擬表，其內(nèi)容來自對表查詢的基礎(chǔ)上。(2) 視圖的行和列數(shù)據(jù)來自于定義視圖的查詢所引用的表，并在引用表時(shí)動(dòng)態(tài)生成。(3

7、) 視圖的結(jié)構(gòu)和顯示的數(shù)據(jù)行并沒有實(shí)際地以視圖結(jié)構(gòu)存儲(chǔ)在數(shù)據(jù)庫中，而是存儲(chǔ)在視圖所引用的基本表中。(4)視圖集中、簡化和自定義每個(gè)用戶對數(shù)據(jù)庫的不同認(rèn)識(shí),是數(shù)據(jù)庫的外模式。用戶可以將數(shù)據(jù)庫中自己感興趣的特定數(shù)據(jù)展現(xiàn)在視圖中。(5) 視圖也可作為一種安全機(jī)制，允許用戶通過視圖訪問數(shù)據(jù)，而不授予用戶直接訪問視圖基礎(chǔ)表的權(quán)限。數(shù)據(jù)庫的三級模式結(jié)構(gòu)視圖示例：4.4.1創(chuàng)建視圖創(chuàng)建視圖的方法： 1）在企業(yè)管理器中創(chuàng)建； 2）使用T-SQL的CREATE VIEW語句。 使用企業(yè)管理器創(chuàng)建好視圖后，可以使用“生成SQL腳本”功能查看和保存其對應(yīng)的SQL定義語句。 創(chuàng)建視圖時(shí)必須遵循以下原則：只能在當(dāng)前數(shù)據(jù)

8、庫中創(chuàng)建視圖。 視圖在數(shù)據(jù)庫中作為一個(gè)對象存儲(chǔ)，視圖名稱不得與數(shù)據(jù)庫中的表重名。 1使用企業(yè)管理器創(chuàng)建視圖 【例4-59】在數(shù)據(jù)庫School中，建視圖V_StudentGrade，查詢學(xué)生成績情況。1）展開School數(shù)據(jù)庫文件夾，右擊“視圖”對象，在彈出的快捷菜單上選擇“新建視圖”命令，打開新建視圖對話框，該對話框包括四個(gè)窗格：上格是表及關(guān)系窗口，中格為視圖選擇表中列的網(wǎng)格，然后是SQL語句窗格，下格是結(jié)果窗格。2）右單擊對話框窗口上格，在快捷菜單中選擇“添加表”命令，顯示添加表對話框。 圖4-57 新建視圖對話框圖4-58 添加表對話框3）在添加表對話框中選擇與視圖有關(guān)的表、視圖或函數(shù)（

9、通過Ctrl或Shift鍵可選擇多項(xiàng)），然后單擊【添加】按鈕；或者直接雙擊要添加的表等，即可將其添加到視圖的查詢中。這里選擇表：T_Student、T_Course和T_Grade。4）選擇的表添加到窗口的上格，單擊每個(gè)表字段前的復(fù)選框，可將該字段添加到視圖中。也可在所示窗口的第二個(gè)子窗口選擇視圖字段，并可指定列的別名、排序方式和規(guī)則等。在選擇過程中，第三個(gè)子窗口中的SELECT語句也會(huì)隨之自動(dòng)改變。也可直接在該子窗口中輸入SELECT語句。這里選擇StudentName、CourseName和Grade三個(gè)字段。 5）單擊工具欄上的按鈕執(zhí)行，結(jié)果顯示窗口的第三個(gè)子窗口。 6）單擊工具欄上的按

10、鈕，在彈出的另存為對話框中為視圖命名，本例輸入“V_StudentGrade”，單擊【確定】按鈕保存視圖，從而完成視圖創(chuàng)建。 圖4-59 V-StudentGrade視圖的建立【例4-60】在數(shù)據(jù)庫School中，建立視圖V_StudentAvgGrade, 查詢學(xué)生的平均成績。1）在新建視圖窗口中上格選擇表T_Student和T_Grade；2）在字段網(wǎng)格中選擇字段和設(shè)定匯總字段，或直接在SQL語句窗格中輔助完成語句：SELECT StudentName, AVG(Grade) AS 平均成績FROM T_Grade INNER JOIN T_Student ON T_Grade.Stude

11、ntCode = T_Student.StudentCodeGROUP BY StudentName3）運(yùn)行查看視圖結(jié)果，保存該視圖為V_StudentAvgGrade。圖4-60 V-StudentAvgGrade視圖的建立【例4-61】在數(shù)據(jù)庫School中，建立視圖V_StudentAvgGradeExcellent, 查詢平均成績大于等于90學(xué)生。 該例可以參照前面方法在基本表T_Student和T_Grade上創(chuàng)建。但由于視圖的內(nèi)容也可以來自另一個(gè)視圖，本例基于V-StudentAvgGrade完成。各窗格設(shè)置及查詢結(jié)果如圖4-61所示。圖4-61 V-StudentAvgGrade

12、Excellent視圖的建立4.4.2 使用視圖 創(chuàng)建后的視圖跟的表的用法相同，可以通過視圖查詢和修改數(shù)據(jù)庫?！纠?-62】在數(shù)據(jù)庫School中，從視圖V_StudentGrade查詢陳佳迪的成績。 SELECT * FROM V_StudentGrade WHERE StudentName=陳佳迪4.4.3修改和刪除視圖 （1）在企業(yè)管理器中修改視圖1）在企業(yè)管理器中展開相應(yīng)數(shù)據(jù)庫和視圖，右單擊要修改的視圖，在彈出的快捷菜單上選擇“設(shè)計(jì)視圖” 。2）在彈出的“設(shè)計(jì)視圖”窗口中，可以用在企業(yè)管理器中創(chuàng)建視圖相同的方法修改已定義好的視圖。修改完畢存盤。（2）在企業(yè)管理器中刪除視圖1）在企業(yè)管理

13、器中展開相應(yīng)數(shù)據(jù)庫和視圖，右單擊要?jiǎng)h除的視圖，在彈出的快捷菜單上選擇“刪除” 。2）在彈出的對話框中單擊【確定】按鈕，即可刪除選擇的視圖。 4.5 存儲(chǔ)過程(1)存儲(chǔ)過程是T-SQL語句的集合，它作為數(shù)據(jù)庫對象之一被存儲(chǔ)在數(shù)據(jù)庫中。 (2)存儲(chǔ)過程的作用和使用方式類似于一些編程語言中的過程。使用存儲(chǔ)過程有以下優(yōu)點(diǎn)：1)可以在一個(gè)存儲(chǔ)過程中執(zhí)行多條SQL語句；2)可通過輸入?yún)?shù)的變化調(diào)用存儲(chǔ)過程進(jìn)行動(dòng)態(tài)執(zhí)行；3)存儲(chǔ)過程在創(chuàng)建時(shí)就在服務(wù)器端進(jìn)行了編譯，節(jié)省SQL語句的運(yùn)行時(shí)間；4)提供了安全機(jī)制，它限制了用戶訪問SQL語句的權(quán)利，只為特定用戶開放存儲(chǔ)過程。 4.5.1 創(chuàng)建存儲(chǔ)過程 用戶首先創(chuàng)建

14、存儲(chǔ)過程以實(shí)現(xiàn)特定的功能，然后可在程序中調(diào)用該存儲(chǔ)過程執(zhí)行。 1使用企業(yè)管理器創(chuàng)建視圖 【例4-63】在School數(shù)據(jù)庫中，創(chuàng)建存儲(chǔ)過程proc_Course， 查詢所有課程信息。 1）打開企業(yè)管理器，展開School數(shù)據(jù)庫文件夾，右單擊“存儲(chǔ)過程”，在彈出的快捷菜單上選擇“新建存儲(chǔ)過程”命令，打開新建存儲(chǔ)過程對話框。 2）將“OWNER”和“PROCEDURE NAME”參數(shù)分別替換成存儲(chǔ)過程所有者的名稱和存儲(chǔ)過程的名稱。默認(rèn)為dbo和PROCEDURE NAME。 3）從“文本”列表框的第二行開始輸入存儲(chǔ)過程的T-SQL語句。這里輸入的T-SQL語句僅有一條:SELECT * FROM

15、T_Course。 4）單擊【語法檢查】按鈕可以檢查創(chuàng)建存儲(chǔ)過程的T-SQL語句的語法是否正確。單擊【另存為模板】按鈕可將所創(chuàng)建的存儲(chǔ)過程設(shè)置為模板。 5）完成后，單擊【確定】按鈕即創(chuàng)建和保存了存儲(chǔ)過程proc_Course。 圖4-64 創(chuàng)建存儲(chǔ)過程示例2使用T-SQL語句創(chuàng)建存儲(chǔ)過程 可以直接在SQL查詢分析器中定義和調(diào)用存儲(chǔ)過程。常用存儲(chǔ)過程的語法格式： CREATE PROCDURE 存儲(chǔ)過程名 形式參數(shù) 數(shù)據(jù)類型VARYING=默認(rèn)值OUTPUT AS SQL語句1 SQL語句n 說明： “形式參數(shù)”名稱必須符合標(biāo)識(shí)符規(guī)則；OUTPUT表示該參數(shù)是可以返回的，可將信息返回調(diào)用者；如果

16、有多個(gè)參數(shù)，可以依次按以上參數(shù)定義規(guī)則列出，用逗號(hào)“,”隔開。 【例4-64】在School中創(chuàng)建一個(gè)的存儲(chǔ)過程proc_SearchStudent,查詢指定學(xué)生的選課情況。CREATE PROC proc_SearchStudent stcode char(8)ASSELECT T_Student.StudentName,T_Course.CourseNameFROM T_Student JOIN T_Grade JOIN T_CourseON T_Grade.CourseCode=T_Course.CourseCodeON T_Student.StudentCode=T_Grade.Stu

17、dentCodeWHERE T_Student.StudentCode=scode 4.5.2 執(zhí)行存儲(chǔ)過程 存儲(chǔ)過程定義后，可通過EXECTE語句來執(zhí)行。 語法格式： EXECUTE 存儲(chǔ)過程名形參=實(shí)參值|變量OUTPUT|DEFAULT1)“形參”是創(chuàng)建存儲(chǔ)過程時(shí)定義的形參名；2)“實(shí)參值”是輸入?yún)?shù)的值；3)“變量”表示用來保存參數(shù)或者返回參數(shù)的變量；OUTPUT表示指定參數(shù)為返回參數(shù)；4)DEFAULT表示使用該參數(shù)的默認(rèn)值作為實(shí)參。5)如有多個(gè)參數(shù)，可依次按以上參數(shù)定義規(guī)則列出，用逗號(hào)“，”隔開。 調(diào)用【例4-63】創(chuàng)建的存儲(chǔ)過程proc_Course的語句為：EXEC proc_

18、Course調(diào)用【例4-64】創(chuàng)建的存儲(chǔ)過程proc_SearchStudent，查詢05101101學(xué)生的選課情況。EXEC proc_SearchStudent 05101101或EXEC proc_ SearchStudent scode=05101101采用“參數(shù)=值”的形式，各個(gè)參數(shù)的順序可以任意排列。實(shí)參順序和定義時(shí)的參數(shù)順序一致?！纠?-65】在School中創(chuàng)建一個(gè)的存儲(chǔ)過程proc_SearchStudentAvgGrade，查詢某個(gè)學(xué)生的選課數(shù)目和平均成績。CREATE PROC proc_SearchStudentAvgGrade stcode char(8), stco

19、unt int OUTPUT,stavg int OUTPUTASSELECT stcount=COUNT(T_Grade.StudentCode),stavg=AVG(T_Grade.Grade)FROM T_GradeWHERE T_Grade.StudentCode=stcodeGROUP BY T_Grade.StudentCode本例是帶一個(gè)輸入?yún)?shù)和二個(gè)輸出參數(shù)的存儲(chǔ)過程，在定義輸出參數(shù)時(shí)要用OUTPUT進(jìn)行說明。 調(diào)用存儲(chǔ)過程proc_SearchStudentAvgGrade，查詢05101101學(xué)生的選課數(shù)目和平均成績。DECLARE scount int ,savg int

20、 EXEC proc_SearchStudentAvgGrade 05101101,scount OUTPUT,savg OUTPUT PRINT 學(xué)生+ 05101101+的選課數(shù)目為+CAST(scount AS char(2)+門 PRINT 學(xué)生+ 05101101+的平均成績?yōu)?STR(savg,5,2)+分 4.5.3 查看、修改和刪除存儲(chǔ)過程 用戶可以利用企業(yè)管理器或T-SQL語句來查看、修改和刪除用戶創(chuàng)建的存儲(chǔ)過程。（1）查看和修改存儲(chǔ)過程在企業(yè)管理器中，展開存儲(chǔ)過程所屬的數(shù)據(jù)庫，單擊“存儲(chǔ)過程”文件夾，在右邊窗格中就可看到所有存儲(chǔ)過程；右擊相應(yīng)的存儲(chǔ)過程名，在彈出的快捷菜單上

21、選擇“屬性”命令，打開存儲(chǔ)過程屬性窗口，出現(xiàn)和創(chuàng)建存儲(chǔ)過程相似的窗口，在中間的文本編輯子窗口中可以查看或修改該存儲(chǔ)過程的文本。1使用企業(yè)管理器創(chuàng)建視圖 （2）刪除存儲(chǔ)過程右擊要?jiǎng)h除的存儲(chǔ)過程名，在彈出的快捷菜單上選擇“刪除”命令，彈出“除去對象”對話框。單擊【顯示相關(guān)性】按鈕可查看該存儲(chǔ)過程所依附的對象，從而了解刪除該存儲(chǔ)過程對數(shù)據(jù)庫的影響。單擊【全部除去】按鈕，即刪除了相應(yīng)的存儲(chǔ)過程。4.6 觸發(fā)器 觸發(fā)器是一種特殊的存儲(chǔ)過程。觸發(fā)器的創(chuàng)建主要用來維護(hù)數(shù)據(jù)表中的數(shù)據(jù)一致性，當(dāng)對數(shù)據(jù)表進(jìn)行插入、刪除、更新等操作時(shí)，觸發(fā)器可自動(dòng)執(zhí)行。 SQL Server提供了兩種類型的觸發(fā)器： （1）AFTE

22、R觸發(fā)器：是在表中數(shù)據(jù)被修改之后才被觸發(fā)，觸發(fā)器對變動(dòng)的數(shù)據(jù)進(jìn)行檢查，如果發(fā)現(xiàn)錯(cuò)誤，將拒絕或回滾變動(dòng)的數(shù)據(jù)。（2）INSTEAD OF觸發(fā)器：在數(shù)據(jù)修改以前被觸發(fā)，并取代修改數(shù)據(jù)的操作，轉(zhuǎn)去執(zhí)行觸發(fā)器定義的操作。4.6.1 創(chuàng)建觸發(fā)器 1使用企業(yè)管理器創(chuàng)建觸發(fā)器 【例4-68】在School數(shù)據(jù)庫中創(chuàng)建一個(gè)簡單的觸發(fā)器tri_StudentInsDel，當(dāng)用戶插入或刪除T_Student表中學(xué)生記錄時(shí)，能自動(dòng)顯示表中的內(nèi)容。1）展開School“數(shù)據(jù)庫/表”文件夾，在右邊窗格中表中，右擊T_Student表，在彈出的快捷菜單上選擇“所有任務(wù)/管理觸發(fā)器”命令，打開觸發(fā)器屬性對話框。 2）系統(tǒng)創(chuàng)

23、建觸發(fā)器語句了CREATE TRIGGER的框架。修改框架中的內(nèi)容為： CREATE TRIGGER tri_StudentInsDel ON dbo.T_Student FOR INSERT, DELETE AS SELECT * FROM T_Student3）單擊【語法檢查】按鈕可以檢查創(chuàng)建觸發(fā)器的T-SQL語句的語法是否正確。4）單擊【確定】按鈕即保存和創(chuàng)建了名稱為“tri_StudentInsDel”的觸發(fā)器。 當(dāng)對表T_Student的數(shù)據(jù)進(jìn)行插入或刪除操作時(shí)，觸發(fā)器“tri_StudentUpdate”將會(huì)自動(dòng)執(zhí)行。使用T-SQL創(chuàng)建觸發(fā)器 觸發(fā)器語法格式： CREATE TRIGGER 觸發(fā)器名ON 表名|視圖名FOR INSERT,UPDATE,DELETEASSQL語句段【例4-69】在School數(shù)據(jù)庫的T_Student表上創(chuàng)建一個(gè)觸發(fā)器tri_StudentCodeUpdate，當(dāng)對學(xué)號(hào)列進(jìn)行修改時(shí)，給出提示信息并取消修改操作。 CREATE TRIGGER tri_StudentCodeUpdateON T