闲的无聊写了个仿MySQL replaceall() 函数

 

 

BEGIN

-- 用于控制循环是否结束
DECLARE DONE INT DEFAULT -1;

-- 变量声明
DECLARE PARAM_OUT VARCHAR(512) DEFAULT '';
DECLARE VAR_STR VARCHAR(512) DEFAULT '';
DECLARE VAR_STR_NEW VARCHAR(512) DEFAULT '';
DECLARE VAR_SPLIT_STR VARCHAR(512) DEFAULT '';
DECLARE VAR_SPLIT_SYMBOL VARCHAR(512) DEFAULT '';

-- 记录上次字符串中某符号出现的位置
DECLARE VAR_LOG_SYMBOL_INDEX INTEGER(12) DEFAULT 0;

-- 记录字符串截取开始、结束和字符出现的位置
DECLARE VAR_STR_INDEX_START INTEGER(12) DEFAULT 0;
DECLARE VAR_STR_INDEX_END INTEGER(12) DEFAULT 0;
DECLARE VAR_STR_SYMBOL_INDEX INTEGER(12) DEFAULT 0;

-- 记录每次更新后字符串截取开始、结束和字符出现的位置
DECLARE VAR_STR_NEW_INDEX_START INTEGER(12) DEFAULT 0;
DECLARE VAR_STR_NEW_INDEX_END INTEGER(12) DEFAULT 0;
DECLARE VAR_STR_NEW_SYMBOL_INDEX INTEGER(12) DEFAULT 0;

-- 设置字符出现在字符串中的首次位置
SET VAR_STR_SYMBOL_INDEX = INSTR(SPLIT_STR, SPLIT_SYMBOL);

-- 字符串存在该符号 则遍历数据
IF VAR_STR_SYMBOL_INDEX <> 0 THEN

-- 首次字符串截取位置初始化
SET PARAM_OUT = '';
SET VAR_STR_INDEX_START = 1;
SET VAR_STR_INDEX_END = VAR_STR_SYMBOL_INDEX - 1;
SET VAR_LOG_SYMBOL_INDEX = VAR_STR_SYMBOL_INDEX;

-- 循环操作
MYLOOP: LOOP

-- 循环结束标志
IF DONE = 1 THEN LEAVE MYLOOP; END IF;

-- SELECT VAR_STR_NEW, VAR_STR_NEW_SYMBOL_INDEX, VAR_STR_INDEX_START, VAR_STR_INDEX_END, VAR_LOG_SYMBOL_INDEX FROM DUAL;
-- 每次截取的字符串
SELECT SUBSTR(SPLIT_STR, VAR_STR_INDEX_START, VAR_STR_INDEX_END) FROM DUAL INTO VAR_STR;

-- 每次截取字符串后产生新的字符串
SET VAR_STR_NEW = SUBSTR(SPLIT_STR, VAR_LOG_SYMBOL_INDEX + 1);
SET VAR_STR_NEW_SYMBOL_INDEX = INSTR(VAR_STR_NEW, SPLIT_SYMBOL);

-- 拼接每次截取出来的字符串
IF LENGTH(PARAM_OUT) <> 0 THEN
SET PARAM_OUT = CONCAT(PARAM_OUT, REPLACED_SYMBOL, VAR_STR);
END IF;

-- 拼接每次截取出来的字符串
IF LENGTH(PARAM_OUT) = 0 THEN
SET PARAM_OUT = CONCAT(PARAM_OUT, VAR_STR);
END IF;

-- 下一次字符串截取位置的初始化
IF VAR_STR_NEW_SYMBOL_INDEX <> 0 THEN
SET VAR_STR_INDEX_START = VAR_LOG_SYMBOL_INDEX + 1;
SET VAR_LOG_SYMBOL_INDEX = VAR_LOG_SYMBOL_INDEX + VAR_STR_NEW_SYMBOL_INDEX;
SET VAR_STR_INDEX_END = VAR_STR_NEW_SYMBOL_INDEX - 1;
END IF;

-- 最后一次字符串不截取直接拼接并设置离开循环标志
IF VAR_STR_NEW_SYMBOL_INDEX = 0 AND LENGTH(VAR_STR_NEW) > 0 THEN
SET PARAM_OUT = CONCAT(PARAM_OUT, REPLACED_SYMBOL, VAR_STR_NEW);
-- SELECT VAR_STR_NEW, VAR_STR_NEW_SYMBOL_INDEX, VAR_STR_INDEX_START, VAR_STR_INDEX_END, VAR_LOG_SYMBOL_INDEX FROM DUAL;
SET DONE = 1;
END IF;

-- 设置离开循环标志
IF VAR_STR_NEW_SYMBOL_INDEX = 0 AND LENGTH(VAR_STR_NEW) < 1 THEN
SET DONE = 1;
END IF;

-- 关闭循环
END LOOP MYLOOP;

END IF;
RETURN PARAM_OUT;
END

转载于:https://www.cnblogs.com/Dream-Lasting/p/8991930.html