BEGIN declare C_SERAIL VARCHAR(50); -- 剩下的串号 declare C_PHONENUM VARCHAR(50);-- 当前号码 -- DECLARE RETURNSTR VARCHAR(8000); DECLARE V_INDEX INT; DECLARE V_SERAIL VARCHAR(5000);-- 当前串号 SELECT SUBSTRING_INDEX(I_SERAIL, '|', 1) INTO C_SERAIL; -- 第一次出现分隔符的下标 SELECT LOCATE('|', I_SERAIL) INTO V_INDEX; -- 截取余下串号字符串 SELECT SUBSTRING(I_SERAIL,V_INDEX+1) INTO V_SERAIL; IF RETURNSTR IS NULL THEN SET RETURNSTR = ''; END IF; WHILE V_INDEX > 0 DO-- 查询手机号SELECT A.PHONENUM INTO C_PHONENUM FROM T_SMS_PHONEANDSERIAL A WHERE A.SERIALNUM = C_SERAIL LIMIT 0,1;
SET RETURNSTR = CONCAT(RETURNSTR,C_SERAIL,'|',C_PHONENUM,';'); -- 当前串号 SELECT SUBSTRING_INDEX(V_SERAIL, '|', 1) INTO C_SERAIL; -- 第一次出现分隔符的下标 SELECT LOCATE('|', V_SERAIL) INTO V_INDEX; -- 截取余下串号字符串 SELECT SUBSTRING(V_SERAIL,V_INDEX+1) INTO V_SERAIL; END WHILE;
declare C_SERAIL VARCHAR(50); -- 剩下的串号
declare C_PHONENUM VARCHAR(50);-- 当前号码
-- DECLARE RETURNSTR VARCHAR(8000);
DECLARE V_INDEX INT;
DECLARE V_SERAIL VARCHAR(5000);-- 当前串号
SELECT SUBSTRING_INDEX(I_SERAIL, '|', 1) INTO C_SERAIL;
-- 第一次出现分隔符的下标
SELECT LOCATE('|', I_SERAIL) INTO V_INDEX;
-- 截取余下串号字符串
SELECT SUBSTRING(I_SERAIL,V_INDEX+1) INTO V_SERAIL; IF RETURNSTR IS NULL THEN
SET RETURNSTR = '';
END IF;
WHILE V_INDEX > 0 DO-- 查询手机号SELECT A.PHONENUM
INTO C_PHONENUM
FROM T_SMS_PHONEANDSERIAL A
WHERE A.SERIALNUM = C_SERAIL LIMIT 0,1;
SET RETURNSTR = CONCAT(RETURNSTR,C_SERAIL,'|',C_PHONENUM,';');
-- 当前串号
SELECT SUBSTRING_INDEX(V_SERAIL, '|', 1) INTO C_SERAIL;
-- 第一次出现分隔符的下标
SELECT LOCATE('|', V_SERAIL) INTO V_INDEX;
-- 截取余下串号字符串
SELECT SUBSTRING(V_SERAIL,V_INDEX+1) INTO V_SERAIL; END WHILE;
END