MIS应用开发(T-SQL编程).ppt

MIS应用开发 T SQL编程 2 主要内容 变量的使用逻辑控制语句事务处理视图操作存储过程触发器 3 变量的使用1 和C语言一样 变量分为 局部变量 局部变量必须以标记 作为前缀 如 age局部变量的使用也是先声明 再赋值全局变量 全局变量必须以标记 作为前缀 如 version全局变量由系统定义和维护 我们只能读取 不能修改全局变量的值 4 变量的使用2 例如 DECLARE namevarchar 8 DECLARE seatint 声明局部变量DECLARE 变量名数据类型 例如 SET name 张三 SELECT name stuNameFROMstuInfoWHEREstuNo s25302 必须确保筛选出的记录只有1条 或 5 变量的使用3 查找李文才的信息 DECLARE namevarchar 8 学员姓名SET name 李文才 使用SET赋值SELECT FROMstuInfoWHEREstuName name 查找李文才的左右同桌 DECLARE seatint 座位号SELECT seat stuSeatFROMstuInfo 使用SELECT赋值WHEREstuName nameSELECT FROMstuInfoWHERE stuSeat seat 1 OR stuSeat seat 1 GO 6 变量的使用4 全局变量都使用两个 标志作为前缀 7 逻辑控制语句1 SQL中的IF ELSE语句IF 条件 BEGIN语句1语句2 ENDELSEBEGIN语句1 语句2 END SQL中的WHILE语句WHILE 条件 BEGIN语句1语句2 BREAKEND CASEWHEN条件1THEN结果1WHEN条件2THEN结果2 ELSE其他结果END 8 逻辑控制语句2 9 逻辑控制语句3 10 逻辑控制语句4 11 事务处理1 事务 TRANSACTION 是作为单个逻辑工作单元执行的一系列操作这些操作作为一个整体一起向系统提交 要么都执行 要么都不执行事务是一个不可分割的工作逻辑单元 转帐过程就是一个事务 它需要两条UPDATE语句来完成 这两条语句是一个整体 如果其中任一条出现错误 则整个转帐业务也应取消 两个帐户中的余额应恢复到原来的数据 从而确保转帐前和转帐后的余额不变 即都是1001元 12 事务处理2 事务必须具备以下四个属性 简称ACID属性 原子性 Atomicity 事务是一个完整的操作 事务的各步操作是不可分的 原子的 要么都执行 要么都不执行一致性 Consistency 当事务完成时 数据必须处于一致状态隔离性 Isolation 对数据进行修改的所有并发事务是彼此隔离的 这表明事务必须是独立的 它不应以任何方式依赖于或影响其他事务永久性 Durability 事务完成后 它对数据库的修改被永久保持 事务日志能够保持事务的永久性 13 事务处理3 T SQL使用下列语句来管理事务 开始事务 BEGINTRANSACTION提交事务 COMMITTRANSACTION回滚 撤销 事务 ROLLBACKTRANSACTION一旦事务提交或回滚 则事务结束 判断某条语句执行是否出错 使用全局变量 ERROR ERROR只能判断当前一条T SQL语句执行是否有错 为了判断事务中所有T SQL语句是否有错 我们需要对错误进行累计 如 SET errorSum errorSum error 14 事务处理4 关键语句讲解 BEGINTRANSACTION 定义变量 用于累计事务执行过程中的错误 DECLARE errorSumINTSET errorSum 0 初始化为0 即无错误 转帐 张三的帐户少1000元 李四的帐户多1000元 UPDATEbankSETcurrentMoney currentMoney 1000WHEREcustomerName 张三 SET errorSum errorSum errorUPDATEbankSETcurrentMoney currentMoney 1000WHEREcustomerName 李四 SET errorSum errorSum error 累计是否有错误 使用事务解决银行转帐问题 开始事务 指定事务从此处开始 后续的T SQL语句都是一个整体 累计是否有错误 15 事务处理5 IF errorSum0 如果有错误BEGINprint 交易失败 回滚事务 ROLLBACKTRANSACTIONENDELSEBEGINprint 交易成功 提交事务 写入硬盘 永久的保存 COMMITTRANSACTIONENDGOprint 查看转帐事务后的余额 SELECT FROMbankGO 根据是否有错误 确定事务是提交还是撤销 如果有错 则回滚操作 事务结束 如果成功 则提交操作 事务结束 16 视图操作1 视图是一张虚拟表 它表示一张表的部分数据或多张表的综合数据 其结构和数据是建立在对表的查询基础上视图中并不存放数据 而是存放在视图所引用的原始表 基表 中同一张原始表 根据不同用户的不同需求 可以创建不同的视图视图的用途筛选表中的行防止未经许可的用户访问敏感数据降低数据库的复杂程度将多个物理数据库抽象为一个逻辑数据库 17 视图操作2 使用T SQL语句创建视图的语法 CREATEVIEWview nameAS 18 视图操作3 IFEXISTS SELECT FROMsysobjectsWHEREname view stuInfo stuMarks DROPVIEWview stuInfo stuMarksGOCREATEVIEWview stuInfo stuMarksASSELECT姓名 stuName 学号 stuInfo stuNo 笔试成绩 writtenExam 机试成绩 labExam 平均分 writtenExam labExam 2FROMstuInfoLEFTJOINstuMarksONstuInfo stuNo stuMarks stuNoGOSELECT FROMview stuInfo stuMarks 19 存储过程1 存储过程 procedure 类似于C语言中的函数用来执行管理任务或应用复杂的业务规则存储过程可以带参数 也可以返回结果存储过程可以包含数据操纵语句 变量 逻辑控制语句等存储过程的特点有 执行速度更快允许模块化程序设计提高系统安全性减少网络流通量 20 存储过程2 系统存储过程由系统定义 存放在master数据库中类似C语言中的系统函数系统存储过程的名称都以 sp 开头或 xp 开头用户自定义存储过程由用户在自己的数据库中创建的存储过程类似C语言中的用户自定义函数 21 存储过程3 定义存储过程的语法CREATEPROC EDURE 存储过程名 参数1数据类型 默认值OUTPUT 参数n数据类型 默认值OUTPUTASSQL语句GO和C语言的函数一样 参数可选参数分为输入参数 输出参数输入参数允许有默认值 22 存储过程4 问题 请创建存储过程 查看本次考试平均分以及未通过考试的学员名单 23 存储过程5 CREATEPROCEDUREproc stuASDECLARE writtenAvgfloat labAvgfloatSELECT writtenAvg AVG writtenExam labAvg AVG labExam FROMstuMarksprint 笔试平均分 convert varchar 5 writtenAvg print 机试平均分 convert varchar 5 labAvg IF writtenAvg 70AND labAvg 70 print 本班考试成绩 优秀 ELSEprint 本班考试成绩 较差 print print 参加本次考试没有通过的学员 SELECTstuName stuInfo stuNo writtenExam labExamFROMstuInfoINNERJOINstuMarksONstuInfo stuNo stuMarks stuNoWHEREwrittenExam 60ORlabExam 60GO proc stu为存储过程的名称 笔试平均分和机试平均分变量 显示考试成绩的等级 显示未通过的学员 24 存储过程6 EXECUTE 执行 语句用来调用存储过程调用的语法EXEC过程名 参数 EXECproc stu 25 触发器1 触发器是在对表进行插入 更新或删除操作时自动执行的存储过程触发器通常用于强制业务规则触发器定义在特定的表上 与表相关自动触发执行不能直接调用是一个事务 可回滚 触发器是一种高级约束 可以定义比用CHECK约束更为复杂的约束可执行复杂的SQL语句 if while case 可引用其它表中的列 26 触发器2 DELETE触发器INSERT触发器UPDATE触发器 触发器触发时 系统自动在内存中创建deleted表或inserted表只读 不允许修改 触发器执行完成后 自动删除inserted表临时保存了插入或更新后的记录行可以从inserted表中检查插入的数据是否满足业务需求如果不满足 则向用户报告错误消息 并回滚插入操作deleted表临时保存了删除或更新前的记录行可以从deleted表中检查被删除的数据是否满足业务需求如果不满足 则向用户报告错误消息 并回滚插入操作 27 触发器3 创建触发器的语法 CREATETRIGGERtrigger nameONtable name WITHENCRYPTION FOR DELETE INSERT UPDATE AST SQL语句GO WITHENCRYPTION表示加密触发器定义的SQL文本DELETE INSERT UPDATE指定触发器的类型 28 触发器4 关键代码 CREATETRIGGERtrig transInfoONtransInfoFORINSERTASDECLARE typechar 4 outMoneyMONEYDECLARE myCardIDchar 10 balanceMONEYSELECT type transType outMoney transMoney myCardID cardIDFROMinsertedIF type 支取 UPDATEbankSETcurrentMoney currentMoney outMoneyWHEREcardID myCardIDELSEUPDATEbankSETcurrentMoney currentMoney outMoneyWHEREcardID myCardID GO 从inserted表中获取交易类型 教员金额等 根据交易类型 减少或增加对应卡号的余额 29 触发器5 关键代码 CREATETRIGGERtrig delete transInfoONtransInfoFORDELETEASprint 开始备份数据 请稍后 IFNOTEXISTS SELECT FROMsysobjectsWHEREname backupTable SELECT INTObackupTableFROMdeletedELSEINSERTINTObackupTableSELECT FROMdeletedprint 备份数据成功 备份表中的数据为 SELECT FROMbackupTableGO 从deleted表中获取被删除的交易记录 30 触发器6 关键代码 CREATETRIGGERtrig update bankONbankFORUPDATEASDECLARE beforeMoneyMONEY afterMoneyMONEYSELECT beforeMoney currentMoneyFROMde