SWPU2019数据库实验四
SWPU2019数据库实验四

SWPU2019数据库实验四

针对于2021.6.7日晚的sqlserver数据库实验四的记录,仅供参考,如有抄袭行为与我无关

记得修改个人信息

(一)存储过程(截图内容:存储过程、调用语句、执行结果)

1、创建并调用带参数存储过程,计算某个部门的平均工资

 

存储过程:

USE Company_huanghe

GO

CREATE PROCEDURE HH1 @DNONUMBER char(7)

AS

SELECT DNO,AVG(SALARY)as 平均工资 FROM EMPLOYEE

JOIN DEPARTMENT ON (EMPLOYEE.DNO=DEPARTMENT.DNUMBER)

WHERE DNO=@DNONUMBER

GROUP BY DNO;

GO

 

 

调用语句:

EXECUTE dbo.HH1 '5'

 

 

执行结果:

 

4Q1

 

 

 

2、创建并调用存储过程,计算指定员工(工号)的每周总工作小时数,在存储过程中使用一个输入参数(工号)和一个输出参数(总工作小时数)。

 

存储过程:

USE Company_huanghe

GO

CREATE PROCEDURE HH2 @SSN VARCHAR(50),@SUM NUMERIC(18,1) OUTPUT

AS

SELECT @SUM=SUM(HOURS) FROM Employee,WORKS_ON

WHERE Employee.SSN=WORKS_ON.ESSN AND @SSN=Employee.SSN

GO

 

 

 

调用语句:

DECLARE @SUM NUMERIC(18,1)

EXECUTE dbo.HH2 '123456789',@SUM OUTPUT

PRINT'每周总工作时间(小时):'+STR(@SUM)

 

 

执行结果:

 

 

4Q2

 

 

(二)触发器(截图内容:触发器创建语句、触发语句、执行结果)

1、在项目表上创建一个触发器,当插入一条信息时,显示插入的信息,当删除一条信息时,显示删除的信息,当修改项目信息时分别显示更新前和更新后的项目信息。

 

触发器创建语句:

USE Company_huanghe

GO

CREATE TRIGGER HH3 ON PROJECT FOR INSERT,DELETE,UPDATE

AS

SELECT * FROM INSERTED

SELECT * FROM DELETED

GO

 

 

 

触发语句:

INSERT INTO Project VALUES('TESTPRO','5','BEIJING','5')

DELETE FROM Project WHERE PNAME='TESTPRO'

UPDATE Project SET PNAME='TESTPRO' WHERE Project.PNUMBER=1

 

 

 

执行结果:

 

4Q3
4Q4
4Q5

 

 

[reply]
2、修改部门表结构,新增员工数量字段 Sums,初始值为现有部门的员工数,在此基础上创建触发器实现以下功能:员工表中每插入一条信息,相应部门的Sums值加1。

 

修改部门表结构并创建触发器:

USE Company_huanghe

ALTER TABLE Department

ADD sums int

GO

UPDATE Department SET sums=1 WHERE DNUMBER=1

UPDATE Department SET sums=3 WHERE DNUMBER=4

UPDATE Department SET sums=4 WHERE DNUMBER=5

GO

CREATE TRIGGER HH4 ON EMPLOYEE FOR INSERT

AS

DECLARE @DNO INT

SELECT @DNO=DNO FROM inserted

UPDATE Department SET sums=sums+1 WHERE DNUMBER=@DNO

GO

 

 

 

触发语句:

INSERT INTO Employee(SSN,DNO) VALUES (231213750,1)

 

 

执行结果:

 

4Q6

 

对比前后

 

4Q7

 

3、设计一个更新触发器,员工工资只能升不能降

触发器创建语句:

USE Company_huanghe

GO

CREATE TRIGGER HH5 ON EMPLOYEE FOR UPDATE

AS

DECLARE @NEW_SALARY INT,@OLD_SALARY INT

SELECT @NEW_SALARY=SALARY FROM inserted

SELECT @OLD_SALARY=SALARY FROM deleted

IF (@NEW_SALARY<@OLD_SALARY)

PRINT'操作非法'

ROLLBACK

GO

 

 

 

触发语句:

UPDATE Employee SET SALARY=100 WHERE SSN=333445555

 

 

执行结果:

 

4Q8

 

 

4、为员工表创建一个实现级联删除的触发器,当删除员工表中的某个员工信息时,先检测该员工是否存在,如果不存在,显示“此员工不存在!”,若该员工存在,则先删除该员工在工作记录表和家属表中的信息,最后删除该员工在员工表中的信息。(Instead of类型触发器)

 

触发器创建语句:

USE  Company_huanghe

GO

CREATE TRIGGER HH6 ON EMPLOYEE INSTEAD OF DELETE

AS

DECLARE @SSN VARCHAR(50)

SELECT @SSN=SSN FROM deleted

IF @SSN IN(SELECT SSN FROM Employee)

BEGIN

DELETE FROM WORKS_ON WHERE WORKS_ON.ESSN=@SSN

DELETE FROM Dependent WHERE Dependent.ESSN=@SSN

DELETE FROM Employee WHERE EMPLOYEE.SSN=@SSN

END

ELSE

PRINT'此员工不存在!'

GO

 

 

触发语句:

DELETE FROM Employee WHERE SSN=1111

 

DELETE FROM Employee WHERE SSN=123456789

 

 

执行结果:

1111工号不存在,自然不能删除

 

4Q9

 

可以看到123456789工号对应的smith的数据已经消失了

 

4Q10
4Q11
4Q12
4Q13
六、收获,体会及问题

收获:

实践了如何创建存储过程。

已经设立存储过程的意义:作为已经编译好的过程可以更快的提供服务而无需多次编译。

可以保护数据,用户程序只能通过接口输入,得到处理后的数据,而不能直接对数据本身进行直接操作。

触发器可以方便的代替掉一些繁琐的工作,比如这里的员工信息录入,对应部门的人数自动加一。

触发器可以保护数据,在一定程度上用规则来避免出现一些错误数据。

创建变量需要进行声明,output和declare是一定要加上的。

If语句后面如果是多语句,需要用begin end将其变成一个语句块,不然ifelse语句会报错。

[/reply]
演示到此结束

 

SWPU2019数据库实验三

SWPU2019数据库实验二

4.5 8 投票数
Article Rating
订阅
提醒
32 评论
最新评论
最久评论 最受欢迎的评论
内联反馈
查看所有评论
32
0
有什么想说的吗?评论几句?x
()
x