نحوه استفاده از Stored Procedure

[بیتا_حکمت]

سلام ، من امروز در مورد Stored Procedure و نحوه استفاده Stored Procedure در Php و Mysql سرچ کردم ، متاسفانه نتیجه اکثر جست و جوهایی این بود که اکثر سایت ها تکه تکه به یه بخش از این موضوع پرداختند و نتونستم مثالی کاملی برای یادگیری پیدا کنم . اینطور که متوجه شدم برای یادگیری کار با Stored Procedure باید چند نکته
رو یاد بگیرم :

1- اینکه چطور یک SP تعریف کنم

2- اینکه چطور برای SP هایی که تعریف می کنم کوئری بنویسم

3- فراخوانی این SP ها در Php (که اکثرا مقالات به PDO اشاره کرده بودن )
یک جایی هم نوشته بود که هاست شما باید از Stored Procedure پیشتبانی کنه
قصد دارم پله پله به کمک شما دوستان یاد بگیرم ..


اولین سوال : من برای نمایش تمام رکوردهای .... این SP رو نوشتم اما خطا داد

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

تا جایی که متوجه شدم sp_users_SelectAll که اینجا نام SP هست می تونه یک نام اختیاری باشه (شایدم اینطور نباشه !)

اما در مورد دیتابیس می تونم بگم که جدول dle_admin_logs در دیتابسم وجود داره و همینطور فلید id داخل اون جدول هست ..

[هادی2020]

من که اینقدر بهش وابستم که حتی توی ادمین سایتم هم با sp ها کار می کنم.

DELIMITER $$
CREATE PROCEDURE `sp_profile_SelectRow`(IN _id int)
BEGIN
    SET NAMES UTF8;
    SELECT `id`, `adderss`, `city`, `telephonene`, `postalcode` FROM `profile` WHERE `id` = _id;
END$$
DELIMITER ;

`sp_profile_SelectRow` : نام sp
داخل پرانتز متغیر های ورودی یا خروجی یا ورودی خروجی
داخل بلاک BEGIN و END هم دستورات البته یادتون باشه DELIMITER هر چه تعریف شده باشه بعد از END باید قرار بگیره
sp ها کش می شوند و در دفعات بعدی سریعتر اجرا میشوند.
خیالت رو از بابت یکسری از حملات مثل sql injection راحت می کنن
و ...

$result = mysql_query("CALL `sp_profile_SelectRow`($profileID)");

البته من با pdo سرو کار دارم
کوری می تونه پیچیده تر باشه و یا بصورت داینامیک طراحی شده باشه یعنی با توجه به ورودی ها دستور select تغییر کنه البته کمتر ازش استفاده کن ولی تا الان مشکلی ازش ندیدم بعضی ها هم میگن این شیوه امکان sql injection داره اما هر قدر خودم سعی کردم نشد.

CREATE PROCEDURE `sp_users_SelectAll`(IN `_admin` TINYINT(1), IN `_roles_id` INT, IN `_email` VARCHAR(255), IN `_fullName` VARCHAR(255), IN `_beginDate` INT, IN `_endDate` INT, IN `_news` TINYINT(1), IN `_mobile` VARCHAR(250), IN `_postalcode` VARCHAR(250), IN `_start` INT, IN `_count` INT, IN `_show` TINYINT(1), IN `_del` TINYINT(1))
BEGIN
DECLARE _statment varchar(1000);
DECLARE _where varchar(1000);
DECLARE _order varchar(250);

SET _statment = '';
SET _where = '';
SET _order = '';

SET _statment = 'SELECT DISTINCT  SQL_CALC_FOUND_ROWS
users.`id`, users.`roles_id`,  users.`email`,users.`pass`,users.`date`,
users.`fullname`,users. `show`,users.`del`, users.`news`
from users left JOIN profiles on(users.`id` =profiles.`users_id`)
 ' ;

IF _roles_id <> -1 THEN
    SET _where = CONCAT(_where, ' `users`.`roles_id` = ',_roles_id,' AND ');
ELSEIF _roles_id = -1 THEN
    SET _where = CONCAT(_where, ' `users`.`roles_id` <> 1 AND ');
END IF;
IF _email <> '' THEN
  SET _where = CONCAT(_where, ' `users`.`email` like "%',_email,'%" AND ');
END IF;	
IF _news <> -1 THEN
    SET _where = CONCAT(_where, ' `users`.`news` = ',_news,' AND ');
END IF;

IF _beginDate <> -1 AND _endDate <>  -1 THEN
    SET _where = CONCAT(_where, ' `users`.`date` >= ',_beginDate,' AND `users`.`date` <= ',_endDate,' AND ');
END IF;
IF _fullName <> '' THEN
    SET _where = CONCAT(_where, ' `users`.`fullName` like "%',_fullName,'%" AND ');
END IF;

IF _admin = 1 or _admin = 11 THEN    #11 SELECT ALL & 1 SELECT WITH LIMIT
    SET _where = CONCAT(_where, ' `users`.`show` = 1 AND `users`.`del` = 0 ');
ELSEIF _admin = 2 or _admin = 22 THEN    #22 SELECT ALL & 2 SELECT WITH LIMIT
    IF _show <> -1 THEN
        SET _where = CONCAT( _where , ' `users`.`show` = ', _show, ' AND ');
    END IF;
    SET _where = CONCAT(_where, ' `users`.`del` = ', _del );
END IF;

IF _where <> '' THEN
    SET _where = CONCAT(' where ',_where);
END IF;

SET _order = ' ORDER BY `users`.`email` ';

IF _admin = 1 OR _admin = 2 THEN    /* only select with Limit*/
    SET _statment = CONCAT(_statment, _where , _order);
    SET _statment = CONCAT(_statment,' LIMIT  ', _start, ' , ', _count);
ELSEIF _admin = 11 OR _admin = 22 THEN
    SET _statment = CONCAT( _statment, _where, _order);
END IF;

SET NAMES UTF8 ;
SET @statment = _statment;
PREPARE dynquery FROM @statment;
EXECUTE dynquery;
DEALLOCATE PREPARE dynquery;

#SELECT _statment;
END

[بیتا_حکمت]

ممنون ، اما چرا SP ی که کدشو گذاشتم خطا می ده ؟

[راوندی]

روش استفاده اش اینطوریه:

<?php 
$con=mysqli_connect("localhost","root","","test");
 mysqli_set_charset($con, "utf8"); 
$result = mysqli_query($con, 'CALL jadval(1);');
 while($row = mysqli_fetch_array($result)) {
 echo $row['name'];
 }
 mysqli_close($con);
 ?>

[هادی2020]

قبل از sp یه DELIMITER بزار همراه با جداکننده مثلا $$
بعد از END کاراکترهای DELIMITER (در اینجا $$) رو قرار بده
(مدتی هست با این سایت مشکل دارم که نمی دونم چرا نمیشه به راحتی به پست گذاشت الان پست دوم رو تونستم کاملش کنم)

[بیتا_حکمت]

ممنونم .امشب قدم اول رو یاد گرفتم d:

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

DELIMITER $$
CREATE PROCEDURE `Learn_SP`()
BEGIN
    SELECT * FROM `Test` ORDER BY `ID`;
END $$

[هادی2020]

sp هات رو اگر درست نام گذاری کنی وقتی زیاد بشن به راحتی میتونی پیداشون کنی
sp رو با کلمه sp_ شروع کن مثل چیزی که برات توی مثال آوردم بعدش نام جدول بعدش کاری که انجام میده
مثلا sp_users_SelectAll - sp_users_SelectByID - sp_users_UpdateRow -
قبل از select ها یا insert و update از دستور SET NAMES UTF8; داخل sp استفاده کن
تمام دستورات sp پست اول رو با دقت مطالعه کنید نکات دیگری رو برای یادگیری داره که ازش می تونی استفاده کنی

[بیتا_حکمت]

با این دستــور میشه Sp ها رو داخل phpmyADmin کـــرد ، اما چطوری می تونم اونا رو ویراش کنم ؟ یا اینکه چطور می تونم یک Sp رو داخل Mysql فراخوانی کنم

SHOW PROCEDURE STATUS;
SHOW FUNCTION STATUS;