现在表的记录是:
![](http://imgsrc.baidu.com/forum/w%3D580/sign=60f45b8ee81190ef01fb92d7fe1a9df7/328b644a20a44623ec362e019722720e0df3d77b.jpg)
要把每行记录按“,”分割处理,重新形成不重复的代码-名称表:
![](http://imgsrc.baidu.com/forum/w%3D580/sign=031c59429fef76c6d0d2fb23ad17fdf6/10e30ba4462309f7769326717d0e0cf3d6cad67b.jpg)
存储过程实现如下:
DROP PROCEDURE IF EXISTS p_zd;
CREATE PROCEDURE p_zd()
BEGIN
DECLARE tmp_dm TEXT ;
DECLARE tmp_mc TEXT ;
DECLARE insert_str LONGTEXT DEFAULT '';
DECLARE tmp_count INT DEFAULT 0;
DECLARE cur_account_finished INTEGER DEFAULT 0;-- 定义游标遍历数据结束的标志 1 遍历结束 ;0 没有结束
DECLARE cur_account CURSOR FOR
SELECT
dm,
mc
FROM
源表
WHERE dm IS NOT NULL
GROUP BY
dm,
mc
-- 将结束标志绑定到游标
DECLARE CONTINUE HANDLER FOR NOT FOUND SET cur_account_finished = 1;
-- 打开游标
OPEN cur_account;
-- 遍历游标,取出数据,写入临时表
read_loop:LOOP
-- 进行遍历 字段赋值
FETCH NEXT FROM cur_account
INTO tmp_dm,tmp_mc;
-- 判断是否结束
IF cur_account_finished = 1 THEN
LEAVE read_loop;
END IF;
SET tmp_count=LENGTH(tmp_dm)-LENGTH(REPLACE(tmp_dm,',',''));
IF LOCATE(',',tmp_dm) = 0 THEN
DELETE FROM t_zd where dm=tmp_dm;
INSERT INTO t_zd(dm,mc) values(tmp_dm,tmp_mc);
ELSE
SET @c=0;
WHILE @c <= tmp_count DO
SET @c=@c+1;
SET @SUB_DM=SUBSTRING_INDEX(SUBSTRING_INDEX(tmp_dm,',',@c),',',-1);
SET @SUB_MC=SUBSTRING_INDEX(SUBSTRING_INDEX(tmp_mc,',',@c),',',-1);
IF LOCATE(@SUB_DM,insert_str)=0 THEN
SET insert_str=CONCAT(insert_str,'(\'',@SUB_DM,'\',\'',@SUB_MC,'\'),');
END IF;
END WHILE;
-- 先批量删除
SET @delsql=CONCAT('DELETE FROM t_zd where dm in (\'',REPLACE(tmp_dm,',','\',\''),'\')');
PREPARE delete_dm_stmt FROM @delsql;
EXECUTE delete_dm_stmt;
-- 再批量提交
SET @insertsql=CONCAT('INSERT INTO t_zd(dm,mc) VALUES',insert_str,'(\'a\',\'a\')');
PREPARE insert_dm_stmt FROM @insertsql;
EXECUTE insert_dm_stmt;
DELETE FROM t_zd where dm='a';
-- 重置字符串
SET insert_str='';
END IF;
-- 结束循环
END LOOP;
-- 关闭游标
CLOSE cur_account;
END
![](http://imgsrc.baidu.com/forum/w%3D580/sign=60f45b8ee81190ef01fb92d7fe1a9df7/328b644a20a44623ec362e019722720e0df3d77b.jpg)
要把每行记录按“,”分割处理,重新形成不重复的代码-名称表:
![](http://imgsrc.baidu.com/forum/w%3D580/sign=031c59429fef76c6d0d2fb23ad17fdf6/10e30ba4462309f7769326717d0e0cf3d6cad67b.jpg)
存储过程实现如下:
DROP PROCEDURE IF EXISTS p_zd;
CREATE PROCEDURE p_zd()
BEGIN
DECLARE tmp_dm TEXT ;
DECLARE tmp_mc TEXT ;
DECLARE insert_str LONGTEXT DEFAULT '';
DECLARE tmp_count INT DEFAULT 0;
DECLARE cur_account_finished INTEGER DEFAULT 0;-- 定义游标遍历数据结束的标志 1 遍历结束 ;0 没有结束
DECLARE cur_account CURSOR FOR
SELECT
dm,
mc
FROM
源表
WHERE dm IS NOT NULL
GROUP BY
dm,
mc
-- 将结束标志绑定到游标
DECLARE CONTINUE HANDLER FOR NOT FOUND SET cur_account_finished = 1;
-- 打开游标
OPEN cur_account;
-- 遍历游标,取出数据,写入临时表
read_loop:LOOP
-- 进行遍历 字段赋值
FETCH NEXT FROM cur_account
INTO tmp_dm,tmp_mc;
-- 判断是否结束
IF cur_account_finished = 1 THEN
LEAVE read_loop;
END IF;
SET tmp_count=LENGTH(tmp_dm)-LENGTH(REPLACE(tmp_dm,',',''));
IF LOCATE(',',tmp_dm) = 0 THEN
DELETE FROM t_zd where dm=tmp_dm;
INSERT INTO t_zd(dm,mc) values(tmp_dm,tmp_mc);
ELSE
SET @c=0;
WHILE @c <= tmp_count DO
SET @c=@c+1;
SET @SUB_DM=SUBSTRING_INDEX(SUBSTRING_INDEX(tmp_dm,',',@c),',',-1);
SET @SUB_MC=SUBSTRING_INDEX(SUBSTRING_INDEX(tmp_mc,',',@c),',',-1);
IF LOCATE(@SUB_DM,insert_str)=0 THEN
SET insert_str=CONCAT(insert_str,'(\'',@SUB_DM,'\',\'',@SUB_MC,'\'),');
END IF;
END WHILE;
-- 先批量删除
SET @delsql=CONCAT('DELETE FROM t_zd where dm in (\'',REPLACE(tmp_dm,',','\',\''),'\')');
PREPARE delete_dm_stmt FROM @delsql;
EXECUTE delete_dm_stmt;
-- 再批量提交
SET @insertsql=CONCAT('INSERT INTO t_zd(dm,mc) VALUES',insert_str,'(\'a\',\'a\')');
PREPARE insert_dm_stmt FROM @insertsql;
EXECUTE insert_dm_stmt;
DELETE FROM t_zd where dm='a';
-- 重置字符串
SET insert_str='';
END IF;
-- 结束循环
END LOOP;
-- 关闭游标
CLOSE cur_account;
END
![](http://imgsrc.baidu.com/forum/pic/item/96be543e6709c93dc04743bf923df8dcd000546e.jpg?v=tbs)