【MySQL】存储过程-购买游戏装备

Question

#创建存储过程buy，实现游戏装备的购买。业务逻辑：用户购买游戏装备，首先检查余额是否充足，购买成功后，用户信息表游戏币数值减少，装备表如果已有该装备，则装备数+1, 否则装备表添加记录(数目为1), 要么一起成功，要么一起失败

CREATE PROCEDURE buy(

    IN ID_user int,       ##用户编号

    IN ID_device int,    ##装备编号

    out s_result  nchar(30)  ##支付结果: DONE |  Error occurred – terminating | incorrect parameter| insufficient 

)

BEGIN

END

重要提示：支付结果只能是 DONE、Error occurred – terminating、incorrect parameter、insufficient balance中的一种，成功的提示为DONE

以下是数据：

##CREATE DATABASE shopping DEFAULT CHARACTER SET utf8;

##SET @@autocommit = 0; -- 1代表自动提交，0代表手动提交

##事务的执行过程

##1、start transaction，开始事务。

##2、执行insert、update、delete的sql语句。

##3、如果sql语句执行失败，可手动rollback回滚事务，否则执行commit提交事务。

/*打开数据库 */

##use shopping

CREATE TABLE user(

ID INT PRIMARY KEY comment '用户账号',

name NCHAR(50) NOT NULL comment '用户名称',

coins int comment '游戏币余额'

);

insert into user values( 1998, 'ADa', 60 );

insert into user values( 1999, 'batman', 30 );

insert into user values( 2001, 'Fa– Mulan', 500 );

CREATE TABLE device(

ID INT PRIMARY KEY comment '装备编号',

desc_deveice NCHAR(200) NOT NULL comment '装备描述',

price int comment '价格'

);

insert into device values(587, '血滴子, 可提高体力', 10);

insert into device values(254, '制式大刀, 普通材质', 5);

CREATE TABLE user_device(

IDofuser INT comment '用户编码',

IDofdevice INT comment '装备编码',

qty smallint default 1 comment '用户拥有该装备的数量',

primary key (IDofuser, IDofdevice)

);

insert into user_device values(1998, 587, 10 );

insert into user_device values(1998, 254, 1 );

insert into user_device values(2001, 254, 2 );

[注意：SQL表名请用小写]

Answer 1

DELIMITER @@
CREATE  PROCEDURE  buy(
        IN ID_user INT,
        IN ID_device INT,
        OUT s_result  NCHAR(30)  ##支付结果: DONE |  Error occurred – terminating | incorrect parameter| insufficient 
) 
BEGIN
     DECLARE icoins INT;
    DECLARE iprice INT;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
     BEGIN
        signal SQLSTATE '45000' SET message_text='消费不成功';
        ROLLBACK;
    END;
    SET icoins = (SELECT coins FROM `user` WHERE ID=ID_user);
    SET iprice = (SELECT price FROM device WHERE ID=ID_device);
    IF iprice > icoins THEN
        signal SQLSTATE '45000' SET message_text='消费不成功';
    END IF;
    START TRANSACTION;
    UPDATE `user` SET coins=coins-iprice WHERE ID=ID_user;
    IF EXISTS(SELECT * FROM user_device WHERE IDofuser=ID_user AND IDofdevice=ID_device) THEN
        UPDATE user_device SET qty=qty+1 WHERE IDofuser=ID_user AND IDofdevice=ID_device;
    ELSE
        INSERT INTO user_device(IDofuser,IDofdevice) VALUES(ID_user,ID_device);
    END IF;
    SET s_result='DONE';
    COMMIT;
END@@
DELIMITER ;