Mysqlストアド・プロシージャ・カーソルの例
1838 ワード
CREATE PROCEDURE solution_relative_pro()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE sid varchar(32);
DECLARE rn int;
DECLARE s_reply_cursor CURSOR FOR
select r.tid,count(1) as rn
from tbl_cms_reply r
where r.best = 3 and r.isdelete = 0
group by r.tid;
DECLARE s_like_cursor CURSOR FOR
select l.target_id,count(1) as rn
from tbl_cms_like l
where l.like_type = 1 and l.`status` = 1
group by l.target_id;
DECLARE s_collect_cursor CURSOR FOR
select c.target_id,count(1) as rn
from tbl_cms_collection c
where c.collect_type = 0 and c.`status` = 1
group by c.target_id;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN s_reply_cursor;
read_loop:LOOP
FETCH s_reply_cursor INTO sid,rn;
IF done THEN
LEAVE read_loop;
END IF;
update tbl_cms_solution s set s.reply = rn where s.id = sid;
END LOOP;
CLOSE s_reply_cursor;
SET done = 0;
SELECT CONCAT("s_reply_cursor executed ",done);
OPEN s_like_cursor;
read_loop:LOOP
FETCH s_like_cursor INTO sid,rn;
IF done THEN
LEAVE read_loop;
END IF;
update tbl_cms_solution s set s.`like` = rn where s.id = sid;
END LOOP;
CLOSE s_like_cursor;
SET done = 0;
SELECT CONCAT("s_like_cursor executed ",done);
OPEN s_collect_cursor;
read_loop:LOOP
FETCH s_collect_cursor INTO sid,rn;
IF done THEN
LEAVE read_loop;
END IF;
update tbl_cms_solution s set s.collect = rn where s.id = sid;
END LOOP;
CLOSE s_collect_cursor;
END
以前はOracleを使用していましたが、mysqlを書くのは初めてで、構文が一致していません.mysqlの公式ドキュメントを参照してください.http://dev.mysql.com/doc/refman/5.7/en/cursors.html,
ここでdeclare handlerの構文はhttp://dev.mysql.com/doc/refman/5.7/en/declare-handler.html,
デバッグ中にoracleのようにdbms_を使用する必要がある場合があります.output出力文mysqlではSELECT CONCAT(「s_like_cursor executed」,done)を使用します.代わりに来る.