Yub5Fm3T

1//
2CREATE PROCEDURE goods_info() -- Курсоры 2
3BEGIN
4	DECLARE done int(11) DEFAULT 0;
5	DECLARE gt VARCHAR(255);
6	DECLARE gs int(11);
7	DECLARE da VARCHAR(255);
8
9
10	DECLARE cur1 CURSOR FOR SELECT g.`title`, SUM(og.count_goods), CONCAT(YEAR(o.`close_date`), '-', MONTH(o.`close_date`)) AS `time`
11									FROM `orders` o JOIN `contracts` c ON (o.contracts_id = c.id)
12									JOIN orders_has_goods og ON (og.orders_id = o.id) 
13									JOIN goods g ON (g.id = og.goods_id) WHERE YEAR(o.`close_date`) > YEAR(NOW())-3 
14									GROUP BY g.`title`, YEAR(o.`close_date`), MONTH(o.`close_date`) 
15									ORDER BY g.`title`, o.`close_date`;
16    DECLARE continue handler for NOT FOUND set done = 1;
17
18	CREATE TABLE IF NOT EXISTS `goods_raport` (
19		`id` INT UNSIGNED NOT NULL AUTO_INCREMENT KEY,
20		`goods_name` varchar(255) NOT NULL,
21		`count` int(11) NOT NULL,
22		`date_by_month` VARCHAR(255) NOT NULL
23	);
24	TRUNCATE TABLE `goods_raport`;
25
26    OPEN cur1;
27    foreach: LOOP
28      FETCH cur1 INTO gt, gs, da;
29      IF done = 1 THEN
30        LEAVE foreach;
31      END IF;
32	  INSERT INTO `goods_raport` (`goods_name`, `count`, `date_by_month`) VALUES (gt, gs, da);
33    END LOOP foreach;
34    CLOSE cur1;
35
36	SELECT * FROM `goods_raport`;
37END;
38//