资源预览内容
第1页 / 共30页
第2页 / 共30页
第3页 / 共30页
第4页 / 共30页
第5页 / 共30页
第6页 / 共30页
第7页 / 共30页
第8页 / 共30页
第9页 / 共30页
第10页 / 共30页
亲,该文档总共30页,到这儿已超出免费预览范围,如果喜欢就下载吧!
资源描述
MIS应用开发(T-SQL编程),2,主要内容,变量的使用 逻辑控制语句 事务处理 视图操作 存储过程 触发器,3,变量的使用1,和C语言一样,变量分为: 局部变量: 局部变量必须以标记作为前缀 ,如age 局部变量的使用也是先声明,再赋值 全局变量: 全局变量必须以标记 作为前缀,如version 全局变量由系统定义和维护,我们只能读取,不能修改全局变量的值,4,变量的使用2,例如: DECLARE name varchar(8) DECLARE seat int,声明局部变量 DECLARE 变量名 数据类型,例如: SET name=张三 SELECT name=stuName FROM stuInfo WHERE stuNo=s25302,必须确保筛选出的记录只有1条,或,5,变量的使用3,/*-查找李文才的信息-*/ DECLARE name varchar(8) -学员姓名 SET name=李文才 -使用SET赋值 SELECT * FROM stuInfo WHERE stuName = name /*-查找李文才的左右同桌-*/ DECLARE seat int -座位号 SELECT seat=stuSeat FROM stuInfo -使用SELECT赋值 WHERE stuName=name SELECT * FROM stuInfo WHERE (stuSeat = seat+1) OR (stuSeat = seat-1) GO,6,变量的使用4,全局变量都使用两个标志作为前缀,7,逻辑控制语句1,SQL中的IF-ELSE语句 IF (条件) BEGIN 语句1 语句2 END ELSE BEGIN 语句1; 语句2; END,SQL中的WHILE语句 WHILE (条件) BEGIN 语句1 语句2 BREAK END,CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果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使用下列语句来管理事务: 开始事务:BEGIN TRANSACTION 提交事务:COMMIT TRANSACTION 回滚(撤销)事务:ROLLBACK TRANSACTION 一旦事务提交或回滚,则事务结束。 判断某条语句执行是否出错: 使用全局变量ERROR; ERROR只能判断当前一条T-SQL语句执行是否有错,为了判断事务中所有T-SQL语句是否有错,我们需要对错误进行累计; 如: SET errorSum=errorSum+error,14,事务处理4,关键语句讲解 BEGIN TRANSACTION /*-定义变量,用于累计事务执行过程中的错误-*/ DECLARE errorSum INT SET errorSum=0 -初始化为0,即无错误 /*-转帐:张三的帐户少1000元,李四的帐户多1000元*/ UPDATE bank SET currentMoney=currentMoney-1000 WHERE customerName=张三 SET errorSum=errorSum+error UPDATE bank SET currentMoney=currentMoney+1000 WHERE customerName=李四 SET errorSum=errorSum+error -累计是否有错误,使用事务解决银行转帐问题,开始事务(指定事务从此处开始,后续的T-SQL语句都是一个整体),累计是否有错误,15,事务处理5,IF errorSum0 -如果有错误 BEGIN print 交易失败,回滚事务 ROLLBACK TRANSACTION END ELSE BEGIN print 交易成功,提交事务,写入硬盘,永久的保存 COMMIT TRANSACTION END GO print 查看转帐事务后的余额 SELECT * FROM bank GO,根据是否有错误,确定事务是提交还是撤销,如果有错,则回滚操作,事务结束,如果成功,则提交操作,事务结束,16,视图操作1,视图是一张虚拟表,它表示一张表的部分数据或多张表的综合数据,其结构和数据是建立在对表的查询基础上 视图中并不存放数据,而是存放在视图所引用的原始表(基表)中 同一张原始表,根据不同用户的不同需求,可以创建不同的视图 视图的用途 筛选表中的行 防止未经许可的用户访问敏感数据 降低数据库的复杂程度 将多个物理数据库抽象为一个逻辑数据库,17,视图操作2,使用T-SQL语句创建视图的语法,CREATE VIEW view_name AS ,18,视图操作3,IF EXISTS (SELECT * FROM sysobjects WHERE name = view_stuInfo_stuMarks) DROP VIEW view_stuInfo_stuMarks GO CREATE VIEW view_stuInfo_stuMarks AS SELECT 姓名=stuName,学号=stuInfo.stuNo, 笔试成绩 =writtenExam, 机试成绩=labExam, 平均分=(writtenExam+labExam)/2 FROM stuInfo LEFT JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo GO SELECT * FROM view_stuInfo_stuMarks,19,存储过程1,存储过程(procedure)类似于C语言中的函数 用来执行管理任务或应用复杂的业务规则 存储过程可以带参数,也可以返回结果 存储过程可以包含数据操纵语句、变量、逻辑 控制语句等 存储过程的特点有: 执行速度更快 允许模块化程序设计 提高系统安全性 减少网络流通量,20,存储过程2,系统存储过程 由系统定义,存放在master数据库中 类似C语言中的系统函数 系统存储过程的名称都以“sp_”开头或”xp_”开头 用户自定义存储过程 由用户在自己的数据库中创建的存储过程 类似C语言中的用户自定义函数,21,存储过程3,定义存储过程的语法 CREATE PROCEDURE 存储过程名 参数1 数据类型 = 默认值 OUTPUT, , 参数n 数据类型 = 默认值 OUTPUT AS SQL语句 GO 和C语言的函数一样,参数可选 参数分为输入参数、输出参数 输入参数允许有默认值,22,存储过程4,问题:请创建存储过程,查看本次考试平均分以及未通过考试的学员名单,23,存储过程5,CREATE PROCEDURE proc_stu AS DECLARE writtenAvg float,labAvg float SELECT writtenAvg=AVG(writtenExam), labAvg=AVG(labExam) FROM stuMarks print 笔试平均分:+convert(varchar(5),writtenAvg) print 机试平均分:+convert(varchar(5),labAvg) IF (writtenAvg70 AND labAvg70) print 本班考试成绩:优秀 ELSE print 本班考试成绩:较差 print - print 参加本次考试没有通过的学员: SELECT stuName,stuInfo.stuNo,writtenExam,labExam FROM stuInfo INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo WHERE writtenExam60 OR labExam60 GO,proc_stu为存储过程的名称,笔试平均分和机试平均分变量,显示考试成绩的等级,显示未通过的学员,24,存储过程6,EXECUTE(执行)语句用来调用存储过程 调用的语法 EXEC 过程名 参数,EXEC proc_stu,25,触发器1,触发器是在对表进行插入、更新或删除操作时自动执行的存储过程 触发器通常用于强制业务规则 触发器定义在特定的表上,与表相关 自动触发执行 不能直接调用 是一个事务(可回滚) 触发器是一种高级约束,可以定义比用CHECK 约束更为复杂的约束 可执行复杂的SQL语句(if/while/case) 可引用其它表中的列,26,触发器2,DELETE 触发器 INSERT 触发器 UPDATE 触发器,触发器触发时: 系统自动在内存中创建deleted表或inserted表 只读,不允许修改;触发器执行完成后,自动删除 inserted 表 临时保存了插入或更新后的记录行 可以从inserted表中检查插入的数据是否满足业务需求 如果不满足,则向用户报告错误消息,并回滚插入操作 deleted 表 临时保存了删除或更新前的记录行 可以从deleted表中检查被删除的数据是否满足业务需求 如果不满足,则向用户报告错误消息,并回滚插入操作,27,触发器3,创建触发器的语法:,CREATE TRIGGER trigger_name ON table_name WITH ENCRYPTION FOR DELETE, INSERT, UPDATE AS T-SQL语句 GO,WITH ENCRYPTION表示加密触发器定义的SQL文本 DELETE, INSERT, UPDATE指定触发器的类型,28,触发器4,-关键代码- CREATE TRIGGER trig_transInfo ON transInfo FOR INSERT AS DECLARE type char(4),outMoney MONEY DECLARE myCardID char(10),balance MONEY SELECT type=transType,outMoney=transMoney, myCardID=cardID FROM inserted IF (type=支取) UPDATE bank SET currentMoney=currentMoney-outMoney WHERE cardID=myCardID ELSE UPDATE bank SET currentMoney=currentMoney+outMoney WHERE cardID=myCardID . GO,从inserted表中获取交易类型
网站客服QQ:2055934822
金锄头文库版权所有
经营许可证:蜀ICP备13022795号 | 川公网安备 51140202000112号