ตอนที่ 5 : การสร้าง Declare Temp Table - TEMPORARY (MySQL : Stored Procedure)

CREATE TEMPORARY TABLE IF NOT EXISTS temp(
		RowID INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
		Name VARCHAR(50),
		CountryName VARCHAR(50)) ENGINE = MEMORY;

DROP PROCEDURE IF EXISTS getCustomer;

DELIMITER //
CREATE PROCEDURE getCustomer()
BEGIN
	
	# Create Temp Table
	CREATE TEMPORARY TABLE IF NOT EXISTS temp(
			RowID INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
			Name VARCHAR(50),
			CountryName VARCHAR(50)) ENGINE = MEMORY;

			# Insert Table
			INSERT INTO temp (Name,CountryName) VALUES ('Weerachai Nukitram','Thailand');
			INSERT INTO temp (Name,CountryName) VALUES ('Surachai Sirisart','United Stats');
			INSERT INTO temp (Name,CountryName) VALUES ('Adisorn Boonsong','United Kingdom');

	# Return Rows
	SELECT * FROM temp;

	# Drop Table
	DROP TABLE temp;	
        
END //

DELIMITER ;

CALL getCustomer();

DROP PROCEDURE IF EXISTS getCustomer;

DELIMITER //
CREATE PROCEDURE getCustomer()
BEGIN
	
	# Create Temp Table
	CREATE TEMPORARY TABLE IF NOT EXISTS temp(
			RowID INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
			Name VARCHAR(50),
			CountryCode VARCHAR(2)) ENGINE = MEMORY;

			# Insert Table
			INSERT INTO temp (Name,CountryCode) SELECT NAME,COUNTRY_CODE FROM customer;

	# Return Rows
	SELECT * FROM temp;

	# Drop Table
	DROP TABLE temp;	
        
END //

DELIMITER ;

CALL getCustomer();