完成存储过程的创建、调用;完成触发器的创建与维护。根据下面资料完成5.2.3和5.3.3中的实验内容。
没有图7,应该写7结果写成8了,不影响阅读。
实验过程
5.2.3 实验
(1)根据订单明细表中的数据,利用游标修改OrderMaster 表中orderSum的值。遍历 OrderDetail 表中的数据,计算每个订单号(orderNo)的总金额(totalAmount),然后将这个总金额更新到 OrderMaster 表中对应的订单上。
题目要求我们使用游标(Cursor)来遍历OrderDetail表,计算每个订单号(orderNo)的总金额(totalAmount),并将这个总金额更新到OrderMaster表中对应的订单上。
解题思路:
声明游标:声明一个名为orderCursor的游标,用于从OrderDetail表中选取每个订单号的总金额。使用GROUP BY对订单号进行分组,并用SUM(quantity * price)计算总金额。
定义变量:定义两个变量@OrderNo和@TotalAmount,分别用来存储从游标中获取的订单号和总金额。
打开游标:执行OPEN语句打开游标,准备开始遍历。
遍历游标:使用FETCH NEXT FROM语句从游标中逐行获取数据,并将获取的数据存储到定义的变量中。
更新数据:在WHILE循环中,检查@@FETCH_STATUS的值,如果为0,表示成功获取了数据。然后使用UPDATE语句更新OrderMaster表中对应订单号的orderSum字段。
继续获取:在循环体内,继续使用FETCH NEXT FROM获取下一行数据。
结束循环:当游标中没有更多行时,@@FETCH_STATUS将不为0,此时结束循环。
关闭和释放游标:在循环结束后,使用CLOSE和DEALLOCATE语句关闭并释放游标资源。
图1 利用游标修改OrderMaster 表中orderSum的值
图2 OrderMaster 表运行结果
图3 OrderDetail表
(2)创建存储过程,要求:按第2章员工表定义中的CHECK约束自动产生员工编
号。该过程的输入参数为员工入职的年份,输出参数是自动生成的员工编号,该编号满足第2章员工表定义中的CHECK约束,且后3位流水号等于表中与人职年份相同的员工编号最大值加1,例如输入参数为2020,且员工表中该年度最大的编码是E2020005,则自动产生的编号为E20200006;如果该入职年份没有其他员工,则流水号为001。
解题思路:
定义存储过程:创建名为GenerateEmployeeNo的存储过程,接受一个输入参数@HireYear(员工入职年份),并有一个输出参数@EmployeeNo(自动生成的员工编号)。
格式化年份:使用FORMAT函数确保年份是四位数字格式。
查找最大流水号:通过SELECT语句和MAX函数查找给定年份中员工编号的最大流水号。
处理没有员工的情况:如果给定年份没有员工入职,即@MaxSerialNumber为NULL,则设置@MaxSerialNumber为0。
计算新的流水号:将找到的最大流水号加1,以生成新的流水号。
格式化员工编号:构造新的员工编号,格式为'E'后跟四位年份和三位流水号。
返回员工编号:通过SELECT语句返回生成的员工编号。
调用存储过程:使用EXEC语句调用存储过程,并使用变量@NewEmployeeNo接收输出的员工编号。