存储过程 ：字段拆分

-- 修改采购需求订单关联表order_id字段类型
call proc_modify_column_if_exist(
        'dfs_purchase_request_order_relation',
    'order_id',
    'ALTER TABLE `dfs_purchase_request_order_relation`
     MODIFY COLUMN `order_id`  varchar(255) NULL DEFAULT NULL COMMENT ''关联单据id''');


-- 计算分割后字符串个数函数：func_split_sum
-- 传入待分割的字符串和分割符，返回分割后的字符串个数
DELIMITER $$
DROP FUNCTION IF EXISTS `func_split_sum` $$
CREATE FUNCTION `func_split_sum`
(f_string varchar(1000),f_delimiter varchar(5)) RETURNS int(11)
DETERMINISTIC
BEGIN
    return 1+(length(f_string) - length(replace(f_string,f_delimiter,'')));
END$$
DELIMITER;


-- 字符串分割函数：func_split_string
-- 传入待拆分的字符串和分割符,返回拆分后的字符串
DELIMITER $$
DROP FUNCTION IF EXISTS `func_split_string` $$
CREATE FUNCTION `func_split_string`
(f_string varchar(1000),f_delimiter varchar(5),f_i int) RETURNS varchar(255) CHARSET utf8
DETERMINISTIC
BEGIN
        DECLARE result varchar(255) default '';
        SET result = reverse(substring_index(reverse(substring_index(f_string,f_delimiter,f_i)),f_delimiter,1));
        return result;
END$$
DELIMITER;


-- 存储过程：proc_split
-- 传入待拆分的字段、需绑定的id、分割符；构建临时表，插入拆分数据
DELIMITER $$
DROP PROCEDURE IF EXISTS `proc_split` $$
CREATE PROCEDURE `proc_split`
(IN f_string_1 varchar(1000),IN f_string_2 varchar(1000),IN rid int, IN f_delimiter varchar(5))
BEGIN
DECLARE s_sum int default 0;
DECLARE i int default 0;
SET s_sum = func_split_sum(f_string_1,f_delimiter);
DROP TABLE IF EXISTS `tmp_split`;
create temporary table `tmp_split` (`tmp_1` varchar(128) not null,`tmp_2` varchar(128) not null,`ids` int );
while i < s_sum
do
    SET i = i + 1;
    -- 待插入字段（order_id,order_num）判空
        if (length(f_string_1) > 0 ) then
    insert into tmp_split(`tmp_1`,`tmp_2`,`ids`) values (func_split_string(f_string_1,f_delimiter,i),func_split_string(f_string_2,f_delimiter,i),rid);
        end if;
end while;
-- SELECT * FROM tmp_split;
END$$
DELIMITER;

-- 存储过程：proc_tab_insert
-- 根据临时表拆分结果插入数据
DELIMITER $$
DROP PROCEDURE IF EXISTS `proc_tab_insert` $$
CREATE PROCEDURE `proc_tab_insert` ()
BEGIN
    DECLARE orderId varchar(500) default '' ;
    DECLARE orderNum varchar(500) default '' ;
    DECLARE requestId int default 0;
    DECLARE isEnd int DEFAULT 0;
    DECLARE cur1  CURSOR FOR SELECT order_id FROM dfs_purchase_request ;
    DECLARE cur2  CURSOR FOR SELECT order_num FROM dfs_purchase_request ;
    DECLARE cur3  CURSOR FOR SELECT id FROM dfs_purchase_request;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET isEnd = 1;
    OPEN cur1 ;
    OPEN cur2 ;
    OPEN cur3 ;
    FETCH cur1 INTO orderId;
    FETCH cur2 INTO orderNum;
    FETCH cur3 INTO requestId;
    -- 清空关联表
    -- TRUNCATE  dfs_purchase_request_order_relation;
    WHILE isEnd<>1 DO
        call proc_split(orderId,orderNum,requestId,",");
                INSERT INTO dfs_purchase_request_order_relation (order_id,order_num,request_id) SELECT tmp_1,tmp_2,ids from tmp_split;
        FETCH cur1 INTO orderId;
        FETCH cur2 INTO orderNum;
        FETCH cur3 INTO requestId;
    END WHILE;
    CLOSE cur1;
    CLOSE cur2;
    CLOSE cur3;
-- SELECT * FROM dfs_purchase_request_order_relation;
END$$
DELIMITER;

-- 执行拆分表存储过程
call proc_tab_insert();


-- 删除历史重复数据
delete from dfs_purchase_request_order_relation
where id not in (
select t.max_id
from (select max(id) as max_id from dfs_purchase_request_order_relation
group by request_id, order_id) as t);

-- 从order表更新order_type
update dfs_purchase_request_order_relation a, dfs_order b
set a.order_type = b.order_num_type
where a.order_id = b.order_id;

-- 执行完删除存储过程
DROP FUNCTION IF EXISTS `func_split_sum`;
DROP FUNCTION IF EXISTS `func_split_string`;
DROP PROCEDURE IF EXISTS `proc_split`;
DROP PROCEDURE IF EXISTS `proc_tab_insert`;

未经允许 禁止转载