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*/BEGINDECLARE 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;/*应用当月累计大于配置的值*/DECLAREt_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 THENSET t_check=-12;ELSEIF t_operate='reduce' THEN/*用户来福币不足兑换*/IF (t_extcredits2-in_num)<t_lfblimit THENSET t_check=-12;ELSEIF (t_extcredits2-in_num)<0 THENSET 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 : BEGINIF t_check<0 THENLEAVE 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 THENSET t_check=-13;END IF;END LABLEpersonday;/*比较当月累计*/LABLEpersonmonth : BEGINIF t_check<0 THENLEAVE 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 THENSET t_check=-14;END IF;END LABLEpersonmonth;/*比较应用当天累计*/LABLEappday : BEGINIF t_check<0 THENLEAVE 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 THENSET t_check=-15;END IF;END LABLEappday;/*比较当月累计*/LABLEappmonth : BEGINIF t_check<0 THENLEAVE 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 THENSET 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 ;