ตอนที่ 1 : รู้จักและการสร้าง Stored Procedure บน MySQL (MySQL : Stored Procedure) |
ตอนที่ 1 : รู้จักและการสร้าง Stored Procedure บน MySQL (MySQL : Stored Procedure) เชื่อหรือไม่ว่า MySQL ที่เป็นฐานข้อมูลที่ถูกใช้มากที่สุด แต่กลับเป็น Database ที่คนให้ความสำคัญกับ Stored Procedure นั้นน้อยมาก แมกระทั่งการใช้งาน View ซึ่งเป็นฟีเจอร์ที่มีประโยชน์มากก็ยังถูกละเลยไม่ให้ความสำคัญ อาจะเป็นสาเหตุเพราะ MySQL เพิ่งจะออก Feature นี้มาพร้อมกับเวอร์ชัน 5.x (แต่มันก็หลายปีมาแล้วน่ะ) แต่ การเขียนด้วย Stored Procedure บน MySQL ก็แทบจะไม่ได้รับความนิยมเลย ส่วนหนึ่งเพราะแหล่งความรู้ที่สอนใช้งานทั่ว ๆ ไป จะเป็นการเขียน PHP แบบเพื่ออ่านค่า Query จาก Table เมื่อได้ค่าก็ค่อยนำส่งไปให้ MySQL ทำงานสลับไป สลับมา มันก็ทำงานได้เช่นเดียวกัน และการเขียน Query แบบทั่ว ๆ ไป ก็คงจะง่ายในการนำไปใช้งานจริงมากกว่า เพราะการเขียนบน Stored procedure เมื่อนำ Stored ไปใช้งานการ Export/Import ก็ไม่ค่อยสะดวก ฉะนั้นเราจะเห็นว่าหลาย ๆ เว็บ หรือแม้กระทั่ง Open Source ต่าง ๆ ก็จะไม่ค่อยให้ความสำคัญกับ Stored Procedure เลย และอีกสาเหตุที่สำคัญประเด็นหนึ่งคือ MySQL เป็น Freeware ที่สามารถใช้งานได้ฟรี รวมทั้งอนาคตของ MySQL ก็ไม่ค่อยจะแน่นอน การพัฒนาฟีเจอร์และความสามารถที่เป็น Version ฟรีนั้นจึงไม่โดดเด่นหรือน่าใช้ซะเท่าไหร่ รวมทั้ง Tool หรือเครื่องมือก็ไม่ค่อยจะใช้งานได้ดีซะนะ แต่ถ้าเราได้ลองศึกษามันจริง ๆ แล้ว สำหรับผม จากที่ได้ลองเขียนเล่น ๆ ดู คิดว่ามันจะมีประโยชน์มาก ๆ กับระบบหรือ Application ที่ทำงานซับซ้อนและมีการ Query หนัก ๆ
MySQL Database Server
การเขียน Stored Procedure จะจำเป็นมากในการพัฒนาระบบขนาดใหญ่ ที่มีการจัดเก็บข้อมูลและประมวลผลจำนวนมาก ๆ เพราะการเขียน Query ในปกติ ที่มีการติดต่อกับข้อมูลหลาย ๆ ครั้ง เราจะต้อง Select แล้วใช้โปรแกรมอ่านค่า เมื่ออ่านได้ค่าแล้วค่อยส่งไปประมวลผลที่ Database ซ้ำ ๆ ซึ่งจะเป็นการทำงานซ้ำซ้อน มีการรับส่งระหว่าง Application กับ Database เป็นสิบหรือหลายร้อยครั้ง ซึ่งผลที่ตามมาคือ Performance ของโปรแกรมจะทำงานช้ามาก ทางเลือกในการแก้ปํยหานี้ก็คือ เราใช้การทำงานซ้ำซ้อนทั้งหมดนี้ที่ Database แทน โดยเราเพียงส่งค่า Parameters ที่จำเป็นต้องใช้ จากนั้นบน Database ก็จะนำค่า Parameters ที่ส่งไปนั้น ทำงานตามคำสั่งต่าง ๆ บน Stored Procedure ที่เราเขียนขึ้น เมื่อได้ค่าที่ต้องการค่อยส่งค่า Result กลับมายังโปรแกรม วิธีนี้จะเป็นการเพิ่มประสิทธิภาพการทำงานของโปรแกรมให้ทำงานเร็วขึ้น และลด Traffic ระหว่าง Database กับ Application ได้สูงมาก
ข้อดีการใช้ Stored Procedure- Syntax การทำงานที่ซับซ้อน และพัฒนา Logic ที่เป็นมากกว่าการ Query ธรรมดา
- เพิ่มประสิทธิภาพการทำงานการ Query Database ได้อย่างดีเยี่ยม ลดภาระการทำงานของ Application
- ลด Traffic ของ Network หรือระหว่าง Database กับ Application
- การพัฒนาค่อนข้างจะเป็นระบบ แยกระหว่าง Application Logic กับ Database Logic ได้ชัดเจน เช่น ถ้าต้องการแก้ไข Logic ของ Database อาจจะเพียงแก้ไขที่ Stored Procedure เท่านั้น
ข้อเสียการใช้ Stored Procedure- การเขียน Stored จะภูกกับ Database นั้น ๆ เมื่อเปลี่ยน Database ไปใช้ตัวอื่น จะต้องเขียน Stored ใหม่ทั้งหมด
- Syntax ของการเขียน Stored Procedure จะไม่เหมือนกัน
- เมื่อนำไปใช้บน Production Server การ Debug ตรวจสอบหาข้อผิดพลาดทำได้ยากพอสมควร
- ฟีเจอร์ของ Stored บน MySQL ยังมีข้อจำกัดและขอบเขตการทำงาน
- Tool หรือเครื่องมือพัฒนาไม่ค่อยมีประสิทธิภาพ เช่น ต้องเขียนบนพวก phpMyAdmin หรือ Command Line
ในการเขียน Stored Procedure สามารถเขียนได้หลายวิธี ตามพวก Tools ต่าง ๆ แต่ในบทความนี้ผมจะใช้การเขียนบน phpMyAdmin เพราะมันมีฟีเจอร์ที่รองรับการเขียน Stored Procedure , View , Function และ Trigger ด้วย
สำหรับในบทความหัวข้อ Stored Procedure บน MySQL Database นี้ทางทีมงานของไทยครีเอทได้คิดหัวข้อมาประมาณ 15-20 หัวข้อ ซึ่งจะสรุปเฉพาะประเด็นและใจความสำคัญของการเขียน Stored ให้สามารถนำไปใช้งานได้จริง โดยได้ยกตัวอย่างการสร้าง Table ประกอบขึ้นมา 2 ตารางคือ customer, country และ audit ตามโครงสร้างดังนี้
Table : customer
CREATE TABLE IF NOT EXISTS `customer` (
`CUSTOMER_ID` varchar(4) NOT NULL,
`NAME` varchar(50) NOT NULL,
`EMAIL` varchar(50) NOT NULL,
`COUNTRY_CODE` varchar(2) NOT NULL,
`BUDGET` decimal(18,2) NOT NULL,
`USED` decimal(18,2) NOT NULL,
PRIMARY KEY (`CUSTOMER_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
--
-- Dumping data for table `customer`
--
INSERT INTO `customer` (`CUSTOMER_ID`, `NAME`, `EMAIL`, `COUNTRY_CODE`, `BUDGET`, `USED`) VALUES
('C001', 'Win Weerachai', '[email protected]', 'TH', 1000000.00, 600000.00),
('C002', 'John Smith', '[email protected]', 'UK', 2000000.00, 800000.00),
('C003', 'Jame Born', '[email protected]', 'US', 3000000.00, 600000.00),
('C004', 'Chalee Angel', '[email protected]', 'US', 4000000.00, 100000.00);
Table : country
CREATE TABLE IF NOT EXISTS `country` (
`COUNTRY_CODE` varchar(2) NOT NULL,
`COUNTRY_NAME` varchar(50) NOT NULL,
PRIMARY KEY (`COUNTRY_CODE`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
--
-- Dumping data for table `country`
--
INSERT INTO `country` (`COUNTRY_CODE`, `COUNTRY_NAME`) VALUES
('TH', 'Thailand '),
('UK', 'United Kingdom'),
('US', 'United States');
Table : audit
CREATE TABLE IF NOT EXISTS `audit` (
`AUDIT_ID` int(4) NOT NULL,
`CUSTOMER_ID` varchar(4) NOT NULL,
`LOG_DATE` date NOT NULL,
`USED` decimal(18,2) NOT NULL,
PRIMARY KEY (`AUDIT_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
--
-- Dumping data for table `audit`
--
INSERT INTO `audit` (`AUDIT_ID`, `CUSTOMER_ID`, `LOG_DATE`, `USED`) VALUES
(1, 'C001', '2015-08-01', 100000.00),
(2, 'C001', '2015-08-05', 200000.00),
(3, 'C001', '2015-08-10', 300000.00),
(4, 'C002', '2015-08-02', 400000.00),
(5, 'C002', '2015-08-07', 100000.00),
(6, 'C002', '2015-08-15', 300000.00),
(7, 'C003', '2015-08-20', 400000.00),
(8, 'C003', '2015-08-25', 200000.00),
(9, 'C004', '2015-07-04', 100000.00);
สามารถนำ SQL Statement นี้ไปรันเพื่อสร้าง Table และ Database บน Query ได้เลย
Table ทั้ง 3 ตาราง
ตัวอย่างการสร้าง Stored Procedure บน MySQL Database
Syntax การสร้าง MySQL Stored Procedure
CREATE PROCEDURE getCustomer()
BEGIN
SELECT * FROM customer;
END
Syntax กรณีที่กำหนดให้ User root ได้ใช้ได้คนเดียว
CREATE DEFINER = 'root'@'localhost' PROCEDURE getCustomer()
BEGIN
SELECT * FROM customer;
END
ในการรันชุดคำสั่งในการสร้าง Stored Procedure จะต้องกำหนดขอบเขตด้วย DELIMITER ด้วย
DELIMITER //
CREATE PROCEDURE getCustomer()
BEGIN
SELECT * FROM customer;
END //
DELIMITER ;
ควรเพิ่มคำสั่ง DROP IF EXISTS ด้วย ใช้ในการณีที่มันมีอยู่แล้วจะให้ลบแล้วสร้างใหม่
DROP PROCEDURE IF EXISTS getCustomer;
DELIMITER //
CREATE PROCEDURE getCustomer()
BEGIN
SELECT * FROM customer;
END //
DELIMITER ;
ให้รัน Query บน phpMyAdmin
การเรียกใช้งาน Stored Procedure สามารถเลือก New Query ตามด้วยคำสั่ง
CALL getCustomer();
การตรวจสอบรายการ Stored Procedure ที่มีอยู่ในระบบ
เลือกเมนู Routines
รายการ Stored Procedure ของ MySQL
การสร้าง MySQL Stored Procedure บน phpMyAdmin
คลิกที่ Add routine
เป็นตัวอย่างการสร้าง Stored Procedure บน phpMyAdmin จะเห็นว่าสะดวกกว่าเขียนเองมาก
การแก้ไข MySQL Stored Procedure บน phpMyAdmin
ให้คลิกที่ Edit
จะมีหน้าจอให้แก้ไขคำสั่งของ Stored Procedure
การเรียกใช้งาน Stored Procedure สามารถเลือก New Query ตามด้วยคำสั่ง
CALL getCustomer();
การ Comment หรือ Remark ใน Stored Procedure
# Comment or Remark
จะใช้เครื่องหมาย #
.
|
ช่วยกันสนับสนุนรักษาเว็บไซต์ความรู้แห่งนี้ไว้ด้วยการสนับสนุน 
Load balance : Server 04
