数据库应用基础（SQL Server 2019）（AIGC赋能版） 课件 项目7 Transact-SQL编程基础

项目7Transact-SQL编程基础《数据库应用基础（SQLServer2019）》项目目标理解Transact-SQL基本组成掌握流程控制语句的使用方法掌握函数的使用方法掌握游标的使用方法掌握处理事务的方法本章目录7.1理解Transact-SQL7.2使用流程控制语句7.3使用函数7.4使用游标7.5处理事务理解Transact-SQL7.1任务7.1理解Transact-SQLTransact-SQL语言是SQLServer系统的核心组件。与SQLServer实例通信的所有应用程序可以通过向服务器发送Transact-SQL语句来实现数据访问，并对存储在数据库中的数据进行查询和更新。通过本任务将学习和理解Transact-SQL语言的基本组成。任务目标理解Transact-SQL语言组成理解批处理和脚本的概念理解常量、变量和表达式的概念理掌握注释语句的使用方法7.1.1Transact-SQL语言组成Transact-SQL语言组成组成部分功能主要语句数据定义语言（DDL）创建、修改、删除数据库对象CREATE,ALTER,DROP数据操作语言（DML）添加、修改、删除、检索数据INSERT,UPDATE,DELETE,SELECT数据控制语言（DCL）安全管理（授权、收回、拒绝）GRANT,REVOKE,DENY事务管理语言（TCL）事务开始、提交、回滚BEGINTRANSACTION,COMMITTRANSACTION,ROLLBACKTRANSACTION附加语言元素标识符、变量、常量、运算符、函数、流程控制、错误处理、注释—7.1.2批处理与脚本批处理：包含一个或多个T-SQL语句的组合，一次发送到服务器编译为单个执行计划GO命令：向SSMS或sqlcmd发出批处理结束的信号，不是T-SQL语句；可后跟正整数指定执行次数错误类型：编译错误（如语法错误）：整个批处理不执行运行时错误（如违反约束）：多数停止执行当前及后续语句；某些仅停止当前语句，

其他继续批处理规则：CREATE类语句（如CREATEVIEW）不能与其他语句组合在同一批处理中，

必须以该语句开头不能在同一个批处理中更改表后立即引用新列EXECUTE若不是批处理第一条语句，则必须使用该关键字脚本：存储在.sql文件中的一系列T-SQL语句，可包含多个批处理（由GO分隔）7.1.3标识符创建数据库对象时需要使用标识符对其进行命名常规标识符：符合标识符规则，使用时无需分隔分隔标识符：用双引号"或方括号[]括起，用于不符合规则的名称长度：1~128字符（本地临时表最多116字符）完全限定名称：服务器.数据库.架构.对象；可省略部分限定符服务器.数据库.架构.对象服务器.数据库..对象服务器..架构.对象服务器...对象数据库.架构.对象数据库..对象架构.对象对象7.1.4常量（字面量）常量格式示例常量类型格式示例字符串'数据库应用基础'，嵌入单引号用两个：'O''Brien'Unicode字符串N'Michél'（N前缀必须大写）二进制0xAE,0x12f（十六进制，无引号）bit0或1datetime'2019-10-01','20:30:12'integer123,2020decimal1894.12float/real101.5E5,0.5E-2money$12,$542023.14（前缀$）uniqueidentifier'6F9619FF-8B86-D011-B42D-00C04FC964FF'正负数+145,-21474836487.1.5局部变量声明

DECLARE@变量名数据类型必须以@开头；不能是text/ntext/image赋值SET@变量名=表达式（首选）SELECT@变量名=表达式可一次赋值多个，若返回多值则取最后一个显示SELECT@变量名→结果在“结果”窗格PRINT@变量名→消息在“消息”窗格

变量需为char/varchar或可隐式转换作用域声明所在的批处理或存储过程内7.1.6表达式定义标识符、值和运算符的组合，可求值运算符分类优先级遵循T-SQL运算符优先级规则类别运算符比较运算符=,>,<,>=,<=,<>,!=,!>,!<逻辑运算符ALL,AND,ANY,BETWEEN,EXISTS,IN,LIKE,NOT,OR算术运算符+,-,*,/,%一元运算符+（正）,-（负）,~（位非）位运算符&（与）,|（或）,^（异或）,~（非）字符串串联+赋值运算符=7.1.7空值（NULL）含义值未知，不同于空白或0测试空值ISNULL/ISNOTNULL（不能用=）三值逻辑比较结果可为TRUE、FALSE或UNKNOWNANSI_NULLS设置：ON（默认）：任何含NULL的比较返回UNKNOWNOFF：NULL=NULL返回TRUE建议尽量少用空值，设计时考虑其影响7.1.8注释语句行内注释：从--到行尾；可单独一行或跟在代码后

--注释文字 块注释：可跨多行；支持嵌套（需成对匹配）

/*注释文字*/ 示例--查询学生表SELECT*FROM学生;--星号表示所有列/***多行注释示例**可以描述复杂逻辑*/使用流程控制语句7.2任务7.2使用流程控制语句默认情况下脚本中的各个Transact-SQL语句按其出现的顺序依次执行。如果需要按照指定的条件进行控制转移或重复执行某些操作，则应该通过流程控制语句来实现。通过本任务将学习和使用流程控制语句的使用方法。任务目标掌握BEGIN...END的使用方法掌握IF...ELSE语句的使用方法掌握CASE函数的使用方法掌握WAITFOR语句的使用方法掌握WHILE语句的使用方法掌握TRY...CATCH语句的使用方法7.2.1BEGIN...END语句功能：将多条T-SQL语句组合成一个语句块（复合语句）语法：BEGIN{语句|语句块}END说明：必须成对使用；可嵌套；常用于WHILE、IF...ELSE、CASE中需要多条语句的场景示例（IF中使用BEGIN...END）：IF@@ERROR<>0BEGINPRINT'发生错误';END7.2.2IF...ELSE语句（1/2）功能条件执行，满足条件时执行一组语句，否则执行另一组语法：IF布尔表达式

{语句|语句块}[ELSE{语句|语句块}]说明布尔表达式返回TRUE/FALSE；可使用BEGIN...END定义语句块7.2.2IF...ELSE语句（2/2）例7.2：根据课程名称判断是否存在，不存在则添加，存在则显示课时IFNOTEXISTS(SELECT*FROM课程WHERE课程名称=@cname)BEGININSERTINTO课程VALUES(@cname,'专业基础课',64);PRINT'课程添加成功！';ENDELSEBEGINSELECT@chour=课时FROM课程WHERE课程名称=@cname;PRINT'课程已存在，课时数为'+CAST(@chourASchar(3));END7.2.3CASE函数功能计算条件列表并返回多个可能结果之一（不是语句，是表达式）两种格式例7.3：简单CASE实现交叉表查询SELECT学号,姓名,SUM(CASE课程名称WHEN'数学'THEN成绩ELSE0END)AS[数学],SUM(CASE课程名称WHEN'语文'THEN成绩ELSE0END)AS[语文]FROM学生成绩视图GROUPBY学号,姓名;格式语法特点适用场景简单CASE函数CASE输入表达式WHEN匹配表达式THEN结果...END单一字段值与多个常量比较CASE搜索函数CASEWHEN布尔表达式THEN结果...END复杂条件判断7.2.3CASE函数例7.4：CASE搜索函数将百分制转为等级制SELECT学号,姓名,CASEWHEN成绩<60THEN'不及格'WHEN成绩BETWEEN60AND69THEN'及格'WHEN成绩BETWEEN70AND79THEN'中等'WHEN成绩BETWEEN80AND89THEN'良好'WHEN成绩>=90THEN'优秀'ENDAS成绩等级FROM学生成绩视图;7.2.4WAITFOR语句功能：延迟执行批处理、存储过程或事务语法WAITFOR{DELAY'等待时段'|TIME'完成时间'}DELAY：指定等待的时间间隔（最长24小时）TIME：指定执行的具体时间点（不能包含日期）示例--延迟2小时后执行WAITFORDELAY'02:00';EXECUTEsp_helpdb;--在晚上11:20执行WAITFORTIME'23:20';EXECUTEsp_update_job...;7.2.5WHILE语句功能：重复执行语句块，直到条件为假语法WHILE布尔表达式

{语句|语句块|BREAK|CONTINUE}BREAK：退出最内层WHILE循环CONTINUE：重新开始循环，忽略其后语句例7.5：统计教师表中教师人数DECLARE@tidint=1,@countint=0;WHILEEXISTS(SELECT*FROM教师WHERE教师编号=@tid)BEGINSET@count=@count+1;SET@tid=@tid+1;ENDPRINT'教师总人数：'+CAST(@countASchar(2));7.2.6TRY...CATCH语句（1/3）功能：捕获并处理错误（类似C#/C++异常处理）语法：BEGINTRY{语句|语句块}ENDTRYBEGINCATCH{语句|语句块}ENDCATCH说明：TRY块必须紧跟CATCH块；可捕获严重级别>10且不终止连接的错误7.2.6TRY...CATCH语句（2/3）CATCH块中的系统函数ERROR_NUMBER()：错误号ERROR_MESSAGE()：完整错误文本ERROR_SEVERITY()：严重性ERROR_STATE()：状态号ERROR_PROCEDURE()：发生错误的存储过程/触发器名ERROR_LINE()：错误行号7.2.6TRY...CATCH语句（3/3）例7.6：捕获被零除错误BEGINTRYSELECT1/0;ENDTRYBEGINCATCHSELECTERROR_NUMBER()AS错误号,ERROR_MESSAGE()AS错误信息;ENDCATCH;使用函数7.3任务7.3使用函数Transact-SQL提供了大量的内置函数，可以用于执行特定操作。此外，Transact-SQL还允许用户定义自己所需要的函数。通过本任务将学会使用Transact-SQL内置函数和用户自定义函数来完成各种常见任务。任务目标理解Transact-SQL函数的用途和分类掌握字符串函数的使用方法掌握数学函数的使用方法掌握日期函数的的使用方法掌握转换函数的的使用方法掌握系统函数的的使用方法掌握创建和应用用户定义函数的方法7.3.1函数概述函数的用途：可在任何表达式中使用（SELECT列表、WHERE条件、视图、约束等）确定性分类：严格确定函数：相同输入始终返回相同结果确定函数：相同输入和数据库状态返回相同结果非确定函数：相同输入可能返回不同结果（如GETDATE()）限制：只有确定性函数可用于索引视图、计算列等函数类别：聚合、配置、加密、游标、日期时间、数学、元数据、排名、行集、安全、字符串、系统、系统统计、文本图像7.3.2字符串函数部分常用字符串函数函数功能示例ASCII返回最左侧字符ASCII码ASCII('A')→65CHARASCII码转字符CHAR(65)→'A'LEFT返回左边指定字符数LEFT('SQLServer',3)→'SQL'LEN返回字符数（不含尾随空格）LEN('数据库')→3LOWER/UPPER大小写转换LOWER('SQL')→'sql'LTRIM/RTRIM删除前导/尾随空格LTRIM('abc')→'abc'REPLACE替换子串REPLACE('abcb','b','#')→'a#c#'SUBSTRING返回子串SUBSTRING('abcde',2,3)→'bcd'STR数字转字符STR(3.14159,5,3)→'3.142'REVERSE反转字符串REVERSE('abc')→'cba'7.3.3数学函数部分常用数学函数函数功能示例ABS绝对值ABS(-5)→5CEILING向上取整CEILING(123.45)→124FLOOR向下取整FLOOR(123.45)→123POWER幂运算POWER(2,8)→256RAND返回0~1随机数RAND(100)ROUND四舍五入ROUND(123.9995,3)→124.0000SQRT平方根SQRT(16)→4SQUARE平方SQUARE(5)→257.3.4日期函数部分常用日期函数函数功能示例GETDATE当前日期时间（datetime）GETDATE()SYSDATETIME当前日期时间（datetime2(7)）SYSDATETIME()DATEADD日期加减DATEADD(day,10,GETDATE())DATEDIFF日期差值DATEDIFF(day,'2024-1-1',GETDATE())DATEPART返回日期部分（整数）DATEPART(year,GETDATE())YEAR/MONTH/DAY返回年月日YEAR(GETDATE())7.3.5转换函数隐式转换自动完成（如smallint→int）显式转换CAST和CONVERT样式参数表7.1：用于日期→字符串表7.2：float→字符串表7.3：money→字符串时的格式控制函数语法特点CASTCAST(表达式AS数据类型[(长度)])ANSI标准，样式固定CONVERTCONVERT(数据类型[(长度)],表达式[,样式])SQLServer特有，可指定样式（日期、货币等格式）7.3.6系统函数部分常用系统函数函数功能@@ERROR上一个语句的错误号@@IDENTITY最后插入的标识值@@ROWCOUNT上一语句影响的行数@@SERVERNAME本地服务器名称@@VERSIONSQLServer版本信息DB_ID()/DB_NAME()数据库ID/名称HOST_NAME()工作站名ISDATE()/ISNUMERIC()检查是否为有效日期/数值ISNULL()用指定值替换NULLNEWID()生成GUIDOBJECT_ID()/OBJECT_NAME()对象ID/名称USER_NAME()当前数据库用户名7.3.7用户定义函数（1/2）用户定义函数概述优点：模块化编程、执行速度快、减少网络流量四种类型：标量值函数：返回单个值（如int、varchar）内联表值函数：返回表，单个SELECT语句多语句表值函数：返回表，可包含多条语句CLR函数：基于.NET程序集函数结构：标题（名称、参数、返回类型）+正文（BEGIN...END）允许的语句：DECLARE、SET、游标操作（局部）、流程控制、SELECT（赋值）、INSERT/UPDATE/DELETE（局部表变量）、EXECUTE（扩展存储过程）不允许：TRY...CATCH、某些非确定性函数（如NEWID、RAND）7.3.7用户定义函数（2/2）创建标量值函数语法CREATEFUNCTION[架构.]函数名称

(@参数名数据类型[=默认值],...)RETURNS返回值数据类型[WITH{ENCRYPTION|SCHEMABINDING}]ASBEGIN

函数体

RETURN标量表达式END;调用：必须提供架构名，如SELECTdbo.MyFunc()修改：使用ALTERFUNCTION或SSMS“修改”命令删除：DROPFUNCTION[架构.]函数名称使用游标7.4任务7.4使用游标关系数据库中的操作会对整个行集起作用。由SELECT语句返回的行集包括满足WHERE子句中条件的所有行，这种由语句返回的完整行集称为结果集。应用程序尤其是交互式联机应用程序并不总能将整个结果集作为一个单元来有效地处理，这些应用程序需要一种机制，以便每次处理一行或一部分行。游标就是提供这种机制的对结果集的一种扩展。通过本任务将学习和掌握游标的使用方法。任务目标理解游标的基本概念掌握定义和打开游标的方法掌握通过游标提取和更新数据的方法掌握关闭和释放游标的方法7.4.1理解游标游标的作用：每次处理一行或一部分行，扩展结果集处理能力游标功能：定位在结果集的特定行从当前位置检索一行或一部分行支持对当前位置的行进行修改提供不同级别的数据更改可见性游标处理五步骤：定义游标（DECLARECURSOR）打开游标（OPEN）提取数据（FETCH）更改数据（UPDATE/DELETE...WHERECURRENTOF）关闭游标（CLOSE）并释放（DEALLOCATE）7.4.2定义游标（1/2）SQL-92语法（简化）：DECLARE游标名称[INSENSITIVE][SCROLL]CURSORFORSELECT语句[FOR{READONLY|UPDATE[OF列名[,...]]}]INSENSITIVE：创建临时表，不反映基表修改，不允许修改SCROLL：支持所有提取选项（FIRST、LAST、PRIOR、NEXT等）7.4.2定义游标（2/2）Transact-SQL扩展语法（简化）：DECLARE游标名称CURSOR[LOCAL|GLOBAL][FORWARD_ONLY|SCROLL][STATIC|KEYSET|DYNAMIC|FAST_FORWARD][READ_ONLY|SCROLL_LOCKS|OPTIMISTIC]FORSELECT语句[FORUPDATE[OF列名[,...]]]LOCAL：局部作用域（批处理/存储过程内）GLOBAL：全局作用域（连接内）FORWARD_ONLY：只进，仅支持NEXTSTATIC：静态游标（临时副本）DYNAMIC：动态游标，反映所有更改READ_ONLY：只读，不能更新SCROLL_LOCKS：锁定行确保更新成功OPTIMISTIC：乐观并发，使用timestamp检查7.4.3打开游标语法：OPEN[[GLOBAL]游标名称|游标变量名称]检查成功：@@ERROR=0表示成功获取行数：@@CURSOR_ROWS-m：异步填充，m为当前行数-1：动态游标，行数不定0：无打开游标或无符合条件的行n：已完全填充，n为总行数例7.14：定义并打开游标提取商2402班学生7.4.4通过游标提取数据（1/2）FETCH语法FETCH[[NEXT|PRIOR|FIRST|LAST|ABSOLUTE{n|@nvar}|RELATIVE{n|@nvar}]FROM][[GLOBAL]游标名称|@游标变量][INTO@变量名[,...]]提取选项NEXT：下一行（默认）PRIOR：上一行FIRST/LAST：第一行/最后一行ABSOLUTEn：绝对行号（正为从头，负为从尾）RELATIVEn：相对当前行的偏移7.4.4通过游标提取数据（2/2）状态检查：@@FETCH_STATUS0：成功；-1：失败或行不在结果集；-2：提取的行不存在例7.14：完整示例DECLAREstu_cursorCURSORFORSELECT学号,姓名,性别,出生日期FROM学生WHERE班级编号='商2402';OPENstu_cursor;FETCHNEXTFROMstu_cursor;WHILE@@FETCH_STATUS=0BEGINFETCHNEXTFROMstu_cursor;ENDCLOSEstu_cursor;DEALLOCATEstu_cursor;7.4.5通过游标更新数据前提：游标定义时必须包含FORUPDATE[OF列...]更新语法：UPDATE表名SET...WHERECURRENTOF游标名称删除语法：DELETEFROM表名WHERECURRENTOF游标名称例7.15：创建测试表，通过游标为每行成绩列设置不同的60~100随机整数定义可更新游标：FORUPDATEOF成绩循环中使用UPDATE测试表SET成绩=...WHERECURRENTOFscore_cursor7.4.6关闭和释放游标关闭游标（CLOSE）：

CLOSE{[GLOBAL]游标名称|游标变量名称}释放当前结果集，解除锁定保留数据结构，可重新打开释放游标（DEALLOCATE）：

DEALLOCATE{[GLOBAL]游标名称|@游标变量名称}删除游标引用，释放所有数据结构处理事务7.5任务7.5处理事务事务是作为单个逻辑工作单元执行的一系列操作。如果某一事务成功，则在该事务中进行的所有数据更改均会提交，并成为数据库中的永久组成部分。如果事务遇到错误而且必须取消或回滚，则所有数据更改均被清除。通过本任务将学习和掌握处理事务的方法。任务目标