数据库系统及应用（第5版）课件 第8章 数据库编程基础

T-SQL数据库编程基础T-SQL简介游标与SQL的宿主使用存储过程触发器及其用途动态SQLT-SQL简介SQLServer使用的语言称作Transact-SQL(简称T-SQL)，它不仅包括基本SQL操作和查询的内容(第3章介绍的关系数据库标准语言SQL)，还有一般程序设计的能力。数据类型分类数据类型数值类型整型int,bigint,smallint,tinyint定点数字类型decimal(p,q),numeric(p,q)浮点数字类型float,real货币类型money,smallmoney日期和时间类型datetime,smalldatetime字符串类型ASCIIchar(n),

varchar(n),

textUnicodenchar(n),

nvarchar(n),

ntext二进制类型binary,varbinary,varbinary(max)图像类型image全局唯一标识符uniqueidentifierXML类型xml特殊bit,cursor,timestamp,sysname,table,sql_variant变量

SQLServer允许使用局部变量和全局变量，局部变量用DECLARE语句说明，而全局变量由系统预先定义和维护。局部变量说明DECLARE@<变量名><数据类型>[,@<变量名><数据类型>...]注意：@前缀全局变量全局变量是SQLServer预定义的，用于返回有关配置设置和系统运行的信息，全局变量以@@做前缀。例如，全局变量@@SERVERNAME返回当前服务器的名称，全局变量@@FETCH_STATUS返回当前读游标记录的状态信息。运算符与表达式T-SQL支持算术运算、位运算、比较运算、逻辑运算和字符串运算等常规运算。常量、变量、字段名或函数通过与运算符的有机结合可以构成各类表达式函数T-SQL提供了大量的函数供用户使用。常用函数名称格式功能系统日期函数GETDATE()返回当前系统日期年函数YEAR(日期表达式)返回年的四位整数。月函数MONTH(日期表达式)返回1～12之间的整数，表示一年的某月日函数DAY(日期表达式)返回值为1～31之间的整数，表示日期中的某一天。取子串函数SUBSTRING(字符串表达式,起始位置,长度)返回字符或表达式的一部分。截取左子串函数LEFT(字符串表达式，数值表达式)从“字符串表达式”左侧第1个字符开始，截取“数值表达式”值个字符。截取右子串函数RIGHT(字符串表达式，数值表达式)从“字符串表达式”的最右端的第1个字符开始，截取“数值表达式”值个字符类型转换函数CAST(表达式AS

数据类型)将某种数据类型的表达式显式转换为另一种数据类型。CAST和CONVERT提供相似的功能。CONVERT(数据类型[(长度)],表达式)赋值语句有SET和SELECT两种赋值语句可以给局部变量赋值。使用SET命令对变量赋值的语句格式是

SET@local_variable=expression使用SELECT命令对变量赋值的语句格式是

SELECT@local_variable=expression[,@local_variable=expression…]或SELECT@local_variable=expression[,@local_variable=expression…]FROM<表名>|<视图名>…复合语句BEGIN…END条件语句IFBoolean_expression sql_statement[ELSE sql_statement]循环语句WHILEBoolean_expression sql_statement在循环体中可以使用BREAK语句强制跳出循环（转到循环语句后的语句），可以使用CONTINUE语句强制开始下一次循环（转到循环体的第一条语句）。WAITFOR语句WAITFOR语句通常用于事务控制，例如当已超过指定的时间间隔或到达一天中指定的时间再恢复程序的运行。例1：使程序延迟10秒执行WAITFORDELAY'00:00:10'例2：使程序等到14时20分再开始执行WAITFORTIME'14:20'PRINT语句PRINTmsg_str|@local_variable|string_exprmsg_str是字符串常量，@local_variable可以是任何有效的字符数据类型变量（或者能够隐式转换为字符数据类型的变量），string_expr是字符串表达式。只能输出一个字符串如何输出非字符串？如数值如何输出多个值？RAISERROR语句RAISERROR语句可以根据出错类型输出用户定义的出错信息。RETURN语句RETURN语句为返回语句，它无条件终止当前的查询、存储过程或批处理，返回调用程序。游标游标游标当前行指针游标结果集游标

游标可以看作是临时文件DECLARECURSOR——声明游标（文件）OPEN——打开游标（文件）FETCH——提取游标（文件）CLOSE——关闭游标（文件）DEALLOCATE

——释放游标（文件）声明用来存储数据的变量开始声明游标打开游标提取完毕？提取数据关闭游标释放游标结束否是例1在T-SQL中用游标列出所有仓库记录声明游标

游标可以看作是一个临时存储或临时文件，它的内容就是SELECT语句的查询结果。

基本格式

DECLARE<游标名>CURSORFOR<SELECT-查询>声明用来存储数据的变量开始声明游标打开游标提取完毕？提取数据关闭游标释放游标结束否是例1在T-SQL中用游标列出所有仓库记录DECLAREwh_cursorCURSORFORSELECT仓库号,城市,面积FROM仓储.仓库打开游标该游标必须是用DECLARECURSOR语句已经定义好的。执行该语句意味着执行在DECLARECURSOR语句中定义的SELECT查询，并使游标指针指向查询结果的第一条记录。只能打开已经声明但还没有打开的游标。

基本格式

OPEN<游标名>声明用来存储数据的变量开始声明游标打开游标提取完毕？提取数据关闭游标释放游标结束否是例1在T-SQL中用游标列出所有仓库记录DECLAREwh_cursorCURSORFORSELECT仓库号,城市,面积

FROM仓储.仓库OPENwh_cursor从游标中读记录

这里的游标必须是已经说明并打开了的，INTO后的主变量要与在DECLARECURSOR中SELECT的字段相对应。该语句的功能是取出游标的当前记录并送入主变量，同时使游标指针指向下一条记录。

基本格式

FETCH<游标名>[INTO<主变量1>,<主变量2>…]声明用来存储数据的变量开始声明游标打开游标提取完毕？提取数据关闭游标释放游标结束否是在T-SQL中使用游标使用全局变量@@FETCH_STATUS来判断FETCH语句对游标的操作状态，取值如下：0：FETCH语句成功；-1：FETCH语句失败或行不在结果集中；-2：提取的行不存在。循环语句控制是否继续读取游标WHILE@@FETCH_STATUS=0例1在T-SQL中用游标列出所有仓库记录DECLARE@whnoCHAR(6),@cityCHAR(10),@areaINTDECLAREwh_cursorCURSORFORSELECT仓库号,城市,面积FROM仓储.仓库OPENwh_cursorFETCHFROMwh_cursorINTO@whno,@city,@area例1在T-SQL中用游标列出所有仓库记录DECLARE@whnoCHAR(6),@cityCHAR(10),@areaINTDECLAREwh_cursorCURSORFORSELECT仓库号,城市,面积FROM仓储.仓库OPENwh_cursorFETCHFROMwh_cursorINTO@whno,@city,@areaWHILE@@FETCH_STATUS=0BEGIN FETCHFROMwh_cursorINTO@whno,@city,@areaEND例1在T-SQL中用游标列出所有仓库记录DECLARE@whnoCHAR(6),@cityCHAR(10),@areaINTDECLAREwh_cursorCURSORFORSELECT仓库号,城市,面积FROM仓储.仓库OPENwh_cursorFETCHFROMwh_cursorINTO@whno,@city,@areaWHILE@@FETCH_STATUS=0BEGIN FETCHFROMwh_cursorINTO@whno,@city,@areaEND例1在T-SQL中用游标列出所有仓库记录DECLARE@whnoCHAR(6),@cityCHAR(10),@areaINTDECLAREwh_cursorCURSORFORSELECT仓库号,城市,面积FROM仓储.仓库OPENwh_cursorFETCHFROMwh_cursorINTO@whno,@city,@areaWHILE@@FETCH_STATUS=0BEGIN FETCHFROMwh_cursorINTO@whno,@city,@areaEND例1在T-SQL中用游标列出所有仓库记录DECLARE@whnoCHAR(6),@cityCHAR(10),@areaINTDECLAREwh_cursorCURSORFORSELECT仓库号,城市,面积FROM仓储.仓库OPENwh_cursorFETCHFROMwh_cursorINTO@whno,@city,@areaWHILE@@FETCH_STATUS=0BEGIN FETCHFROMwh_cursorINTO@whno,@city,@areaEND例1在T-SQL中用游标列出所有仓库记录DECLARE@whnoCHAR(6),@cityCHAR(10),@areaINTDECLAREwh_cursorCURSORFORSELECT仓库号,城市,面积FROM仓储.仓库OPENwh_cursorPRINT'--------仓库列表--------'PRINT'仓库号所在城市仓库面积'FETCHFROMwh_cursorINTO@whno,@city,@areaWHILE@@FETCH_STATUS=0BEGIN PRINT@whno+''+@city+STR(@area,4) FETCHFROMwh_cursorINTO@whno,@city,@areaEND关闭游标

在使用CLOSE语句关闭某游标后，系统并没有完全释放游标的资源，并且也没有改变游标的定义，当再次使用OPEN语句时可以重新打开此游标。

基本格式

CLOSE<游标名>声明用来存储数据的变量开始声明游标打开游标提取完毕？提取数据关闭游标释放游标结束否是例1在T-SQL中用游标列出所有仓库记录DECLARE@whnoCHAR(6),@cityCHAR(10),@areaINTDECLAREwh_cursorCURSORFORSELECT仓库号,城市,面积FROM仓储.仓库OPENwh_cursorPRINT'--------仓库列表--------'PRINT'仓库号所在城市仓库面积'FETCHFROMwh_cursorINTO@whno,@city,@areaWHILE@@FETCH_STATUS=0BEGIN PRINT@whno+''+@city+STR(@area,4) FETCHFROMwh_cursorINTO@whno,@city,@areaENDCLOSEwh_cursor释放游标

释放游标就释放了与该游标有关的一切资源，包括游标的声明，以后就不能再使用OPEN语句打开此游标了。

基本格式DEALLOCATE<游标名>声明用来存储数据的变量开始声明游标打开游标提取完毕？提取数据关闭游标释放游标结束否是例1在T-SQL中用游标列出所有仓库记录DECLARE@whnoCHAR(6),@cityCHAR(10),@areaINTDECLAREwh_cursorCURSORFORSELECT仓库号,城市,面积FROM仓储.仓库OPENwh_cursorPRINT'--------仓库列表--------'PRINT'仓库号所在城市仓库面积'FETCHFROMwh_cursorINTO@whno,@city,@areaWHILE@@FETCH_STATUS=0BEGIN PRINT@whno+''+@city+STR(@area,4) FETCHFROMwh_cursorINTO@whno,@city,@areaENDCLOSEwh_cursorDEALLOCATEwh_cursor游标概念归纳与程序设计语言中的文件相对照DECLARECURSOR相当于说明了一个文件OPEN相当于打开文件FETCH相当于读一条记录CLOSE相当于关闭文件DEALLOCATE相当于删除文件。例2：在T-SQL中使用游标列出每个仓库及其职工的工资信息。游标可以嵌套例2：在T-SQL中使用游标列出每个仓库及其职工的信息。游标可以嵌套游标可以嵌套DECLARE@whnoCHAR(6),@cityCHAR(10)DECLARE@enoCHAR(8),@salarynumeric(8,2)DECLAREwh_cursorCURSORFORSELECT仓库号,城市FROM仓储.仓库--说明游标wh_cursorOPENwh_cursor--打开游标wh_cursorFETCHFROMwh_cursorINTO@whno,@city--从游标读数据WHILE@@FETCH_STATUS=0--用@@FETCH_STATUS判断FETCH语句是否成功BEGINPRINT'仓库：'+@whno+'位于'+@city+'有职工‘--*************************************DECLAREemp_cursorCURSORFORSELECT职工号,工资FROM基础.职工WHERE仓库号=@whno--说明嵌套的内层游标emp_cursorOPENemp_cursor--打开游标emp_cursorFETCHFROMemp_cursorINTO@eno,@salary--从emp_cursor游标读数据WHILE@@FETCH_STATUS=0--用@@FETCH_STATUS判断FETCH语句是否成功BEGINPRINT@eno+'的工资是'+str(@salary,8,2)FETCHFROMemp_cursorINTO@eno,@salary--读下一条职工信息ENDCLOSEemp_cursor--关闭内层游标emp_cursorDEALLOCATEemp_cursor--释放内层游标emp_cursor--**************************************FETCHFROMwh_cursorINTO@whno,@city--读下一条仓库信息（外层游标）ENDCLOSEwh_cursor--关闭游标wh_cursorDEALLOCATEwh_cursor--释放游标wh_cursor游标可以嵌套DECLARE@whnoCHAR(6),@cityCHAR(10)DECLARE@enoCHAR(8),@salarynumeric(8,2)DECLAREwh_cursorCURSORFORSELECT仓库号,城市FROM仓储.仓库--说明游标wh_cursorOPENwh_cursor--打开游标wh_cursorFETCHFROMwh_cursorINTO@whno,@city--从游标读数据WHILE@@FETCH_STATUS=0--用@@FETCH_STATUS判断FETCH语句是否成功BEGINPRINT'仓库：'+@whno+'位于'+@city+'有职工‘--*************************************DECLAREemp_cursorCURSORFORSELECT职工号,工资FROM基础.职工WHERE仓库号=@whno--说明嵌套的内层游标emp_cursorOPENemp_cursor--打开游标emp_cursorFETCHFROMemp_cursorINTO@eno,@salary--从emp_cursor游标读数据WHILE@@FETCH_STATUS=0--用@@FETCH_STATUS判断FETCH语句是否成功BEGINPRINT@eno+'的工资是'+str(@salary,8,2)FETCHFROMemp_cursorINTO@eno,@salary--读下一条职工信息ENDCLOSEemp_cursor--关闭内层游标emp_cursorDEALLOCATEemp_cursor--释放内层游标emp_cursor--**************************************FETCHFROMwh_cursorINTO@whno,@city--读下一条仓库信息（外层游标）ENDCLOSEwh_cursor--关闭游标wh_cursorDEALLOCATEwh_cursor--释放游标wh_cursor游标可以嵌套例2：在T-SQL中使用游标列出每个仓库及其职工的信息。游标可以嵌套游标可以嵌套游标可以嵌套注意@FETCH_STATUS的使用课后思考题存储过程的创建与使用@parameter：给出参数名（注意需要使用@做前缀）创建和执行存储过程CREATEPROCEDURE[schema_name.]procedure_name[@parameterdata_type[VARYING][=default][OUT|OUTPUT],…]ASsql_statementdata_type：指出参数的数据类型。

VARYING：指定作为输出参数支持的结果集，该参数由存储过程动态构造，其内容可能发生改变，仅适用于cursor（游标）类型的参数。

=default：给出参数的默认值，该值必须是常量或NULL。

OUT|OUTPUT：指示参数是输出参数。data_type：指出参数的数据类型。

VARYING：指定作为输出参数支持的结果集，该参数由存储过程动态构造，其内容可能发生改变，仅适用于cursor（游标）类型的参数。

=default：给出参数的默认值，该值必须是常量或NULL。

OUT|OUTPUT：指示参数是输出参数。创建存储过程通常是在数据库设计和开发阶段完成的；存储过程可以嵌套，即在一个存储过程中可以调用另外一个存储过程；在存储过程中不可以使用创建数据库对象的语句（例如CREATETABLE等各类CREATE语句）。存储过程一般用来完成数据查询和数据处理操作；几点说明@return_status：用于存放存储过程返回的状态。这个变量在执行存储过程前必须说明过。执行存储过程

EXECUTE[@return_status=][schema_name.]procedure_name[@parameter=]{value|@variable[OUTPUT]}[,...n]@parameter：存储过程中定义的参数。procedure_name：要执行或调用的存储过程名。value：传递给存储过程的参数值。如果参数名称没有指定，参数值必须以在存储过程中定义的顺序提供；如果在存储过程中定义了默认值，则可以不必指定参数。@parameter：存储过程中定义的参数。@variable：用来存储参数或返回参数的变量。存储过程的修改和删除ALTERPROCEDURE，格式与CREATEPROCEDURE命令的格式类似。DROPPROCEDURE[schema_name.]procedure_name修改存储过程删除存储过程操作存储过程应用举例例8-5.使用简单存储过程（不使用任何参数）查询所有职工信息（姓名、仓库号、工资、所在城市）例8-6.使用带有参数的简单过程返回指定城市的职工信息例8-7.使用带有参数和返回值的简单过程查询指定仓库订单金额大于指定值的订单数，查询结果通过RETURN语句返回例8-5.使用简单过程（不使用任何参数）

CREATEPROCEDUREuspGetAllEmpASSELECT姓名,仓库.仓库号,工资,城市AS所在城市

FROM仓储.仓库JOIN基础.职工

ON仓库.仓库号=职工.仓库号EXECUTEuspGetAllEmp查询所有职工信息（姓名、仓库号、工资、所在城市）

例8-6.使用带有参数的简单过程CREATEPROCEDUREuspGetEmp1@citychar(10)ASSELECT*FROM基础.职工WHERE仓库号IN(SELECT仓库号FROM仓储.仓库

WHERE城市=@city)*为得到“北京”的职工信息，可通过执行存储过程uspGetEmp1：EXECUTEuspGetEmp1@city='北京’或EXECUTEuspGetEmp1'北京'返回指定城市的职工信息操作例8-7.使用带有参数和返回值的简单过程CREATEPROCEDUREuspGetOrderNum@whnochar(6),@summoneyASDECLARE@countintSELECT@count=COUNT(*)FROM订货.订购单WHERE金额>=@sumAND经手人IN(SELECT职工号FROM基础.职工WHERE仓库号=@whno)RETURN@count查询指定仓库订单金额大于指定值的订单数，查询结果通过RETURN语句返回例8-7.使用带有参数和返回值的简单过程调用存储过程uspGetOrderNum查询WH1仓库金额大于1000的订单数：DECLARE@countintEXECUTE@count=uspGetOrderNum'WH1',1000PRINT'WH1仓库金额在1000以上的订单数是:'+STR(@count)查询指定仓库订单金额大于指定值的订单数，查询结果通过RETURN语句返回操作触发器的基本概念触发器可以看作是一类特殊的存储过程。基本特点：在满足某个特定条件时自动触发执行。触发器和存储过程同是提高数据库服务器性能的有力工具。触发器概念触发器是一种程序或一种过程，它和存储过程一样是事先设计好并存储在数据库中的，与存储过程不同的是触发器不需要专门调用或执行，触发器是在某个特定条件发生时自动触发执行的。

触发器概念

插入触发器删除触发器更新触发器定义触发器的表（或视图）激活触发器的数据操作语句采取的动作触发器概念DML触发器分为触发器的三个要素DML触发器也是一个数据库对象，但DML触发器依附于表（或视图）。类型1类型2类型3DML(数据操作语言)触发器DDL(数据定义语言)触发器LOGIN触发器DML触发器分类在执行数据操作语言（Update、Insert和Delete）时触发

DDL触发器在执行数据定义语言（Create）时触发LOGIN触发器在有用户登录（Login）时触发建立触发器CREATETRIGGER[schema_name.]trigger_nameON{table|view}{FOR|INSTEADOF}{[INSERT][,][UPDATE][,][DELETE]}--至少选一项。ASsql_statement

ON指出定义触发器的表或视图，视图只能被INSTEADOF

触发器引用FORINSERT、UPDATE或DELETE指出触发事件INSTEADOF指出用触发器取代触发动作的语句，即不执行触发触发器的数据操作语句，而由触发器的语句完成有关操作sql_statement：触发器所采取的操作删除触发器DROPTRIGGER[schema_name.]trigger_nameON指出定义触发器的表或视图，视图只能被INSTEADOF

触发器引用FORINSERT、UPDATE或DELETE指出触发事件INSTEADOF指出用触发器取代触发动作的语句，即不执行触发触发器的数据操作语句，而由触发器的语句完成有关操作sql_statement：触发器所采取的操作第二步第三步第一步创建触发器执行插入语句重新创建触发器重点一体会触发器FOR和INSTEADOF的区别CREATETRIGGERwh_trigger1ON仓储.仓库INSTEADOFINSERTASPRINT'INSTEADOF触发器'第一步操作第二步第三步第一步创建触发器执行插入语句重新创建触发器重点一体会触发器FOR和INSTEADOF的区别INSERTINTO仓储.仓库

VALUES('WH8','杭州',450)问：结果WH8的仓库记录插入了吗？第二步操作第二步第三步第一步创建触发器执行插入语句C重新创建触发器重点一体会触发器FOR和INSTEADOF的区别DROPTRIGGER仓储.wh_trigger1CREATETRIGGERwh_trigger1ON仓储.仓库FORINSERTASPRINT'插入了一个仓库元组’再执行插入语句

INSERTINTO仓储.仓库VALUES('WH8',’杭州',450)结果WH8的仓库记录插入了吗？第三步操作For和Insteadof的区别，案例中前者触发时执行for后的插入操作，后者虽触发了插入触发器，但插入操作未执行。案例总结sqlserver上允许为每类数据操作定义多个触发器，当发生相应操作时，依次触发相应触发器。原则上，DML触发器在一个表上一种类型的触发器只能定义一个。重点一体会触发器FOR和INSTEADOF的区别第一步第二步第三步CREATETRIGGERwh_trigger1ON仓储.仓库INSTEADOFINSERTASPRINT'INSTEADOF触发器'INSERTINTO仓储.仓库VALUES('WH8','杭州',450)问：结果WH8的仓库记录插入了吗？DROPTRIGGER仓储.wh_trigger1CREATETRIGGERwh_trigger1ON仓储.仓库FORINSERTASPRINT'插入了一个仓库元组’再执行插入语句INSERTINTO仓储.仓库

VALUES('WH8','杭州',450)结果WH8的仓库记录插入了吗？当发生INSERT操作时，新插入的记录不仅添加在表中，也添加到在inserted表中；当发生DELETE操作时，被删除的记录传输到deleted表中；当发生UPDATE操作时，修改前的旧记录存储在deleted表中，修改后的新记录存储在inserted表中。deleted表和inserted表当DML触发器激活时系统会自动创建并管理两个特殊的临时表：deleted表和inserted表

DML触发器也是一个数据库对象，但DML触发器依附于表（或视图）。DML触发器分为插入触发器、删除触发器和更新触发器三类DML触发器第三步操作deleted表和inserted表的作用030201可以使用deleted表和inserted表判断正在操作的记录是否符合要求，从而检查错误并采取相应的措施；找出数据修改前、后表的状态差异，并基于该差异采取相应的措施；可以扩展表之间的参照完整性。注

deleted表和inserted表只在触发器内可用，一旦触发器完成任务，这两个系统产生的临时表将自动删除