ตอนที่ 9 : การใช้พารามิเตอร์ชนิด OUT เพื่อส่งค่ากลับ (MySQL : Stored Procedure)

OUT Parameter_Name DataType(size) -- return this variable

DROP PROCEDURE IF EXISTS getCustomerCount;

DELIMITER //
CREATE PROCEDURE getCustomerCount(IN pCountryCode VARCHAR(2),
	OUT pCustomerCount INT)
BEGIN
	
	# Count Customer by CountryCode
	SELECT COUNT(*) INTO pCustomerCount FROM customer WHERE COUNTRY_CODE = pCountryCode;
        
END //

DELIMITER ;

DROP PROCEDURE IF EXISTS getCustomer;

DELIMITER //
CREATE PROCEDURE getCustomer(IN pCountryCode VARCHAR(2))
BEGIN
	# Declare Variable
	DECLARE pCustomerCount INT DEFAULT 0;

	# Call stored procedure getCustomerCount
	CALL getCustomerCount(pCountryCode,pCustomerCount); // call and return pCustomerCount
	
	# Print
	SELECT CONCAT('Customer Count = ',pCustomerCount);
        
END //

DELIMITER ;

CALL getCustomer('US');

DROP PROCEDURE IF EXISTS getCustomer;

DELIMITER //
CREATE PROCEDURE getCustomer(IN pCountryCode VARCHAR(2),
	OUT pRowFound INT)
BEGIN

	# Return SELECT
	SELECT * FROM customer WHERE COUNTRY_CODE = sCountryCode;
	
	# Return  pRowFound
	SET pRowFound  = FOUND_ROWS();
        
END //

DELIMITER ;

SET @p0='US'; CALL `getCustomer`(@p0, @p1); SELECT @p1 AS `pRowFound`;

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 ;

SET @p0='C005'; SET @p1='Fun Wipa'; SET @p2='[email protected]'; SET @p3='TH'; SET @p4='100000'; SET @p5='0'; CALL `insertCustomer`(@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7); SELECT @p6 AS `pResult`, @p7 AS `pMessage`;