第

9

章

MSSQLServer

數(shù)據(jù)庫技術(shù)本章主要內(nèi)容：（1）SQLServer概述（2）SQLServer中，SQL語句的使用（4）掌握SQLServer存儲過程的編程（5）掌握SQLServer觸發(fā)器的編程（6）SQLServer設(shè)置每天定時(shí)自動做數(shù)據(jù)庫備份（3）SQLServer編程基礎(chǔ)，包括游標(biāo)對象Cursor的使用9.1SQLServer概述

9.1.1SQLServer的發(fā)展歷程1998年12月，SQLServer7.0正式上市。2000年8月，SQLServer2000發(fā)布，其中包括企業(yè)版、標(biāo)準(zhǔn)版、開發(fā)版、個(gè)人版四個(gè)版本。2008年，SQL

Server

2008正式發(fā)布。2010年4月，又發(fā)布了SQL

Server

2008的改進(jìn)版SQL

Server

2008R2。2017年10月，微軟新一代數(shù)據(jù)庫產(chǎn)品SQLServer2017發(fā)布。2019年11月，微軟發(fā)布了新一代數(shù)據(jù)庫產(chǎn)品SQLServer2019。9.1.2

SQLServer的下載和安裝

SQLServer2019有5個(gè)版本可供選擇：Enterprise（企業(yè)版）、Standard（標(biāo)準(zhǔn)版）、Web（網(wǎng)站版）、Develpoer（開發(fā)版）、Express（精簡版），其中后2個(gè)版本免費(fèi)。雙擊下載的安裝文件：SQL2019-SSEI-Dev.exe，進(jìn)入安裝，如圖9-2所示。9.1.2

SQLServer的下載和安裝9.1.2

SQLServer的下載和安裝SQLServerManagementStudio是SQLServer可視化集成管理工作室，這個(gè)必須安裝。9.1.3SQLServer的設(shè)置SQLServer2019安裝成功后，還要進(jìn)行2個(gè)設(shè)置，才能開始使用，一是啟用TCP/IP，二是設(shè)置SQLServer服務(wù)器驗(yàn)證方式為雙身份驗(yàn)證模式，并設(shè)置系統(tǒng)管理員sa的密碼。1.啟用TCP/IP方法：點(diǎn)擊電腦左下角，找到“MicrosoftSQLServer2019”，點(diǎn)擊：“SQLServer2019配置管理器”，進(jìn)入：SQLServerConfigurationManager。如圖9-7、9-8所示。9.1.3SQLServer的設(shè)置1.啟用TCP/IP9.1.3SQLServer的設(shè)置2.設(shè)置SQLServer服務(wù)器驗(yàn)證方式為雙身份驗(yàn)證方法：點(diǎn)擊電腦左下角，找到“MicrosoftSQLServerManagementStudio”，點(diǎn)擊進(jìn)入。如圖9-9（a）、（b）所示。9.1.3SQLServer的設(shè)置設(shè)置SQLServer服務(wù)器驗(yàn)證方式，如圖9-10（a）、（b）、（c）所示。9.1.3SQLServer的設(shè)置設(shè)置SQLServer服務(wù)器驗(yàn)證方式，如圖9-10（a）、（b）、（c）所示。9.1.3SQLServer的設(shè)置設(shè)置SQLServer服務(wù)器驗(yàn)證方式，如圖9-10（a）、（b）、（c）所示。9.1.3SQLServer的設(shè)置3.設(shè)置系統(tǒng)管理員sa的密碼9.1.3SQLServer的設(shè)置3.設(shè)置系統(tǒng)管理員sa的密碼9.1.3SQLServer的設(shè)置到此為止，SQLServer的安裝及設(shè)置已經(jīng)全部完成。要使設(shè)置生效，必須重啟SQLServer服務(wù)，或重啟電腦。

如果不想重啟電腦，那么通過下列方法，也可重啟SQLServer服務(wù)：

點(diǎn)擊電腦左下角，找到“SQLServer2019配置管理器”，點(diǎn)擊進(jìn)入：SQLServerConfigurationManager。如圖9-13所示。

點(diǎn)擊圖9-13中的屬性，可以看到連接SQLServer的各種IP及端口號。9.1.3SQLServer的設(shè)置退出SSMS后，重新進(jìn)入SSMS，就可以用設(shè)置的sa密碼，進(jìn)行登錄。9.1.4SQLServer的核心進(jìn)程

SQLServer2019安裝成功后，有一個(gè)核心程序：sqlservr.exe，默認(rèn)位于“C:\ProgramFiles\MicrosoftSQLServer\MSSQL15.MSSQLSERVER\MSSQL\Binn”路徑下。該程序的啟動模式必須設(shè)定為“自動”。9.1.5SQLServer的系統(tǒng)數(shù)據(jù)庫

系統(tǒng)數(shù)據(jù)庫是指SQLServer安裝時(shí)自帶的數(shù)據(jù)庫，它們存儲了SQLServer相關(guān)的系統(tǒng)信息，主要協(xié)助SQLServer各方面的數(shù)據(jù)管理。SQLServer安裝時(shí)，帶有四個(gè)系統(tǒng)數(shù)據(jù)庫：

（1）master數(shù)據(jù)庫：記錄SQLServer系統(tǒng)的所有系統(tǒng)級別信息，包括系統(tǒng)的登錄賬戶、系統(tǒng)配置設(shè)置、數(shù)據(jù)庫文件的位置和SQLServer初始化信息。（2）model數(shù)據(jù)庫：用于在SQLServer實(shí)例上創(chuàng)建用戶數(shù)據(jù)庫的模板。當(dāng)發(fā)出Createdatabase語句時(shí)新數(shù)據(jù)庫的一部分通過復(fù)制model數(shù)據(jù)庫中的內(nèi)容創(chuàng)建，剩余部分有空頁填充。（3）tempdb數(shù)據(jù)庫：保存所有的臨時(shí)表和臨時(shí)存儲過程，包括其他臨時(shí)存儲要求。

（4）msdb數(shù)據(jù)庫：供SQLServer代理程序調(diào)度報(bào)警、作業(yè)時(shí)使用。9.1.6SQLServer的數(shù)據(jù)庫文件和日志文件SQLServer2019的每個(gè)數(shù)據(jù)庫對應(yīng)有3種類型的數(shù)據(jù)庫文件。

（1）主數(shù)據(jù)文件。該文件是數(shù)據(jù)庫的起點(diǎn)，指向數(shù)據(jù)庫中文件的其他部分。每個(gè)數(shù)據(jù)庫都有一個(gè)主數(shù)據(jù)庫文件，默認(rèn)文件名為：數(shù)據(jù)庫名.mdf。（2）次數(shù)據(jù)文件。包含除主數(shù)據(jù)文件以外的所有數(shù)據(jù)文件。有的數(shù)據(jù)庫可能沒有次數(shù)據(jù)文件，有的可能有多個(gè)。其默認(rèn)文件擴(kuò)展名為：*.ndf。（3）日志文件。包含恢復(fù)數(shù)據(jù)庫所需的所有日志信息。每個(gè)數(shù)據(jù)庫至少有一個(gè)日志文件，有的可能有多個(gè)。其默認(rèn)文件擴(kuò)展名為：*.ldf。9.1.6SQLServer的數(shù)據(jù)庫文件和日志文件這些數(shù)據(jù)庫文件，默認(rèn)路徑：

C:\ProgramFiles\MicrosoftSQLServer\MSSQL15.MSSQLSERVER\MSSQL\DATA

9.2SQLServer中，SQL語句的使用本章主要內(nèi)容：（1）SQLServer概述（2）SQLServer中，SQL語句的使用（4）掌握SQLServer存儲過程的編程（5）掌握SQLServer觸發(fā)器的編程（6）SQLServer設(shè)置每天定時(shí)自動做數(shù)據(jù)庫備份（3）SQLServer編程基礎(chǔ)，包括游標(biāo)對象Cursor的使用9.2.1創(chuàng)建用戶數(shù)據(jù)庫在SSMS中，創(chuàng)建一個(gè)數(shù)據(jù)庫有兩種方法，一是利用可視化界面，一是利用SQL語句。

一個(gè)數(shù)據(jù)庫被創(chuàng)建后，會在系統(tǒng)默認(rèn)的數(shù)據(jù)路徑（或指定路徑）下，生成兩個(gè)數(shù)據(jù)庫物理文件：一個(gè)是主數(shù)據(jù)文件，擴(kuò)展名為*.mdf，一個(gè)是日志文件，擴(kuò)展名為*.ldf。

1.利用可視化界面創(chuàng)建數(shù)據(jù)庫9.2.1創(chuàng)建用戶數(shù)據(jù)庫

1.利用可視化界面創(chuàng)建數(shù)據(jù)庫9.2.1創(chuàng)建用戶數(shù)據(jù)庫

1.利用可視化界面創(chuàng)建數(shù)據(jù)庫9.2.1創(chuàng)建用戶數(shù)據(jù)庫

2.利用SQL語句創(chuàng)建數(shù)據(jù)庫12345678910111213141516CreateDatabasemyTeston/*----------------------創(chuàng)建數(shù)據(jù)庫-----------------*/( name=myTest_data,/*數(shù)據(jù)邏輯文件名*/

FileName='d:\data\myTest_data.mdf',/*數(shù)據(jù)物理文件名*/ Size=10MB,/*初始數(shù)據(jù)文件大小*/

Maxsize=200MB,/*數(shù)據(jù)物理文件最大限制*/

FileGrowth=5/*按5%增長*/)Logon( name=myTest_log,/*日志邏輯文件名*/

FileName='d:\data\myTest_log.ldf',/*日志物理文件名*/ Size=5MB,/*初始日志文件大小*/

Maxsize=25MB,/*日志物理文件最大限制*/

FileGrowth=5MB/*按5MB增長*/);9.2.1創(chuàng)建用戶數(shù)據(jù)庫

2.利用SQL語句創(chuàng)建數(shù)據(jù)庫9.2.1創(chuàng)建用戶數(shù)據(jù)庫

用戶數(shù)據(jù)庫創(chuàng)建成功后，可以在對應(yīng)的路徑下，看到生成的數(shù)據(jù)庫物理文件，如圖9-21所示。9.2.2創(chuàng)建基本表例9-2根據(jù)例3-2中的教學(xué)、借（還）書數(shù)據(jù)庫的關(guān)系模式圖，在student數(shù)據(jù)庫下，創(chuàng)建對應(yīng)的基本表，如圖9-22所示。9.2.3數(shù)據(jù)更新（Insert、Update、Delete）和數(shù)據(jù)查詢（Select）例9-3根據(jù)例9-2的表結(jié)構(gòu)，利用本章數(shù)據(jù)附件“例9-3insertSQL.txt”提供的插入語句，將其數(shù)據(jù)插入到student數(shù)據(jù)庫中。下面是部分Insert語句。12345678910111213insertintoT(tNo,tName,title)values('T21','李老師','教授');insertintoT(tNo,tName,title)values('T22','王老師','副教授');insertintoT(tNo,tName,title)values(‘T23’,‘劉老師’,‘講師’);insertintoC(cNo,cName,tNo)values('C21','MATHS','T21');insertintoC(cNo,cName,tNo)values('C22','高等代數(shù)','T22');insertintoC(cNo,cName,tNo)values('C23','DataStructure','T23');insertintoC(cNo,cName,tNo)values('C24','離散數(shù)學(xué)','T23');insertintoC(cNo,cName,tNo)values('C25','DataBase','T24');insertintoC(cNo,cName,tNo)values(‘C26’,‘C語言程序設(shè)計(jì)’,‘T24’);insertintoS(SNo,SName,Age,Sex,dtBirthDate)values('01071101','張三',22,'M','1982-09-05');insertintoS(SNo,SName,Age,Sex,dtBirthDate)values('01071102','李麗',21,'F','1983-03-06');insertintoSC(SNo,cNo,Score)values('01071101','C22',90);9.2.4SQLServer數(shù)據(jù)類型表9-1SQLServer常用數(shù)據(jù)類型序號類別數(shù)據(jù)類型1整數(shù)型tinyint、smallint、int、integer、bigint、bit2定長浮點(diǎn)型numeric(p,d)、decimal(p,d)3單精度浮點(diǎn)型float(n)4雙精度浮點(diǎn)型real5貨幣型smallmoney、money6字符型char(n)、varchar(n)、text7雙字節(jié)字符型nchar(n)、nvarchar(n)、ntext8二進(jìn)制型binary、varbinary、image9日期型date、datetime9.2.5SQLServer常用內(nèi)置函數(shù)（1）聚合函數(shù)。SQLServer2019增加了2個(gè)統(tǒng)計(jì)函數(shù)。序號函數(shù)名功能說明1count（*）返回記錄數(shù)2Avg（列名）返回列名取值的平均值，其中列名必須為數(shù)值型，空值（null）被忽略3max（列名）返回列名取值的最大值，空值（null）被忽略4min（列名）返回列名取值的最小值，空值（null）被忽略5sum（列名）返回列名取值的和，其中列名必須為數(shù)值型，空值（null）被忽略6Var（列名）返回列名取值的統(tǒng)計(jì)方差，其中列名必須為數(shù)值型，空值（null）被忽略7Stdev（列名）返回列名取值的標(biāo)準(zhǔn)差，其中列名必須為數(shù)值型，空值（null）被忽略9.2.5SQLServer常用內(nèi)置函數(shù)（2）字符函數(shù)。SQLServer常用字符函數(shù)。序號函數(shù)名功能說明示例1len（s）返回字符串s在Unicode下的長度，含左邊的空格，但不含右邊的空格selectlen('中國ok')：返回4selectlen('中國ok')

：返回52datalength(s)返回字符串s的字節(jié)串長度selectdatalength('中國ok')：返回63charindex(c,s)返回字符c在字符串s的起始位置selectcharindex('b','abc,ab')

：返回24ltrim（s）刪除字符串s左邊的空格selectltrim('abc')：返回'abc'5rtrim（s）刪除字符串s右邊的空格selectrtrim('abc')：返回'abc'6str（x）將數(shù)值型x轉(zhuǎn)為字符串，前導(dǎo)有空格selectltrim(str(123.56))：返回‘123’7left（s,m）返回字符串s的左邊m個(gè)字符selectleft('abcd',2)

：返回'ab'8right（s,m）返回字符串s的右邊m個(gè)字符selectright('abcd',2)

：返回'cd'9substring（s,m,n）從字符串s的左邊第m個(gè)字符開始，取n個(gè)字符selectsubstring('abcd',2,2)

：返回'bc'9.2.5SQLServer常用內(nèi)置函數(shù)（3）日期函數(shù)。序號函數(shù)名功能說明示例1getdate（）以SQLServer標(biāo)準(zhǔn)內(nèi)部格式，返回系統(tǒng)當(dāng)前的日期、時(shí)間selectgetdate()：返回2021-06-2307:45:21.8772dateAdd（f,n,d）按指定時(shí)間單位f，對日期d增加n間隔后的datetime值selectdateadd(day,2,getdate())selectdateadd(month,2,'2021-06-08')selectdateadd(year,-2,'2021-06-08')3date（d）返回日期d的“日”部分的整數(shù)selectdate('2021-06-08')：返回84month（d）返回日期d的“月”部分的整數(shù)selectmonth('2021-06-08')：返回65year（d）返回日期d的“年”部分的整數(shù)selectyear('2021-06-08')：返回20216dateDiff(f,d1,d2)按指定時(shí)間單位f，返回從日期d1到d2間隔的數(shù)值selectdatediff(day,'2021-05-08','2021-06-08')selectdatediff(month,'2021-05-30','2021-06-01')selectdatediff(year,'2021-05-30','2021-06-01')9.2.5SQLServer常用內(nèi)置函數(shù)例如，在SSMS，輸入上面的部分示例語句，結(jié)果如圖9-23所示。9.2.5SQLServer常用內(nèi)置函數(shù)（4）系統(tǒng)函數(shù)。SQLServer常用系統(tǒng)函數(shù)。序號函數(shù)名功能說明示例1convert（type(n),f）按指定的數(shù)據(jù)類型（含長度）、格式f，返回一個(gè)值selectconvert(varchar(6),123.56)selectconvert(char(10),getdate(),120)selectconvert(char(10),getdate(),108)2Host_name（）返回?cái)?shù)據(jù)庫所在計(jì)算機(jī)名selectHOST_name()3System_user返回當(dāng)前登錄數(shù)據(jù)庫的用戶名selectsystem_user9.2.5SQLServer常用內(nèi)置函數(shù)例如，在SSMS，輸入上面的部分示例語句，結(jié)果如圖9-24所示。

9.3SQLServer后臺編程基礎(chǔ)本章主要內(nèi)容：（1）SQLServer概述（2）SQLServer中，SQL語句的使用（4）掌握SQLServer存儲過程的編程（5）掌握SQLServer觸發(fā)器的編程（6）SQLServer設(shè)置每天定時(shí)自動做數(shù)據(jù)庫備份（3）SQLServer編程基礎(chǔ)，包括游標(biāo)對象Cursor的使用

9.3SQLServer后臺編程基礎(chǔ)數(shù)據(jù)庫端程序，主要指存儲過程、自定義函數(shù)和觸發(fā)器。一些比較復(fù)雜的邏輯處理程序放置在后臺處理，可以大大提高數(shù)據(jù)處理效率。

數(shù)據(jù)庫端處理程序，一般稱為Transaction-SQL（事務(wù)性-SQL，簡稱T-SQL），加入了很多高級語言的元素，大大豐富了SQL語言的表現(xiàn)能力。9.3.1變量及賦值1.局部變量用戶在程序中自己定義的變量，稱為局部變量，聲明時(shí)必須以@作為前綴。局部變量的作用范圍僅限于用戶定義它的那個(gè)程序塊。

9.3.1變量及賦值局部變量必須先聲明，再賦值。其聲明的語法為：

Declare@變量名

數(shù)據(jù)類型[=初始值]/*聲明變量時(shí)，可以指定初始值*/局部變量的賦值，有3種方法：（1）定義時(shí)直接初始化。下面語句聲明了一個(gè)定長字符串變量，初始值為’0’：12

Declare@flagchar(1)='0'/*聲明一個(gè)定長字符串變量*/print@flag/*打印局部變量的值*/（2）用Set語句給變量賦值。例如，下面語句聲明了一個(gè)整數(shù)變量，賦值為8：12

Declare@xint/*聲明一個(gè)整數(shù)變量*/Set@x=8/*給局部變量賦值*/

9.3.1變量及賦值（3）用Select語句賦值。該語句來自于查詢記錄的結(jié)果集，其中，結(jié)果集中的記錄數(shù)必須大于或等于1。如果記錄數(shù)大于1，則取最后一條記錄的值。如果記錄數(shù)為0，則返回空值（null）。例如，下列語句第5行，select取最后一個(gè)記錄值，而第8行，則為空。123456789

usestudent/*將student設(shè)置為當(dāng)前數(shù)據(jù)庫*/declare@sNochar(8)/*局部變量：學(xué)號*/declare@sNo1char(8)/*局部變量：學(xué)號*/declare@sName

varchar(30)/*局部變量：姓名*/select@sNo=sNo,@sName=ltrim(sName)fromswheresex='M'print'學(xué)號：'+@sNo/*輸出：學(xué)號：05071137*/print'姓名：'+@sName/*輸出：姓名：劉勇*/select@sNo1=sNofromswhere1=2/*沒有記錄*/printisnull(@sNo1,'空')/*輸出：空*/

9.3.1變量及賦值在SSMS中，輸入上述代碼

9.3.1變量及賦值

2.全局變量在SQLServer中，前綴用@@標(biāo)識的變量，稱為全局變量，也稱系統(tǒng)變量。全局變量由系統(tǒng)定義和維護(hù)，用戶不能定義全局變量，也不能修改全局變量的值，只能讀取。全局變量記錄了SQLServer服務(wù)器的活動狀態(tài)、系統(tǒng)設(shè)置，及SQL語句的執(zhí)行狀態(tài)等。常見的全局變量，如表9-6所示。

9.3.1變量及賦值表9-6SQLServer常用全局變量序號全局變量名含義1@@error返回上一條T-SQL語句的錯(cuò)誤號，如果上一條T-SQL語句執(zhí)行沒有錯(cuò)誤，則返回02@@MAX_CONNECTIONS返回SQLServer實(shí)例允許同時(shí)進(jìn)行的最大用戶連接數(shù)。3@@rowcount返回上一條SQL語句影響的行數(shù)4@@SERVERNAME返回運(yùn)行SQLServer的本地服務(wù)器的名稱5@@VERSION返回當(dāng)前的SQLServer安裝的版本、處理器體系結(jié)構(gòu)、生成日期和操作系統(tǒng)6@@cursor_rows返回當(dāng)前打開的游標(biāo)所含記錄的條數(shù)9.3.2T-SQL語句中的程序控制1.If條件分支結(jié)構(gòu)和Case多分支結(jié)構(gòu)（1）If單分支選擇結(jié)構(gòu)（3）case-end多分支選擇結(jié)構(gòu)If條件表達(dá)式begin

語句塊endCasewhen條件1then結(jié)果1when條件2then結(jié)果2…else其他結(jié)果End（2）If-else雙分支選擇結(jié)構(gòu)If條件表達(dá)式begin

語句塊1endElsebegin

語句塊2end9.3.2T-SQL語句中的程序控制

2.While循環(huán)在While循環(huán)中，先對指定的表達(dá)式進(jìn)行判斷，如果為真，則執(zhí)行循環(huán)體內(nèi)的語句，否則退出循環(huán)。語法結(jié)構(gòu)如下：

While條件表達(dá)式Begin

語句塊[break]

語句塊 [continue]End9.3.3T-SQL語句中的事務(wù)與意外處理在T-SQL語句塊中，可以顯示定義一個(gè)事務(wù)，一般通過SQL語句執(zhí)行時(shí)的全局變量的取值，捕獲意外。如果沒有發(fā)生意外，就提交事務(wù)，否則，就回滾。其語法結(jié)構(gòu)如下：

BeginTransaction--開始事務(wù)

語句塊If@@error=0/*全局變量@@error=0，表示沒有發(fā)生意外*/CommitTransaction--提交事務(wù)elseRollbackTransaction--回滾事務(wù)9.3.4T-SQL語句中的游標(biāo)（Cursor）在T-SQL語句塊中，如果一條查詢語句有多條返回記錄，而要逐條進(jìn)行處理，這就是游標(biāo)（cursor）。Declare游標(biāo)名ScrollCursorForSelect語句/*定義游標(biāo)*/Open游標(biāo)名/*打開游標(biāo)*/Select@row=@@cursor_rows/*獲取游標(biāo)中，記錄的行數(shù)*/While@row>0BeginFetchNextfrom游標(biāo)名into局部變量/*獲取游標(biāo)當(dāng)前記錄的值*/SQL

語句塊set@row=@row–1/*給表達(dá)式賦值*/EndClose游標(biāo)名/*關(guān)閉游標(biāo)*/Deallocate游標(biāo)名/*釋放游標(biāo)*/游標(biāo)的操作分4步：聲明游標(biāo)變量、打開游標(biāo)、使用和關(guān)閉游標(biāo)。9.3.4T-SQL語句中的游標(biāo)（Cursor）例9-4編寫一段程序，定義一個(gè)游標(biāo)，查詢學(xué)生的學(xué)號、姓名，并按性別不同，分男、女輸出學(xué)號、姓名。12345usestudent/*將student設(shè)置為當(dāng)前數(shù)據(jù)庫*/declare@sNochar(8)/*局部變量：學(xué)號*/declare@sName

varchar(30)/*局部變量：姓名*/declare@sexchar(1)/*局部變量：性別*/declare@rowint=0/*聲明局部變量，初始值為0，表示游標(biāo)中記錄的行數(shù)*/例9-4編寫一段程序，定義一個(gè)游標(biāo)67891011121314151617181920declarecur_tempscrollcursorforselectsNo,sName,sexfroms/*定義游標(biāo)*/opencur_temp/*打開游標(biāo)*/select@row=@@cursor_rows/*獲取游標(biāo)中記錄的行數(shù)（全局變量）*/while@row>0/*若行數(shù)計(jì)數(shù)器大于0*/begin/*------------------------開始循環(huán)-------------------*/fetchnextfromcur_tempinto@sNo,@sName,@sex/*獲取游標(biāo)當(dāng)前記錄的值*/if@sex=‘M’print@sNo+','+@sName+',男'/*打?。簩W(xué)號，姓名，性別*/elseprint@sNo+','+@sName+',女'set@row=@row-1/*計(jì)數(shù)器減1*/end/*--------------------------結(jié)束循環(huán)-------------------*/closecur_temp/*關(guān)閉游標(biāo)*/Deallocatecur_temp/*從內(nèi)存釋放游標(biāo)*/例9-4編寫一段程序，定義一個(gè)游標(biāo)9.3.5T-SQL程序中，一些語句的說明

1.Set語句與Select語句的比較

Set只能給一個(gè)變量賦值，不支持多變量賦值。Select有3種功能，分別是：給變量賦值、輸出變量的值、查詢數(shù)據(jù)庫中表的記錄。Select語句支持多個(gè)變量賦值，變量之間用逗號隔開。

在給變量賦值時(shí)，Set與Select的區(qū)別有3點(diǎn)，如表9-9所示。序號前提情況Set賦值Select賦值1對多個(gè)變量同時(shí)賦值不支持支持2表達(dá)式返回多個(gè)值出錯(cuò)取返回值中的最后一個(gè)3表達(dá)式?jīng)]有返回值變量被賦null值變量保持原值9.3.5T-SQL程序中，一些語句的說明例如，在學(xué)生表S中，有多條記錄，其中含有“學(xué)號：01071101、姓名：張三”，下面是Set語句賦值情況。123456789declare@sName

varchar(30)=''/*聲明一個(gè)變量，初始值為空字符*/set@sName=(selectsNamefromswheresNo='01071101')/*表達(dá)式只有一條記錄*/print@sName/*輸出學(xué)號對應(yīng)的學(xué)生姓名：張三*/

set@sName=(selectsNamefromswhere1=2)/*表達(dá)式?jīng)]有記錄，則賦予null*/printisnull(@sName,'空值')/*輸出：空值*/

set@sName=(selectsNamefromswhere1=1)/*表達(dá)式有多條記錄，則出錯(cuò)*/printisnull(@sName,'空值')/*輸出：空值*/9.3.5T-SQL程序中，一些語句的說明下面是Select語句賦值情況。123456789declare@sName

varchar(30)=''/*聲明一個(gè)變量，初始值為空字符*/select@sName=sNamefromswheresNo='01071101'/*表達(dá)式只有一條記錄*/print@sName/*輸出學(xué)號對應(yīng)的學(xué)生姓名：張三*/

select@sName=sNamefromswhere1=1/*表達(dá)式有多條記錄，取最后一條記錄*/printisnull(@sName,'空值')/*輸出最后一條記錄：徐慧*/

select@sName=sNamefromswhere1=2/*表達(dá)式?jīng)]有記錄，則保持原值*/printisnull(@sName,'空值')/*輸出上次變量的值：徐慧*/9.3.5T-SQL程序中，一些語句的說明

2.Select語句與Print語句的比較

Print是將用戶的信息輸出到客戶端，它只能輸出一個(gè)變量的值，且該變量的數(shù)據(jù)類型只能為字符型，或能夠轉(zhuǎn)為字符型的，如數(shù)值型、日期型等。

Select語句可以輸出多個(gè)變量的值，變量的類型可不受限制。它甚至可將變量的值輸出到指定的文件中。

9.4SQLServer存儲過程本章主要內(nèi)容：（1）SQLServer概述（2）SQLServer中，SQL語句的使用（4）掌握SQLServer存儲過程的編程（5）掌握SQLServer觸發(fā)器的編程（6）SQLServer設(shè)置每天定時(shí)自動做數(shù)據(jù)庫備份（3）SQLServer編程基礎(chǔ)，包括游標(biāo)對象Cursor的使用9.4.1存儲過程的創(chuàng)建及調(diào)用表9-9創(chuàng)建存儲過程語法結(jié)構(gòu)If(exists(select*fromsys.objectswherename='cp_name'))/*若指定的存儲過程cp_name存在*/Dropprocedurecp_name/*刪除存儲過程cp_name*/GoCreateprocedurecp_name@param_nameparam_type[=default_value][out]AsBeginSQL語句塊[Return@return_value]End9.4.1存儲過程的創(chuàng)建及調(diào)用幾點(diǎn)說明：（1）sys.objects為當(dāng)前數(shù)據(jù)庫的系統(tǒng)表，它記錄了當(dāng)前數(shù)據(jù)庫中的所有數(shù)據(jù)對象。

（2）存儲過程可以沒有參數(shù)，也可以有多個(gè)輸入?yún)?shù)，參數(shù)之間用逗號隔開，定義輸入?yún)?shù)時(shí)，可以指定初始值，參數(shù)的數(shù)據(jù)類型可以是SQLServer支持的任何數(shù)據(jù)類型。其中out表示為輸出參數(shù)。

（3）存儲過程可以沒有返回值。如果有，可以用“Return”語句，指定存儲過程的返回值。

（4）調(diào)用存儲過程的方法為：exec存儲過程名。9.4.1存儲過程的創(chuàng)建及調(diào)用例9-5編寫一段程序，創(chuàng)建一個(gè)簡單的存儲過程，查詢學(xué)生的學(xué)號、姓名、性別。例9-5編寫一個(gè)簡單的存儲過程，查詢學(xué)生的學(xué)號、姓名、性別

9.4.2帶輸出參數(shù)的存儲過程帶輸出參數(shù)的存儲過程，定義時(shí)要標(biāo)明“out或output”，調(diào)用時(shí)，要標(biāo)明“output”。例9-6編寫一個(gè)帶有輸入?yún)?shù)、輸出參數(shù)的存儲過程，然后再調(diào)用它。

基本思路：下面程序，創(chuàng)建了一個(gè)名為cp_sc2的存儲過程，輸入?yún)?shù)為學(xué)號：@sNo，輸出參數(shù)為該學(xué)生選修課程的門數(shù)：@iCount。調(diào)用時(shí)，輸出參數(shù)要加上“output”。例9-6帶有輸入?yún)?shù)、輸出參數(shù)的存儲過程，然后再調(diào)用它。123456789Createprocedurecp_sc2@sNochar(8),@iCount

intoutas/*-------------輸入?yún)?shù)：@sNo

為學(xué)號，輸出參數(shù)：@iCount為選課記錄數(shù)-------------*/Begin

Select@iCount=count(*)fromscwheresNo=@sNo/*該學(xué)號的選課門數(shù)*/End

declare@yint/*聲明一個(gè)局部變量*/exec

cp_sc2'01071102',@youtput/*調(diào)用存儲過程，其中@y為輸出變量*/Print'選課門數(shù)：'+str(@y)/*打印輸出變量的值*/

9.4.3帶Return返回語句的存儲過程

在T-SQL中，存儲過程遇到Return語句，會立即返回，不再執(zhí)行Return后面的程序。Return的右邊可以帶一個(gè)返回值，該返回值只能為一個(gè)整數(shù)值，表示存儲過程的執(zhí)行狀態(tài)。存儲過程中可以沒有Return語句。此時(shí)，默認(rèn)情況下，返回值0表示存儲過程執(zhí)行成功，-1表示存儲過程執(zhí)行中出現(xiàn)錯(cuò)誤。存儲過程中也可以出現(xiàn)多個(gè)Return語句。可以通過指定不同的返回值，標(biāo)識存儲過程不同的執(zhí)行狀態(tài)。調(diào)用時(shí)，獲得存儲過程的返回值的語法為：

Declare@iReturnintExec@iReturn=存儲過程名

9.4.3帶Return返回語句的存儲過程例如，例9-6的存儲過程cp_sc2中沒有Return語句，但調(diào)用時(shí)，可以獲得其返回值，方法如下，結(jié)果如圖9-29所示。1234declare@yint/*聲明一個(gè)局部變量*/declare@iReturn

intexec@iReturn=cp_sc2'01071102',@youtput/*調(diào)用存儲過程，其中@y為輸出變量*/select'返回值：',@iReturn/*打印返回值*/

9.4.3帶Return返回語句的存儲過程例9-7編寫一個(gè)帶有Return語句的存儲過程，然后再調(diào)用它。123456789101112Createprocedurecs_insert@sNochar(8),@sName

varchar(30),@sexchar(1)AsBegin

declare@iReturn

intsetnocounton/*不顯示SQL語句執(zhí)行的結(jié)果*/InsertintoS(sNo,sName,sex,dtBirthdate)values(@sNo,@sName,@sex,‘2001-01-06’)set@iReturn=@@error/*獲取執(zhí)行SQL語句的錯(cuò)誤號*/if@iReturn<>0/*@@error!=0表示發(fā)生意外*/begin

RaisError(‘插入記錄失敗!’,16,1)/*函數(shù)RaisError用于拋出一個(gè)自定義錯(cuò)誤*/endreturn@iReturnEnd

基本思路：下面程序，創(chuàng)建了一個(gè)名為cp_insert的存儲過程，輸入?yún)?shù)為@sNo（學(xué)號）、，@sName（姓名）、@sex（姓名），返回值為@iReturn（執(zhí)行SQL語句的錯(cuò)誤號）。

9.4.3帶Return返回語句的存儲過程例9-7編寫一個(gè)帶有Return語句的存儲過程，然后再調(diào)用它。第一次按下面方式執(zhí)行該存儲過程，是成功的。第二次執(zhí)行時(shí)，出現(xiàn)錯(cuò)誤，原因是“學(xué)號重復(fù)”，如圖9-30所示。

9.4.4存儲過程綜合案例

例9-8編寫一個(gè)存儲過程，給定一串漢字，生成并輸出對應(yīng)的拼音碼。

基本思路：利用例9-2中的漢字編碼表，再根據(jù)本章數(shù)據(jù)附件“例9-8insertSql_Chinese.txt”中的插入語句，生成其中的6836個(gè)漢字的拼音碼。然后利用這6836個(gè)漢字的拼音碼，給出一串漢字，依次從左邊開始，每次取單個(gè)漢字，查詢該漢字的拼音碼，再迭加輸出。123456789Createprocedurecs_ChineseCode@strNameaschar(60)AS/*------------------------------------------------------------------------------------------------------------------

生成參數(shù)：@strName（一串漢字）的拼音碼@vcPYCode------------------------------------------------------------------------------------------------------------------*/Begindeclare@cChinese

varchar(2)/*單個(gè)漢字*/declare@cPYchar(1)/*單個(gè)漢字的拼音*/declare@iForasint=1/*循環(huán)計(jì)數(shù)器*/declare@iLengthasint/*一串漢字的長度，即：漢字的個(gè)數(shù)*/

例9-8編寫一個(gè)存儲過程，給定一串漢字，生成并輸出對應(yīng)的拼音碼。1011121314151617181920212223declare@vcPYCodeasvarchar(50)=''/*一串漢字的拼音碼*/set@strName=ltrim(rtrim(@strName))/*消除參數(shù)左右空格*/set@iLength=Len(@strName)/*獲取參數(shù)中，字符的個(gè)數(shù)*/setnocounton/*執(zhí)行SQL語句，屏蔽執(zhí)行提示*/While@iFor<=@iLength/*從1開始，若循環(huán)計(jì)數(shù)器不大于字符的個(gè)數(shù)*/

Begin/*-------------------------------------開始循環(huán)-----------------------------------------------*/

set@cChinese=Substring(@strName,@iFor,1)/*從左邊開始，依次取單個(gè)字符*/

select@cPY=isnull(cPY,'')fromsmchinesewherecChineseName=@cChinese

set@vcPYCode=ltrim(rtrim(@vcPYCode))+@cPY

/*將單個(gè)漢字的拼音碼依次累加*/select@cChinese,@cPY/*測試：每循環(huán)一次，輸出單個(gè)漢字，及拼音碼*/set@iFor=@iFor+1/*計(jì)數(shù)器加1*/

End/*--------------------------------------結(jié)束循環(huán)-----------------------------------------------*/print@vcPYCode/*輸出：@strName

對應(yīng)的拼音碼*/End調(diào)用存儲過程：execcs_ChineseCode'中華人民共和國'

輸出：ZHRMGHG

9.4.4存儲過程綜合案例例9-9根據(jù)例9-2中的圖書信息表：book（cBookNo,vcBookName,vcPYcode），編寫一個(gè)存儲過程，將所有圖書，根據(jù)圖書名稱，修改對應(yīng)的拼音碼（vcPYcode）?；舅悸罚合榷x一個(gè)游標(biāo)，對應(yīng)的查詢語句為：

SelectcBookNo,vcBookNamefrombook然后依次從游標(biāo)中讀取書名，再由書名生成對應(yīng)的拼音碼，進(jìn)行數(shù)據(jù)更新。123456789CreateProcedurecp_book_PYcodeAs/*---------生成并修改book中的vcPYcode

-------*/Begin

declare@vcBookName

varchar(60)/*圖書名稱*/declare@cBookNochar(8)/*圖書編號*/declare@cChinese

varchar(2)/*單個(gè)漢字*/declare@cPYchar(1)/*單個(gè)漢字的拼音*/declare@iFor

int=1/*循環(huán)計(jì)數(shù)器*/declare@iLength

int/*圖書名稱的長度，即漢字的個(gè)數(shù)*/declare@vcPYCode

varchar(50)=''/*圖書名稱的拼音碼*/例9-9book（cBookNo,vcBookName,vcPYcode），生成書名的拼音碼1011121314151617181920212223

declare@iRows

int/*游標(biāo)中的記錄條數(shù)*/

declare@iError

int=0

setnocounton/*執(zhí)行SQL語句，屏蔽執(zhí)行提示*/

Begintransaction/*開始事務(wù)*/

declarecursorTempScrollcursorfor/*定義游標(biāo)*/

SelectcBookNo,ltrim(rtrim(vcBookName))frombook

open

cursorTemp/*打開游標(biāo)*/

set@iRows=@@cursor_rows/*獲取游標(biāo)中記錄行數(shù)*/

while@iRows>0

begin

fetchnextfromcursorTempinto@cBookNo,@vcBookName/*獲取游標(biāo)當(dāng)前字段值*/

set@iLength=Len(@vcBookName)/*圖書名稱的長度*/

set@vcPYCode=''

set@iFor=1例9-9book（cBookNo,vcBookName,vcPYcode），生成書名的拼音碼2425262728293031323334353637383940while@iFor<=@iLength/*從1開始，若循環(huán)計(jì)數(shù)器不大于圖書名稱的長度*/

begin

set@cChinese=Substring(@vcBookName,@iFor,1)/*從左邊開始,依次取單個(gè)字符*/

select@cPY=isnull(cPY,'')fromsmchinesewherecChineseName=@cChinese

set@vcPYCode=ltrim(rtrim(@vcPYCode))+@cPY/*將單個(gè)漢字的拼音碼依次累加*/

set@iFor=@iFor+1/*計(jì)數(shù)器加1*/

end

select@vcBookName,@vcPYCode/*測試：輸出書名，及拼音碼*/

updatebooksetvcPYCode=@vcPYCodewherecBookNo=@cBookNo/*修改拼音碼*/

set@iError=@@error/*獲取SQL語句執(zhí)行的錯(cuò)誤號*/

if@iError<>0/*錯(cuò)誤號不等于0，表示失敗*/

begin

rollbacktransaction/*回滾事務(wù)*/break/*跳出循環(huán)*/

end

set@iRows=@iRows-1

end例9-9book（cBookNo,vcBookName,vcPYcode），生成書名的拼音碼4142434445464748

if@iError=0/*錯(cuò)誤號等于0，表示成功*/

begin

committransaction/*提交事務(wù)*/

end

close

cursorTemp/*關(guān)閉游標(biāo)*/

deallocate

cursorTemp/*從內(nèi)存釋放游標(biāo)*/

return@iErrorend調(diào)用存儲過程：execcp_book_PYcode

輸入查詢語句：Select*frombook，即可看到已經(jīng)修改的拼音碼。

9.5SQLServer觸發(fā)器本章主要內(nèi)容：（1）SQLServer概述（2）SQLServer中，SQL語句的使用（4）掌握SQLServer存儲過程的編程（5）掌握SQLServer觸發(fā)器的編程（6）SQLServer設(shè)置每天定時(shí)自動做數(shù)據(jù)庫備份（3）SQLServer編程基礎(chǔ)，包括游標(biāo)對象Cursor的使用9.5SQLServer中的觸發(fā)器觸發(fā)器的有關(guān)概念，請參看8.3節(jié)。觸發(fā)器（trigger）是建立在數(shù)據(jù)表上、保證數(shù)據(jù)完整性的一種機(jī)制，當(dāng)對一個(gè)表進(jìn)行

insert、delete、或

update操作時(shí)就會激活相應(yīng)的觸發(fā)器。觸發(fā)器經(jīng)常用于加強(qiáng)復(fù)雜的數(shù)據(jù)完整性約束和業(yè)務(wù)規(guī)則等。表9-10創(chuàng)建觸發(fā)器語法結(jié)構(gòu)Createtrigger觸發(fā)器名on表名for<Insert|delete|update>AsBeginSQL語句塊End9.5SQLServer中的觸發(fā)器當(dāng)觸發(fā)器觸發(fā)時(shí)，系統(tǒng)自動在內(nèi)存中會創(chuàng)建deleted臨時(shí)表或inserted臨時(shí)表。這2張臨時(shí)只讀，不允許修改，觸發(fā)器執(zhí)行完成后，自動刪除。其中，inserted表臨時(shí)保存了插入或更新后的記錄行；deleted表臨時(shí)保存了刪除或更新前的記錄行。9.5SQLServer中的觸發(fā)器例9-10根據(jù)例9-2中的學(xué)生、教工借（還）書數(shù)據(jù)表的結(jié)構(gòu)，在表smBorrow上創(chuàng)建一個(gè)插入觸發(fā)器，要求實(shí)現(xiàn)：學(xué)生的借書記錄（未還的）不能超過3條，教工的借書記錄（未還的）不能超過5條。一旦借書成功，立即將該圖書的狀態(tài)cStatus修改為’2’（表示借出）。

Book（cBookNo，vcBookName，vcPYCode，cStatus），其中cStatus=’1’表示在庫

smBorrow（iID，dtBorrowDate，cBookNo，cB