Chapter 15

Stored Procedure

အခု အခန်းမှာတော့ MySQL ရဲ့ Stored Procedure ကို လေ့လာရမှာပါ။ Stored Procedure ဆိုတာက SQL statement တွေကို တစ်စုတစ်စည်းတည်း သိမ်းထားပြီး နာမည်တစ်ခု နဲ့ ခေါ်သုံးနိုင်သည့် ပုံစံပါ။ Query တွေ အများကြီးကို တစ်ခါတည်း run ချင်သည့် အခါ ဒါမှမဟုတ် logic တစ်ခုကို ထပ်ခါထပ်ခါ သုံးချင်သည့် အခါမှာ Stored Procedure က အသုံးဝင်ပါတယ်။ တစ်ခါ ရေးထားလိုက်ရင် နောက်တစ်ခါ ခေါ်သုံးရုံ ပဲ ဖြစ်လို့ code တွေ ထပ်ရေးစရာ မလိုတော့ပါဘူး။

ဒီ အခန်းမှာ ရှေ့ အခန်းတွေက students , departments နဲ့ Fees table တွေကို ဆက်လက် အသုံးပြုသွားပါမယ်။

DELIMITER

Stored Procedure ရေးတဲ့ အခါ statement တစ်ခု နဲ့ တစ်ခု ကြားမှာ semicolon ( ; ) ကို သုံးရပါတယ်။ ဒါပေမယ့် MySQL က ; တွေ့ရင် statement ပြီးပြီ လို့ ထင်ပြီး run လုပ်လိုက်မှာ ဖြစ်ပါတယ်။ ဒါ​ကြောင့် Procedure တစ်ခုလုံး မပြီးခင် run မသွားအောင် DELIMITER ကို သုံးပြီး အဆုံးသတ် သင်္ကေတ ကို ယာယီ ပြောင်းပေးရပါတယ်။

DELIMITER //

အပေါ်က command က အဆုံးသတ် သင်္ကေတ ကို ; အစား // အဖြစ် ပြောင်းလိုက်တာပါ။ Procedure ရေးပြီးတဲ့ အခါ ; ကို ပြန်ပြောင်းပေးရပါမယ်။

DELIMITER ;

Procedure ဖန်တီးခြင်း

အရင်ဆုံး parameter မပါသည့် ရိုးရိုး Procedure တစ်ခု ဖန်တီးကြည့်ရအောင်။ students table ထဲက data အားလုံးကို ထုတ်ပေးမည့် Procedure တစ်ခု ဖြစ်ပါတယ်။

DELIMITER //

CREATE PROCEDURE GetAllStudents()
BEGIN
    SELECT * FROM students;
END //

DELIMITER ;

အပေါ်မှာ CREATE PROCEDURE နဲ့ Procedure နာမည် GetAllStudents ကို ပေးထားပါတယ်။ BEGIN နဲ့ END ကြားမှာ run ချင်သည့် statement တွေကို ထည့်ရေးရပါတယ်။ အခု students table ကို ထုတ်ပေးမည့် SELECT statement တစ်ခုကို ထည့်ထားပါတယ်။

CALL

Procedure ကို run ဖို့ အတွက် CALL ကို သုံးပါတယ်။

CALL GetAllStudents();

+------------+-----------+----------+---------------------+
| student_id | name      | dep_code | created_at          |
+------------+-----------+----------+---------------------+
|          1 | Mg Mg     | CS_101   | 2020-10-14 00:14:06 |
|          2 | Aung Gyi  | CS_101   | 2020-10-14 00:14:06 |
|          3 | Yang Aung | CS_101   | 2020-10-14 00:14:06 |
|          4 | Kyaw Kyaw | CS_102   | 2020-10-14 00:14:06 |
|          5 | Moe Moe   | CS_102   | 2020-10-14 00:14:06 |
+------------+-----------+----------+---------------------+

CALL GetAllStudents(); ဆိုပြီး ခေါ်လိုက်ရုံ နဲ့ အထဲက SELECT * FROM students; ကို run သွားတာ တွေ့ရပါမယ်။

IN Parameter

Procedure ကို value တစ်ခု ပေးပို့ပြီး အလုပ်လုပ်ချင်သည့် အခါမှာ parameter ကို သုံးနိုင်ပါတယ်။ IN ကတော့ Procedure ထဲကို value ထည့်ပေးမည့် parameter အမျိုးအစားပါ။ အခု dep_code ကို ပေးပြီး အဲဒီ department က ကျောင်းသား တွေ ကို ထုတ်ပေးမည့် Procedure တစ်ခု ဖန်တီးကြည့်ရအောင်။

DELIMITER //

CREATE PROCEDURE GetStudentsByDept(IN dept VARCHAR(255))
BEGIN
    SELECT * FROM students WHERE dep_code = dept;
END //

DELIMITER ;

အခု dept ဆိုသည့် parameter ကို CALL လုပ်သည့် အခါ ထည့်ပေးရပါမယ်။

CALL GetStudentsByDept('CS_101');

+------------+-----------+----------+---------------------+
| student_id | name      | dep_code | created_at          |
+------------+-----------+----------+---------------------+
|          1 | Mg Mg     | CS_101   | 2020-10-14 00:14:06 |
|          2 | Aung Gyi  | CS_101   | 2020-10-14 00:14:06 |
|          3 | Yang Aung | CS_101   | 2020-10-14 00:14:06 |
+------------+-----------+----------+---------------------+

CS_101 လို့ ပေးလိုက်သည့် အတွက် CS_101 department က ကျောင်းသား တွေ ကို ပဲ ထုတ်ပေးတာ တွေ့ရပါမယ်။ တကယ်လို့ CS_102 လို့ ပြောင်း ခေါ်ရင် CS_102 က data တွေ ပဲ ထွက်လာပါလိမ့်မယ်။

OUT Parameter

OUT ကတော့ Procedure ထဲက ရလဒ် တန်ဖိုး ကို ပြန်ထုတ်ပေးမည့် parameter ပါ။ အခု department တစ်ခု မှာ ကျောင်းသား ဘယ်နှစ်ယောက် ရှိသလဲ ဆိုသည့် အရေအတွက် ကို ပြန်ပေးမည့် Procedure တစ်ခု ဖန်တီးကြည့်ရအောင်။

DELIMITER //

CREATE PROCEDURE CountStudentsByDept(IN dept VARCHAR(255), OUT total INT)
BEGIN
    SELECT COUNT(*) INTO total FROM students WHERE dep_code = dept;
END //

DELIMITER ;

အပေါ်မှာ SELECT COUNT(*) INTO total ဆိုပြီး ရလဒ်ကို total ဆိုသည့် OUT parameter ထဲ ထည့်လိုက်တာ တွေ့ရပါမယ်။ INTO ကတော့ value ကို variable ထဲ ထည့်ဖို့ အတွက် သုံးတာပါ။

အခု CALL လုပ်သည့် အခါ ရလဒ်ကို သိမ်းဖို့ variable တစ်ခု လိုပါတယ်။ MySQL မှာ variable ကို @ နဲ့ စတင် ရေးပါတယ်။

CALL CountStudentsByDept('CS_101', @total);
SELECT @total;

+--------+
| @total |
+--------+
|      3 |
+--------+

CS_101 မှာ ကျောင်းသား ၃ ယောက် ရှိသည့် အတွက် @total ထဲမှာ 3 ဝင်သွားတာ တွေ့ရပါမယ်။

Variable နှင့် Logic

Procedure ထဲမှာ variable တွေ ကြေညာပြီး logic တွေ ရေးနိုင်ပါတယ်။ Variable ကို DECLARE နဲ့ ကြေညာရပါတယ်။ အခု department တစ်ခု ရဲ့ fee total ကို တွက်ပြီး၊ ၁ သိန်း ၅ သောင်း ထက် များ မများ ကို စာသား နဲ့ ပြန်ပေးမည့် Procedure တစ်ခု ရေးကြည့်ရအောင်။

DELIMITER //

CREATE PROCEDURE CheckDeptFee(IN dept VARCHAR(255), OUT result VARCHAR(50))
BEGIN
    DECLARE total INT;

    SELECT SUM(amount) INTO total FROM Fees WHERE dep_code = dept;

    IF total > 150000 THEN
        SET result = 'High Fee';
    ELSE
        SET result = 'Normal Fee';
    END IF;
END //

DELIMITER ;

အပေါ်မှာ DECLARE total INT; နဲ့ variable တစ်ခု ကြေညာထားပါတယ်။ ပြီးတော့ IF ... THEN ... ELSE ... END IF နဲ့ condition စစ်ထားပါတယ်။ SET ကတော့ variable ထဲ value ထည့်ဖို့ အတွက် ပါ။

CALL CheckDeptFee('CS_101', @result);
SELECT @result;

+------------+
| @result    |
+------------+
| Normal Fee |
+------------+

CS_101 ရဲ့ fee က 100000 ဖြစ်သည့် အတွက် 150000 အောက်မှာ ရှိနေပြီး Normal Fee လို့ ထွက်လာတာ တွေ့ရပါမယ်။

Procedure ဖျက်ခြင်း

ရှိပြီးသား Procedure ကို ဖျက်ချင်သည့် အခါ DROP PROCEDURE ကို သုံးပါတယ်။

DROP PROCEDURE IF EXISTS GetAllStudents;

IF EXISTS ကို ထည့်ထားရင် အဲဒီ Procedure မရှိ ခဲ့ရင်တောင် error မတက်ဘဲ ကျော်သွားပါမယ်။

Procedure များကို ကြည့်ခြင်း

Database ထဲမှာ ရှိသည့် Procedure တွေ ကို ကြည့်ချင်ရင် အောက်က command ကို သုံးနိုင်ပါတယ်။

SHOW PROCEDURE STATUS WHERE Db = 'myschool';

အပေါ်က myschool နေရာမှာ ကိုယ့် ရဲ့ database နာမည်ကို ထည့်ပေးရပါမယ်။

အခု ဆိုရင် Stored Procedure ဆိုတာ ဘာလဲ၊ parameter တွေ ဘယ်လို သုံးရလဲ၊ logic တွေ ဘယ်လို ရေးရလဲ ဆိုတာ သိသွားပါပြီ။ Stored Procedure ကို သုံးခြင်း အားဖြင့် ရှုပ်ထွေးသည့် logic တွေ ကို database ထဲမှာ စုစည်းထားနိုင်ပြီး ထပ်ခါထပ်ခါ ခေါ်သုံးနိုင်ပါတယ်။