MySQL stored procedures, implementing a loop of two cursors

Today, using PHP to develop a Web site, meet a demand. Count Google traffic on the site's keywords to calculate the growth of a keyword this week for Google traffic last week. Goole flow statistics are obtained through the Log Analyzer program. The intended difference calculation is also implemented in PHP, but the thought of a circular query database, will create a database pressure, and then write a stored procedure.

First Data structure:

CREATE TABLE ' Mobile_keywords_weeklog ' (

' id ' int (one) not NULL auto_increment,

' Topdate ' Date DEFAULT NULL,

' keywords_id ' int (one) DEFAULT ' 0 ',

' S_PV ' int (one) DEFAULT ' 0 ',

' S_GV ' int (one) DEFAULT ' 0 ',

' S_BV ' int (one) DEFAULT ' 0 ',

' D_value ' int (one) default ' 0 ' COMMENT ' versus previous week's difference ',

PRIMARY KEY (' id '),

KEY ' topdate ' (' topdate '),

KEY ' keywords_id ' (' keywords_id '),

KEY ' S_PV ' (' S_GV ')

) Engine=myisam auto_increment=702 DEFAULT charset=latin1

A stored procedure that implements a two-cursor loop:

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 to Brother_mobile.mobile_keywords_weeklog GROUP by topdate order by Topdate D ESC 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 ' 02000 ' 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 ' 02000 ' 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 do end REPEAT out_repeat;

Close Cur1;

end$$

DELIMITER;

The specific syntax in the stored procedure allows you to view the MySQL Help documentation.

Finally, call Pro_week_stat () can be invoked.