xNZmHfb2

1CREATE PROCEDURE get_clients()
2BEGIN
3	DECLARE done INT DEFAULT 0;
4	DECLARE a,c,d INT;
5	DECLARE b VARCHAR(45);
6	
7	DECLARE cur CURSOR FOR 
8	SELECT clients.id, clients.name, orders.id as oid, items.price FROM clients
9	JOIN client_type ct ON ct.id = clients.type_id
10	JOIN contracts ON contracts.client_id = clients.id
11	JOIN orders ON orders.contract_id = contracts.id
12	JOIN items_to_order ito ON ito.order_id = orders.id
13	JOIN items ON items.id = ito.items_id;
14	
15	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
16		
17	DROP TEMPORARY TABLE IF EXISTS temp_table;
18    CREATE TEMPORARY TABLE temp_table(id INT PRIMARY KEY, name VARCHAR(45), order_count INT DEFAULT 0, price INT DEFAULT 0);
19	
20	OPEN cur;
21	REPEAT
22		FETCH cur INTO a, b, c, d;
23		IF NOT done THEN
24			IF EXISTS (SELECT * FROM temp_table WHERE id = a) THEN
25				UPDATE temp_table
26				SET order_count = order_count + 1,
27					temp_table.price = temp_table.price + d
28				WHERE temp_table.id = a;
29			ELSE
30				INSERT INTO temp_table VALUES (a,b,c,d);
31			END IF;
32		END IF;
33	UNTIL done END REPEAT;
34	CLOSE cur;
35	
36	SELECT * FROM temp_table;
37	DROP TEMPORARY TABLE IF EXISTS temp_table;
38END
39//