فراخوانی Stored Procedure ها در PDO

اگر با پایگاه های داده کار کرده باشید حتما اسم stored procedure ها (در فارسی بعضا به آن ها "رویه های ذخیره شده" نیز می گویند) را شنیده اید. در اولین جلسه از این سری آموزشی نیز توضیح دادیم که این دوره مخصوص افرادی است که قبلا با پایگاه های داده کار کرده باشند اما باز هم به منظور یادآوری، ساختار کلی آن ها را برای شما توضیح می دهم:

stored procedure چیست؟

stored procedure ها در واقع کد های SQL ای هستند که به دلیل استفاده ی مکرر، از قبل ذخیره می شوند تا بعدها چندین بار اجرا شوند. بنابراین اگر کوئری ای دارید که بار ها و بار ها از آن استفاده می کنید، می توانید آن را به صورت stored procedure ذخیره کنید و هر بار که به آن نیاز داشتید تنها صدایش بزنید. ساختار کلی برای ساخت یک stored procedure به این شکل است:

CREATE PROCEDURE procedure_name
AS
sql_statement
GO;

و ساختار صدا زدن آن نیز به این شکل:

EXEC procedure_name;

نکته: این ساختار در پایگاه های داده ی مختلف، متفاوت است. قبل از استفاده از ساختار مناسب برای پایگاه داده ی خود اطمینان حاصل کنید.

در ساختار بالا عبارت procedure_name به معنی "نام رویه" و sql_statement قسمتی است که کد های SQL خود را در آن جا می نویسید.

به طور مثال فرض کنید در پایگاه داده ی خود جدولی به این شکل داریم:

یکی از کوئری هایی که ممکن است بارها و بارها مورد استفاده ی ما قرار بگیرد، دریافت تمام مشتریان است، بنابراین یک stored procedure به همین نام ایجاد می کنیم:

CREATE PROCEDURE SelectAllCustomers
AS
SELECT * FROM Customers
GO;

و هر بار که خواستیم این کوئری را اجرا کنیم از دستور زیر استفاده می کنیم:

EXEC SelectAllCustomers;

نکته: اگر یادتان باشد stored procedure ها می توانند پارامتر نیز بگیرند مانند:

CREATE PROCEDURE SelectAllCustomers @City nvarchar(30)
AS
SELECT * FROM Customers WHERE City = @City
GO;

و هنگام فراخوانی:

EXEC SelectAllCustomers City = "London";

همچنین تعداد این پارامتر ها می تواند بیشتر از یکی باشد که در این صورت آن ها را با استفاده از ویرگول انگلیسی از هم جدا می کنیم. مثال:

CREATE PROCEDURE SelectAllCustomers @City nvarchar(30), @PostalCode nvarchar(10)
AS
SELECT * FROM Customers WHERE City = @City AND PostalCode = @PostalCode
GO;

و برای فراخوانی:

EXEC SelectAllCustomers City = "London", PostalCode = "WA1 1DP";

من وارد این مسئله و باقی جزئیات آن نمی شویم چرا که بحث اصلی ما استفاده از stored procedure ها در PDO است. اگر با این مطلب آشنایی ندارید به مقالات روکسو و دیگر پایگاه های اینترنتی مراجعه کنید.

فراخوانی stored procedure ها در PDO

وقتی بحث stored procedure پیش می آید، تمام برنامه نویسان به یاد نکته ای عجیب در این رابطه می افتند: هر stored procedure ای که فراخوانی می شود، یک result set¹ اضافه نیز بر میگرداند؛ یعنی یک یا چند result set که دارای داده های واقعی هستند به علاوه ی یک result set خالی! باید از قبل بدانید که result set ها (بعضا در فارسی با نام "مجموعه نتایج" به آن ها اشاره می شود) ردیف هایی هستند که طی یک کوئری از جدول به ما برگردانده می شوند.

بر این اساس می توان گفت اگر یک stored procedure را فراخوانی کنید و سپس به سراغ اجرای کوئری بعدی بروید با خطای معروف "Cannot execute queries while other unbuffered queries are active" مواجه خواهید شد.

چرا؟ به این دلیل که هنوز result set اضافی و خالی را بررسی نکرده اید و نمی توانید آن را به امان خدا رها کنید!

بنابراین اگر از stored procedure ای استفاده می کنید که قرار است تنها یک result set را برگرداند از دستور ()PDOStatement::nextRowset استفاده کنید. البته حواستان باشد که ابتدا تمام داده های برگشت داده شده را fetch کنید و گرنه دستور ما نادیده گرفته می شود. مثال:

$stmt = $pdo->query("CALL bar()");
$data = $stmt->fetchAll();
$stmt->nextRowset();

اما اگر از stored procedure ای استفاده می کنید که قرار است چندین result set را برگرداند، به روش زیر عمل کنید:

$stmt = $pdo->prepare("CALL foo()");
$stmt->execute();
do {
    $data = $stmt->fetchAll();
    var_dump($data);
} while ($stmt->nextRowset() && $stmt->columnCount());

همانطور که در کد می بینید از یک حقه ی زیرکانه استفاده کرده ایم.

result set اضافی را که یادتان هست؟ این result set آنقدر خالی است که هر نوع تلاشی برای fetch کردن هر چیزی از آن باعث بروز خطا می شود، بنابراین نمی توان تنها از (()while ($stmt->nextRowset استفاده کرد و باید مراقب داده های خالی نیز باشیم. به همین علت از ()PDOStatement::columnCount استفاده می کنیم.

این یکی از تفاوت های اساسی mysql ext و کتابخانه های مدرن است. زمانی که در ()mysql_query از یک stored procedure استفاده می کردیم هیچ راهی برای ادامه ی کار با آن اتصال (connection) نبود چرا که هیچ تابع ()nextResult ای وجود نداشت. به علت پس از فراخوانی یک stored procedure، مجبور بودیم آن اتصال را قطع کرده و برای اجرای دیگر کوئری ها اتصال جدیدی بسازیم.

نکته: حواستان باشد که ممکن است سینتکس یا همان ساختار کلی این دستور برای پایگاه های داده ی مختلف متفاوت باشد. به طور مثال اگر بخواهید از stored procedure ها در Microsoft SQL server استفاده کنید، باید از ساختار زیر پیروی کنید:

$stmt = $pdo->prepare("EXEC stored_procedure ? ?");

در کد بالا علامت های سوال placeholder هستند. همچنین باید توجه داشته باشید که در این ساختار از هیچ نوع پرانتز، براکت و علائم نگارشی اینچنینی استفاده نمی شود.

امیدوارم از این قسمت، استفاده ی لازم را برده باشید.