· 8 years ago · Apr 12, 2018, 07:32 PM
1DELIMITER $$
2
3CREATE PROCEDURE `report`()
4BEGIN
5 DECLARE col_number INT(2) DEFAULT 0;
6 DECLARE counter INT(2) DEFAULT 0;
7 DECLARE done INT(1) DEFAULT 0;
8 DECLARE last_prod VARCHAR(128) DEFAULT "";
9 DECLARE prod_name VARCHAR(128);
10 DECLARE cross_prod_name VARCHAR(128);
11 DECLARE col_name VARCHAR(32);
12 DECLARE create_temp_tbl TEXT;
13
14 -- ------------------------------------------------------------------------
15 -- Query for fetching products and associated cross products.
16 -- ------------------------------------------------------------------------
17 DECLARE cross_products CURSOR FOR
18 SELECT SQL_NO_CACHE
19 b.product_name,
20 c.product_name
21 FROM cross_sell_products AS a
22 INNER JOIN product_names AS b ON
23 a.product_id = b.product_id
24 INNER JOIN product_names AS c ON
25 a.cross_sell_product_id = c.product_id;
26
27 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
28
29
30 -- ------------------------------------------------------------------------
31 -- Find the largest number of cross products for a single product
32 -- ------------------------------------------------------------------------
33 SELECT SQL_NO_CACHE
34 COUNT(*) AS total INTO col_number
35 FROM cross_sell_products
36 GROUP BY product_id
37 ORDER BY total DESC
38 LIMIT 1;
39
40
41 -- ------------------------------------------------------------------------
42 -- Get rid of any instance of report_tmp. Given its structure is changing
43 -- from procedure call to procedure call, it might cause problems because
44 -- of the different number of columns it has versus the ones that we want
45 -- to insert.
46 -- ------------------------------------------------------------------------
47 DROP TABLE IF EXISTS report_temp;
48
49
50 -- ------------------------------------------------------------------------
51 -- Create a table with as many fields for cross products as the number
52 -- stored in col_number (which is the maximum number of cross products for
53 -- a single product).
54 -- Also, make product_name a primary key. We'll need this later in the
55 -- insertion phase.
56 -- ------------------------------------------------------------------------
57 SET create_temp_tbl = "CREATE TEMPORARY TABLE report_temp (product_name VARCHAR(128) PRIMARY KEY, ";
58
59 WHILE counter < col_number DO
60 SET col_name = CONCAT("cross_sel_product_", counter);
61 SET create_temp_tbl = CONCAT(create_temp_tbl, CONCAT(col_name, " VARCHAR(128)"));
62
63 IF counter != col_number - 1 THEN
64 SET create_temp_tbl = CONCAT(create_temp_tbl, ", ");
65 END IF;
66
67 SET counter = counter + 1;
68 END WHILE;
69
70 SET @x = CONCAT(create_temp_tbl, ");");
71
72 PREPARE stmt FROM @x;
73 EXECUTE stmt;
74 DEALLOCATE PREPARE stmt;
75 TRUNCATE TABLE report_temp;
76
77
78 -- ------------------------------------------------------------------------
79 -- Begin fetch of products and cross products
80 -- ------------------------------------------------------------------------
81 OPEN cross_products;
82
83 REPEAT
84 FETCH cross_products INTO prod_name, cross_prod_name;
85
86 IF NOT done THEN
87 -- ----------------------------------------------------------------
88 -- Be sure to reset the counter every time the product group is
89 -- changing, so that we don't attempt to use more fields than
90 -- there are in the temporary table.
91 -- ----------------------------------------------------------------
92 IF NOT prod_name = last_prod THEN
93 SET counter = 0;
94 SET last_prod = prod_name;
95 END IF;
96
97 -- ----------------------------------------------------------------
98 -- For each cross product of a product, try to insert it, in case
99 -- it's not the first one in the group a key duplication error will
100 -- be reported. In this case, update the entry with a new cross
101 -- product.
102 -- ----------------------------------------------------------------
103 SET col_name = CONCAT("cross_sel_product_", counter);
104 SET @insert_stmt = CONCAT("INSERT INTO report_temp SET"
105 ," product_name = ?, "
106 , col_name ," = ? "
107 ,"ON DUPLICATE KEY UPDATE "
108 , col_name ," = ?");
109
110 SET @prod_name = prod_name;
111 SET @cross_prod_name = cross_prod_name;
112
113 PREPARE stmt_ins FROM @insert_stmt;
114 EXECUTE stmt_ins USING @prod_name, @cross_prod_name, @cross_prod_name;
115 DEALLOCATE PREPARE stmt_ins;
116
117 -- Go to next field
118 SET counter = counter + 1;
119 END IF;
120 UNTIL done END REPEAT;
121
122 CLOSE cross_products;
123
124 -- ------------------------------------------------------------------------
125 -- Return desired result
126 -- ------------------------------------------------------------------------
127 SELECT SQL_NO_CACHE * FROM report_temp;
128END $$
129
130DELIMITER ;
131
132
133
134-- ----------------------------------------------------------------------------
135-- Table structure for table 'cross_sell_products'
136-- ----------------------------------------------------------------------------
137
138CREATE TABLE /*!32312 IF NOT EXISTS*/ `cross_sell_products` (
139 `product_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
140 `cross_sell_product_id` int(10) unsigned NOT NULL,
141 PRIMARY KEY (`product_id`,`cross_sell_product_id`) /*!50100 USING BTREE */
142) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
143
144
145
146-- ----------------------------------------------------------------------------
147-- Dumping data for table 'cross_sell_products'
148-- ----------------------------------------------------------------------------
149
150LOCK TABLES `cross_sell_products` WRITE;
151/*!40000 ALTER TABLE `cross_sell_products` DISABLE KEYS*/;
152INSERT INTO `cross_sell_products` (`product_id`, `cross_sell_product_id`) VALUES
153 ('1','2'),
154 ('1','3'),
155 ('2','1'),
156 ('2','4'),
157 ('2','5'),
158 ('2','6');
159/*!40000 ALTER TABLE `cross_sell_products` ENABLE KEYS*/;
160UNLOCK TABLES;
161
162
163-- ----------------------------------------------------------------------------
164-- Table structure for table 'products'
165-- ----------------------------------------------------------------------------
166
167CREATE TABLE /*!32312 IF NOT EXISTS*/ `products` (
168 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
169 PRIMARY KEY (`id`)
170) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
171
172
173
174-- ----------------------------------------------------------------------------
175-- Dumping data for table 'products'
176-- ----------------------------------------------------------------------------
177
178LOCK TABLES `products` WRITE;
179/*!40000 ALTER TABLE `products` DISABLE KEYS*/;
180INSERT INTO `products` (`id`) VALUES
181 ('1'),
182 ('2'),
183 ('3');
184/*!40000 ALTER TABLE `products` ENABLE KEYS*/;
185UNLOCK TABLES;
186
187
188-- ----------------------------------------------------------------------------
189-- Table structure for table 'product_names'
190-- ----------------------------------------------------------------------------
191
192CREATE TABLE /*!32312 IF NOT EXISTS*/ `product_names` (
193 `product_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
194 `product_name` varchar(128) NOT NULL,
195 PRIMARY KEY (`product_id`)
196) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
197
198
199
200-- ----------------------------------------------------------------------------
201-- Dumping data for table 'product_names'
202-- ----------------------------------------------------------------------------
203
204LOCK TABLES `product_names` WRITE;
205/*!40000 ALTER TABLE `product_names` DISABLE KEYS*/;
206INSERT INTO `product_names` (`product_id`, `product_name`) VALUES
207 ('1','first product'),
208 ('2','second product'),
209 ('3','third product'),
210 ('4','fourth product'),
211 ('5','fifth product'),
212 ('6','sixth product');
213/*!40000 ALTER TABLE `product_names` ENABLE KEYS*/;
214UNLOCK TABLES;
215/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS*/;