Mysql-临时表的使用

DELIMITER $$

USE `cradar1`$$

DROP PROCEDURE IF EXISTS `PD_mal_rip_domain`$$

CREATE DEFINER=`root`@`%` PROCEDURE `PD_mal_rip_domain`(IN v_rip VARCHAR(20), IN begin_time VARCHAR(20))
BEGIN

DECLARE total INT;
DECLARE v_domain VARCHAR(4000);
DECLARE aa INT DEFAULT 1;

DROP TABLE IF EXISTS temp_domains;

CREATE TEMPORARY TABLE temp_domains (
temp_domain VARCHAR(255) NOT NULL ,
temp_rip VARCHAR(20) NOT NULL
);



SELECT LENGTH(domain)-LENGTH(REPLACE(domain, ',', '')) + 1 , domain INTO total , v_domain
FROM mal_rips_result
WHERE rip = v_rip
AND DATE = DATE_FORMAT(begin_time, '%Y%m%d');





TRUNCATE TABLE temp_domains;

WHILE aa<= total DO



INSERT INTO temp_domains(temp_rip,temp_domain) VALUES( v_rip,SUBSTRING_INDEX(SUBSTRING_INDEX(v_domain, ',', aa),',',-1));



SET aa=aa+1;

END WHILE;


SELECT a.temp_domain AS 域名, b.dgatype
FROM temp_domains a
INNER JOIN (SELECT domain, dgatype FROM mal_domains_raw
WHERE DATE = DATE_FORMAT(begin_time, '%Y%m%d')) b
ON a.temp_domain = b.domain ;
END$$

DELIMITER ;

转载于:https://www.cnblogs.com/20160501a/p/6970370.html