ตอนที่ 11 : การใช้ Error Handling หรือ Try Catch ดักจับข้อผิดลาด (MySQL : Stored Procedure)

DROP PROCEDURE IF EXISTS Stored_Name;

DELIMITER //
CREATE PROCEDURE Stored_Name()

BEGIN
	BEGIN
		DECLARE EXIT HANDLER FOR SQLEXCEPTION
		# Statement 1

		# Statement 2
	END;
	// Statement for after exit handler error (1)

	BEGIN
		DECLARE EXIT HANDLER FOR SQLEXCEPTION
		# Statement 3

		# Statement 4
	END;
	// Statement for after exit handler error (2)

END //
DELIMITER ;

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 duplicateKey BOOLEAN DEFAULT FALSE;
	BEGIN
		DECLARE EXIT HANDLER FOR 1062 /* Duplicate key*/ SET duplicateKey = TRUE;
		#DECLARE CONTINUE HANDLER FOR NOT FOUND SET errorStatus = FALSE;

		# 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 duplicateKey = TRUE THEN
		#SELECT "Error Duplicate Key";
		CALL writeLog('Error Insert duplicate Key');
	END IF;

END //
DELIMITER ;

CALL insertCustomer('C005','Fun Wipa','[email protected]','TH','100000','0');

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),
	OUT pResult INT,
	OUT pMessage VARCHAR(500))
BEGIN
	
	# Declare Variable
	DECLARE errCode CHAR(5) DEFAULT '00000';
	DECLARE errMsg TEXT;
	DECLARE effRows INT;

	# Declare Handler Exception
	DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
	    BEGIN
	      GET DIAGNOSTICS CONDITION 1
		errCode = RETURNED_SQLSTATE, errMsg = MESSAGE_TEXT;
	    END;

	# Statement 
	INSERT INTO customer (CUSTOMER_ID, NAME, EMAIL, COUNTRY_CODE, BUDGET, USED)
		VALUES (pCustomerID, pName, pEmail, pCountryCode, pBudget, pUsed);

	# Set Result 
	IF errCode = '00000' THEN
		GET DIAGNOSTICS effRows = ROW_COUNT;
		SET pResult  = effRows;
		SET pMessage = 'Insert Data Successfully';
	ELSE
		SET pResult  = 0;
		SET pMessage = CONCAT('Error, Code = ',errCode,', Message = ',errMsg);
	END IF;

END //
DELIMITER ;