1 / 28

学习情景 6

学习情景 6. 设计和创建学生成绩数据库的存储过程和触发器. 技能目标： ● 能够创建数据库的存储过程（含游标使用） ● 能够创建数据库表的触发器 知识目标： ● 掌握存储过程的概念、语法和规则 ● 了解游标的概念、使用语法和方法 ● 掌握触发器的概念、用法、语法等相关知识. 任务 1: 当教师成绩录入完成后，教学秘书按班级打印课程成绩汇总信息（班级平均分、最高分、最低分、及格率）给教师，用于分析。. 如果知道班级号 31012331 ，课程号： 12312050 declare @num float,@num_pas float

usoa
Download Presentation

学习情景 6

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 学习情景6 设计和创建学生成绩数据库的存储过程和触发器

  2. 技能目标： ● 能够创建数据库的存储过程（含游标使用） ● 能够创建数据库表的触发器 知识目标： ● 掌握存储过程的概念、语法和规则 ● 了解游标的概念、使用语法和方法 ● 掌握触发器的概念、用法、语法等相关知识

  3. 任务1: 当教师成绩录入完成后，教学秘书按班级打印课程成绩汇总信息（班级平均分、最高分、最低分、及格率）给教师，用于分析。 如果知道班级号31012331，课程号：12312050 declare @num float,@num_pas float select @num=count(*) from sc where sc.cno='12312050' and substring(sc.sno,1,8)='31012331' select @num_pas=count(*) from sc where sc.cno='12312050' and substring(sc.sno,1,8)='31012331' and score>=60

  4. Select substring(sc.sno,1,8) classno,cno, count(*) num ,avg(score) avgscore,max(score) max, min(score) min,@num_pas/@num passinggrade into #t from sc where sc.cno='12312050' and substring(sc.sno,1,8)='31012331' group by substring(sc.sno,1,8),sc.cno select #t.classno,classname,#t.cno,cname,num,avgscore,max,min,passinggrade from #t,class,c where #t.classno=class.classno and #t.cno=c.cno 问题：不同教师上不同课，总不能每次修改SQL语句，然后把查询语句传送给学校数据库服务器。这样会导致（1）网络流量大（2）客户端写SQL语句复杂（3）查询效率不高

  5. 解决办法：存储过程 问题：不同教师上不同课，总不能每次修改SQL语句，然后把查询语句传送给学校数据库服务器。这样会导致（1）网络流量大（2）客户端写SQL语句复杂（3）查询效率不高 CREATE PROCEDURE stusp_scoresum @classno char(8),@cno char(8) as begin declare @num float,@num_pas float select @num=count(*) from sc where sc.cno=@cno and substring(sc.sno,1,8)=@classno select @num_pas=count(*) from sc where sc.cno=@cno and substring(sc.sno,1,8)=@classno and score>=60

  6. 解决办法：存储过程 Select substring(sc.sno,1,8) classno,cno, count(*) num ,avg(score) avgscore,max(score) max, min(score) min,@num_pas/@num passinggrade into #t from sc where sc.cno=@cno and substring(sc.sno,1,8)=@classno group by substring(sc.sno,1,8),sc.cno select #t.classno,classname,#t.cno,cname,num,avgscore,max,min,passinggrade from #t,class,c where #t.classno=class.classno and #t.cno=c.cno end 查询： stusp_scoresum '31012331','12312051‘ EXECUTE stusp_scoresum '31012331','12312051‘ EXEC stusp_scoresum '31012331','12312051'

  7. [相关知识1.1]存储过程 概念：储存在服务器上的T-SQL程序（预先定义并编译好的），保存在数据库中 目的：减轻网络流量，提高执行效率 注意 存储过程与T-SQL程序的区别

  8. [相关知识1.2]创建存储过程方法 1. 使用SQL语句 2. 使用SQL-EM

  9. [相关知识1.3]使用SQL语句 语句格式: CREATE PROCEDURE <存储过程名> [@<局部变量名><数据类型>[,…n]] AS <T-SQL语句>

  10. [相关知识1.4]执行存储过程 语句格式: EXEC[UTE] <存储过程名>[<参数值>[,…]] 说明： 如EXEC[UTE] 语句是批处理的第一条语句，则可省略EXEC[UTE]

  11. 任务2：使用SQL-EM创建表class_num(classno[char(8)，主键],num(smallint),mannum(smallint),womannum(smallint))，用于存放各班学生人数的统计信息， 然后编写存储过程stusp_classtotal_sum，用于根据表s的信息统计汇总数据插入表n。 SQL-EM→表→右键→新建表 class_num SQL-EM→存储过程→右键→新建存储过程(名称改为stusp_classtotal_sum后输入下面代码)

  12. delete from class_num insert into class_num(classno,num) select classno, count(*) from s group by classno select classno, count(*) as mnum into tt from s where ssex='男' group by classno update class_num set mannum=mnum from tt where tt.classno=class_num.classno drop table tt select classno, count(*) as mnum into tt from s where ssex='女' group by classno update class_num set womannum=mnum from tt where tt.classno=class_num.classno drop table tt update class_num set womannum=0 where womannum is null update class_num set mannum=0 where mannum is null 执行：EXEC stusp_classtotal_sum

  13. [技能拓展1]查看和修改存储过程 SQL-EM→存储过程→右键→属性

  14. [技能拓展2]删除存储过程 1. 使用SQL语句 语句格式： DROP PRODRUCE <存储过程名>[,…n] 2. 使用SQL-EM SQL-EM→存储过程→右键→删除

  15. 拓展任务1：查询学生学号、姓名、平均成绩，按平均分排名次，并显示名次。拓展任务1：查询学生学号、姓名、平均成绩，按平均分排名次，并显示名次。 create procedure printstuinf as begin declare @n smallint ,@sno char(10),@sname char(20),@avg smallint set @n=0 declare cur_s cursor for select s.sno, sname,avg from s,( select sno,avg(score) avg from sc group by sno) as t where s.sno=t.sno order by avg desc print '名次 学号 姓名 平均成绩' print '--------------------------------------------'

  16. 拓展任务1：查询学生学号、姓名、平均成绩，按平均分排名次，并显示名次。拓展任务1：查询学生学号、姓名、平均成绩，按平均分排名次，并显示名次。 open cur_s fetch next from cur_s into @sno, @sname,@avg while @@fetch_status=0 begin set @n=@n+1 print str(@n,4)+' '+@sno+' '+@sname+str(@avg,2) fetch next from cur_s into @sno, @sname,@avg end close cur_s deallocate cur_s end

  17. [知识拓展1.1]游标概念 游标的概念：对查询到的记录需要做逐一处理，而 不是对整个记录集进行同一操作。 T-SQL游标： 用于服务器，最常用 游标的种类 API游标： 用于客户端与服务器的连接程序 客户游标： 用于客户端应用程序

  18. [知识拓展1.2]使用游标 声明游标： DECLARE <游标名> CURSOR FOR <SELECT语句> 打开游标： OPEN <游标名> 读取游标： FETCH NEXT FROM <游标名> [INTO @<变量名>[,...n]] @@FETCH_STATUS=0 FETCH执行成功 -1 FETCH执行失败-行超出范围 -2 所读数据不存在 关闭游标： CLOSE <游标名> 释放游标： DEALLOCATE <游标名>

  19. 任务3：class_num表用于存放各班级的总人数和男女生人数，则为了保证数据库数据的完整性，必须在对班级表和学生表修改后，同时更新class_num表。如何实现上述过程自动进行。任务3：class_num表用于存放各班级的总人数和男女生人数，则为了保证数据库数据的完整性，必须在对班级表和学生表修改后，同时更新class_num表。如何实现上述过程自动进行。 Create trigger set_class_num_in on s for insert as begin select classno,count(*) as numw into #T1 from inserted where ssex='女' group by classno select classno,count(*)as numw into #T2 from inserted where ssex='男' group by classno

  20. update class_num set num=num+numw,womannum=womannum+numw from #T1 where class_num.classno=#T1.classno update class_num set num=num+numw,mannum=mannum+numw from #T2 where class_num.classno=#T2.classno end

  21. create trigger set_class_num_d on s for delete as begin select classno,count(*) as numw into #T1 from deleted where ssex='女' group by classno select classno,count(*)as numw into #T2 from deleted where ssex='男' group by classno update class_num set num=num-numw,womannum=womannum-numw from #T1 where class_num.classno=#T1.classno update class_num set num=num-numw,mannum=mannum-numw from #T2 where class_num.classno=#T2.classno end

  22. Create trigger set_class_num_up on s for update as begin select classno,count(*) as numw into #T1 from inserted where ssex='女' group by classno select classno,count(*)as numw into #T2 from inserted where ssex='男' group by classno update class_num set num=num+numw,womannum=womannum+numw from #T1 where class_num.classno=#T1.classno update class_num set num=num+numw,mannum=mannum+numw from #T2 where class_num.classno=#T2.classno

  23. select classno,count(*) as numw into #T3 from deleted where ssex='女' group by classno select classno,count(*)as numw into #T4 from deleted where ssex='男' group by classno update class_num set num=num-numw,womannum=womannum-numw from #T3 where class_num.classno=#T3.classno update class_num set num=num-numw,mannum=mannum-numw from #T4 where class_num.classno=#T4.classno end

  24. [相关知识3.1]触发器概述 当修改（插入、删除、修改）表s后，如何保证表n数据与表s数据的一致性。 引 入： 触发器是建立在表上的特殊的存储过程，当对该表进行插入、删除、修改操作后，将自动执行相应的insert、delete、update触发器。 概 念： 执行方式： ● insert(delete)：对表插入(删除)记录时，将插入(删除)的记录放入inserted(deleted)表中，该表为一逻辑表(结构与原表相同)，保存插入(删除)的记录，然后执行触发器指定的操作。 ●update：先delete，再insert。

  25. [相关知识3.2]创建触发器 1. 使用SQL-EM SQL-EM→表→右键→所有任务→管理触发器 2. 使用SQL语句

  26. [相关知识3.3]使用SQL语句 语句格式: CREATE TRIGGER <触发器名> ON <表名> FOR INSERT| DELETE | UPDATE AS <T-SQL语句> 注意：如果需要创建触发器，通常应同时创建INSERT、 DELETE和UPDATE才能保证数据的一致性

  27. [技能拓展3]查看和修改触发器 SQL-EM→表→右键→所有任务→管理触发器 2. 使用SQL语句 语句格式：ALTER TRIGGER <触发器名> ON <表名> FOR INSERT| DELETE | UPDATE AS <T-SQL语句>

  28. [技能拓展4]删除触发器 1. 使用SQL-EM SQL-EM→表→右键→所有任务→管理触发器 2. 使用SQL语句 语句格式：DROP TRIGGER <触发器名>[,…n]

More Related