عبارات شاخه در دستور SELECT. اوراکل، وظایف معمولی SQL

تابع NVL

تابع NVL معمولاً بیشترین استفاده را دارد. تابع دو پارامتر دریافت می کند: NVL(expr1,expr2). اگر اولین پارامتر expr1 NULL نباشد، تابع مقدار خود را برمی‌گرداند. اگر پارامتر اول NULL باشد، تابع مقدار پارامتر دوم expr2 را برمی گرداند.

در نظر گرفتن مثال عملی. فیلد COMM در جدول EMP ممکن است حاوی باشد مقادیر NULL. هنگام اجرای یک پرس و جو مانند:

EMPNO، ENAME، COMM، NVL(COMM، 0) NVL_COMM را انتخاب کنید

از SCOTT.EMP

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

تابع CEIL(n).

تابع CEIL کوچکترین عدد صحیح را بزرگتر یا مساوی با عدد n ارسال شده به عنوان پارامتر برمی گرداند. مثلا:

SELECT CEIL(100) X1، CEIL(-100) X2، CEIL(100.2) X3، CEIL(-100.2) X4

از DUAL

TRUNC(n[,m])

تابع TRUNC عدد n کوتاه شده را به m رقم اعشار برمی گرداند. پارامتر m ممکن است حذف شود، در این صورت n به یک عدد صحیح کوتاه می شود.

SELECT TRUNC(100.25678) X1، TRUNC(-100.25678) X2، TRUNC(100.99) X3،

TRUNC (100.25678، 2) X4

از DUAL

تابع SIGN(n).

تابع SIGN علامت یک عدد را تعیین می کند. اگر n مثبت باشد، تابع 1 را برمی گرداند. اگر منفی باشد، -1 برمی گردد. اگر صفر باشد، 0 برگردانده می شود به عنوان مثال:

SELECT SIGN(100.22) X1، SIGN(-100.22) X2، SIGN(0) X3

از DUAL

ویژگی جالب این تابع قابلیت انتقال m است برابر با صفراین منجر به خطای تقسیم بر صفر نمی شود.

تابع POWER(n، m).

تابع POWER عدد n را به توان m می رساند. درجه می تواند کسری و منفی باشد که به طور قابل توجهی قابلیت های این تابع را گسترش می دهد.

SELECT POWER(10, 2) X1, POWER(100, 1/2) X2,

POWER(1000، 1/3) X3، POWER(1000، -1/3) X4

از DUAL

در برخی موارد، هنگام فراخوانی این تابع، ممکن است با آن مواجه شوید استثنا. مثلا:

SELECT POWER(-100، 1/2) X2

از DUAL

AT این موردسعی شده است جذر یک عدد منفی محاسبه شود که منجر به خطای ORA-01428 "Argument out of range" می شود.

تابع SQRT(n)

این تابعبرمی گرداند ریشه دوماز شماره n مثلا:

SQRT(100) X را انتخاب کنید

از DUAL

توابع EXP(n) و LN(n).

تابع EXP e را به توان n می رساند و تابع LN لگاریتم طبیعی n را محاسبه می کند (با n باید بزرگتر از صفر باشد). مثال:

SELECT EXP(2) X1، LN(1) X2، LN(EXP(2)) X3

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

توابع تو در تو

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

تابع1(پارامتر1، پارامتر2، ...) = نتیجه

جایگزین کردن یک پارامتر تابع با فراخوانی یک تابع دیگر می‌تواند به عباراتی مانند این منجر شود

F1(param1.1، F2(param2.1، param2.2، F3(param3.1))، param1.3)

توابع تو در تو ابتدا قبل از استفاده از نتایج آنها به عنوان مقادیر ورودی برای سایر توابع ارزیابی می شوند. توابع از عمیق ترین سطح تودرتو تا بالاترین از چپ به راست ارزیابی می شوند. عبارت قبلی به صورت زیر اجرا می شود

1. تابع F3(param1) ارزیابی می شود و مقدار بازگشتی به عنوان پارامتر سوم برای تابع 2 استفاده می شود، اجازه دهید آن را param2.3 بنامیم.
2. سپس تابع F2 (param1, param2.2, param2.3) ارزیابی می شود و مقدار برگشتی به عنوان پارامتر دوم تابع F1 - param1.2 استفاده می شود.
3. در نهایت تابع F1(param1, param2, param1.3) ارزیابی شده و نتیجه به برنامه فراخوانی بازگردانده می شود.

بنابراین، تابع F3 در سومین سطح تودرتو قرار دارد.

یک پرس و جو را در نظر بگیرید

next_day(last_day(sysdate)-7, 'tue') را از dual انتخاب کنید.

1. سه تابع در این پرس و جو وجود دارد، از سطح پایین تربه بالا - SYSDATE، LAST_DAY، NEXT_DAY. درخواست به این صورت است
2. تابع SYSDATE تو در تو اجرا می شود. جریان را برمی گرداند زمان سیستم. فرض کنید تاریخ فعلی 28 اکتبر 2009 است
3. سپس، نتیجه تابع سطح دوم LAST_DAY محاسبه می شود. LAST_DATE ('28-OCT-2009') آخرین روز اکتبر 2009، یعنی 31 اکتبر 2009 برمی گردد.
4. سپس هفت روز از این تاریخ کم می شود - 24 اکتبر به دست می آید.
5. در نهایت، تابع NEXT_DAY('24-OCT-2009'، 'Tue') ارزیابی می شود و پرس و جو آخرین سه شنبه ماه اکتبر را برمی گرداند - که در مثال ما 27-OCT-2009 است.

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

توابع شعبه

توابع شاخه، همچنین به عنوان IF-THEN-ELSE شناخته می شود، برای تعیین مسیر اجرا بسته به شرایط خاص استفاده می شود. توابع شاخه بر اساس نتیجه ارزیابی شرایط، نتایج متفاوتی را برمی‌گردانند. در گروه چنین توابعی، توابعی برای کار با مقدار NULL وجود دارد: NVL، NVL2، NULLIF و COALESCE. و همچنین توابع عمومی، با تابع DECODE و عبارت CASE نشان داده می شود. تابع DECODE است تابع اوراکل، در حالی که عبارت CASE در استاندارد ANSI SQL است.

تابع NVL

تابع NVL مقدار یک ستون یا عبارت از هر نوع داده را برای یک مقدار تهی بررسی می کند. اگر مقدار NULL باشد، یک مقدار پیش‌فرض غیر NULL جایگزین برمی‌گرداند، در غیر این صورت مقدار اصلی برگردانده می‌شود.

تابع NVL دارای دو است پارامترهای مورد نیازو نحو NVL (اصلی، ifnull) است که در آن اصل مقدار اصلی برای آزمایش است و ifnull نتیجه ای است که توسط تابع در صورت NULL بودن اصلی است. نوع داده پارامترهای ifnull و اصلی باید سازگار باشد. یعنی یا نوع داده باید یکسان باشد یا اینکه امکان تبدیل ضمنی مقادیر از یک نوع به نوع دیگر وجود داشته باشد. تابع NVL مقداری از نوع داده مشابه با نوع داده پارامتر اصلی را برمی‌گرداند. سه پرس و جو را در نظر بگیرید

Query 1: nvl(1234) را از dual انتخاب کنید.

Query 2: nvl(null, 1234) را از dual انتخاب کنید.

Query 3: nvl(substr('abc', 4), 'No substring exists') را از dual انتخاب کنید.

از آنجایی که تابع NVL به دو پارامتر نیاز دارد، query 1 یک خطای ORA-00909: تعداد نامعتبر آرگومان ها را برمی گرداند. Query 2 1234 را برمی گرداند زیرا NULL در حال بررسی است و NULL است. کوئری سه از یک تابع SUBSTR تودرتو استفاده می کند که سعی می کند کاراکتر چهارم را از یک رشته سه کاراکتری استخراج کند، NULL را برمی گرداند و تابع NVL رشته «No sbusstring وجود دارد» را برمی گرداند.

تابع NVL هنگام کار با اعداد بسیار مفید است. برای تبدیل مقادیر NULL به 0 استفاده می شود تا عملیات حسابیبیش از اعداد NULL برنگرداند

تابع NVL2

تابع NVL2 عملکرد بیشتری نسبت به NVL ارائه می دهد، اما NULL را نیز کنترل می کند. مقدار یک ستون یا عبارت از هر نوع را برای یک مقدار تهی بررسی می کند. اگر مقدار NULL نباشد، پارامتر دوم برگردانده می شود، در غیر این صورت پارامتر سوم برگردانده می شود، برخلاف تابع NVL که در این حالت مقدار اصلی را برمی گرداند.

تابع NVL2 دارای سه پارامتر مورد نیاز است و نحو NVL2 (اصل، ifnotnull، ifnull) است، که در آن original مقداری است که باید آزمایش شود، ifnotnull مقداری است که باید برگردانده شود اگر اصلی NULL نباشد، و ifnull مقداری است که باید برگردانده شود اگر اصلی باشد. خالی. انواع داده‌های پارامترهای ifnotnull و ifnull باید سازگار باشند و نمی‌توانند سازگار باشند LONG را تایپ کنید. نوع داده برگردانده شده توسط تابع NVL2 برابر با نوع داده پارامتر ifnotnull است. بیایید به چند نمونه نگاه کنیم

Query 1: nvl2 (1234, 1, 'a string') را از dual انتخاب کنید.

Query 2: nvl2 (null, 1234, 5678) را از dual انتخاب کنید.

پرس و جو 3: nvl2(substr('abc', 2), 'Not bc', 'No substring') را از dual انتخاب کنید.

پارامتر ifnotnull در query 1 یک عدد و پارامتر ifnull یک رشته است. از آنجایی که انواع داده ها ناسازگار هستند، خطای "ORA-01722: عدد نامعتبر" برگردانده می شود. پرس و جوی دو یک پارامتر ifnull را برمی گرداند زیرا اصلی NULL است و نتیجه آن 5678 است. پرس و جوی سه از تابع SUBSTR استفاده می کند که 'bc' را برمی گرداند و NVL2 ('bc', 'Not bc', 'No substring') را فراخوانی می کند - که یک ifnotnull برمی گرداند. پارامتر - 'نه bc'.

تابع NULLIF

تابع NULLIF دو مقدار را برای یکسان آزمایش می کند. اگر آنها یکسان باشند، NULL برگردانده می شود، در غیر این صورت اولین پارامتر برگردانده می شود. تابع NULLIF دو پارامتر مورد نیاز دارد و نحو NULLIF (ifunequal, krahasim_item) است. تابع دو پارامتر را با هم مقایسه می کند و اگر یکسان باشند، NULL و در غیر این صورت پارامتر ifunequal را برمی گرداند. درخواست ها را در نظر بگیرید

Query 1: nullif(1234, 1234) را از dual انتخاب کنید.

Query one NULL را برمی گرداند زیرا پارامترها یکسان هستند. رشته های پرس و جو 2 به تاریخ تبدیل نمی شوند، بلکه به عنوان رشته ها مقایسه می شوند. از آنجایی که طول رشته ها متفاوت است، پارامتر ifunequal 24-JUL-2009 برگردانده می شود.

در شکل 10-4، تابع NULLIF درون تابع NVL2 قرار گرفته است. تابع NULLIF به نوبه خود از توابع SUBSTR و UPPER به عنوان بخشی از عبارت در پارامتر ifunequal استفاده می کند. ستون EMAIL با این عبارت مقایسه می‌شود، که حرف اول نام همراه با نام خانوادگی را برای کارمندانی که نام کوچکشان 4 کاراکتر است را برمی‌گرداند. وقتی این مقادیر برابر باشند، NULLIF NULL را برمی گرداند، در غیر این صورت مقدار پارامتر ifunequal را برمی گرداند. این مقادیر به عنوان پارامتری برای تابع NVL2 استفاده می شود. NVL2 به نوبه خود شرحی از مطابقت یا عدم تطابق عناصر مقایسه شده را برمی‌گرداند.

شکل 10-4 - با استفاده از تابع NULLIF

تابع COALESCE

تابع COALESCE اولین مقدار غیر NULL را از لیست پارامترها برمی گرداند. اگر تمام پارامترها NULL باشند، NULL برگردانده می شود. تابع COALESCE دو پارامتر مورد نیاز و به تعداد دلخواه دارد پارامترهای اختیاریو نحو COALESCE(expr1, expr2, ..., exprn) که در آن اگر مقدار expr 1 NULL نباشد نتیجه expr1 است و در غیر این صورت اگر NULL نباشد نتیجه expr2 است و غیره. COALESCE از نظر معنی با توابع NVL تو در تو برابر است

COALESCE(expr1, expr2) = NVL(expr1, expr2)

COALESCE(expr1, expr2, expr3) = NVL(expr1,NVL(expr2, expr3))

نوع داده مقدار برگشتی در صورت یافتن مقدار غیر NULL برابر با نوع داده اولین مقدار غیر NULL است. برای جلوگیری از خطای «ORA-00932: انواع داده های ناسازگار»، همه پارامترهای غیر NULL باید با اولین پارامتر غیر NULL سازگار باشند. سه مثال را در نظر بگیرید

Query 1: coalesce (null, null, null, 'a string') را از dual انتخاب کنید.

Query 2: coalesce (null, null, null) را از dual انتخاب کنید.

Query 3: coalesce(substr('abc', 4), 'Not bc', 'No substring') را از dual انتخاب کنید.

Query 1 پارامتر چهارم را برمی گرداند: یک رشته، زیرا اولین پارامتر غیر NULL است. Query Two NULL را برمی گرداند زیرا تمام پارامترها NULL هستند. کوئری 3 پارامتر اول را ارزیابی می کند، NULL دریافت می کند و پارامتر دوم را برمی گرداند زیرا اولین پارامتر غیر NULL است.

اگر از قبل با تابع NVL آشنا باشید، پارامترهای تابع NVL2 می تواند گیج کننده باشد. اگر مقدار NULL نباشد، NVL (اصل، ifnull) اصل را برمی‌گرداند، در غیر این صورت ifnull. NVL2 (اصل، ifnotnull، ifnull) ifnotnull را برمی‌گرداند اگر اصلی نباشد در غیر این صورت ifnull. سردرگمی از این واقعیت ناشی می شود که پارامتر دوم تابع NVL ifnull است، در حالی که NVL2 دارای ifnotnull است. بنابراین به موقعیت پارامتر در تابع تکیه نکنید.

عملکرد DECO

تابع DECODE منطق if-then-else را با بررسی دو پارامتر اول برای برابری و برگرداندن مقدار سوم در صورت مساوی بودن و یا یک مقدار متفاوت در غیر این صورت پیاده سازی می کند. تابع DECODE دارای سه پارامتر مورد نیاز است و نحو آن DECODE (expr1, comp1, iftrue1, , ) است. این پارامترها همانطور که در مثال شبه کد زیر نشان داده شده است استفاده می شوند.

IF expr1=comp1 سپس iftrue1 را برگردانید

در غیر این صورت اگر expr1=comp2 سپس iftrue2 را برگردانید

در غیر این صورت اگر exprN=compN باشد، iftrueN را برگردانید

در غیر این صورت بازگشت NULL|iffalse;

ابتدا expr1 با comp1 مقایسه می شود. اگر مساوی باشند iftrue1 برگردانده می شود. اگر expr1 برابر با comp1 نباشد، آنگاه چه اتفاقی می افتد بستگی به این دارد که آیا پارامترهای comp2 و iftrue2 مشخص شده باشند. اگر داده شود، مقدار expr1 با comp2 مقایسه می شود. اگر مقادیر برابر باشند، iftrue2 برگردانده می شود. اگر نه، اگر جفت پارامتر compN، iftrueN وجود داشته باشد، expr1 و compN با هم مقایسه می‌شوند و اگر true باشد، iftrueN در صورت برابری برگردانده می‌شود. اگر در هیچ مجموعه ای از پارامترها مطابقت یافت نشد، اگر این پارامتر مشخص شده بود، iffalse برگردانده می شود یا NULL.

تمام پارامترهای تابع DECODE می توانند عبارت باشند. نوع مقدار بازگشتی برابر با نوع اولین اعتباردهنده - پارامتر است comp 1. Expression expr 1 به طور ضمنی به نوع داده پارامتر comp تبدیل می شود1. هر کس دیگری گزینه های موجودمقایسه 1 … compN همچنین به طور ضمنی به نوع comp تبدیل می شوند 1. DECODE یک مقدار NULL را برابر با مقدار NULL دیگر در نظر می گیرد، یعنی. اگر expr1 NULL و comp3 NULL و comp2 NULL نیست، iftrue3 برگردانده می شود. بیایید به چند نمونه نگاه کنیم

Query 1: decode(1234, 123, '123 is a match') را از dual انتخاب کنید.

Query 2: decode(1234, 123, '123 is a match', 'No match') را از dual انتخاب کنید.

Query 3: decode('search', 'comp1', 'true1', 'comp2', 'true2', 'search', 'true3', substr('2search', 2, 6), 'true4', ' را انتخاب کنید false') از dual;

کوئری یک مقدار 1234 و 123 را با هم مقایسه می کند. از آنجایی که آنها برابر نیستند، iftrue1 نادیده گرفته می شود، و از آنجایی که iffalse تعریف نشده است، NULL برگردانده می شود. کوئری دو با پرس و جو 1 یکسان است با این تفاوت که مقدار iffalse تعریف شده است. از آنجایی که 1234 برابر با 123 نیست، اگر اشتباه باشد - "بدون مطابقت" برگردانده می شود. کوئری سه مقادیر پارامتر را در برابر مقدار جستجو بررسی می کند. پارامترهای comp1 و comp2 برابر با 'جستجو' نیستند، بنابراین نتایج iftrue1 و iftrue2 نادیده گرفته می شوند. یک مطابقت در سومین عملیات مقایسه عنصر comp3 (موقعیت پارامتر 6) یافت می شود و مقدار بازگشتی iftrue3 (پارامتر 7) 'true3' است. از آنجایی که مطابقت یافت می شود، دیگر محاسباتی انجام نمی شود. یعنی علیرغم اینکه مقدار comp4 (پارامتر 8) با expr1 نیز مطابقت دارد، این عبارت هرگز ارزیابی نمی شود زیرا مطابقت در مقایسه قبلی پیدا شده است.

عبارت CASE

تمام زبان های برنامه نویسی نسل سوم و چهارم ساختار case را پیاده سازی می کنند. مانند تابع DECODE، عبارت CASE به شما امکان می دهد منطق if-then-else را پیاده سازی کنید. دو گزینه برای استفاده از عبارت CASE وجود دارد. مجموعه دستورات CASE ساده عنصر اصلییک بار مقایسه کنید و سپس همه را فهرست کنید شرایط لازمچک ها CASE پیچیده (جستجو شده) هر دو عبارت را برای هر شرط ارزیابی می کند.

عبارت CASE دارای سه پارامتر مورد نیاز است. نحو یک عبارت به نوع آن بستگی دارد. برای یک عبارت CASE ساده به نظر می رسد این است

CASE search_expr

WHEN krahasim_expr1 سپس iftrue1

)

تابع TRUNC عدد n کوتاه شده را به m رقم اعشار برمی گرداند. پارامتر m ممکن است حذف شود، در این صورت n به یک عدد صحیح کوتاه می شود.

SELECT TRUNC(100.25678) X1، TRUNC(-100.25678) X2، TRUNC(100.99) X3،

TRUNC (100.25678، 2) X4

از DUAL

تابع SIGN(n).

تابع SIGN علامت یک عدد را تعیین می کند. اگر n مثبت باشد، تابع 1 را برمی گرداند. اگر منفی باشد، -1 برمی گردد. اگر صفر باشد، 0 برگردانده می شود به عنوان مثال:

SELECT SIGN(100.22) X1، SIGN(-100.22) X2، SIGN(0) X3

از DUAL

یکی از ویژگی های جالب این تابع امکان پاس کردن m برابر با صفر است - در این حالت خطای تقسیم بر 0 وجود ندارد.

تابع POWER(n، m).

تابع POWER عدد n را به توان m می رساند. درجه می تواند کسری و منفی باشد که به طور قابل توجهی قابلیت های این تابع را گسترش می دهد.

SELECT POWER(10, 2) X1, POWER(100, 1/2) X2,

POWER(1000، 1/3) X3، POWER(1000، -1/3) X4

از DUAL

در برخی موارد، ممکن است در هنگام فراخوانی این تابع یک استثنا رخ دهد. مثلا:

SELECT POWER(-100، 1/2) X2

از DUAL

در این حالت سعی می شود جذر یک عدد منفی محاسبه شود که منجر به خطای ORA-01428 "Argument out of range" می شود.

تابع SQRT(n)

این تابع جذر n را برمی گرداند. مثلا:

SQRT(100) X را انتخاب کنید

از DUAL

توابع EXP(n) و LN(n).

تابع EXP e را به توان n می رساند و تابع LN لگاریتم طبیعی n را محاسبه می کند (با n باید بزرگتر از صفر باشد). مثال:

SELECT EXP(2) X1، LN(1) X2، LN(EXP(2)) X3