【论坛精华帖整理】一个SQL存储过程

本文由sisijian在论坛所发，CU技术文章整理，供大家参考学习，转载请注明出处，谢谢。

 

在一个论坛上有人发个帖子问这个问题：

有一表的记录为：
 Task_ID   Employee_Name  STEPWORKTIME
   6262         张三        2
   6262         李四          2
   6262         王二         1.5
   6265         成某        2
   6265         赵某         2
   6265         钱某         1.5
……
我想求一个函数能将Task_ID字段相同记录合并为一条记录。
即想得到如下结果：
Task_ID Employees                 WorkLoad
   6262    张三、李四、王二          5.5     
   6265    成某、赵某、钱某          5.5
   ……

我写了一下，没写出UDF，只写了个存储过程。能应付他大概的要求。
可是楼主不怎么满意，想着与其丢掉，不如索性贴出来，大家批评批评^_^。

先创建一个表，名为FromTable
db2 create table FromTable(id varchar(10),name varchar(200),stepworktime int)
插入数据
db2 insert into FromTable values ('6262','张三',2)
db2 insert into FromTable values ('6262','李四',2)
db2 insert into FromTable values ('6262','王二',1.5)
db2 insert into FromTable values ('6265','成某',2)
db2 insert into FromTable values ('6265','赵某',2)
db2 insert into FromTable values ('6265','钱某',1.5)

现在再创建一个表，为ToTable
跟test1000一样的结构，用一个存储过程把你要的结果插进去
创表
db2 create  TABLE ToTable ( id  varchar(100), name varchar(100),sum int )

写存储过程


CREATE PROCEDURE ADMINISTRATOR.ProcConcatName (  )
------------------------------------------------------------------------
--SQL 存储过程
--Sisijian
--2005-01-20
------------------------------------------------------------------------

Lable1: begin

------------------------------------------------------------------------
--定义变量
--v_NumOfRecd存放对应FromTable每个id记录条数
--v_Index控制当前记录是在id相同的记录中第几条
--v_id等三个变量用于存放临时数据
--at_end控制是否到底
------------------------------------------------------------------------
   DECLARE SQLSTATE CHAR(5);
   DECLARE v_NumOfRecd int;
   DECLARE v_Index int;
   DECLARE v_Id varCHAR(100);
   DECLARE v_ConcatedName varchar(5000);
   DECLARE v_SumOfWorkTime int;
   DECLARE at_end INT DEFAULT 0;


   DECLARE not_found CONDITION FOR SQLSTATE '02000';
   DECLARE C1 CURSOR FOR
     SELECT id, count(*)
     FROM FromTable   
     GROUP BY id
     ORDER BY id;
   
   DECLARE CONTINUE HANDLER FOR not_found
     SET at_end = 1;

------------------------------------------------------------------------
--游标移动一次，就到一个新的id，id不会重复，因为经过上面的group by
------------------------------------------------------------------------
   OPEN C1;
   Concat_Loop:
   LOOP
     FETCH C1 INTO v_Id, v_NumOfRecd;
     IF at_end = 1 THEN
       LEAVE Concat_Loop;
     END IF;
------------------------------------------------------------------------
--遇到每个id ，第一条记录都应该直接插入的
------------------------------------------------------------------------
SET v_Index=1;
SET v_ConcatedName = (SELECT name  FROM 
(SELECT ROW_NUMBER() over()  as a ,FromTable.*  FROM FromTable where id = v_Id) as x where a=v_Index );
SET v_SumOfWorkTime = (SELECT STEPWORKTIME  FROM 
(SELECT ROW_NUMBER() over()  as a ,FromTable.* FROM FromTable where id = v_Id) as x where a=v_Index );
    INSERT INTO ToTable VALUES (v_Id, v_ConcatedName,v_SumOfWorkTime);
SET v_Index=2;
------------------------------------------------------------------------
--如果有第二条的话，就连接名字字符串，累加STEPWORKTIME数据
------------------------------------------------------------------------
Inner_Loop:
LOOP
IF v_Index = (v_NumOfRecd+1)  THEN
LEAVE Inner_Loop;
ELSE
SET v_ConcatedName = v_ConcatedName||','||(SELECT name  FROM 
(SELECT ROW_NUMBER() over()  as a,FromTable.* FROM FromTable where id = v_Id) as x where a=v_Index );
SET v_SumOfWorkTime = v_SumOfWorkTime+(SELECT STEPWORKTIME  FROM 
(SELECT ROW_NUMBER() over()  as a ,FromTable.* FROM FromTable where id = v_Id) as x where a=v_Index );

UPDATE ToTable SET name =  v_ConcatedName where id = v_Id;
UPDATE ToTable SET num =  v_SumOfWorkTime where id = v_Id;
SET v_Index=v_Index+1;
END IF ;

END LOOP Inner_Loop;
   END LOOP Concat_Loop;
   CLOSE C1;
END Lable1 

我的数据类型设错了，所以运行改存储过程后，查询ToTable表结果会有点点出入。
D:\>;db2 select * from totable

ID
                     NAME
                                          SUM
--------------------------------------------------------------------------------
-------------------- -----------------------------------------------------------
----------------------------------------- -----------
6262
                     张三,李四,王二
                                                    5
6265
                     赵某,钱某,成某
                                                    5

  2 条记录已选择。