526互联

sql server 练习：存储过程+临时表+游标

发布时间 2023-07-15 16:12:20作者: 孤幽影暗

1、题目

要求编写一个存储过程 spGetTest 统计 T_Test 表的数据，执行输出结果如下图:

2、数据

if not object_id('T_Test') is null  --判断表是否存在
   drop table T_Test  --删除表
go 
--创建用户金额发生表 
create table T_Test(
   fUser_Name nvarchar(40), --用户名称 
   fUser_Dep  nvarchar(40), --用户部门
   fMoney     numeric(24,6), --用户发生金额
   fDate      datetime       --金额发生时间
)


go 
--插入测试数据
insert T_Test(fUser_Name,fUser_Dep,fMoney,fDate)
values('小王','营业部',120.3,'2023/06/21 11:00:00')
insert T_Test(fUser_Name,fUser_Dep,fMoney,fDate)
values('小王','营业部',450,'2023/06/21 12:00:00')
insert T_Test(fUser_Name,fUser_Dep,fMoney,fDate)
values('小王','营业部',100,'2023/06/21 14:00:00')
insert T_Test(fUser_Name,fUser_Dep,fMoney,fDate)
values('小李','营业部',110.34,'2023/06/21 10:00:00')
insert T_Test(fUser_Name,fUser_Dep,fMoney,fDate)
values('小李','营业部',120,'2023/06/21 11:00:00')
go
insert T_Test(fUser_Name,fUser_Dep,fMoney,fDate)
values('老刘','生产部',300,'2023/06/21 19:00:00')
insert T_Test(fUser_Name,fUser_Dep,fMoney,fDate)
values('老刘','生产部',150,'2023/06/21 14:00:00')
insert T_Test(fUser_Name,fUser_Dep,fMoney,fDate)
values('郭总','生产部',220,'2023/06/21 11:00:00')
insert T_Test(fUser_Name,fUser_Dep,fMoney,fDate)
values('郭总','生产部',340,'2023/06/21 18:00:00')
insert T_Test(fUser_Name,fUser_Dep,fMoney,fDate)
values('郭总','生产部',60,'2023/06/21 16:00:00')
insert T_Test(fUser_Name,fUser_Dep,fMoney,fDate)
values('郭总','生产部',508,'2023/06/21 13:00:00')
go
insert T_Test(fUser_Name,fUser_Dep,fMoney,fDate)
values('孙总','储运部',400.06,'2023/06/21 14:00:00')
insert T_Test(fUser_Name,fUser_Dep,fMoney,fDate)
values('孙总','储运部',220,'2023/06/21 13:00:00')
insert T_Test(fUser_Name,fUser_Dep,fMoney,fDate)
values('孙总','储运部',520,'2023/06/21 15:00:00')
insert T_Test(fUser_Name,fUser_Dep,fMoney,fDate)
values('小黄','储运部',720,'2023/06/21 16:00:00')
insert T_Test(fUser_Name,fUser_Dep,fMoney,fDate)
values('小黄','储运部',150,'2023/06/21 18:00:00')
insert T_Test(fUser_Name,fUser_Dep,fMoney,fDate)
values('大彭','储运部',190.2,'2023/06/21 12:00:00')
insert T_Test(fUser_Name,fUser_Dep,fMoney,fDate)
values('大彭','储运部',170,'2023/06/21 16:00:00')
insert T_Test(fUser_Name,fUser_Dep,fMoney,fDate)
values('大彭','储运部',180,'2023/06/21 18:00:00')
insert T_Test(fUser_Name,fUser_Dep,fMoney,fDate)
values('大彭','储运部',409,'2023/06/21 12:00:00')
insert T_Test(fUser_Name,fUser_Dep,fMoney,fDate)
values('二胖','储运部',360,'2023/06/21 17:00:00')

3、实现

IF EXISTS (SELECT * FROM sys.objects WHERE name='proc_ybTest')
    DROP PROCEDURE proc_ybTest
GO

CREATE PROCEDURE proc_ybTest
AS
BEGIN
    DECLARE @depname NVARCHAR(20);
    DECLARE @results TABLE (部门 NVARCHAR(20), fUser_Name NVARCHAR(50), 金额 DECIMAL(18, 2), 最近消费时间 DATETIME);

    DECLARE dep_name CURSOR FOR SELECT fUser_Dep FROM T_Test GROUP BY fUser_Dep;
    OPEN dep_name;

    FETCH NEXT FROM dep_name INTO @depname;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        INSERT INTO @results 
            SELECT
                CASE WHEN fUser_Dep=@depname THEN '' END AS 部门, 
                fUser_Name, SUM(fMoney) AS 金额, MAX(fDate) AS 最近消费时间
            FROM T_Test
            WHERE fUser_Dep = @depname
            GROUP BY fUser_Dep, fUser_Name
            UNION ALL
            SELECT @depname+'_合计', '', SUM(fMoney), null
            FROM T_Test
            WHERE fUser_Dep = @depname;

        FETCH NEXT FROM dep_name INTO @depname;
    END;

    CLOSE dep_name;
    DEALLOCATE dep_name;

    SELECT CASE 
        WHEN 部门 NOT LIKE '%合计%' THEN ''
        ELSE 部门
        END AS 部门,
    fUser_Name,金额,最近消费时间 FROM @results;
END

exec proc_ybTest

执行存储过程结果如下：

4、解释

4.1AI文字解释

以下是对存储过程进行逐行解释：

  1. IF EXISTS (SELECT * FROM sys.objects WHERE name='proc_ybTest'): 这行代码用于检查是否存在名为 proc_ybTest 的存储过程。

  2. DROP PROCEDURE proc_ybTest: 如果存在名为 proc_ybTest 的存储过程，则使用 DROP PROCEDURE 语句将其删除。

  3. GO: GO 是一个批处理分隔符，它将上述两行代码分隔为两个独立的批处理。

  4. CREATE PROCEDURE proc_ybTest: 创建名为 proc_ybTest 的存储过程。

  5. AS: AS 关键字指示存储过程的主体开始。

  6. DECLARE @depname NVARCHAR(20);: 声明一个名为 @depname 的变量，用于存储部门名称。

  7. DECLARE @results TABLE (部门 NVARCHAR(20), fUser_Name NVARCHAR(50), 金额 DECIMAL(18, 2), 最近消费时间 DATETIME);: 声明一个名为 @results 的表变量，用于存储结果集。

  8. DECLARE dep_name CURSOR FOR SELECT fUser_Dep FROM T_Test GROUP BY fUser_Dep;: 声明名为 dep_name 的游标，用于循环遍历部门名称。

  9. OPEN dep_name;: 打开游标，准备开始遍历。

  10. FETCH NEXT FROM dep_name INTO @depname;: 从游标中获取第一条部门名称，并将其存储在 @depname 变量中。

  11. WHILE @@FETCH_STATUS = 0: 进入一个循环，只要游标还有更多的数据可获取，就继续循环。

  12. BEGIN: 循环体的开始。

  13. INSERT INTO @results: 将以下查询结果插入到 @results 表变量中。

    • 部门行：根据当前部门名称（@depname）查询相应的部门数据，包括部门、用户名、金额和最近消费时间。
    • 合计行：根据当前部门名称（@depname）查询相应的金额合计，并将部门名称拼接上 "_合计" 后作为部门。

  14. FETCH NEXT FROM dep_name INTO @depname;: 从游标中获取下一条部门名称，并将其存储在 @depname 变量中。

  15. END: 循环体的结束。

  16. CLOSE dep_name;: 关闭游标。

  17. DEALLOCATE dep_name;: 释放游标的资源。

  18. SELECT CASE WHEN 部门 NOT LIKE '%合计%' THEN '' ELSE 部门 END AS 部门, fUser_Name, 金额, 最近消费时间 FROM @results;: 从 @results 表变量中选择部门、用户名、金额和最近消费时间，并使用 CASE 语句处理部门名称，使合计行的部门名称为空字符串。