數據庫原理及應用教程（第5版） （微課版）課件 第7章　SQL Server高級應用

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

SELECT@變量名1=值1

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

SET@變量名=值一條SELECT語句，可以同時給多個值賦值，用英文逗號隔開即可；而一條SET語句，只能給一個變量賦值。7.1Transact-SQL編程基礎7.1.2變量及其使用2.局部變量（2）局部變量的賦值例如，以下語句完成對變量的聲明和賦值。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同時賦值7.1Transact-SQL編程基礎7.1.2變量及其使用2.局部變量（2）局部變量的賦值例如，從數據表S中查詢學號為“S7”的學生的學號與姓名，并將查詢到的學號與姓名分別存儲到局部變量@sno和@sn中。DECLARE@snoVARCHAR(10),@snVARCHAR(10)SELECT@sno=SNo,@sn=SNFROMSWHERESNo='S7'7.1Transact-SQL編程基礎7.1.2變量及其使用3.變量值的輸出（1）用SELECT語句輸出SELECT語句除了可以用于表的查詢或給變量賦值外，還可以用于輸出變量或表達式的值，用于向客戶端查詢分析器“結果”窗口中以表格方式輸出信息，其語法格式如下：

SELECT變量名或表達式[，...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語句輸出三個變量@sno、@sn和@score的值7.1Transact-SQL編程基礎7.1.2變量及其使用3.變量值的輸出（2）用PRINT語句輸出PRINT語句的作用向客戶端查詢分析器“消息”窗口中輸出信息，語法格式如下：

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

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

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

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

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

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

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

RETURNscalar_expressionEND7.4用戶自定義函數7.4.1創(chuàng)建標量值函數使用CREATEFUNCTION語句創(chuàng)建標量值函數【例7-20】在數據庫TeachingDB中，創(chuàng)建一個標量值函數IsPrime，判斷一個正整數是否為素數，如果為素數，則函數返回1，否則返回0，待判斷的正整數通過參數傳給函數。USETeachingDBGOCREATEFUNCTIONIsPrime(@nASINT)RETURNSINTASBEGINDECLARE@iINTDECLARE@signINTSET@sign=1SET@i=2WHILE@i<=SQRT(@n)BEGINIF@n%@i=0BEGINSET@sign=0BREAKENDSET@i=@i+1ENDRETURN@signEND7.4用戶自定義函數7.4.2創(chuàng)建內聯表值函數用CREATEFUNCTION語句創(chuàng)建內聯表值函數的語法格式如下：CREATEFUNCTION<function_name>([{@parameter_name[AS]parameter_data_type[=default][READONLY]}[,...n]])RETURNSTABLE[WITHENCRYPTION][AS]RETURN（select_statement）7.4用戶自定義函數7.4.2創(chuàng)建內聯表值函數【例7-21】在數據庫TeachingDB中，創(chuàng)建一個內聯表值函數GetStuNums，該函數的功能是統計并返回學生表S中的各個系的學生人數，要求結果表中含有系名和對應的學生人數。USETeachingDBGOCREATEFUNCTIONGetStuNums()RETURNSTABLEASRETURN(SELECTDeptAS系名,COUNT(Dept)AS人數FROMSGROUPBYDept)7.4用戶自定義函數7.4.3創(chuàng)建多語句表值函數與創(chuàng)建內聯表值函數不同的是，多語句表值函數的函數體中，在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用戶自定義函數7.4.3創(chuàng)建多語句表值函數【例7-22】在數據庫TeachingDB中，創(chuàng)建一個多語句表值函數FindFails，該函數能夠返回指定學號的學生不及格的課程名及成績，學號以參數的形式傳遞到函數。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用戶自定義函數7.4.4調用用戶自定義函數語法格式如下：函數名(參數)在實際使用中，函數的調用可以放在一個表達式中，也可以直接利用SELECT語句直接顯示函數的返回值。7.5觸發(fā)器7.5.1觸發(fā)器概述1.觸發(fā)器的概念觸發(fā)器是一種特殊類型的存儲過程。觸發(fā)器主要有以下優(yōu)點：觸發(fā)器是在某個事件發(fā)生時自動激活而執(zhí)行的。觸發(fā)器可以實現比約束更為復雜的完整性要求。觸發(fā)器可以根據表數據修改前后的狀態(tài)，根據其差異采取相應的措施。觸發(fā)器可以防止惡意的或錯誤的INSERT、UPDATE和DELETE操作。7.5觸發(fā)器7.5.1觸發(fā)器概述2.觸發(fā)器的種類（1）DML觸發(fā)器DML觸發(fā)器是在執(zhí)行數據操縱語言（DML）事件時被激活而自動執(zhí)行的觸發(fā)器。（2）DDL觸發(fā)器DDL觸發(fā)器是在響應各種數據定義語言（DDL）事件而激活執(zhí)行的存儲過程。（3）登錄觸發(fā)器登錄觸發(fā)器是由登錄（LOGON）事件而激活的觸發(fā)器。7.5觸發(fā)器7.5.2觸發(fā)器的工作原理SQLServer在工作時為每個觸發(fā)器在服務器的內存上建立兩個特殊的表：插入表和刪除表。對表的操作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】設計一個觸發(fā)器，該觸發(fā)器的作用為：當在學生表S中刪除某一個學生時，在學生選課表SC中的選課記錄也相應地被全部刪除。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)建一個作用域為數據庫TeachingDB

范圍的DDL觸發(fā)器safety，禁止修改和刪除當前數據庫中的任何表。USETeachingDBGOCREATETRIGGERsafetyONDATABASEFORDROP_TABLE,ALTER_TABLEASPRINT'不能刪除或修改數據庫表！'ROLLBACKGO7.5.3創(chuàng)建觸發(fā)器2．創(chuàng)建DDL觸發(fā)器7.5觸發(fā)器7.5.4查看觸發(fā)器1．查看數據表中創(chuàng)建的觸發(fā)器調用執(zhí)行系統存儲過程sp_helptrigger查看表中觸發(fā)器的語法格式如下：EXECsp_helptrigger'table'[,'type’]【例7-26】查看數據表S中已創(chuàng)建的所有類型的觸發(fā)器。USETeachingDBGOEXECsp_helptrigger'S’GO7.5觸發(fā)器7.5.4查看觸發(fā)器2．查看觸發(fā)器的定義文本觸發(fā)器的定義文本存儲在系統表syscomments中，利用系統存儲過程sp_helptext可查看某個觸發(fā)器的內容，語法格式為：EXECsp_helptext'trigger_name'【例7-27】查看已創(chuàng)建的觸發(fā)器“insert_sc”的內容。USETeachingDBGOEXECsp_helptext'insert_sc’GO7.5觸發(fā)器7.5.4查看觸發(fā)器3．查看觸發(fā)器的所有者和創(chuàng)建時間系統存儲過程sp_help可用于查看觸發(fā)器的所有者和創(chuàng)建日期，語法格式如下：EXECsp_help'trigger_name'【例7-28】查詢已創(chuàng)建的觸發(fā)器“insert_sc”的有關信息。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][,][