《单元07用t-sql增加测试数据和事务控制PPT课件.ppt》由会员分享，可在线阅读，更多相关《单元07用t-sql增加测试数据和事务控制PPT课件.ppt（40页珍藏版）》请在三一文库上搜索。
1、单元7,用T-SQL增加测试数据和事务控制,回顾,在班级表中删除1号班级，报错如图，请问原因是什么？ 创建外键约束的语法？ 请列出ComboBox的常用属性 使用DataGridView进行筛选使用其什么属性？ 如何使用DataRelation实现主从表的关联？,本单元任务,任务一 为学生表插入十万行数据 任务二 完成“转专业”模块,新增管理员,新增教师,新增学生,本单元目标,掌握如何定义变量并赋值 掌握如何输出数据 掌握逻辑控制语句 理解批处理的概念 理解事务的概念 掌握ADO.NET中控制事务的方法,任务一为学生表插入十万行数据,任务描述：,任务分析：,为了进行系统的压力测试，为学生表添加
2、十万行数据 。,1使用INSERT语句可为学生表添加一行数据。 2使用循环控制可添加n。 3如果十万行数据都一样，则意思不大。因此，要使用随机数，产生不同的数据。 4考虑系统性能，应每100行提交一次。,使用变量,变量分为： 局部变量： 局部变量必须以标记作为前缀 ，如age 局部变量的使用也是先声明，再赋值 全局变量： 全局变量必须以标记 作为前缀，如version 全局变量由系统定义和维护，我们只能读取，不能修改全局变量的值,局部变量,例如： DECLARE name varchar(8) DECLARE seat int,声明局部变量 DECLARE 变量名 数据类型,赋值 SET 变量
3、名 =值 SELECT 变量名 = 值,例如： SET name=张三 SELECT name=studentName FROM student WHERE studentNo=2003010101,必须确保筛选出的记录只有1条,或,局部变量示例,分析： （1）查找张亮同学的班级号 （2）查找班级号是（1）结果的学生信,【例7-1】查找与张亮同班的学生信息,-声明存放学生姓名的变量，最多可存贮10个字符 DECLARE name varchar(10) -声明存放班级号 DECLARE classID int Set name = 张亮 Select classID = classID Fro
4、m Student Where StudentName = name Select * from Student Where ClassID = classID,全局变量,全局变量都使用两个标志作为前缀,print SQL Server的版本+VERSION print 服务器的名称: +SERVERNAME INSERT INTO student(studentName,studentNo) VALUES(武松,s25328) -如果大于0表示上一条语句执行有错误 print 当前错误号+convert(varchar(5),ERROR) print 刚才报名的学员，座位号为: +conve
5、rt(varchar(5),IDENTITY ) UPDATE student SET LoginPWD=aaa WHERE studentName=武松 print 当前错误号+convert(varchar(5),ERROR) GO,输出语句 print,演示：使用全局变量,参考语句,SQL Server的版本,服务器名称,错误号,逻辑控制语句,IFELSE语句,SQL中的IF-ELSE语句 IF （条件） BEGIN 语句1 语句2 END ELSE BEGIN 语句1； 语句2； END,ELSE是可选部分 如果有多条语句，才需要BEGIN-END语句块,IF-ELSE示例,分析： （
6、1）统计平均成绩存入临时变量。 （2）用IFELSE判断。,【例7-3】统计并显示课程号为1的期末成绩平均分，如果平均分在80以上，显示“成绩优秀”，并查询成绩前3名学生的学号；如果在80以下，显示“成绩一般”，并查询成绩后3名学生的学号,declare avg float select avg=avg(score1) from score where subjectID=1 if (avg = 80) begin print 成绩优秀 select top 3 studentno from score where subjectID=1 order by score1 desc end el
7、se begin print 成绩一般 select top 3 studentno from score where subjectID=1 order by score1 end,逻辑控制语句,CASEEND多分支语句,CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2 ELSE 其他结果 END,CASE 输入值 WHEN 值1 THEN 结果1 WHEN 值2 THEN 结果2 ELSE 其他结果 END,CASE示例,【例7-4】学生的性别：0为男生，1为女生，要求查询每位学生的学号和性别,Select StudentNo, 性别 = CASE SEX
8、WHEN 0 THEN 男生 WHEN 1 THEN 女生 END FROM Student,逻辑控制语句,WHILE循环语句,SQL中的WHILE语句 WHILE （条件） BEGIN 语句1 语句2 BREAK END,BREAK表示退出循环 如果有多条语句，才需要BEGIN-END语句块,WHILE示例,【例7-5】求10的阶乘,DECLARE Result integer, i integer SELECT Result=1, i=10 WHILE i0 BEGIN SET Result=Result*i SET i=i-1 END PRINT Result,批处理语句,批处理是包含一
9、个或多个 SQL 语句的组，从应用程序一次性地发送到SQL Server执行 SQL Server 将批处理语句编译成一个可执行单元，此单元称为执行计划。执行计划中的语句每次执行一条,客户端应用程序,SQL Server服务器,批处理语句： 语句1 语句2 GO,批处理语句示例,SELECT * FROM student SELECT * FROM score UPDATE score SET score3=score1*0.6+score2*0.4 GO,GO是批处理的标志，表示SQL Server将这些T-SQL语句编译为一个执行单元，提高执行效率 一般是将一些逻辑相关的业务操作语句，放置
10、在同一批中，这完全由业务需求和代码编写者决定,批处理语句示例,-例如建表语句的末尾必须添加GO CREATE TABLE stuInfo ( . ) GO,SQLServer规定：如果是建库、建表语句、以及我们后面学习的存储过程和视图等，则必须在语句末尾添加 GO 批处理标志,随机函数,RAND ( )：随机函数，返回从0 到1之间的随机float值。 NEWID ( )：创建一个uniqueidentifier类型的唯一值,select rand()*100,产生0-100内的随机数,select Left(convert(varchar(40),newid(),10),产生10位随机字符串
11、,事务,银行转账,账户A,账户B,事务是一个工作单元，是一种要么成功，要么失败的操作。 例如，银行转账问题： 假定资金从账户A转到账户B，至少需要两步： 账户A的资金减少 然后账户B的资金相应增加,假定张三的账户直接转账1000元到李四的账户,为什么需要事务,CREATE TABLE bank ( customerName CHAR(10), -顾客姓名 currentMoney MONEY -当前余额 ) GO ALTER TABLE bank ADD CONSTRAINT CK_currentMoney CHECK(currentMoney=1) GO INSERT INTO bank(c
12、ustomerName,currentMoney) VALUES(张三,1000) INSERT INTO bank(customerName,currentMoney) VALUES(李四,1),创建账户表，存放用户的账户信息,添加约束：根据银行规定，账户余额不能少于1元，否则视为销户,张三开户，开户金额为1000元 ；李四开户，开户金额1元,为什么需要事务,目前两个账户的余额总和为：1000+1=1001元,为什么需要事务,模拟实现转账 ： 从张三的账户转账1000元到李四的账户,/*-转账测试：张三转账1000元给李四-*/ -我们可能会这样这样编写语句 -张三的账户少1000元，李四的
13、账户多1000元 UPDATE bank SET currentMoney=currentMoney-1000 WHERE customerName=张三 UPDATE bank SET currentMoney=currentMoney+1000 WHERE customerName=李四 GO -再次查看转账后的结果。 SELECT * FROM bank GO,请问：执行转账语句后，张三、李四的账户余额为多少？,张三的账户没有减少 但李四的账户却多了1000元 100010012001元 总额多出了1000元！,为什么需要事务,-张三的账户减少1000元，李四的账户增加1000元 UPD
14、ATE bank SET currentMoney=currentMoney-1000 WHERE customerName=张三 UPDATE bank SET currentMoney=currentMoney+1000 WHERE customerName=李四 GO,错误原因分析：,执行失败，所以张三还是1000元,继续往下执行：执行成功，所以李四变为1001元,如何解决呢？使用事务,UPDATE语句违反约束：余额=1元,什么是事务,事务是作为逻辑工作单元执行的一系列操作。它包含了一组SQL语句，但整组SQL作为一个整体向系统提交或撤销。对于大型的数据库应用系统，如订票系统、银行系统等
15、特别适合。 事务有四个属性，称为 ACID（原子性、一致性、隔离性和持久性）属性，只有这样才能成为一个事务。,转账过程就是一个事务。 它需要两条UPDATE语句来完成，这两条语句是一个整体，如果其中任一条出现错误，则整个转账业务也应取消，两个账户中的余额应恢复到原来的数据，从而确保转账前和转账后的余额不变，即都是1001元。,创建事务,T-SQL管理事务的语句有： BEGIN TRANSAVTION：开始事务。 COMMIT TRANSACTION：提交事务。 ROLLBACK TRANSACTION：回滚事务。 事务的分类有： 显式事务：通过发出BEGIN TRANSACTION语句显式启动
16、事务。 自动提交事务：SQL Server的默认模式。每个单独的T-SQL语句作为一个事务，成功后提交，错误则自动回滚。 隐性事务：通过设置SET IMPLICIT_TRANSACTIONS ON语句，将隐性事务模式设置为打开。在隐性事务操作时，提交或回滚事务后自动启动一个新事务。,解决银行转账问题,BEGIN TRANSACTION DECLARE errorSum INT -用于累计事务执行过程中的错误-*/ SET errorSum=0 -初始化为0，即无错误 /*-转帐：张三的账户少1000元，李四的账户多1000元*/ UPDATE bank SET currentMoney=cur
17、rentMoney-1000 WHERE customerName=张三 SET errorSum=errorSum+error -累计是否有错误 UPDATE bank SET currentMoney=currentMoney+1000 WHERE customerName=李四 SET errorSum=errorSum+error -累计是否有错误 /*-根据是否有错误，确定事务是提交还是撤销-*/ IF errorSum0 -如果有错误 BEGIN print 交易失败，回滚事务 ROLLBACK TRANSACTION END ELSE BEGIN print 交易成功，提交事务，
18、写入硬盘，永久的保存 COMMIT TRANSACTION END ,【例7-8】使用显式事务来处理转账业务。,任务实施,1使用WHILE循环添加10万行数据 2使用随机函数 3实现隐性事务实现100行提交一次,代码分析,关键代码,set nocount on SET IMPLICIT_TRANSACTIONS ON- 设置开启隐性事务 declare i int, cnt int, d datetime select d=getdate(), i=1, cnt=100000 while (i=cnt) begin INSERT INTO tstuTest VALUES(i,Left(conv
19、ert(varchar(40),newid(),10),i%2, convert(varchar(40), newid(), rand()*100, d-i%1000) set i = i + 1 if (i%100=0)- 每100行提交一次 commit tran End SET IMPLICIT_TRANSACTIONS OFF- 设置关闭自动事务 commit tran,任务二实现“学籍变动管理”模块,任务描述：,任务分析：,大宝经理检测学籍变动模块功能，结果发现了大问题：明明有两位学生登记了“休学”，可学生信息查询还是“在读”。大宝经理责令张力修改。,1需要登记手续办理情况：学号、学
20、籍变动类型、何时办理、申请理由、审核领导等；如果是转专业，还需要登记转出专业、转入专业、转入方审核领导等。 2登记学籍变动信息后，还需要修改该学生的状态，学籍变动造成的学生状态变化如图所示：,ADO.NET控制事务,在ADO.NET中，可以使用Connection和Transaction对象来控制事务。 执行事务： 调用Connection对象的BeginTransaction开始事务。 将Transaction对象分配给要执行的Command的Transaction属性。 执行所需的命令。 调用Transaction对象的Commit方法来完成事务，或调用Rollback方法来取消事务。,A
21、DO.NET控制事务范例,【例7-9】实现银行转账业务。,private void btnSave_Click(object sender, EventArgs e) DBHelper.connection.Open(); SqlTransaction myTrans = DBHelper.connection.BeginTransaction(); /为事务创建一个命令 SqlCommand myCommand = new SqlCommand(); myCommand.Connection = DBHelper.connection; myCommand.Transaction = myT
22、rans; try myCommand.CommandText = string.Format( UPDATE bank SET currentMoney=currentMoney-0 WHERE customerName=1, txtMoney.Text, txtOldName.Text); myCommand.ExecuteNonQuery(); myCommand.CommandText = string.Format( UPDATE bank SET currentMoney=currentMoney+0 WHERE customerName=1, txtMoney.Text, txt
23、NewName.Text); myCommand.ExecuteNonQuery(); myTrans.Commit();/提交 MessageBox.Show(转账成功！); catch (Exception ex) myTrans.Rollback();/回滚 MessageBox.Show(转账失败！); finally DBHelper.connection.Close(); ,任务实施,1设计界面 2创建视图v_student 3窗体打开时获取学籍变动类别信息 4输入学号后显示该学生信息 5转专业，则显示新专业的信息；否则不用填写 6保存学籍变动信息，用事务控制,代码分析,保存学籍变
24、动信息，用事务控制,private void btnSave_Click(object sender, EventArgs e) string sqlIns, sqlUpdate = String.Empty; /插入学籍变动表的SQL和修改学生状态SQL string agree = rdPass.Checked?1:0;/申请是否通过 string stuStaus = String.Empty; /学生状态变量 string type = cbxType.SelectedValue.ToString();/学籍变动类别编号 /设置学生的目标状态标志 switch (int.Parse(t
25、ype) case 1:/休学 stuStaus = 2; break; case 2:/复学 stuStaus = 1;break; case 3:/退学 stuStaus = 3;break; default:/留级、转专业 stuStaus = 1;break; if (type != 5)/ 非转专业，插入学籍变动表的SQL和修改学生状态SQL sqlIns = String.Format(INSERT INTO StuStatusChange ( StudentNO,TypeID, ChangeDate,OldSpecID, Agree,OldHead,Reason,Memo) Va
26、lues(0,1,2,3, 4,5,6,7), txtStudentNo.Text, type, dtDate.Text, txtSpecOldID. Text, agree, txtHeadOld.Text, txtReason.Text, txtMemo.Text); sqlUpdate = String.Format(Update Student set StudentStateID=0 where studentno=1, stuStaus, txtStudentNo.Text); ,代码分析,保存学籍变动信息，用事务控制,else sqlIns = String.Format(INS
27、ERT INTO StuStatusChange(StudentNO,TypeID, ChangeDate,OldSpecID,Agree, OldHead,Reason,Memo,NewSpecID,NewHead)Values (0,1,2,3,4,5,6,7,8,9), txtStudentNo.Text, type, dtDate.Text, txtSpecOldID.Text, agree, txtHeadOld.Text, txtReason.Text, txtMemo.Text, cbxSpecNew.SelectedValue, txtHeadNew.Text); DBHelp
28、er.connection.Open(); SqlTransaction myTrans = DBHelper.connection.BeginTransaction(); /为事务创建一个命令 SqlCommand comm = new SqlCommand(sqlIns, DBHelper.connection); comm.Transaction = myTrans; try comm.ExecuteNonQuery(); if (int.Parse(type) 4) comm.CommandText = sqlUpdate; comm.ExecuteNonQuery(); myTran
29、s.Commit();/提交 MessageBox.Show(学籍变动成功！); catch (Exception ex) myTrans.Rollback();/回滚 MessageBox.Show(学籍变动失败！); finally DBHelper.connection.Close(); ,总结,SQL Server有全局变量和局部变量，变量名称分别以“”和“”开头。 局部变量赋值有两种方式：SELECT和SET 可以使用PRINT语句在消息区输出显示数据 使用IFELSE语句作为条件判断语句 使用CASEEND语句作为多分支语句，它有两种使用语法，并可放到SELECT中。 使用WHILE语句可使一组语句重复多次 GO语句是批处理结束的标志 事务有4大特性，分别使用BEGIN TRAN、COMMIT TRAN和ROLLBACK TRAN来显示控制事务 在ADO.NET 中，可以使用Connection 和Transaction 对象来控制事务,作业,谢谢！,个人观点供参考，欢迎讨论,