MySQL Stored Procedure & Function

RDBMS มีให้เลือกใช้งานมากมาย ทั้งแบบ ฟรี (free) , มีค่า (license fee) Oracle ,MSSQL ,PostgreSQL ,MySQL จึงไม่ใช่เรื่องง่ายเลย ที่จะเลือกมาศึกษาสำหรับผู้เริ่มต้น

RDBMS มาพร้อมกับความสามารถการทำ Stored Procedure และ Function มันก็คือการเพิ่มความสามารถของงาน SELECT ,INSERT ,UPDATE ,DELETE ให้อยู่ในรูปแบบการเขียนโปรแกรม ซึ่งในแต่ละ Database Business เองก็มีการเขียนที่แจกต่างกัน แต่โดยรวมมี concept การทำงานที่ไม่ต่างกันเลย เมื่อเรียนรู้ Oracle Database จนเชียวชาญ ก็จะไปเรียนรู้ MySQL Stored Procedure ,Function ได้ไม่ยากเลย

อยากจะเขียนโปรแกรมกับ RDBMS ละ ทำไง

มีบางสถานะการอยากที่จะ reuse (column/field) ที่ผ่านการใส่ condition แล้ว กับ sql statement อื่น ๆ เหมือนการที่เราเขียน method (JAVA) หรือ function (Javascript) มีการส่ง parameter (IN parameter) เหล่านี้ จะมาหาคำตอบและทำความรู้จัก Stored Procedure & Function กัน

Stored Procedure & Function คืออะไร

Procedure
- เป็น schema object ชนิดนึงที่ถูกเก็บอยู่ใน database
- เป็นโปรแกรมย่อยชนิดหนึ่งที่สร้างเพื่อใช้งานเฉพาะงาน
- สามารถเรียกใช้งานซ้ำ ๆ ได้ , แก้ไขได้ตลอด
- มีความสามารถที่มากกว่า Function
- มีการ return ค่า (OUT parameter)
- มีการ รับค่า ( IN ) , ส่งค่าออก (OUT) ได้
- ส่งค่าออก ในรูปแบบ Cursor (ข้อมูลในรูปแบบ ResultSet) ได้ ซึ่งข้อนี้ Function ทำไม่ได้

Function
- เป็น schema object ชนิดนึงที่ถูกเก็บอยู่ใน database
- ใช้งานร่วมกับ select statement (เหมือนการ select field นึง)
- จำเป็นต้อง return ค่าเสมอ (อาจจะเป็น null ก็ได้)
- สามารถใช้ซ้ำ ๆ สามารถแก้ไขได้

ตัวอย่าง Create Procedure

โจทย์
- procedure name: citycount
- in parameter: country CHAR(3)
- out parameter: cities INT
- procedure logic: select count(*) from city WHERE countryCode = country

mysql> delimiter //

mysql> CREATE PROCEDURE citycount (IN country CHAR(3), OUT cities INT)
       BEGIN
         SELECT COUNT(*) INTO cities FROM world.city
         WHERE CountryCode = country;
       END//
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;

mysql> CALL citycount('JPN', @cities); -- cities in Japan
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @cities;
+---------+
| @cities |
+---------+
|     248 |
+---------+
1 row in set (0.00 sec)

mysql> CALL citycount('FRA', @cities); -- cities in France
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @cities;
+---------+
| @cities |
+---------+
|      40 |
+---------+
1 row in set (0.00 sec)

อธิบายการทำงาน
- CREATE PROCEDURE citycount() กำหนดชื่อให้กับ Procedure
- IN country CHAR(3) รับ parameter length 3 เช่น ‘THA’
- OUT cities INT คืนค่า จำนวนเมืองในประเทศเป็นจำนวนตัวเลข
- BEGIN เริ่ม procedure
- INTO cities นำค่า count(*) set ใส่ตัวแปร cities
- END จบ procedure

ตัวอย่าง Create Function

โจทย์
- function name: hello
- in parameter: message CHAR(20)
- return : CHAR(50)
- function logic: concat(‘Hello, ‘,message,’!’)

mysql> CREATE FUNCTION hello (message CHAR(20))
mysql> RETURNS CHAR(50) DETERMINISTIC
       RETURN CONCAT('Hello, ',s,'!');
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT hello('world');
+----------------+
| hello('world') |
+----------------+
| Hello, world!  |
+----------------+
1 row in set (0.00 sec)

อธิบายการทำงาน
- CREATE FUNCTION hello() สร้าง function
- message CHAR(20) รับ parameter length 20
- RETURNS CHAR(50) คืนค่าชนิด string
- RETURN CONCAT(‘Hello, ‘,message,’!’) ต่อ string และคืนค่าออกจาก function
- SELECT hello(‘world’); เรียก function ส่ง ‘hello’

ศึกษาเพิ่มเติม https://dev.mysql.com/doc/refman/8.0/en/create-procedure.html

ขอบคุณที่ติดตามครับ