ตอนที่ 12 : การใช้ Transaction บน MySQL Stored Procedure (MySQL : Stored Procedure)

พฤษภาคม 31, 2018 2:45 am โดย admin
0
25

ตอนที่ 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

Stored Procedure บน MySQL

Transaction Syntax

1.START TRANSACTION;
2. 
3.DECLARE EXIT HANDLER FOR SQLEXCEPTION
4.BEGIN
5.ROLLBACK;
6.EXIT PROCEDURE;
7.END;
8.COMMIT;

MySQL Transaction

ในการใช้งาน Transaction บน MySQL Database จะต้องกำหนด Data Type ของ Table เป็นแบบ InnoDB

Example : ตัวอย่างการใช้ TRANSACTION ในรูปแบบที่ง่าย ๆ บน MySQL Store Procedure

01.DROP PROCEDURE IF EXISTS insertCustomer;
02. 
03.DELIMITER //
04.CREATE PROCEDURE insertCustomer(IN pCustomerID VARCHAR(4),
05.IN pName VARCHAR(150),
06.IN pEmail VARCHAR(150),
07.IN pCountryCode VARCHAR(2),
08.IN pBudget DECIMAL(18,2),
09.IN pUsed DECIMAL(18,2))
10.BEGIN
11. 
12.Declare Flag for Error
13.DECLARE errorStatus BOOLEAN DEFAULT FALSE;
14. 
15.# Start Transaction
16.START TRANSACTION;
17. 
18.BEGIN
19.DECLARE EXIT HANDLER FOR SQLEXCEPTION SET errorStatus = TRUE;
20. 
21.# Statement 1
22.INSERT INTO customer (CUSTOMER_ID, NAME, EMAIL, COUNTRY_CODE, BUDGET, USED)
23.VALUES (pCustomerID, pName, pEmail, pCountryCode, pBudget, pUsed);
24. 
25.# Statement 2
26.INSERT INTO customer (CUSTOMER_ID, NAME, EMAIL, COUNTRY_CODE, BUDGET, USED)
27.VALUES (pCustomerID, pName, pEmail, pCountryCode, pBudget, pUsed);
28.END;
29. 
30.Case error in HANDLER
31.IF errorStatus = TRUE THEN
32.ROLLBACK;
33.ELSE
34.COMMIT;
35.END IF;
36. 
37.END //
38.DELIMITER ;

จากตัวอย่างนี้จะมีการ Insert ข้อมูลลงในตารางจำนวน 2 ครั้ง ซึ่งจะ Error ของ SQLEXCEPTION เกิดขึ้น แน่นอนเพราะ Key ซ้ำ และเมื่อ Error ที่ถูกจัดเก็บลงใน errorStatus = TRUE; เราสามารถเอาค่าตัวแปรนี้ไปเป็นเงื่อนไขเพื่อการ COMMIT หรือ ROLLBACK
อ้างถึงการจัดเก็บ Message Log : ตอนที่ 3 : การ Print และแสดงผลบน Stored Procedure (MySQL : Stored Procedure) 

 

1.CALL insertCustomer('C005','Fun Wipa','fun.wipa@thaicreate.com','TH','100000','0');

สามารถประยุกต์ใช้ได้กับ SAVEPOINT โดยเราจะใช้การ SAVEPOINT ในตำแหน่งที่ต้องการ แล้วสามารถที่จะ ROLLBACK มายัง SAVEPOINT นี้ได้

01.DROP PROCEDURE IF EXISTS insertCustomer;
02. 
03.DELIMITER //
04.CREATE PROCEDURE insertCustomer(IN pCustomerID VARCHAR(4),
05.IN pName VARCHAR(150),
06.IN pEmail VARCHAR(150),
07.IN pCountryCode VARCHAR(2),
08.IN pBudget DECIMAL(18,2),
09.IN pUsed DECIMAL(18,2))
10.BEGIN
11. 
12. 
13.Declare Flag for Error
14.DECLARE errorStatus BOOLEAN DEFAULT FALSE;
15. 
16.# Start Transaction
17.START TRANSACTION;
18. 
19.Create save_point
20.SAVEPOINT save_point;
21. 
22.BEGIN
23.DECLARE EXIT HANDLER FOR SQLEXCEPTION SET errorStatus = TRUE;
24. 
25.# Statement 1
26.INSERT INTO customer (CUSTOMER_ID, NAME, EMAIL, COUNTRY_CODE, BUDGET, USED)
27.VALUES (pCustomerID, pName, pEmail, pCountryCode, pBudget, pUsed);
28. 
29.# Statement 2
30.INSERT INTO customer (CUSTOMER_ID, NAME, EMAIL, COUNTRY_CODE, BUDGET, USED)
31.VALUES (pCustomerID, pName, pEmail, pCountryCode, pBudget, pUsed);
32.END;
33. 
34.Case error in HANDLER
35.IF errorStatus = TRUE THEN
36.ROLLBACK TO SAVEPOINT save_point;
37.ELSE
38.COMMIT;
39.END IF;
40. 
41.END //
42.DELIMITER ;

แหล่งที่มา : thaicreate.com/tutorial