ตอนที่ 2 : การสร้าง Parameters บน MySQL (MySQL : Stored Procedure)

DROP PROCEDURE IF EXISTS Procedure_Name;

DELIMITER //
CREATE PROCEDURE Procedure_Name(IN Param1 DataType(size),IN Param2 DataType(size),)
BEGIN

END //
DELIMITER ;

DROP PROCEDURE IF EXISTS getCustomer;

DELIMITER //
CREATE PROCEDURE getCustomer(IN pCountryCode VARCHAR(2))
BEGIN
   SELECT * FROM customer WHERE COUNTRY_CODE = pCountryCode;
END //
DELIMITER ;

CALL getCustomer('US');

DROP PROCEDURE IF EXISTS getCustomer;

DELIMITER //
CREATE PROCEDURE getCustomer(IN pCountryCode VARCHAR(2),IN pUsed DECIMAL(18,2))
BEGIN
   SELECT * FROM customer WHERE COUNTRY_CODE = pCountryCode AND USED >= pUsed;
END //
DELIMITER ;

CALL getCustomer('US','500000');

DROP PROCEDURE IF EXISTS getCustomer;

DELIMITER //
CREATE PROCEDURE getCustomer(IN pCountryCode VARCHAR(2),IN pUsed DECIMAL(18,2))
BEGIN

	SET @sCountryCode = pCountryCode;
	SET @sUsed = pUsed;
	PREPARE id FROM
		'SELECT * FROM customer WHERE COUNTRY_CODE = ? AND USED >= ?';
	EXECUTE id USING @sCountryCode,@sUsed;
	DEALLOCATE PREPARE id;

END //
DELIMITER ;

CALL getCustomer('US','500000');

BEGIN
   SELECT * FROM customer
   	WHERE COUNTRY_CODE = pCountryCode
        	AND USED >=pUsed;
END