w怎样合并分支条件，减少代码行数？
1 DROP PROCEDURE IF EXISTS w_self_update_amzasin; 2 DELIMITER /w/ 3 CREATE PROCEDURE w_self_update_amzasin() 4 BEGIN 5 DECLARE done INT DEFAULT FALSE; 6 DECLARE w_pkid INT; 7 DECLARE w_asin VARCHAR(16); 8 DECLARE w_country VARCHAR(6); 9 DECLARE wcur CURSOR FOR SELECT pkid,asin,country FROM amzasin; 10 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; 11 12 OPEN wcur; 13 read_loop: LOOP 14 FETCH wcur INTO w_pkid,w_asin,w_country; 15 IF done THEN LEAVE read_loop; 16 END IF; 17 SET @w_nothhtps='www.amazon.'; 18 SET @w_most=CONCAT(LOWER(w_country),'/product/dp/',w_asin); 19 CASE LOWER(w_country) 20 WHEN 'ca' THEN SELECT 'w'; 21 WHEN 'in' THEN SELECT 'w'; 22 WHEN 'es' THEN SELECT 'w'; 23 WHEN 'it' THEN SELECT 'w'; 24 WHEN 'fr' THEN SELECT 'w'; 25 26 WHEN 'jp' THEN SET @w_most=CONCAT('co.',@w_most); 27 WHEN 'uk' THEN SET @w_most=CONCAT('co.',@w_most); 28 29 WHEN 'mx' THEN SET @w_most=CONCAT('com.',@w_most); 30 31 WHEN 'us' THEN SET @w_most=CONCAT('com/gp/product/',w_asin); 32 ELSE 33 BEGIN 34 END; 35 END CASE; 36 37 SET @w_nothhtps=CONCAT(@w_nothhtps,@w_most); 38 SET @wtmp=CONCAT('UPDATE amzasin ',' SET url="',@w_nothhtps,'" WHERE pkid=',w_pkid); 39 PREPARE stmt FROM @wtmp ; 40 EXECUTE stmt ; 41 DROP PREPARE stmt; 42 END LOOP; 43 CLOSE wcur; 44 END/w/ 45 DELIMITER; 46 CALL w_self_update_amzasin();
ERROR
1 DROP PROCEDURE IF EXISTS w_self_update_minerasinlist_minerasinlist; 2 DELIMITER /w/ 3 CREATE PROCEDURE w_self_update_minerasinlist() 4 BEGIN 5 DECLARE done INT DEFAULT FALSE; 6 DECLARE w_autoid INT; 7 DECLARE w_asin VARCHAR(16); 8 DECLARE w_countrycode VARCHAR(6); 9 DECLARE wcur CURSOR FOR SELECT autoid,asin,countrycode FROM minerasinlist; 10 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; 11 12 OPEN wcur; 13 read_loop: LOOP 14 FETCH wcur INTO w_autoid,w_asin,w_countrycode; 15 IF done THEN LEAVE read_loop; 16 END IF; 17 SET @w_nothhtps='www.amazon.'; 18 SET @w_most=CONCAT(LOWER(w_countrycode),'/product/dp/',w_asin); 19 CASE LOWER(w_countrycode) 20 WHEN 'ca' 21 WHEN 'in' 22 WHEN 'es' 23 WHEN 'it' 24 WHEN 'fr' THEN SELECT 'w'; 25 26 WHEN 'jp' 27 WHEN 'uk' THEN SET @w_most=CONCAT('co.',@w_most); 28 29 WHEN 'mx' THEN SET @w_most=CONCAT('com.',@w_most); 30 31 WHEN 'us' THEN SET @w_most=CONCAT('com/gp/product/',w_asin); 32 ELSE 33 BEGIN 34 END; 35 END CASE; 36 37 SET @w_nothhtps=CONCAT(@w_nothhtps,@w_most); 38 SET @wtmp = CONCAT('UPDATE minerasinlist ',' SET url="',@w_nothhtps,'" WHERE autoid=',w_autoid); 39 PREPARE stmt FROM @wtmp ; 40 EXECUTE stmt ; 41 DROP PREPARE stmt; 42 END LOOP; 43 CLOSE wcur; 44 END/w/ 45 DELIMITER; 46 CALL w_self_update_minerasinlist( );