littlerubyworld的博客

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 ;