مقدمه
به طور معمول، هنگام کار با یک پایگاه داده رابطه‌ای، درخواست‌های زبان پرس و جو ساختاریافته (SQL) را برای بازیابی یا دستکاری داده‌ها، مانند SELECT، INSERT، UPDATE یا DELETE، مستقیماً از داخل کد برنامه خود صادر می‌کنید. این عبارات به طور مستقیم روی جداول پایگاه داده زیربنایی کار می کنند و آنها را دستکاری می کنند. اگر عبارات یکسان یا گروهی از عبارات در چندین برنامه کاربردی استفاده شود که به یک پایگاه داده دسترسی دارند، اغلب در برنامه های کاربردی تکی تکرار می شوند.
MySQL، مشابه بسیاری از سیستم های مدیریت پایگاه داده رابطه ای دیگر، از استفاده از رویه های ذخیره شده پشتیبانی می کند. رویه‌های ذخیره‌شده به گروه‌بندی یک یا چند عبارت SQL برای استفاده مجدد تحت یک نام مشترک کمک می‌کنند و منطق تجاری مشترک را در خود پایگاه داده جمع‌بندی می‌کنند. چنین رویه‌ای را می‌توان از برنامه‌ای فراخوانی کرد که به پایگاه داده برای بازیابی یا دستکاری داده‌ها به روشی ثابت دسترسی دارد.
با استفاده از رویه‌های ذخیره‌شده، می‌توانید روال‌های قابل استفاده مجدد را برای کارهای رایج ایجاد کنید تا در چندین برنامه مورد استفاده قرار گیرند، اعتبار داده‌ها را تأیید کنید، یا با محدود کردن کاربران پایگاه داده از دسترسی مستقیم به جداول زیربنایی و صدور درخواست‌های دلخواه، یک لایه اضافی از امنیت دسترسی به داده ارائه دهید.
در این آموزش، شما یاد خواهید گرفت که رویه های ذخیره شده چیست و چگونه رویه های ذخیره شده اولیه ایجاد کنید که داده ها را برمی گرداند و از پارامترهای ورودی و خروجی استفاده می کند.
پیش نیازها
برای پیروی از این راهنما، به رایانه ای نیاز دارید که یک سیستم مدیریت پایگاه داده رابطه ای مبتنی بر SQL (RDBMS) را اجرا کند. دستورالعمل ها و مثال های موجود در این راهنما با استفاده از محیط زیر تأیید شدند:
- سروری که اوبونتو 20.04 را اجرا می کند، با یک کاربر غیر ریشه با امتیازات مدیریتی و فایروال پیکربندی شده با UFW
- MySQL روی سرور نصب و ایمن شده است
- آشنایی اولیه با اجرای کوئری های SELECT برای بازیابی داده ها از پایگاه داده
توجه: لطفاً توجه داشته باشید که بسیاری از RDBMS ها از پیاده سازی منحصر به فرد خود از SQL استفاده می کنند و نحو رویه های ذخیره شده بخشی از استاندارد رسمی SQL نیست. اگرچه دستورات ذکر شده در این آموزش ممکن است در RDBMS های دیگر کار کنند، رویه های ذخیره شده مختص پایگاه داده هستند و بنابراین اگر آنها را روی سیستمی غیر از MySQL آزمایش کنید، سینتکس یا خروجی دقیق ممکن است متفاوت باشد.
شما همچنین به یک پایگاه داده خالی نیاز دارید که در آن بتوانید جداول استفاده از رویه های ذخیره شده را ایجاد کنید. ما شما را تشویق می کنیم که برای جزئیات مربوط به اتصال به سرور MySQL و ایجاد پایگاه داده آزمایشی مورد استفاده در نمونه های این راهنما، بخش اتصال به MySQL و راه اندازی یک پایگاه داده نمونه زیر را طی کنید.
اتصال به MySQL و راه اندازی یک پایگاه داده نمونه
در این قسمت به سرور MySQL متصل می شوید و یک پایگاه داده نمونه ایجاد می کنید تا بتوانید از مثال های این راهنما استفاده کنید.
برای این راهنما، از یک مجموعه خودروی خیالی استفاده خواهید کرد. شما جزئیات مربوط به خودروهای تحت مالکیت فعلی را با برند، مدل، سال ساخت و ارزش آنها ذخیره خواهید کرد.
اگر سیستم پایگاه داده SQL شما بر روی یک سرور راه دور اجرا می شود، SSH را از دستگاه محلی خود به سرور خود وارد کنید:
ssh sammy@your_server_ip
سپس اعلان سرور MySQL را باز کنید و نام حساب کاربری MySQL خود را جایگزین سامی کنید:
mysql -u sammy -p
ایجاد یک پایگاه داده با نام رویه ها:
CREATE DATABASE procedures;
اگر پایگاه داده با موفقیت ایجاد شده باشد، خروجی زیر را دریافت خواهید کرد:
Output
Query OK, 1 row affected (0.01 sec)برای انتخاب پایگاه داده رویه ها، عبارت USE زیر را اجرا کنید:
USE procedures;
خروجی زیر را دریافت خواهید کرد:
Output
Database changedپس از انتخاب پایگاه داده، می توانید نمونه جداول را درون آن ایجاد کنید. ماشین‌های جدول حاوی داده‌های ساده‌سازی شده درباره ماشین‌ها در پایگاه داده خواهد بود. ستون های زیر را نگه می دارد:
- make: این ستون نشان دهنده هر خودروی متعلق به آن است که با استفاده از نوع داده varchar با حداکثر 100 کاراکتر بیان شده است.
- مدل: این ستون دارای نام مدل خودرو است که با استفاده از نوع داده varchar با حداکثر 100 کاراکتر بیان شده است.
- year: این ستون سال ساخت خودرو را با نوع داده int برای نگهداری مقادیر عددی ذخیره می کند.
- مقدار: این ستون ارزش خودرو را با استفاده از نوع داده اعشاری با حداکثر 10 رقم و 2 رقم پس از نقطه اعشار ذخیره می کند.
جدول نمونه را با دستور زیر ایجاد کنید:
CREATE TABLE cars (
make varchar(100),
model varchar(100),
year int,
value decimal(10, 2)
);اگر خروجی زیر چاپ شود، جدول ایجاد شده است:
Output
Query OK, 0 rows affected (0.00 sec)پس از آن، با اجرای عملیات INSERT INTO زیر، جدول خودروها را با داده های نمونه بارگیری کنید:
INSERT INTO cars
VALUES
('Porsche', '911 GT3', 2020, 169700),
('Porsche', 'Cayman GT4', 2018, 118000),
('Porsche', 'Panamera', 2022, 113200),
('Porsche', 'Macan', 2019, 27400),
('Porsche', '718 Boxster', 2017, 48880),
('Ferrari', '488 GTB', 2015, 254750),
('Ferrari', 'F8 Tributo', 2019, 375000),
('Ferrari', 'SF90 Stradale', 2020, 627000),
('Ferrari', '812 Superfast', 2017, 335300),
('Ferrari', 'GTC4Lusso', 2016, 268000);عملیات INSERT INTO ده خودروی اسپرت نمونه با پنج مدل پورشه و پنج مدل فراری را به جدول اضافه می کند. خروجی زیر نشان می دهد که هر پنج ردیف اضافه شده اند:
Output
Query OK, 10 rows affected (0.00 sec)
Records: 10 Duplicates: 0 Warnings: 0با آن، شما آماده هستید تا بقیه راهنما را دنبال کنید و شروع به استفاده از رویه های ذخیره شده در SQL کنید.
مقدمه ای بر رویه های ذخیره شده
رویه‌های ذخیره شده در MySQL و در بسیاری از سیستم‌های پایگاه داده رابطه‌ای دیگر، اشیایی نامیده می‌شوند که حاوی یک یا چند دستورالعمل هستند که در هنگام فراخوانی توسط پایگاه داده در یک دنباله اجرا می‌شوند. در ابتدایی ترین مثال، یک رویه ذخیره شده می تواند یک عبارت رایج را تحت یک روال قابل استفاده مجدد ذخیره کند، مانند بازیابی داده ها از پایگاه داده با فیلترهای اغلب استفاده می شود. به عنوان مثال، می توانید یک رویه ذخیره شده برای بازیابی مشتریان فروشگاه آنلاینی که در چند ماه گذشته سفارش داده اند ایجاد کنید. در پیچیده ترین سناریوها، رویه های ذخیره شده می توانند برنامه های گسترده ای را نشان دهند که منطق تجاری پیچیده را برای برنامه های کاربردی قوی توصیف می کنند.
مجموعه دستورالعمل‌ها در یک رویه ذخیره‌شده می‌تواند شامل عبارات رایج SQL، مانند کوئری‌های SELECT یا INSERT باشد که داده‌ها را برمی‌گرداند یا دستکاری می‌کند. علاوه بر این، رویه های ذخیره شده می توانند از موارد زیر استفاده کنند:
- پارامترها به رویه ذخیره شده ارسال شده یا از طریق آن بازگردانده می شوند.
- متغیرهای اعلام شده برای پردازش داده های بازیابی شده به طور مستقیم در کد رویه.
- عبارات شرطی، که امکان اجرای بخش هایی از کد رویه ذخیره شده را بسته به شرایط خاصی مانند دستورالعمل های IF یا CASE می دهد.
- حلقه‌هایی مانند WHILE، LOOP و REPEAT اجازه می‌دهند بخش‌هایی از کد چندین بار اجرا شوند، مانند هر ردیف در مجموعه داده‌های بازیابی شده.
- دستورالعمل‌های رسیدگی به خطا، مانند بازگرداندن پیام‌های خطا به کاربران پایگاه داده که به رویه دسترسی دارند.
- فراخوانی سایر رویه های ذخیره شده در پایگاه داده.
توجه: سینتکس گسترده ای که توسط MySQL پشتیبانی می شود، امکان نوشتن برنامه های قوی و حل مشکلات پیچیده با رویه های ذخیره شده را فراهم می کند. این راهنما فقط استفاده اساسی از رویه های ذخیره شده را با عبارات SQL که در بدنه رویه ذخیره شده، پارامترهای ورودی و خروجی محصور شده اند، پوشش می دهد. اجرای کد شرطی، استفاده از متغیرها، حلقه‌ها و مدیریت خطای سفارشی خارج از محدوده این راهنما است. ما شما را تشویق می کنیم که در مورد رویه های ذخیره شده در اسناد رسمی MySQL اطلاعات بیشتری کسب کنید.
هنگامی که رویه با نام خود فراخوانی می شود، موتور پایگاه داده آن را همانطور که تعریف شده است، دستورالعمل به دستورالعمل اجرا می کند.
کاربر پایگاه داده باید مجوزهای مناسب برای اجرای رویه داده شده را داشته باشد. این نیاز مجوز لایه ای از امنیت را فراهم می کند، دسترسی مستقیم به پایگاه داده را ممنوع می کند و در عین حال به کاربران امکان دسترسی به رویه های فردی را می دهد که اجرای آنها ایمن هستند.
رویه های ذخیره شده مستقیماً روی سرور پایگاه داده اجرا می شوند و تمام محاسبات را به صورت محلی انجام می دهند و نتایج را تنها پس از پایان به کاربر فراخوان برمی گرداند.
اگر می خواهید رفتار رویه را تغییر دهید، می توانید رویه را در پایگاه داده به روز کنید و برنامه هایی که از آن استفاده می کنند به طور خودکار نسخه جدید را دریافت می کنند. همه کاربران بلافاصله بدون نیاز به تنظیم برنامه های خود، استفاده از کد رویه جدید را شروع می کنند.
در اینجا ساختار کلی کد SQL مورد استفاده برای ایجاد یک رویه ذخیره شده است:
DELIMITER //
CREATE PROCEDURE procedure_name(parameter_1, parameter_2, . . ., parameter_n)
BEGIN
instruction_1;
instruction_2;
. . .
instruction_n;
END //
DELIMITER ;اولین و آخرین دستورالعمل در این قطعه کد عبارتند از DELIMITER // و DELIMITER ;. معمولاً MySQL از علامت نقطه ویرگول (;) برای تعیین حدود عبارات و نشان دادن زمان شروع و پایان آنها استفاده می کند. اگر چندین دستور را در کنسول MySQL اجرا کنید که با نقطه ویرگول جدا شده اند، آنها به عنوان دستورات جداگانه در نظر گرفته می شوند و به طور مستقل یکی پس از دیگری اجرا می شوند. با این حال، رویه ذخیره شده می‌تواند چندین فرمان را در بر بگیرد که در صورت فراخوانی به‌طور متوالی اجرا می‌شوند. هنگامی که می خواهید به MySQL بگویید یک رویه جدید ایجاد کند، این مشکل ایجاد می کند. موتور پایگاه داده با علامت نقطه ویرگول در بدنه رویه ذخیره شده روبرو می شود و فکر می کند که باید اجرای دستور را متوقف کند. در این شرایط، دستور مورد نظر، کل کد ایجاد رویه است، نه یک دستورالعمل واحد در خود رویه، بنابراین MySQL قصد شما را اشتباه تفسیر می کند.
برای رفع این محدودیت، از دستور DELIMITER برای تغییر موقت جداکننده از ; به // برای مدت تماس CREATE PROCEDURE. سپس، تمام نقطه ویرگول های داخل بدنه رویه ذخیره شده به همان شکلی که هست به سرور ارسال می شود. پس از اتمام کل مراحل، جداکننده به ; با آخرین DELIMITER ;.
قلب کد برای ایجاد یک رویه جدید، فراخوانی CREATE PROCEDURE است که به دنبال آن نام رویه: procedure_name در مثال آمده است. نام رویه توسط یک لیست اختیاری از پارامترهایی که رویه می پذیرد دنبال می شود. بخش آخر بدنه رویه است که در دستورات BEGIN و END محصور شده است. در داخل کد رویه وجود دارد که می تواند حاوی یک عبارت SQL باشد، مانند پرس و جوی SELECT یا کد پیچیده تر.
دستور END به جای یک نقطه ویرگول معمولی با //، یک جداکننده موقت خاتمه می یابد.
در بخش بعدی، یک رویه ذخیره شده اولیه بدون پارامتری ایجاد می کنید که یک پرس و جو را در بر می گیرد.
ایجاد یک رویه ذخیره شده بدون پارامتر
در این بخش، اولین رویه ذخیره شده خود را ایجاد می‌کنید که یک عبارت SQL SELECT را در بر می‌گیرد تا لیستی از اتومبیل‌های متعلق به سفارش‌شده بر اساس برند و ارزش آنها را به ترتیب نزولی بازگرداند.
با اجرای عبارت SELECT که می خواهید استفاده کنید شروع کنید:
SELECT * FROM cars ORDER BY make, value DESC;
پایگاه داده لیستی از خودروها را از جدول خودروها، ابتدا بر اساس برند و سپس در یک برند، بر اساس مقدار به ترتیب نزولی، برمی گرداند:
Output
+---------+---------------+------+-----------+
| 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)با ارزش ترین فراری در بالای لیست قرار دارد و کم ارزش ترین پورشه در انتهای لیست قرار دارد.
فرض کنید این پرس و جو به طور مکرر در چندین برنامه کاربردی یا توسط چندین کاربر استفاده می شود و فرض کنید می خواهید مطمئن شوید که همه از روش مشابهی برای ترتیب دادن نتایج استفاده می کنند. برای انجام این کار، می خواهید یک رویه ذخیره شده ایجاد کنید که آن عبارت را تحت یک رویه با نام قابل استفاده مجدد ذخیره می کند.
برای ایجاد این رویه ذخیره شده، قطعه کد زیر را اجرا کنید:
DELIMITER //
CREATE PROCEDURE get_all_cars()
BEGIN
SELECT * FROM cars ORDER BY make, value DESC;
END //
DELIMITER ;همانطور که در بخش قبل توضیح داده شد، اولین و آخرین دستورات (DELIMITER // و DELIMITER 😉 به MySQL می گویند که از تلقی کاراکتر نقطه ویرگول به عنوان جداکننده دستور برای مدت زمان ایجاد رویه خودداری کند.
دستور CREATE PROCEDURE SQL با نام رویه get_all_cars دنبال می‌شود، که می‌توانید آن را تعریف کنید تا به بهترین وجه توضیح دهید که رویه چه می‌کند. بعد از نام رویه، یک جفت پرانتز () وجود دارد که می توانید پارامترها را اضافه کنید. در این مثال، رویه از پارامترها استفاده نمی کند، بنابراین پرانتز خالی است. سپس، بین دستورات BEGIN و END که ابتدا و انتهای بلوک کد رویه را تعریف می‌کنند، عبارت SELECT که قبلاً استفاده شده بود، کلمه به کلمه نوشته می‌شود.
پایگاه داده با یک پیام موفقیت پاسخ خواهد داد:
Output
Query OK, 0 rows affected (0.02 sec)روال get_all_cars اکنون در پایگاه داده ذخیره شده است و هنگامی که فراخوانی شود، دستور ذخیره شده را همانطور که هست اجرا می کند.
برای اجرای رویه های ذخیره شده ذخیره شده، می توانید از دستور CALL SQL و به دنبال آن نام رویه استفاده کنید. سعی کنید رویه جدید ایجاد شده را مانند این اجرا کنید:
CALL get_all_cars;
نام رویه، get_all_cars، تنها چیزی است که برای استفاده از رویه نیاز دارید. دیگر نیازی به تایپ دستی هیچ بخشی از عبارت SELECT که قبلاً استفاده کرده اید ندارید. پایگاه داده نتایج را درست مانند خروجی دستور SELECT اجرا می کند:
Output
+---------+---------------+------+-----------+
| 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)شما اکنون با موفقیت یک رویه ذخیره شده بدون هیچ پارامتری ایجاد کرده اید که همه ماشین ها را از جدول ماشین های سفارش داده شده به روشی خاص باز می گرداند. شما می توانید از روش در چندین برنامه استفاده کنید.
در بخش بعدی، رویه‌ای ایجاد می‌کنید که پارامترهایی را برای تغییر رفتار رویه بسته به ورودی کاربر می‌پذیرد.
ایجاد یک رویه ذخیره شده با پارامتر ورودی
در این بخش، پارامترهای ورودی را به تعریف رویه ذخیره شده اضافه می‌کنید تا به کاربرانی که این رویه را اجرا می‌کنند اجازه دهید داده‌ها را به آن ارسال کنند. به عنوان مثال، کاربران می توانند فیلترهای پرس و جو را ارائه دهند.
رویه ذخیره شده قبلی get_all_cars همه ماشین ها را همیشه از جدول ماشین ها بازیابی می کند. بیایید رویه دیگری برای یافتن خودروهای یک سال تولید معین ایجاد کنیم. برای اجازه دادن به آن، یک پارامتر با نام را در تعریف رویه تعریف می‌کنید.
کد زیر را اجرا کنید:
DELIMITER //
CREATE PROCEDURE get_cars_by_year(
IN year_filter int
)
BEGIN
SELECT * FROM cars WHERE year = year_filter ORDER BY make, value DESC;
END //
DELIMITER ;چندین تغییر در کد ایجاد رویه از بخش قبل وجود دارد.
اول، نام get_cars_by_year است، که این روش را شرح می‌دهد: خودروها را بر اساس سال تولیدشان بازیابی کنید.
پرانتزهای خالی قبلی اکنون حاوی یک تعریف پارامتر هستند: IN year_filter int. کلمه کلیدی IN به پایگاه داده می گوید که پارامتر توسط کاربر فراخوان به رویه ارسال می شود. year_filter یک نام دلخواه برای پارامتر است. از آن برای ارجاع به پارامتر در کد رویه استفاده خواهید کرد. در نهایت، int نوع داده است. در این حالت سال تولید به صورت یک مقدار عددی بیان می شود.
پارامتر year_filter تعریف شده پس از نام رویه در عبارت SELECT در عبارت WHERE year = year_filter ظاهر می شود و جدول خودروها را با سال تولید آنها فیلتر می کند.
پایگاه داده یک بار دیگر با یک پیام موفقیت پاسخ خواهد داد:
Output
Query OK, 0 rows affected (0.02 sec)سعی کنید رویه را بدون ارسال هیچ پارامتری به آن اجرا کنید، درست مانند قبل:
CALL get_cars_by_year;
پایگاه داده MySQL یک پیغام خطا برمی گرداند:
Error message
ERROR 1318 (42000): Incorrect number of arguments for PROCEDURE procedures.get_cars_by_year; expected 1, got 0این بار، رویه ذخیره شده انتظار دارد پارامتری ارائه شود، اما هیچ کدام داده نشد. برای فراخوانی یک رویه ذخیره شده با پارامترها، می توانید مقادیر پارامتر را در داخل پرانتز به همان ترتیبی که توسط رویه مورد انتظار است ارائه دهید. برای بازیابی خودروهای تولید شده در سال 2017، موارد زیر را اجرا کنید:
CALL get_cars_by_year(2017);
اکنون روال فراخوانی شده به درستی اجرا می شود و لیست خودروهای آن سال را برمی گرداند:
Output
+---------+---------------+------+-----------+
| 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 استفاده کنید. مشابه پارامترهای IN، پارامترهای OUT دارای نام و نوع داده مرتبط با آنها هستند. با این حال، به جای ارسال داده ها به رویه ذخیره شده، می توان آنها را با داده های رویه ذخیره شده پر کرد تا مقادیر را به کاربر فراخوان بازگرداند.
یک رویه get_car_stats_by_year ایجاد کنید که با استفاده از پارامترهای خروجی، داده‌های خلاصه خودروها را از یک سال تولید معین برمی‌گرداند:
DELIMITER //
CREATE PROCEDURE get_car_stats_by_year(
IN year_filter int,
OUT cars_number int,
OUT min_value decimal(10, 2),
OUT avg_value decimal(10, 2),
OUT max_value decimal(10, 2)
)
BEGIN
SELECT COUNT(*), MIN(value), AVG(value), MAX(value)
INTO cars_number, min_value, avg_value, max_value
FROM cars
WHERE year = year_filter ORDER BY make, value DESC;
END //
DELIMITER ;این بار در کنار پارامتر IN year_filter که برای فیلتر کردن خودروها بر اساس سال تولید استفاده می شود، چهار پارامتر OUT در بلوک پرانتز تعریف شده است. پارامتر cars_number با نوع داده int نمایش داده می شود و برای برگرداندن تعداد خودروهای مجموعه استفاده می شود. پارامترهای min_value، avg_value و max_value ارزش بازار را نشان می‌دهند و با نوع اعشاری (10، 2) تعریف می‌شوند (مشابه ستون ارزش در جدول ماشین‌ها). از اینها برای بازگرداندن اطلاعات مربوط به ارزان‌ترین و گران‌ترین خودروها از مجموعه، و همچنین میانگین قیمت تمام خودروهای همسان استفاده می‌شود.
دستور SELECT چهار مقدار را از جدول cars با استفاده از توابع ریاضی SQL جستجو می کند: COUNT برای به دست آوردن تعداد کلی اتومبیل ها و MIN، AVG و MAX برای بدست آوردن مقدار حداقل، متوسط و حداکثر از ستون مقدار.
برای اینکه به پایگاه داده بگوییم که نتایج آن کوئری باید در پارامترهای خروجی رویه ذخیره شده ذخیره شود، یک کلمه کلیدی جدید به نام INTO معرفی شده است. بعد از کلمه کلیدی INTO، نام چهار پارامتر رویه مربوط به داده های بازیابی شده فهرست می شود. با این کار، MySQL مقدار COUNT(*) را در پارامتر cars_number، نتیجه MIN(value) را در پارامتر min_value و غیره ذخیره می کند.
پایگاه داده ایجاد موفقیت آمیز رویه را تأیید می کند:
Output
Query OK, 0 rows affected (0.02 sec)حال با اجرای زیر روال جدید را اجرا کنید:
CALL get_car_stats_by_year(2017, @number, @min, @avg, @max);
چهار پارامتر جدید با علامت @ شروع می شوند. اینها نام متغیرهای محلی در کنسول MySQL هستند که می توانید از آنها برای ذخیره موقت داده ها استفاده کنید. وقتی آن ها را به رویه ذخیره شده ای که ایجاد کرده اید منتقل می کنید، این رویه مقادیری را در آن متغیرها درج می کند.
پایگاه داده با موارد زیر پاسخ خواهد داد:
Output
Query OK, 1 row affected (0.00 sec)این با رفتار قبلی متفاوت است، جایی که نتایج بلافاصله روی صفحه نمایش داده می‌شوند. این به این دلیل است که نتایج رویه ذخیره شده در پارامترهای خروجی ذخیره شده و به عنوان نتیجه پرس و جو برگردانده نشده است. برای دسترسی به نتایج، می توانید آنها را مستقیماً در پوسته MySQL به صورت زیر انتخاب کنید:
SELECT @number, @min, @avg, @max;
با این پرس و جو، شما مقادیری را از متغیرهای محلی انتخاب می کنید، نه اینکه دوباره رویه را فراخوانی کنید. رویه ذخیره شده نتایج خود را در آن متغیرها ذخیره کرد و تا زمانی که از پوسته جدا نشوید، داده ها در دسترس خواهند بود.
توجه: برای کسب اطلاعات بیشتر در مورد استفاده از متغیرهای تعریف شده توسط کاربر در MySQL، به بخش متغیرهای تعریف شده توسط کاربر در مستندات مراجعه کنید. هنگامی که در توسعه برنامه استفاده می شود، راه های دسترسی به داده های بازگشتی از رویه های ذخیره شده در زبان ها و چارچوب های برنامه نویسی مختلف متفاوت خواهد بود. در صورت شک، به اسناد زبان و چارچوب انتخابی خود مراجعه کنید.
خروجی مقادیر متغیرهای پرس و جو را نمایش می دهد:
Output
+---------+----------+-----------+-----------+
| @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 استفاده کنید:
DROP PROCEDURE get_car_stats_by_year;
پایگاه داده حذف موفقیت آمیز رویه را با یک پیام موفقیت آمیز تایید می کند:
Output
Query OK, 0 rows affected (0.02 sec)با تلاش برای فراخوانی، می توانید تأیید کنید که رویه حذف شده است. اجرا کردن:
CALL get_car_stats_by_year(2017, @number, @min, @avg, @max);
این بار، یک پیغام خطایی خواهید دید که رویه در پایگاه داده وجود ندارد:
Error message
ERROR 1305 (42000): PROCEDURE procedures.get_car_stats_by_year does not existدر این بخش، نحوه حذف رویه های ذخیره شده موجود در پایگاه داده را یاد گرفتید.
نتیجه
با دنبال کردن این راهنما، یاد گرفتید که رویه های ذخیره شده چیست و چگونه از آنها در MySQL برای ذخیره عبارات قابل استفاده مجدد در رویه های نامگذاری شده استفاده کنید و بعداً آنها را اجرا کنید. شما رویه های ذخیره شده را بدون پارامترها و رویه هایی ایجاد کردید که از پارامترهای ورودی و خروجی استفاده می کنند تا آنها را انعطاف پذیرتر کند.
