mysql into temp_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 ;