存储过程写法

存储过程不是经常用到,但是有时负责业务需要用到,每次都去百度,这次记一下,方便下次用到,可以快速定位.

下面的实例写的清楚,可以参考下

BEGIN
	#按天拉取(ds_fenfen_lotto)的数据到报表(ds_report)表中
	DECLARE t_error INTEGER;#出错标识
	DECLARE done INTEGER;#结束标识

	DECLARE r_id BIGINT(20) DEFAULT 0;#id
	DECLARE r_count int(11) DEFAULT NULL;#数量
	DECLARE r_username varchar(50) DEFAULT 'ds';#用户名
	DECLARE r_site_id INT;#网站id
	DECLARE r_bet_amount decimal(18,2) DEFAULT NULL;#金额
	DECLARE r_valid_amount decimal(18,2) DEFAULT NULL;#有效金额
	DECLARE r_pay_off decimal(18,2) DEFAULT NULL;#盈利金额
	DECLARE r_bet_time_day date DEFAULT NULL;#日期 以天为单位

	#DECLARE fetchSeqOk BOOLEAN;
	DECLARE cur CURSOR FOR 
		SELECT count(1) count,ff.username, ff.site_id, count(ff.bet_amount), count(ff.valid_amount), count(ff.pay_off),DATE_FORMAT(ff.bet_time,'%Y-%m-%d')
		FROM ds_fenfen_lotto ff
		GROUP BY ff.username,ff.site_id,DATE_FORMAT(ff.bet_time,'%Y-%m-%d');

	#将一些标识绑定到游标
	#DECLARE CONTINUE HANDLER FOR NOT FOUND SET fetchSeqOk=true;#结束标识
	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;  
	DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1;#出错标识

	#SET fetchSeqOk=FALSE;
	START TRANSACTION;

	#循环游标读取
	OPEN cur; 
	#提取游标的数据
	read_loop:LOOP
		FETCH cur INTO r_count, r_username, r_site_id, r_bet_amount, r_valid_amount, r_pay_off, r_bet_time_day;
		IF done=1 THEN
			LEAVE read_loop;
		ELSE
			#遍历游标,#处理业务
			IF EXISTS(SELECT id FROM ds_report WHERE username = r_username AND site_id = r_site_id AND bet_time = r_bet_time_day) THEN
				#存在更新
				SELECT id INTO r_id FROM ds_report WHERE username = r_username AND site_id = r_site_id AND bet_time = r_bet_time_day;
				UPDATE ds_report 
				SET bet_count = bet_count + r_count,  betamount = betamount + r_bet_amount, winlose = winlose + r_pay_off, validamount = validamount + r_valid_amount
				WHERE id = r_id;
		  ELSE
				#不存在插入
				INSERT INTO ds_report(username,bet_count,betamount,winlose,validamount,site_id,bet_time) 
				VALUES(r_username,r_count,r_bet_amount,r_pay_off,r_valid_amount,r_site_id,r_bet_time_day);
			END IF;
		END IF;
	END LOOP;
	CLOSE cur;

	IF t_error = 1 THEN
		ROLLBACK;
	ELSE
		COMMIT;
	END IF;
END