mysql 5.5.9 存储过程

小需求 用了下存储过程，mysql的这玩意不够强大 5.0+才支持存储过程 和我熟知的oracle的很多语法基本都不一样，略以总结，加深印象
BEGIN
INSERT INTO vegaga_db.vg_user_account_log
(account_id,sell_order_number,amount,balance,opt_type,opt_ip,opt_date,opt_by,opt_memo,state)
SELECT
a_id,
so_no,
amount,
balance,
opt_type,
opt_ip,
opt_date,
opt_by,
remark,
301
FROM old_vegaga.t_f_account_log_vegaga old_vga
WHERE old_vga.a_id IN (SELECT aaa.id FROM vegaga_db.vg_user_account aaa);
END
第一步：此为导数据 没有select into的语法 不过有个 insert into select 的语法可以替代之
BEGIN
declare userId BIGINT;
DECLARE allPoint BIGINT DEFAULT 0;
-- select _userId;
DECLARE cursor_vga CURSOR FOR select vu.id from vg_user vu where vu.create_date <= '2011-07-15 17:00:00';
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET userId = 1;
OPEN cursor_vga;
FETCH cursor_vga INTO userId;
WHILE (userId is not null) DO
BEGIN
declare put_userPoint INTEGER;
declare userPoint INTEGER;
declare hasPoint INTEGER ;
SET put_userPoint = ((select SUM(vso.final_amount) + sum(vso.coupon_cost) from vg_sell_order vso where
vso.so_state = 105 and vso.user_id = userId));
SET userPoint = (SELECT pp.point_balance from vg_user_point pp where pp.user_id = userId);
SET hasPoint = ((select count(*) from vg_user_point po where po.user_id = userId));
IF hasPoint < 1 THEN
INSERT INTO `vg_user_point` VALUES ('', userId, '0', '301', '301');
END IF;
IF put_userPoint IS NULL THEN
SET put_userPoint = 0;
END IF;
IF userPoint IS NULL THEN
SET userPoint = 0;
END IF;
update vg_user_point vup SET vup.point_balance = (put_userPoint/2 + userPoint) where vup.user_id = userId;
SET allPoint = allPoint + (put_userPoint/2);
FETCH cursor_vga INTO userId;
END;
END WHILE;
CLOSE cursor_vga;
select allPoint;
END
此为用fetch逐步增量遍历游标，最后一次游标的值为null ，so 换种写法：
BEGIN
DECLARE userId BIGINT;
DECLARE allPoint BIGINT DEFAULT 0;
DECLARE b INT(1);
declare put_userPoint INTEGER;
declare userPoint INTEGER;
declare hasPoint INTEGER ;
DECLARE cursor_vga CURSOR FOR select vu.id from vg_user vu where vu.create_date <= '2011-07-15 17:00:00';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1;
SET b=0;
OPEN cursor_vga;
REPEAT
FETCH cursor_vga INTO userId;
SET put_userPoint = ((select SUM(vso.final_amount) + sum(vso.coupon_cost) from vg_sell_order vso where vso.so_state = 105 and vso.user_id = userId));
SET userPoint = (SELECT pp.point_balance from vg_user_point pp where pp.user_id = userId);
SET hasPoint = ((select count(*) from vg_user_point po where po.user_id = userId));
IF hasPoint < 1 THEN
IF userId IS NOT NULL THEN
INSERT INTO `vg_user_point` VALUES ('', userId, '0', '301', '301');
END IF;
END IF;
IF put_userPoint IS NULL THEN
SET put_userPoint = 0;
END IF;
IF userPoint IS NULL THEN
SET userPoint = 0;
END IF;
IF userId IS NOT NULL THEN
update vg_user_point vup SET vup.point_balance = (put_userPoint/2 + userPoint) where vup.user_id = userId;
END IF;
SET allPoint = allPoint + (put_userPoint/2);
-- FETCH cursor_vga INTO userId;
-- END;
-- END WHILE;
UNTIL b = 1
END REPEAT;
CLOSE cursor_vga;
select allPoint;
END
注意，用工具调试的 所以头部没有CREATE PROCEDURE 的声明 只不过是工具默认给你加了

延伸阅读