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

พฤษภาคม 21, 2018 2:30 am โดย admin
0
21

ตอนที่ 7 : การใช้ Cursor บน Stored Procedure (MySQL : Stored Procedure) สำหรับ Cursor บน MySQL Stored Procedure เป็นการประกาศ Declare ตัวแปร (Variable) ชนิด Cursor ซึ่งถ้าจะให้เข้าใจง่าย ๆ ก็คือ เป็นตัวแปรที่ได้จากค่าในขณะนั้น ๆ ที่ประกอบด้วยหลาย Column และ Index ในรูปแบบของ Recordset ที่สามารถกำหนด Block ของการใช้งาน และเราสามารถที่จะกำหนดให้ Cursor ระบุค่าตัวแปรไปยังรายการต่าง ๆ ภายใน Block ไปขังตำแหน่งในค่าตัวแปรได้ ซึ่งเจ้าตัวแปร Cursor นี้ใน Stored Procedure นิยมนำมาเก็บค่าที่ได้จากการ SELECT และนำค่าตัวแปร Cursor ที่ได้ไป Loop รายการของข้อมูลเพื่อทำงานอื่น ๆ

โครงสร้างของตาราง customer และ country

Table : customer

Stored Procedure บน MySQL

Table : country

Stored Procedure บน MySQL

Cursor Syntax

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;

Example 1 : ตัวอย่างการใช้ Cursor เพื่อ Loop ข้อมูลจาก SELECT และแสดงผลออกทาง Result

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 ;

อธิบายเพิ่มเติม

ประกาศตัวแปร

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

 

อ่านข้อมูลจาก SELECT ทั้งหมดมาจัดเก็บไว้ที่ cursor_customer

DECLARE customer_cursor CURSOR FOR SELECT NAME, EMAIL FROM customer WHERE COUNTRY_CODE = pCountryCode;

Declare ตัว HANDLER ถ้า Loop จนหมด Record ให้กำหนดค่า end_record = TRUE;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET end_record = TRUE;

เปิด cursor_customer แล้วเอาค่าที่ได้ไปใส่ sName และ sEmail

OPEN customer_cursor; customer_loop: LOOP FETCH customer_cursor INTO sName, sEmail;

ถ้าถึง Record สุดท้ายหรือหมด Record แล้วให้ออกจาก Loop ทันที

IF end_record THEN CLOSE customer_cursor; LEAVE customer_loop; END IF;

 

CALL getCustomer('US')

Result

Cursor บน Stored Procedure

Example 2 : การใช้ Cursor เพื่อ Loop ข้อมูลจาก SELECT การสร้าง Temporary Table จัดเก็บค่าที่ได้จากการ Loop Cursor

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')

Result

Cursor บน Stored Procedure

แหล่งที่มา : thaicreate.com/tutorial