Transcript 数据库原理与SQL Server

《网络数据库技术》
—— 省级精品课程
http://www.qzct1.net/yjj
学习情景6
设计和创建学生成绩数据库的存储过程和触发器
主讲：余建军
衢州职业技术学院
网络数据库技术
http://www.qzct1.net/yjj
技能目标：
●
能够创建数据库的存储过程（含游标使用）
●
能够创建数据库表的触发器
知识目标：
● 掌握存储过程的概念、语法和规则
● 了解游标的概念、使用语法和方法
● 掌握触发器的概念、用法、语法等相关知识
衢州职业技术学院
网络数据库技术
http://www.qzct1.net/yjj
任务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
衢州职业技术学院
网络数据库技术
http://www.qzct1.net/yjj
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）查询效率不
高
衢州职业技术学院
网络数据库技术
http://www.qzct1.net/yjj
解决办法：存储过程
问题：不同教师上不同课，总不能每次修改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
衢州职业技术学院
网络数据库技术
http://www.qzct1.net/yjj
解决办法：存储过程
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'
衢州职业技术学院
网络数据库技术
http://www.qzct1.net/yjj
[相关知识1.1]存储过程
概念：储存在服务器上的T-SQL程序（预先定义并编译好
的），保存在数据库中
目的：减轻网络流量，提高执行效率
注意
存储过程与T-SQL程序的区别
衢州职业技术学院
网络数据库技术
http://www.qzct1.net/yjj
[相关知识1.2]创建存储过程方法
1. 使用SQL语句
2. 使用SQL-EM
衢州职业技术学院
网络数据库技术
http://www.qzct1.net/yjj
[相关知识1.3]使用SQL语句
语句格式:
CREATE PROCEDURE <存储过程名> [@<局部变量名><数据类型>[,…n]]
AS
<T-SQL语句>
衢州职业技术学院
网络数据库技术
http://www.qzct1.net/yjj
[相关知识1.4]执行存储过程
语句格式:
EXEC[UTE] <存储过程名>[<参数值>[,…]]
说明：
如EXEC[UTE] 语句是批处理的第一条语句，则可省略EXEC[UTE]
衢州职业技术学院
网络数据库技术
http://www.qzct1.net/yjj
任务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后输入下面代码)
衢州职业技术学院
网络数据库技术
http://www.qzct1.net/yjj
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
衢州职业技术学院
网络数据库技术
http://www.qzct1.net/yjj
[技能拓展1]查看和修改存储过程
SQL-EM→存储过程→右键→属性
衢州职业技术学院
网络数据库技术
http://www.qzct1.net/yjj
[技能拓展2]删除存储过程
1. 使用SQL语句
语句格式：
DROP PRODRUCE <存储过程名>[,…n]
2. 使用SQL-EM
SQL-EM→存储过程→右键→删除
衢州职业技术学院
网络数据库技术
http://www.qzct1.net/yjj
拓展任务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 '--------------------------------------------'
衢州职业技术学院
网络数据库技术
http://www.qzct1.net/yjj
拓展任务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
衢州职业技术学院
网络数据库技术
http://www.qzct1.net/yjj
[知识拓展1.1]游标概念
游标的概念：对查询到的记录需要做逐一处理，而
不是对整个记录集进行同一操作。
T-SQL游标： 用于服务器，最常用
游标的种类
API游标：用于客户端与服务器的连接程序
客户游标：用于客户端应用程序
衢州职业技术学院
网络数据库技术
http://www.qzct1.net/yjj
[知识拓展1.2]使用游标
声明游标： DECLARE <游标名> CURSOR
FOR
<SELECT语句>
打开游标： OPEN <游标名>
读取游标： FETCH NEXT FROM <游标名> [INTO @<变量名>[,...n]]
@@FETCH_STATUS=0
-1
-2
关闭游标：
FETCH执行成功
FETCH执行失败-行超出范围
所读数据不存在
CLOSE <游标名>
释放游标： DEALLOCATE <游标名>
衢州职业技术学院
网络数据库技术
http://www.qzct1.net/yjj
任务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
衢州职业技术学院
网络数据库技术
http://www.qzct1.net/yjj
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
衢州职业技术学院
网络数据库技术
http://www.qzct1.net/yjj
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
衢州职业技术学院
网络数据库技术
http://www.qzct1.net/yjj
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
衢州职业技术学院
网络数据库技术
http://www.qzct1.net/yjj
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
衢州职业技术学院
网络数据库技术
http://www.qzct1.net/yjj
[相关知识3.1]触发器概述
引
概
入：当修改（插入、删除、修改）表s后，如何保证表n
数据与表s数据的一致性。
念：触发器是建立在表上的特殊的存储过程，当对该表
进行插入、删除、修改操作后，将自动执行相应的
insert、delete、update触发器。
执行方式：● insert(delete)：对表插入(删除)记录时，将插入
(删除)的记录放入inserted(deleted)表中，该表为一
逻辑表(结构与原表相同)，保存插入(删除)的记录，
然后执行触发器指定的操作。
●update：先delete，再insert。
衢州职业技术学院
网络数据库技术
http://www.qzct1.net/yjj
[相关知识3.2]创建触发器
1. 使用SQL-EM
SQL-EM→表→右键→所有任务→管理触发器
2. 使用SQL语句
衢州职业技术学院
网络数据库技术
http://www.qzct1.net/yjj
[相关知识3.3]使用SQL语句
语句格式:
CREATE TRIGGER <触发器名>
ON <表名>
FOR INSERT| DELETE | UPDATE
AS
<T-SQL语句>
注意：如果需要创建触发器，通常应同时创建INSERT、
DELETE和UPDATE才能保证数据的一致性
衢州职业技术学院
网络数据库技术
http://www.qzct1.net/yjj
[技能拓展3]查看和修改触发器
SQL-EM→表→右键→所有任务→管理触发器
2. 使用SQL语句
语句格式：ALTER TRIGGER <触发器名> ON <表名>
FOR INSERT| DELETE | UPDATE
AS
<T-SQL语句>
衢州职业技术学院
网络数据库技术
http://www.qzct1.net/yjj
[技能拓展4]删除触发器
1. 使用SQL-EM
SQL-EM→表→右键→所有任务→管理触发器
2. 使用SQL语句
语句格式：DROP TRIGGER <触发器名>[,…n]
衢州职业技术学院