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 ;

最後更新：2017-04-02 06:52:03

MYSQL 存儲過程和函數案例例子

上一篇： ListView事件的研究

下一篇： JAVA編程思想第四版—第三章—習題與答案

相關內容

熱門內容

最新內容

MYSQL 存儲過程和函數 案例 例子

上一篇： ListView事件的研究

下一篇： JAVA編程思想第四版—第三章—習題與答案

相關內容

熱門內容

最新內容

MYSQL 存儲過程和函數案例例子