รู้จักและการสร้าง Stored Procedure บน MySQL

พฤษภาคม 9, 2018 1:48 am โดย admin
0
9

ตอนที่ 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

01.CREATE TABLE IF NOT EXISTS `customer` (
02.`CUSTOMER_ID` varchar(4) NOT NULL,
03.`NAMEvarchar(50) NOT NULL,
04.`EMAIL` varchar(50) NOT NULL,
05.`COUNTRY_CODE` varchar(2) NOT NULL,
06.`BUDGET` decimal(18,2) NOT NULL,
07.`USED` decimal(18,2) NOT NULL,
08.PRIMARY KEY (`CUSTOMER_ID`)
09.) ENGINE=MyISAM DEFAULT CHARSET=utf8;
10. 
11.--
12.-- Dumping data for table `customer`
13.--
14. 
15.INSERT INTO `customer` (`CUSTOMER_ID`, `NAME`, `EMAIL`, `COUNTRY_CODE`, `BUDGET`, `USED`) VALUES
16.('C001''Win Weerachai''win.weerachai@thaicreate.com''TH', 1000000.00, 600000.00),
17.('C002''John  Smith''john.smith@thaicreate.com''UK', 2000000.00, 800000.00),
18.('C003''Jame Born''jame.born@thaicreate.com''US', 3000000.00, 600000.00),
19.('C004''Chalee Angel''chalee.angel@thaicreate.com''US', 4000000.00, 100000.00);


Stored Procedure บน MySQL

Table : country

01.CREATE TABLE IF NOT EXISTS `country` (
02.`COUNTRY_CODE` varchar(2) NOT NULL,
03.`COUNTRY_NAME` varchar(50) NOT NULL,
04.PRIMARY KEY (`COUNTRY_CODE`)
05.) ENGINE=MyISAM DEFAULT CHARSET=utf8;
06. 
07.--
08.-- Dumping data for table `country`
09.--
10. 
11.INSERT INTO `country` (`COUNTRY_CODE`, `COUNTRY_NAME`) VALUES
12.('TH''Thailand  '),
13.('UK''United Kingdom'),
14.('US''United States');


Stored Procedure บน MySQL

Table : audit

01.CREATE TABLE IF NOT EXISTS `audit` (
02.`AUDIT_ID` int(4) NOT NULL,
03.`CUSTOMER_ID` varchar(4) NOT NULL,
04.`LOG_DATE` date NOT NULL,
05.`USED` decimal(18,2) NOT NULL,
06.PRIMARY KEY (`AUDIT_ID`)
07.) ENGINE=MyISAM DEFAULT CHARSET=utf8;
08. 
09.--
10.-- Dumping data for table `audit`
11.--
12. 
13.INSERT INTO `audit` (`AUDIT_ID`, `CUSTOMER_ID`, `LOG_DATE`, `USED`) VALUES
14.(1, 'C001''2015-08-01', 100000.00),
15.(2, 'C001''2015-08-05', 200000.00),
16.(3, 'C001''2015-08-10', 300000.00),
17.(4, 'C002''2015-08-02', 400000.00),
18.(5, 'C002''2015-08-07', 100000.00),
19.(6, 'C002''2015-08-15', 300000.00),
20.(7, 'C003''2015-08-20', 400000.00),
21.(8, 'C003''2015-08-25', 200000.00),
22.(9, 'C004''2015-07-04', 100000.00);


Stored Procedure บน MySQL

สามารถนำ SQL Statement นี้ไปรันเพื่อสร้าง Table และ Database บน Query ได้เลย

Stored Procedure บน MySQL

Table ทั้ง 3 ตาราง

ตัวอย่างการสร้าง Stored Procedure บน MySQL Database

Syntax การสร้าง MySQL Stored Procedure

1.CREATE PROCEDURE getCustomer()
2.BEGIN
3.SELECT FROM customer;
4.END


Syntax กรณีที่กำหนดให้ User root ได้ใช้ได้คนเดียว

1.CREATE DEFINER = 'root'@'localhost' PROCEDURE getCustomer()
2.BEGIN
3.SELECT FROM customer;
4.END


ในการรันชุดคำสั่งในการสร้าง Stored Procedure จะต้องกำหนดขอบเขตด้วย DELIMITER ด้วย

DELIMITER // CREATE PROCEDURE getCustomer() BEGIN SELECT * FROM customer; END // DELIMITER ;


ควรเพิ่มคำสั่ง DROP IF EXISTS ด้วย ใช้ในการณีที่มันมีอยู่แล้วจะให้ลบแล้วสร้างใหม่

1.DROP PROCEDURE IF EXISTS getCustomer;
2. 
3.DELIMITER //
4.CREATE PROCEDURE getCustomer()
5.BEGIN
6.SELECT FROM customer;
7.END //
8.DELIMITER ;


Stored Procedure บน MySQL

ให้รัน Query บน phpMyAdmin

การเรียกใช้งาน Stored Procedure สามารถเลือก New Query ตามด้วยคำสั่ง

1.CALL getCustomer();

 

การตรวจสอบรายการ Stored Procedure ที่มีอยู่ในระบบ

Stored Procedure บน MySQL

เลือกเมนู Routines

Stored Procedure บน MySQL

รายการ Stored Procedure ของ MySQL

การสร้าง MySQL Stored Procedure บน phpMyAdmin

Stored Procedure บน MySQL

คลิกที่ Add routine

Stored Procedure บน MySQL

เป็นตัวอย่างการสร้าง Stored Procedure บน phpMyAdmin จะเห็นว่าสะดวกกว่าเขียนเองมาก

การแก้ไข MySQL Stored Procedure บน phpMyAdmin

Stored Procedure บน MySQL

ให้คลิกที่ Edit

Stored Procedure บน MySQL

จะมีหน้าจอให้แก้ไขคำสั่งของ Stored Procedure

การเรียกใช้งาน Stored Procedure สามารถเลือก New Query ตามด้วยคำสั่ง

1.CALL getCustomer();


Stored Procedure บน MySQL

การ Comment หรือ Remark ใน Stored Procedure

1.# Comment or Remark


จะใช้เครื่องหมาย #

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