自己做个备忘记录

1.需求：

根据传入的字段查询，给查询的表添加锁，查询不允许更新，
更新成功后添加添加详细。

2,。需要用到表锁，事务，拼装sql语句执行

CREATE DEFINER = CURRENT_USER PROCEDURE `NewProc`(IN `i_field` varchar(32),IN `i_uid` varchar(32),IN `i_amount` varchar(32),IN `i_source` varchar(255),IN `i_attach` varchar(1000),OUT `o_status` tinyint,OUT `msg` varchar(1000))
Label_end:
BEGIN
    #i_field 请求user_wallet的字段，i_uid用户uid,i_amount 变化的金额,i_source为插入钱包source字段数据，i_attach为插入钱包attach字段json数据
    DECLARE num_error TINYINT default 0;
    #sql语句出错时num_error赋值为1
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET num_error = 1;  
    IF ((i_field REGEXP 'balance|coin|lvbao|disposit') = 0) THEN
    #没有该字段（字段限制）
        SET msg = '请填写正确的字段';
        SET o_status = 0;
        LEAVE label_end;
    END IF;

    IF (i_amount= 0) THEN
        SET o_status = 0;
        SET msg = '输入金额不能为0';
        LEAVE label_end;
    END IF;

    select COUNT(*) from user_permission where uid = i_uid AND freeze_status != 0;
    IF (FOUND_ROWS() = 0) THEN
    #--钱包冻结
        SET o_status = 0;
        SET msg = '钱包被冻结';
        LEAVE label_end;
    END IF;
    
    #查询并锁表（查询完毕之前不能更新该数据）
    SELECT * FROM user_wallet WHERE uid = i_uid FOR UPDATE;
    IF (FOUND_ROWS() = 0) THEN
    #--没有用户信息
        SET o_status = 0;
        SET msg = '没有用户';
        LEAVE label_end;
    END IF;
    
    START TRANSACTION;

    #执行拼装的sql语句
    SET @sql_concat = CONCAT('update user_wallet set ', i_field, " = ", i_field," + ", i_amount," where uid = '", i_uid, "'");
    PREPARE presql FROM @sql_concat;
    EXECUTE presql;
    #影响行数不能加在DEALLOCATE PREPARE presql; 后面，不然不能获取到影响行数
    IF (ROW_COUNT() < 1 ) THEN
            #当sql语句出错时， 为-1
            SET o_status = 0;
            SET msg = '更新钱包失败';
            SET num_error = num_error + 1;
    END IF;
    DEALLOCATE PREPARE presql;
    
    #拼装sql语句不能赋值给局部变量，可以赋值给用户变量
    SET @sql_concat = CONCAT('select ', i_field, " INTO @field_total from user_wallet where uid = '", i_uid, "'");
    PREPARE presql FROM @sql_concat;
    EXECUTE presql; 
    DEALLOCATE PREPARE presql;
    
    SET @sql_concat = CONCAT('insert into user_wallet_', i_field, "_detail(uid, value, remain, source, attach) values ('",i_uid, "','", i_amount, "','", @field_total, "','", i_source, "','", i_attach, "')");
    PREPARE presql FROM @sql_concat;
    EXECUTE presql;
    IF (ROW_COUNT() < 1 ) THEN
            #当sql语句出错时， 为-1
            SET o_status = 0;
            SET msg = '插入详细失败';
            SET num_error = num_error + 1;
            LEAVE label_end;
    END IF;
    DEALLOCATE PREPARE presql;
    SET @field_total = 0;
    
    IF (num_error > 0) THEN
        ROLLBACK;
        LEAVE label_end;
    ELSE
        #不发生错误，切能成功插入，和更新
        COMMIT;
    END IF;
    
    SET o_status = 1;
    SET msg = '操作成功';
        

END;