《数据库系统原理与应用（第二版）》课件第7章

第七章SQLServer2005程序设计7.1Transact-SQL程序设计基础7.1.1Transact-SQL语法格式约定大写字母：代表Transact-SQL保留的关键字.小字字母：表示对象标识符和表达式等.大括号{}或尖括号<>：大括号或尖括号中的内容为必选参数，其中可包含多个选项，各选项之间用竖线分隔，用户必须从这些选项中选择一项.方括号[]：它所列出的项目列表为可选项，用户可根据需要选择使用.竖线｜：表示参数之间是“或”的关系，可以从中选择任意一个使用.[,…n]：表示重复前面的语法单元，各项之间用逗号分隔。[…n]：表示重复前面的语法单元，各项之间用空格分隔。注释：注释为Transact-SQL脚本程序中的说明信息，SQLServer不执行这部分内容。

单行注释：使用两个连字符（--）作为注释的开始标志。从它到本行行尾的所有内容均为注释信息。块注释：块注释的格式为/*…*/，其间的所有内容均为注释信息。块注释与单行注释的不同之处是它可以跨越多行，并且可以插入在程序代码中的任何地方。7.1.1Transact-SQL语法格式约定(续)7.1.2标识符1．常规标识符

常规标识符遵守以下的命名规则：标识符长度可以为1～128个字符。标识符的首字符必须为Unicode2.0标准所定义的字母或_、＠、＃符号。标识符第一个字符后面的字符可以为UnicodeStandard2.0所定义的字符、数字或＠、#、$、_符号。标识符内不能嵌入空格和特殊字符。标识符不能与SQLServer中的保留关键字同名。2．定界标识符定界标识符允许在标识符中使用SQLServer保留关键字或常规标识符中不允许使用的一些特殊字符，但必须由双引号或方括号定界符进行分隔.7.1.3运算符1．算术运算符+：加。-：减。*：乘。/：除。%：取模。2．位运算符对整数或二进制数据进行按位与（&）、或（|）、异或（^）、求反（～）等逻辑运算。3．比较运算符>：大于。=：等于。<：小于。>=：大于等于。<=：小于等于。<>：不等于。!=：不等于。!>：不大于。!<：不小于。4．逻辑运算符AND。OR。NOT。[NOT]BETWEEN….AND。[NOT]LIKE。[NOT]IN。IS[NOT]NULL。ALL、SOME、ANY。[NOT]EXISTS。6．赋值运算符SQLServer中的赋值运算符为等号（=），它将表达式的值赋给一个变量.5．字符串连接符“+”可以实现字符串之间的连接操作

7．1．4．变量DECLARE语句的语法格式为：DECLARE＠variable_namedate_type[,@variable_namedata_type…]1．变量声明2．变量赋值SET语句的语法格式为:SET@variable_name=expressionSELECT语句为变量赋值的语法格式为：SELECT@variable_name=expression[FROM<表名>WHERE<条件>]7.1.5流程控制语句1．BEGIN…END语句BEGIN{SQL语句｜语句块}END2．条件语句条件语句的语法格式为：IF

<布尔表达式>{SQL语句｜语句块}[ELSE{SQL语句｜语句块}]7.1.5流程控制语句(续)3．转移语句GOTO

<标号>4．循环语句循环语句的语法格式为：

WHILE<布尔表达式> {SQL语句｜语句块} [BREAK] {SQL语句｜语句块} [CONTINUE] [SQL语句｜语句块]7.1.5流程控制语句(续)5．等待语句WAITFOR{DELAY'interval'｜TIME'time'}6．返回语句RETURN

[整数表达式]简单CASE函数语法：CASEinput_expression

WHENwhen_expressionTHENresult_expression

[...n]

[

ELSEelse_result_expression

]END7．CASE语句CASE搜索函数语法：CASE

WHENBoolean_expressionTHENresult_expression

[...n]

[

ELSEelse_result_expression

]END7.1.6．异常处理1．TRY...CATCH语句TRY...CATCH语句是在SQLSERVER2005数据库中新加入的，用于实现异常的处理。TRY...CATCH语法结构如下：BEGINTRY

{语句｜语句块}ENDTRYBEGINCATCH

{语句｜语句块}ENDCATCH2．与异常有关的函数ERROR_NUMBER()：返回错误号。ERROR_SEVERITY()：返回错误的严重级别。ERROR_STATE()：返回错误状态号。ERROR_PROCEDURE()：返回发生错误的存储过程或触发器的名称。ERROR_LINE()：返回发生错误的行号。ERROR_MESSAGE()：返回错误的消息文本。3．抛出错误语句RAISERROR(message|msg_id,,severity,state

[,argument[,...n]])[WITHoption[,...n]]7.1.7游标（1）游标的定义DECLARE<游标名>[INSENSITIVE][SCROLL]CURSORFOR<查询语句>[FOR<READONLY|UPDATE[OFcolumn_list]>（2）打开游标OPEN<游标名>（3）从一个打开的游标中提取数据行

FETCH[[NEXT|PRIOR|FIRST|LAST|ABSOLUTE{n|@nvar}|RELATIVE{n|@nvar}]FROM]<游标名>[INTO<变量名列表>]（4）关闭游标CLOSE<游标名>（5）释放游标

DEALLOCATE<游标名>（6）游标应用举例在图书管理信息系统（BookSys）中，有一个名为Book的图书信息表，其中有一个名为Price的图书单价字段。考虑到图书维护成本的不断增长，再考虑按如下规则对图书单价进行提价：30元以下的，提价10%；60元以下的，提价20%；60元以上的，提价30%。参考教材中的代码.7.2存储过程7.2.1存储过程概述存储过程分为三类：系统提供的存储过程、用户定义的存储过程和扩展存储过程.(2)自定义的存储过程：是由用户为完成某一特定功能而编写的存储过程。在SQLSERVER2005中，按编写的语言，又分为两种类型：T-SQL和CLR。（1）系统提供的存储过程：在安装SQLServer时，系统创建了很多系统存储过程（3）扩展存储过程：是对动态链接库（DLL）函数的调用7.2.2存储过程的优点（1）减少网络流量（2）增强代码的重用性和共享性（3）加快系统运行速度（4）加强安全性7.2.3．用T-SQL语句创建存储过程创建存储过程的语法为：CREATEPROCEDURE<procedure_name>[WITHENCRYPTION][<@parameterdata_type>[=default][OUTPUT]][,...n]ASsql_statement[...n]WITHENCRYPTION：加密存储过程代码，保护作者知识产权。procedure_name：存储过程的名称。@parameter：参数名称，注意名称前必须有“@”符号。data_type：参数的数据类型。default：输入参数的缺省值。OUTPUT：表明该参数是输出参数。sql_statement：SQL语句，这是存储过程的重点构造部分CREATEPROCEDUREprocReader1 --创建存储过程@CardIDCHAR(10)ASSELECTBORROW.CARDID,READER.[NAME],BORROW.BOOKID,BOOK.BOOKNAME,BDATE,SDATEFROMBORROW,BOOK,READERWHEREBORROW.BOOKID=BOOK.BOOKIDANDBORROW.CARDID=READER.CARDIDANDREADER.CARDID=@CardID例:创建一存储过程，根据传入的读者卡号，检索该读者的借书信息，包括卡号、姓名、书号、书名、借书时间和还书时间。该存储过程带一输入参数：@CardID，即传入一个读者卡号7．2．4．执行存储过程执行存储过程的完整语法如下：[EXEC][@return_value=]procedure_name[Value_List][@return_value=]用于接收存储过程的返回值。procedure_name：要执行的存储过程的名称。Value_List：输入参数值。参数之间用逗号分隔，输出参数一定要传变量7.2.5删除存储过程删除存储过程是指删除由用户创建的存储过程。格式：DROPPROCEDURE存储过程名。7.2.6．在ManagementStudio中修改存储过程

在“对象资源管理器”中,展开用户数据库（如BOOKSYS）、“可编程性”、“存储过程”，在“存储过程”节点下可以看到用户创建的所有存储过程，右击要修改的存储过程,在弹出的菜单中选择“修改”命令。代码编辑器中显示该存储过程的代码如图所示7.2.7使用ManagementStudio中模板新建存储过程直接在“存储过程”节点上右键单击，在弹出菜单中选择“新建存储过程”命令，系统会打开代码编辑器，并在代码编辑器中显示创建存储过程的模板。用户可以修改模板中的参数并添加相关T-SQL代码.7.3函数7.3.1函数类型（1）内连标量函数：是指返加类型为RETURN子句中定义的数据类型的单个值,内连标量函数的函数体是单个T-SQL语句（2）多语句标量函数：是指返加类型为RETURN子句中定义的数据类型的单个值。函数体是包含在BEGIN...END之间的一组T-SQL语句。

（3）内连表值函数：返回TABLE数据类型，它没有函数体，返回的表是单个SELECT语句执行后的返回结果集

7.3.1函数类型(续)（4）多语句表值函数：返回TABLE数据类型，函数体中包含一组T-SQL语句，这些语句可以生成行，并插入到返回表中。（5）内置函数：也称系统函数，是SQLSERVER2005提供的返回标量数据类型或TABLE数据类型的函数。内置函数不能修改。7．3．2．函数的优点函数和存储过程类似，具有执行速度快、减少网络流量、增强代码的重用性和共享性等优点7.3.3函数与存储过程如果存储过程返回单个标量值，则使用标量函数更有优势；如果存储过程返回单个结果集，则可以使用表值函数来替代。7.3.4用T-SQL语句创建函数1．创建标量函数CREATEFUNCTION<函数名>([{参数名参数数据类型

[=默认值]}

[,...n]

])RETURNS返回的数据类型

[WITHENCRYPTION]

[AS]

BEGIN

函数体

RETURN<表达式>

END例:创建内连标量函数，返回给定日期的月信息。CREATEFUNCTIONgetMonth--函数名(@datedatetime)---参数RETURNSint--返回类型

AS

BEGIN

RETURNDATEPART(MM,@date)---返回值

END2．创建内连表值函数CREATEFUNCTION<函数名>([{参数名参数数据类型

[=默认值]}

[,...n]

])RETURNSTABLE

[WITHENCRYPTION]

[AS]

RETURNSELECT语句例7-15用函数返回指定读者的所有借书（未还）记录

CREATEFUNCTIONgetBorrow(@cardidchar(14))RETURNSTABLEASRETURNSELECT*FROMborrowWHEREcardid=@cardid----返回结果3．创建多语句表值函数CREATEFUNCTION<函数名>([{参数名参数数据类型

[=默认值]}

[,...n]

])RETURNS@return_variableTABLE<table_type_definition>

[WITHENCRYPTION]

[AS]

BEGIN

函数体

RETURN

END7.3.5删除函数格式：DROPFUNCTION<函数名>7.3.6在SManagementStudio中修改函数在“对象资源管理器”中,依次展开“数据库”节点、用户数据库（本例为BOOKSYS）、“可编程性”、“函数”，根据函数类型选择“表值函数”或“标量函数”结点，在该节点下可以看到用户创建的所有相关函数，选择要修改的函数.右击函数名，在弹出的菜单中选择“修改”命令.7.3.7函数的调用1．标量函数调用SQLSERVER2005中，标量函数可以直接调用，凡是可以出现表达式的地方都可以调用标量函数。标量函数的调用语法为：<架构名>.函数名2．表值函数调用SQLSERVER2005中，表值函数返回的是结果集，在T-SQL中可以象访问表或视图一样调用表值函数。7.3.8常用内置函数1．日期与时间函数（1）GETDATE函数：GETDATE（）没有输入参数，返回当前系统日期和时间（2）DATEPART函数：DATEPART（datepart,date），返回date参数指定的日期中的datepart参数指定的日期部份的整数.（3）DATEDIFF函数：DATEDIFF(datepart,startdate,enddate)，返回enddate和startdate表示的两个日期之差，结果由参数datepart决定

1．日期与时间函数(续)（4）DATEADD函数：DATEADD(datepart,number,date)，返回参数date指定的日期上增加number后得到的新的日期

（5）YEAR、MONTH、DAY函数：它们均接收一个日期参数，返回给定日期中年、月、日

（6）DATENAME函数：DATENAME(datepart,date)，返回指定日期的指定日期部份的字符串。2．字符串函数ASCIINCHARSOUNDEXCHARPATINDEXSPACECHARINDEXQUOTENAMESTRDIFFERENCEREPLACESTUFFLEFTREPLICATESUBSTRINGLENREVERSEUNICODELOWERRIGHTUPPERLTRIMRTRIM

3．数学函数SQLServer2005提供了大量的数学函数，参考教材表7-5。7.4触发器

触发器是一种特殊的存储过程，当在指定的数据表进行行插入、修改或删除操作时被自动调用

7．4．1．触发器概述1．触发器的概念触发器（Trigger）是用户对某一表中的数据做插入、更新和删除操作时被触发执行的一段程序.通常我们使用触发器来检查用户对表的操作是否符合整个应用系统的需求及是否符合商业规则，以维持表内数据的完整性和正确性。

2．与触发器有关的两个特殊表SQLServer为每个触发器创建了两个专用临时表：INSERTED表和DELETED表,它们存放于内存中，而不是存放在数据库中。这两个表的结构总是与被该触发器作用的表的结构相同

（1）INSERTED表：存放由于INSERT或UPDATE语句的执行而导致要加到该触发器作用的表中去的所有新行

（2）DELETED表：存放由于DELETE或UPDATE语句的执行而导致要从被该触发器作用的表中删除的所有行。即把被作用表中要删除或要更新的旧值移到DELETED表中3．触发器的分类（1）DML触发器：DML触发器是当数据库中发生数据操作语言（DML）事件时要执行的操作

DML触发器包括两种类型，AFTER触发器和INSTEADOF触发器.

（2）DDL触发器：DDL触发器是SQLServer2005新增的功能。它是一种特殊的触发器，在响应数据定义语言（DDL）时触发

4．创建触发器的T-SQL语CREATETRIGGER<触发器名>ON<表名|视图名>[WITHENCRYPTION]<FOR|AFTER|INSTEADOF><[INSERT][,][UPDATE][,][DELETE]>AS[IFUPDATE(列名)]<SQL语句[...n]>WITHENCRYPTION选项加密触发器代码，保护作者的知识产权UPDATE(列名)是一个函数，用于测试是否对表或视图的指定列进行了INSERT或UPDATE操作7.4.2AFTER触发器AFTER触发器是在执行INSERT、UPDATE、DELETE任一操作之后被触发。AFTER触发器只能在表上定义AFTER触发器是在执行INSERT、UPDATE、DELETE任一操作之后被触发。AFTER触发器只能在表上定义1．INSERT触发器INSERT触发器由INSERT语句触发，即用户在表中插入一条记录且插入成功时，触发INSERT触发器例7-17创建一触发器以实现如下功能：当往BORROW表中插入一条记录时，如果书号或卡号不存在，则撤消插入INSERT触发器的工作过程如下：（1）用户或系统运行INSERT语句。（2）如果记录不违反限制，则将记录插入到临时表INSERTED中。（3）触发触发器。（4）如果触发器执行完毕而无错误，则INSERTED表被删除，插入操作完成。

2．DELETE触发器DELETE触发器由DELETE语句触发，即用户在表中删除一条记录时，触发DELETE触发器.例7-18创建一触发器以实现如下功能：当试图删除BORROW表中的一条记录时，若还书日期为空或还书日期距今还不到半年，则撤消事务.DELETE触发器的工作过程如下：（1）用户或系统运行DELETE语句。（2）如果记录不违反外部关键字限制，则删除表中的记录并将其插入到临时表DELETED中。（3）触发触发器。（4）如果触发器执行完毕而无错误，则DELETED表被删除，删除操作完成。

3．UPDATE触发器更新操作可以看成一个删除加一个插入：删除旧值和插入新值。例7-19创建一触发器以实现如下功能：如果要更改READER表中的CARDID，则先检查BORROW表中是否有记录引用了该CARDID