功能：对overnight_abcd根据column01字段的数据进行表切分，且分为overnight_2019、overnight_2018、overnight_2017、overnight_2016、overnight_2015表，同时替换overnight_abcd表中column03字段对应的行政区划代码为对应的中文名，接着，根据column01数据的值，将数据对应存入以'overnight_'开头+数据值的表。
用到的代码：存储过程创建和删除、游标循环、Insert into Table2(field1,field2,...) select value1,value2,... from Table1批量查询插入语句（去重复判断）、字符串的分割、UPDATE table SET field = replace(field,'被替换','替换成') 批量替换、create table动态建表、CONCAT字符串拼接。
表名和表数据：
1、overnight_abcd。
2、original_et
3、original_etfield
4、original_fielddef
5、rule_valuereplacedetail
代码部分：
delimiter //
drop procedure if exists cutTable //
create procedure cutTable(in oldTable varchar(32), in newTable varchar(32), in cutKey varchar(16), in cutValue varchar(50), in colList varchar(200), in repeatSQL varchar(1000))
comment '执行表切分'
begin
set @cols = substr(colList,1,length(colList)-1);
/* 判断重复 */
/*
INSERT INTO overnight_2019 (
column01,
column02,
column03,
column04,
column05
) SELECT
column01,
column02,
column03,
column04,
column05
FROM
overnight_abcd
WHERE
NOT EXISTS (
SELECT
1
FROM
overnight_2019
WHERE
overnight_abcd.column01 = overnight_2019.column01
AND overnight_abcd.column02 = overnight_2019.column02
AND overnight_abcd.column03 = overnight_2019.column03
AND overnight_abcd.column04 = overnight_2019.column04
AND overnight_abcd.column05 = overnight_2019.column05
)
AND column01 = '2019';
*/
set @repeatSQL = CONCAT(' not exists ( select 1 from ', newTable, ' where ', @repeatSQL, ' ) and ');
set @insertSQL = CONCAT('Insert into ', newTable, ' ( ', @cols , ' ) ', 'select ' , @cols, ' from ', oldTable, ' where ', @repeatSQL, cutKey, " = '", cutValue, "';");
/* 预处理动态sql语句 */
PREPARE stmt FROM @insertSQL;
/* 执行sql语句 */
EXECUTE stmt;
/* 释放prepare */
deallocate prepare stmt;
end
//
drop procedure if exists getRule_ValueReplaceDetail //
create procedure getRule_ValueReplaceDetail(in tName varchar(32), in colName varchar(16), in repID varchar(16))
comment '获取替换规则明细表'
begin
DECLARE done INT DEFAULT 0;
/* 序号 */
DECLARE repNum varchar(16);
/* 原值 */
DECLARE fValue varchar(18) DEFAULT '';
/* 替换值 */
DECLARE lValue varchar(18) DEFAULT '';
/* 更新语句 */
DECLARE colSQL varchar(500) DEFAULT '';
/* 查找替换规则明细表 */
DECLARE curRVRD CURSOR FOR SELECT Rep_Num,FirstValue,LastValue FROM Rule_ValueReplaceDetail where Rep_ID = repID;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN curRVRD;
REPEAT
FETCH curRVRD INTO repNum,fValue,lValue;
IF NOT done THEN
/* 字段替换sql */
set colSQL = CONCAT('update ',tName,' set ', colName, ' = replace(', colName, ',', "'", fValue, "',", "'", lValue, "' ); " );
/* 预处理动态sql语句 */
set @mySQL = colSQL;
PREPARE stmt FROM @mySQL;
/* 执行sql语句 */
EXECUTE stmt;
/* 释放prepare */
deallocate prepare stmt;
END IF;
UNTIL done END REPEAT;
CLOSE curRVRD;
end
//
drop procedure if exists getOriginal_ETField //
create procedure getOriginal_ETField(in tId varchar(16), in tName varchar(32), in colName varchar(16))
comment '获取清洗字段信息表'
begin
DECLARE done INT DEFAULT 0;
/* 是否主键 */
DECLARE isPK varchar(4);
/* 是否表切分字段 */
DECLARE isCutField varchar(4) DEFAULT '';
/* 切分规则 */
DECLARE cutRule varchar(40);
/* 是否进行值替换 */
DECLARE isReplace varchar(4);
/* 替换规则ID */
DECLARE repID varchar(16);
/* 查找清洗字段信息表 */
DECLARE curOETF CURSOR FOR SELECT Is_PK,Is_CutField,Cut_Rule,Is_Replace,Rep_ID FROM Original_ETField where Task_ID = tId and Field_Name = colName;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN curOETF;
REPEAT
FETCH curOETF INTO isPK, isCutField, cutRule, isReplace, repID;
IF NOT done THEN
if isReplace = 'IR01' then
call getRule_ValueReplaceDetail(tName, colName, repID);
end if;
END IF;
UNTIL done END REPEAT;
CLOSE curOETF;
end
//
drop procedure if exists data_clean //
create procedure data_clean(in tId varchar(16), in tName varchar(32))
comment '表和表数据清洗'
begin
DECLARE done INT DEFAULT 0;
/* 表名前缀 */
DECLARE tblPre varchar(50);
/* 是否有切分 */
DECLARE isCut varchar(4) DEFAULT 'CF02';
/* 切分规则 */
DECLARE ruleCut varchar(60);
/* 表切分语句 */
DECLARE createSql varchar(500) DEFAULT '';
/* 字段名称 */
DECLARE fieldName varchar(16);
/* 字段长度 */
DECLARE fieldLength varchar(6);
/* 字段类型 */
DECLARE fieldType varchar(12);
/* 字段含义 */
DECLARE fieldMemo varchar(60);
/* 切分规则取出 */
DECLARE myCuleCut varchar(60);
/* 切分规则key */
DECLARE cutKey varchar(10);
/* 切分规则value */
DECLARE cutValue varchar(50);
/* 查找字段定义表 */
DECLARE curOFD CURSOR FOR SELECT Field_Name,Field_Lenth,Field_Type,Field_Memo FROM Original_FieldDef where Task_ID = tId;
/* 查找元数据抽取清洗表 */
DECLARE curET CURSOR FOR SELECT Tbl_Pre,Is_Cut,Rule_Cut FROM Original_ET WHERE Task_ID = tId;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN curET;
REPEAT
FETCH curET INTO tblPre, isCut, ruleCut;
IF NOT done THEN
IF isCut = 'CF01' THEN
set myCuleCut = ruleCut;
ELSE
set myCuleCut = '';
END IF;
END IF;
UNTIL done END REPEAT;
CLOSE curET;
set done = 0;
/* 字段值替换 */
set @changeCols = '';
OPEN curOFD;
REPEAT
FETCH curOFD INTO fieldName, fieldLength, fieldType, fieldMemo;
IF NOT done THEN
/* 字段名列表获取 */
set @changeCols = CONCAT(@changeCols,fieldName,',');
END IF;
UNTIL done END REPEAT;
CLOSE curOFD;
/* 值替换处理 */
/* 算出分隔符的总数 */
set @cols = length(@changeCols) - length(replace(@changeCols,',',''));
set @left_cols = @changeCols;
while @cols > 0 do
/* 得到分隔符','前面的字符串 */
set @sub_col = substr(@left_cols,1,instr(@left_cols,',')-1);
/* 得到分隔符','后面的字符串 */
set @left_cols = substr(@left_cols,length(@sub_col)+length(',')+1);
set @my_col = trim(@sub_col);
call getOriginal_ETField(tId, tName, @my_col);
set @cols = @cols - 1;
end while;
IF myCuleCut = '' THEN
select isCut;
ELSE
/* 获取表切分规则key，截取第一个':'之前的所有字符 */
set cutKey = SUBSTRING_INDEX(myCuleCut, ':', 1);
/* 获取表切分规则value，截取倒数第一个':'之后的所有字符 */
set cutValue = SUBSTRING_INDEX(myCuleCut, ':', -1);
/* 算出分隔符的总数 */
set @i = length(cutValue) - length(replace(cutValue,',',''));
set @left_cutValue = cutValue;
while @i > 0 do
/* 得到分隔符','前面的字符串 */
set @sub_str = substr(@left_cutValue,1,instr(@left_cutValue,',')-1);
/* 得到分隔符','后面的字符串 */
set @left_cutValue = substr(@left_cutValue,length(@sub_str)+length(',')+1);
set @n = trim(@sub_str);
/* 新的表名 */
set @newTable = CONCAT(tblPre, @n);
/* 去重复sql */
set @repeatSQL = '';
SET createSql = CONCAT('create table if not exists ',tblPre, @n, '(');
set done = 0;
OPEN curOFD;
REPEAT
FETCH curOFD INTO fieldName, fieldLength, fieldType, fieldMemo;
IF NOT done THEN
SET createSql = CONCAT(createSql,fieldName, ' ', fieldType,'(',fieldLength,')', ' ', 'COMMENT', ' ', "'", fieldMemo, "'", ',');
/* 去重复sql拼接 */
/*
AND overnight_abcd.column01 = overnight_2019.column01
AND overnight_abcd.column02 = overnight_2019.column02
AND overnight_abcd.column03 = overnight_2019.column03
AND overnight_abcd.column04 = overnight_2019.column04
AND overnight_abcd.column05 = overnight_2019.column05
*/
set @repeatSQL = CONCAT(@repeatSQL, ' and ', tName, '.', fieldName, ' = ', @newTable, '.', fieldName);
END IF;
UNTIL done END REPEAT;
set createSql = left(createSql,char_length(createSql)-1);
set createSql = CONCAT(createSql,')ENGINE=InnoDB DEFAULT CHARSET=utf8;');
CLOSE curOFD;
set @sql = createSql;
/*select @sql;*/
/* 预处理动态sql语句 */
PREPARE stmt FROM @sql;
/* 执行sql语句 */
EXECUTE stmt;
/* 释放prepare */
deallocate prepare stmt;
/* 删除首个 and */
set @repeatSQL = substr(@repeatSQL,5);
call cutTable(tName, @newTable, cutKey, @n, @changeCols, @repeatSQL);
set createSql = '';
set @i = @i - 1;
end while;
END IF;
end
//
delimiter;
/* 调用这个存储过程 */
call data_clean('OT_1001', 'overnight_abcd');------------------------------------------------------END--------------------------------------------------
版权声明：本文为kuaikuai_945原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接和本声明。