عبارات شعبه در دستور SELECT. تبدیل NVL برای انواع داده های Nvl sql توضیحات

تابع NVL

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

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

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

از SCOTT.EMP

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

EMPNO	ENAME	COMM	NVL_COMM
7369	اسمیت		0
7499	آلن	300	300
7521	بخش	500	500
7566	جونز		0
7654	مارتین	1400	1400
7698	بلیک		0
7782	کلارک		0
7839	پادشاه		0
7844	TURNER	0	0
7900	جیمز		0
7902	آب کم عمق		0
7934	میلر		0

تابع 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 برابر صفر بدون خطای تقسیم بر 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

X1	X2	X3	X4
100	10	10	0,1

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

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

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

توابع تو در تو

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

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

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

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

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

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

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

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

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

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

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

توابع شعبه

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

تابع NVL

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

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

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

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

پرس و جو 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 انتخاب کنید.

Query 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 است و در غیر این صورت نتیجه expr2 است. اگر NULL نباشد و غیره. 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 با هم مقایسه می‌شوند و 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

)

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

TRUNC(100.25678، 2) X4

از DUAL