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

DECLARE sName VARCHAR(150);
DECLARE sEmail VARCHAR(100);
DECLARE end_record BOOLEAN;

DEClARE customer_cursor CURSOR FOR 
SELECT NAME, EMAIL FROM customer;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET end_record = TRUE;

OPEN customer_cursor;

customer_loop: LOOP
     FETCH  customer_cursor INTO sName, sEmail;
     IF end_record THEN
	 CLOSE customer_cursor;
	 LEAVE customer_loop;
     END IF;

     ####### Statement #######


     ######################
END LOOP customer_loop;

DROP PROCEDURE IF EXISTS getCustomer;

DELIMITER //
CREATE PROCEDURE getCustomer(IN pCountryCode VARCHAR(2))
BEGIN

	# Declare Variable
	DECLARE sName VARCHAR(150);
	DECLARE sEmail VARCHAR(100);
	DECLARE end_record BOOLEAN;

	DECLARE sTemp VARCHAR(255) DEFAULT '';

	# Declare Cursor from SQL Query
	DECLARE customer_cursor CURSOR FOR 
	SELECT NAME, EMAIL FROM customer WHERE COUNTRY_CODE = pCountryCode;

	DECLARE CONTINUE HANDLER FOR NOT FOUND SET end_record = TRUE;

	# Open Loop Data
	OPEN customer_cursor;
	customer_loop: LOOP
	     FETCH  customer_cursor INTO sName, sEmail;
	     IF end_record THEN
		 CLOSE customer_cursor;
		 LEAVE customer_loop;
	     END IF;

	     SET  sTemp = CONCAT(sTemp,'Name = ',sName,' Email = ',sEmail,' => ');

	END LOOP customer_loop;

	SELECT sTemp;

END //
DELIMITER ;

CALL getCustomer('US')

DROP PROCEDURE IF EXISTS getCustomer;

DELIMITER //
CREATE PROCEDURE getCustomer(IN pCountryCode VARCHAR(2))
BEGIN
	
	# Declare Variable
	DECLARE sCustomerID VARCHAR(4);
	DECLARE sCustomerName VARCHAR(100);
	DECLARE sCustomerCountry VARCHAR(50);
	DECLARE end_record BOOLEAN;

	# Declare Cursor from SQL Query
	DECLARE customer_cursor CURSOR FOR
		SELECT A.NAME, A.EMAIL, B.COUNTRY_NAME FROM customer A 
			LEFT JOIN country B ON  A.COUNTRY_CODE = B.COUNTRY_CODE 
				WHERE A.COUNTRY_CODE = pCountryCode;

	DECLARE CONTINUE HANDLER FOR NOT FOUND SET end_record = TRUE;

	# Create Temp Table
	CREATE TEMPORARY TABLE IF NOT EXISTS temp(
		RowID INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
		CustomerID VARCHAR(4),
		CustomerName VARCHAR(100),
		CustomerCountry VARCHAR(50)) ENGINE = MEMORY;

	# Open Loop Data
	OPEN customer_cursor;
	customer_loop: LOOP
		FETCH  customer_cursor INTO sCustomerID, sCustomerName, sCustomerCountry;
		IF end_record THEN
			 CLOSE customer_cursor;
			 LEAVE customer_loop;
		END IF;

		# Insert Table
		INSERT INTO temp (CustomerID, CustomerName, CustomerCountry) 
		VALUES(sCustomerID, sCustomerName, sCustomerCountry);

	END LOOP customer_loop;
	
	# Return Rows
	SELECT * FROM temp;

	# Drop Table
	DROP TABLE temp;	

END //
DELIMITER ;

CALL getCustomer('US')