mysql存储过程

CREATE PROCEDURE CollectionAndLikeOperation(
IN OperationType VARCHAR(1), #'0' 就是收藏操作；是 '1'就是点赞操作；
IN PColumnType varchar(1), #栏目类型 0：活动 1：产品 2：报道
IN PRelatedCode varchar(30), #关联的code
IN PUserCode varchar(30), #用户编号
out success TINYINT, #是否执行成功
OUT AddOrDel VARCHAR(1), #操作结果 '0' 取消点赞 '1'添加
OUT msg VARCHAR(200) #错误信息
)

BEGIN

DECLARE CheckExistence int DEFAULT 0 ;
DECLARE t_error INTEGER DEFAULT 0;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1; #try
START TRANSACTION; #开启事务
CASE OperationType
WHEN '0' #如果操作类型是'0' 就是收藏操作；是 '1'就是点赞操作；
then

select count(*) into CheckExistence from activity_Collection where ColumnType=PColumnType and UserCode=PUserCode and RelatedCode=PRelatedCode;
CASE PColumnType
when '0'
THEN #活动点赞操作
if CheckExistence>0 #删除点赞
then
#活动点赞数减去1
update activity_info set CollectionCount=CollectionCount-1 where Code=PRelatedCode;
DELETE from activity_Collection where ColumnType=PColumnType and UserCode=PUserCode and RelatedCode=PRelatedCode;
set AddOrDel='0';
else #添加点赞
update activity_info set CollectionCount= CASE when CollectionCount is NULL then 1 ELSE CollectionCount+1 end where `Code`=PRelatedCode;
insert into activity_Collection(ColumnType,RelatedCode,UserCode,CreateTime) VALUES(PColumnType,PRelatedCode,PUserCode, now());
set AddOrDel='1';
end if;
when '1' #产品点赞操作
THEN
if CheckExistence>0
THEN
update activity_product set CollectionCount=CollectionCount-1 where Code=PRelatedCode;
DELETE from activity_Collection where ColumnType=PColumnType and UserCode=PUserCode and RelatedCode=PRelatedCode;
set AddOrDel='0';
else
update activity_product set CollectionCount= CASE when CollectionCount is NULL then 1 ELSE CollectionCount+1 end where `Code`=PRelatedCode;
insert into activity_Collection(ColumnType,RelatedCode,UserCode,CreateTime) VALUES(PColumnType,PRelatedCode,PUserCode, now());
set AddOrDel='1';
end IF;
when '2' #活动报道点赞操作
THEN
if CheckExistence>0
THEN
update activity_product set CollectionCount=CollectionCount-1 where Code=PRelatedCode;
DELETE from activity_Collection where ColumnType=PColumnType and UserCode=PUserCode and RelatedCode=PRelatedCode;
set AddOrDel='0';
else
update activity_report set CollectionCount= CASE when CollectionCount is NULL then 1 ELSE CollectionCount+1 end where `Code`=PRelatedCode;
insert into activity_Collection(ColumnType,RelatedCode,UserCode,CreateTime) VALUES(PColumnType,PRelatedCode,PUserCode, now());
set AddOrDel='1';
end IF;
else #栏目类型错误

set success=FALSE;
set msg='栏目类型错误';

END CASE;

WHEN '1' # 点赞操作
then


select count(*) into CheckExistence from activity_like where ColumnType=PColumnType and UserCode=PUserCode and RelatedCode=PRelatedCode;
CASE PColumnType
when '0'
THEN #活动点赞操作
if CheckExistence>0 #删除点赞
then
#活动点赞数减去1
update activity_info set LikeCount=LikeCount-1 where Code=PRelatedCode;
DELETE from activity_like where ColumnType=PColumnType and UserCode=PUserCode and RelatedCode=PRelatedCode;
set AddOrDel='0';
else #添加点赞
update activity_info set LikeCount= CASE when LikeCount is NULL then 1 ELSE LikeCount+1 end where `Code`=PRelatedCode;
insert into activity_like(ColumnType,RelatedCode,UserCode,CreateTime) VALUES(PColumnType,PRelatedCode,PUserCode, now());
set AddOrDel='1';
end if;
when '1' #产品点赞操作
THEN
if CheckExistence>0
THEN
update activity_product set LikeCount=LikeCount-1 where Code=PRelatedCode;
DELETE from activity_like where ColumnType=PColumnType and UserCode=PUserCode and RelatedCode=PRelatedCode;
set AddOrDel='0';
else
update activity_product set LikeCount= CASE when LikeCount is NULL then 1 ELSE LikeCount+1 end where `Code`=PRelatedCode;
insert into activity_like(ColumnType,RelatedCode,UserCode,CreateTime) VALUES(PColumnType,PRelatedCode,PUserCode, now());
set AddOrDel='1';
end IF;
when '2' #活动报道点赞操作
THEN
if CheckExistence>0
THEN
update activity_product set LikeCount=LikeCount-1 where Code=PRelatedCode;
DELETE from activity_like where ColumnType=PColumnType and UserCode=PUserCode and RelatedCode=PRelatedCode;
set AddOrDel='0';
else
update activity_report set LikeCount= CASE when LikeCount is NULL then 1 ELSE LikeCount+1 end where `Code`=PRelatedCode;
insert into activity_like(ColumnType,RelatedCode,UserCode,CreateTime) VALUES(PColumnType,PRelatedCode,PUserCode, now());
set AddOrDel='1';
end IF;
else #栏目类型错误

set success=FALSE;
set msg='栏目类型错误';

END CASE;

else #操作类型参数输入错误
set success=FALSE;
set msg='操作类型错误';
end CASE;

IF t_error = 1 THEN #验证操作是否有错误 有错误回滚数据

ROLLBACK;
set success=FALSE;
set msg='系统错误';
ELSE
COMMIT;
set success=TRUE;
set msg='操作成功';
END IF;

END