Stored Procedure עם משתנים ב-MySQL

במאמר הקודם למדנו על Stored Procedure – כיצד ליצור אחד כזה ולהשתמש בו. Stored Procedure הוא פונקציה שיכולה לקבל פרמטרים וגם להחזיר פרמטרים.

הכנסת פרמטרים ל-Stored Procedure

על מנת שה-Stored Procedure שלנו יקבל משתנים, אנו צריכים להגדיר לו איזה משתנים לקבל, בואו לדוגמא נניח שאנו רוצים ליצור פונקציה שמקבלת פרמטר של מספר קליינט ומחזירה לי את השורה שלו בטבלת my_costumer שאותה אנו מכירים ממאמרים קודמים.

ראשית, כפי שלמדנו במאמר הקודם אנו נשנה את ה-DELIMITER שלנו ל’//’ :

DELIMITER //

אחרי כן, נתחיל בכתיבת ה-Stored Procedure שלנו, שהוא די דומה ל-Stored Procedure שכתבנו במאמר הקודם אך הפעם אנו כותבים משהו בתוך הסוגריים של הפונקציה – ראשית את המילה IN שמשמעותה היא פרמטר שנכנס לפונקציה ואז את שם הפרמטר. השם חשוב כי אנו נשתמש בו בתוך הפונקציה כמקובל. לבסוף אנו נכתוב את סוג הפרמטר, במקרה שלנו הוא TINYINT. (זה הזמן להזכיר את המדריך לסוגי הנתונים שיש ב-MySQL). את שאר הפונקציה כותבים בדיוק כמו כל Stored Procedure.

הנה הדוגמא המיוחלת:

CREATE PROCEDURE showClient(
IN cl TINYINT)
BEGIN
SELECT * FROM my_costumer WHERE costumer_id = cl;
END //

לא נשכח בסוף להחזיר את ה-DELIMITER למה שהוא היה קודם.

DELIMITER ;

שימו לב לפרמטר ‘cl’ שהוא הדבר השונה והחשוב כאן וכיצד הוא משתלב ב-select. כאשר אני קורא לפונקציה, אני חייב להכניס את הפרמטר כדי לקבל תשובה. אם אני אכניס ‘1’, אני אקבל את הפרטים של לקוח שה-costumer_id שלו שווה לאחד. וכך אקרא לפונקציה שלי.

CALL showClient(1);

והיא תחזיר לי את השורה הנכונה:

+-------------+---------------+--------------+--------------+
| costumer_id | costumer_name | costumer_age | costumer_sex |
+-------------+---------------+--------------+--------------+
|           1 | moshiku       |           21 | male         |
+-------------+---------------+--------------+--------------+

קבלת משתנים מה-Stored Procedure

בעוד שקל יחסית להכניס משתנים ל-Stored Procedure שלנו. קבלת משתנים היא טיפה’לה יותר מסובכת. ראשית, אנו ניצור Stored Procedure שמטרתו להחזיר לי 2 משתנים – הראשון הוא הגיל המקסימלי בטבלה שלי והשני הוא הגיל הממוצע. למי שלא זוכר את השיעור על שליפת נתונים, עושים את זה באופן הבא:

SELECT AVG(costumer_age) FROM my_costumer;
SELECT MAX(costumer_age) FROM my_costumer;

הדבר השונה היחידי בבניית Stored Procedure שנותן לנו פרמטרים הוא לכתוב OUT במקום IN.

CREATE PROCEDURE getAges(
OUT avgAge TINYINT,
OUT maxAge TINYINT
)
BEGIN
SELECT MAX(costumer_age) INTO maxAge FROM my_costumer;
SELECT AVG(costumer_age) INTO avgAge FROM my_costumer;
END //

יצרתי Stored Procedure ששמו הוא getAges. הוא מחזיר לי שני פרמטרים – avgAge ו-maxAge ששניהם מסוג TINYINT. שאילתת ה-SELECT מכניסה את התוצאות לפרמטרים באמצעות INTO. גם זה חלק חשוב, ה-INTO נכנסה מיד אחרי הצהרת ה-SELECT ולפני ה-FROM.

עכשיו נוכל להריץ את ה-Stored Procedure. כשאנו מריצים את ה-Stored Procedure אנו חייבים לכתוב שמות של שני פרמטרים בתוך הסוגריים על מנת שנוכל להתייחס אליהם. מבולבלים? לא צריך להיות. פשוט בוחרים שמות של משתנים (יכולים להיות אותם שמות שיש בתוך ה-Stored Procedure). כך זה נראה:

CALL getAges(@ageMAX, @ageAVG);

מה זה ה’@’ האלו? זה הסימן למשתנים. אם אנו מריצים את זה, אנו לא מקבלים כלום. על מנת לקבל את המשתנים, אנו צריכים לבחור אותם:

SELECT @ageMax;

אפשר גם לבחור כמה:

SELECT @ageMax, @ageAVG;

וזה יחזיר לי את התוצאות שיש במשתנה:

+---------+---------+
| @ageMax | @ageAVG |
+---------+---------+
| 22      | 42      |
+---------+---------+

השאילתות שהראיתי ב-Stored Procedure הן פשוטות ולא מוסיפות הרבה. אך ניתן לכתוב Stored Procedure מורכבות הרבה יותר עם שימוש ב-IF ומשתנים נוספים בדיוק כמו כל פונקציה אחרת. למרות שכיום ממעטים להשתמש ב-Stored Procedure, עדיין מדובר בפתרון הטוב ביותר עבור מסד נתונים שמשתמשות בו מספר תוכנות.

במאמר הבא אנו נלמד על אינטגרציה בין MySQL ל-PHP.