MySQL procedure getASR example

[Emilio]

Код: Выделить всё

CREATE DEFINER=`emil`@`%` PROCEDURE `getASR`(IN `iDate1` DATETIME, IN `iDate2` DATETIME, OUT `oASR` FLOAT)
   LANGUAGE SQL
   NOT DETERMINISTIC
   CONTAINS SQL
   SQL SECURITY DEFINER
   COMMENT ''
BEGIN
   DECLARE answered, total int;
   SELECT COUNT(*) INTO total FROM cdr where start_stamp BETWEEN iDate1 AND iDate2;
   SELECT COUNT(*) INTO answered FROM cdr where hangup_cause='NORMAL_CLEARING' AND start_stamp BETWEEN iDate1 AND iDate2;
   SET oASR = answered/total;
END

call getASR('2016-05-12 09:00:00','2016-05-12 10:00:00',@A);

mysql> select @A;
+---------------------+
| @A                  |
+---------------------+
| 0.10428015142679214 |
+---------------------+
1 row in set (0.00 sec)