第9-10章T-sql編程與應(yīng)用

1、第9章 Transact-SQL 編程基礎(chǔ) T-SQL是微軟對是微軟對SQL的擴(kuò)展的擴(kuò)展,具有具有SQL的主要特點(diǎn)的主要特點(diǎn),同時(shí)增加了變量同時(shí)增加了變量,運(yùn)運(yùn)算符算符,函數(shù)函數(shù),流程控制和注釋等語言元流程控制和注釋等語言元素素,使得其功能更加強(qiáng)大。使得其功能更加強(qiáng)大。 T-SQL語言可直接和數(shù)據(jù)庫引擎進(jìn)行語言可直接和數(shù)據(jù)庫引擎進(jìn)行交互，執(zhí)行效率高。交互，執(zhí)行效率高。 T-SQL是一系列操作命令的集合。是一系列操作命令的集合。其值在程序運(yùn)行過程中始終不變。其值在程序運(yùn)行過程中始終不變。由用戶自己定由用戶自己定義的，作用范圍僅在程序內(nèi)部義的，作用范圍僅在程序內(nèi)部 。定義定義 Declare 變量

2、名變量名使用使用Set 和和Select為變量賦值為變量賦值 是是SQL Server系統(tǒng)內(nèi)系統(tǒng)內(nèi)部提供并賦值的量部提供并賦值的量,由系統(tǒng)定義和維護(hù)由系統(tǒng)定義和維護(hù)。 使用 全局變量名1.常量與變量的使用 (P190)9.2 T-SQL 語言基礎(chǔ)語言基礎(chǔ)connections 返回連接次數(shù)返回連接次數(shù)error 返回上次執(zhí)行語句產(chǎn)生的錯(cuò)誤數(shù)返回上次執(zhí)行語句產(chǎn)生的錯(cuò)誤數(shù)rowcount 返回上一個(gè)語句所處理的行數(shù)返回上一個(gè)語句所處理的行數(shù)servername 返回本地服務(wù)器名返回本地服務(wù)器名version 返回當(dāng)前服務(wù)器的日期、版本和返回當(dāng)前服務(wù)器的日期、版本和處理器類型處理器類型.2、變量、變

3、量的使用的使用l系統(tǒng)變量系統(tǒng)變量例例1：select servername as 本服務(wù)器本服務(wù)器例例2：顯示今天所有試圖連接本地服務(wù)器：顯示今天所有試圖連接本地服務(wù)器的次數(shù)。的次數(shù)。 select left(getdate(),10) as 日期日期, connections as 連接次數(shù)連接次數(shù)l系統(tǒng)變量系統(tǒng)變量例例3：把成績小于把成績小于60分的加分的加5分，如果沒分，如果沒有滿足條件的記錄，顯示有滿足條件的記錄，顯示“沒有滿足條沒有滿足條件記錄件記錄”，否則顯示，否則顯示“操作成功操作成功”。 update sc set degree=degree+5 where degree2 %

4、 b or a=c and bc print true else print false2FTTTT9.3、 流程控制語句（P P201201） 1）begin end 2) IF語句語句語法格式：語法格式： IF ELSE例例: 查詢學(xué)生表，若學(xué)生平均成績大于查詢學(xué)生表，若學(xué)生平均成績大于60則顯示則顯示“平均成績大于等于平均成績大于等于60”，否則顯，否則顯示示“平均成績小于平均成績小于60”。IF(SELECT AVG(成績成績) FROM 成績表成績表 ) = 60 begin PRINT 平均成績大于等于平均成績大于等于 select AVG(成績成績) from 成績表成績表 en

5、dELSE PRINT 平均成績小于平均成績小于在消息欄顯示在消息欄顯示在結(jié)果欄顯示在結(jié)果欄顯示3) Case 語句 (P206) 語法語法1：Case 表達(dá)式表達(dá)式 when 表達(dá)式表達(dá)式1 Then 結(jié)果結(jié)果1 when 表達(dá)式表達(dá)式2 Then 結(jié)果結(jié)果2 Else 語句語句nend當(dāng)當(dāng)“表達(dá)式表達(dá)式”的值的值=“表達(dá)式表達(dá)式1”的值時(shí)返的值時(shí)返回結(jié)果回結(jié)果 “結(jié)果結(jié)果1”. 語法語法2：Case when 布爾表達(dá)式布爾表達(dá)式1 Then 結(jié)果結(jié)果1 when 布爾表達(dá)式布爾表達(dá)式2 Then 結(jié)果結(jié)果2 Else 語句語句nend當(dāng)當(dāng)“布爾表達(dá)式布爾表達(dá)式1”的的值為真，則返回結(jié)果值

6、為真，則返回結(jié)果 “結(jié)果結(jié)果1”.use 成績庫 select 姓名 , 所在系= case 系號 when d001 then 計(jì)科系 when d002 then 生物系 when d003 then 中文系 end from 學(xué)生表例例1：不通過系部表顯示各學(xué)生所屬系。：不通過系部表顯示各學(xué)生所屬系。select 姓名姓名,a.學(xué)號學(xué)號,成績成績,成績等級成績等級= case when 成績成績=60 and 成績成績=70 and 成績成績=80 and 成績成績=90 and 成績成績=100 then 優(yōu)優(yōu) else 成績錄入不對成績錄入不對 endfrom 學(xué)生表學(xué)生表 a, 成

7、績表成績表 b where a.學(xué)號學(xué)號=b.學(xué)號學(xué)號例例2：通過學(xué)生表、成績表顯示學(xué)生成績等級。：通過學(xué)生表、成績表顯示學(xué)生成績等級。4） WHILE語句語句 語法格式如下：語法格式如下：WHILE WHILE BREAK BREAK CONTINUE CONTINUE END ENDCONTINUE語句的作用是讓程序跳過之后的所有語句繼續(xù)執(zhí)行。(見下列)條件為真則條件為真則進(jìn)入循環(huán)進(jìn)入循環(huán)declare num intset num=11while num0 begin set num=num-1 if num=8 begin continue end if num=5 begin bre

8、ak end print numend109761. 批處理分隔符（批處理分隔符（GO語句）語句）2. 使用不同的數(shù)據(jù)庫（使用不同的數(shù)據(jù)庫（USE）3. 執(zhí)行存儲(chǔ)過程（執(zhí)行存儲(chǔ)過程（EXEC）4. 注釋（注釋（- - 或或 /* */）5. 續(xù)行（無需續(xù)行符）續(xù)行（無需續(xù)行符）其它：第10章 數(shù)據(jù)庫服務(wù)器編程內(nèi)容包括10.1 存儲(chǔ)過程存儲(chǔ)過程10.3 自定義函數(shù)自定義函數(shù)10.2 觸發(fā)器觸發(fā)器q存儲(chǔ)過程（procedure）類似于C語言中的函數(shù)q用來執(zhí)行管理任務(wù)或應(yīng)用復(fù)雜的業(yè)務(wù)規(guī)則q存儲(chǔ)過程可以帶參數(shù)，也可以返回結(jié)果int sum(int a,int b) int s; s =a+b; ret

9、urn s ;存儲(chǔ)過程相當(dāng)于C語言中的函數(shù)10.1.1 什么是存儲(chǔ)過程?存儲(chǔ)過程-單個(gè) SELECT 語句SELECT 語句塊SELECT語句與邏輯控制語句可以包含可以包含q存儲(chǔ)過程可以包含數(shù)據(jù)操縱語句、變量、邏輯控制語句等q系統(tǒng)存儲(chǔ)過程系統(tǒng)存儲(chǔ)過程q由系統(tǒng)定義，存放在由系統(tǒng)定義，存放在mastermaster數(shù)據(jù)庫中數(shù)據(jù)庫中q類似類似C C語言中的系統(tǒng)函數(shù)語言中的系統(tǒng)函數(shù)q系統(tǒng)存儲(chǔ)過程的名稱都以系統(tǒng)存儲(chǔ)過程的名稱都以“sp_”sp_”開頭開頭或或”xp_”xp_”開頭開頭q用戶自定義存儲(chǔ)過程用戶自定義存儲(chǔ)過程q由用戶在自己的數(shù)據(jù)庫中創(chuàng)建的存儲(chǔ)過程由用戶在自己的數(shù)據(jù)庫中創(chuàng)建的存儲(chǔ)過程q類似類似

10、C C語言中的用戶自定義函數(shù)語言中的用戶自定義函數(shù)存儲(chǔ)過程的分類: (P216)EXEC sp_databasesEXEC sp_renamedb Northwind,Northwind1EXEC sp_tablesEXEC sp_columns student EXEC sp_help studentEXEC sp_helpconstraint studentEXEC sp_helpindex studentEXEC sp_helptext sc_view1 EXEC sp_stored_procedures 常用的系統(tǒng)存儲(chǔ)過程 修改數(shù)據(jù)庫的名稱(單用戶訪問)列出當(dāng)前系統(tǒng)中的數(shù)據(jù)庫查看表信息

11、返回表中字段信息查看表的摘要信息查看表的約束查看表的索引查看視圖的語句文本查看存儲(chǔ)過程10.1.2 創(chuàng)建和執(zhí)行存儲(chǔ)過程創(chuàng)建和執(zhí)行存儲(chǔ)過程 (P217)語法格式：語法格式：Create procedure 過程名過程名AS SQL 語句語句例例1：use 成績庫成績庫 gocreate proc mylx as select * from 學(xué)生表學(xué)生表 where 性別性別=男男Go Exec mylx執(zhí)行過程執(zhí)行過程CREATE PROCEDURE proc_stu AS DECLARE avgdegree float SELECT avgdegree=AVG(成績) FROM 成績表 pri

12、nt 平均分：+convert(varchar(10),avgdegree) IF (avgdegree70) print 本班考試成績：優(yōu)秀 ELSE print 本班考試成績：較差 print - print 參加本次考試沒有通過的學(xué)員： SELECT * FROM 成績表 WHERE 成績60 GOEXEC proc_stu例例2：創(chuàng)建存儲(chǔ)過程，查看本次考試平均分以及未：創(chuàng)建存儲(chǔ)過程，查看本次考試平均分以及未通過考試的學(xué)員名單通過考試的學(xué)員名單. (不帶參數(shù)例不帶參數(shù)例)創(chuàng)建帶參數(shù)的存儲(chǔ)過程 (P205)q存儲(chǔ)過程的參數(shù)分兩種：int sum (int a, int b) int s;

13、s=a+b; return s;c=sum（5, 8）傳入?yún)?shù)值q 輸入?yún)?shù)： 用于向存儲(chǔ)過程傳入值，類似C語言的按值傳遞;q 輸出參數(shù)： 用于在調(diào)用存儲(chǔ)過程后， 返回結(jié)果，類似C語言的 按引用傳遞; 返回結(jié)果問題：問題：修改前例修改前例2 2：由于每次考試的難易程度不：由于每次考試的難易程度不一樣，每次一樣，每次 考試的及格線考試的及格線可能會(huì)有變化，可能會(huì)有變化，從而考試的評判結(jié)果也相應(yīng)變化。從而考試的評判結(jié)果也相應(yīng)變化。分析：在存儲(chǔ)過程添加一個(gè)輸入?yún)?shù)：degreePass 及格線要求 1、帶輸入?yún)?shù)存儲(chǔ)過程、帶輸入?yún)?shù)存儲(chǔ)過程CREATE PROCEDURE proc_stu degr

14、eePass int AS print - print 參加本次考試沒有通過的學(xué)員：參加本次考試沒有通過的學(xué)員： SELECT * from 成績表成績表 WHERE 成績成績min 使用默認(rèn)值使用默認(rèn)值 （P219）例：創(chuàng)建顯示未通過考試的（不一定是例：創(chuàng)建顯示未通過考試的（不一定是60分分及格）學(xué)生信息。及格）學(xué)生信息。CREATE PROCEDURE proc_bk degreePass int =60 AS print 本次考試沒有通過的學(xué)生：本次考試沒有通過的學(xué)生： SELECT * from 成績表成績表 WHERE 成績成績degreePass GO執(zhí)行：執(zhí)行：EXEC proc

15、_bk2. 帶輸出參數(shù)的存儲(chǔ)過程（P220） 定義存儲(chǔ)過程時(shí)，輸出參數(shù)要跟定義存儲(chǔ)過程時(shí)，輸出參數(shù)要跟 output 執(zhí)行存儲(chǔ)過程時(shí)，接收輸出的參數(shù)要跟執(zhí)行存儲(chǔ)過程時(shí)，接收輸出的參數(shù)要跟output例：創(chuàng)建存儲(chǔ)過程可查詢某學(xué)生的班主任姓名。例：創(chuàng)建存儲(chǔ)過程可查詢某學(xué)生的班主任姓名。Create procedure pro_getteacher 學(xué)生姓名學(xué)生姓名 nvarchar(20)=徐紅徐紅, 班主任班主任 nchar(20) outputAs select 班主任班主任 =B.header from 學(xué)生表學(xué)生表 A,班級表班級表 B where A.班號班號=B.班號班號 and 姓名

16、姓名= 學(xué)生姓名學(xué)生姓名 Declare 教師姓名 nvarchar(20)Exec pro_getteacher 李晨,教師姓名 outputSelect 李晨的班主任是 + 教師姓名 as 結(jié)果練習(xí)： 創(chuàng)建一個(gè)存儲(chǔ)過程，可以查詢指定課程號的平創(chuàng)建一個(gè)存儲(chǔ)過程，可以查詢指定課程號的平均成績。均成績。調(diào)用：調(diào)用：declare pjf realexec zdkcavg c2,pjf outputprint pjfcreate proc zdkcavg(kh char(10),avg real output)as select avg=avg(成績成績) from 成績表成績表 where 課號

17、課號=kh3、存儲(chǔ)過程其它操作（P221）p修改：修改：Alter procedure 過程名過程名 AS SQL 語句語句p重命名：重命名：Sp_rename 原名，新名原名，新名p刪除：刪除：Drop procedure 過程名過程名p查看：查看：Sp_helptext 對象名對象名10.2 觸發(fā)器10.2.1 觸發(fā)器(P222)l觸發(fā)器觸發(fā)器-是一種特殊的存儲(chǔ)過程，是一種特殊的存儲(chǔ)過程，是基于是基于表、視圖、數(shù)據(jù)庫、服務(wù)器而創(chuàng)建的。當(dāng)表、視圖、數(shù)據(jù)庫、服務(wù)器而創(chuàng)建的。當(dāng)滿足一定條件時(shí)由系統(tǒng)滿足一定條件時(shí)由系統(tǒng)自動(dòng)執(zhí)行（觸發(fā)）。自動(dòng)執(zhí)行（觸發(fā)）。n與存儲(chǔ)過程的不同：觸發(fā)器主要是通過事與存儲(chǔ)

18、過程的不同：觸發(fā)器主要是通過事件的觸發(fā)而被執(zhí)行；而存儲(chǔ)過程是通過調(diào)件的觸發(fā)而被執(zhí)行；而存儲(chǔ)過程是通過調(diào)用用（exec）過程名而被執(zhí)行。過程名而被執(zhí)行。 觸發(fā)器的類型觸發(fā)器的類型： DML觸發(fā)器觸發(fā)器-當(dāng)數(shù)據(jù)庫中發(fā)生數(shù)據(jù)當(dāng)數(shù)據(jù)庫中發(fā)生數(shù)據(jù)操作語言時(shí)啟用。（包括：操作語言時(shí)啟用。（包括：insert、 update 、delete語句）語句） DDL觸發(fā)器觸發(fā)器-當(dāng)服務(wù)器或數(shù)據(jù)庫中當(dāng)服務(wù)器或數(shù)據(jù)庫中發(fā)生數(shù)據(jù)定義語言時(shí)啟用。（如發(fā)生數(shù)據(jù)定義語言時(shí)啟用。（如create 、alter 、drop等語句）等語句） 10.2.2 DML10.2.2 DML觸發(fā)器的觸發(fā)器的創(chuàng)建創(chuàng)建 觸發(fā)的觸發(fā)的指令指令分為

19、：分為： Insert、 Update、 Delete 觸發(fā)的觸發(fā)的時(shí)機(jī)時(shí)機(jī)分為：分為： For( After)、 Instead of 注：兩個(gè)特殊的臨時(shí)表Deleted 和Inserted Deleted 表表-用于存儲(chǔ)用于存儲(chǔ) DELETE 和和 UPDATE 語句所影響的行的復(fù)本。在執(zhí)行語句所影響的行的復(fù)本。在執(zhí)行 DELETE 或或 UPDATE 語句時(shí)，行從觸發(fā)語句時(shí)，行從觸發(fā)器表中刪除，傳輸?shù)狡鞅碇袆h除，傳輸?shù)?deleted 表中。表中。 Inserted 表表-用于存儲(chǔ)用于存儲(chǔ) INSERT 和和 UPDATE 語句所影響的行的副本。在一個(gè)語句所影響的行的副本。在一個(gè)插入或更

20、新事務(wù)處理中，新建行被同時(shí)添插入或更新事務(wù)處理中，新建行被同時(shí)添加到加到 inserted 表和觸發(fā)器表中。表和觸發(fā)器表中。 創(chuàng)建觸發(fā)器CREATE TRIGGER trigger_name ON table|view WITH ENCRYPTION AFTER | INSTEAD OF FOR INSERT , UPDATE , DELETE NOT FOR REPLICATION AS sql_statement .n 例例1、創(chuàng)建一觸發(fā)器，當(dāng)在學(xué)生表中插入一記錄、創(chuàng)建一觸發(fā)器，當(dāng)在學(xué)生表中插入一記錄后顯示后顯示“歡迎新同學(xué)！歡迎新同學(xué)！” create trigger inxs on 學(xué)

21、生表學(xué)生表 after insert as print 歡迎新同學(xué)！歡迎新同學(xué)！檢驗(yàn)：檢驗(yàn)：insert into 學(xué)生表學(xué)生表(sno,sname) values (1234,小明小明)例2、創(chuàng)建觸發(fā)器，當(dāng)向成績表插入新記錄時(shí)，如果插入記錄的課程號不在課程表中，則提示“該課程號不存在，不能插入該記錄”。create trigger inser_cj on 學(xué)生表學(xué)生表 for insertas set nocount off declare kch char(5) select kch=課程表課程表.課程號課程號 from 課程表課程表,inserted where 課程表課程表.課程號課程

22、號=inserted.課程號課程號 if kch print 記錄插入成功！記錄插入成功！ else begin print 該課程號不存在，不能插入記錄！該課程號不存在，不能插入記錄！ rollback transaction end操作：操作：insert into sc values(123,c6,90)取消插入取消插入操作操作上例或?yàn)椋篶reate trigger inser_cj on 成績表成績表 for insertas declare kch char(5) select kch=課程號課程號 from inserted if kch in (select 課程號課程號 fro

23、m 課程表課程表) print 記錄插入成功！記錄插入成功！ else begin print 該課程號不存在，不能插入記錄！該課程號不存在，不能插入記錄！ rollback transaction end例3、當(dāng)刪除課程表記錄時(shí)，檢測成績表中是否存在該課程號的記錄，若有則不能刪除。create trigger del_kc on 課程表課程表 for deleteas if (select count(*) from 成績表成績表,deleted where 成績表成績表.課程號課程號=deleted.課程號課程號)0 begin print 該課程號被成績表引用，不可刪除此記錄該課程號被成

24、績表引用，不可刪除此記錄 rollback transaction end else print 記錄已刪除。記錄已刪除。上例還可寫為：上例還可寫為：create trigger del_kc2 on 課程表課程表 for deleteas declare c as char(8) select c=cno from deleted if c in (select 課程號課程號 from 成績表成績表) begin print 該課程被成績表引用，不可刪除該課程被成績表引用，不可刪除 rollback transaction end else print 記錄已刪除。記錄已刪除。例4、創(chuàng)建觸發(fā)

25、器，當(dāng)修改課程表的課程號時(shí)提示“不能修改課程號！”create trigger update_kc on 課程表for updateas if update(課程號)begin print 不能修改課程號 rollback transactionend執(zhí)行：執(zhí)行：update 課程表課程表 set 課程號課程號= c01 where 課程號課程號= c1Instead of 觸發(fā)器-表示不執(zhí)行觸發(fā)SQL語句，而是執(zhí)行觸發(fā)器。例例5：create trigger kc_delete on 課程表課程表instead of deleteas print 不能刪除課程表信息！不能刪除課程表信息！不執(zhí)

26、行Delete而是執(zhí)行AS后語句。習(xí)題：設(shè)計(jì)觸發(fā)器，當(dāng)刪除課程表記錄時(shí)，實(shí)現(xiàn)先刪除成績表中該課程成績記錄再刪除課程表記錄。（也即實(shí)現(xiàn)級聯(lián)刪除）10.2.3 創(chuàng)建DDL觸發(fā)器（P230）create trigger on with encryption as begin . end例：例： 創(chuàng)建觸發(fā)器，防止當(dāng)前數(shù)據(jù)庫中任意表被創(chuàng)建觸發(fā)器，防止當(dāng)前數(shù)據(jù)庫中任意表被刪除。刪除。create trigger safe1 on database for drop_table,alter_tableas begin print 要?jiǎng)h除或修改表，請先禁用safe1觸發(fā)器 rollback end嘗試分別執(zhí)行

27、以下語句，觀察結(jié)果。嘗試分別執(zhí)行以下語句，觀察結(jié)果。1、drop table 系部表系部表 2、alter table 學(xué)生學(xué)生表表 add 政治面貌政治面貌 char(5)l禁用和啟用觸發(fā)器命令：禁用和啟用觸發(fā)器命令： disable trigger safe1 on database enable trigger safe1 on database管理觸發(fā)器的其它命令 (P232)修改修改 ：分：分DML觸發(fā)器和觸發(fā)器和 DDL觸發(fā)器觸發(fā)器 不不同。同。刪除：刪除： drop trigger 觸發(fā)器名觸發(fā)器名10.3 用戶自定義函數(shù)SQL Server 2008用戶自定義函數(shù)用戶自定義函數(shù)v 自定義函數(shù)的基本概念l 用戶自定義函數(shù)是為了實(shí)現(xiàn)某些功能，用戶用戶自定義函數(shù)是為了實(shí)現(xiàn)某些功能，用戶對多個(gè)對多個(gè)T