ตอนที่ 12 : การใช้ Transaction บน MySQL Stored Procedure (MySQL : Stored Procedure) |
ตอนที่ 12 : การใช้ Transaction บน MySQL Stored Procedure (MySQL : Stored Procedure) อีกฟีเจอร์หนึ่งที่น่าสนใจบน MySQL Stored Procedure คือการจัดการกับความถูกต้องในการทำงานของ SQL Statement ด้วย Transaction ความสามารถของ Transaction คือ หลังจากที่เรา Start Transaction จะสามารถควบคุมการทำงานของ Query ที่เกิดขึ้น ในกรณีที่มีการ Error หรือ เกิดเงื่อนไขที่ไม่ต้องการ เราจะสามารถทำการ Rollback ย้อนกลับข้อมูลที่เกิดขึ้นระหว่างการทำงาน เช่น INSERT, UPDATE และ DELETE ให้กลับมายังจุดก่อนที่ Stored Procedure ที่จะทำได้
ถ้าจะพูดถึงเรื่อง Transaction บน MySQL เราสามารถใช้การทำงานผ่านโปรแกรมที่เรียกใช้งานได้ เช่นแทนที่จะใช้ Transaction บน Stored Procedure ก็ไปประกาศบน PHP, .NET หรืออื่น ๆ ซึ่งจะทำให้การควบคุม Transaction นั้นได้ดีกว่า เพราะสามารถ Rollback ได้ทั้งหมดของ Process ที่เกิดขึ้น
Note!! การใช้งาน Transaction ของ MySQL จะต้องกำหนดชนิดของ Database เป็นแบบ InnoDB
โครงสร้างของตาราง customer และ country
Table : customer
Transaction Syntax
START TRANSACTION;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
EXIT PROCEDURE;
END;
COMMIT;
ในการใช้งาน Transaction บน MySQL Database จะต้องกำหนด Data Type ของ Table เป็นแบบ InnoDB
Example : ตัวอย่างการใช้ TRANSACTION ในรูปแบบที่ง่าย ๆ บน MySQL Store Procedure
DROP PROCEDURE IF EXISTS insertCustomer;
DELIMITER //
CREATE PROCEDURE insertCustomer(IN pCustomerID VARCHAR(4),
IN pName VARCHAR(150),
IN pEmail VARCHAR(150),
IN pCountryCode VARCHAR(2),
IN pBudget DECIMAL(18,2),
IN pUsed DECIMAL(18,2))
BEGIN
# Declare Flag for Error
DECLARE errorStatus BOOLEAN DEFAULT FALSE;
# Start Transaction
START TRANSACTION;
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET errorStatus = TRUE;
# Statement 1
INSERT INTO customer (CUSTOMER_ID, NAME, EMAIL, COUNTRY_CODE, BUDGET, USED)
VALUES (pCustomerID, pName, pEmail, pCountryCode, pBudget, pUsed);
# Statement 2
INSERT INTO customer (CUSTOMER_ID, NAME, EMAIL, COUNTRY_CODE, BUDGET, USED)
VALUES (pCustomerID, pName, pEmail, pCountryCode, pBudget, pUsed);
END;
# Case error in HANDLER
IF errorStatus = TRUE THEN
ROLLBACK;
ELSE
COMMIT;
END IF;
END //
DELIMITER ;
จากตัวอย่างนี้จะมีการ Insert ข้อมูลลงในตารางจำนวน 2 ครั้ง ซึ่งจะ Error ของ SQLEXCEPTION เกิดขึ้น แน่นอนเพราะ Key ซ้ำ และเมื่อ Error ที่ถูกจัดเก็บลงใน errorStatus = TRUE; เราสามารถเอาค่าตัวแปรนี้ไปเป็นเงื่อนไขเพื่อการ COMMIT หรือ ROLLBACK
อ้างถึงการจัดเก็บ Message Log : ตอนที่ 3 : การ Print และแสดงผลบน Stored Procedure (MySQL : Stored Procedure)
CALL insertCustomer('C005','Fun Wipa','[email protected]','TH','100000','0');
สามารถประยุกต์ใช้ได้กับ SAVEPOINT โดยเราจะใช้การ SAVEPOINT ในตำแหน่งที่ต้องการ แล้วสามารถที่จะ ROLLBACK มายัง SAVEPOINT นี้ได้
DROP PROCEDURE IF EXISTS insertCustomer;
DELIMITER //
CREATE PROCEDURE insertCustomer(IN pCustomerID VARCHAR(4),
IN pName VARCHAR(150),
IN pEmail VARCHAR(150),
IN pCountryCode VARCHAR(2),
IN pBudget DECIMAL(18,2),
IN pUsed DECIMAL(18,2))
BEGIN
# Declare Flag for Error
DECLARE errorStatus BOOLEAN DEFAULT FALSE;
# Start Transaction
START TRANSACTION;
# Create save_point
SAVEPOINT save_point;
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET errorStatus = TRUE;
# Statement 1
INSERT INTO customer (CUSTOMER_ID, NAME, EMAIL, COUNTRY_CODE, BUDGET, USED)
VALUES (pCustomerID, pName, pEmail, pCountryCode, pBudget, pUsed);
# Statement 2
INSERT INTO customer (CUSTOMER_ID, NAME, EMAIL, COUNTRY_CODE, BUDGET, USED)
VALUES (pCustomerID, pName, pEmail, pCountryCode, pBudget, pUsed);
END;
# Case error in HANDLER
IF errorStatus = TRUE THEN
ROLLBACK TO SAVEPOINT save_point;
ELSE
COMMIT;
END IF;
END //
DELIMITER ;
.
|
ช่วยกันสนับสนุนรักษาเว็บไซต์ความรู้แห่งนี้ไว้ด้วยการสนับสนุน 
Load balance : Server 03
