这篇博客主要介绍了SQL Server中的事务处理，包括如何确保一组操作全部成功或全部回滚。此外，还探讨了游标的使用，展示了如何遍历并打印成本表的所有数据。接着，定义了一个存储过程，用于计算特定单位在指定时间范围内的成本运行情况。最后，创建了三个触发器，分别在插入、更新和删除成本表数据时自动计算和调整结算金额、删除材料消耗表的相关记录。
一、实验目的
1．掌握事务的概念、性质、定义及使用；
2．掌握游标的概念、组成、创建及使用；
3．掌握存储过程的概念、类型、特点、创建、执行及管理。
4．掌握触发器的概念、创建、管理及使用。
二、相关学习链接
SQL Sever游标
SQL Sever事务处理
SQL Sever基础之存储过程
SQL Sever基础之触发器
三、实验内容
1．进行如下事务处理练习（把下列五条语句作为一个事务处理，只有五条语句全部成功执行才做提交，并给出成功的提示信息；否则就做回退处理，并给出具体的错误提示信息）：
insert into 成本表 values(‘zy2021006’,‘112202002’,‘y005’,
10000,‘张三’, ‘07-01-2021’ ,‘07-04-2021’,‘07-25-2021’,
‘作业公司作业一队’,‘堵漏’,7000,2500,1000,1400,11900,
‘李四’,‘07-26-2021’,11900,‘王五’,‘07-28-2021’)
insert into材料消耗表values(‘zy2021006’,‘wm001’,200)
insert into材料消耗表values(‘zy2021006’,‘wm002’,200)
insert into材料消耗表values(‘zy2021006’,‘wm003’,200)
insert into材料消耗表values(‘zy2021006’,‘wm004’,100)
begin transaction
go
-- insert into 成本表 values('zy2021006','112202002','y005',10000,'张三', '07-01-2021' ,'07-04-2021','07-25-2021','作业公司作业一队','堵漏',7000,2500,1000,1400,11900,'李四','07-26-2021',11900,'王五','07-28-2021')
insert into 材料消耗表 values('zy2021006','wm001',200)
insert into 材料消耗表 values('zy2021006','wm002',200)
insert into 材料消耗表 values('zy2021006','wm003',200)
insert into 材料消耗表 values('zy2021006','wm004',100)
if @@error<>0
begin
print '语句执行失败'
rollback
end
else
begin
print '语句执行成功'
commit
end;
把第一个插入语句注释掉之后：
2．进行如下游标练习：
定义一个游标，用于存放成本表的全部行数据，并打印以下表头和各行数据。
表头：单据号 预算单位 井号 预算金额 预算人 预算日期 开工日期 完工日期 施工单位 施工内容 材料费 人工费 设备费 其它费用 结算金额 结算人 结算日期 入账金额 入账人 入账日期
执行以上所定义的游标，查看是否能正确输出结果。
begin tran
declare yb cursor for
select * from 成本表
open yb
declare @单据号 varchar(20)
declare @预算单位 varchar(20)
declare @井号 varchar(20)
declare @预算金额 varchar(20)
declare @预算人 varchar(20)
declare @预算日期 varchar(20)
declare @开工日期 varchar(20)
declare @完工日期 varchar(20)
declare @施工单位 varchar(20)
declare @施工内容 varchar(20)
declare @材料费 varchar(20)
declare @材料一费用 varchar(20)
declare @材料二费用 varchar(20)
declare @材料三费用 varchar(20)
declare @材料四费用 varchar(20)
declare @人工费 varchar(20)
declare @设备费 varchar(20)
declare @其他费用 varchar(20)
declare @结算金额 varchar(20)
declare @结算人 varchar(20)
declare @结算日期 varchar(20)
declare @入账金额 varchar(20)
declare @入账人 varchar(20)
declare @入账日期 varchar(20)
print '单据号 '+'预算单位 '+'井号 '+'预算金额 '+'预算人 '+'预算日期 '+'开工日期 '+'完工日期 '+'施工单位 '+'施工内容 '+'材料费 '+'材料一费用 '+'材料二费用 '+'材料三费用 '+'材料四费用 '+'人工费 '+ '设备费 '+'其他费用 '+'结算金额 '+'结算人 '+'结算日期 '+'入账金额 '+'入账人 '+'入账日期'
fetch next from yb
into
@单据号,
@预算单位,
@井号,
@预算金额,
@预算人,
@预算日期,
@开工日期,
@完工日期,
@施工单位,
@施工内容,
@材料费,
@材料一费用,
@材料二费用,
@材料三费用,
@材料四费用,
@人工费,
@设备费,
@其他费用,
@结算金额,
@结算人,
@结算日期,
@入账金额,
@入账人,
@入账日期
while(@@fetch_status = 0)
begin
print
@单据号+' '+
@预算单位+' '+
@井号+' '+
@预算金额+' '+
@预算人+' '+
@预算日期+' '+
@开工日期+' '+
@完工日期+' '+
@施工单位+' '+
@施工内容+' '+
@材料费+' '+
@材料一费用+' '+
@材料二费用+' '+
@材料三费用+' '+
@材料四费用+' '+
@人工费+' '+
@设备费+' '+
@其他费用+' '+
@结算金额+' '+
@结算人+' '+
@结算日期+' '+
@入账金额+' '+
@入账人+' '+
@入账日期
fetch next from yb
into
@单据号,
@预算单位,
@井号,
@预算金额,
@预算人,
@预算日期,
@开工日期,
@完工日期,
@施工单位,
@施工内容,
@材料费,
@材料一费用,
@材料二费用,
@材料三费用,
@材料四费用,
@人工费,
@设备费,
@其他费用,
@结算金额,
@结算人,
@结算日期,
@入账金额,
@入账人,
@入账日期
end
close yb
3．定义一个存储过程，要求完成以下功能：
生成某单位(单位可以是采油厂或采油矿或采油队)某段时间内的成本运行情况（输入参数：单位代码 起始日期 结束日期）。
输出格式 *单位时间—**时间成本运行情况
预算金额 结算金额 入账金额 未结算金额 未入账金额
**. **. **. **. **.
其中：未结算金额=预算金额-结算金额
未入账金额=结算金额-入账金额
分三种情况（单位分别为：采油厂、采油矿、采油队）执行以上定义的存储过程，查看执行输出结果。
if object_id('_someDepCost','p') is not null
drop procedure _someDepCost;
go
create procedure _someDepCost
@单位代码 varchar(40),
@起始日期 datetime,
@结束日期 datetime
as
declare @单位名称 varchar(40);
declare @预算金额 money,
@结算金额 money,
@入账金额 money,
@未结算金额 money,
@未入账金额 money;
select @单位名称 = 单位名称 from 单位表
where 单位代码 = @单位代码
set @预算金额 = (select sum(预算金额) from 成本表
where 预算单位 like @单位代码 + '%' and (预算日期 between @起始日期 and @结束日期))
set @结算金额 = (select sum(结算金额) from 成本表
where 预算单位 like @单位代码 + '%' and (结算日期 between @起始日期 and @结束日期))
set @入账金额 = (select sum(入账金额) from 成本表
where 预算单位 like @单位代码 + '%' and (入账日期 between @起始日期 and @结束日期) and
(入账金额 is not null))
set @未结算金额 = (select sum(预算金额) from 成本表
where (结算金额 is null) and (预算日期 between @起始日期 and @结束日期))
set @未入账金额 = (select sum(结算金额) from 成本表
where (入账金额 is null) and (结算日期 between @起始日期 and @结束日期))
set @未结算金额 = @预算金额 - @结算金额
set @未入账金额 = @结算金额 - @入账金额
if @预算金额 is null
set @预算金额 = 0
if @结算金额 is null
set @结算金额 = 0
if @入账金额 is null
set @入账金额 = 0
if @未结算金额 is null
set @未结算金额 = 0
if @未入账金额 is null
set @未入账金额 = 0
declare @result varchar(100)
set @result = convert(varchar,@预算金额)+' '+convert(varchar,@结算金额)+
' '+convert(varchar,@入账金额)+' '+convert(varchar,@未结算金额)+' '+convert(varchar,@未入账金额)
print @单位名称+'单位'+convert(varchar,@起始日期,102)+'--'+convert(varchar,@结束日期,102)+'成本运营状况'
print '预算金额 结算金额 入账金额 未结算金额 未入账金额'
print @result
go
execute _someDepCost @单位代码 = '1122',@起始日期 = '2016-5-1',@结束日期 = '2020-5-29';
execute _someDepCost @单位代码 = '112201',@起始日期 = '2020-5-1',@结束日期 = '2020-5-29';
execute _someDepCost @单位代码 = '112201001',@起始日期 = '2020-5-1',@结束日期 = '2020-5-29';
go
4．针对成本表定义三个触发器，分别完成以下功能：
⑴ 对成本表插入一行数据时，自动计算并插入结算金额字段（结算金额=材料费+人工费+设备费+其它费用）。
⑵ 当修改成本表的某行数据时自动修改结算金额字段。
⑶ 当删除成本表中一行数据时，自动删除材料消耗表中相应明细数据。
⑷ 对上述3个触发器用适当的更新语句进行验证，并查看结果是否达到预期结果。
if OBJECT_ID('trmytr1','TR') is not null
drop trigger tr_mytrl
go
create trigger trmytr1 on 成本表
after insert
as
declare @结算金额 money
declare @temp1 money,@temp2 money,@temp3 money,@temp4 money;
select @temp1 = 材料费,@temp2 = 人工费,@temp3 = 设备费,@temp4 = 其他费用
from inserted;
set @结算金额 = @temp1+@temp2+@temp3+@temp4;
update 成本表
set 结算金额 = @结算金额
where 单据号 = (select 单据号 from inserted);
go
--(2)---------------------------------------------
if OBJECT_ID('trmytr2','TR') is not null
drop trigger tr_mytr2
go
create trigger trmytr2 on 成本表
after update
as
declare @结算金额 money
declare @temp1 money,@temp2 money,@temp3 money,@temp4 money;
select @temp1 = 材料费,@temp2 = 人工费,@temp3 = 设备费,@temp4 = 其他费用
from inserted;
set @结算金额 = @temp1+@temp2+@temp3+@temp4;
update 成本表
set 结算金额 = @结算金额
where 单据号 = (select 单据号 from inserted);
go
--(3)----------------------------------
if OBJECT_ID('trmytr3','TR') is not null
drop trigger tr_mytr3
go
create trigger trmytr3 on 成本表
instead of delete
as
delete from 材料消耗表
where 单据号 = (select 单据号 from deleted);
--(4)------------------------
begin tran
/*(1)*/ insert into 成本表(单据号,预算人,材料费,人工费,设备费,其他费用)
values('2017000','test',100,200,300,400);
delete from 成本表 where 单据号 = '2017000';
/*(2)*/ insert into 成本表(单据号,预算人,材料费,人工费,设备费,其他费用)
values('2020111','test',100,200,300,400);
update 成本表
set 材料费 = 100000
where 单据号 = '2017111';
/*(3)*/insert into 成本表(单据号,预算人,材料费,人工费,设备费,其他费用)
values('2020110','test',100,200,300,400);
insert into 材料消耗表
values('2017110','wm001');
select 单据号,物码
from
材料消耗表;
delete from 成本表
where 单据号 = '2020110';
select 单据号,物码
from 材料消耗表
rollback
扫一扫
1212
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
