时间戳

首页 > 解决方案 > MySQL 存储过程更新多个数据库

mysql - MySQL 存储过程更新多个数据库

问题描述

我无法理解此存储过程的功能。我知道它应该更新预订表但是我不明白完整的语法以及这个程序在后台做什么

CREATE PROCEDURE make_booking (IN p_room_id VARCHAR(255), IN p_booked_date DATE, IN p_booked_time 
TIME, IN p_member_id VARCHAR(255))
BEGIN
DECLARE v_price DECIMAL(6, 2);
DECLARE v_payment_due DECIMAL(6, 2);    
SELECT price INTO v_price FROM rooms WHERE id = p_room_id;  
INSERT INTO bookings (room_id, booked_date, booked_time, member_id) VALUES (p_room_id, 
p_booked_date, p_booked_time, p_member_id); 
SELECT payment_due INTO v_payment_due FROM members WHERE id = p_member_id;
UPDATE members SET payment_due = v_payment_due + v_price WHERE id = p_member_id;    
END $$;

所涉及的表格及其相关字段如下供参考：

房间：id、room_type、价格预订：id、room_id、booked_date、booked_time、member_id、datetime_of_booking、payment_status 成员：id、密码、电子邮件、member_since、payment_due

再次感谢你！

标签： mysql

解决方案

带有注释的注释希望能解释这里发生了什么：

CREATE PROCEDURE make_booking (IN p_room_id VARCHAR(255), IN p_booked_date DATE, IN p_booked_time 
TIME, IN p_member_id VARCHAR(255))
BEGIN

/* Declare some temporary variables to hold some data that we'll be SELECTing later */
DECLARE v_price DECIMAL(6, 2);
DECLARE v_payment_due DECIMAL(6, 2);    

/* SELECT the price of the room and put it into the v_price variable */
SELECT price INTO v_price FROM rooms WHERE id = p_room_id;  

/* INSERT a record into the bookings table for the room and member */
INSERT INTO bookings (room_id, booked_date, booked_time, member_id) VALUES (p_room_id, 
p_booked_date, p_booked_time, p_member_id); 

/* Get the current payment due for the member and put it into the v_payment_due variable */
SELECT payment_due INTO v_payment_due FROM members WHERE id = p_member_id;

/* Update the payment due for the member to whatever it was before plus the price of the room we put into v_price earlier */
UPDATE members SET payment_due = v_payment_due + v_price WHERE id = p_member_id;  

END $$;

推荐阅读