توابعی که در این متن توضیح داده می شوند، داده های دریافتی از نوع کاراکتری، عددی یا تاریخ را به نوع دیگر تبدیل می کنند. در ادامه توابعی که مربوط به مقادیر NULL می باشند معرفی می گردند. همچنین روش استفاده از عبارات شرطی با منطق IF THEN-ELSE توضیح داده می شود.

گاهی اوقات در یک دستور SQL احتیاجی به نوع خاصی از داده ها می باشد. اگر اطلاعات مورد نظر از نوع داده دیگر باشند عمل تبدیل نوع داده انجام می گیرد. در دیتابیس اوراکل این عمل تبدیل به دو روش IMPLICIT(تلویحی) و EXPLICIT(صریح) انجام می گردد.

1.روش IMPLICIT توسط دیتابیس اوراکل و به صورت اتوماتیک انجام می شود.

2.روش EXPLICIT توسط کاربر یا برنامه نویس استفاده می شود.

فرمت دستور تبدیل داده در روش EXPLICIT به شکل زیر است:  

    <<  نوع داده  TO  نوع داده>> 

+ اوراکل پیشنهاد می کند تا حد امکان در دستورات SQL از روش EXPLICIT استفاده شود.

تبدیل نوع داده به روش IMPLICIT

در این روش عمل تبدیل نوع داده های جدول زیر به صورت اتوماتیک توسط دیتابیس اوراکل انجام می شود.

+ در عمل تبدیل کارکتر به عدد باید رشته کاراکتری شامل یک عدد باشد.

مثال: در دستور SELECT زیر، ستون job_id از نوع داده ای کاراکتر می باشد بنابراین دیتابیس اوراکل به صورت اتوماتیک و به روش IMPLICIT عدد 2 را به رشته کاراکتری “2” تبدیل می نماید.

SQL> SELECT * FROM EMPLOYEES WHERE JOB_ID=2;

مثال: در دستور SELECT زیر، رشته کاراکتری ’01-JAN-90’ به روش IMPLICIT به داده از نوع تاریخ تبدیل می شود و در عبارت WHERE استفاده می گردد.

SQL>SELECT NAME FROM EMPLOYEES WHERE HIRE_DATE > ’01-JAN-90’;

 

تبدیل نوع داده به روش EXPLICIT

سه تابع زیر به روش EXPLICIT عمل تبدیل نوع داده را انجام می دهند:

TO_CHAR(number|date [, fmt [, nlsparams] ] )

TO_NUMBER(char[,fmt[, nlsparams]])

TO_DATE(char[,fmt[,nlsparams]])

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

پارامتر nlsparams قالب کاراکترهایی که باید برای برخی نشانگرهای عددی مانند علامت دسیمال استفاده گردد را مشخص می کند. این پارامتر معمولا تعریف نمی شود.

+ اگر هر کدام از پارامترهای ورودی این توابع تعریف نشوند از مقدار پیش فرض آنها در session استفاده می گردد.

مثال: مدل فرمت تاریخ ’11-NOV-2000’ برابر با ‘DD-MON-YYYY’ می باشد.

مثال: در دستور SELECT زیر تاریخ استخدام کارمند Higgins با مدل فرمت دلخواه کاراکتری ‘MM/YY’ نمایش می یابد.

SQL>SELECT employee_id, TO_CHAR(hire_date, ‘MM/YY’) Month_Hired FROM   employees WHERE  last_name = ‘Higgins’;

مثال: در این دستور نام افراد همراه با تاریخ استخدام آنها به صورت یک رشته کاراکتری با فرمت دلخواه نمایش می یابد. توجه شود که عبارت fm قبل از DD سبب ایجاد خوانایی بهتر در اعداد خروجی می شود.

+ برای تبدیل مقادیر عددی به مقادیر کاراکتری از تابع TO_CHAR استفاده می شود.

برخی از فرمت هایی که در تابع TO_CHAR قابل استفاده می باشند را در جدول زیر می بینید.

مثال: حقوق کارمند ERNEST با فرمت ‘$99,999.00’ نمایش داده شود.

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

توابع عمومی

این دسته از توابع با مقادیر NULL سروکار دارند و عملیات این توابع بر اساس NULL بودن یا نبودن مقدارها انجام می شود.

تابع NVL

فرمت کلی این تابع به صورت (NVL(EXP1,EXP2 می باشد. این تابع یک مقدار NULL را به یک مقدار حقیقی تبدیل می کند. البته توجه شود که نوع داده های EXP1 وEXP2 باید یکسان باشند. زمانی که از این تابع استفاده می گردد اگر EXP1 شامل یک مقدار NULL باشد مقدار EXP2 جایگزین آن می شود.

مثال:

NVL(commission_pct,0)

NVL(hire_date,’01-JAN-97′)

NVL(job_id,’No Job Yet’)

مثال: در فرمول مثال زیر برای محاسبه درآمد سالیانه تمام کارمندان باید مقادیرمربوط به ستون حق کمیسیون آنها مقداری غیر از NULL باشد. لذا مقدار این ستون برای کارمندانی که حق کمیسیون نگرفته بودند برابر با 0 شده است.

تابع NVL2

این تابع به صورت NVL2(EXP1,EXP2,EXP3) تعریف می گردد. اگر مقدار EXP1 یک مقدار NULL باشد مقدار EXP3 باز می گردد و در غیر این صورت مقدار EXP2 برگردانده خواهد شد.

مثال: اگر کارمندان حق کمسیون دریافت کرده بودند در ستون INCOME عبارت ‘SAL+COMM’ چاپ گردد و در غیر  این صورت عبارت ‘SAL’ چاپ گردد.

تابع NULLIF

این تابع به صورت NULLIF(EXP1,EXP2) بکار می رود و اگر مقدار EXP1 و EXP2 برابر بود مقدار NULL بازمی گردد و در غیر این صورت مقدار EXP1 بازگردانده می شود.

مثال: در این مثال تعداد حروف به کار رفته در نام و نام خانوادگی کارمندان  در ستون های EXP1 و EXP2 نمایش داده می شود. اگر این مقدارها برابر بودند در ستون RESULT مقدار NULL چاپ می گردد و در غیر این صورت مقدار EXP1 چاپ می شود.

تابع COALESCE

این تابع به صورت COALESCE(EXP1,EXP2,…,EXPn) تعریف می گردد. طرز کار این تابع همانند تابع NVL می باشد با این تفاوت که چندین مقدار EXP را به ترتیب برای NULL بودن بررسی کند و هر کدام NULL بود به سراغ بعدی برود. به عبارتی دیگر اولین مقدار EXP غیر NULL بازگردانده می شود.

مثال: در این مثال میزان حقوق کارمندان برای سال جدید محاسبه می گردد. اگر کارمندان حق کمسیون دریافت نکرده باشند حقوق آنها 2000 واحد اضافه می گردد و اگر دریافت کرده باشند به نسبت حق کمیسیون دریافتی افزایش حقوق خواهد داشت.

عبارات شرطی با منطق IF-THEN-ELSE

در دستورات SQL به منظور پیاده سازی منطق IF-THEN-ELSE از دو روش CASE و DECODE استفاده می شود.

روش CASE

در این روش می توان از منطق شرطی بدون نیاز به فراخوانی هیچ پروسیجری استفاده نمود. اگر هر کدام از COMPARISON_EXPR ها برابر با EXP بود مقدار RETURN_EXPR مربوط به آن بازگردانده می شود.

+ نوع داده ی EXPR و تمامی COMPARISION_EXPRها باید یکسان باشد. همجنین نوع داده ی تمام return_expr ها باید یکسان باشد.

نکته: اگر هیچ کدام از شرط های CASE صدق نکند مقدار NULL بازگردانده می شود.

مثال: افزایش حقوق کارمندان بر اساس حوزه فعالیت آنها. همچنین اگر کارمندی در 3 حوزه  IT_PROG، ST_CLERK،  SA_REP فعالیت نکند، افزایش حقوق نخواهد داشت.

+ می توان از روش CASE جهت جستجوی موارد خاص استفاده نمود.

مثال: کارمندان را بر اساس میزان حقوق دریافتی در ستون خروجی qualified_salary به سه دسته Low Medium و Good تقسیم کنید.

تابع DECODE

طرز کار این تابع مانند روش CASE می باشد و همان منطق IF-THEN-ELSE را پیاده سازی می کند.

مثال: افزایش حقوق کارمندان بر اساس حوزه فعالیت آنها. در ضمن اگر کارمندان در 3 حوزه  IT_PROG، ST_CLERK،  SA_REP فعالیت نکنند، افزایش حقوق نخواهند داشت.

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