Mysql存储过程

因为项目积分这块为变动不大，维护要求不高。且性能要求较高，所以将新增积分部分逻辑转移为存储过程实现。

删除存储过程为：（如果存在函数则删除）

DROP PROCEDURE IF EXISTS addIntegral

创建存储过程时声明传入参数（入参为IN  出参）：

CREATE PROCEDURE addIntegral(
	IN data_id CHAR(32),IN data_userid CHAR(32),IN data_type CHAR(1),
	IN data_teamId CHAR(32),IN data_taskId CHAR(32),IN data_taskType CHAR(1),
	IN data_batchId CHAR(32),IN data_markId CHAR(32),IN data_markName VARCHAR(80),
	IN data_duration INT(11),IN data_integral DECIMAL(5,2),IN data_identity CHAR(1),
	IN data_status CHAR(1),IN data_extend TEXT,IN data_createTime DATETIME,
	IN data_auditTime DATETIME,IN data_settlement DATETIME,IN data_pendIntegral DECIMAL(12,2),
	IN data_dateCycle DATETIME,IN data_teamStatus CHAR(1),IN data_tableName text,IN data_audit CHAR(1)
) 

*DECLARE定义的类似是局部变量，@set定义的类似全局变量。

*定义code为SQL_STATE，默认00000为操作成功。

*声明异常处理方法，如果发生异常，则获取错误原因设置code为错误代码，msg为错误文本，并（回滚事务）

DECLARE EXIT HANDLER FOR SQLEXCEPTION
							BEGIN
								GET DIAGNOSTICS CONDITION 1 
								code = RETURNED_SQLSTATE, msg = MESSAGE_TEXT;
								ROLLBACK;
								SELECT code;
							END;

*开启事务

START TRANSACTION;  

因为积分有两个表，一个历史表，一个积分表，历史表为按季度分表，则需要动态表名。

set @data_id=data_id,@data_userid=data_userid,@data_type=data_type,@data_teamId=data_teamId,@data_taskId=data_taskId,
						@data_taskType=data_taskType,@data_batchId=data_batchId,@data_markId=data_markId,@data_markName=data_markName,
						@data_duration=data_duration,@data_integral=data_integral,@data_identity=data_identity,@data_status=data_status,
						@data_extend=data_extend,@data_createTime=data_createTime,@data_auditTime=data_auditTime,@data_settlement=data_settlement,
						@tableName=data_tableName,@data_audit=data_audit;
						set @addIntegralHistory = concat(  
						"INSERT INTO ", @tableName,   
						" (
						id,user_id,type,team_id,task_id,task_type,batch_id,mark_id,mark_name,duration,integral,identity,status,extend,createTime,auditTime,settlement
						)   
						VALUES 
						(@data_id,@data_userid,@data_type,@data_teamId,@data_taskId,@data_taskType,@data_batchId,@data_markId,@data_markName,@data_duration,@data_integral,
						@data_identity,@data_status,@data_extend,@data_createTime,@data_auditTime,@data_settlement);");  
							
						PREPARE addIntegralHistory FROM @addIntegralHistory;     
						EXECUTE addIntegralHistory;

设置参数和表名为变量。使用CONCAT拼接sql语句，并声明变量@addIntegralHistory为此sql，EXECUTE执行这个sql语句。

继续执行其他sql语句，IF判断参数决定sql语句是否执行。最后COMMIT提交事务。查询code值并返回。

完整存储过程如下：

CREATE PROCEDURE addIntegral(
	IN data_id CHAR(32),IN data_userid CHAR(32),IN data_type CHAR(1),
	IN data_teamId CHAR(32),IN data_taskId CHAR(32),IN data_taskType CHAR(1),
	IN data_batchId CHAR(32),IN data_markId CHAR(32),IN data_markName VARCHAR(80),
	IN data_duration INT(11),IN data_integral DECIMAL(5,2),IN data_identity CHAR(1),
	IN data_status CHAR(1),IN data_extend TEXT,IN data_createTime DATETIME,
	IN data_auditTime DATETIME,IN data_settlement DATETIME,IN data_pendIntegral DECIMAL(12,2),
	IN data_dateCycle DATETIME,IN data_teamStatus CHAR(1),IN data_tableName text,IN data_audit CHAR(1)
)    
    BEGIN    
					DECLARE code CHAR(5) DEFAULT '00000';
					DECLARE msg TEXT;

					DECLARE EXIT HANDLER FOR SQLEXCEPTION
							BEGIN
								GET DIAGNOSTICS CONDITION 1 
								code = RETURNED_SQLSTATE, msg = MESSAGE_TEXT;
								ROLLBACK;
								SELECT code;
							END;

          START TRANSACTION;    
						INSERT INTO integral (
						id,user_id,type,team_id,task_id,task_type,batch_id,mark_id,mark_name,duration,integral,identity,status,extend,createTime,auditTime,settlement
						)
						VALUES
						(data_id,data_userid,data_type,data_teamId,data_taskId,data_taskType,data_batchId,data_markId,data_markName,data_duration,data_integral,
						 data_identity,data_status,data_extend,data_createTime,data_auditTime,data_settlement);

-- 					动态表名
						set @data_id=data_id,@data_userid=data_userid,@data_type=data_type,@data_teamId=data_teamId,@data_taskId=data_taskId,
						@data_taskType=data_taskType,@data_batchId=data_batchId,@data_markId=data_markId,@data_markName=data_markName,
						@data_duration=data_duration,@data_integral=data_integral,@data_identity=data_identity,@data_status=data_status,
						@data_extend=data_extend,@data_createTime=data_createTime,@data_auditTime=data_auditTime,@data_settlement=data_settlement,
						@tableName=data_tableName,@data_audit=data_audit;
						set @addIntegralHistory = concat(  
						"INSERT INTO ", @tableName,   
						" (
						id,user_id,type,team_id,task_id,task_type,batch_id,mark_id,mark_name,duration,integral,identity,status,extend,createTime,auditTime,settlement
						)   
						VALUES 
						(@data_id,@data_userid,@data_type,@data_teamId,@data_taskId,@data_taskType,@data_batchId,@data_markId,@data_markName,@data_duration,@data_integral,
						@data_identity,@data_status,@data_extend,@data_createTime,@data_auditTime,@data_settlement);");  
							
						PREPARE addIntegralHistory FROM @addIntegralHistory;     
						EXECUTE addIntegralHistory; 
  
					  UPDATE user SET
						pend_integral = IFNULL(pend_integral,0)+IFNULL(data_pendIntegral,0)
						WHERE user_id = data_userid;

						IF @data_audit='0' THEN
							INSERT INTO team (
							team_id,date_cycle,user_id,integral,status
							)
							VALUES
							(data_teamId,data_dateCycle,data_userid,data_integral,data_teamStatus) ON DUPLICATE KEY UPDATE integral = integral + data_integral;
						END IF;
						COMMIT;   
						select code;
END