单元07用T-SQL增加测试数据和事务控制.pptVIP

单元7,用T-SQL增加测试数据和事务控制,回顾,在班级表中删除1号班级，报错如图，请问原因是什么？ 创建外键约束的语法？ 请列出ComboBox的常用属性 使用DataGridView进行筛选使用其什么属性？ 如何使用DataRelation实现主从表的关联？,本单元任务,任务一 为学生表插入十万行数据 任务二 完成“转专业”模块,新增管理员,新增教师,新增学生,本单元目标,掌握如何定义变量并赋值 掌握如何输出数据 掌握逻辑控制语句 理解批处理的概念 理解事务的概念 掌握ADO.NET中控制事务的方法,任务一为学生表插入十万行数据,任务描述：,任务分析：,为了进行系统的压力测试，为学生表添加十万行数据 。,1使用INSERT语句可为学生表添加一行数据。 2使用循环控制可添加n。 3如果十万行数据都一样，则意思不大。因此，要使用随机数，产生不同的数据。 4考虑系统性能，应每100行提交一次。,使用变量,变量分为： 局部变量： 局部变量必须以标记作为前缀 ，如age 局部变量的使用也是先声明，再赋值 全局变量： 全局变量必须以标记 作为前缀，如version 全局变量由系统定义和维护，我们只能读取，不能修改全局变量的值,局部变量,例如： DECLARE name varchar(8) DECLARE seat int,声明局部变量 DECLARE 变量名 数据类型,赋值 SET 变量名 =值 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 From Student Where StudentName = name Select * from Student Where ClassID = classID,全局变量,全局变量都使用两个标志作为前缀,输出语句 print,print SQL Server的版本+VERSION print 服务器的名称: +SERVERNAME INSERT INTO stuInfo(stuName,stuNo,stuSex,stuAge) VALUES(武松,s25328,男,23) -如果大于0表示上一条语句执行有错误 print 当前错误号+convert(varchar(5),ERROR) print 刚才报名的学员，座位号为: +convert(varchar(5),IDENTITY ) UPDATE stuinfo SET stuAge=85 WHERE stuName=李文才 print 当前错误号+convert(varchar(5),ERROR) GO,演示：使用全局变量,参考语句,SQL Server的版本,服务器名称,座位号（自动编号）,错误号,错误号,逻辑控制语句,IFELSE语句,SQL中的IF-ELSE语句 IF （条件） BEGIN 语句1 语句2 END ELSE BEGIN 语句1； 语句2； END,ELSE是可选部分 如果有多条语句，才需要BEGIN-END语句块,IF-ELSE示例,分析： （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 else begin print 成绩一般 select top 3 studentno from score where subjectID=1 order by score1 end,逻辑控制语句,CASEEND多分支语句1,CASE 输入值 WHEN 值1 THEN 结果1 WHEN 值2 THEN 结果2 ELSE 其他结果 END,CASE示例1,【例7-4】学生的性别：0为男生，1为女生，要求查询每位学生的学号和性别,Select StudentNo, 性别 = CASE SEX WHEN 0 THEN 男生 WHEN 1 THEN 女生 END FROM Student,逻辑控制语句,CASEEND多分支语句2,CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2 ELSE 其他结果 END,CASE示例2,问题： 采用美国的ABCDE五级打分制来显示总评成绩。 A级: 90分以上 B级： 8089分 C级: 7079分 D级： 6069分 E级： 60分以下,Select StudentNo, 分数 = CASE WHEN score1=60 and score1=70 and score1=80 and score190 THEN B ELSE A END FROM score,逻辑控制语句,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,WHILE示例2,问题： 本次考试成绩较差，假定要提分，确保每人笔试都通过。提分规则很简单，先每人都加2分，看是否都通过，如果没有全部通过，每人再加2分，再看是否都通过，如此反复提分，直到所有人都通过为止 。,学员成绩表,分析： 第一步，统计没通过的人数 ； 第二步，如果有人没通过，加分； 第三步，循环判断。,DECLARE n int WHILE(1=1) -条件永远成立 BEGIN SELECT n=COUNT(*) FROM stuMarks WHERE writtenExam0) UPDATE stuMarks -每人加2分 SET writtenExam=writtenExam+2 ELSE BREAK -退出循环 END print 加分后的成绩如下： SELECT * FROM stuMarks,WHILE示例,参考语句,批处理语句,批处理是包含一个或多个 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语句编译为一个执行单元，提高执行效率 一般是将一些逻辑相关的业务操作语句，放置在同一批中，这完全由业务需求和代码编写者决定,批处理语句示例,-例如建表语句的末尾必须添加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位随机字符串,事务,银行转账,账户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(customerName,currentMoney) VALUES(张三,1000) INSERT INTO bank(customerName,currentMoney) VALUES(李四,1),创建账户表，存放用户的账户信息,添加约束：根据银行规定，账户余额不能少于1元，否则视为销户,张三开户，开户金额为1000元 ；李四开户，开户金额1元,为什么需要事务,目前两个账户的余额总和为：1000+1=1001元,为什么需要事务,模拟实现转账 ： 从张三的账户转账1000元到李四的账户,/*-转账测试：张三转账1000元给李四-*/ -我们可能会这样这样编写语句 -张三的账户少1000元，李四的账户多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元 UPDATE bank SET currentMoney=currentMoney-1000 WHERE customerName=张三 UPDATE bank SET currentMoney=currentMoney+1000 WHERE customerName=李四 GO,错误原因分析：,执行失败，所以张三还是1000元,继续往下执行：执行成功，所以李四变为1001元,如何解决呢？使用事务,UPDATE语句违反约束： 余额=1元,什么是事务,事务是作为逻辑工作单元执行的一系列操作。它包含了一组SQL语句，但整组SQL作为一个整体向系统提交或撤销。对于大型的数据库应用系统，如订票系统、银行系统等特别适合。 事务有四个属性，称为 ACID（原子性、一致性、隔离性和持久性）属性，只有这样才能成为一个事务。,转账过程就是一个事务。 它需要两条UPDATE语句来完成，这两条语句是一个整体，如果其中任一条出现错误，则整个转账业务也应取消，两个账户中的余额应恢复到原来的数据，从而确保转账前和转账后的余额不变，即都是1001元。,创建事务,T-SQL管理事务的语句有： BEGIN TRANSACTION： 开始事务。 COMMIT TRANSACTION： 提交事务。 ROLLBACK TRANSACTION： 回滚事务。 事务的分类有： 显式事务：通过发出BEGIN TRANSACTION语句显式启动事务。 自动提交事务：SQL Server的默认模式。每个单独的T-SQL语句作为一个事务，成功后提交，错误则自动回滚。 隐性事务：通过设置SET IMPLICIT_TRANSACTIONS ON语句，将隐性事务模式设置为打开。在隐性事务操作时，提交或回滚事务后自动启动一个新事务。,解决银行转账问题,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 -累计是否有错误 /*-根据是否有错误，确定事务是提交还是撤销-*/ IF errorSum0 -如果有错误 BEGIN print 交易失败，回滚事务 ROLLBACK TRANSACTION END ELSE BEGIN print 交易成功，提交事务，写入硬盘，永久的保存 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(convert(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需要登记手续办理情况：学号、学籍变动类型、何时办理、申请理由、审核领导等；如果是转专业，还需要登记转出专业、转入专业、转入方审核领导等。 2登记学籍变动信息后，还需要修改该学生的状态，学籍变动造成的学生状态变化如图所示：,ADO.NET控制事务,在ADO.NET中，可以使用Connection和Transaction对象来控制事务。 执行事务： 调用Connection对象的BeginTransaction开始事务。 将Transaction对象分配给要执行的Command的Transaction属性。 执行所需的命令。 调用Transaction对象的Commit方法来完成事务，或调用Rollback方法来取消事务。,ADO.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 = myTrans; 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, txtNewName.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保存学籍变动信息，用事务控制,代码分析,保存学籍变动信息，用事务控制,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(type) 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) Values(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(“INSERT INTO StuStatusChange(StudentNO,TypeID, ChangeDate,OldSpecID,Agree, OldHead,Reason,Memo,NewS