در دستورات SQL هر تابع مجموعه ای از عملیات انجام می دهد و می تواند تغییراتی را بر داده های دیتابیس اعمال کند. در دیتابیس اوراکل می توان یک تابع جدید تعریف نمود یا از توابع از پیش تعریف شده استفاده کرد.  توابع از پیش تعریف شده برای اطلاعات با نوع داده های مختلف(کاراکتر، عددی، تاریخی و …) استفاده می شوند. در این فصل ساختار کلی توابع در SQL را عنوان می کنیم و تعدادی از توابع از پیش تعریف شده در دیتابیس اوراکل و روش استفاده از آنها را با ذکر مثال توضیح می دهیم.

توابع SQL می توانند یک یا چند آرگومان ورودی داشته باشند ولی تنها یک مقدار را به عنوان خروجی (output) برمی گردانند.

+ مقادیر ورودی یا همان آرگومنت ها می توانند عبارت، ستون، متغیر و یا یک مقدار ثابت باشند.

توابع SQL بر دو نوع می باشند:

–Single-Row: این مدل از توابع برای هر ردیف از جداول اعمال می شوند و یک نتیجه مشخص به ازای هرکدام از ردیف ها برمی گردانند. مانند توابع کاراکتری، اعداد، تاریخ، توابع تبدیل نوع داده و توابع عمومی.

–Multiple-Row: این دسته از توابع برای مجموعه ای از ردیف های جداول اعمال می شوند و به ازای هر مجموعه از ردیف ها یک خروجی مشخص بازمی گردانند.

 

توابع کاراکتری

ورودی توابع کاراکتری از نوع کاراکتر می باشد و خروجی آنها می تواند عدد یا کاراکتر باشد. در جدول زیر سه مورد از توابع کاراکتری را می بینید که ورودی آنها عبارت ‘SQL Course’ است.

—تابع LOWER: این تابع عبارت کاراکتری ورودی را به یک عبارت با حروف کوچک تبدیل می کند.

—تابع UPPER: این تابع عبارت کاراکتری ورودی را به یک عبارت با حروف بزرگ تبدیل می کند.

—تابع INITCAP: این تابع اولین حرف در هر کلمه از عبارت کاراکتری ورودی را به حرف بزرگ تبدیل می کند و سایر حروف را به صورت حروف کوچک نمایش می دهد.

مثال: می خواهیم اطلاعات شغلی کارمند Higgins را نمایش دهیم.

در اولین دستور select نام کارمند را به صورت ‘higgins’ جستجو می کند و هیچ نامی در دیتابیس پیدا نمی کند اما در قسمت دوم با تغییر شرط where مشخصات کارمند مورد نظر نمایش می یابد.

بنابراین اگر در دیتابیس نام این کارمند به صورت HIGGINS، Higgins یا … ثبت شده باشد همچنان دستور Select دوم نتیجه مورد نظر را نمایش می دهد.

همانطور که از مثال قبل مشخص می باشد نام کارمند در خروجی نمایش داده شده به همان قالبی که در دیتابیس درج گردیده بود هست.

گروه دیگری از توابع کاراکتری را همراه با یک مثال در جدول زیر می بینید.

—تابع CONCAT دو مقدار را به هم متصل می کند.

—تابع SUBSTR یک رشته کاراکتری با طول مشخص شده در آرگومنت سوم را از آرگومنت اول جدا کرده و بازمی گرداند. در ضمن دومین آرگومنت نشانگر نقطه شروع می باشد(نشانگر اولین حرف عبارت شماره 1 می باشد).

—تابع LENTGH طول یک رشته کاراکتری ورودی را محاسبه می کند و بر حسب یک عدد بازمی گرداند.

—تابع INSTR شماره محل قرارگیری یک حرف خاص که در آرگومنت دوم تعیین می گردد را باز می گرداند.

—تابع LPAD اگر طول رشته کاراکتری ورودی(آرگوکنت اول) کمتر از عدد آرگومنت دوم باشد از حرف آرگومنت سوم در سمت چپ آن رشته کاراکتری استفاده می گردد تا طول عبارت نهایی به عدد آرگومنت دوم برسد.

—تابع RPAD همانند LPAD می باشد ولی در سمت راست عبارت کاراکتری اعمال می گردد.

مثال:

LPAD(‘tech’, 8, ‘0’);

Result: ‘0000tech’

LPAD(‘tech on the net’, 16, ‘z’);

Result: ‘ztech on the net’

RPAD(‘tech’, 8, ‘0’)

Result: ‘tech0000’

SELECT SUBSTR(‘ABCDEFG’,3,4) “Substring”  FROM DUAL;

Result: ‘CDEF’

مثال:

+ توابع از نوع Single-Row می تواند به صورت تودرتو استفاده گردد. در این حالت ترتیب اعمال توابع از درونی ترین تابع آغاز می گردد.

مثال: در این مثال ابتدا تابع SUBSTR اجرا گردیده و نتیجه آن تابع به عنوان آرگومنت تابع CONCAT بازگردانی می گردد.همچنین در نهایت تابع UPPER اجرا می شود:

 

توابع عددی

این توابع و مثال آنها در جدول زیر نمایش داده شده اند.

—تابع ROUND دومین آرگومنت ورودی را به عنوان دقت اعشار در عدد آرگومنت اول اعمال می کند و نتیجه را به سمت بالا round کرده و برمی گرداند.

—تابع TRUNC همانند ROUND عمل می کند با این تفاوت که نتیجه را به سمت پایین round می کند.

—تابع CEIL یک عدد اعشاری دریافت می کند و مقدار صحیح آن که به سمت بالا round گردیده را بازمی گرداند.

—تابع FLOOR همانند تابع CEIL عمل می کند با این تفاوت که نتیجه نهایی به سمت پایین round می گردد.

—تابع MOD باقی مانده عمل تقسیم دو مقدار ورودی را باز می گرداند.

+ اگر در توابع ROUND و TRUNC آرگومنت دوم تعیین نشوداین مقدار به صورت پیش فرض برابر با 0 خواهد بود و اگر مقدار تعیین شده برای این آرگومنت منفی باشد در این صورت عملیات در سطح یکان یا دهگان و … اعمال می گردد.

مثال:

+ در دیتابیس اوراکل مالک جدول DUAL  کاربر SYS است ولی توسط تمام کاربران قابل دسترسی می باشد. این جدول شامل یک ستون به نام DUMMY می باشد.

از این جدول زمانی استفاده می گردد که همانند مثال قبل احتیاجی به دیتای جداول دیگر نیست و فقط نیاز است که یک مقدار ثابت نمایش داده شود. بنابراین به منظور کامل کردن دستور Select نام این جدول در عبارت  from ذکر می گردد.

مثال:

مثال: در این مثال به منظور نمایش اطلاعات کارمندانی که شماره EMPLOYEE_ID آنها زوج می باشد از تابع MOD استفاده شده است.

 

تاریخ در اوراکل و توابع تاریخی

قالب نمایش تاریخ در دیتابیس اوراکل به صورت DD-MON-RR(سال-ماه-روز) می باشد.

مثال:

+ زمانی که در دیتابیس یک رکورد با ستونی از نوع تاریخ درج می شود اطلاعات مربوط به قرن آن رکورد نیز از طریق تابع SYSDATE تعیین می گردد و همراه آن رکورد درج می شود. هر چند در زمان نمایش اطلاعات تاریخ قرن نمایش داده نمی شود. در واقع اطلاعات تاریخی در دیتابییس به قالب زیر درج می شوند:

ثانیه       دقیقه     ساغت     روز        ماه         سال       قرن

نکته: تابع SYSDATE زمان و تاریخ فعلی سیستم را نمایش می دهد. می توان از این تابع در قسمت مربوط به نام ستون هر عبارت select استفاده نمود ولی معمولا از نام جدول عمومی DUAL استفاده می گردد.

+ تابع SYSDATE تاریخ و زمان مربوط به سروری که دیتابیس اوراکل بر روی آن راه اندازی گردیده است را نمایش می دهد.

بنابراین اگر در یک Session خاص از سرور محلی خود به یک سرور دیتابیس در کشور دیگر متصل شویم و تابع SYSDATE را فراخوانی کنیم، تاریخ آن کشور مقصد نمایش داده می شود.

ولی اگر در این Session از تابع CURRENT_DATE استفاده گردد، تاریخ محلی نمایش داده می شود. تابع CURRENT_TIMESTAMP نیز زمان و تاریخ محلی را نمایش می دهد.

مثال:

SELECT CURRENT_DATE FROM DUAL;

26-MAY-14

SELECT CURRENT_TIMESTAMP FROM DUAL;

 

عملیات ریاضی در داده های از نوع تاریخ

از آنجایی که داده های از نوع تاریخ با فرمت عددی در دیتابیس ذخیره می گردند، می توان از عملیات جمع و تفریق برای آنها استفاده نمود. اگر در عملیات جمع یا تفریق یک عدد ثابت استفاده شود، روزهای آن تاریخ تغییر می کند. اگر مقدار آن عدد بر 24 تقسیم گردد تعداد ساعت مشخص خواهد شد. همچنین می توان مقدار دو تاریخ مختلف را جمع یا تفریق نمود.

مثال: نام کارمندان به همراه سابقه خدمتی آنها براساس تعداد هفته نمایش داده شود. (تعداد روزهایی که از زمان استخدام یک کارمند سپری شده است از تفاضل تاریخ سیستم و تاریخ استخدام آن کارمند بدست آمده است)

 توابع زیر مربوط به داده های از نوع تاریخ می باشند:

—تابع MONTHS_BETWEEN دو مقدار از نوع تاریخ به عنوان ورودی دریافت می کند و اختلاف ماه های آنها را برمی گرداند. اگر تاریخ ورودی اول جدیدتر باشد مقدار بازگشتی مثبت خواهد بود. در غیر این صورت یک مقدار منفی برگردانده می شود.

مثال:

—تابع ADD_MONTHS یک مقدار صحیح به عنوان آرگومنت دوم دریافت می کند و این مقدار را به عدد مربوط به ماه در تاریخ ورودی اضافه می کند( این مقدار صحیح می تواند یک عدد منفی باشد).

مثال:

—تابع NEXT_DAY یک مقدار کاراکتری که بیانگر یک روز خاص از هفته می باشد را توسط آرگومنت دوم دریافت می کند و تاریخی که آن روز از هفته برای بار اول رخ می دهد را بر می گرداند.

مثال:

—تابع LAST_DAY یک تاریخ به عنوان ورودی دریافت کرده و تاریخ آخرین روز در ماه آن تاریخ را برمی گرداند.

مثال:

نکته: می توان از توابع ROUND و TRUNC برای مقادیر از نوع تاریخ نیز استفاده نمود.

مثال: تاریخ 22 آگوست سال 03 را با تابع ROUND و TRUNC و بر اساس ماه و سال نمایش دهید.

ROUND( date [, format] )

ROUND(TO_DATE (’22-AUG-03′),’YEAR’)

Result: ’01-JAN-04′

ROUND(TO_DATE (’22-AUG-03′),’MONTH’)

Result: ’01-SEP-03′

TRUNC ( date [, format ] )

TRUNC(TO_DATE(’22-AUG-03′), ‘YEAR’)

Result: ’01-JAN-03′

TRUNC(TO_DATE(’22-AUG-03′), ‘MONTH’)

Result: ’01-AUG-03′