发现美的眼睛 Prepared SQL Statement
1 DROP PROCEDURE IF EXISTS truncate_insert_sales_rank_toparow_month; 2 DELIMITER /w/ 3 CREATE PROCEDURE truncate_insert_sales_rank_toparow_month () 4 BEGIN 5 TRUNCATE sales_rank_toparow_month ; 6 INSERT INTO sales_rank_toparow_month ( 7 fk_country, 8 fk_categoryid, 9 history_year, 10 history_month 11 ) SELECT 12 country, 13 categoryid, 14 history_year, 15 history_month 16 FROM 17 grab_sales_rank_month 18 GROUP BY 19 country, 20 categoryid, 21 history_year, 22 history_month ; 23 END/w/ 24 DELIMITER; 25 CALL truncate_insert_sales_rank_toparow_month; 26 27 DROP PROCEDURE IF EXISTS insert_update_sales_rank_toparow_month; 28 DELIMITER /w/ 29 CREATE PROCEDURE insert_update_sales_rank_toparow_month () 30 BEGIN 31 SET @wtab = ' sales_rank_toparow_month al '; 32 SET @w = 10; 33 WHILE @w < 110 DO 34 SET @wfield = CONCAT('coin',@w); 35 SET @wnewvalue = CONCAT('(SELECT sum_coin FROM grab_sales_rank_month da WHERE al.fk_country = da.country AND al.fk_categoryid = da.categoryid AND al.history_year = da.history_year AND al.history_month = da.history_month AND da.topx=',@w,')'); 36 SET @wfieldb = CONCAT('amount',@w); 37 SET @wnewvalueb = CONCAT('(SELECT sum_amount FROM grab_sales_rank_month da WHERE al.fk_country = da.country AND al.fk_categoryid = da.categoryid AND al.history_year = da.history_year AND al.history_month = da.history_month AND da.topx=',@w,' )'); 38 SET @wpre = CONCAT('UPDATE ',@wtab,' SET ',@wfield,'=',@wnewvalue,',',@wfieldb,'=',@wnewvalueb); 39 PREPARE stmt FROM @wpre ; 40 EXECUTE stmt ; 41 DROP PREPARE stmt; 42 SET @w = @w + 10 ; 43 END 44 WHILE ; 45 END/w/ 46 DELIMITER ; 47 CALL insert_update_sales_rank_toparow_month; 48 49 50 51 52 53 DROP PROCEDURE IF EXISTS truncate_insert_sales_rank_toparow_week; 54 DELIMITER /w/ 55 CREATE PROCEDURE truncate_insert_sales_rank_toparow_week () 56 BEGIN 57 TRUNCATE sales_rank_toparow_week ; 58 INSERT INTO sales_rank_toparow_week ( 59 fk_country, 60 fk_categoryid, 61 history_year, 62 history_week 63 ) SELECT 64 country, 65 categoryid, 66 history_year, 67 history_week 68 FROM 69 grab_sales_rank_week 70 GROUP BY 71 country, 72 categoryid, 73 history_year, 74 history_week ; 75 END/w/ 76 DELIMITER; 77 CALL truncate_insert_sales_rank_toparow_week; 78 79 DROP PROCEDURE IF EXISTS insert_update_sales_rank_toparow_week; 80 DELIMITER /w/ 81 CREATE PROCEDURE insert_update_sales_rank_toparow_week () 82 BEGIN 83 SET @wtab = ' sales_rank_toparow_week al '; 84 SET @w = 10; 85 WHILE @w < 110 DO 86 SET @wfield = CONCAT('coin',@w); 87 SET @wnewvalue = CONCAT('(SELECT sum_coin FROM grab_sales_rank_week da WHERE al.fk_country = da.country AND al.fk_categoryid = da.categoryid AND al.history_year = da.history_year AND al.history_week = da.WeekValue AND da.topx=',@w,' )'); 88 SET @wfieldb = CONCAT('amount',@w); 89 SET @wnewvalueb = CONCAT('(SELECT sum_amount FROM grab_sales_rank_week da WHERE al.fk_country = da.country AND al.fk_categoryid = da.categoryid AND al.history_year = da.history_year AND al.history_week = da.WeekValue AND da.topx=',@w,' )'); 90 SET @wpre = CONCAT('UPDATE ',@wtab,' SET ',@wfield,'=',@wnewvalue,',',@wfieldb,'=',@wnewvalueb); 91 PREPARE stmt FROM @wpre ; 92 EXECUTE stmt ; 93 DROP PREPARE stmt; 94 SET @w = @w + 10 ; 95 END 96 WHILE ; 97 END/w/ 98 DELIMITER ; 99 CALL insert_update_sales_rank_toparow_week; 100 101 102 103 104 105 DROP PROCEDURE IF EXISTS truncate_insert_sales_rank_toparow_all; 106 DELIMITER /w/ 107 CREATE PROCEDURE truncate_insert_sales_rank_toparow_all () 108 BEGIN 109 TRUNCATE sales_rank_toparow_all ; 110 INSERT INTO sales_rank_toparow_all ( 111 fk_country, 112 fk_categoryid 113 ) SELECT 114 country, 115 categoryid 116 FROM 117 grab_sales_rank_all 118 GROUP BY 119 country, 120 categoryid; 121 END/w/ 122 DELIMITER; 123 CALL truncate_insert_sales_rank_toparow_all; 124 125 DROP PROCEDURE IF EXISTS insert_update_sales_rank_toparow_all; 126 DELIMITER /w/ 127 CREATE PROCEDURE insert_update_sales_rank_toparow_all () 128 BEGIN 129 SET @wtab = ' sales_rank_toparow_all al '; 130 SET @w = 10; 131 WHILE @w < 110 DO 132 SET @wfield = CONCAT('coin',@w); 133 SET @wnewvalue = CONCAT('(SELECT sum_coin FROM grab_sales_rank_all da WHERE al.fk_country = da.country AND al.fk_categoryid = da.categoryid AND da.topx=',@w,')'); 134 SET @wfieldb = CONCAT('amount',@w); 135 SET @wnewvalueb = CONCAT('(SELECT sum_amount FROM grab_sales_rank_all da WHERE al.fk_country = da.country AND al.fk_categoryid = da.categoryid AND da.topx=',@w,')'); 136 SET @wpre = CONCAT('UPDATE ',@wtab,' SET ',@wfield,'=',@wnewvalue,',',@wfieldb,'=',@wnewvalueb); 137 PREPARE stmt FROM @wpre ; 138 EXECUTE stmt ; 139 DROP PREPARE stmt; 140 SET @w = @w + 10 ; 141 END 142 WHILE ; 143 END/w/ 144 DELIMITER ; 145 CALL insert_update_sales_rank_toparow_all;
1 DROP PROCEDURE 2 IF EXISTS `prepare_update`; 3 DELIMITER /w/ 4 5 6 CREATE PROCEDURE `prepare_update` () 7 BEGIN 8 9 SET @wtab = ' wtable ' ; 10 SET @w = 10 ; 11 WHILE @w < 40 DO 12 13 SET @wfield = CONCAT('coin' ,@w) ; 14 SET @wnewvalue = @w + 1 ; 15 SET @wpre = CONCAT( 16 'UPDATE ' ,@wtab, 17 ' SET ' ,@wfield, 18 '=' ,@wnewvalue 19 ) ; 20 PREPARE stmt FROM @wpre ; 21 EXECUTE stmt ; 22 DROP PREPARE stmt ; 23 SET @w = @w + 10 ; 24 END 25 WHILE ; 26 END/w/ 27 DELIMITER ; 28 29 30 CALL `prepare_update`;
http://dev.mysql.com/doc/refman/5.7/en/sql-syntax-prepared-statements.html
1 CREATE TABLE t1 (a INT NOT NULL); 2 INSERT INTO t1 VALUES (4), (8), (11), (32), (80); 3 SET @table = 't1'; 4 SET @s = CONCAT('SELECT * FROM ', @table); 5 PREPARE stmt3 FROM @s; 6 EXECUTE stmt3;
1 SET @s = 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse'; 2 PREPARE stmt2 FROM @s; 3 SET @a = 6; 4 SET @b = 8; 5 EXECUTE stmt2 USING @a, @b;
1 PREPARE stmt1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse'; 2 SET @a = 3; 3 SET @b = 4; 4 EXECUTE stmt1 USING @a, @b; 5 SHOW VARIABLES LIKE '%a%';
