آشنایی با روندهای ذخیره‌شده MySQL

روندهای ذخیره‌شده MySQL موجب‌ گروه‌بندی وظایف چندگانه در یک قالب واحد شده و آن را برای اجرا در آینده در سرور ذخیره می‌کنند. در نتیجه استفاده از این روندها، مدیریت پایگاه داده‌ ساده‌سازی می‌شود و همچنین ترافیک شبکه کاهش می‌یابد. به عنوان مثال، ارائه یک کوئری به سرور MySQL موجب پردازش کوئری شده و نتایج را برمی‌گرداند. استفاده از روندهای ذخیره‌شده باعث ذخیره کوئری‌ها در سرور شده و این کوئری‌ها می توانند بعداً اجرا شوند.

در این آموزش، نحوه ساخت، لیست‌کردن، جایگزینی و حذف روندهای ذخیره‌شده MySQL را فراخواهید گرفت.

پیش‌نیازها

• سرور MySQL و نصب MySQL Workbench
• یک حساب کاربری MySQL با دسترسی‌های روت

روندهای ذخیره شده در MySQL چه هستند؟

روندهای ذخیره‌شده MySQL یک سری عبارت‌های «از پیش کامپایل شده» SQL هستند که در یک پایگاه داده ذخیره می‌شوند. این زیرروال‌ها یا Subroutine ها شامل یک نام، یک لیست پارامتر و عبارت‌های SQL می‌شوند.

تمام سیستم‌های پایگاه داده منطقی از روندهای ذخیره‌شده پشتیبانی می‌کنند و نیازی به هیچ‌گونه بسته‌های محیطی اضافی نخواهند داشت.

چگونه از روندهای ذخیره‌شده استفاده کنیم؟

به منظور فراخوانی روندهای ذخیره شده، می‌توانید از دستور CALL یا سایر روندهای ذخیره‌شده استفاده کنید. وقتی برای اولین بار یک روند ذخیره‌شده را فراخوانی می‌کنید، MySQL در پایگاه داده به دنبال آن می‌گردد. سپس کد را کامپایل کرده، آن را در حافظه نهان جای می‌دهد و آن را اجرا می‌کند.

در دفعات بعدی، روند ذخیره‌شده از داخل حافظه نهان اجرا می‌شوند و در نتیجه، این روندها برای وظایف تکرارشونده بسیار مفید خواهند بود.

روندهای ذخیره‌شده از پارامترها برای اختصاص مقادیر و سفارشی‌کردن نتایج استفاده می‌کنند. پارامترها برای مشخص‌های ستون‌ها در جدولی به کار می‌روند که کوئری در آن اجرا شده و نتایج را برمی‌گرداند.

روندهای ذخیره‌شده همچنین می‌توانند شامل کنترل‌گرهای IF، CASE و LOOP باشند و به صورت دوره‌ای کدها را به کار گیرند.

ساخت روندهای ذخیره‌شده

ایجاد روندهای ذخیره‌شده MySQL به دو روش صورت می‌گیرد.

۱) استفاده از MySQL Shell

از قالب زیر برای ساخت یک روند ذخیره شده در MySQL استفاده کنید.

DELIMITER //

CREATE PROCEDURE procedure_name ( IN | OUT | INOUT parameter_name parameter_datatype (length), … )

BEGIN

SQL statements

END //

DELIMITER ;

این قالب به صورت پیش‌فرض همراه با پایگاه داده مورد استفاده می‌آید. با این وجود، شما می‌توانید از این قالب  برای یک پایگاه داده دیگر بهره ببرید. این کار با مشخص کردن نام پایگاه داده به صورت database_name.procedure_name ممکن خواهد بود.

در اینجا، در اولین آرگومان DELIMITER، delimiter پیش‌فرض به صورت // تنظیم شده است. این در حالی است که آخرین تنظیم آرگومان DELIMITER نقطه‌‌ویرگول یا ; است. برای استفاده از عبارت‌های چندگانه می توانید از delimiter های دیگر مانند $$ استفاده کنید.

عنوان «روند» بعد از آرگومان CREATE PROCEDURE می‌آید. پس از عنوان روند، از پرانتزها برای مشخص‌کردن پارامترهای مورد استفاده در این روند، نام پارامتر، نوع داده و طول داده استفاده می‌شود. هر کدام از این پارامترها می‌بایست با یک علامت کاما از یکدیگر جدا شوند.

وضعیت‌های پارامترها به صورت زیرند:

• IN – برای استفاده از پارامتر به عنوان ورودی. در این وضعیت، کوئری یک آرگومنان به روند ذخیره شده وارد می‌کند. همچنین مقدار پارامتر همیشه به صورت محافظت‌شده خواهد بود.
• OUT – برای استفاده از پارامتر به عنوان خروجی. امکان تغییر مقدار در درون روند ذخیره‌شده وجود خواهد داشت و مقدار جدید به برنامه فراخوانی‌کننده برگشت داده می‌شود.
• INOUT – ترکیبی از پارامترهای IN و OUT. برنامه فراخوانی‌کننده آرگومان را وارد می‌کند و «روند» می‌تواند پارامتر INOUT را ویرایش کرده و مقدار جدید را به برنامه بازگشت دهد.

به عنوان مثال:

با فراخوانی روند ذخیره‌شده، آن را اجرا نمایید.

CALL procedure_name;

در نتیجه، کوئری نتایج را برای روند ذخیره شده برگشت می‌دهد.

۲) استفاده از MySQL Workbench

راه دیگر برای ایجاد یک روند ذخیره‌شده، کاربرد MySQL Workbench Wizard خواهد بود. همه‌چیز برای این منظور ساده و ابتدایی خواهد بود و شما نیازی به نگرانی در مورد جانمایی delimiter ها یا فرمت‌ها نخواهید داشت.

مراحل زیر را دنبال کنید:

گام ۱) در پنجره ناوبری MySQL Workbench، روی گزینه “Stored Procedures” کلیک کرده و سپس آیتم Create Stored Procedure… را انتخاب کنید.

گام ۲) نام روند ذخیره‌شده را وارد کنید و کد را در فاصله بلوک BEGIN … END وارد کنید.

گام ۳) بررسی دوباره کد و کلیک بر روی دکمه Apply

گام ۴) تأیید اجرا با کلیک بر Apply و ساخت روند با کلیک بر Finish

گام ۵) اجرای روند برای بررسی نحوه کارآیی آن. یک سربرگ جدید SQL برای اجرای کوئری‌ها ایجاد کنید.

گام ۶) در سربرگ SQL، روند را فراخوانی کرده و روی Execute کلیک کنید.

اگر خطایی در راه نباشد، MySQL روند ذخیره‌شده را اجرا کرده و نتایج را در خروجی نشان می‌دهد.

تهیه لیست از روندهای ذخیره‌شده

سه روش برای مشاهده لیست روندهای ذخیره‌شده وجود دارد.

۱) استفاده از MySQL Shell

برای دریافت لیست تمام روندهای ذخیره‌شده‌ای که به آنها دسترسی دارید، می‌توانید از قالب زیر استفاده نمایید.

SHOW PROCEDURE STATUS [LIKE 'pattern' | WHERE search_condition]

عبارت SHOW PROCEDURE STATUS باعث ایجاد یک خروجی نسبتاً طولانی می‌شود.  این عبارت عناوین و مشخصات روندهای ذخیره‌شده‌ای را که دسترسی به آنها وجود دارد، نمایش خواهد داد.

با رفتن به ادامه خروجی، می‌توانید کل روندهای ذخیره‌شده کنونی سرور را مشاهده نمایید.

آرگومان LIKE می‌تواند روندهای ذخیره‌شده‌ای را پیدا کند که شامل یک عبارت خاص در عنوانشان باشند. همچنین با استفاده از علامت % می‌تواندید تعداد کاراکترها و از جمله صفر را وارد کنید.

به عنوان مثال:

آرگومان WHERE به شما اجازه می‌دهد که تنه در میان لیست روندهای ذخیره شده یک پایگاه داده جستجو کنید.

به عنوان مثال:

در این مثال، عبارت فقط باعث برگشت روندهای ذخیره‌شده برای پایگاه داده ’customer_list’ می‌شود.

۲) استفاده از Data Dictionary

پایگاه داده information_schema شامل یک جدول با عنوان routines است. این جدول حاوی اطلاعاتی در مورد روندهای ذخیره‌شده و توابع مرتبط با تمام پایگاه‌های داده موجود در سرور MySQL است.

به منظور مشاهده تمام روندهای ذخیره‌شده مرتبط با یک پایگاه داده خاص از قالب زیر استفاده نمایید.

SELECT

routine_name

FROM

information_schema.routines

WHERE

routine_type = 'PROCEDURE'

AND routine_schema = 'database_name';

۳) استفاده از MySQL Workbench

برای مشاهده روندهای ذخیره‌شده از طریف رابط کاربری، از MySQL Workbench استفاده کنید. برای این منظور، مراحل زیر پیش روی شما هستند:

گام ۱) در بخش Navigator، روی پایگاه داده‌ای که می‌خواهید استفاده کنید، دو بار کلیک کنید.

گام ۲) لیست بازشونده Stored Procedures را باز کنید.

این آیتم تمام روندهای ذخیره شده برای پایگاه داده کنونی را نمایش می‌دهد.

تغییر روند ذخیره‌شده

تغییر روند ذخیره‌شده به معنای اصلاح مشخصات یک روند است. باید گفت که هیچ‌گونه عبارت کاربردی در MySQL برای اصلاح پارامترها در بدنه یک روند ذخیره شده وجود ندارد. برای تغییر پارامترها یا بدنه روند، باید روند ذخیره شده را حذف و یک روند جدید ایجاد کرد.

تغییر روند ذخیره‌شده به دو طریق زیر صورت می‌گیرد:

۱) استفاده از MySQL Shell

تغییر مشخصات یک روند با استفاده از عبارت ALTER PROCEDURE انجام می‌پذیرد. به عنوان نمونه، می‌توانیم به روندی که قبلاً ایجاد کرده بودیم، یک کامنت اضافه کنیم. قالب کار به صورت زیر خواهد بود:

ALTER PROCEDURE procedure_name

COMMENT 'Insert comment here';

۲) استفاده از MySQL Workbench

رابط کاربری MySQL Workbench این امکان را به کاربران می‌دهد که با اضافه‌کردن پارامترها یا اصلاح کد، یک روند ذخیره‌شده را تغییر دهند. MySQL Workbench پس از دریافت تغییرات توسط کاربر، روند ذخیره‌شده کنونی را حذف کرده و یک روند جدید ایجاد می‌کند.

برای این منظور، مراحل زیر را دنبال کنید.

گام ۱) در بخش Navigator، روی روند ذخیره‌شده موردنظرتان راست‌کلیک کنید. سپس باید آیتم Alter Stored procedure… را انتخاب نمایید.

گام ۲) وقتی سربرگ باز می‌شود، تغییرات موردنظر را در روند ذخیره‌شده ایجاد کنید و سپس دکمه Apply را بزنید.

گام ۳) در نتیجه، یک پنجره بررسی SQL Script باز می‌شود که پیشرفت فرآیند، یعنی حذف روند ذخیره‌شده فعلی و ایجاد یک روند جدید را نشان می‌دهد.

نهایتاً روی دکمه Apply و سپس Finish در پنجره بعدی کلیک کنید تا اسکریپت اجرا شود.

حذف روند ذخیره‌شده

برای حذف (Drop) یک روند داریم:

۱) استفاده از MySQL Shell

حذف یک روند ذخیره‌شده از سرور با کمک عبارت DROP PROCEDURE صورت می‌گیرد.

قالب پایه به شکل زیر است:

DROP PROCEDURE [IF EXISTS] stored_procedure_name;

پارامتر IF EXISTS در صورت روند ذخیره‌شده در سرور وجود داشته باشد، آن را حذف می‌کند. عنوان روند ذخیره‌شده به جای stored_procedure_name قرار می‌گیرد.

به عنوان مثال:

از ‌آنجایی که هیچ روند ذخیره شده‌ای با عنوان “test” در سرور وجود ندارد، خروجی می‌گوید که هیچ ردیفی تحت‌تأثیر قرار نگرفته و روند ذخیره‌شده موردنظر وجود ندارد.

حذف روندی که در سرور وجود نداشته باشد و در نبود پارامتر IF EXISTS، منجر به یک پیغام خطا خواهد شد.

۲) استفاده از MySQL Workbench

برای حذف یک روند ذخیره شده با MySQL Workbench، مراحل زیر را دنبال کنید:

گام ۱) آیتم “Stored Procedures” را در بخش Navigator باز کنید. روی روند ذخیره شده‌ای که می‌خواهید آن را پاک کنید، راست‌کلیک رده و در منو، گزینه “Drop Stored Procedure…” را انتخاب نمایید.

گام ۲) در پنجره تأیید، روی دکمه Drop Now کلیک کنید تا «روند ذخیره‌شده» حذف شود.

در نتیجه، روند به صورت دائمی از سرور شما حذف خواهد شد.

مزایا و معایب روندهای ذخیره‌شده MySQL

روندهای ذخیره با توجه به اینکه برای مقاصد خاصی درنظر گرفته می‌شود،‌دارای برخی معایب و مزایا هستند. در ادامه به برخی از این موارد اشاره می‌کنیم.

مزایای استفاده از روندهای ذخیره‌شده

۱) کاهش ترافیک شبکه

روندهای ذخیهر باعث می‌شوند که ترافیک شبکه بین اپلیکیشن‌ها و سرورها کمتر شود. دلیل این موضوع حفظ تمام منطق برنامه‌ریزی بر روی سرور است. در این حالت، به جای ارسال نتایج کوئری چندگانه در سراسر شبکه، اپلیکیشن‌ها فقط عنوان روند و ورودی پارامتر را منتقل می‌کنند.

۲) ارتقای ایمنی

این امکان برای مدیر پایگاه داده به‌وجود می‌آید که دسترسی اپلیکیشن‌ها را تنها به روندهای ذخیره‌شده خاص فعال کند و آنها دسترسی مستقیم به جداول نداشته باشند. از آنجایی که پارامترهای ورودی به عنوان «مقادیر» و نه «کد اجرایی» درنظر گرفته می‌شوند، روندهای ذخیره‌شده موجب جلوگیری نفوذ اسکریپت نیز می‌گردند.

۳) مرکزیت کسب‌وکار

روندهای ذخیره‌شده موجب فشرده‌سازی منطق کسب‌کار مورد استفاده توسط اپلیکیشن‌های چندگانه می‌شود. این موضوع از تکرار منطق‌ها در اپلیکیشن‌های مختلف جلوگیری کرده و پایداری بیشتری برای پایگاه داده ایجاد می‌کند.

معایب استفاده از روندهای ذخیره‌شده

۱) میزان استفاده از منابع

کاربرد روندهای ذخیره‌شده متعدد و عملیات منطقی موجب می‌شود که در هر بار ارتباط، بخش زیادی از حافظه و پردازنده درگیر شوند.

۲) عدم قابلیت جابجایی

انتقال یک روند ذخیره‌شده که در یک زبان خاص نوشته شده، کار ساده‌ای ندارد. همچنین یک روند ذخیره‌شده بستگی به کاربر و پایگاه داده خاص خواهد داشت.

۳) آزمایش و عیب‌یابی

MySQL هیچ‌گونه ابزاری برای آزمایش و عیب‌یابی روندهای ذخیره‌شده درنظر نگرفته است. بنابراین، پیدا کردن نقص آنها بسیار مشکل خواهد بود. توسعه و نگهداری روندهای ذخیره‌شده نیازمند دانش قابل‌توجهی است و این موضوعی چالش‌برانگیز برای توسعه‌دهندگان جوان  درنظر گرفته می‌شود.

جمع‌بندی

در این مطلب با مفهوم روندهای ذخیره‌شده و نحوه استفاده از‌ آنها آشنا شدیم. همچنین نحوه ساخت، اصلاح، نمایش تمام روندها و حذف روندهای بلا استفاده مرور شد. امیدواریم که این مطلب از وبلاگ آریانت نیز مورد توجه شما قرار گرفته باشد.