Архивы if

Вложеный запрос в процедуре MySQL, курсоры и прочие прелести

06.06.2018 Павел Грибов MySQL

Оставляю код процедуры для «истории».

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

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

Оставить комментарий cursor, if, mysql, sql

Запрос с условием IF в MySQL..

03.12.2014 Павел Грибов MySQL

Синтаксис:

IF(выражение, значение если true, значение если false)

Например:

select if (now()>timefrom and now()<=timeto,1,0) as act,if (now()<=timefrom,1,0) as futo,categories.descr,categories.above,usbox_services.mul,usbox_services.person_id,usbox_services.comment,usbox_services.created,usbox_services.timefrom,usbox_services.timeto from usbox_services inner join categories on (categories.tar_id=usbox_services.tar_id and categories.cat_idx=usbox_services.cat_idx) where usbox_services.vg_id=6861 order by usbox_services.timeto desc;

select if (now()>timefrom and now()<=timeto,1,0) as act,if (now()<=timefrom,1,0) as futo,categories.descr,categories.above,usbox_services.mul,usbox_services.person_id,usbox_services.comment,usbox_services.created,usbox_services.timefrom,usbox_services.timeto from usbox_services inner join categories on (categories.tar_id=usbox_services.tar_id and categories.cat_idx=usbox_services.cat_idx) where usbox_services.vg_id=6861 order by usbox_services.timeto desc;

Возвратит act=1 если текущий момент времени между timefrom и timeto, а так-же futu=1 если timefrom>=now()

Оставить комментарий if, mysql, условие

Ноябрь 2024
Пн	Вт	Ср	Чт	Пт	Сб	Вс
				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	28	29	30