روال ها و کرسرها (Procedures and Cursors) در SQL — راهنمای مقدماتی

در این راهنما با روش نوشتن روال‌ها و کرسر ها برای یک RDBMS آشنا می‌شویم. SQL یک مهارت ضروری برای مهندسان نرم‌افزار امروزی محسوب می‌شود. چون اغلب نرم‌افزارها به نوعی با داده‌ها سر و کار دارند و به طور کامل با یک RDBMS «سیستم مدیریت پایگاه داده رابطه‌ای» (Relational Database Management System) ادغام شده‌اند. چه یک اپلیکیشن وب باشد، چه API یا اپلیکیشن درون‌سازمانی، در هر صورت RDBMS همه جا حضور دارد و SQL زبانی برای کوئری زدن به یک RDBMS محسوب می‌شود.

شما به عنوان یک دانشمند داده می‌بایست با SQL و تکنیک‌های مربوط به آن آشنا باشید. برای این که بتوانید به یک RDBMS کوئری بزنید و پاسخ‌هایی را برای سؤال‌های خاصی در مورد داده‌های مورد نظر دریافت کنید، SQL کمترین ابزار مورد نیاز است.

در این راهنما با روش نوشتن روال‌ها و کرسرها آشنا خواهید شد که یکی از جنبه‌های مهم SQL محسوب می‌شود. آیا تا کنون خواسته‌اید RDBMS شما به طور خودکار اقدام‌های خاصی را در هنگام رویداد اتفاق معین اجرا کند؟ برای نمونه فرض کنید یک رکورد جدید برای کارمندی در یک جدول به نام Employees ایجاد کرده‌اید و می‌خواهید این رکورد در جدول‌های دیگر مرتبط مانند Departments نیز انعکاس یابد. در این صورت بهترین راهنما را برای مطالعه انتخاب کرده‌اید، چون در این راهنما دقیقاً به توضیح روش این کار خواهیم پرداخت.

در این راهنما موارد زیر را خواهیم آموخت:

• یک روال در یک RDBMS به چه معنا است؟
• چگونه می‌توانیم یک روال بنویسیم؟
• انواع مختلف روال‌ها کدام هستند؟
• یک کرسر در یک RDBMS به چه معنا است؟
• چگونه می‌توان انواع مختلفی از کرسرها را نوشت؟
• انواع مختلف کرسرها کدام هستند؟

یک روال در یک RDBMS به چه معنا است؟

پیش از ادامه این راهنما در مورد روال‌ها و کرسرها باید کمی در مورد PL/SQL بدانید. PL/SQL یک زبان ساخت‌یافته بلوکی است که به توسعه‌دهندگان کمک می‌کند تا قدرت SQL را با گزاره‌های روال ترکیب کنند. اما ما قصد نداریم آن را به روش متعارف آموزش دهیم؛ بلکه آن را در طی مسیر آموزش SQL و به تدریج در موارد نیاز یاد خواهیم گرفت.

فیلم آموزش مقدماتی برنامه نویسی اوراکل PL/SQL در فرادرس

کلیک کنید

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

نوشتن روال‌ها

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

CREATE PROCEDURE procedure_name
AS
sql_statement
GO;

دقت کنید که این ساختارها در مورد تقریباً هر نوع RDBMS به کار گرفته می‌شوند، چه Oracle باشد و چه PostgreSQL یا MySQL.

پس از این که روال خود را ایجاد کردید باید آن را اجرا کنید. در ادامه ساختار این کار ارائه شده است:

EXEC procedure_name;

اکنون یک روال ساده می‌نویسیم. تصویر زیر را از یک RDBMS که شامل یک جدول به نام Customers را در نظر بگیرید:

می‌خواهیم روالی به نام SelectAllCustomers بنویسیم که همه مشتریان را از جدول Customers انتخاب کند:

CREATE PROCEDURE SelectAllCustomers
AS
SELECT * FROM Customers
GO;

به این منظور باید دستور زیر را اجرا کنید:

EXEC SelectAllCustomers;

روال‌ها می‌توانند بلوک‌های خودکفایی از گزاره‌ها باشند که آن‌ها را از هر جدولی مانند مثال قبلی مستقل می‌سازد. در مثال زیر یک روال ساده ایجاد می‌کنیم که هنگام اجرا در خروجی، رشته‌ای به صورت «!Hello World» را نمایش می‌دهد.

CREATE PROCEDURE welcome
AS
BEGIN
dbms_output.put_line('Hello World!');
END;

دو روش برای اجرای یک روال مستقل وجود دارد:

• استفاده از کلیدواژه EXEC
• فراخوانی نام روال از بلوک PL/SQL

روال فوق با نام «welcome» می‌تواند با کلیدواژه EXEC به صورت زیر فراخوانی شود:

 EXEC welcome;

اکنون می‌بینید که در متد بعدی یک روال را از بلوک PL/SQL دیگر فراخوانی می‌کنیم:

BEGIN
welcome;
END;

یک روال می‌تواند جایگزین نیز بشود. تنها کافی است کلیدواژه REPLACE را هنگام ایجاد روال قید کنید. بدین ترتیب یک روال در صورت موجود بودن، جایگزین می‌شود و در غیر این صورت یک روال تازه ایجاد می‌شود.

CREATE OR REPLACE PROCEDURE welcome
AS
BEGIN
dbms_output.put_line('Hello World!');
END;

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

DROP PROCEDURE procedure-name;

روال‌ها می‌توانند بر اساس پارامترها نیز متفاوت باشند. ممکن است روال‌های یک پارامتری یا چند پارامتری وجود داشته باشند. در ادامه به بررسی این روال‌ها می‌پردازیم.

ما از همان جدول Customers به این منظور استفاده می‌کنیم. برای سهولت کار تصویر زیر دوباره ارائه شده است:

می‌خواهیم یک روال ذخیره شده بنویسیم که مشتریان را از یک شهر خاص از جدول مربوطه انتخاب کند:

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

مفاهیم مشترک را به صورت زیر می‌توان ارائه کرد:

• ما City@ اول را نوشته و نوع و اندازه آن را به عنوان یکی از پارامترهایی که هنگام اجرای روال ارائه خواهد شد تعریف کرده‌ایم.
• City@ دوم به متغیر شرطی City انتساب می‌یابد که چیزی به جز یک ستون در جدول Customers نیست.

این روال به صورت زیر اجرا می‌شود:

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";

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

کرسر در یک RDBMS چیست؟

پایگاه داده‌ای مانند Oracle یک ناحیه حافظه ایجاد می‌کند که به نام «ناحیه زمینه» (context area) شناخته می‌شود و در آن به پردازش گزاره‌های SQL می‌پردازیم که شامل همه اطلاعات مورد نیاز برای پردازش گزاره، برای نمونه تعداد ردیف‌های پردازش شده است.

فیلم آموزش اوراکل - مدیریت بانک اطلاعاتی با Oracle – مقدماتی در فرادرس

کلیک کنید

یک کرسر اشاره‌گری به این ناحیه زمینه است. PL/SQL ناحیه زمینه را از طریق یک کرسر کنترل می‌کند. یک کرسر ناحیه کاری موقتی است که در هنگام اجرای یک گزاره SQL در حافظه سیستم ایجاد می‌شود. کرسر شامل اطلاعاتی در مورد گزاره SELECT و ردیف‌های داده دستیابی شده از سوی آن است. از این رو کرسرها برای تسریع پردازش کوئری‌ها در پایگاه‌های داده بزرگ مورد استفاده قرار می‌گیرند. دلیل این که باید از کرسر در پایگاه داده استفاده کرد، این است که باید اقداماتی را روی ردیف‌های منفرد اجرا کرد.

کرسرها دو نوع دارند که عبارت هستند از کرسرهای ضمنی و کرسرهای صریح و در ادامه به بررسی روش نوشتن انواع مختلف آن‌ها می‌پردازیم.

نوشتن کرسرها

در ابتدای این بخش به توضیح ماهیت کرسرهای ضمنی می پردازیم.

فیلم آموزش کوئری‌ نویسی پیشرفته در SQL Server در فرادرس

کلیک کنید

کرسرهای ضمنی

«کرسرهای ضمنی» (Implicit Cursors) به طور خودکار از سوی Oracle هر زمان که یک گزاره SQL اجرا شود و همچنین کرسر صریحی برای گزاره تعریف نشده باشد، تولید می‌شوند. برنامه‌نویس‌ها نمی‌توانند کرسر‌های ضمنی و اطلاعات آن را کنترل کنند. هر زمان که یک گزاره DML «زبان دستکاری داده» (Data Manipulation Language) یعنی دستورهای INSERT, UPDATE و DELETE استفاده می‌شود یک کرسر ضمنی مرتبط با گزاره ایجاد می‌شود. در مورد عملیات INSERT، این کرسر داده‌هایی را که باید درج شوند نگهداری می‌کند. در مورد عملیات UPDATE و DELETE نیز کرسر ردیف‌هایی که تحت تأثیر قرار خواهند گرفت را معین می‌کند.

جدیدترین کرسر ضمنی را می‌توان به صورت کرسر SQL-ی در نظر گرفت که همواره خصوصیاتی مانند زیر دارد:

%FOUND,
%ISOPEN,
%NOTFOUND,
%ROWCOUNT.

تصویر زیر این خصوصیات را به صورت خلاصه توصیف می‌کند:

در تصویر زیر شِمایی از یک پایگاه داده را می‌بینید که شامل جدولی به نام Employee است:

در ادامه یک کرسر می‌نویسیم که حقوق افرادی را که سنشان کمتر از 30 است را 1000 واحد افزایش می‌دهد.

DECLARE
total_rows number(2);

BEGIN
UPDATE Employee
SET salary = salary + 1000
where age < 30;
IF sql%notfound THEN
   dbms_output.put_line('No employees found for under 30 age');
ELSIF sql%found THEN
   total_rows:= sql%rowcount;
   dbms_output.put_line(total_rows || ' employees updated ');
END IF;
END;

کارهایی که در کد فوق اجرا می‌شوند را به صورت زیر می‌توان جمع‌بندی کرد:

• یک متغیر به نام total_rows برای ذخیره‌سازی تعداد کارمندانی که تحت تأثیر اقدام کرسر قرار می‌گیرند تعریف می‌کنیم.
• بلوک کرسرها با BEGIN آغاز می‌شود و یک کوئری ساده SQL نوشته شده است که میزان حقوق افرادی که سنشان کمتر از 30 است را به‌روزرسانی می‌کند.
• خروجی را در حالتی که چنین مدخلی در پایگاه داده نباشد و سن کارمندی کمتر از 30 باشد به روز می‌کنیم. از خصوصیت %notfound به این منظور استفاده می‌شود. دقت کنید که کرسر ضمنی sql در اینجا به ذخیره‌سازی همه اطلاعات مرتبط می‌پردازد.
• در نهایت تعداد رکوردهایی که تحت تأثیر قرار می‌گیرند، در خصوصیت %rowcount ذخیره می‌شود.

زمانی که کد فوق در اعلان SQL اجرا شود، نتیجه زیر حاصل می‌شود:

2 Employees updated (assume there are 2 records where age < 30)

در ادامه به بررسی کرسرهای صریح می‌پردازیم.

کرسرهای صریح

«کرسرهای صریح» (Explicit Cursors) کنترل تعریف‌شده‌تری روی ناحیه زمینه ایجاد می‌کنند. این کرسرها روی گزاره SELECT-ی که بیش از یک ردیف را بازگشت می‌دهد، ایجاد می‌شوند. ساختار ایجاد یک کرسر صریح به صورت زیر است:

CURSOR cursor_name IS select_statement;

اگر با کرسرهای صریح کار می‌کنید، باید یک توالی از مراحل را طی کنید که به صورت زیر است:

• اعلان کرسر برای مقداردهی اولیه در حافظه
• باز کردن کرسر برای تخصیص ناحیه حافظه
• واکشی کرسر برای داده‌های دریافتی
• بستن کرسر برای تخصیص زدایی از حافظه

در تصویر زیر چرخه عمر یک کرسر صریح معمولی را مشاهده می‌کنید:

در ادامه هر یک از مراحل بیشتر توضیح داده شده است:

اعلان کرسر

کرسر صریح همراه با گزاره SELECT اعلان می‌شود.

فیلم آموزش پایگاه داده MySQL مای اس کیو ال در فرادرس

کلیک کنید

برای نمونه:

CURSOR C IS SELECT id, name, address FROM Employee where age > 30;

باز کردن کرسر

زمانی که کرسر را باز می‌کنید،CPU حافظه‌ای را برای کرسر تخصیص می‌دهد و آن را برای واکشی کردن ردیف‌هایی که از سوی گزاره SQL مختص آن بازگشت می‌یابند آماده می‌کند. برای نمونه، کرسر تعریف شده فوق را به صورت زیر باز می‌کنیم:

OPEN C;

واکشی کردن کرسر

واکشی کردن کرسر شامل دسترسی یک به یک به ردیف‌های جدول مربوطه در SQL اختصاص یافته به کرسر است.

FETCH C INTO C_id, C_name, C_address;

بستن کرسر

بستن کرسر به معنی آزاد کردن حافظه تخصیص یافته است. کرسر باز شده قبلی به صورت زیر بسته خواهد شد:

CLOSE C;

در ادامه همه قطعات فوق را به روشی معنادار کنار هم می‌چینیم.

گرد هم آوردن همه قطعات

DECLARE
C_id Employee.ID%type;
C_name Employee.NAME%type;
C_address Employee.ADDRESS%type;
CURSOR C is
SELECT id, name, address FROM Employee where age > 30;
BEGIN
OPEN C;
LOOP
FETCH C INTO C_id, C_name, C_address;
dbms_output.put_line(ID || ' ' || NAME || ' ' || ADDRESS);
EXIT WHEN C%notfound;
END LOOP;
CLOSE C;
END;

بدین ترتیب با روش اعلان متغیرهای کرسر C_id, C_name و C_address آشنا شدیم. خط ;C_id Employee.ID%type تضمین می‌کند که C_id با همان نوع داده‌ای ایجاد می‌شود که نوع داده ID در جدول Employee است.

با استفاده از LOOP می‌توانیم روی کرسر حلقه‌ای تعریف کرده و رکوردها را واکشی کرده و نمایش دهیم. همچنین می‌توانیم مواردی را که هیچ رکوردی از سوی کرسر یافت نمی‌شود را مدیریت کنیم. زمانی که کد در اعلان SQL اجرا شود، نتیجه به صورت زیر خواهد بود:

سخن پایانی

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

فیلم مجموعه آموزش پایگاه داده – مقدماتی تا پیشرفته در فرادرس

کلیک کنید

اگر این مطلب برای شما مفید بوده است، آموزش‌های زیر نیز به شما پیشنهاد می‌شوند:

• مجموعه آموزش‌های پایگاه داده و سیستم های مدیریت اطلاعات
• آموزش کار با دستورات پایگاه داده در SQL Server
• مجموعه آموزش‌های دروس مهندسی کامپیوتر
• 13 دستور SQL مهم که هر برنامه‌نویسی باید بداند
• آموزش دستورهای SQL – مجموعه مقالات جامع وبلاگ فرادرس
• آموزش SQL Server Management Studio | کامل، رایگان و گام به گام
• MongoDB چیست؟ — راهنمای شروع با دیتابیس مانگو دی بی

==