WEB开发网
开发学院数据库MSSQL Server Stored Procedure(存储过程)编写经验和优化措施... 阅读

Stored Procedure(存储过程)编写经验和优化措施

 2007-11-11 08:01:05 来源:WEB开发网   
核心提示: 在一个论坛上有人发个帖子问这个问题:有一表的记录为: Task_ID Employee_NameSTEPWORKTIME 6262 张三 2 6262 李四2 6262 王二1.5 6265 成某 2 6265 赵某 2 6265 钱某 1.5……我想求一个函数能将Task_ID字段相同记录合并为一条记录,即想得到如

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

有一表的记录为:
 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 条记录已选择。

写得不好,请大家多体谅下呵:)

上一页  1 2 3 4 5 6 7  下一页

Tags:Stored Procedure 存储

编辑录入:coldstar [复制链接] [打 印]
赞助商链接