تحقیقات

آموزش stored procedure در Mysql

• توسط : علی حسین شهابی
• 0 نظر

با سلام و درود

مقاله که امروز معرفی می کنیم مربوط میشه به معرفی و نحوه ایجاد  stored procedure در Mysql .

 

stored procedure و یا sp چیست؟

stored procedure ها رویه هایی هستند که شما میتونید پارامتر ورودی به اونها بدید و کوئری های MySql رو اجرا کنید، نتیجه ی اون میتونه شامل خروجی باشه میتونه هم نباشه و فقط دستوری اجرا بشه . مثلا برای نوشتن دستورات  Select , Update , delete و … میتونید از stored procedure ها استفاده کنید.

زمانی شما خروجی دریافت میکنید که چیزی رو select کرده باشید و زمانی هم که  update , delete , insert انجام میدید فقط یه کوئری رو  Execute و اجرا کردید که در نتیجه مقداری رو به عنوان خروجی به شما برنمیگردونه.

در صورتی که SP هارو همراه با PDO استفاده کنید نتیجه ی بسیار موثری خواهید گرفت. زمانی که stored procedure یکمرتبه اجرا میشه، در حافظه ذخیره میشه و برای فراخوانی مجدد نیاز به سپری کردن زمان اولیه نیست و سریع کوئری اجرا میشه پس سرعت بالایی در اجرای دستورات خواهید داشت.

در یک SP شما میتونید چندین کوئری رو اجرا کنید و حتی از دستورات If condition و دستورات شرطی هم استفاده کنید. میتونید متغییر تعریف کنید و متغییر رو مقدار دهی کنید و در کوئری ها و دستورات شرطی به کار ببرید . همه ی این مراحل سمت آموزش mysql اجرا میشه و سرعت بالایی هم خواهد داشت.

پس تا الان به چند مزیت Stored Procedure اشاره کردیم:

• امنیت بالا در اجرای کوئری ها
• سرعت بالا در اجرای کوئری ها
• اجرای چندین کوئری در یک  SP
• استفاده از دستورات  IF و  Else در SP
• استفاده از متغییرها و ذخیره سازی مقادیر در متغییرها

 

نحوه ی نوشتن Stored Procedure در MYSQL :

CREATE PROCEDURE `sp_Name`(IN parameter DataType, IN parameter DataType)
BEGIN
 MySql Statement
END

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

 

در این حالت id میشه پارامتر ورودی ما و خروجی که به ما برمیگردونه، اطلاعات و فیلدهای مربوط به کاربر شماره ۳۰ هست. پس stored procedure ما به شکل زیر خواهد بود:

CREATE PROCEDURE `sp_users_selectRow`(IN _id INT)
BEGIN
	SET NAMES UTF8;
    SELECT * FROM users WHERE id = _id;
END

 

رای اینکه کاراکترهای فارسی هم پشتیبانی بشه از SET NAMES UTF8; استفاده کردیم.

خوب حالا برای اجرای این کوئری کافیه اون رو CALL کنیم :

CALL `sp_users_selectRow`(30);

 

نحوه ی نوشتن Stored Procedure ها در MySql

CREATE PROCEDURE `sp_Name`(IN parameter DataType, IN parameter DataType)
BEGIN
 MySql Statement
END

نامStored Procedure هست ، پارامترهایی که داخل پرانتز جلوی نام SP هست، پارامترهای ورودی هستند که در صورتی استفاده می شوند که بخوایم از شرط ها در دستورات استفاده کنیم و یا یه پارامتر به کوئری پاس بدیم. با IN شروع میشه بعد نام پارامتر رو مینویسیم و بعد نوع داده ای اون رو مشخص میکنیم .بعد از گزینه  BEGIN کوئری مورد نظرمون رو مینویسیم و بعد با END بهStored Procedure خاتمه میدیم

 

نحوه ی صدا زدن Stored Procedure ها در php و  MySql

CALL `sp_Name`(parameter,parameter);

 

مثال برای نوشتن و پیاده کردن  Stored Procedure ها

فرض کنید یک جدول داریم به نام  users که قراره یکStored Procedure بنویسیم و اطلاعات تمامی کاربران این جدول رو بگیریم. برای این کار کافیه از دستور  Select در MySql استفاده کنیم. در اینجا چون قراره اطلاعات همه ی کاربرهارو بگیریم پس شرطی هم برای نوشتن نداریم بنابراین پارامتر ورودی هم نداریم:

CREATE PROCEDURE `sp_users_SelectAll`()
BEGIN
	SELECT * FROM `users` ORDER BY `id`;
END

خوب حالا این SP رو نوشتیم برای درج اون در MySql باید به phpmyadmin بریم. دیتابیس مورد نظر رو انتخاب کنید و گزینه sql رو کلیک کنید:

همونطور که در بالا میبینید، Stored Procedure نوشته شده رو در کادر sql کپی می کنید، انتهای END دو عدد $$ و برای گزینه Delimiter هم دو عدد $$ قرار میدید، حالا دکمه Go رو بزنید، اگر Stored Procedure شما مشکلی نداشته باشه، با رنگ سبز رنگ اون رو بالای کادر SQL مینویسه و Stored Procedure رو در mysql ذخیره میکنه در صورتی که مشکل داشته باشه خطای شمارو بهتون نمایش میده. برای مشاهده sp های نوشته و ذخیره شده میتونید به گزینه Routines برید و تمامی Stored Procedure هایی که نوشتید رو ببینید. خوب حالا Stored Procedure که نوشتیم رو میخوایم در قالب php فراخوانی کنیم و نتایج رو نمایش بدیم. خب حالا برای استفاده و صدا زدن Stored Procedure در php و pdo مانند زیر عمل می نماییم :

$sql = 'CALL `sp_users_SelectAll`()';
$q = $conn->prepare($sql);
$q->execute();
$q->setFetchMode(PDO::FETCH_ASSOC);

برگرفته از سایت مدرسه مجازی ایرانیان