Вложеный запрос в процедуре MySQL, курсоры и прочие прелести
Оставляю код процедуры для «истории».
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
CREATE PROCEDURE `GetInfo`(IN `mac_sw` VARCHAR(20), IN `port_sw` VARCHAR(20)) NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER BEGIN DECLARE value2 varchar(200); DECLARE type2 int(11); DECLARE code2 int(11); DECLARE cnt INT(10); DECLARE DONE integer default 0; DECLARE ip_cur CURSOR FOR SELECT code, type, value FROM dhcp_subnets where segment_id in (SELECT segment_id FROM dhcp_clients_by_relay WHERE upper(relay_id) = upper(mac_sw) AND upper(relay_port) = upper(port_sw)) UNION SELECT code, type, value FROM dhcp_clients_by_relay WHERE upper(relay_id) = upper(mac_sw) AND upper(relay_port) = upper(port_sw) ORDER BY CODE; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1; OPEN ip_cur; SET cnt=0; REPEAT FETCH ip_cur INTO code2,type2,value2; IF NOT DONE THEN SELECT code2 as code,type2 as type,value2 as value; END IF; SET cnt=cnt+1; UNTIL DONE END REPEAT; CLOSE ip_cur; IF cnt=1 THEN SELECT 1 as code,5 as type,"255.255.224.0" as value; SELECT 3 as code,5 as type,"172.30.128.1" as value; SELECT 6 as code,5 as type,"11.163.82.1,8.8.8.8" as value; SELECT 15 as code,4 as type,"цывацу.ru" as value; SELECT 51 as code,2 as type,"86400" as value; SELECT 1009 as code,5 as type,CONCAT("172.30.",ROUND((RAND() * (254 - 0)) + 0),".",ROUND((RAND() * (254 - 0)) + 0)) as value; END IF; END |