數(shù)據(jù)庫原理及應(yīng)用教程（第5版） （微課版）課件 第7章　SQL Server高級(jí)應(yīng)用

北京市優(yōu)質(zhì)本科課程教材數(shù)據(jù)庫原理及應(yīng)用教程（第5版）“十二五”普通高等教育本科國(guó)家級(jí)規(guī)劃教材國(guó)家級(jí)一流線上課程配套教材第7章SQLServer高級(jí)應(yīng)用第7章SQLServer高級(jí)應(yīng)用本章目錄Transact-SQL編程基礎(chǔ)01OPTION02OPTIONSQLServer常用內(nèi)置函數(shù)03OPTION存儲(chǔ)過程04OPTION用戶自定義函數(shù)05OPTION觸發(fā)器7.1Transact-SQL編程基礎(chǔ)7.1.1注釋符利用注釋符可以在程序代碼中添加注釋，起說明作用，不影響程序代碼的執(zhí)行結(jié)果。注釋的作用有兩個(gè)第一，對(duì)程序代碼的功能及實(shí)現(xiàn)方式進(jìn)行簡(jiǎn)要的解釋和說明，以便于將來對(duì)程序代碼進(jìn)行維護(hù)；第二，可以把程序中暫時(shí)不用的語句注釋起來，使它們暫時(shí)不被執(zhí)行，等需要這些語句時(shí)，再去掉注釋符將它們恢復(fù)。7.1Transact-SQL編程基礎(chǔ)7.1.1注釋符在Transact-SQL中可以使用兩類注釋符。（1）--：即雙連線字符，用于單行注釋，該符號(hào)到行尾的內(nèi)容都為注釋，既可以用在行首，也可以用在行末。例如，如下代碼中第一行和第二行的末尾是注釋：--這是一行注釋USETeachingDB--打開數(shù)據(jù)庫TeachingDB7.1Transact-SQL編程基礎(chǔ)7.1.1注釋符在Transact-SQL中可以使用兩類注釋符。（2）/*…*/：“/*“用于注釋文字的開頭，“*/“用于注釋文字的結(jié)尾，二者之間的所有內(nèi)容都是注釋，可在程序中標(biāo)識(shí)多行文字為注釋。比如，以下代碼段中的前三行就是一段注釋內(nèi)容，后兩行是程序代碼（每行末含有單行注釋）。/*以下程序代碼的功能是：①打開數(shù)據(jù)庫TeachingDB②從學(xué)生表s中讀取所有的男生信息*/USETeachingDB--打開數(shù)據(jù)庫TeachingDBSELECT*FROMSWHERESex='男'--查詢學(xué)生表S中的所有男生信息7.1Transact-SQL編程基礎(chǔ)7.1.2變量及其使用1.全局變量全局變量是SQLServer系統(tǒng)內(nèi)部創(chuàng)建和使用的變量，其作用范圍并不局限于某一程序，而是任何程序均可隨時(shí)調(diào)用。全局變量通常存儲(chǔ)一些SQLServer的配置設(shè)定值和效能統(tǒng)計(jì)數(shù)據(jù)等。用戶可在程序中用全局變量來測(cè)試系統(tǒng)的設(shè)定值或Transact-SQL命令執(zhí)行后的狀態(tài)值。使用全局變量時(shí)，應(yīng)注意以下幾點(diǎn)：全局變量不是由用戶定義的，而是由SQLServer系統(tǒng)事先定義的。全局變量的值由SQLServer系統(tǒng)自動(dòng)維護(hù)。用戶只能使用系統(tǒng)預(yù)先定義的全局變量。因此，全局變量對(duì)用戶而言是只讀的，用戶只能讀取全局變量的值，而不能對(duì)它們進(jìn)行修改或管理。全局變量名前以“@@”前綴開頭。7.1Transact-SQL編程基礎(chǔ)7.1.2變量及其使用1.全局變量常用的SQLServer全局變量名稱及其值的含義如下表所示。全局變量名變量值的含義@@CONNECTIONS返回自最近一次啟動(dòng)SQLServer以來連接或視圖連接的次數(shù)@@MAX_CONNECTIONS返回SQLServer實(shí)例允許同時(shí)連接的最大用戶連接數(shù)@@MAX_PRECISION返回?cái)?shù)據(jù)庫服務(wù)器中當(dāng)前設(shè)置的DECIMAL和NUMERIC數(shù)據(jù)類型所用的精度@@ERROR返回最后執(zhí)行SQL語句的錯(cuò)誤代碼，如沒有錯(cuò)誤，則返回0@@ROWCOUNT返回上一次語句執(zhí)行所影響的數(shù)據(jù)記錄行數(shù)@@SERVERNAME返回運(yùn)行SQLServer的數(shù)據(jù)庫服務(wù)器名稱@@SERVICENAME返回運(yùn)行SQLServer數(shù)據(jù)庫服務(wù)器實(shí)例名@@VERSION返回SQLServer當(dāng)前安裝的版本、日期、處理器類型和操作系統(tǒng)@@LANGUAGE返回當(dāng)前SQLServer服務(wù)器所使用的語言名7.1Transact-SQL編程基礎(chǔ)7.1.2變量及其使用2.局部變量（1）局部變量的聲明局部變量必須先用DECLARE語句聲明后才可使用，其聲明形式如下：DECLARE@變量名1變量類型及長(zhǎng)度[=初值][，@變量名2變量類型及長(zhǎng)度=初值，…];“@變量名1”是被聲明變量的名稱，命名規(guī)則要符合標(biāo)識(shí)符的規(guī)定，變量名要以“@”為前綴?！白兞款愋汀笔侵副宦暶髯兞康臄?shù)據(jù)類型，它可以是SQLServer支持的除TEXT、NTEXT、IMAGE外的數(shù)據(jù)類型，如：可以是INT、VARCHAR、DATETIME、FLOAT等。“長(zhǎng)度”是變量值的長(zhǎng)度，對(duì)于定長(zhǎng)的數(shù)據(jù)類型，如：INT、REAL、FLOAT等不需要提供長(zhǎng)度的定義；但有些數(shù)據(jù)類型，如CHAR、VARCHAR、NUMERIC、DECIMAL等長(zhǎng)度不固定，為節(jié)省存儲(chǔ)空間，此時(shí)要求提供長(zhǎng)度（甚至小數(shù)位數(shù)），可在數(shù)據(jù)類型后面用英文括號(hào)“()”寫明?！俺踔怠笔强蛇x的，用于給聲明的變量同時(shí)賦初值。如果沒有提供初值，則變量的默認(rèn)值為NULL。一條DECLARE語句，可以同時(shí)定義多個(gè)變量，它們之間用英文逗號(hào)隔開。7.1Transact-SQL編程基礎(chǔ)7.1.2變量及其使用2.局部變量（1）局部變量的聲明DECLARE@idCHAR(8)--聲明一個(gè)長(zhǎng)度為8個(gè)字符的CHAR類型的局部變量@idDECLARE@nameNVARCHAR(20)--聲明一個(gè)長(zhǎng)度為20個(gè)字符的VARCHAR類型的局部變量@nameDECLARE@noINT,@sumNUMERIC(4,1)--同時(shí)聲明兩個(gè)局部變量@so和@s，@no為INT類型，@sum為NUMERIC類型，長(zhǎng)度為4，小數(shù)位數(shù)為1DECLARE@sINT=0,@addrCHAR(20)='default'--聲明局部變量@s和@addr，同時(shí)分別賦初值0和'default'。7.1Transact-SQL編程基礎(chǔ)7.1.2變量及其使用2.局部變量（2）局部變量的賦值為了在程序中使用局部變量的值進(jìn)行運(yùn)算，則必須為其賦值，可以使用SELECT或SET語句給局部變量賦值。其語法如下：

SELECT@變量名1=值1

[,@變量名2=值2,…];或

SET@變量名=值一條SELECT語句，可以同時(shí)給多個(gè)值賦值，用英文逗號(hào)隔開即可；而一條SET語句，只能給一個(gè)變量賦值。7.1Transact-SQL編程基礎(chǔ)7.1.2變量及其使用2.局部變量（2）局部變量的賦值例如，以下語句完成對(duì)變量的聲明和賦值。DECLARE@noINT,@scoreNUMERIC(4,1)--聲明變量@no和@scoreDECLARE@iINT,@jINT--聲明變量@i和@jSET@no=1101--用SET語句將變量@no的值賦為1101SET@score=90--用SET語句將變量@score的值賦為90SELECT@i=0,@j=0--用一條SELECT語句給變量@i和@j同時(shí)賦值7.1Transact-SQL編程基礎(chǔ)7.1.2變量及其使用2.局部變量（2）局部變量的賦值例如，從數(shù)據(jù)表S中查詢學(xué)號(hào)為“S7”的學(xué)生的學(xué)號(hào)與姓名，并將查詢到的學(xué)號(hào)與姓名分別存儲(chǔ)到局部變量@sno和@sn中。DECLARE@snoVARCHAR(10),@snVARCHAR(10)SELECT@sno=SNo,@sn=SNFROMSWHERESNo='S7'7.1Transact-SQL編程基礎(chǔ)7.1.2變量及其使用3.變量值的輸出（1）用SELECT語句輸出SELECT語句除了可以用于表的查詢或給變量賦值外，還可以用于輸出變量或表達(dá)式的值，用于向客戶端查詢分析器“結(jié)果”窗口中以表格方式輸出信息，其語法格式如下：

SELECT變量名或表達(dá)式[，...n]例如，DECLARE@snoVARCHAR(6),@snNVARCHAR(10),@scoreREAL--聲明局部變量SELECT@sno='S10',@sn='吳偉'--用SELECT語句給局部變量@sno和@sn賦值SET@score=85*0.7+95*0.3--用SET語句給局部變量@score賦值SELECT@sno,@sn,@score--用SELECT語句輸出三個(gè)變量@sno、@sn和@score的值7.1Transact-SQL編程基礎(chǔ)7.1.2變量及其使用3.變量值的輸出（2）用PRINT語句輸出PRINT語句的作用向客戶端查詢分析器“消息”窗口中輸出信息，語法格式如下：

PRINT變量名或表達(dá)式變量名或表達(dá)式是被輸出的內(nèi)容，即輸出變量或表達(dá)式的值。一條PRINT語句只能輸出一個(gè)值。例如，DECLARE@sINT--聲明局部變量@sSET@s=5/2--給局部變量@s賦值PRINT@@VERSION--輸出全局變量@@version的值PRINT@s--輸出局部變量@s的值PRINT-25%4--輸出表達(dá)式-25%4的值7.1Transact-SQL編程基礎(chǔ)7.1.3批處理與GO語句1．批處理批處理是被SQLServer服務(wù)器系統(tǒng)作為一個(gè)邏輯單元對(duì)待的Transact-SQL語句組。批處理的特點(diǎn)是，一個(gè)批處理中的所有語句被編譯成一個(gè)執(zhí)行計(jì)劃，作為一個(gè)邏輯整體對(duì)待，從客戶端發(fā)送到SQLServer服務(wù)器一起解析和執(zhí)行。如果其中一條語句不能通過語法分析或發(fā)生編譯錯(cuò)誤，那么該批處理中的任何語句都不被執(zhí)行。7.1Transact-SQL編程基礎(chǔ)7.1.3批處理與GO語句2．GO語句（1）GO語句的語句格式

GOGO語句必須自成一行。GO語句不是Transact-SQL命令，而是由各種SQLServer命令實(shí)用程序（如:ManagementStudio中的"查詢"窗口）識(shí)別的命令。7.1Transact-SQL編程基礎(chǔ)7.1.3批處理與GO語句2．GO語句（2）GO語句的作用用于在程序中將多條Transact-SQL語句進(jìn)行分隔，每?jī)蓚€(gè)GO語句之間的語句組就是一個(gè)批處理單元。如果希望將程序中的語句分為多個(gè)批處理，可使用GO語句。例如：USETeachingDBCREATETABLET3(aINT)GOINSERTINTOT3VALUES(1)INSERTINTOT3VALUES(1,1)INSERTINTOT3VALUES(3)GOSELECT*FROMT3GO7.1Transact-SQL編程基礎(chǔ)7.1.4流程控制語句1.BEGIN…END語句BEGIN…END的語法格式如下：BEGIN<命令行或程序塊>END7.1Transact-SQL編程基礎(chǔ)7.1.4流程控制語句2.IF…ELSE語句IF…ELSE語句的語法格式如下：IF<條件表達(dá)式><命令行或程序塊>[ELSE<命令行或程序塊>]【例7-1】從數(shù)據(jù)庫TeachingDB中的SC數(shù)據(jù)表求出學(xué)號(hào)為S1同學(xué)的平均成績(jī)，如果此平均成績(jī)大于或等于60分，則輸出“Pass!”信息，否則輸出“Fail!”。USETeachingDBGOIF(SELECTAVG(Score)FROMSCWHERESNo='S1')>=60PRINT'Pass!'ELSEPRINT'Fail!'GO7.1Transact-SQL編程基礎(chǔ)7.1.4流程控制語句3.IF[NOT]EXISTS語句IF[NOT]EXISTS語句的語法格式如下：IF[NOT]EXISTS(SELECT子查詢)<命令行或程序塊>[ELSE<命令行或程序塊>]【例7-2】從數(shù)據(jù)庫TeachingDB中的S數(shù)據(jù)表讀取學(xué)號(hào)為S1同學(xué)的數(shù)據(jù)記錄。如果存在，則輸出“存在學(xué)號(hào)為S1的學(xué)生”；否則輸出“不存在學(xué)號(hào)為S1的學(xué)生”。USETeachingDBGODECLARE@messageVARCHAR(255)IFEXISTS(SELECT*FROMSWHERESNo='S1')SET@message='存在學(xué)號(hào)為S1的學(xué)生'ELSESET@message='不存在學(xué)號(hào)為S1的學(xué)生'PRINT@messageGO7.1Transact-SQL編程基礎(chǔ)7.1.4流程控制語句4.CASE語句【例7-3】編寫程序段，從數(shù)據(jù)庫TeachingDB中的學(xué)生表S選取SNo和Sex，如果Sex字段值為“男”，則輸出“M”；如果為“女”則輸出“F”。USETeachingDBGOSELECTSNo,Sex=CASESexWHEN'男'THEN'M'WHEN'女'THEN'F'ENDFROMSGO（1）格式1：CASE<輸入表達(dá)式>WHEN<表達(dá)式1>THEN<結(jié)果1>…WHEN<表達(dá)式n>THEN<結(jié)果n>[ELSE<結(jié)果m>]END7.1Transact-SQL編程基礎(chǔ)7.1.4流程控制語句4.CASE語句【例7-4】編寫程序段，從數(shù)據(jù)庫TeachingDB中的SC表查詢所有同學(xué)選課的成績(jī)情況，凡成績(jī)?yōu)榭盏妮敵觥拔纯肌?，小?0分的輸出“不及格”，60分至70分的輸出“及格”，70分至90分的輸出“良好”，大于或等于90分的輸出“優(yōu)秀”。USETeachingDBGOSELECTSNo,CNo,Score=CASEWHENScoreISNULLTHEN'未考'WHENScore<60THEN'不及格'WHENScore>=60ANDScore<70THEN'及格'WHENScore>=70ANDScore<90THEN'良好'WHENScore>=90THEN'優(yōu)秀'ENDFROMSCGO（2）格式2：CASEWHEN<表達(dá)式1>THEN<結(jié)果1>…WHEN<表達(dá)式n>THEN<結(jié)果n>[ELSE<結(jié)果m>]END7.1Transact-SQL編程基礎(chǔ)7.1.4流程控制語句5．WHILE…CONTINUE…BREAK語句（1）WHILE語句WHILE<條件表達(dá)式>BEGIN<程序塊>END（2）BREAK語句BREAK語句的語法格式：BREAK（2）CONTINUE語句CONTINUE語句的語法格式：CONTINUE7.1Transact-SQL編程基礎(chǔ)7.1.4流程控制語句5．WHILE…CONTINUE…BREAK語句【例7-5】編寫程序段，計(jì)算并輸出1～100之間所有能被11整除的數(shù)的個(gè)數(shù)及它們的總和。DECLARE@sSMALLINT,@iSMALLINT,@numsSMALLINTSET@s=0SET@i=1SET@nums=0WHILE@i<=100BEGINIF@i%11=0BEGINSET@s=@s+@iSET@nums=@nums+1ENDSET@i=@i+1ENDPRINT@numsPRINT@s7.1Transact-SQL編程基礎(chǔ)7.1.4流程控制語句5．WHILE…CONTINUE…BREAK語句【例7-6】編寫程序段，計(jì)算并輸出2～20之間所有的素?cái)?shù)（素?cái)?shù)是除了能被1和它本身整除外，不能被別的自然數(shù)整除的數(shù)）。DECLARE@nINT,@iINTSET@n=2WHILE@n<=20BEGINSET@i=2WHILE@i<=@n-1BEGINIF@n%@i=0BREAKSET@i=@i+1ENDIF@i=@nPRINT@nSET@n=@n+1END7.1Transact-SQL編程基礎(chǔ)7.1.4流程控制語句6．語句標(biāo)簽和GOTO語句（1）語句標(biāo)簽語句標(biāo)簽的作用是標(biāo)記程序中的某個(gè)語句位置，可以在一個(gè)標(biāo)識(shí)符的后面加上冒號(hào)來形成一個(gè)語句標(biāo)簽，寫在某個(gè)程序代碼語句的前面或單獨(dú)形成一行，此時(shí)，該標(biāo)識(shí)符就成為一個(gè)語句標(biāo)簽，語法格式是：標(biāo)識(shí)符:例如：sign:beg:SET@i=0上面的sign和beg都是語句標(biāo)簽。7.1Transact-SQL編程基礎(chǔ)7.1.4流程控制語句6．語句標(biāo)簽和GOTO語句（2）GOTO語句GOTO語句是無條件跳轉(zhuǎn)語句，其語法格式如下：GOTO語句標(biāo)簽當(dāng)程序執(zhí)行到GOTO語句時(shí)，它可使程序的執(zhí)行無條件地跳轉(zhuǎn)到GOTO語句中指定的語句標(biāo)簽位置處，從此位置繼續(xù)執(zhí)行。7.1Transact-SQL編程基礎(chǔ)7.1.4流程控制語句6．語句標(biāo)簽和GOTO語句【例7-7】編寫程序段，使用語句標(biāo)簽和GOTO語句實(shí)現(xiàn)求1+2+3+…+10的總和。DECLARE@sSMALLINT,@iSMALLINTSET@i=1SET@s=0BEG:--語句標(biāo)簽BEGIF@i<=10BEGINSET@s=@s+@iSET@i=@i+1GOTOBEG/*使程序跳轉(zhuǎn)到語句標(biāo)簽為BEG的地方執(zhí)行*/ENDPRINT@s7.1Transact-SQL編程基礎(chǔ)7.1.4流程控制語句7．RETURN語句RETURN語句的語法格式如下：RETURN[表達(dá)式]RETURN語句用于使程序從一個(gè)查詢、存儲(chǔ)過程、函數(shù)或批處理中無條件返回調(diào)用處，其后面的語句不再執(zhí)行。在存儲(chǔ)過程中，用RETURN語句返回值時(shí)，則被返回表達(dá)式的值必須是一個(gè)整數(shù)值，如果未指定返回值，則返回0。在標(biāo)量值函數(shù)中，可用RETURN語句返回一個(gè)標(biāo)量值（不能是TABLE、TEXT、NTEXT、IMAGE和TIMESTAMP等類型）。在表值函數(shù)中，可用RETURN語句返回一個(gè)TABLE類型的值（即數(shù)據(jù)表）。7.2SQLServer常用內(nèi)置函數(shù)7.2.1數(shù)學(xué)函數(shù)函數(shù)類別函數(shù)名及格式功能示例三角函數(shù)SIN(x)返回以弧度表示的x的正弦SIN(0)的值為0COS(x)返回以弧度表示的x的余弦COS(0)的值為1TAN(x)返回以弧度表示的x的正切TAN(3.14159/4)的值為0.999998COT(x)返回以弧度表示的x的余切COT(1)的值為0.642092反三角函數(shù)ASIN(x)返回以x為正弦值的角（弧度）ASIN(0)的值為0ACOS(x)返回以x為余弦值的角（弧度）ACOS(1)的值為0ATAN(x)返回以x為正切值的角（弧度）ATAN(0)的值為0角度弧度轉(zhuǎn)換DEGREES(x)把x從弧度轉(zhuǎn)換為角度DEGREES(1)的值為57RADIANS(x)把x從角度轉(zhuǎn)換為弧度RADIANS(90.0)的值為1.570796指數(shù)函數(shù)EXP(x)返回以e為底的x次冪EXP(1)的值為2.718282POWER(x,y)返回以x為底的y次冪POWER(2,4)的值為16平方函數(shù)SQUARE(x)返回x的平方SQUARE(5)的值為25對(duì)數(shù)函數(shù)LOG(x)返回x的以e為底的自然對(duì)數(shù)值LOG(1)的值為0LOG10(x)返回x的以10為底的常用對(duì)數(shù)值LOG10(10)的值為1平方根函數(shù)SQRT(x)返回x的平方根SQRT(1)的值為1取近似值函數(shù)CEILING(x)返回大于或等于x的最小整數(shù)CEILING(-5.6)的值為-5FLOOR(x)返回小于或等于x的最大整數(shù)FLOOR(-5.2)的值為-6ROUND(x,n)將x四舍五入為指定的小數(shù)位數(shù)nROUND(5.6782,2)的值為5.6800絕對(duì)值函數(shù)ABS(x)返回x的絕對(duì)值A(chǔ)BS(-3.4)的值為3.4符號(hào)函數(shù)SIGN(x)測(cè)試x的正負(fù)號(hào)，返0、1或－1SIGN(-3.4)的值為-1隨機(jī)數(shù)函數(shù)RAND()返回0～1之間的隨機(jī)浮點(diǎn)數(shù)

圓周率函數(shù)PI()返回值為

PI()的值3.141592653589797.2SQLServer常用內(nèi)置函數(shù)7.2.2字符串函數(shù)函數(shù)類別函數(shù)名及格式功能示例字符串轉(zhuǎn)換函數(shù)ASCII(s)返回字符串s的第一個(gè)字符的ASCII碼ASCII('abc')的值為97，即輸出字符'a'的ASCII碼值97CHAR(x)返回以x的值為ASCII碼值的字符CHAR(97)的值字符'a'LOWER(s)將字符串s中的所有字母轉(zhuǎn)換為小寫LOWER('ABCDE123')的值為'abcde123'UPPER(s)將字符串s中的所有字母轉(zhuǎn)換為大寫UPPER('abcd123XYZ')的值為'ABCD123XYZ'STR(x,a,b)把數(shù)值x轉(zhuǎn)換為字符型數(shù)據(jù)，a指定返回的字符串長(zhǎng)度，b指定返回的小數(shù)位數(shù)（下一位四舍五入后再轉(zhuǎn)換）STR(12.5678,6,1)的結(jié)果為'12.6'STR(12.5678,6,3)的結(jié)果為'12.568'去掉字符串兩端的空格符函數(shù)LTRIM(s)去掉字符串s開始處（左端）的空格符LTRIM('CAPITAL')的值為'CAPITAL'RTRIM(s)去掉字符串s結(jié)尾處（右端）的空格符RTRIM('CAPITAL')的值為'CAPITAL'TRIM(s)去掉字符串s兩端的空格符TRIM('CAPITAL')的值為'CAPITAL'求子串函數(shù)LEFT(s,n)從字符串s的左端截取n個(gè)字符形成子串LEFT('首都北京',2)的值為'首都'RIGHT(s,n)從字符串s的右端截取n個(gè)字符組成子串RIGHT('BEIJING',4)的值為'JING'SUBSTRING(s,start,length)從字符串s的start位置開始截取長(zhǎng)度為length的子字符串SUBSTRING('BEIJING',1,3)的值為'BEI'7.2SQLServer常用內(nèi)置函數(shù)7.2.2字符串函數(shù)函數(shù)類別函數(shù)名及格式功能示例字符串比較函數(shù)CHARINDEX(s1,s2)返回字符串s1在字符串s2中出現(xiàn)的位置，若未出現(xiàn)，則返回0值CHARINDEX('BCD','ABCDEFGHI')的結(jié)果為2PATINDEX('%s1%'，s2)返回字符串s1（兩端必須用通配符%括起來）在字符串s2中出現(xiàn)的位置，若未出現(xiàn)，返回0值PATINDEX('%BCD%','ABCDEFGHI')的值為2字符串操作函數(shù)CONCAT(s1,s2...sn)返回字符串s1，s2，…，sn等多個(gè)字符串順序連接形成的一個(gè)字符串CONCAT('首都','北京')的值為'首都北京'LEN(s)返回字符串s中的字符個(gè)數(shù)（即求字符串s的長(zhǎng)度）LEN('北京bj')的值為4QUOTENAME(s,c)返回字符串s用字符c括起來形成的字符串，若c省略，表示用“[]”括起來QUOTENAME('China')的值為[China]QUOTENAME('China','(')的值為(China)REPLACE(s,s1,s2)用字符串s2替換字符串s中的子串s1REPLACE('abbcab','ab','xxx')的值為'xxxbcxxx'REPLICATE(s,n)將字符串s重復(fù)n次，形成新的字符串REPLICATE('北京',2)的值為'北京北京'REVERSE(s)將字符串s的順序反過來REVERSE('abc')的值為'cba'SPACE(n)返回n個(gè)空格所組成的字符串SPACE(5)返回5個(gè)空格符組成的字符串''STUFF(s1,p,n,s2)將字符串s1中從第p個(gè)位置開始的n個(gè)字符替換為字符串s2STUFF('123456789',5,2,'ABCD')的結(jié)果為字符串'1234ABCD789'7.2SQLServer常用內(nèi)置函數(shù)7.2.3數(shù)據(jù)類型轉(zhuǎn)換函數(shù)1.CAST函數(shù)CAST函數(shù)的語法格式如下：CAST(<expression>AS<data_type>[（length）])參數(shù)expression是必選項(xiàng)，表示要進(jìn)行數(shù)據(jù)類型轉(zhuǎn)換的表達(dá)式，也就是要把expression表達(dá)式的值的數(shù)據(jù)類型轉(zhuǎn)換為由參數(shù)data_type所指定的目標(biāo)數(shù)據(jù)類型。AS為關(guān)鍵字。參數(shù)data_type是必選項(xiàng)，是要轉(zhuǎn)換的目標(biāo)數(shù)據(jù)類型，參數(shù)length是可選項(xiàng)，用于指定目標(biāo)數(shù)據(jù)類型的長(zhǎng)度。7.2SQLServer常用內(nèi)置函數(shù)7.2.3數(shù)據(jù)類型轉(zhuǎn)換函數(shù)2.CONVERT函數(shù)CONVERT函數(shù)的語法格式如下：CONVERT(<data_type>[（length）]，<expression>[，style])參數(shù)data_type是必選項(xiàng)，表示數(shù)據(jù)類型，即轉(zhuǎn)換后的目標(biāo)數(shù)據(jù)類型，它可以是SQLServer系統(tǒng)定義的數(shù)據(jù)類型。參數(shù)length是可選項(xiàng)，用于指定轉(zhuǎn)換后的數(shù)據(jù)類型的長(zhǎng)度，省略時(shí)為30。參數(shù)expression是必選項(xiàng)，是要被轉(zhuǎn)換數(shù)據(jù)類型的表達(dá)式，也就是要把expression表達(dá)式的值的數(shù)據(jù)類型轉(zhuǎn)換為由參數(shù)data_type所指定的數(shù)據(jù)類型。參數(shù)style是可選項(xiàng)，專用于將DATATIME和SMALLDATETIME數(shù)據(jù)轉(zhuǎn)換為字符串時(shí)所選用的由SQLServer系統(tǒng)提供的轉(zhuǎn)換樣式編號(hào)，不同的樣式編號(hào)用不同的格式顯示日期和時(shí)間。7.2SQLServer常用內(nèi)置函數(shù)7.2.4日期和時(shí)間函數(shù)日期和時(shí)間函數(shù)主要用于處理DATETIME和SMALLDATETIME類型的日期和時(shí)間數(shù)據(jù)。SQLServer提供的日期和時(shí)間函數(shù)主要有獲取當(dāng)前日期、獲取當(dāng)前時(shí)間、計(jì)算日期的函數(shù)和計(jì)算時(shí)間的函數(shù)等。常用的日期和時(shí)間函數(shù)，以及參數(shù)的含義請(qǐng)參考7.2.4節(jié)的表7-5和表7-6。7.2SQLServer常用內(nèi)置函數(shù)7.2.5系統(tǒng)信息函數(shù)函數(shù)類別函數(shù)名及格式功能獲取數(shù)據(jù)庫編號(hào)DB_ID()以整數(shù)值返回當(dāng)前數(shù)據(jù)庫的編號(hào)獲取數(shù)據(jù)庫名稱DB_NAME()以字符串形式返回當(dāng)前數(shù)據(jù)庫的名稱獲取服務(wù)器計(jì)算機(jī)編號(hào)HOST_ID()以字符串形式返回服務(wù)器端計(jì)算機(jī)標(biāo)識(shí)號(hào)獲取服務(wù)器計(jì)算機(jī)名稱HOST_NAME()以字符串形式返回服務(wù)器端計(jì)算機(jī)的名稱獲取用戶登錄數(shù)據(jù)庫服務(wù)器的登錄名SUSER_NAME()以字符串形式返回用戶登錄數(shù)據(jù)庫服務(wù)器的登錄名獲取用戶在數(shù)據(jù)庫中的名字USER_NAME()以字符串形式返回用戶在當(dāng)前數(shù)據(jù)庫中的名字7.3存儲(chǔ)過程7.3.1存儲(chǔ)過程的概念、優(yōu)缺點(diǎn)及分類1．存儲(chǔ)過程的概念存儲(chǔ)過程（StoredProcedure）是一組完成特定功能的SQL程序代碼段，經(jīng)編譯后存儲(chǔ)在數(shù)據(jù)庫中，可被觸發(fā)器、其他存儲(chǔ)過程、程序設(shè)計(jì)語言等所調(diào)用。每個(gè)存儲(chǔ)過程在定義時(shí)被指定為一個(gè)特定的名稱，即存儲(chǔ)過程的名稱，因此，用戶可通過指定存儲(chǔ)過程的名字并給出參數(shù)（如果該存儲(chǔ)過程帶有參數(shù)）來調(diào)用執(zhí)行指定的存儲(chǔ)過程。存儲(chǔ)過程能夠完成的功能由其過程體中的代碼來決定，當(dāng)存儲(chǔ)過程被調(diào)用執(zhí)行時(shí)，過程體中的代碼將被執(zhí)行，從而完成了相應(yīng)的功能。SQLServer中的存儲(chǔ)過程與其他編譯語言中的過程類似，比如，可以接受輸入?yún)?shù)并以輸出參數(shù)的形式將多個(gè)值返回至調(diào)用處，存儲(chǔ)過程的執(zhí)行能夠完成某個(gè)預(yù)先設(shè)定的功能等。7.3存儲(chǔ)過程7.3.1存儲(chǔ)過程的概念、優(yōu)缺點(diǎn)及分類2．存儲(chǔ)過程的優(yōu)點(diǎn)增強(qiáng)了SQL語言的功能和靈活性。存儲(chǔ)過程中可用流程控制語句對(duì)SQL語句的執(zhí)行進(jìn)行控制，有很強(qiáng)的靈活性，可以實(shí)現(xiàn)復(fù)雜的功能?？杀欢啻沃貜?fù)調(diào)用。創(chuàng)建好的存儲(chǔ)過程被存儲(chǔ)在其隸屬的數(shù)據(jù)庫中，以后可以被多次調(diào)用，而不必重新編寫存儲(chǔ)過程中的代碼。能實(shí)現(xiàn)更快的執(zhí)行速度。存儲(chǔ)過程是預(yù)編譯的，存儲(chǔ)過程在創(chuàng)建時(shí)，SQLServer就對(duì)其進(jìn)行編譯、分析和優(yōu)化，并且給出最終被存儲(chǔ)在系統(tǒng)表中的執(zhí)行計(jì)劃。減少網(wǎng)絡(luò)流量。SQL語句被組織到一個(gè)存儲(chǔ)過程中，客戶端調(diào)用該存儲(chǔ)過程時(shí)，網(wǎng)絡(luò)中傳送的只是調(diào)用語句，不需要在網(wǎng)絡(luò)中傳送這些SQL語句代碼，從而大大降低了網(wǎng)絡(luò)流量?？勺鳛橐环N安全機(jī)制來利用?？稍O(shè)定只有某用戶才具有對(duì)指定存儲(chǔ)過程的使用權(quán)，從而實(shí)現(xiàn)對(duì)相應(yīng)數(shù)據(jù)訪問權(quán)限的限制，避免了非授權(quán)用戶對(duì)數(shù)據(jù)的訪問，保證了數(shù)據(jù)的安全性。7.3存儲(chǔ)過程7.3.1存儲(chǔ)過程的概念、優(yōu)缺點(diǎn)及分類3．存儲(chǔ)過程的缺點(diǎn)移植性差。不同數(shù)據(jù)庫廠商的擴(kuò)展SQL編程語法都不太相同，因此，在某種數(shù)據(jù)庫系統(tǒng)上編寫的存儲(chǔ)過程，難以直接移植到另外一種數(shù)據(jù)庫系統(tǒng)中。難以調(diào)試、維護(hù)。由于數(shù)據(jù)庫系統(tǒng)一般沒有較好的調(diào)試器，很多時(shí)候在編寫和調(diào)試存儲(chǔ)過程的代碼時(shí)，只能使用PRINT語句輸出信息的輔助形式來調(diào)試，當(dāng)存儲(chǔ)過程的功能代碼比較多、功能比較復(fù)雜時(shí)，對(duì)代碼功能的調(diào)試更麻煩。無法應(yīng)用緩存。雖然可以使用全局臨時(shí)表之類的方法可以做數(shù)據(jù)緩存，但這樣加重了數(shù)據(jù)庫服務(wù)器的負(fù)擔(dān)。服務(wù)器不能負(fù)載均衡。由于存儲(chǔ)過程是存儲(chǔ)在數(shù)據(jù)庫服務(wù)器上的數(shù)據(jù)庫中，存儲(chǔ)過程的執(zhí)行全部在服務(wù)器端完成，這就把業(yè)務(wù)處理的負(fù)擔(dān)壓在數(shù)據(jù)庫服務(wù)器上了，無法通過中間層來靈活分擔(dān)負(fù)載和壓力均衡負(fù)載等。7.3存儲(chǔ)過程7.3.1存儲(chǔ)過程的概念、優(yōu)缺點(diǎn)及分類4．存儲(chǔ)過程的分類（1）系統(tǒng)存儲(chǔ)過程常用的系統(tǒng)存儲(chǔ)過程如表所示。存儲(chǔ)過程名稱及語法格式功能sp_helpdb[database_name]顯示參數(shù)database_name所指定的數(shù)據(jù)庫的名稱、大小等信息，如參數(shù)省略，則顯示所有數(shù)據(jù)庫的信息sp_helptext<object_name>顯示參數(shù)object_name所指定的用戶自定義存儲(chǔ)過程、函數(shù)、觸發(fā)器、視圖等對(duì)象的定義代碼內(nèi)容sp_renamedb<dbname>,<newdbname>將參數(shù)dbname所指定的數(shù)據(jù)庫的名稱改為參數(shù)newdbname所表示的名稱sp_helplogins[loginname]查看數(shù)據(jù)庫服務(wù)器的所有登錄名信息或由參數(shù)loginname所指定的登錄名信息sp_helpsrvrolemember查看所有數(shù)據(jù)庫用戶所屬的固定服務(wù)器角色信息7.3存儲(chǔ)過程7.3.1存儲(chǔ)過程的概念、優(yōu)缺點(diǎn)及分類4．存儲(chǔ)過程的分類（2）用戶自定義存儲(chǔ)過程用戶自定義存儲(chǔ)過程是由用戶（程序開發(fā)人員或DBA）在指定數(shù)據(jù)庫中創(chuàng)建并能完成某一特定功能的存儲(chǔ)過程。在本節(jié)中所涉及的存儲(chǔ)過程主要是指用戶自定義存儲(chǔ)過程。（3）擴(kuò)展存儲(chǔ)過程用編程語言（如：C#）創(chuàng)建的以DLL形式獨(dú)立存在的、用以擴(kuò)展SQLServer服務(wù)器功能的存儲(chǔ)過程，其名稱以“xp_”為命名前綴進(jìn)行標(biāo)識(shí)，SQLServer服務(wù)器實(shí)例可以通過動(dòng)態(tài)加載和運(yùn)行這些DLL以完成存儲(chǔ)過程的功能。7.3存儲(chǔ)過程7.3.2創(chuàng)建存儲(chǔ)過程當(dāng)創(chuàng)建存儲(chǔ)過程時(shí)，需要確定存儲(chǔ)過程的幾個(gè)組成部分：（1）指定存儲(chǔ)過程的名稱。（2）所有的輸入?yún)?shù)（包括參數(shù)名及其數(shù)據(jù)類型等）以及傳給調(diào)用者的輸出參數(shù)。（3）被執(zhí)行的針對(duì)數(shù)據(jù)庫的操作語句，包括調(diào)用其他存儲(chǔ)過程的語句。（4）返回給調(diào)用者的狀態(tài)值以指明調(diào)用是成功還是失敗。7.3存儲(chǔ)過程7.3.2創(chuàng)建存儲(chǔ)過程使用CREATEPROCEDURE語句創(chuàng)建存儲(chǔ)過程CREATEPROCEDURE<procedure_name>[{@parameterdata_type}[VARYING][=default][OUTPUT]][,...n][WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]AS[BEGIN]sql_statements[END]7.3存儲(chǔ)過程7.3.2創(chuàng)建存儲(chǔ)過程使用CREATEPROCEDURE語句創(chuàng)建存儲(chǔ)過程【例7-13】定義能夠通過輸出參數(shù)返回值的存儲(chǔ)過程。在TeachingDB數(shù)據(jù)庫中，創(chuàng)建一個(gè)名稱為QueryStudent的存儲(chǔ)過程。該存儲(chǔ)過程的功能是從數(shù)據(jù)表S中根據(jù)學(xué)號(hào)查詢某一同學(xué)的姓名和所在系，要求：待查學(xué)生的學(xué)號(hào)由過程參數(shù)傳入過程，查詢到的學(xué)生姓名和所在系信息通過過程輸出參數(shù)帶出過程。USETeachingDBGOCREATEPROCEDUREQueryStudent@noCHAR(6),@nameNVARCHAR(10)OUTPUT,@dNVARCHAR(20)OUTPUTASBEGINSELECT@name=SN,@d=DeptFROMSWHERESNo=@noENDGO7.3存儲(chǔ)過程7.3.3調(diào)用執(zhí)行存儲(chǔ)過程使用EXECUTE命令調(diào)用執(zhí)行存儲(chǔ)過程EXECUTE{[@return_status=]<procedure_name>[[@parameter=]{value|@variable[OUTPUT]|[DEFAULT]}][,…n][WITHRECOMPILE]}7.3存儲(chǔ)過程7.3.3調(diào)用執(zhí)行存儲(chǔ)過程使用EXECUTE命令調(diào)用執(zhí)行存儲(chǔ)過程【例7-15】調(diào)用執(zhí)行數(shù)據(jù)庫TeachingDB中的帶參數(shù)的存儲(chǔ)過程InsertRecord，調(diào)用時(shí)向存儲(chǔ)過程中傳遞5個(gè)參數(shù)值，存儲(chǔ)過程在執(zhí)行過程中利用這5個(gè)參數(shù)的值組成一條新記錄，并插入到學(xué)生表S中。USETeachingDBGOEXECInsertRecord@sno='S7',@sn='王大利',@sex='男',@age=18,@dept='計(jì)算機(jī)'7.3存儲(chǔ)過程7.3.4存儲(chǔ)過程的參數(shù)形式參數(shù)與實(shí)際參數(shù)（1）形式參數(shù)形式參數(shù)，簡(jiǎn)稱形參，是指在定義一個(gè)存儲(chǔ)過程或函數(shù)（后面章節(jié)介紹）時(shí)，跟在存儲(chǔ)過程名右側(cè)或函數(shù)名右側(cè)括號(hào)內(nèi)的變量名，它們用于接收從外界（調(diào)用處）傳遞給該存儲(chǔ)過程或函數(shù)的數(shù)據(jù)。（2）實(shí)際參數(shù)實(shí)際參數(shù)，簡(jiǎn)稱實(shí)參，是指在調(diào)用存儲(chǔ)過程時(shí)，傳送給被調(diào)存儲(chǔ)過程的常量、變量或表達(dá)式。實(shí)參表可由常量、有效的變量名組成，實(shí)際參數(shù)一定是處在調(diào)用存儲(chǔ)過程中的調(diào)用語句處，位于被調(diào)過程名右側(cè)。一般來説，實(shí)際參數(shù)個(gè)數(shù)、數(shù)據(jù)類型、參數(shù)傳遞類型必須與被調(diào)存儲(chǔ)過程的形式參數(shù)的定義要求保持一致。7.3存儲(chǔ)過程7.3.4存儲(chǔ)過程的參數(shù)具有默認(rèn)值的形參在定義存儲(chǔ)過程時(shí)，在形參表中用“=”為形參提供了默認(rèn)值的形參，稱為具有默認(rèn)值的形參?！纠?-16】調(diào)用執(zhí)行存儲(chǔ)過程InsertRecordDefa，向數(shù)據(jù)表S中插入一條新記錄：（'S9'，'陳昊'，'男'，17）。USETeachingDBGOEXECInsertRecordDefa'S9','陳昊','男',177.3存儲(chǔ)過程7.3.4存儲(chǔ)過程的參數(shù)3.輸出參數(shù)根據(jù)參數(shù)能否從存儲(chǔ)過程返回值，可將定義存儲(chǔ)過程時(shí)的形參分為輸入?yún)?shù)和輸出參數(shù)兩種類型。在存儲(chǔ)過程的定義中由OUTPUT標(biāo)識(shí)的形參為輸出參數(shù)，未標(biāo)有OUTPUT的形參默認(rèn)為輸入?yún)?shù)。輸入?yún)?shù)表示要求在調(diào)用存儲(chǔ)過程時(shí)，必須為該參數(shù)傳入一個(gè)確定的實(shí)參值（或有確定值的表達(dá)式），用于在存儲(chǔ)過程中運(yùn)算使用。輸出參數(shù)表示要求在調(diào)用存儲(chǔ)過程時(shí)，必須為該參數(shù)傳入一個(gè)用戶變量（實(shí)參變量），用于將存儲(chǔ)過程運(yùn)算中的結(jié)果帶出到調(diào)用處使用，該種參數(shù)的功能是將值從存儲(chǔ)過程中帶出，且在實(shí)參表中，對(duì)應(yīng)于輸出參數(shù)的實(shí)參變量后面也必須用OUTPUT進(jìn)行注明。7.3存儲(chǔ)過程7.3.4存儲(chǔ)過程的參數(shù)3.輸出參數(shù)【例7-17】調(diào)用執(zhí)行數(shù)據(jù)庫TeachingDB中的帶輸出參數(shù)的存儲(chǔ)過程QueryStudent，輸出學(xué)號(hào)為“S5”同學(xué)的姓名和所在系名。USETeachingDBGODECLARE@rnNVARCHAR(10),@rdNVARCHAR(20)EXECQueryStudent'S5',@rnOUTPUT,@rdOUTPUTSELECT@rnAS姓名,@rdAS系名7.3存儲(chǔ)過程7.3.5查看存儲(chǔ)過程使用系統(tǒng)存儲(chǔ)過程查看存儲(chǔ)過程（1）查看存儲(chǔ)過程的名稱EXECsp_stored_procedures其中，sp_stored_procedures是SQLServer提供的系統(tǒng)存儲(chǔ)過程名稱。（2）查看存儲(chǔ)過程的定義信息EXECsp_helptext<procedure_name>其中，sp_helptext是SQLServer提供的系統(tǒng)存儲(chǔ)過程的名稱。參數(shù)procedure_name表示待查看代碼信息的用戶自定義存儲(chǔ)過程的名稱。7.3存儲(chǔ)過程7.3.6重命名存儲(chǔ)過程使用系統(tǒng)存儲(chǔ)過程重命名通過調(diào)用執(zhí)行SQLServer提供的系統(tǒng)存儲(chǔ)過程sp_rename對(duì)用戶自定義存儲(chǔ)過程進(jìn)行重命名，其調(diào)用執(zhí)行的語法格式如下：EXECsp_rename<origin_procedure_name>,<new_procedure_name>其中，sp_rename是SQLServer提供的系統(tǒng)存儲(chǔ)過程的名稱。參數(shù)origin_procedure_name和new_procedure_name分別表示被重新命名的存儲(chǔ)過程的原名稱和新名稱。例如，要將存儲(chǔ)過程MyProc的名稱改為MyNewPoc，可執(zhí)行以下語句：EXECsp_rename

MyProc,MyNewPoc7.3存儲(chǔ)過程7.3.7刪除存儲(chǔ)過程使用DROPPROCEDURE語句刪除存儲(chǔ)過程DROPPROCEDURE語句可將一個(gè)或多個(gè)存儲(chǔ)過程或者存儲(chǔ)過程組從當(dāng)前數(shù)據(jù)庫中刪除，其語法格式為：DROPPROCEDURE<procedure_name>[,…n]其中，參數(shù)procedure_name表示要被刪除的存儲(chǔ)過程名稱。。例如，要將存儲(chǔ)過程MyProc的名稱改為MyNewPoc，可執(zhí)行以下語句：EXECsp_rename

MyProc,MyNewPoc7.3存儲(chǔ)過程7.3.8修改存儲(chǔ)過程使用ALTERPROCEDURE命令修改存儲(chǔ)過程修改用CREATEPROCEDURE命令已創(chuàng)建的存儲(chǔ)過程，并且不改變權(quán)限的授予情況以及不影響任何其他的獨(dú)立的存儲(chǔ)過程或觸發(fā)器，常使用ALTERPROCEDURE命令，其語法規(guī)則是：ALTERPROCEDURE<procedure_name>[;number][{@parameterdata_type}[VARYING][=default][OUTPUT]][,...n][WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}][FORREPLICATION]AS

sql_statement[,...n]其中，各參數(shù)和保留字的具體含義請(qǐng)參看CREATEPROCEDURE命令。7.4用戶自定義函數(shù)7.4.1創(chuàng)建標(biāo)量值函數(shù)使用CREATEFUNCTION語句創(chuàng)建標(biāo)量值函數(shù)CREATEFUNCTION<function_name>([{@parameter_name[As]parameter_data_type[=default][READONLY]}[,...n]])RETURNSreturn_data_type[WITHENCRYPTION][AS]BEGINfunction_body

RETURNscalar_expressionEND7.4用戶自定義函數(shù)7.4.1創(chuàng)建標(biāo)量值函數(shù)使用CREATEFUNCTION語句創(chuàng)建標(biāo)量值函數(shù)【例7-20】在數(shù)據(jù)庫TeachingDB中，創(chuàng)建一個(gè)標(biāo)量值函數(shù)IsPrime，判斷一個(gè)正整數(shù)是否為素?cái)?shù)，如果為素?cái)?shù)，則函數(shù)返回1，否則返回0，待判斷的正整數(shù)通過參數(shù)傳給函數(shù)。USETeachingDBGOCREATEFUNCTIONIsPrime(@nASINT)RETURNSINTASBEGINDECLARE@iINTDECLARE@signINTSET@sign=1SET@i=2WHILE@i<=SQRT(@n)BEGINIF@n%@i=0BEGINSET@sign=0BREAKENDSET@i=@i+1ENDRETURN@signEND7.4用戶自定義函數(shù)7.4.2創(chuàng)建內(nèi)聯(lián)表值函數(shù)用CREATEFUNCTION語句創(chuàng)建內(nèi)聯(lián)表值函數(shù)的語法格式如下：CREATEFUNCTION<function_name>([{@parameter_name[AS]parameter_data_type[=default][READONLY]}[,...n]])RETURNSTABLE[WITHENCRYPTION][AS]RETURN（select_statement）7.4用戶自定義函數(shù)7.4.2創(chuàng)建內(nèi)聯(lián)表值函數(shù)【例7-21】在數(shù)據(jù)庫TeachingDB中，創(chuàng)建一個(gè)內(nèi)聯(lián)表值函數(shù)GetStuNums，該函數(shù)的功能是統(tǒng)計(jì)并返回學(xué)生表S中的各個(gè)系的學(xué)生人數(shù)，要求結(jié)果表中含有系名和對(duì)應(yīng)的學(xué)生人數(shù)。USETeachingDBGOCREATEFUNCTIONGetStuNums()RETURNSTABLEASRETURN(SELECTDeptAS系名,COUNT(Dept)AS人數(shù)FROMSGROUPBYDept)7.4用戶自定義函數(shù)7.4.3創(chuàng)建多語句表值函數(shù)與創(chuàng)建內(nèi)聯(lián)表值函數(shù)不同的是，多語句表值函數(shù)的函數(shù)體中，在RETURN語句之前還有其他的Transact-SQL語句，語法格式如下：CREATEFUNCTION<function_name>([{@parameter_name[AS]parameter_data_type[=default][READONLY]}[,...n]])RETURNS@return_variableTABLE<table_type_definition>[WITHENCRYPTION][AS]BEGINfunction_body

RETURNEnd7.4用戶自定義函數(shù)7.4.3創(chuàng)建多語句表值函數(shù)【例7-22】在數(shù)據(jù)庫TeachingDB中，創(chuàng)建一個(gè)多語句表值函數(shù)FindFails，該函數(shù)能夠返回指定學(xué)號(hào)的學(xué)生不及格的課程名及成績(jī)，學(xué)號(hào)以參數(shù)的形式傳遞到函數(shù)。USETeachingDBGOCREATEFUNCTIONFindFails(@student_idCHAR(6))RETURNS@t_scoreTABLE(

CnameNVARCHAR(10),GradeNUMERIC(4,1))ASBEGININSERTINTO@t_scoreSELECTCN,ScoreFROMSC,CWHERESC.CNo=C.CNoandSC.SNo=@student_idandScore<60RETURNEND7.4用戶自定義函數(shù)7.4.4調(diào)用用戶自定義函數(shù)語法格式如下：函數(shù)名(參數(shù))在實(shí)際使用中，函數(shù)的調(diào)用可以放在一個(gè)表達(dá)式中，也可以直接利用SELECT語句直接顯示函數(shù)的返回值。7.5觸發(fā)器7.5.1觸發(fā)器概述1.觸發(fā)器的概念觸發(fā)器是一種特殊類型的存儲(chǔ)過程。觸發(fā)器主要有以下優(yōu)點(diǎn)：觸發(fā)器是在某個(gè)事件發(fā)生時(shí)自動(dòng)激活而執(zhí)行的。觸發(fā)器可以實(shí)現(xiàn)比約束更為復(fù)雜的完整性要求。觸發(fā)器可以根據(jù)表數(shù)據(jù)修改前后的狀態(tài)，根據(jù)其差異采取相應(yīng)的措施。觸發(fā)器可以防止惡意的或錯(cuò)誤的INSERT、UPDATE和DELETE操作。7.5觸發(fā)器7.5.1觸發(fā)器概述2.觸發(fā)器的種類（1）DML觸發(fā)器DML觸發(fā)器是在執(zhí)行數(shù)據(jù)操縱語言（DML）事件時(shí)被激活而自動(dòng)執(zhí)行的觸發(fā)器。（2）DDL觸發(fā)器DDL觸發(fā)器是在響應(yīng)各種數(shù)據(jù)定義語言（DDL）事件而激活執(zhí)行的存儲(chǔ)過程。（3）登錄觸發(fā)器登錄觸發(fā)器是由登錄（LOGON）事件而激活的觸發(fā)器。7.5觸發(fā)器7.5.2觸發(fā)器的工作原理SQLServer在工作時(shí)為每個(gè)觸發(fā)器在服務(wù)器的內(nèi)存上建立兩個(gè)特殊的表：插入表和刪除表。對(duì)表的操作Inserted表Deleted表增加記錄（INSERT）存放增加的記錄無刪除記錄（DELETE）無存放被刪除的記錄修改記錄（UPDATE）存放更新后的記錄存放更新前的記錄7.5觸發(fā)器7.5.2觸發(fā)器的工作原理1.INSERT觸發(fā)器的工作原理7.5觸發(fā)器7.5.2觸發(fā)器的工作原理2．DELETE觸發(fā)器的工作原理7.5觸發(fā)器7.5.2觸發(fā)器的工作原理3．UPDATE觸發(fā)器的工作原理7.5觸發(fā)器7.5.3創(chuàng)建觸發(fā)器1．創(chuàng)建DML觸發(fā)器使用CREATETRIGGER創(chuàng)建DML觸發(fā)器的語法格式為：CREATETRIGGER<trigger_name>ON{table_name|view_name}[WITHENCRYPTION]{FOR|AFTER|INSTEADOF}{[INSERT][,][UPDATE][,][DELETE]}ASsql_statement[;]7.5觸發(fā)器【例7-23】設(shè)計(jì)一個(gè)觸發(fā)器，該觸發(fā)器的作用為：當(dāng)在學(xué)生表S中刪除某一個(gè)學(xué)生時(shí)，在學(xué)生選課表SC中的選課記錄也相應(yīng)地被全部刪除。USETeachingDBGOCREATETRIGGERdel_SONSAFTERDELETEAS DELETEFROMSC WHERESC.SNo

IN(SELECTSNoFROMDELETED)GO7.5.3創(chuàng)建觸發(fā)器1．創(chuàng)建DML觸發(fā)器7.5觸發(fā)器7.5.3創(chuàng)建觸發(fā)器2．創(chuàng)建DDL觸發(fā)器使用CREATETRIGGER創(chuàng)建DDL觸發(fā)器的語法格式為：CREATETRIGGER<trigger_name>ON{ALLSERVER|DATABASE}[WITHENCRYPTION]{FOR|AFTER}{event_type|event_group}[,...n]ASsql_statement[;]7.5觸發(fā)器【例7-25】創(chuàng)建一個(gè)作用域?yàn)閿?shù)據(jù)庫TeachingDB

范圍的DDL觸發(fā)器safety，禁止修改和刪除當(dāng)前數(shù)據(jù)庫中的任何表。USETeachingDBGOCREATETRIGGERsafetyONDATABASEFORDROP_TABLE,ALTER_TABLEASPRINT'不能刪除或修改數(shù)據(jù)庫表！'ROLLBACKGO7.5.3創(chuàng)建觸發(fā)器2．創(chuàng)建DDL觸發(fā)器7.5觸發(fā)器7.5.4查看觸發(fā)器1．查看數(shù)據(jù)表中創(chuàng)建的觸發(fā)器調(diào)用執(zhí)行系統(tǒng)存儲(chǔ)過程sp_helptrigger查看表中觸發(fā)器的語法格式如下：EXECsp_helptrigger'table'[,'type’]【例7-26】查看數(shù)據(jù)表S中已創(chuàng)建的所有類型的觸發(fā)器。USETeachingDBGOEXECsp_helptrigger'S’GO7.5觸發(fā)器7.5.4查看觸發(fā)器2．查看觸發(fā)器的定義文本觸發(fā)器的定義文本存儲(chǔ)在系統(tǒng)表syscomments中，利用系統(tǒng)存儲(chǔ)過程sp_helptext可查看某個(gè)觸發(fā)器的內(nèi)容，語法格式為：EXECsp_helptext'trigger_name'【例7-27】查看已創(chuàng)建的觸發(fā)器“insert_sc”的內(nèi)容。USETeachingDBGOEXECsp_helptext'insert_sc’GO7.5觸發(fā)器7.5.4查看觸發(fā)器3．查看觸發(fā)器的所有者和創(chuàng)建時(shí)間系統(tǒng)存儲(chǔ)過程sp_help可用于查看觸發(fā)器的所有者和創(chuàng)建日期，語法格式如下：EXECsp_help'trigger_name'【例7-28】查詢已創(chuàng)建的觸發(fā)器“insert_sc”的有關(guān)信息。USETeachingDBGOEXECsp_help'insert_sc’GO7.5觸發(fā)器7.5.5修改觸發(fā)器利用ALTERTRIGGER語句修改DML觸發(fā)器ALTERTRIGGER<schema_name.trigger_name>ON(table_name|view_name)[WITHENCRYPTION]{FOR|AFTER|INSTEADOF}{[DELETE][,][INSERT][,][