در این قسمت پروسیجر(PROCEDURE) و روش استفاده از آن را توضیح می دهیم ولی در ابتدا لازم است با مفهوم زیربرنامه یا SUBPROGRAM آشنا باشیم.
در زبان PL/SQL، زیربرنامه یک واحد از برنامه است که کار خاصی را انجام می دهد. برنامه های اصلی از ترکیب این زیربرنامه ها تشکیل می شوند و به این ترتیب مفهوم طراحی MODULAR شکل می گیرد. هر زیربرنامه می تواند بوسیله یک برنامه یا توسط یک زیربرنامه دیگر اجرا شود.
زیربرنامه های PL/SQL در واقع بلاک های PL/SQL هستند که برای آنها یک نام در نظر گرفته می شود و می توانند با تعدادی پارامتر فراخوانی شوند.
انواع زیربرنامه های PL/SQL
1.تابع(function): فقط یک مقدار را بر می گرداند و معمولا از آنها برای محاسبات ریاضی و بازگشت نتیجه استفاده می شود.
2.پروسیجر(procedure): به طور مستقیم مقداری برنمی گرداند و از آنها برای اجرای عملیات خاص استفاده می شود.
کجا می توان یک زیربرنامه ساخت؟
1.در سطح SCHEMA
به این نوع از زیربرنامه ها STANDALONE می گویند و در داخل دیتابیس اوراکل ذخیره می شوند. زیربرنامه های STANDALONE با دستور CREATE PROCEDURE یا CREATE FUNCTION ساخته می شوند و با دستور DROP می توان آنها را حذف نمود.
2.درون یک پکیج
می توان در داخل پکیچ یک زیربرنامه را ساخت و در دیتابیس ذخیره کرد ولی زمانی که آن پکیج حذف گردد این زیربرنامه نیز حذف می شود.
3.داخل یک بلاک PL/SQL
اجزای یک زیربرنامه در PL/SQL
در فصل اول اجزای بلاک های ANONYMOUS را توضیح دادیم. همانند این بلاک ها، زیریرنامه ها هم شامل سه قسمت هستند:
1.قسمت declarative
استفاده از قسمت DECLARE اختیاری است البته برای زیربرنامه ها این قسمت با کلمه کلیدی DECLARE شروع نمی شود. در این قسمت متغیرها، CONSTANTها، نوع داده ها ، EXCEPTION و CRUSOR تعریف می شود. مواردی که در DECLARE تعریف می شوند فقط برای آن زیربرنامه به صورت محلی قابل دسترس است و با پایان اجرای زیربرنامه از بین می روند.
2.قسمت اجرایی
این قسمت اجباری است و شامل دستورات اجرایی آن زیربرنامه است.
3.قسمت EXCEPTION HANDLING
این قسمت اختیاری است و شامل دستوراتی می شود که در زمان رخداد خطای زیربرنامه، باید اجرا گردند.
ساخت پروسیجر
در ادامه سینتکس ساخت پروسیجر را می بینید:
CREATE [OR REPLACE] PROCEDURE procedure_name
[(parameter_name [IN | OUT | IN OUT] type [, …])]
{IS | AS}
BEGIN
< procedure_body >
END procedure_name;
در این سینتکس استفاده از پارامترها اختیاری است. همچنین معمولا زمانی که یک پروسیجر از نوع STANDALONE در داخل دیتابیس تعریف می شود بجای IS از کلمه کلیدی AS استفاده می گردد.
مثال: نمایش رشته HELLO WORLD توسط یک پروسیجر STANDALONE.
CREATE OR REPLACE PROCEDURE greetings
AS
BEGIN
dbms_output.put_line(‘Hello World!’);
END;
/
روش اجرای پروسیجرهای STANDALONE
به دو روش می توان پروسیجرهای STANDALONE را اجرا کرد.
1.با استفاده از کلمه کلیدی EXECUTE
2.فراخوانی نام آن پروسیجر در داخل یک بلاک PL/SQL
مثال: اجرای پروسیجر GREETING به دو روش:
روش 1:
EXECUTE greetings;
روش 2:
BEGIN
greetings;
END;
/
خروجی اجرای پروسیجر در این دو روش:
Hello World
PL/SQL procedure successfully completed.
پاک کردن پروسیجر STANDALONE
برای حذف کردن یک پروسیجر باید از دستور DROP استفاده نمود.
DROP PROCEDURE greetings;
انواع پارامترهای زیربرنامه در PL/SQL
1.پارامتر از نوع IN
با استفاده از پارامتر IN می توان یک مقدار، عبارت، LITERAL ،CONSTANT یا متغیری که مقدار گرفته را به داخل زیربرنامه ارسال نمود. این پارامتر به صورت فقط خواندنی است و نمی توان آن را تغییر داد.
2.پارامتر از نوع OUT
این پارامتر یک مقدار را به برنامه فراخواننده بازمی گرداند. پارامتر OUT مانند یک متغیر عمل می کند که می توان مقدار آن را تغییر داد و دوباره به این مقدار جدید اشاره کرد. بنابراین پارامتر اصلی باید از نوع متغیر باشد که مقدار نهایی آن بازگردانده می شود.
3.پارامترهای از نوع IN OUT
این نوع پارامترها یک مقدار اولیه به داخل زیربرنامه ارسال می کنند ولی مقدار بروز شده را به فراخواننده بر می گردانند. همچنین پارامتر اصلی IN OUT باید از نوع متغیر باشد.
مثال 1: با پارامتر IN مقدارهای ورودی را دریافت کنید و مقدار حداقل آن را در پارامتر OUT قرار دهید.
DECLARE
a number;
b number;
c number;
PROCEDURE findMin(x IN number, y IN number, z OUT number) IS
BEGIN
IF x < y THEN
z:= x;
ELSE
z:= y;
END IF;
END;
BEGIN
a:= 23;
b:= 45;
findMin(a, b, c);
dbms_output.put_line(‘ Minimum of (23, 45) : ‘ || c);
END;
/
خروجی:
Minimum of (23, 45) : 23
PL/SQL procedure successfully completed.
نکته: در مثال بالا a، b و c پارامترهای اصلی و x ، y و z پارامترهای رسمی هستند.
مثال 2: محاسبه و بازگرداندن توان دوم ورودی:
DECLARE
a number;
PROCEDURE squareNum(x IN OUT number) IS
BEGIN
x := x * x;
END;
BEGIN
a:= 23;
squareNum(a);
dbms_output.put_line(‘ Square of (23): ‘ || a);
END;
/
خروجی
Square of (23): 529
PL/SQL procedure successfully completed.
روش های ارسال پارامتر
پارامترهای اصلی را می توان به سه روش به زیربرنامه ها ارسال نمود.
1.POSIIONAL NOTATION:
همان حالت عادی برای ارسال پارامترهای اصلی به پارامترهای رسمی است.
مثال:
findMin(a, b, c, d);
2.NAMED NOTATION:
به روش زیر و با علامت <= می توان پارامتر اصلی را به پارامتر رسمی پاس داد.
findMin(x=>a, y=>b, z=>c, m=>d);
3.می توان ترکیبی از روش یک و دو استفاده کرد. البته ابتدا باید از روش POSITIONAL استفاده نمود.
مثال 1: این روش مجاز است:
findMin(a, b, c, m=>d);
مثال 2: این روش مجاز نیست:
findMin(x=>a, b, c, d);
مرسی، عالی