DROP PROCEDURE IF EXISTS truncate_insert_sales_rank_toparow_month;
DELIMITER /w/
CREATE PROCEDURE truncate_insert_sales_rank_toparow_month ()
BEGIN
TRUNCATE sales_rank_toparow_month ;
INSERT INTO sales_rank_toparow_month (
fk_country,
fk_categoryid,
history_year,
history_month
) SELECT
country,
categoryid,
history_year,
history_month
FROM
grab_sales_rank_month
GROUP BY
country,
categoryid,
history_year,
history_month ;
END/w/
DELIMITER;
CALL truncate_insert_sales_rank_toparow_month; DROP PROCEDURE IF EXISTS insert_update_sales_rank_toparow_month;
DELIMITER /w/
CREATE PROCEDURE insert_update_sales_rank_toparow_month ()
BEGIN
SET @wtab = ' sales_rank_toparow_month al ';
SET @w = 10;
WHILE @w < 110 DO
SET @wfield = CONCAT('coin',@w);
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,')');
SET @wfieldb = CONCAT('amount',@w);
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,' )');
SET @wpre = CONCAT('UPDATE ',@wtab,' SET ',@wfield,'=',@wnewvalue,',',@wfieldb,'=',@wnewvalueb);
PREPARE stmt FROM @wpre ;
EXECUTE stmt ;
DROP PREPARE stmt;
SET @w = @w + 10 ;
END
WHILE ;
END/w/
DELIMITER ;
CALL insert_update_sales_rank_toparow_month; DROP PROCEDURE IF EXISTS truncate_insert_sales_rank_toparow_week;
DELIMITER /w/
CREATE PROCEDURE truncate_insert_sales_rank_toparow_week ()
BEGIN
TRUNCATE sales_rank_toparow_week ;
INSERT INTO sales_rank_toparow_week (
fk_country,
fk_categoryid,
history_year,
history_week
) SELECT
country,
categoryid,
history_year,
history_week
FROM
grab_sales_rank_week
GROUP BY
country,
categoryid,
history_year,
history_week ;
END/w/
DELIMITER;
CALL truncate_insert_sales_rank_toparow_week; DROP PROCEDURE IF EXISTS insert_update_sales_rank_toparow_week;
DELIMITER /w/
CREATE PROCEDURE insert_update_sales_rank_toparow_week ()
BEGIN
SET @wtab = ' sales_rank_toparow_week al ';
SET @w = 10;
WHILE @w < 110 DO
SET @wfield = CONCAT('coin',@w);
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,' )');
SET @wfieldb = CONCAT('amount',@w);
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,' )');
SET @wpre = CONCAT('UPDATE ',@wtab,' SET ',@wfield,'=',@wnewvalue,',',@wfieldb,'=',@wnewvalueb);
PREPARE stmt FROM @wpre ;
EXECUTE stmt ;
DROP PREPARE stmt;
SET @w = @w + 10 ;
END
WHILE ;
END/w/
DELIMITER ;
CALL insert_update_sales_rank_toparow_week; DROP PROCEDURE IF EXISTS truncate_insert_sales_rank_toparow_all;
DELIMITER /w/
CREATE PROCEDURE truncate_insert_sales_rank_toparow_all ()
BEGIN
TRUNCATE sales_rank_toparow_all ;
INSERT INTO sales_rank_toparow_all (
fk_country,
fk_categoryid
) SELECT
country,
categoryid
FROM
grab_sales_rank_all
GROUP BY
country,
categoryid;
END/w/
DELIMITER;
CALL truncate_insert_sales_rank_toparow_all; DROP PROCEDURE IF EXISTS insert_update_sales_rank_toparow_all;
DELIMITER /w/
CREATE PROCEDURE insert_update_sales_rank_toparow_all ()
BEGIN
SET @wtab = ' sales_rank_toparow_all al ';
SET @w = 10;
WHILE @w < 110 DO
SET @wfield = CONCAT('coin',@w);
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,')');
SET @wfieldb = CONCAT('amount',@w);
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,')');
SET @wpre = CONCAT('UPDATE ',@wtab,' SET ',@wfield,'=',@wnewvalue,',',@wfieldb,'=',@wnewvalueb);
PREPARE stmt FROM @wpre ;
EXECUTE stmt ;
DROP PREPARE stmt;
SET @w = @w + 10 ;
END
WHILE ;
END/w/
DELIMITER ;
CALL insert_update_sales_rank_toparow_all;
DROP PROCEDURE
IF EXISTS `prepare_update`;
DELIMITER /w/ CREATE PROCEDURE `prepare_update` ()
BEGIN SET @wtab = ' wtable ' ;
SET @w = 10 ;
WHILE @w < 40 DO SET @wfield = CONCAT('coin' ,@w) ;
SET @wnewvalue = @w + 1 ;
SET @wpre = CONCAT(
'UPDATE ' ,@wtab,
' SET ' ,@wfield,
'=' ,@wnewvalue
) ;
PREPARE stmt FROM @wpre ;
EXECUTE stmt ;
DROP PREPARE stmt ;
SET @w = @w + 10 ;
END
WHILE ;
END/w/
DELIMITER ; CALL `prepare_update`;
http://dev.mysql.com/doc/refman/5.7/en/sql-syntax-prepared-statements.html
CREATE TABLE t1 (a INT NOT NULL);
INSERT INTO t1 VALUES (4), (8), (11), (32), (80);
SET @table = 't1';
SET @s = CONCAT('SELECT * FROM ', @table);
PREPARE stmt3 FROM @s;
EXECUTE stmt3;
SET @s = 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
PREPARE stmt2 FROM @s;
SET @a = 6;
SET @b = 8;
EXECUTE stmt2 USING @a, @b;
PREPARE stmt1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
SET @a = 3;
SET @b = 4;
EXECUTE stmt1 USING @a, @b;
SHOW VARIABLES LIKE '%a%';