数据库原理与开发技术 课件 第9章 MS SQL Server 数据库技术

第

9

章

MSSQLServer

数据库技术本章主要内容：（1）SQLServer概述（2）SQLServer中，SQL语句的使用（4）掌握SQLServer存储过程的编程（5）掌握SQLServer触发器的编程（6）SQLServer设置每天定时自动做数据库备份（3）SQLServer编程基础，包括游标对象Cursor的使用9.1SQLServer概述

9.1.1SQLServer的发展历程1998年12月，SQLServer7.0正式上市。2000年8月，SQLServer2000发布，其中包括企业版、标准版、开发版、个人版四个版本。2008年，SQL

Server

2008正式发布。2010年4月，又发布了SQL

Server

2008的改进版SQL

Server

2008R2。2017年10月，微软新一代数据库产品SQLServer2017发布。2019年11月，微软发布了新一代数据库产品SQLServer2019。9.1.2

SQLServer的下载和安装

SQLServer2019有5个版本可供选择：Enterprise（企业版）、Standard（标准版）、Web（网站版）、Develpoer（开发版）、Express（精简版），其中后2个版本免费。双击下载的安装文件：SQL2019-SSEI-Dev.exe，进入安装，如图9-2所示。9.1.2

SQLServer的下载和安装9.1.2

SQLServer的下载和安装SQLServerManagementStudio是SQLServer可视化集成管理工作室，这个必须安装。9.1.3SQLServer的设置SQLServer2019安装成功后，还要进行2个设置，才能开始使用，一是启用TCP/IP，二是设置SQLServer服务器验证方式为双身份验证模式，并设置系统管理员sa的密码。1.启用TCP/IP方法：点击电脑左下角，找到“MicrosoftSQLServer2019”，点击：“SQLServer2019配置管理器”，进入：SQLServerConfigurationManager。如图9-7、9-8所示。9.1.3SQLServer的设置1.启用TCP/IP9.1.3SQLServer的设置2.设置SQLServer服务器验证方式为双身份验证方法：点击电脑左下角，找到“MicrosoftSQLServerManagementStudio”，点击进入。如图9-9（a）、（b）所示。9.1.3SQLServer的设置设置SQLServer服务器验证方式，如图9-10（a）、（b）、（c）所示。9.1.3SQLServer的设置设置SQLServer服务器验证方式，如图9-10（a）、（b）、（c）所示。9.1.3SQLServer的设置设置SQLServer服务器验证方式，如图9-10（a）、（b）、（c）所示。9.1.3SQLServer的设置3.设置系统管理员sa的密码9.1.3SQLServer的设置3.设置系统管理员sa的密码9.1.3SQLServer的设置到此为止，SQLServer的安装及设置已经全部完成。要使设置生效，必须重启SQLServer服务，或重启电脑。

如果不想重启电脑，那么通过下列方法，也可重启SQLServer服务：

点击电脑左下角，找到“SQLServer2019配置管理器”，点击进入：SQLServerConfigurationManager。如图9-13所示。

点击图9-13中的属性，可以看到连接SQLServer的各种IP及端口号。9.1.3SQLServer的设置退出SSMS后，重新进入SSMS，就可以用设置的sa密码，进行登录。9.1.4SQLServer的核心进程

SQLServer2019安装成功后，有一个核心程序：sqlservr.exe，默认位于“C:\ProgramFiles\MicrosoftSQLServer\MSSQL15.MSSQLSERVER\MSSQL\Binn”路径下。该程序的启动模式必须设定为“自动”。9.1.5SQLServer的系统数据库

系统数据库是指SQLServer安装时自带的数据库，它们存储了SQLServer相关的系统信息，主要协助SQLServer各方面的数据管理。SQLServer安装时，带有四个系统数据库：

（1）master数据库：记录SQLServer系统的所有系统级别信息，包括系统的登录账户、系统配置设置、数据库文件的位置和SQLServer初始化信息。（2）model数据库：用于在SQLServer实例上创建用户数据库的模板。当发出Createdatabase语句时新数据库的一部分通过复制model数据库中的内容创建，剩余部分有空页填充。（3）tempdb数据库：保存所有的临时表和临时存储过程，包括其他临时存储要求。

（4）msdb数据库：供SQLServer代理程序调度报警、作业时使用。9.1.6SQLServer的数据库文件和日志文件SQLServer2019的每个数据库对应有3种类型的数据库文件。

（1）主数据文件。该文件是数据库的起点，指向数据库中文件的其他部分。每个数据库都有一个主数据库文件，默认文件名为：数据库名.mdf。（2）次数据文件。包含除主数据文件以外的所有数据文件。有的数据库可能没有次数据文件，有的可能有多个。其默认文件扩展名为：*.ndf。（3）日志文件。包含恢复数据库所需的所有日志信息。每个数据库至少有一个日志文件，有的可能有多个。其默认文件扩展名为：*.ldf。9.1.6SQLServer的数据库文件和日志文件这些数据库文件，默认路径：

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

9.2SQLServer中，SQL语句的使用本章主要内容：（1）SQLServer概述（2）SQLServer中，SQL语句的使用（4）掌握SQLServer存储过程的编程（5）掌握SQLServer触发器的编程（6）SQLServer设置每天定时自动做数据库备份（3）SQLServer编程基础，包括游标对象Cursor的使用9.2.1创建用户数据库在SSMS中，创建一个数据库有两种方法，一是利用可视化界面，一是利用SQL语句。

一个数据库被创建后，会在系统默认的数据路径（或指定路径）下，生成两个数据库物理文件：一个是主数据文件，扩展名为*.mdf，一个是日志文件，扩展名为*.ldf。

1.利用可视化界面创建数据库9.2.1创建用户数据库

1.利用可视化界面创建数据库9.2.1创建用户数据库

1.利用可视化界面创建数据库9.2.1创建用户数据库

2.利用SQL语句创建数据库12345678910111213141516CreateDatabasemyTeston/*----------------------创建数据库-----------------*/( name=myTest_data,/*数据逻辑文件名*/

FileName='d:\data\myTest_data.mdf',/*数据物理文件名*/ Size=10MB,/*初始数据文件大小*/

Maxsize=200MB,/*数据物理文件最大限制*/

FileGrowth=5/*按5%增长*/)Logon( name=myTest_log,/*日志逻辑文件名*/

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

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

FileGrowth=5MB/*按5MB增长*/);9.2.1创建用户数据库

2.利用SQL语句创建数据库9.2.1创建用户数据库

用户数据库创建成功后，可以在对应的路径下，看到生成的数据库物理文件，如图9-21所示。9.2.2创建基本表例9-2根据例3-2中的教学、借（还）书数据库的关系模式图，在student数据库下，创建对应的基本表，如图9-22所示。9.2.3数据更新（Insert、Update、Delete）和数据查询（Select）例9-3根据例9-2的表结构，利用本章数据附件“例9-3insertSQL.txt”提供的插入语句，将其数据插入到student数据库中。下面是部分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','高等代数','T22');insertintoC(cNo,cName,tNo)values('C23','DataStructure','T23');insertintoC(cNo,cName,tNo)values('C24','离散数学','T23');insertintoC(cNo,cName,tNo)values('C25','DataBase','T24');insertintoC(cNo,cName,tNo)values(‘C26’,‘C语言程序设计’,‘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数据类型表9-1SQLServer常用数据类型序号类别数据类型1整数型tinyint、smallint、int、integer、bigint、bit2定长浮点型numeric(p,d)、decimal(p,d)3单精度浮点型float(n)4双精度浮点型real5货币型smallmoney、money6字符型char(n)、varchar(n)、text7双字节字符型nchar(n)、nvarchar(n)、ntext8二进制型binary、varbinary、image9日期型date、datetime9.2.5SQLServer常用内置函数（1）聚合函数。SQLServer2019增加了2个统计函数。序号函数名功能说明1count（*）返回记录数2Avg（列名）返回列名取值的平均值，其中列名必须为数值型，空值（null）被忽略3max（列名）返回列名取值的最大值，空值（null）被忽略4min（列名）返回列名取值的最小值，空值（null）被忽略5sum（列名）返回列名取值的和，其中列名必须为数值型，空值（null）被忽略6Var（列名）返回列名取值的统计方差，其中列名必须为数值型，空值（null）被忽略7Stdev（列名）返回列名取值的标准差，其中列名必须为数值型，空值（null）被忽略9.2.5SQLServer常用内置函数（2）字符函数。SQLServer常用字符函数。序号函数名功能说明示例1len（s）返回字符串s在Unicode下的长度，含左边的空格，但不含右边的空格selectlen('中国ok')：返回4selectlen('中国ok')

：返回52datalength(s)返回字符串s的字节串长度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）将数值型x转为字符串，前导有空格selectltrim(str(123.56))：返回‘123’7left（s,m）返回字符串s的左边m个字符selectleft('abcd',2)

：返回'ab'8right（s,m）返回字符串s的右边m个字符selectright('abcd',2)

：返回'cd'9substring（s,m,n）从字符串s的左边第m个字符开始，取n个字符selectsubstring('abcd',2,2)

：返回'bc'9.2.5SQLServer常用内置函数（3）日期函数。序号函数名功能说明示例1getdate（）以SQLServer标准内部格式，返回系统当前的日期、时间selectgetdate()：返回2021-06-2307:45:21.8772dateAdd（f,n,d）按指定时间单位f，对日期d增加n间隔后的datetime值selectdateadd(day,2,getdate())selectdateadd(month,2,'2021-06-08')selectdateadd(year,-2,'2021-06-08')3date（d）返回日期d的“日”部分的整数selectdate('2021-06-08')：返回84month（d）返回日期d的“月”部分的整数selectmonth('2021-06-08')：返回65year（d）返回日期d的“年”部分的整数selectyear('2021-06-08')：返回20216dateDiff(f,d1,d2)按指定时间单位f，返回从日期d1到d2间隔的数值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常用内置函数例如，在SSMS，输入上面的部分示例语句，结果如图9-23所示。9.2.5SQLServer常用内置函数（4）系统函数。SQLServer常用系统函数。序号函数名功能说明示例1convert（type(n),f）按指定的数据类型（含长度）、格式f，返回一个值selectconvert(varchar(6),123.56)selectconvert(char(10),getdate(),120)selectconvert(char(10),getdate(),108)2Host_name（）返回数据库所在计算机名selectHOST_name()3System_user返回当前登录数据库的用户名selectsystem_user9.2.5SQLServer常用内置函数例如，在SSMS，输入上面的部分示例语句，结果如图9-24所示。

9.3SQLServer后台编程基础本章主要内容：（1）SQLServer概述（2）SQLServer中，SQL语句的使用（4）掌握SQLServer存储过程的编程（5）掌握SQLServer触发器的编程（6）SQLServer设置每天定时自动做数据库备份（3）SQLServer编程基础，包括游标对象Cursor的使用

9.3SQLServer后台编程基础数据库端程序，主要指存储过程、自定义函数和触发器。一些比较复杂的逻辑处理程序放置在后台处理，可以大大提高数据处理效率。

数据库端处理程序，一般称为Transaction-SQL（事务性-SQL，简称T-SQL），加入了很多高级语言的元素，大大丰富了SQL语言的表现能力。9.3.1变量及赋值1.局部变量用户在程序中自己定义的变量，称为局部变量，声明时必须以@作为前缀。局部变量的作用范围仅限于用户定义它的那个程序块。

9.3.1变量及赋值局部变量必须先声明，再赋值。其声明的语法为：

Declare@变量名

数据类型[=初始值]/*声明变量时，可以指定初始值*/局部变量的赋值，有3种方法：（1）定义时直接初始化。下面语句声明了一个定长字符串变量，初始值为’0’：12

Declare@flagchar(1)='0'/*声明一个定长字符串变量*/print@flag/*打印局部变量的值*/（2）用Set语句给变量赋值。例如，下面语句声明了一个整数变量，赋值为8：12

Declare@xint/*声明一个整数变量*/Set@x=8/*给局部变量赋值*/

9.3.1变量及赋值（3）用Select语句赋值。该语句来自于查询记录的结果集，其中，结果集中的记录数必须大于或等于1。如果记录数大于1，则取最后一条记录的值。如果记录数为0，则返回空值（null）。例如，下列语句第5行，select取最后一个记录值，而第8行，则为空。123456789

usestudent/*将student设置为当前数据库*/declare@sNochar(8)/*局部变量：学号*/declare@sNo1char(8)/*局部变量：学号*/declare@sName

varchar(30)/*局部变量：姓名*/select@sNo=sNo,@sName=ltrim(sName)fromswheresex='M'print'学号：'+@sNo/*输出：学号：05071137*/print'姓名：'+@sName/*输出：姓名：刘勇*/select@sNo1=sNofromswhere1=2/*没有记录*/printisnull(@sNo1,'空')/*输出：空*/

9.3.1变量及赋值在SSMS中，输入上述代码

9.3.1变量及赋值

2.全局变量在SQLServer中，前缀用@@标识的变量，称为全局变量，也称系统变量。全局变量由系统定义和维护，用户不能定义全局变量，也不能修改全局变量的值，只能读取。全局变量记录了SQLServer服务器的活动状态、系统设置，及SQL语句的执行状态等。常见的全局变量，如表9-6所示。

9.3.1变量及赋值表9-6SQLServer常用全局变量序号全局变量名含义1@@error返回上一条T-SQL语句的错误号，如果上一条T-SQL语句执行没有错误，则返回02@@MAX_CONNECTIONS返回SQLServer实例允许同时进行的最大用户连接数。3@@rowcount返回上一条SQL语句影响的行数4@@SERVERNAME返回运行SQLServer的本地服务器的名称5@@VERSION返回当前的SQLServer安装的版本、处理器体系结构、生成日期和操作系统6@@cursor_rows返回当前打开的游标所含记录的条数9.3.2T-SQL语句中的程序控制1.If条件分支结构和Case多分支结构（1）If单分支选择结构（3）case-end多分支选择结构If条件表达式begin

语句块endCasewhen条件1then结果1when条件2then结果2…else其他结果End（2）If-else双分支选择结构If条件表达式begin

语句块1endElsebegin

语句块2end9.3.2T-SQL语句中的程序控制

2.While循环在While循环中，先对指定的表达式进行判断，如果为真，则执行循环体内的语句，否则退出循环。语法结构如下：

While条件表达式Begin

语句块[break]

语句块 [continue]End9.3.3T-SQL语句中的事务与意外处理在T-SQL语句块中，可以显示定义一个事务，一般通过SQL语句执行时的全局变量的取值，捕获意外。如果没有发生意外，就提交事务，否则，就回滚。其语法结构如下：

BeginTransaction--开始事务

语句块If@@error=0/*全局变量@@error=0，表示没有发生意外*/CommitTransaction--提交事务elseRollbackTransaction--回滚事务9.3.4T-SQL语句中的游标（Cursor）在T-SQL语句块中，如果一条查询语句有多条返回记录，而要逐条进行处理，这就是游标（cursor）。Declare游标名ScrollCursorForSelect语句/*定义游标*/Open游标名/*打开游标*/Select@row=@@cursor_rows/*获取游标中，记录的行数*/While@row>0BeginFetchNextfrom游标名into局部变量/*获取游标当前记录的值*/SQL

语句块set@row=@row–1/*给表达式赋值*/EndClose游标名/*关闭游标*/Deallocate游标名/*释放游标*/游标的操作分4步：声明游标变量、打开游标、使用和关闭游标。9.3.4T-SQL语句中的游标（Cursor）例9-4编写一段程序，定义一个游标，查询学生的学号、姓名，并按性别不同，分男、女输出学号、姓名。12345usestudent/*将student设置为当前数据库*/declare@sNochar(8)/*局部变量：学号*/declare@sName

varchar(30)/*局部变量：姓名*/declare@sexchar(1)/*局部变量：性别*/declare@rowint=0/*声明局部变量，初始值为0，表示游标中记录的行数*/例9-4编写一段程序，定义一个游标67891011121314151617181920declarecur_tempscrollcursorforselectsNo,sName,sexfroms/*定义游标*/opencur_temp/*打开游标*/select@row=@@cursor_rows/*获取游标中记录的行数（全局变量）*/while@row>0/*若行数计数器大于0*/begin/*------------------------开始循环-------------------*/fetchnextfromcur_tempinto@sNo,@sName,@sex/*获取游标当前记录的值*/if@sex=‘M’print@sNo+','+@sName+',男'/*打印：学号，姓名，性别*/elseprint@sNo+','+@sName+',女'set@row=@row-1/*计数器减1*/end/*--------------------------结束循环-------------------*/closecur_temp/*关闭游标*/Deallocatecur_temp/*从内存释放游标*/例9-4编写一段程序，定义一个游标9.3.5T-SQL程序中，一些语句的说明

1.Set语句与Select语句的比较

Set只能给一个变量赋值，不支持多变量赋值。Select有3种功能，分别是：给变量赋值、输出变量的值、查询数据库中表的记录。Select语句支持多个变量赋值，变量之间用逗号隔开。

在给变量赋值时，Set与Select的区别有3点，如表9-9所示。序号前提情况Set赋值Select赋值1对多个变量同时赋值不支持支持2表达式返回多个值出错取返回值中的最后一个3表达式没有返回值变量被赋null值变量保持原值9.3.5T-SQL程序中，一些语句的说明例如，在学生表S中，有多条记录，其中含有“学号：01071101、姓名：张三”，下面是Set语句赋值情况。123456789declare@sName

varchar(30)=''/*声明一个变量，初始值为空字符*/set@sName=(selectsNamefromswheresNo='01071101')/*表达式只有一条记录*/print@sName/*输出学号对应的学生姓名：张三*/

set@sName=(selectsNamefromswhere1=2)/*表达式没有记录，则赋予null*/printisnull(@sName,'空值')/*输出：空值*/

set@sName=(selectsNamefromswhere1=1)/*表达式有多条记录，则出错*/printisnull(@sName,'空值')/*输出：空值*/9.3.5T-SQL程序中，一些语句的说明下面是Select语句赋值情况。123456789declare@sName

varchar(30)=''/*声明一个变量，初始值为空字符*/select@sName=sNamefromswheresNo='01071101'/*表达式只有一条记录*/print@sName/*输出学号对应的学生姓名：张三*/

select@sName=sNamefromswhere1=1/*表达式有多条记录，取最后一条记录*/printisnull(@sName,'空值')/*输出最后一条记录：徐慧*/

select@sName=sNamefromswhere1=2/*表达式没有记录，则保持原值*/printisnull(@sName,'空值')/*输出上次变量的值：徐慧*/9.3.5T-SQL程序中，一些语句的说明

2.Select语句与Print语句的比较

Print是将用户的信息输出到客户端，它只能输出一个变量的值，且该变量的数据类型只能为字符型，或能够转为字符型的，如数值型、日期型等。

Select语句可以输出多个变量的值，变量的类型可不受限制。它甚至可将变量的值输出到指定的文件中。

9.4SQLServer存储过程本章主要内容：（1）SQLServer概述（2）SQLServer中，SQL语句的使用（4）掌握SQLServer存储过程的编程（5）掌握SQLServer触发器的编程（6）SQLServer设置每天定时自动做数据库备份（3）SQLServer编程基础，包括游标对象Cursor的使用9.4.1存储过程的创建及调用表9-9创建存储过程语法结构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存储过程的创建及调用几点说明：（1）sys.objects为当前数据库的系统表，它记录了当前数据库中的所有数据对象。

（2）存储过程可以没有参数，也可以有多个输入参数，参数之间用逗号隔开，定义输入参数时，可以指定初始值，参数的数据类型可以是SQLServer支持的任何数据类型。其中out表示为输出参数。

（3）存储过程可以没有返回值。如果有，可以用“Return”语句，指定存储过程的返回值。

（4）调用存储过程的方法为：exec存储过程名。9.4.1存储过程的创建及调用例9-5编写一段程序，创建一个简单的存储过程，查询学生的学号、姓名、性别。例9-5编写一个简单的存储过程，查询学生的学号、姓名、性别

9.4.2带输出参数的存储过程带输出参数的存储过程，定义时要标明“out或output”，调用时，要标明“output”。例9-6编写一个带有输入参数、输出参数的存储过程，然后再调用它。

基本思路：下面程序，创建了一个名为cp_sc2的存储过程，输入参数为学号：@sNo，输出参数为该学生选修课程的门数：@iCount。调用时，输出参数要加上“output”。例9-6带有输入参数、输出参数的存储过程，然后再调用它。123456789Createprocedurecp_sc2@sNochar(8),@iCount

intoutas/*-------------输入参数：@sNo

为学号，输出参数：@iCount为选课记录数-------------*/Begin

Select@iCount=count(*)fromscwheresNo=@sNo/*该学号的选课门数*/End

declare@yint/*声明一个局部变量*/exec

cp_sc2'01071102',@youtput/*调用存储过程，其中@y为输出变量*/Print'选课门数：'+str(@y)/*打印输出变量的值*/

9.4.3带Return返回语句的存储过程

在T-SQL中，存储过程遇到Return语句，会立即返回，不再执行Return后面的程序。Return的右边可以带一个返回值，该返回值只能为一个整数值，表示存储过程的执行状态。存储过程中可以没有Return语句。此时，默认情况下，返回值0表示存储过程执行成功，-1表示存储过程执行中出现错误。存储过程中也可以出现多个Return语句。可以通过指定不同的返回值，标识存储过程不同的执行状态。调用时，获得存储过程的返回值的语法为：

Declare@iReturnintExec@iReturn=存储过程名

9.4.3带Return返回语句的存储过程例如，例9-6的存储过程cp_sc2中没有Return语句，但调用时，可以获得其返回值，方法如下，结果如图9-29所示。1234declare@yint/*声明一个局部变量*/declare@iReturn

intexec@iReturn=cp_sc2'01071102',@youtput/*调用存储过程，其中@y为输出变量*/select'返回值：',@iReturn/*打印返回值*/

9.4.3带Return返回语句的存储过程例9-7编写一个带有Return语句的存储过程，然后再调用它。123456789101112Createprocedurecs_insert@sNochar(8),@sName

varchar(30),@sexchar(1)AsBegin

declare@iReturn

intsetnocounton/*不显示SQL语句执行的结果*/InsertintoS(sNo,sName,sex,dtBirthdate)values(@sNo,@sName,@sex,‘2001-01-06’)set@iReturn=@@error/*获取执行SQL语句的错误号*/if@iReturn<>0/*@@error!=0表示发生意外*/begin

RaisError(‘插入记录失败!’,16,1)/*函数RaisError用于抛出一个自定义错误*/endreturn@iReturnEnd

基本思路：下面程序，创建了一个名为cp_insert的存储过程，输入参数为@sNo（学号）、，@sName（姓名）、@sex（姓名），返回值为@iReturn（执行SQL语句的错误号）。

9.4.3带Return返回语句的存储过程例9-7编写一个带有Return语句的存储过程，然后再调用它。第一次按下面方式执行该存储过程，是成功的。第二次执行时，出现错误，原因是“学号重复”，如图9-30所示。

9.4.4存储过程综合案例

例9-8编写一个存储过程，给定一串汉字，生成并输出对应的拼音码。

基本思路：利用例9-2中的汉字编码表，再根据本章数据附件“例9-8insertSql_Chinese.txt”中的插入语句，生成其中的6836个汉字的拼音码。然后利用这6836个汉字的拼音码，给出一串汉字，依次从左边开始，每次取单个汉字，查询该汉字的拼音码，再迭加输出。123456789Createprocedurecs_ChineseCode@strNameaschar(60)AS/*------------------------------------------------------------------------------------------------------------------

生成参数：@strName（一串汉字）的拼音码@vcPYCode------------------------------------------------------------------------------------------------------------------*/Begindeclare@cChinese

varchar(2)/*单个汉字*/declare@cPYchar(1)/*单个汉字的拼音*/declare@iForasint=1/*循环计数器*/declare@iLengthasint/*一串汉字的长度，即：汉字的个数*/

例9-8编写一个存储过程，给定一串汉字，生成并输出对应的拼音码。1011121314151617181920212223declare@vcPYCodeasvarchar(50)=''/*一串汉字的拼音码*/set@strName=ltrim(rtrim(@strName))/*消除参数左右空格*/set@iLength=Len(@strName)/*获取参数中，字符的个数*/setnocounton/*执行SQL语句，屏蔽执行提示*/While@iFor<=@iLength/*从1开始，若循环计数器不大于字符的个数*/

Begin/*-------------------------------------开始循环-----------------------------------------------*/

set@cChinese=Substring(@strName,@iFor,1)/*从左边开始，依次取单个字符*/

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

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

/*将单个汉字的拼音码依次累加*/select@cChinese,@cPY/*测试：每循环一次，输出单个汉字，及拼音码*/set@iFor=@iFor+1/*计数器加1*/

End/*--------------------------------------结束循环-----------------------------------------------*/print@vcPYCode/*输出：@strName

对应的拼音码*/End调用存储过程：execcs_ChineseCode'中华人民共和国'

输出：ZHRMGHG

9.4.4存储过程综合案例例9-9根据例9-2中的图书信息表：book（cBookNo,vcBookName,vcPYcode），编写一个存储过程，将所有图书，根据图书名称，修改对应的拼音码（vcPYcode）。基本思路：先定义一个游标，对应的查询语句为：

SelectcBookNo,vcBookNamefrombook然后依次从游标中读取书名，再由书名生成对应的拼音码，进行数据更新。123456789CreateProcedurecp_book_PYcodeAs/*---------生成并修改book中的vcPYcode

-------*/Begin

declare@vcBookName

varchar(60)/*图书名称*/declare@cBookNochar(8)/*图书编号*/declare@cChinese

varchar(2)/*单个汉字*/declare@cPYchar(1)/*单个汉字的拼音*/declare@iFor

int=1/*循环计数器*/declare@iLength

int/*图书名称的长度，即汉字的个数*/declare@vcPYCode

varchar(50)=''/*图书名称的拼音码*/例9-9book（cBookNo,vcBookName,vcPYcode），生成书名的拼音码1011121314151617181920212223

declare@iRows

int/*游标中的记录条数*/

declare@iError

int=0

setnocounton/*执行SQL语句，屏蔽执行提示*/

Begintransaction/*开始事务*/

declarecursorTempScrollcursorfor/*定义游标*/

SelectcBookNo,ltrim(rtrim(vcBookName))frombook

open

cursorTemp/*打开游标*/

set@iRows=@@cursor_rows/*获取游标中记录行数*/

while@iRows>0

begin

fetchnextfromcursorTempinto@cBookNo,@vcBookName/*获取游标当前字段值*/

set@iLength=Len(@vcBookName)/*图书名称的长度*/

set@vcPYCode=''

set@iFor=1例9-9book（cBookNo,vcBookName,vcPYcode），生成书名的拼音码2425262728293031323334353637383940while@iFor<=@iLength/*从1开始，若循环计数器不大于图书名称的长度*/

begin

set@cChinese=Substring(@vcBookName,@iFor,1)/*从左边开始,依次取单个字符*/

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

set@vcPYCode=ltrim(rtrim(@vcPYCode))+@cPY/*将单个汉字的拼音码依次累加*/

set@iFor=@iFor+1/*计数器加1*/

end

select@vcBookName,@vcPYCode/*测试：输出书名，及拼音码*/

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

set@iError=@@error/*获取SQL语句执行的错误号*/

if@iError<>0/*错误号不等于0，表示失败*/

begin

rollbacktransaction/*回滚事务*/break/*跳出循环*/

end

set@iRows=@iRows-1

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

if@iError=0/*错误号等于0，表示成功*/

begin

committransaction/*提交事务*/

end

close

cursorTemp/*关闭游标*/

deallocate

cursorTemp/*从内存释放游标*/

return@iErrorend调用存储过程：execcp_book_PYcode

输入查询语句：Select*frombook，即可看到已经修改的拼音码。

9.5SQLServer触发器本章主要内容：（1）SQLServer概述（2）SQLServer中，SQL语句的使用（4）掌握SQLServer存储过程的编程（5）掌握SQLServer触发器的编程（6）SQLServer设置每天定时自动做数据库备份（3）SQLServer编程基础，包括游标对象Cursor的使用9.5SQLServer中的触发器触发器的有关概念，请参看8.3节。触发器（trigger）是建立在数据表上、保证数据完整性的一种机制，当对一个表进行

insert、delete、或

update操作时就会激活相应的触发器。触发器经常用于加强复杂的数据完整性约束和业务规则等。表9-10创建触发器语法结构Createtrigger触发器名on表名for<Insert|delete|update>AsBeginSQL语句块End9.5SQLServer中的触发器当触发器触发时，系统自动在内存中会创建deleted临时表或inserted临时表。这2张临时只读，不允许修改，触发器执行完成后，自动删除。其中，inserted表临时保存了插入或更新后的记录行；deleted表临时保存了删除或更新前的记录行。9.5SQLServer中的触发器例9-10根据例9-2中的学生、教工借（还）书数据表的结构，在表smBorrow上创建一个插入触发器，要求实现：学生的借书记录（未还的）不能超过3条，教工的借书记录（未还的）不能超过5条。一旦借书成功，立即将该图书的状态cStatus修改为’2’（表示借出）。

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

smBorrow（iID，dtBorrowDate，cBookNo，cB