实验四 SQL练习4 其它数据库对象的管理
一、实验目的
1.掌握事务的概念、性质、定义及使用;
2.掌握游标的概念、组成、创建及使用;
3.掌握存储过程的概念、类型、特点、创建、执行及管理。
4.掌握触发器的概念、创建、管理及使用。
二、实验学时
2学时
三、实验内容
1.进行如下事务处理练习(把下列五条语句作为一个事务处理,只有五条语句全部成功执行才做提交,并给出成功的提示信息;否则就做回退处理,并给出具体的错误提示信息):
INSERT INTO 成本表 VALUES('ZY2022006','112202002','Y005',
10000,'张三', '07-01-2022' ,'07-04-2022','07-25-2022',
'作业公司作业一队','堵漏',7000,2500,1000,1400,11900,
'李四','07-26-2022',11900,'王五','07-28-2022');
INSERT INTO材料消耗表VALUES('ZY2022006','WM001',200)
INSERT INTO材料消耗表VALUES('ZY2022006','WM002',200)
INSERT INTO材料消耗表VALUES('ZY2022006','WM003',200)
INSERT INTO材料消耗表VALUES('ZY2022006','WM004',100)
START TRANSACTION;
GO
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
START
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.针对成本表定义三个触发器,分别完成以下功能:
⑴ 对成本表插入一行数据时,自动计算并插入结算金额字段(结算金额=材料费+人工费+设备费+其它费用)。
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
⑵ 当修改成本表的某行数据时自动修改结算金额字段。
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
⑶ 当删除成本表中一行数据时,自动删除材料消耗表中相应明细数据。
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);
⑷ 对上述3个触发器用适当的更新语句进行验证,并查看结果是否达到预期结果。
BEGIN TRAN
VALUES('2017000','TEST',100,200,300,400);
DELETE FROM 成本表 WHERE 单据号 = '2017000';
VALUES('2020111','TEST',100,200,300,400);
UPDATE 成本表
SET 材料费 = 100000
WHERE 单据号 = '2017111';
VALUES('2020110','TEST',100,200,300,400);
INSERT INTO 材料消耗表
VALUES('2017110','WM001');
SELECT 单据号,物码
FROM
材料消耗表;
DELETE FROM 成本表
WHERE 单据号 = '2020110';
SELECT 单据号,物码
FROM 材料消耗表
ROLLBACK;