自己做個備忘記錄

1.需求：

根據(jù)傳入的字段查詢漏设，給查詢的表添加鎖，查詢不允許更新澡谭，
更新成功后添加添加詳細(xì)愿题。

2,。需要用到表鎖蛙奖，事務(wù)潘酗，拼裝sql語句執(zhí)行

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字段數(shù)據(jù)雁仲，i_attach為插入錢包attach字段json數(shù)據(jù)
    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 = '請?zhí)顚懻_的字段';
        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
    #--錢包凍結(jié)
        SET o_status = 0;
        SET msg = '錢包被凍結(jié)';
        LEAVE label_end;
    END IF;
    
    #查詢并鎖表（查詢完畢之前不能更新該數(shù)據(jù)）
    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;

    #執(zhí)行拼裝的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;
    #影響行數(shù)不能加在DEALLOCATE PREPARE presql; 后面仔夺，不然不能獲取到影響行數(shù)
    IF (ROW_COUNT() < 1 ) THEN
            #當(dāng)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
            #當(dāng)sql語句出錯時缸兔， 為-1
            SET o_status = 0;
            SET msg = '插入詳細(xì)失敗';
            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
        #不發(fā)生錯誤，切能成功插入吹艇，和更新
        COMMIT;
    END IF;
    
    SET o_status = 1;
    SET msg = '操作成功';
        

END;