MYSQL 存储过程和函数 案例 例子

这个也是做的项目，保存做个备份先。

UCENTER里的 兑换

ucenter_reducelfb //调用数据 call ucenter_reducelfb(参数1,参数2，....)

DELIMITER $

USE `root`$

DROP PROCEDURE IF EXISTS `ucenter_reducelfb`$

CREATE DEFINER=`root`@`localhost` PROCEDURE `ucenter_reducelfb`(in_appid INT,in_tel VARCHAR(11),in_num INT,in_oreason VARCHAR(1000))
/*by 夜色紫宸風 2011-10-25
*/
BEGIN
DECLARE t_tel VARCHAR(11) DEFAULT '';
DECLARE t_uid INT DEFAULT 0;
DECLARE t_uid2 INT DEFAULT 0;
DECLARE t_extcredits2 INT DEFAULT 0;
/*来福币设置*/
DECLARE t_tablename VARCHAR(100) DEFAULT 0;/*来福币表名称*/
DECLARE t_month VARCHAR(10) DEFAULT '';/*本月初*/
DECLARE t_month_next VARCHAR(10) DEFAULT '';/*下月初*/
DECLARE t_day VARCHAR(10) DEFAULT '';/*今天*/
DECLARE t_day_next VARCHAR(10) DEFAULT '';/*明天*/
DECLARE t_total INT DEFAULT 0;/*累计*/
/*兑换来福币设置*/
DECLARE t_appid INT DEFAULT 0;
DECLARE t_lfbeachmax INT DEFAULT 0;/*单次兑换数值大于配置的值*/
DECLARE t_lfblimit INT DEFAULT 0;/*剩余来福币*/
DECLARE t_lfbpersondaymax INT DEFAULT 0;/*用户当天累计大于配置的值*/
DECLARE t_lfbpersonmonthmax INT DEFAULT 0;/*用户当月累计大于配置的值*/
DECLARE t_lfbappdaymax INT DEFAULT 0;/*应用当天累计大于配置的值*/
DECLARE t_lfbappmonthmax INT DEFAULT 0;/*应用当月累计大于配置的值*/
DECLARE	t_operate VARCHAR(10);

DECLARE t_check INT DEFAULT 0;
DECLARE out_return INT DEFAULT 0;
DECLARE tmp INT DEFAULT 0;
DECLARE t_ym INT DEFAULT 0;

SET t_tablename=CONCAT('sz_app_credit_log_',DATE_FORMAT(NOW() ,'%Y%m'));

SET @sqlstr = CONCAT('CREATE TABLE IF NOT EXISTS `',t_tablename,'` (',
'`lid` int(11) NOT NULL AUTO_INCREMENT,',
'`uid` int(11) NOT NULL DEFAULT 0,',
'`operate` enum(''query'',''add'',''reduce'') NOT NULL,',
'`appid` int(11) NOT NULL DEFAULT 0,',
'`ovalue` int(11) NOT NULL DEFAULT 0,',
'`ostatus` int(11) NOT NULL DEFAULT 0,',
'`oreason` varchar(255) NOT NULL,',
'`dateline` int(11) NOT NULL DEFAULT 0,',
'`credittype` tinyint(4) NOT NULL,',
'PRIMARY KEY (`lid`)',
') ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;');
PREPARE stmt FROM @sqlstr;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SELECT uid,tel INTO t_uid,t_tel FROM sz_ucenter_members WHERE tel=in_tel;

IF t_uid>0 THEN
		/*检测主站是否存在该用户*/
		SELECT uid,extcredits2 INTO t_uid2,t_extcredits2 FROM sz_common_member_count WHERE uid=t_uid;

		IF t_uid2>0 THEN
				/*兑换来福币检测开始-----------------------------------------------*/
				SET t_operate='reduce';
				SELECT appid,lfbeachmax,lfblimit,lfbpersondaymax,lfbpersonmonthmax,lfbappdaymax,lfbappmonthmax INTO t_appid,t_lfbeachmax,t_lfblimit,t_lfbpersondaymax,t_lfbpersonmonthmax,t_lfbappdaymax,t_lfbappmonthmax FROM sz_app_credit_config WHERE appid = in_appid;
				IF t_appid>0 THEN
					/*单次兑换数值大于配置的值*/
					IF in_num>t_lfbeachmax THEN
						SET t_check=-12;
					ELSE
						IF t_operate='reduce' THEN
							/*用户来福币不足兑换*/
							IF (t_extcredits2-in_num)<t_lfblimit THEN
								SET t_check=-12;
							ELSE
								IF (t_extcredits2-in_num)<0 THEN
									SET t_check=-12;
								END IF;
							END IF;
						END IF;
						SET t_day=UNIX_TIMESTAMP(DATE_FORMAT(NOW() ,'%Y-%m-%d'));
						SET t_day_next=UNIX_TIMESTAMP(DATE_ADD(DATE_FORMAT(NOW() ,'%Y-%m-%d'),INTERVAL 1 DAY));
						SET t_month=UNIX_TIMESTAMP(DATE_FORMAT(NOW() ,'%Y-%m-01'));
						SET t_month_next=UNIX_TIMESTAMP(DATE_ADD(DATE_FORMAT(NOW() ,'%Y-%m-01'),INTERVAL 1 MONTH));
						LABLEpersonday : BEGIN
							IF t_check<0 THEN
								LEAVE LABLEpersonday;
							END IF;
							/*比较当天累计*/
							SET @sqlstr = CONCAT('select sum(ovalue) INTO @total FROM ',t_tablename,' where appid=',in_appid,' and credittype =1  and uid=',t_uid,' and operate=''',t_operate,'''  and  dateline>=',t_day,' AND dateline<',t_day_next);
							PREPARE stmt FROM @sqlstr;
							EXECUTE stmt;
							DEALLOCATE PREPARE stmt;
							SET t_total=IFNULL(@total,0);

							/*用户当天累计大于配置的值*/
							IF (t_total+in_num)>t_lfbpersondaymax THEN
								SET t_check=-13;
							END IF;
						END LABLEpersonday;
						/*比较当月累计*/
						LABLEpersonmonth : BEGIN
							IF t_check<0 THEN
								LEAVE LABLEpersonmonth;
							END IF;
							SET @sqlstr = CONCAT('select sum(ovalue) INTO @total FROM ',t_tablename,' where appid =',in_appid,' and credittype =1 and uid=',t_uid,' and operate =''',t_operate,''' and dateline>=',t_month,' and dateline <',t_month_next);
							PREPARE stmt FROM @sqlstr;
							EXECUTE stmt;
							DEALLOCATE PREPARE stmt;
							SET t_total=IFNULL(@total,0);
							/*用户当月累计大于配置的值*/
							IF (t_total+in_num)>t_lfbpersonmonthmax THEN
								SET t_check=-14;
							END IF;
						END LABLEpersonmonth;
						/*比较应用当天累计*/
						LABLEappday : BEGIN
							IF t_check<0 THEN
								LEAVE LABLEappday;
							END IF;
							SET @sqlstr = CONCAT('select sum(ovalue) INTO @total FROM ',t_tablename,' where appid =',in_appid,' and credittype = 1  and operate =''',t_operate,''' and dateline>=',t_day,' and dateline <',t_day_next);
							PREPARE stmt FROM @sqlstr;
							EXECUTE stmt;
							DEALLOCATE PREPARE stmt;
							SET t_total=IFNULL(@total,0);
							/*应用当天累计大于配置的值*/
							IF (t_total+in_num)>t_lfbappdaymax THEN
								SET t_check=-15;
							END IF;
						END LABLEappday;
						/*比较当月累计*/
						LABLEappmonth : BEGIN
							IF t_check<0 THEN
								LEAVE LABLEappmonth;
							END IF;
							SET @sqlstr = CONCAT('select sum(ovalue) INTO @total FROM ',t_tablename,' where appid=',in_appid,' and credittype =1  and operate =''',t_operate,''' and dateline>=',t_month,' and dateline<',t_month_next);
							PREPARE stmt FROM @sqlstr;
							EXECUTE stmt;
							DEALLOCATE PREPARE stmt;
							SET t_total=IFNULL(@total,0);
							/*应用当月累计大于配置的值*/
							IF (t_total+in_num)>t_lfbappmonthmax THEN
								SET t_check=-16;
							ELSE
								/*符合兑换条件*/
								SET t_check=1;
							END IF;
						END LABLEappmonth;

					END IF;
				ELSE
					/*该应用没有配置积分兑换限制或者没有该应用*/
					SET t_check=-11;
				END IF;

				/*兑换来福币检测结束-----------------------------------------------*/
				IF t_check=1 THEN
					/*判断用户积分是否足够*/
					IF t_extcredits2<in_num THEN
						/*来福币操作日志*/
						SET tmp= common_lfbchange_log(in_appid,in_tel,in_num,2,in_oreason,4);
						SET t_check=-3;
						SET out_return= -3;
					ELSE
						/*更新用户积分*/
						UPDATE sz_common_member_count SET extcredits2 =extcredits2-in_num WHERE uid=t_uid;
						/*查询用户积分*/
						SELECT extcredits2 INTO t_extcredits2 FROM sz_common_member_count WHERE uid=t_uid;
						/*写更新日志*/
						SET @sqlstr=CONCAT('INSERT INTO ',t_tablename,' (`uid`,`operate`,`credittype`,`appid`,`ovalue`,`oreason`,`dateline`,`ostatus`) values (',t_uid,',''reduce'',1,',in_appid,',',in_num,',''',in_oreason,''',',UNIX_TIMESTAMP(NOW()),',',t_extcredits2,')');
						PREPARE stmt FROM @sqlstr;
						EXECUTE stmt;
						DEALLOCATE PREPARE stmt;
						/*来福币操作日志*/
						SET tmp= common_lfbchange_log(in_appid,in_tel,in_num,2,in_oreason,0);
						SET out_return= t_extcredits2;

					END IF;
				ELSE
					/*来福币操作日志*/
					SET tmp= common_lfbchange_log(in_appid,in_tel,in_num,2,in_oreason,t_check);
					SET out_return= t_check;
				END IF;

		/*苏州生活网不存在该用户*/
		ELSE
			/*来福币日志*/
			SET tmp= common_lfbchange_log(in_appid,in_tel,in_num,2,in_oreason,2);
			SET out_return=-1;
		END IF;
/*ucenter 中不存在该用户*/
ELSE
		/*来福币日志*/
		SET tmp= common_lfbchange_log(in_appid,in_tel,in_num,2,in_oreason,1);
		SET out_return= -1;
END IF;
SELECT out_return;
END$

DELIMITER ;

common_lfbchange_log

DELIMITER $

USE `root`$

DROP FUNCTION IF EXISTS `common_lfbchange_log`$

CREATE DEFINER=`root`@`localhost` FUNCTION `common_lfbchange_log`(in_appid INT,in_tel VARCHAR(11),in_num INT,in_type INT,in_oreason VARCHAR(500),in_result INT) RETURNS INT(11)
    NO SQL
    COMMENT 'type 1:添加 2:减少\r\nresult  0:成功 1:ucenter中没有该用户 2:苏州生活网中不存在该用户'
BEGIN
/*by 夜色紫宸風 2011-10-25
*/
INSERT INTO sz_common_lfbchange_log (`appid`,`tel`,`num`,`type`,`oreason`,`result`,`dateline`) VALUES (in_appid,in_tel,in_num,in_type,in_oreason,in_result,UNIX_TIMESTAMP(NOW()));
RETURN 0;
END$

DELIMITER ;