第9章SQLSERVER的程序设计ppt课件.ppt

第九章SQLServer的程序设计 SQLServer数据库应用技术 2 本章主要内容 9 1批处理与流程控制9 2事务处理9 3锁机制9 4游标 3 9 1批处理与流程控制 通常 服务器端的程序使用SQL语句来编写 一般而言 一个服务器端的程序是由以下一些部分组成 批注释程序中使用的变量改变批中语句的执行顺序的流程控制语言错误和消息的处理 4 9 1批处理与流程控制 一 批处理批处理就是单个或多个Transact SQL语句的集合 由用户或应用程序一次性发送给SQLServer解释并执行批处理内的所有语句指令 建立批处理如同编写SQL语句 区别在于它是多条语句同时执行的 用GO语句作为一个批处理的结束 5 一 批处理 使用批时的几点限制 1 大多数的create命令要在单个批中执行 但createdatabase createtable和createindex例外2 用set语句改变的选项在批结束时生效3 在同一个批中不能改变一个表再立即引用其新列 6 例9 1 一个批处理的例子 usestudentgocreateviewteacher info viewasselectteacher id name tech title salaryfromteacher infogoselect fromteacher info viewgo createview必须是批处理中唯一的语句 一 批处理 7 例9 2 错误例题 usestudentgocreateviewstud info viewasselectstud id namefromstud infoinsertintostud info viewvalues 050910211 黄铭 go 一 批处理 8 例9 3 无效批处理 declare stud varintgoselect stud var 25goprint stud vargo 一 批处理 9 二 流程控制 使用T SQL语言编程的时候 常常要利用各种流程控制语句去进行顺序 分支控制转移 循环等操作 T SQL提供了一组流程控制语句 包括 条件控制语句 无条件转移语句 循环语句和返回状态值给调用例程的语句1 BEGIN END语句块2 IF ELSE语句3 CASE表达式4 WHILE语句5 WAITFOR语句6 GOTO语句7 RETURN语句 10 二 流程控制 begin end 其语法如下 BEGIN 命令行 程序块 ENDBEGIN END用来设定一个程序块 将在BEGIN END内的所有程序视为一个单元执行 BEGIN END经常在条件语句 如IF ELSE 或循环等控制流程的语句中使用 在BEGIN END中可嵌套另外的BEGIN END来定义另一程序块 11 二 流程控制 if else IF ELSE语句用来实现选择结构 其语法格式如下 IF 条件表达式 命令行 程序块 ELSE 命令行或程序块 其中 可以是各种表达式的组合 但表达式的值必须是逻辑值 真 或 假 ELSE子句是可选的 最简单的IF语句没有ELSE子句部分 IF ELSE用来判断当某一条件成立时执行某段程序 条件不成立时执行另一段程序 如果不使用程序块 IF或ELSE只能执行一条命令 IFELSE可以进行嵌套 最多嵌套32级 12 二 流程控制 if else 例9 4 if selectavg grade fromstud gradewhereleft stud id 8 04010101 60beginprint pass end 13 二 流程控制 if else 例9 5 统计2004级计算机应用技术专业02班的学生人数不超过5人 则向数据表stud info中插入新的学生记录 declare p totalintbeginselect p total count fromstud infowhereleft stud id 8 04010102 if p total 5 insertintostud infovalues 0401010245 谢背光 05 14 1986 男 广东省湛江市 514020 565 end 14 二 流程控制 if else 例9 5改 在stud info表中 查询1986年出生的学生的平均入学成绩 如果大于560 则让这些人的入学成绩减5 declare xintbeginselect x avg mark fromstud infowhereyear birthday 1986if x 560 updatestud infosetmark mark 5whereyear birthday 1986end 15 二 流程控制 if else 例9 6 从数据表stud grade中读出学生陈红的成绩 将百分制转换成等级制显示 declare scorenumeric 4 1 stepchar 1 beginselect score gradefromstud gradewherename 陈红 if score 90and score 80 set step B elseif score 70 set step C elseif score 60 set step D elseset step E print stepend 16 二 流程控制 case表达式 CASE函数可以计算多个条件式 并将其中一个符合条件的结果表达式返回 CASE函数按照使用形式的不同 可以分为简单CASE表达式和搜索CASE表达式 注意 1 执行CASE子句时 只运行第一个匹配的子句 2 CASE表达式不是语句 它不能单独执行 而只能作为语句的一部分来使用 17 二 流程控制 case表达式 1 简单CASE表达式将一个测试表达式与一组简单表达式进行比较 如果某个简单表达式与测试表达式的值相等 则返回相应结果表达式的值 其语法格式如下 CASE测试表达式WHEN测试值1THEN结果表达式1 WHEN测试值nTHEN结果表达式n ELSE结果表达式m END 18 二 流程控制 case表达式 例9 7 从学生表stud info中 选取stud id gender 如果gender为 男 则输出 M 如果为 女 输出 F selectstud id sex casegenderwhen 男 then M when 女 then F endfromstud info 19 二 流程控制 case表达式 2 搜索CASE表达式与简单表达式不同的是 搜索表达式中 CASE关键字后面不跟任何表达式 在各WHEN关键字后面跟的都是逻辑表达式 其语法格式如下 CASEWHEN逻辑表达式1THEN结果表达式1 WHEN逻辑表达式nTHEN结果表达式n ELSE结果表达式m END 20 二 流程控制 case表达式 例9 8 从stud grade表中查询所有同学选课成绩情况 凡成绩为空者输出 未考 小于60分输出 不及格 60分至70分输出 及格 70分至90分输出 良好 大于或等于90分时输出 优秀 selectstud id name score casewhengradeisnullthen 未考 whengrade 60andgrade 70andgrade 90then 优秀 endfromstud grade 21 二 流程控制 while语句 其语法如下 WHILE 条件表达式 BEGIN 命令行或程序块 BREAK CONTINUE 命令行或程序块 ENDWHILE命令在设定的条件成立时 会重复执行命令行或程序块 CONTINUE命令可以让程序跳过CONTINUE命令之后的语句 回到WHILE循环的第一行 继续进行下一次循环 BREAK命令则让程序完全跳出循环 结束WHILE命令的执行 WHILE语句也可以嵌套 22 二 流程控制 while语句 例9 9 以下程序计算1 100之间所有能被3整除的数的个数及总和 declare xsmallint ysmallint numssmallintset x 0set y 1set nums 0while y 100 beginif y 3 0 beginset x x yset nums nums 1endset y y 1endprintstr x str nums 23 二 流程控制 waitfor 其语法如下 WAITFOR DELAY TIME WAITFOR命令用来暂时停止程序执行 直到所设定的等待时间已过或所设定的时间已到才继续往下执行 其中 时间 必须为DATETIME类型的数据 但不能包括日期 各关键字含义如下 1 DELAY 用来设定等待的时间段 最多可达24小时2 TIME 用来设定等待结束的时间点 24 二 流程控制 waitfor 例9 10 等待1小时2分零3秒后才执行SELECT语句 例 等到下午15点零8分后才执行SELECT语句 waitfordelay 01 02 03 select fromstud info waitfortime 15 38 00 select fromstud info 25 二 流程控制 goto GOTO语句是无条件转移语句 语法格式为 GOTO标识符GOTO命令用来改变程序执行的流程 使程序跳到标有标识符的指定的程序行再继续往下执行 作为跳转目标的标识符可为数字与字符的组合 但必须以 结尾 在GOTO命令行 标识符后不必跟 26 二 流程控制 goto 例9 11 求1 2 3 100的总和 declare sumsmallint ismallintset i 1set sum 0beg if i 100 beginset sum sum i set i i 1gotobegendprint sum 27 二 流程控制 return 语法如下 RETURN 整数值 RETURN命令用于结束当前程序的执行 返回到上一个调用它的程序或其它程序 在括号内可指定一个返回值 如果没有指定返回值 SQLServer系统会根据程序执行的结果返回一个内定值 如右图 如果运行过程产生了多个错误 SQLServer系统将返回绝对值最大的数值 如果此时用户定义了返回值 则以返回用户定义的值 RETURN语句不能返回NULL值 0程序执行成功 1找不到对象 2数据类型错误 3死锁 4违反权限原则 5语法错误 6用户造成的一般错误 7资源错误如磁盘空间不足 8非致命的内部错误 9已达到系统的极限 10 11致命的内部不一致性错误 12表或指针破坏 13数据库破坏 14硬件错误 28 9 2事务处理 一 事务概述二 事务管理三 事务模式四 事务日志 29 一 事务概述 一个事务是由一系列的对数据库的查询操作和更新操作构成的 事务是数据库系统中执行的一个工作单位 它是由用户定义的一组操作序列 一个事务可以是一组SQL语句 一条SQL语句或整个程序 一个应用程序可以包括多个事务 事务的开始与结束可以由用户显式控制 如果用户没有显式地定义事务 则由DBMS按照缺省规定自动划分事务 在SQL语言中 定义事务的语句有三条 30 一 事务概述 BEGINTRANSACTIONCOMMITROLLBACKBEGINTRANSACTION表示事务的开始 COMMIT表示事务的提交 即将事务中所有对数据库的更新写回到磁盘上的物理数据库中去 此时事务正常结束 ROLLBACK表示事务的回滚 即在事务运行的过程中发生了某种故障 事务不能继续执行 系统将事务中对数据库的所有已完成的更新操作全部撤销 再回滚到事务开始时的状态 31 一 事务概述 例9 12 将student数据库中学生基本信息表stud info的学号stud id由0401010811修改为0401010111 begintranstud transactionupdatestud infosetstud id 0401010111 wherestud id 0401010108 updatestud gradesetstud id 0401010111 wherestud id 0401010108 committranstud transaction 对于显示定义的事务 最后必须提交 32 一 事务概述 事务是由有限的数据库操作序列组成 但并不是任意的数据库操作序列都能成为事务 为了保护数据的完整性 一般要求事务具有以下四个特征 1 原子性一个事务是一个不可分割的工作单位 事务在执行时 应该遵守 要么不做 要么全做 nothingorall 的原则 即不允许事务部分的完成 即使因为故障而使事务未能完成 它执行的部分结果要被取消 33 一 事务概述 2 一致性事务对数据库的作用是数据库从一个一致状态转变到另一个一致状态 所谓数据库的一致状态是指数据库中的数据满足完整性约束 例如 银行企业中 从帐号A转移资金额R到帐号B 是一个典型的事务 这个事务包括两个操作 从帐号A中减去资金额R和在帐号B中增加资金额R 如果只执行其中一个操作 则数据库处于不一致状态 帐务会出现问题 也就是说 两个操作要么全做 要么全不做 否则就不能成为事务 可见事务的一致性与原子性是密切相关的 34 一 事务概述 3 隔离性如果多个事务并发地执行 应像各个事务独立执行一样 一个事务的执行不能被其他事务干扰 即一个事务内部的操作及使用的数据对并发的其他事务是隔离的 并发控制就是为了保证事务间的隔离性 4 持久性指一个事务一旦提交 它对数据库中数据的改变就应该是持久的 即使数据库因故障而受到破坏 DBMS也应该能够恢复 总结 事务的这4个特性一般简称为事务的ACID特性 35 二 事务管理语句 1 建立事务begintran saction 事务名称 tran name variable withmark description 2 commit语句标志事务结束commit tran transaction name tran name variable 或commit work 36 二 事务管理语句 3 rollback语句rollback tran transaction name tran name variable savepoint name savepoint name 或rollback work 4 设置保存点save tran savepoint name 37 二 事务管理语句 补充 两个可用于事务管理的全局变量 error 给出最近一次执行的出错语句引发的错误号 error为0表示未出错 rowcount 给出受事务中已执行语句所影响的数据行数 38 二 事务管理语句 例9 13 declare trannamevarchar 20 select tranname student manager1 begintran trannamegoupdatestud gradesetgrade grade 0 90wherecourse idlike 06 gocommittran tranname 39 二 事务管理语句 例 begintraninsertstud gradevalues 0401010113 王小明 0401010106 80 savetranmy savedeletefromstud gradewherename 王小明 rollbacktranmy savecommittran 40 二 事务管理语句 例9 14 使用事务处理方式对表stud grade执行更新操作 成功则提交事务 失败则取消事务 begintranstudent manager2updatestud gradesetgrade round sqrt grade 10 0 wherecourse idlike 06 if error 0rollbacktranstudent manager2elsecommittranstudent manager2 41 三 事务模式 1 显示事务模式可以由用户在其中定义事务的启动和结束 2 隐式事务模式指在当前事务提交或回滚后 自动启用新事务 setimplicit tranactionson off3 自动事务模式 42 四 事务日志 用来记录事务的每一次对数据库的更新操作 1 事务日志的内容2 事务日志文件 logging 43 9 3锁机制 数据库是一个共享资源 允许多个用户程序并行地存取数据库 若对这种并发操作不加以控制就会破坏数据的一致性 44 一 锁的概念 锁作为一种安全机制 用于控制多个用户的并发操作 防止用户读取正在由其他用户更改的数据或者多个用户同时修改同一数据 确保事务的完整性和数据的一致性 所谓封锁就是当一个事务在对某个数据对象 可以是数据项 记录 数据集 以至整个数据库 进行操作之前 必须获得相应的锁 以保证数据操作的正确性和一致性 锁定机制的主要属性是锁的粒度和锁的类型 45 一 锁的概念 锁的粒度 根据对数据的不同处理 封锁的对象可以是这样一些逻辑单元 字段 记录 表 数据库等 锁对象的大小称之为锁粒度 LockGranularity SQLServer提供了多种粒度的锁 允许一个事务锁定不同类型的资源 锁的粒度越小 系统允许的并发用户数目就越多 数据库的利用率就越高 但封锁机构复杂 系统开销也就越大 管理锁定所需要的系统资源越多 反之 则相反 为了减少锁的成本 应该根据事务所要执行的任务 合理选择锁的粒度 将资源锁定在适合任务的级别范围内 由于同时封锁一个记录的概率很小 一般数据库系统都在记录级上进行封锁 以获得更高的并发度 46 一 锁的概念 锁的粒度 按照粒度增加的顺序 不同粒度的锁可以锁定的资源如表9 1所示 表9 1不同粒度的锁可以锁定的资源 47 一 锁的概念 锁类型 SQLServer使用不同的锁模式锁定资源 这些锁模式确定了并发事务访问资源的方式 常用的锁模式有 1 排它锁 ExclusiveLock 用于数据修改操作排它锁又称写锁 简称为X锁 其采用的原理是禁止并发操作 当事务T对某个数据对象R实现X封锁后 其他事务要等T解除X封锁以后 才能对R进行封锁 这就保证了其他事务在T释放R上的锁之前 不能再对R进行操作 48 一 锁的概念 锁类型 2 共享锁 ShareLock 用于只读取数据的操作共享锁又称读锁 简称为S锁 其采用的原理是允许其他用户对同一数据对象进行查询 但不能对该数据对象进行修改 当事务T对某个数据对象R实现S封锁后 其他事务只能对R加S锁 而不能加X锁 直到T释放R上的S锁 这就保证了其他事务在T释放R上的S锁之前 只能读取R 而不能再对R作任何修改 3 修改 Update 锁 用于可更新的资源中 防止多个会话在读取 锁定及随后可能进行的资源更新时发生常见形式的死锁 49 一 锁的概念 两段锁协议 在运用X锁和S锁这两种基本封锁对数据对象加锁时 还需要约定一些规则 如何时申请X锁或S锁 持锁时间和何时释放等 我们称这些规则为封锁协议 LockingProtocol 对封锁方式规定不同的规则 就形成了各种不同的封锁协议 两段封锁协议是最常用的一种封锁协议 理论上已经证明使用两段封锁协议产生的是可串行化调度 一个事务的运行次序在并行调度执行的结果等价于某一串行调度执行的结果 则称这种调度是可串行化的调度 两段锁协议是指每个事务的执行可以分为两个阶段 加锁阶段 生长阶段 和解锁阶段 衰退阶段 50 一 锁的概念 两段锁协议 加锁阶段 在该阶段可以进行加锁操作 在对任何数据进行读操作之前要申请并获得S锁 在进行写操作之前要申请并获得X锁 加锁不成功 则事务进入等待状态 直到加锁成功才继续执行 解锁阶段 当事务释放了一个封锁以后 事务进入解锁阶段 在该阶段只能进行解锁操作不能再进行加锁操作 51 一 锁的概念 两段锁协议 两段封锁法可以这样来实现 事务开始后就处于加锁阶段 一直到执行ROLLBACK和COMMIT之前都是加锁阶段 ROLLBACK和COMMIT使事务进入解锁阶段 即在ROLLBACK和COMMIT模块中DBMS释放所有封锁 52 二 查看锁的信息 1 使用企业管理器 管理 当前活动 锁 进程ID或锁 对象2 用系统存储过程sp locksp lock spid1 spid1 spid2 spid2 参数说明 spid1 spid1 指定进程的标识号 该标识号存储在master dbo sysprocesses中 如过不指定 则显示所有锁的信息 53 三 死锁 封锁技术的引入能有效解决并发用户的数据一致性问题 但因此可能会引起进程间的死锁问题 1 引起死锁的主要原因是 两个进程已各自锁住一个页 但又要求访问被对方锁住的页 更一般的情况是 一个事务独占了其他事务正在申请的资源 且若干个这样的事务形成一个等待圈 54 三 死锁 2 死锁的避免死锁一旦发生 系统效率将会大大下降 因而要尽量避免死锁的发生 同操作系统避免死锁的方法类似 在数据库环境下 常用的方法有 1 要求每个事务一次就将要使用的数据全部加锁 否则就不能继续执行 2 预先规定一个封锁顺序 所有事务都按这个顺序实行封锁 这样也不会发生死锁 3 允许死锁发生 系统采用某些方式诊断当前系统中是否有死锁发生 SQLServer能自动发现并解除死锁 当发现死锁时 它会选择其进程累计的CPU时间最少者所对应的用户作为 牺牲者 令其夭折 以让其他进程能继续执行 然后将被中断的事务回滚 同时SQLServer发送错误号1205 即 error 1205 给牺牲者 55 9 4游标 一 游标的定义及其优点二 使用游标三 游标示例 56 一 游标的定义及其优点 通常情况下 关系数据库中的操作总会对整个记录集产生影响 而在实际应用中 应用程序有时只需要每次处理一条或一部分记录 在这种情况下 就需要使用游标在服务器内部处理结果集 游标可视为一种特殊的指针 它与某个查询结果相联系 可以指向结果集的任意位置 以便对指定位置的数据进行处理 使用游标可以在查询数据的同时对数据进行处理 游标的优点 p253 57 二 使用游标 使用游标的步骤 1 声明游标 2 打开游标 3 从游标的结果集中读取数据 4 对游标中的数据逐行操作 5 关闭游标 6 释放游标 58 二 使用游标 声明游标 游标与局部变量一样 也要先声明后使用 SQLServer中 使用游标的第一步是声明游标 声明的内容主要包括游标名称 数据来源 选择条件 游标属性 命令格式 DECLAREcursor name scroll CURSORFORselect statement FOR READONLY UPDATE OFcolumn name n 命令说明 1 scroll 说明所声明的游标可以前滚 后滚 可使用所用的提取选项 first last prior next 如果省略scroll 则只能使用next提取选项 59 二 使用游标 声明游标 2 select statement表示一个SELECT查询语句 它查询的表称为游标的基表 查询的结果称为游标结果集 在游标声明的select statement内必须有from子句 且不允许使用关键字COMPUTE COMPUTEBY和INTO 3 READONLY表示所声明的游标为只读的 不得用于DELETE UPDATE语句 不选此项时 则为可修改游标 4 UPDATE表示所声明的游标为可修改游标 后面的OFcolumn name n 指定允许修改的列 无OFcolumn name n 选项时表示允许修改所有列 60 二 使用游标 打开游标 打开游标就是执行相应的SELECT语句的查询操作 检索多行数据 把所有满足查询条件的数据行组成一个集合 称为游标活动集 ActiveSet 或结果集 并把游标指针置于其首端 命令格式 OPENcursor name注意 p254 1 2 61 二 使用游标 推进游标指针并取当前记录 使用游标是指将游标指向 或称 定位于 游标结果集的某一行 对该行的数据进行提取或修改 游标指向的那一行称为游标的当前行 当游标声明被打开后 游标指针位于结果集的第一行之前 使用游标提取数据只能一次提取结果集的单个行 并可以将游标所指行 当前行 的各列数据转移到指定的变量中 这样 其他T SQL语句就可以引用这些变量来访问已提取的数据值 使用游标语法如下 62 二 使用游标 推进游标指针并取当前记录 FETCH NEXT PRIOR FIRST LAST ABSOLUTE n nvar RELATIVE n nvar FROM游标名称 INTO 变量名 n 命令说明 1 NEXT 读取当前游标指针所指向行的下一行数据 并将游标指向该下一行 如果FETCHNEXT是对游标的第一次提取操作 则返回结果集中的第一行 NEXT为默认的游标提取选项 63 二 使用游标 推进游标指针并取当前记录 2 PRIOR 读取当前游标指针所指向行的上一行数据 并将游标指向该上一行 如果FETCHPRIOR为对游标的第一次提取操作 则没有行返回并且游标仍指向第一行 3 FIRST 读取游标结果集的第一行数据并将其作为当前行 4 LAST 读取游标结果集的最后一行数据并将其作为当前行 64 二 使用游标 推进游标指针并取当前记录 5 ABSOLUTE n nvar 表示读取游标结果集的第n行数据 如果n或 nvar为正数 则返回从游标结果集第一行开始计算的正数第n行数据 并将返回的行变成新的当前行 如果n或 nvar为负数 则返回从游标结果集第末行开始计算的倒数第n行数据 并将返回的行变成新的当前行 如果n或 nvar为0 则没有行返回 n必须为整型常量且 nvar必须为smallint tinyint或int类型数据 65 二 使用游标 推进游标指针并取当前记录 6 RELATIVE n nvar 如果n或 nvar为正数 读取当前行之后的第n行并将返回的行变成新的当前行 如果n或 nvar为负数 读取当前行之前的第n行并将返回的行变成新的当前行 如果n或 nvar为0 读取当前行 如果对游标的第一次提取操作时将FETCHRELATIVE的n或 nvar指定为负数或0 则没有行返回 n必须为整型常量 局部变量 nvar必须为smallint tinyint或int型 7 INTO variable name n 表示允许将提取操作的列数据存放到局部变量列表中 66 二 使用游标 推进游标指针并取当前记录 注意 有两个全局变量可以提