create procedure dbo.pro (@banjiid int)
asset xact_abort on
begin tranupdate g_qinshi set yizhu = isnull(yizhu,0) - d.cnt
from g_qinshi a
inner join
(
select qinshiid, count(*) cnt
from s_xuesheng b inner join g_qinshiruzhu c on b.xueshengid = c.xueshengid
where banjiid =@banjiid
group by qinshiid
) d on a.qinshiid=d.qinshiiddelete g_qinshiruzhu
from g_qinshiruzhu a
inner join s_xuesheng b on a.xueshengid = b.xueshengid
where b.banjiid = @banjiidcommit tran
return 0
go
asset xact_abort on
begin tranupdate g_qinshi set yizhu = isnull(yizhu,0) - d.cnt
from g_qinshi a
inner join
(
select qinshiid, count(*) cnt
from s_xuesheng b inner join g_qinshiruzhu c on b.xueshengid = c.xueshengid
where banjiid =@banjiid
group by qinshiid
) d on a.qinshiid=d.qinshiiddelete g_qinshiruzhu
from g_qinshiruzhu a
inner join s_xuesheng b on a.xueshengid = b.xueshengid
where b.banjiid = @banjiidcommit tran
return 0
go
update g_qinshi set yizhu = isnull(yizhu,0) - d.cnt
from g_qinshi a
inner join
(
select qinshiid, count(*) cnt
from s_xuesheng b inner join g_qinshiruzhu c on b.xueshengid = c.xueshengid
where banjiid =@banjiid
group by qinshiid
) d on a.qinshiid=d.qinshiid
就这一部分啊,讲解一下可以吗?或者介绍一本书我去下载来看看
create procedure pro (@banjiid int)
as
update G_qinshi set yizhu=yizhu-1 from G_qinshi where qinshiID in(select qinshiID from G_qinshiRuzhu where xueshengID in (select xueshengID from S_xuesheng where banjiID=@banjiid))
delete G_qinshiRuzhu from G_qinshiRuzhu where xueshengID in (select xueshengID from S_xuesheng where banjiID=@banjiid)
as
update G_qinshi set yizhu=yizhu-1 from G_qinshi where qinshiID in(select qinshiID from G_qinshiRuzhu where xueshengID in (select xueshengID from S_xuesheng where banjiID=@banjiid)) --相关设计寝室减一
delete G_qinshiRuzhu from G_qinshiRuzhu where xueshengID in (select xueshengID from S_xuesheng where banjiID=@banjiid) --然后在删除那个班级的学生的记录
@banjiid varchar(20)
as
set nocount on
create table #s_xuesheng(xueshengID int,banjiid int)
create table #G_qinshiRuzhu(qinshiRuzhuID int,qinshiID int,
xueshengID int )
create table #G_qinshi(qinshiID int,yizhu int)
insert into #s_xuesheng
select 1,5 union
select 2,6 union
select 3,7
insert into #G_qinshiRuzhu
select 5,6,1 union
select 7,8,3
insert into #G_qinshi
select 6,7 union
select 7,8
delete #G_qinshiRuzhu from #G_qinshiRuzhu G
inner join #s_xuesheng S on G.xueshengID=S.xueshengID
where s.banjiid=@banjiid --删除---------------
update #G_qinshi set yizhu=isnull(yizhu,0)-1
select * from #s_xuesheng
select * from #G_qinshiRuzhu
select * from #G_qinshi
drop table #G_qinshi,#s_xuesheng,#G_qinshiRuzhuGO--执行
exec p_del 5
/*结果:
xueshengID banjiID
1 5
2 6
3 7--------------------------
7 8 3---------------------------
6 6
7 7