Defining Stored Programs

转载

mob604756f8c81a 2016-12-27 18:11:00

1 DROP PROCEDURE
 2 IF EXISTS truncate_insert_rank_month;
 3 DELIMITER /w/
 4 
 5 
 6 CREATE PROCEDURE truncate_insert_rank_month ()
 7 BEGIN
 8 
 9 SET @w = 10 ; TRUNCATE rank_month ;
10 WHILE @w < 110 DO
11     INSERT INTO rank_month (
12         fk_country,
13         fk_categoryid,
14         topx,
15         history_year,
16         history_month,
17         coin_sum,
18         amount_sum
19     ) SELECT
20         country,
21         categoryid,
22         @w,
23         DATE_FORMAT(grab_date, '%Y'),
24         DATE_FORMAT(grab_date, '%M'),
25         SUM(grab_coin),
26         SUM(grab_amount)
27     FROM
28         grab_rank
29     WHERE
30         grab_amount_rank < (@w + 1)
31     GROUP BY
32         country,
33         categoryid,
34         DATE_FORMAT(grab_date, '%Y'),
35         DATE_FORMAT(grab_date, '%M') ;
36     SET @w = @w + 10 ;
37     END
38     WHILE ;
39     END/w/
40 DELIMITER;
41 CALL truncate_insert_rank_month;

1 DROP PROCEDURE
 2 IF EXISTS truncate_insert_rank_month;
 3 DELIMITER /w/
 4 
 5 
 6 CREATE PROCEDURE truncate_insert_rank_month ()
 7 BEGIN
 8     TRUNCATE rank_month ;
 9 SET @w = 10 ;
10 WHILE @w < 110 DO
11     INSERT INTO rank_month (
12         fk_country,
13         fk_categoryid,
14         topx,
15         history_year,
16         history_month,
17         coin_sum,
18         amount_sum
19     ) SELECT
20         country,
21         categoryid,
22         @w,
23         DATE_FORMAT(grab_date, '%Y'),
24         DATE_FORMAT(grab_date, '%M'),
25         SUM(grab_coin),
26         SUM(grab_amount)
27     FROM
28         grab_rank
29     WHERE
30         grab_amount_rank < (@w + 1)
31     GROUP BY
32         country,
33         categoryid,
34         DATE_FORMAT(grab_date, '%Y'),
35         DATE_FORMAT(grab_date, '%M') ;
36     SET @w = @w + 10 ;
37     END
38     WHILE ;
39     END/w/
40 DELIMITER;
41 CALL truncate_insert_rank_month;

1 DROP PROCEDURE
 2 IF EXISTS truncate_insert_rank_month;
 3 DELIMITER /w/
 4 
 5 
 6 CREATE PROCEDURE truncate_insert_rank_month ()
 7 BEGIN
 8     TRUNCATE rank_month ;
 9 DECLARE w INT ;
10 SET w = 10 ;
11 WHILE w < 110 DO
12     INSERT INTO rank_month (
13         fk_country,
14         fk_categoryid,
15         topx,
16         history_year,
17         history_month,
18         coin_sum,
19         amount_sum
20     ) SELECT
21         country,
22         categoryid,
23         w,
24         DATE_FORMAT(grab_date, '%Y'),
25         DATE_FORMAT(grab_date, '%M'),
26         SUM(grab_coin),
27         SUM(grab_amount)
28     FROM
29         grab_rank
30     WHERE
31         grab_amount_rank < (w + 1)
32     GROUP BY
33         country,
34         categoryid,
35         DATE_FORMAT(grab_date, '%Y'),
36         DATE_FORMAT(grab_date, '%M') ;
37     SET w = w + 10 ;
38     END
39     WHILE ;
40     END/w/
41 DELIMITER;
42 CALL truncate_insert_rank_month;

[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE w INT ;
SET w = 10 ;
WHILE w < 110 DO
    INSERT INTO rank_month (
        fk' at line 4

http://dev.mysql.com/doc/refman/5.7/en/stored-programs-defining.html

1 DROP PROCEDURE
  2 IF EXISTS truncate_insert_rank_month;
  3 DELIMITER /w/
  4 
  5 
  6 CREATE PROCEDURE truncate_insert_rank_month ()
  7 BEGIN
  8 
  9 DECLARE w INT ; TRUNCATE rank_month ;
 10 SET w = 10 ;
 11 WHILE w < 110 DO
 12     INSERT INTO rank_month (
 13         fk_country,
 14         fk_categoryid,
 15         topx,
 16         history_year,
 17         history_month,
 18         coin_sum,
 19         amount_sum
 20     ) SELECT
 21         country,
 22         categoryid,
 23         w,
 24         DATE_FORMAT(grab_date, '%Y'),
 25         DATE_FORMAT(grab_date, '%M'),
 26         SUM(grab_coin),
 27         SUM(grab_amount)
 28     FROM
 29         grab_rank
 30     WHERE
 31         grab_amount_rank < (w + 1)
 32     GROUP BY
 33         country,
 34         categoryid,
 35         DATE_FORMAT(grab_date, '%Y'),
 36         DATE_FORMAT(grab_date, '%M') ;
 37     SET w = w + 10 ;
 38     END
 39     WHILE ;
 40     END/w/
 41 DELIMITER;
 42 CALL truncate_insert_rank_month;
 43 
 44 DROP PROCEDURE
 45 IF EXISTS truncate_insert_rank_week;
 46 DELIMITER /w/
 47 
 48 
 49 CREATE PROCEDURE truncate_insert_rank_week ()
 50 BEGIN
 51 
 52 DECLARE w INT ; TRUNCATE rank_week ;
 53 SET w = 10 ;
 54 WHILE w < 110 DO
 55     INSERT INTO rank_week (
 56         fk_country,
 57         fk_categoryid,
 58         topx,
 59         history_year,
 60         history_week,
 61         coin_sum,
 62         amount_sum
 63     ) SELECT
 64         country,
 65         categoryid,
 66         w,
 67         DATE_FORMAT(grab_date, '%Y'),
 68         DATE_FORMAT(grab_date, '%V'),
 69         SUM(grab_coin),
 70         SUM(grab_amount)
 71     FROM
 72         grab_rank
 73     WHERE
 74         grab_amount_rank < (w + 1)
 75     GROUP BY
 76         country,
 77         categoryid,
 78         DATE_FORMAT(grab_date, '%Y'),
 79         DATE_FORMAT(grab_date, '%V') ;
 80     SET w = w + 10 ;
 81     END
 82     WHILE ;
 83     END/w/
 84 DELIMITER;
 85 CALL truncate_insert_rank_week;
 86 
 87  
 88 
 89 
 90 DROP PROCEDURE
 91 IF EXISTS truncate_insert_rank_all;
 92 DELIMITER /w/
 93 
 94 
 95 CREATE PROCEDURE truncate_insert_rank_all ()
 96 BEGIN
 97 
 98 DECLARE w INT ; TRUNCATE rank_all ;
 99 SET w = 10 ;
100 WHILE w < 110 DO
101     INSERT INTO rank_all (
102         fk_country,
103         fk_categoryid,
104         topx,
105         coin_sum,
106         amount_sum
107     ) SELECT
108         country,
109         categoryid,
110         w,
111         SUM(grab_coin),
112         SUM(grab_amount)
113     FROM
114         grab_rank
115     WHERE
116         grab_amount_rank < (w + 1)
117     GROUP BY
118         country,
119         categoryid ;
120     SET w = w + 10 ;
121     END
122     WHILE ;
123     END/w/
124 DELIMITER ;
125 
126 
127 CALL truncate_insert_rank_all;