عند استخدام قاعدة بيانات علاقية Relational Database، سنحتاج إلى استخدام استعلامات فردية باستخدام لغة الاستعلام البنيوية Structured Query Language -أو SQL اختصارًا- لاسترجاع البيانات أو معالجتها مثل استعلامات SELECT أو INSERT أو UPDATE أو DELETE من شيفرة التطبيق مباشرةً، إذ تعمل هذه التعليمات مع جداول قاعدة البيانات الأساسية وتعالجها فورًا.
لكن إذا استخدمنا التعليمات أو مجموعة التعليمات نفسها ضمن تطبيقات متعددة يمكنها الوصول إلى قاعدة البيانات نفسها، وتكررت هذه التعليمات عدة مرات فيمكننا في هذه الحالة استخدام الإجراءات المخزَّنة Stored Procedures التي يدعمها MySQL كحال العديد من أنظمة إدارة قواعد البيانات العلاقية الأخرى، حيث تساعد هذه الإجراءات المخزنة في تجميع تعليمة SQL واحدة أو أكثر لإعادة استخدامها باسم مشترك من خلال تغليف منطق العمل المشترك ضمن قاعدة البيانات نفسها، ويمكن استدعاء مثل هذه الإجراءات من التطبيق الذي يصل إلى قاعدة البيانات لاسترجاع البيانات أو معالجتها.
تساعدنا الإجراءات المخزنة على إنشاء برامج قابلة لإعادة الاستخدام للمهام الشائعة التي سنستخدمها عبر تطبيقات متعددة، كما توفر طريقة للتحقق من صحة البيانات، أو تقديم طبقة إضافية من أمان الوصول إلى البيانات من خلال تقييد مستخدمي قاعدة البيانات من الوصول إلى الجداول الأساسية مباشرةً وإنشاء استعلامات عشوائية.
سنتعلّم في هذا المقال ما هي الإجراءات المخزَّنة وكيفية إنشاء إجراءات مخزنة بسيطة تعيد البيانات، وإجراءات تستخدم كلًا من معاملات الدخل والخرج.
مستلزمات العمل
يجب أن يكون لدينا حاسوب يشغّل نظام إدارة قواعد البيانات العلاقية RDBMS المستند إلى لغة SQL. وقد اختبرنا التعليمات والأمثلة الواردة في هذا المقال باستخدام البيئة التالية:
- خادم عامل على توزيعة أوبنتو مع مستخدم بصلاحيات مسؤول مختلف عن المستخدم الجذر وجدار حماية مضبوط باستخدام أداة UFW كما هو موضح في دليل الإعداد الأولي للخادم مع الإصدار 20.04 من أوبنتو
- نظام MySQL مُثبَّت على الخادم كما هو موضح في مقال كيفية تثبيت MySQL على أوبنتو، وقد نفذنا خطوات هذا المقال باستخدام مستخدم MySQL مختلف عن المستخدم الجذر
- معرفة أساسية بتنفيذ استعلامات SELECT لاسترجاع البيانات من قاعدة البيانات
ملاحظة: تجدر الإشارة لأنّ الكثير من أنظمة إدارة قواعد البيانات العلاقية RDBMS لها تقديماتها الفريدة من لغة SQL، ولا تُعَد صيغة الإجراءات المخزنة جزءًا من معيار SQL الرسمي. حيث ستعمل الأوامر المُقدمة في هذا المقال بنجاح مع معظم هذه الأنظمة، ولكن تُعَد الإجراءات المخزنة خاصة بقاعدة البيانات، وبالتالي قد نجد بعض الاختلافات في الصيغة أو الناتج عند اختبارها على أنظمة مختلفة عن MySQL.
سنحتاج أيضًا إلى قاعدة بيانات فارغة يمكن من خلالها إنشاء جداول توضّح استخدام الإجراءات المخزنة، ويمكن مطالعة القسم التالي للحصول على تفاصيل حول الاتصال بخادم MySQL وإنشاء قاعدة بيانات تجريبية، والتي سنستخدمها في أمثلة هذا المقال.
الاتصال بخادم MySQL وإعداد قاعدة بيانات تجريبية
سنتتصل بخادم MySQL وننشئ قاعدة بيانات تجريبية لاتباع الأمثلة الواردة في هذا المقال، حيث سنستخدم مجموعة سيارات افتراضية، ونخزّن تفاصيل السيارات المملوكة حاليًا مع نوعها وطرازها وسنة بنائها وقيمتها.
إذا كان نظام قاعدة بيانات SQL الخاص بنا يعمل على خادم بعيد، نتصل بالخادم باستخدام بروتوكول SSH من جهازنا المحلي كما يلي:
$ ssh user@your_server_ip
ثم نفتح واجهة سطر أوامر خادم MySQL مع وضع اسم حساب مستخدم MySQL الخاص بنا مكان user:
$ mysql -u user -p
ننشئ قاعدة بيانات باسم procedures:
mysql> CREATE DATABASE procedures;
إذا أُنشئِت قاعدة البيانات بنجاح، فسيظهر الخرج التالي:
الخرج Query OK, 1 row affected (0.01 sec)
يمكن اختيار قاعدة البيانات procedures من خلال تنفيذ تعليمة USE التالية:
$ USE procedures;
وسيظهر الخرج التالي:
الخرج Database changed
اخترنا قاعدة البيانات، ويمكنك الآن إنشاء جداول تجريبية ضمنها. سيحتوي الجدول cars على بيانات مبسَّطة حول السيارات الموجودة في قاعدة البيانات، حيث سيحتوي على الأعمدة التالية:
-
make: نوع كل سيارة مملوكة، ونمثّل باستخدام نوع البياناتvarcharبحد أقصى 100 محرف -
model: اسم طراز السيارة، ونمثّله باستخدام نوع البياناتvarcharبحد أقصى 100 محرف -
year: سنة صنع السيارة باستخدام نوع البياناتintللاحتفاظ بالقيم العددية -
value: قيمة السيارة باستخدام نوع البياناتdecimalبحد أقصى 10 أرقام ورقمين بعد الفاصلة العشرية
أنشئ هذا الجدول التجريبي باستخدام الأمر التالي:
mysql> CREATE TABLE cars ( mysql> make varchar(100), mysql> model varchar(100), mysql> year int, mysql> value decimal(10, 2) mysql> );
إذا كان الخرج كما يلي، فهذا يعني إنشاء الجدول بنجاح:
الخرج Query OK, 0 rows affected (0.00 sec)
سندرج بعض البيانات التجريبية في الجدول cars من خلال تنفيذ عملية INSERT INTO التالية:
mysql> INSERT INTO cars mysql> VALUES mysql> ('Porsche', '911 GT3', 2020, 169700), mysql> ('Porsche', 'Cayman GT4', 2018, 118000), mysql> ('Porsche', 'Panamera', 2022, 113200), mysql> ('Porsche', 'Macan', 2019, 27400), mysql> ('Porsche', '718 Boxster', 2017, 48880), mysql> ('Ferrari', '488 GTB', 2015, 254750), mysql> ('Ferrari', 'F8 Tributo', 2019, 375000), mysql> ('Ferrari', 'SF90 Stradale', 2020, 627000), mysql> ('Ferrari', '812 Superfast', 2017, 335300), mysql> ('Ferrari', 'GTC4Lusso', 2016, 268000);
تضيف العملية INSERT INTO عشر سيارات رياضية نموذجية للجدول، حيث توجد أربع سيارات من نوع Porsche وخمسة سيارات من نوع Ferrari. يشير الخرج التالي إلى إضافة جميع الصفوف العشرة:
الخرج Query OK, 10 rows affected (0.00 sec) Records: 10 Duplicates: 0 Warnings: 0
نحن الآن جاهزون لمتابعة هذا المقال والبدء باستخدام الإجراءات المُخزَّنة في لغة SQL.
مقدمة إلى الإجراءات المخزنة Stored Procedures
الإجراءات المخزنة في MySQL وفي العديد من أنظمة قواعد البيانات العلاقية الأخرى هي كائنات مُسمَّاة تحتوي على تعليمة واحدة أو أكثر تضعها وتنفّذها قاعدة البيانات عند استدعائها. يمكن للإجراء المخزن حفظ تعليمة مشتركة ضمن برنامج قابل لإعادة الاستخدام مثل استرجاع البيانات من قاعدة البيانات باستخدام المرشّحات Filters المُستخدَمة كثيرًا، حيث يمكنك مثلًا إنشاء إجراء مخزن لاسترجاع عملاء متجر الكتروني قدّموا طلبات خلال عدد معين من الأشهر. يمكن للإجراءات المخزنة أيضًا تمثيل البرامج الشاملة التي تصف منطق الأعمال المعقد للتطبيقات القوية في السيناريوهات الأكثر تعقيدًا.
يمكن أن تتضمن مجموعة التعليمات في إجراء مخزَّن تعليمات SQL شائعة مثل استعلامات SELECT أو INSERT التي تعيد البيانات أو تعالجها، ويمكن للإجراءات المخزنة الاستفادة مما يلي:
- المعاملات المُمرَّرة إلى الإجراء المخزن أو المُعادة منه
- المتغيرات المُصرَّح عنها لمعالجة البيانات المُسترجَعة من شيفرة الإجراء البرمجية مباشرةً
-
التعليمات الشرطية التي تسمح بتنفيذ أجزاء من شيفرة الإجراء المخزن البرمجية وفق شروط معينة مثل تعليمات
IFأوCASE -
الحلقات مثل
WHILEوLOOPوREPEATلتنفيذ أجزاء من الشيفرة البرمجية عدة مرات - تعليمات معالجة الأخطاء مثل إعادة رسائل الخطأ إلى مستخدمي قاعدة البيانات الذين يمكنهم الوصول إلى الإجراء.
- استدعاءات إجراءات مخزنة أخرى في قاعدة البيانات
ملاحظة: تسمح الصيغة الموسَّعة Extensive Syntax التي يدعمها MySQL بكتابة برامج قوية وحل المشكلات المعقدة باستخدام الإجراءات المخزنة مثل التحكم في تدفق البرنامج باستخدام التعليمات الشرطية واستخدام المتغيرات والحلقات ومعالجة الأخطاء المخصصة وغيرها من الاستخدامات ولكن سيغطي هذا المقال فقط الاستخدام الأساسي للإجراءات المخزنة مع تعليمات SQL المُضمَّنة في جسم الإجراء المخزَّن ومعاملات الدخل والخرج، إذ سيكون تنفيذ التعليمات الشرطية واستخدام المتغيرات والحلقات ومعالجة الأخطاء المُخصَّصة خارج نطاق هذا المقال، لذا يمكن مطالعة توثيق MySQL الرسمي لمعرفة المزيد حول الإجراءات المخزنة.
إذا استدعينا الإجراء باسمه، فسينفّذه محرّك قاعدة البيانات كما هو مُعرَّف تعليمةً تلو الأخرى. يجب أيضًا أن يكون لدى مستخدم قاعدة البيانات الأذونات المناسبة لتنفيذ الإجراء المُحدَّد، حيث توفر هذه الأذونات المطلوبة طبقة من الأمان، مما يمنع الوصول المباشر إلى قاعدة البيانات مع منح المستخدمين إمكانية الوصول إلى إجراءات فردية مضمونة الأمان لتنفيذها.
تُنفَّذ الإجراءات المخزَّنة على خادم قاعدة البيانات مباشرةً مع إجراء جميع العمليات الحسابية محليًا وإعادة النتائج إلى المستخدم المستدعي عند الانتهاء فقط. وإذا أردنا تغيير سلوك الإجراء، فيمكن تحديث الإجراء في قاعدة البيانات، وستلتقط التطبيقات التي تستخدمه الإصدار الجديد تلقائيًا، وسيبدأ جميع المستخدمين باستخدام الشيفرة البرمجية للإجراء الجديد مباشرةً دون الحاجة لتعديل تطبيقاتهم.
فيما يلي الهيكل العام لشيفرة SQL المستخدَمة لإنشاء إجراءٍ مخزَّن:
mysql> DELIMITER // mysql> CREATE PROCEDURE procedure_name(parameter_1, parameter_2, . . ., parameter_n) mysql> BEGIN mysql> instruction_1; mysql> instruction_2; mysql> . . . mysql> instruction_n; mysql> END // mysql> DELIMITER ;
التعليمتان الأولى والأخيرة في مقطع الشيفرة البرمجية السابق هما DELIMITER // و DELIMITER ;، حيث يستخدم MySQL رمز الفاصلة المنقوطة ; لتحديد التعليمات والإشارة إلى بدايتها ونهايتها. إذا نفّذنا تعليمات متعددة في طرفية MySQL مع الفصل بينها بفواصل منقوطة، سيكون التعامل معها كأنها أوامر منفصلة مع تنفيذ كل تعليمة تنفيذًا مستقلًا عن التعليمات الأخرى واحدة تلو الأخرى.
يمكن للإجراء المخزَّن أيضًا أن يتضمّن أوامر متعددة ستُنفَّذ تسلسليًا عند استدعائه، مما يشكّل صعوبة عند محاولة إخبار MySQL بإنشاء إجراء جديد، إذ سيرى محرّك قاعدة البيانات علامة الفاصلة المنقوطة في جسم الإجراء المُخزَّن ويعتقد أنه يجب أن يتوقف عن تنفيذ التعليمة، وبالتالي تكون التعليمة المقصودة في هذه الحالة هي الشيفرة البرمجية لإنشاء الإجراء بالكامل، وليس تعليمةً واحدة ضمن الإجراء نفسه، لذا قد يسيء MySQL تفسير ما نقصده.
يمكن التغلب على هذا القيد من خلال استخدام الأمر DELIMITER لتغيير هذا المحدِّد مؤقتًا من ; إلى // طوال مدة استدعاء التعليمة CREATE PROCEDURE، ثم ستُمرَّر جميع الفواصل المنقوطة الموجودة في جسم الإجراء المخزَّن إلى الخادم كما هي، ثم يتغير المحدِّد مرة أخرى إلى ; في آخر تعليمة DELIMITER ; بعد الانتهاء من الإجراء بالكامل.
يمثّل الاستدعاء CREATE PROCEDURE وبعده اسم الإجراء procedure_name في المثال السابق جوهر الشيفرة البرمجية الخاصة بإنشاء إجراء جديد، ويتبع اسم الإجراء قائمة اختيارية من المعاملات التي سيقبلها الإجراء. الجزء الأخير من الشيفرة البرمجية هو جسم الإجراء المضمَّن ضمن تعليمتي BEGIN و END، ويوجد في الداخل شيفرة الإجراء البرمجية، والتي يمكن أن تحتوي على تعليمة SQL واحدة مثل استعلام SELECT أو شيفرة برمجية أكثر تعقيدًا. ينتهي الأمر END بالرمز //، والذي يُعَد محدِّدًا مؤقتًا عوضًا عن الفاصلة المنقوطة النموذجية.
سننشئ في القسم التالي إجراءً مخزنًا بسيطًا بدون معاملات تتضمن استعلامًا واحدًا.
إنشاء إجراء مخزن بدون معاملات
سننشئ في هذا القسم أول إجراء مخزّن يغلِّف تعليمة SQL واحدة هي التعليمة SELECT لإعادة قائمة السيارات المملوكة المرتبة حسب نوعها وقيمتها بترتيب تنازلي.
نبدأ بتنفيذ التعليمة SELECT التي ستستخدمها كما يلي:
mysql> SELECT * FROM cars ORDER BY make, value DESC;
ستعيد قاعدة البيانات قائمة السيارات من الجدول cars مع ترتيبها حسب نوعها أولًا ثم حسب قيمتها بترتيب تنازلي ضمن نوع السيارة الواحد كما يلي:
الخرج +---------+---------------+------+-----------+ | make | model | year | value | +---------+---------------+------+-----------+ | Ferrari | SF90 Stradale | 2020 | 627000.00 | | Ferrari | F8 Tributo | 2019 | 375000.00 | | Ferrari | 812 Superfast | 2017 | 335300.00 | | Ferrari | GTC4Lusso | 2016 | 268000.00 | | Ferrari | 488 GTB | 2015 | 254750.00 | | Porsche | 911 GT3 | 2020 | 169700.00 | | Porsche | Cayman GT4 | 2018 | 118000.00 | | Porsche | Panamera | 2022 | 113200.00 | | Porsche | 718 Boxster | 2017 | 48880.00 | | Porsche | Macan | 2019 | 27400.00 | +---------+---------------+------+-----------+ 10 rows in set (0.00 sec)
نلاحظ ظهور سيارة الفيراري الأعلى قيمة في أعلى القائمة، وظهور سيارة البورش الأدنى قيمة في الأسفل.
لنفترض استخدام هذا الاستعلام بصورة متكررة في تطبيقات متعددة أو سيستخدمه مستخدمون متعددون ونريد التأكّد من أن الجميع سيستخدمون الطريقة نفسها لترتيب النتائج، لذا يجب إنشاء إجراء مخزن يحفظ تعليمة هذا الاستعلام ضمن إجراء مُسمَّى قابل لإعادة الاستخدام من خلال تنفيذ جزء الشيفرة البرمجية التالي:
mysql> DELIMITER // mysql> CREATE PROCEDURE get_all_cars() mysql> BEGIN mysql> SELECT * FROM cars ORDER BY make, value DESC; mysql> END // mysql> DELIMITER ;
نلاحظ أن الأمرين الأول DELIMITER // والأخير DELIMITER ; يخبران MySQL بالتوقف عن التعامل مع محرف الفاصلة المنقوطة بوصفه محدِّدًا للتعليمات طوال مدة إنشاء الإجراء كما هو موضّح في القسم السابق.
يتبع أمر SQL الذي هو CREATE PROCEDURE اسم الإجراء get_all_cars الذي يمكن تعريفه لوصف ما يفعله الإجراء، ثم يوجد زوج من الأقواس () يمكننا إضافة معاملات ضمنه، ولكن لا يستخدم هذا الإجراء معاملات في مثالنا، لذا ستكون الأقواس فارغة، ثم تُكتَب تعليمة SELECT نفسها المُستخدَمة سابقًا بين الأمرين BEGIN و END اللذين يحددان بداية ونهاية كتلة شيفرة الإجراء البرمجية.
ملاحظة: قد يظهر الخطأ ERROR 1044 (42000): Access denied for user 'user'@'localhost' to database 'procedures' عند تنفيذ الأمر CREATE PROCEDURE بناءً على أذونات مستخدم MySQL الخاص بنا. يمكن منح الأذونات اللازمة لإنشاء وتنفيذ الإجراءات المخزنة للمستخدم من خلال تسجيل الدخول إلى MySQL كمستخدم جذر وتنفيذ الأوامر التالية وتغيير اسم مستخدم MySQL والمضيف حسب الحاجة:
mysql> GRANT CREATE ROUTINE, ALTER ROUTINE, EXECUTE on *.* TO 'user'@'localhost'; mysql> FLUSH PRIVILEGES;
نحدّث أذونات المستخدم، ثم نسجّل الخروج كمستخدم جذر، ونسجّل الدخول مرة أخرى كمستخدم عادي، ثم نعيد تشغيل تعليمة CREATE PROCEDURE.
يمكن معرفة المزيد حول تطبيق الأذونات الخاصة بالإجراءات المخزنة لمستخدمي قاعدة البيانات في توثيق MySQL الرسمي الخاص بصلاحيات MySQL والبرامج المُخزَّنة.
ستستجيب قاعدة البيانات برسالة النجاح التالية:
الخرج Query OK, 0 rows affected (0.02 sec)
أصبح الإجراء get_all_cars الآن محفوظًا في قاعدة البيانات، وستُنفَّذ التعليمة المحفوظة كما هي عند استدعائه. يمكن تنفيذ الإجراءات المخزَّنة المحفوظة من خلال استخدام أمر SQL الذي هو CALL متبوعًا باسم الإجراء. نجرّب الآن تشغيل الإجراء الذي أنشأناه كما يلي:
mysql> CALL get_all_cars;
نحتاج اسم الإجراء get_all_cars فقط لاستخدام هذا الإجراء، إذ لم تَعُد بحاجة إلى كتابة أيّ جزء من تعليمة SELECT التي استخدمناها سابقًا يدويًا، وستعرض قاعدة البيانات النتائج مثل خرج التعليمة SELECT التي نفّذتها سابقًا كما يلي:
الخرج +---------+---------------+------+-----------+ | make | model | year | value | +---------+---------------+------+-----------+ | Ferrari | SF90 Stradale | 2020 | 627000.00 | | Ferrari | F8 Tributo | 2019 | 375000.00 | | Ferrari | 812 Superfast | 2017 | 335300.00 | | Ferrari | GTC4Lusso | 2016 | 268000.00 | | Ferrari | 488 GTB | 2015 | 254750.00 | | Porsche | 911 GT3 | 2020 | 169700.00 | | Porsche | Cayman GT4 | 2018 | 118000.00 | | Porsche | Panamera | 2022 | 113200.00 | | Porsche | 718 Boxster | 2017 | 48880.00 | | Porsche | Macan | 2019 | 27400.00 | +---------+---------------+------+-----------+ 10 rows in set (0.00 sec) Query OK, 0 rows affected (0.00 sec)
نجحنا في إنشاء إجراء مخزَّن بدون معاملات، حيث يعيد هذا الإجراء جميع السيارات من الجدول cars مرتبةً بطريقة معينة، ويمكن استخدام هذا الإجراء في تطبيقات متعددة.
سننشئ في القسم التالي إجراء يقبل المعاملات لتغيير سلوك الإجراء وفقًا لدخل المستخدم.
إنشاء إجراء مخزن مع معامل دخل
سنضمِّن في هذا القسم معاملات دخل في تعريف الإجراء المخزَّن للسماح للمستخدمين الذين ينفّذون الإجراء بتمرير البيانات إليه، فمثلًا يمكن للمستخدمين توفير مرشّحات للاستعلام.
يسترجع الإجراء المخزن get_all_cars الذي أنشأناه مسبقًا جميع السيارات من الجدول cars المصنعة في جميع سنوات التصنيع، ولننشئ الآن إجراء آخر للعثور على السيارات المُصنَّعة في سنة معينة، حيث سنعرِّف معاملًا في تعريف الإجراء من خلال تشغيل الشيفرة البرمجية التالية:
mysql> DELIMITER // mysql> CREATE PROCEDURE get_cars_by_year( mysql> IN year_filter int mysql> ) mysql> BEGIN mysql> SELECT * FROM cars WHERE year = year_filter ORDER BY make, value DESC; mysql> END // mysql> DELIMITER ;
توجد العديد من التغييرات على شيفرة إنشاء الإجراء مقارنة بالشيفرة المستخدمة في القسم السابق، حيث تغيّر الاسم ليكون get_cars_by_year ليمثل عمل الإجراء، وهو استرجاع السيارات بناءً على سنة إصدارها. كما أصبحت الأقواس الفارغة سابقًا محتوية على تعريف معامل واحد هو IN year_filter int، حيث تخبر الكلمة المفتاحية IN قاعدة البيانات بأن المستخدم المستدعِي سيمرّر المعامل إلى الإجراء. يُعَد year_filter اسمًا عشوائيًا للمعامل، حيث سنستخدمه للإشارة إلى المعامل في شيفرة الإجراء البرمجية، و int هو نوع البيانات، حيث نمثّل سنة التصنيع بقيمة عددية. يظهر المعامل year_filter المُعرَّف بعد اسم الإجراء في تعليمة SELECT ضمن التعليمة WHERE year = year_filter، مما يؤدي إلى ترشيح الجدول cars وفقًا لسنة التصنيع، وستستجيب قاعدة البيانات برسالة النجاح التالية:
الخرج Query OK, 0 rows affected (0.02 sec)
نفّذ الإجراء بدون تمرير أي معاملات إليه كما فعلنا سابقًا:
mysql> CALL get_cars_by_year;
وستعيد قاعدة بيانات MySQL رسالة الخطأ التالية:
رسالة خطأ ERROR 1318 (42000): Incorrect number of arguments for PROCEDURE procedures.get_cars_by_year; expected 1, got 0
يتوقع الإجراء المخزن هذه المرة توفير معامل له، ولكن لم نقدّم له أيّ معامل، لذا يمكننا استدعاء إجراء مخزَّن مع معاملات من خلال توفير قيم المعاملات بين قوسين بنفس الترتيب الذي يتوقعه الإجراء. سننفّذ الإجراء التالي لاسترجاع السيارات المصنعة في عام 2017:
mysql> CALL get_cars_by_year(2017);
سيُنفَّذ الإجراء المستدعَى الآن تنفيذًا صحيحًا ويعيد قائمة السيارات من عام 2017، وسينتج الخرج التالي:
الخرج +---------+---------------+------+-----------+ | make | model | year | value | +---------+---------------+------+-----------+ | Ferrari | 812 Superfast | 2017 | 335300.00 | | Porsche | 718 Boxster | 2017 | 48880.00 | +---------+---------------+------+-----------+ 2 rows in set (0.00 sec) Query OK, 0 rows affected (0.00 sec)
تعلّمنا في المثال السابق كيفية تمرير معاملات الدخل إلى الإجراءات المخزنة واستخدامها في الاستعلامات ضمن الإجراء لتوفير خيارات الترشيح، وسنستخدم في القسم التالي معاملات الخرج لإنشاء إجراءات تعيد قيمًا مختلفة متعددة في تنفيذ واحد.
إنشاء إجراء مخزن مع معاملات دخل وخرج
في الإجراءات المخزَّنة التي أنشأناها في المثالين السابقين استدعينا تعليمة SELECT للحصول على مجموعة نتائج، ولكن قد نحتاج في بعض الحالات إلى إجراء مخزّن يعيد قيمًا مختلفة متعددة مع بعضها البعض بدل إعادة مجموعة نتائج واحدة لاستعلام فردي.
لنفترض أننا تريد إنشاء إجراء يوفّر معلومات عن السيارات الصادرة في سنة معينة بما في ذلك كمية السيارات في المجموعة وقيمتها السوقية -الحد الأدنى والحد الأقصى والمتوسط- من خلال استخدام معاملات OUT عند إنشاء إجراء مخزّن جديد.
تحتوي معاملات OUT مثل معاملات IN على أسماء وأنواع بيانات مرتبطة بها، ولكن يمكن ملء هذه المعاملات بالبيانات باستخدام الإجراء المخزن بدل تمرير البيانات إلى الإجراء المخزن لإعادة القيم إلى المستخدم المستدعِي.
لننشئ الآن الإجراء get_car_stats_by_year التالي الذي سيعيد بيانات موجزة عن السيارات من سنة إنتاج معينة باستخدام معاملات خرج:
mysql> DELIMITER // mysql> CREATE PROCEDURE get_car_stats_by_year( mysql> IN year_filter int, mysql> OUT cars_number int, mysql> OUT min_value decimal(10, 2), mysql> OUT avg_value decimal(10, 2), mysql> OUT max_value decimal(10, 2) mysql> ) mysql> BEGIN mysql> SELECT COUNT(*), MIN(value), AVG(value), MAX(value) mysql> INTO cars_number, min_value, avg_value, max_value mysql> FROM cars mysql> WHERE year = year_filter ORDER BY make, value DESC; mysql> END // mysql> DELIMITER ;
استخدمنا معامل IN الذي هو year_filter لترشيح السيارات حسب سنة الإصدار، وعرّفنا أربعة معاملات OUT ضمن كتلة الأقواس. نمثّل المعامل cars_number بنوع البيانات int وسنستخدمه لإعادة عدد السيارات في المجموعة، وتمثّل المعاملات min_value و avg_value و max_value القيمة السوقية وتُعرَّف باستخدام نوع البيانات decimal(10, 2) مثل العمود value في الجدول cars، وتُستخدَم هذه المعاملات لإعادة معلومات حول أرخص وأغلى السيارات من المجموعة، بالإضافة إلى متوسط أسعار جميع السيارات المطابقة.
تستعلم التعليمة SELECT عن أربع قيم من الجدول cars باستخدام دوال SQL الرياضية وهي: COUNT للحصول على العدد الإجمالي للسيارات، و MIN و AVG و MAX للحصول على القيمة الدنيا والمتوسط والقيمة العليا من العمود value. يمكن مطالعة مقال كيفية استخدام التعابير الرياضية والدوال التجميعية في لغة SQL لمعرفة المزيد حول استخدام الدوال الرياضية في لغة SQL.
يمكننا إخبار قاعدة البيانات بأننا نريد تخزين نتائج هذا الاستعلام في معاملات الخرج للإجراء المخزَّن من خلال تقديم كلمة مفتاحية جديدة هي INTO، ونضع بعدها أسماء أربعة معاملات إجراء تقابل البيانات المُسترجَعة، وبالتالي سيحفظ MySQL قيمة COUNT(*) في المعامل cars_number، ونتيجة MIN(value) في المعامل min_value ...إلخ.
تؤكد قاعدة البيانات إنشاء الإجراء بنجاح كما يلي:
الخرج Query OK, 0 rows affected (0.02 sec)
لنشغّل الآن الإجراء الجديد من خلال تنفيذ الأمر التالي:
mysql> CALL get_car_stats_by_year(2017, @number, @min, @avg, @max);
تبدأ المعاملات الأربعة الجديدة بالإشارة @، وهي أسماء متغيرات محلية في طرفية MySQL يمكنك استخدامها لتخزين البيانات مؤقتًا، وإذا مرّرنا هذه المعاملات إلى الإجراء المخزَّن الذي أنشأناه، فسيدرج الإجراءُ قيمًا في هذه المتغيرات.
وستستجيب قاعدة البيانات بالخرج التالي:
الخرج Query OK, 1 row affected (0.00 sec)
يختلف هذا الخرج عن السلوك السابق، حيث كانت النتائج تُعرَض على الشاشة مباشرةً، لأن نتائج الإجراء المخزَّن محفوظة في معاملات الخرج دون إعادتها كنتيجة للاستعلام، ولكن يمكننا الوصول إلى النتائج من خلال استخدام التعليمة SELECT مباشرةً في صدفة MySQL كما يلي:
mysql> SELECT @number, @min, @avg, @max;
نحدّد قيمًا من المتغيرات المحلية باستخدام الاستعلام السابق، ولا نستدعي الإجراء مرة أخرى، ويحفظ الإجراء المخزَّن نتائجه في تلك المتغيرات، وستبقى البيانات متاحة حتى قطع الاتصال بالصدفة.
ملاحظة: يمكن مطالعة على قسم المتغيرات التي يعرِّفها المستخدم في توثيق MySQL الرسمي لمعرفة المزيد حول استخدام هذه المتغيرات. ستختلف طرق الوصول إلى البيانات المُعادة من الإجراءات المخزنة في لغات البرمجة وأطر العمل المختلفة عند استخدامها في تطوير التطبيقات، لذا يتوجب الاطلاع على توثيق اللغة وإطار العمل لمعرفة الطريقة المناسبة.
يعرض الخرج قيم المتغيرات التي استعلمنا عنها كما يلي:
الخرج +---------+----------+-----------+-----------+ | @number | @min | @avg | @max | +---------+----------+-----------+-----------+ | 2 | 48880.00 | 192090.00 | 335300.00 | +---------+----------+-----------+-----------+ 1 row in set (0.00 sec)
تتوافق القيم مع عدد السيارات المُصنَّعة في عام 2017، والقيمة السوقية الدنيا والمتوسطة والعليا للسيارات في هذه السنة من الإنتاج.
تعلّمنا في المثال السابق كيفية استخدام معاملات الخرج لإعادة قيم مختلفة متعددة من الإجراء المخزن لاستخدامها لاحقًا، وسنتعلّم في القسم التالي كيفية إزالة الإجراءات التي أنشأناها.
إزالة الإجراءات المخزنة
سنزيل في هذا القسم الإجراءات المخزنة الموجودة في قاعدة البيانات، فقد لا تكون هناك حاجة إلى الإجراء الذي أنشأناه في بعض الأحيان، أو قد نرغب في تغيير طريقة عمل الإجراء، حيث لا يسمح MySQL بتغيير تعريف الإجراء بعد إنشائه، فالطريقة الوحيدة لذلك هي إزالة الإجراء أولًا وإعادة إنشائه مرة أخرى مع التغييرات المطلوبة.
لنحذف الآن الإجراء الأخير get_car_stats_by_year باستخدام التعليمة DROP PROCEDURE كما يلي:
mysql> DROP PROCEDURE get_car_stats_by_year;
وستؤكد قاعدة البيانات حذف الإجراء برسالة النجاح التالية:
الخرج Query OK, 0 rows affected (0.02 sec)
يمكن التحقق من حذف الإجراء من خلال محاولة استدعائه باستخدام الأمر التالي:
mysql> CALL get_car_stats_by_year(2017, @number, @min, @avg, @max);
سنرى رسالة خطأ تفيد بأن الإجراء غير موجود في قاعدة البيانات كما يلي:
رسالة خطأ ERROR 1305 (42000): PROCEDURE procedures.get_car_stats_by_year does not exist
تعلمنا في هذا القسم كيفية حذف الإجراءات المخزنة الموجودة في قاعدة البيانات.
الخلاصة
تعلمنا في هذا المقال ما هي الإجراءات المخزنة وأنواعها المختلفة وكيفية استخدامها في MySQL لحفظ البيانات القابلة لإعادة الاستخدام في إجراءات مسمَّاة وتنفيذها لاحقًا، يمكن استخدام الإجراءات المخزنة لإنشاء برامج قابلة لإعادة الاستخدام، وتوحيد طرق الوصول إلى البيانات عبر تطبيقات متعددة، بالإضافة إلى تنفيذ سلوكيات معقدة تتجاوز الإمكانيات التي توفرها استعلامات SQL الفردية. غطى هذا المقال فقط أساسيات استخدام الإجراءات المخزنة، لذا لمزيد من المعلومات ننصح بالاطلاع على توثيق MySQL للإجراءات المخزنة لمعرفة مزيد من التفاصيل.
ترجمة -وبتصرف- للمقال How To Use Stored Procedures in MySQL لصاحبَيه Mateusz Papiernik و Rachel Lee.
أفضل التعليقات
لا توجد أية تعليقات بعد
انضم إلى النقاش
يمكنك أن تنشر الآن وتسجل لاحقًا. إذا كان لديك حساب، فسجل الدخول الآن لتنشر باسم حسابك.