1 CREATE DEFINER = 'unisoft'@'%'
2 PROCEDURE unisoftdb.distributePoolId(IN poolNum int)
3 BEGIN
4
5 -- 遍历公司的游标结束条件
6 DECLARE done BOOLEAN DEFAULT FALSE;
7 -- 接收游标中公司ID
8 DECLARE id_val bigint;
9 -- 接收游标中公司的POOL_ID
10 DECLARE poolId_val int;
11 -- 用于循环poolNum的循环变量
12 DECLARE i int;
13 -- 存储每个POOL_ID对应的总记录数 (统计本POOL_ID关联的所有公司联系人数量)
14 DECLARE totalSum int;
15 -- 存储侯选的POOL_ID
16 DECLARE canPoolId bigint;
17 -- 存储侯选的POOL_ID对应的记录数
18 DECLARE minSum int;
19
20 -- 声明遍历公司的游标
21 DECLARE cursor_company_test CURSOR FOR SELECT id, pool_id FROM company_test ORDER BY ID DESC;
22 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
23
24 OPEN cursor_company_test;
25
26 read_loop: LOOP
27
28 FETCH cursor_company_test INTO id_val, poolId_val;
29
30 IF done THEN
31 CLOSE cursor_company_test;
32 LEAVE read_loop;
33 END IF;
34
35 -- 只处理POOL_ID为零的情况
36 IF poolId_val = 0 THEN
37
38 -- 重置内循环变量
39 SET i = 1;
40 SET totalSum = 0;
41 SET minSum = 0;
42 SET canPoolId = 0;
43
44 pro_loop: LOOP
45
46 -- 统计POOL_ID对应的记录数
47 SELECT IFNULL(SUM(cp.contact_num), 0) INTO totalSum FROM company_test c INNER JOIN company_property cp ON c.id = cp.id AND c.pool_id = i;
48
49 IF i > poolNum THEN
50 LEAVE pro_loop;
51 END IF;
52
53 IF i = 1 THEN
54 SET minSum = totalSum;
55 SET canPoolId = i;
56 END IF;
57
58 IF totalSum < minSum THEN
59 SET minSum = totalSum;
60 SET canPoolId = i;
61 END IF;
62
63 SET i = i + 1;
64 END LOOP pro_loop;
65
66 -- print pool id
67 UPDATE company_test SET POOL_ID = canPoolId WHERE ID = id_val;
68 -- SELECT id_val, canPoolId;
69
70 END IF;
71
72 END LOOP read_loop;
73
74 END