数据库系统SSD7 实验4《触发器与游标》

小鱼儿 2021-11-09 03:36 313阅读 0赞

#### 实验4《触发器与游标》 ####

##### 一、实验目的 #####

进一步熟悉SQL语句对数据库进行完整性控制的方法；理解触发器的概念、定义方法和触发条件。理解游标的定义、打开、使用、关闭与释放的方法。

##### 二、实验内容 #####

针对实验3所建销售数据库：

1.  设置一个触发器，该触发器仅允许“dbo”用户可以删除employee表内数据，否则出错。
2.  针对employee表写一个DELETE触发器。
3.  针对employee表写一个UPDATE触发器。
4.  统计employee表中员工的平均薪水，输出低于平均薪水的员工的人数以及这些员工的姓名与薪水（使用游标）。

##### 三、实验方法 #####

本次实验主要是利用SQL Server数据库管理工具结合实验3所建的销售数据库进行触发器设置和游标使用。  
  实验方案主要是结合课程PPT和网络教程进行实验开展。

##### 四、实验步骤 #####

##### 五、实验结果 #####

1．设置一个触发器，该触发器仅允许“dbo”用户可以删除employee表内数据，否则出错。

CREATE TRIGGER dboDelete
    ON employee
    FOR DELETE
    AS
    	IF exists (SELECT * from deleted)
    		begin
    			if USER != 'dbo'
    			begin
    				print '只有dbo用户具有删除权限！'
    				rollback
    			end
    		end
    
    DELETE FROM employee WHERE emp_no='E0019'
    SELECT * FROM employee WHERE emp_no='E0019'

当用户为“dbo”时，删除成功：  
![在这里插入图片描述][20190804195524825.png]

2．针对employee表写一个DELETE触发器。

CREATE TRIGGER employeeDelete
    ON employee
    FOR DELETE
    AS
    	print('正在执行employee表的删除操作。')
    
    DELETE FROM employee WHERE emp_no='E0019'

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NTY0NTU1_size_16_color_FFFFFF_t_70]

3．针对employee表写一个UPDATE触发器。

CREATE TRIGGER employeeUpdate
    ON employee
    FOR UPDATE
    AS
    	print('正在执行employee表的更新操作。')
    
    UPDATE employee SET emp_name='王仁华' WHERE emp_no='E0019'

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NTY0NTU1_size_16_color_FFFFFF_t_70 1]

4．统计employee表中员工的平均薪水，输出低于平均薪水的员工的人数以及这些员工的姓名与薪水（使用游标）。

DECLARE @name varchar(10),
    		@salary varchar(10)
    --1.声明游标
    DECLARE lowAvgSalaryEmployee CURSOR
    FOR
    	SELECT emp_name, salary
    	FROM employee
    	WHERE salary<(SELECT avg(salary) FROM employee)
    --2.打开游标
    OPEN lowAvgSalaryEmployee
    --3.推进游标
    print('name salary')
    FETCH NEXT
    FROM lowAvgSalaryEmployee
    INTO @name,@salary
    --4.逐行操作
    while(@@fetch_status=0)
        begin
    		print(@name+' '+@salary)
            FETCH NEXT FROM lowAvgSalaryEmployee INTO @name,@salary
        end
    --5.关闭游标
    CLOSE lowAvgSalaryEmployee
    --6.释放游标
    DEALLOCATE lowAvgSalaryEmployee

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NTY0NTU1_size_16_color_FFFFFF_t_70 2]

##### 六、实验结论 #####

实验数据和结果见第五点，获取结果均符合预期，无差错。  
  结论：数据库中可以利用触发器实现强制业务规则和数据完整性，实现由主键和外键所不能保证的复杂的参照完整性和数据一致性。  
  利用游标可以实行对查询等结果逐行操作，而不必对整个关系表进行操作。

##### 七、实验小结 #####

本次实验主要学会了如何利用触发器对数据库中的数据进行进一步完整性约束，对触发器的理解更加深刻。同时学会了利用游标操作进行对查询等结果逐行操作，使用游标六步走、变量类型等细节处理。  
  遇到的问题主要是在使用游标输出员工的姓名与薪资时，一直显示类型转换错误；  
![在这里插入图片描述][20190804195656250.png]  
  解决方式：思考尝试后发现经过游标获取的值类型已默认转换为char，所以将获取薪资的变量的类型设为varchar即解决。

[20190804195524825.png]: /images/20211109/25c808852406409f9c4da50d465595b0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NTY0NTU1_size_16_color_FFFFFF_t_70]: /images/20211109/c36bbb16d58443798093ac4f80a4121b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NTY0NTU1_size_16_color_FFFFFF_t_70 1]: /images/20211109/32ecb98f626446e0a8daffdf300c5082.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NTY0NTU1_size_16_color_FFFFFF_t_70 2]: /images/20211109/38d25b09e6404ee2a54068c26321a790.png
[20190804195656250.png]: /images/20211109/8bc57bc51b4c4289982d7fbce528ea52.png