Mysql stored procedure to implement a loop of two cursors

Mysql stored procedure to implement a loop of two cursors

Today, I have a need to develop a website using php. Count the google traffic of website keywords and calculate the growth of a keyword for google traffic last week. Goole traffic statistics are obtained through the log analysis program. I plan to use php to calculate the difference value, but when I want to query the database cyclically, it will put pressure on the database, so I wrote a stored procedure.

First, the data structure:

Create table 'mobile _ keywords_weeklog '(

'Id' int (11) not null AUTO_INCREMENT,

'Topdate' date default null,

'Keywords _ id' int (11) DEFAULT '0 ',

'S _ pv 'int (11) DEFAULT '0 ',

'S _ gv 'int (11) DEFAULT '0 ',

'S _ bv 'int (11) DEFAULT '0 ',

'D _ value' int (11) DEFAULT '0' comment' difference from the previous week ',

Primary key ('id '),

KEY 'topdate' ('topdate '),

Key'keywords _ id' ('keywords _ id '),

KEY's _ pv '('s _ gv ')

) ENGINE = MyISAM AUTO_INCREMENT = 702 default charset = latin1

The storage process achieves a dual-game cycle:

DELIMITER $

USE 'Brother _ mobile' $

Drop procedure if exists 'Pro _ week_stat '$

Create definer = 'root' @ '%' PROCEDURE 'Pro _ week_stat '()

Top: BEGIN

DECLARE done int default 0;

DECLARE curr_week DATE;

DECLARE last_week DATE;

DECLARE a1, b1, c1 INT;

DECLARE a2, b2, c2 INT;

DECLARE d INT;

DECLARE is_update int default 0;

DECLARE all_week cursor for select topdate FROM brother_mobile.mobile_keywords_weeklog group by topdate order by topdate desc limit 0, 2;

DECLARE cur1 cursor for select id, keywords_id, s_gv FROM brother_mobile.mobile_keywords_weeklog WHERE topdate = curr_week;

DECLARE cur2 cursor for select id, keywords_id, s_gv FROM brother_mobile.mobile_keywords_weeklog WHERE topdate = last_week;

Declare continue handler for sqlstate '000000' SET done = 1;

OPEN all_week;

FETCH all_week INTO curr_week;

FETCH all_week INTO last_week;

CLOSE all_week;

If ifnull (curr_week, '') ='' or ifnull (last_week, '') ='' THEN

LEAVE top;

End if;

OPEN cur1;

Out_repeat: REPEAT

FETCH cur1 INTO a1, b1, c1;

BEGIN

DECLARE done1 int default 0;

Declare continue handler for sqlstate '000000' SET done1 = 1;

OPEN cur2;

Inner_repeat: REPEAT

FETCH cur2 INTO a2, b2, c2;

If not done THEN

SET is_update = 0;

IF b1 = b2 THEN

SET d = c1-c2;

SET is_update = 1;

UPDATE brother_mobile.mobile_keywords_weeklog SET d_value = d WHERE id = a1;

LEAVE inner_repeat;

End if;

End if;

UNTIL done1 end repeat inner_repeat;

CLOSE cur2;

END;

IF is_update <> 1 THEN

UPDATE brother_mobile.mobile_keywords_weeklog SET d_value = c1 WHERE id = a1;

End if;

UNTIL done end repeat out_repeat;

CLOSE cur1;

END $

DELIMITER;

You can view the mysql help documentation for the specific syntax in the stored procedure.

 

Call pro_week_stat.