تبدیل NVL برای انواع داده های مختلف. عبارات انشعاب در عبارت SELECT

تابع 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 را برمی گرداند. اگر n منفی باشد، -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

V در این موردسعی شده است جذر یک عدد منفی محاسبه شود که منجر به خطای 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

تابع TO_CHAR با اعداد

توابعی برای تبدیل داده ها به انواع داده های دیگر. TO_CHAR (شماره) یک عدد را به متن تبدیل می کند. TO_NUMBER (رشته) متن را به عدد تبدیل می کند.

SELECT TO_CHAR (123) FROM DUAL رشته 123 را برمی گرداند، SELECT TO_NUMBER ('12345") FROM DUAL 12345 را برمی گرداند.

کار آزمایشگاهی. تغییر فرمت اعداد نمایش داده شده

تغییر در قالب مقادیر عددی در Oracle SQL، تابع TO_CHAR برای کار با مقادیر عددی.

ورزش:

درخواستی بنویسید که نام، نام خانوادگی و اطلاعات حقوق و دستمزد کارمندان را از جدول hr.employees، در قالب نشان داده شده در شکل 1 نشان دهد. 3.4-1:

برنج. 3.4 -1

در این حالت، داده ها باید به گونه ای مرتب شوند که اولین خطوط برای کارکنان با بالاترین حقوق نمایش داده شود.

توجه داشته باشید:

برخی از مقادیر حقوق در شکل. 3.4-1 تغییر کرده اند، بنابراین ممکن است با مقادیر شما مطابقت نداشته باشند.

راه حل:

first_name را به عنوان "نام"، نام خانوادگی را به عنوان "نام خانوادگی"، نام خانوادگی را به عنوان "نام خانوادگی"، TO_CHAR (حقوق، "L999999999.99") به عنوان "حقوق" از hr.employees سفارش دهید.

توابع TO_NUMBER و TO_DATE

تابعی برای تبدیل رشته به تاریخ TO_DATE (رشته، قالب). مقادیر ممکنقالب‌ها قبلاً در بالا مورد بحث قرار گرفته‌اند، بنابراین من چندین مثال از استفاده از این تابع را ارائه خواهم داد. مثال ها:

انتخاب کنید TO_DATE ("01.01.2010"، "DD.MM.YYYY") از جانب DUAL تاریخ "01.01.2010" را برمی گرداند.

انتخاب کنید TO_DATE ("01.JAN.2010"، "DD.MON.YYYY") از جانب DUAL تاریخ "01.01.2009" را برمی گرداند.

انتخاب کنید TO_DATE ("15-01-10"، "DD-MM-YY") از جانب DUAL تاریخ "01/15/2010" را برمی گرداند.

تابع تبدیل رشته به رشته مقدار عددی TO_NUMBER (رشته، قالب). رایج ترین مقادیر فرمت در جدول ذکر شده است، بنابراین بیایید به استفاده از این تابع با مثال نگاه کنیم. مثال ها:

انتخاب کنید TO_NUMBER ('100") از جانب DUAL عدد 100 SELECT TO_NUMBER (`0010.01، "9999D99") را برمی گرداند. از جانب DUAL 10.01 برمی گردد.

انتخاب کنید TO_NUMBER ("500000"، "999G999") از جانب DUAL 500000 برمی گرداند.

عنصر RR در قالب تاریخ

عنصر فرمت تاریخ و زمان RR مشابه عنصر قالب تاریخ و زمان YY است، اما انعطاف‌پذیری بیشتری برای ذخیره مقادیر تاریخ در قرن‌های دیگر فراهم می‌کند. عنصر قالب RR datetime به شما امکان می دهد تاریخ های قرن بیستم تا قرن بیست و یکم را تنها با مشخص کردن دو رقم آخر سال ذخیره کنید.

اگر دو رقم آخر سال جاری از 00 تا 49 باشد، سال برگشتی همان دو رقم اول سال جاری را دارد.

اگر دو رقم آخر سال جاری بین 50 تا 99 باشد، 2 رقم اول سال برگشتی 1 بزرگتر از 2 رقم اول سال جاری است.

اگر دو رقم آخر سال جاری از 00 تا 49 باشد، پس 2 رقم اول سال برگشتی 1 کمتر از دو رقم اول سال جاری است.

اگر دو رقم آخر سال جاری بین 50 تا 99 باشد، سال برگشتی همان دو رقم اول سال جاری را دارد.

تابع NVL

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

مثال: NVL (supplier_city، n / a ") را از تامین کنندگان انتخاب کنید:

در صورت وجود فیلد source_city، عبارت SQL بالا n / " را برمی گرداند مقدار صفر... در غیر این صورت، مقدار source_city را برمی گرداند.

مثال دیگری از استفاده از تابع NVL در Oracle / PLSQL این است:

provider_id، NVL (supplier_desc، source_name) را از تامین کنندگان انتخاب کنید.

این دستور SQL باز خواهد گشت تامین کننده_ناممیدان اگر تامین کننده_ توصیفحاوی یک مقدار تهی است. در غیر این صورت برمی گردد تامین کننده_ توصیف.

مثال آخر: استفاده از تابع NVL در Oracle / PLSQL به این صورت است: NVL (کمیسیون، 0) را از فروش انتخاب کنید.

این دستور SQL 0 را برگرداند if کمیسیونفیلد حاوی مقدار تهی است. وگرنه برمی گشت کمیسیونرشته.

تبدیل NVL برای انواع داده های مختلف

تابع NVL برای تبدیل یک مقدار تعریف نشده به یک مقدار واقعی استفاده می شود: NVL ( بیان 1، بیان 2), جایی که:

بیان 1-مقدار اصلی یا محاسبه شده، که ممکن است تعریف نشده باشد.

بیان 2- مقداری که جایگزین مقدار تعریف نشده می شود.

توجه داشته باشید:تابع NVL می تواند برای تبدیل هر نوع داده ای استفاده شود، اما نتیجه همیشه همان نوع خواهد بود بیان 1.

تبدیل NVL برای انواع متفاوت:

NUMBER - NVL (ستون عددی، 9).

CHAR یا VARCHAR2 - NVL (نمادها | ستون،"در دسترس نیست").

کار آزمایشگاهی. استفاده از تابع NVL

تابع NVL برای کار با مقادیر تهی در Oracle SQL.

ورزش:

درخواستی بنویسید که اطلاعات مربوط به نام و نام خانوادگی کارمندان را از جدول hr.employees و همچنین نرخ کمیسیون (ستون COMMISSION_PCT) برای کارمند نشان دهد. در این مورد، برای آن دسته از کارمندانی که کمیسیون برای آنها تعریف نشده است، باید مقدار 0 را نمایش دهید. نتیجه پرس و جو باید همان چیزی باشد که در شکل نشان داده شده است. 3.5-1.

برنج. 3.5 -1 (نمایش مقادیر با شروع خط 51)

راه حل:

کد درخواست مربوطه می تواند به صورت زیر باشد:

first_name را به‌عنوان «نام»، نام خانوادگی را به‌عنوان «نام خانوادگی»، NVL (COMMISSION_PCT، 0) به‌عنوان «نرخ کمیسیون» از hr.employees انتخاب کنید.

انتخابات در راه است، یعنی امروز استاندار را انتخاب می کنیم. یا بهتر بگوییم مانعی برای عبور نامزدها به دور بعدی تعیین کنند. اما ابتدا باید اصطلاحات را تعریف کنید.

انتخاب تضمینی در SQL چیست؟فرض کنید در شرایط پرس و جو برای جدول، مقایسه ای بین یک فیلد و یک متغیر انجام می شود. بسته به مقدار این متغیر، پرس و جو ممکن است سطرهایی را از جدول برگرداند یا ندهد. اگر مقدار متغیر از بین برود که ردیف‌های جدول برگردانده نمی‌شوند، برای این مورد لازم است که یک نتیجه از پیش تعیین‌شده سمت چپ ایجاد شود. یعنی در هر صورت درخواست کلی باید ضمانت برگرداندن چیزی باشد. خود این اصطلاح از اینجا گرفته شده است. با این حال، کار با این واقعیت پیچیده است (و شاید برعکس، ساده شده است) که به جای یک سلول ساده با یک مقدار، باید تضمین کنیم که یک رشته کامل را برگردانیم.

من به داده های کمیسیون مرکزی انتخابات استناد می کنم. دور اول رای گیری با نتایج زیر به پایان رسید

انتخاب جدول را با t (شناسه، نام، حرفه، آرا) به عنوان (انتخاب 1، "غیر فاسد ناپذیر چیستوروکویچ"، "دادستان"، 9867 از اتحادیه دوگانه همه انتخاب کنید 2، "بودجه موثر اسوویلوویچ"، "تجار"، 8650 از اتحادیه دوتایی همه انتخاب کنید 3، "True Chtodadut Wiszajlovich"، "سردبیر روزنامه"، 745 از اتحادیه دوگانه، همه منتخب 4، "Gracious Lucifera Feoktistovna"، "abbass"، 234 from dual Union همه 5 انتخاب، "Khrensgory Ktotakoy Niktoneznaevich" , " معلم مدرسه روستایی " ، 3 از دوگانه) * از t; تغییر جدول انتخاب اضافه کنید کلید اصلی(شناسه)؛
پرس و جو اولیه که ورود نامزدها به دور بعدی را تعیین می کند بسیار ساده است:
انتخاب * از انتخاب جایی که آرا>: محدود شده است
فرض کنیم که آستانه قبولی 8000 رای باشد. با ترکیب این عدد با: محدود می‌شویم

اما اگر آستانه قبولی 10000 باشد، که بیش از حداکثر تعداد آرا است، چه می‌شود؟ پس بدیهی است که هیچ یک از نامزدهای فوق الذکر به دور بعدی راه پیدا نخواهند کرد. در این صورت دیکتاتوری برقرار می شود و سرهنگ گربه ای خود به خود فرماندار منطقه می شود. در اینجا برخی از راه های اختصاص آن وجود دارد:

روش 1. UNION ALL جداول با خود انباشته

با t به عنوان (انتخاب * از انتخابات که در آن آرا>: محدود شده) شناسه، نام، حرفه، آرای t اتحادیه را انتخاب کنید، همه 0، "Conel"، "cat"، null از t با تعداد (*) = 0 مرتبه بر اساس رای انتخاب کنید. توصیف

روش 2... میزهای UNION ALL با DUAL
با t به عنوان (انتخاب * از انتخابات که در آن رای ها>: محدود شده) شناسه، نام، حرفه، آرا از t اتحادیه همه 0 را انتخاب کنید، "Conel"، "cat"، null از dual جایی که وجود ندارد (انتخاب null از t) ترتیب با آرا توصیف

روش 3. LEFT JOIN جداول با DUAL
nvl (e.id، 0) id، nvl2 (e.id، e.name، "Conel") نام، nvl2 (e.id، e.profession، "cat") حرفه، e.رای از دو سمت چپ به انتخابات e on e.votes> بپیوندید
برای مواردی که جدول دارای یک فیلد NOT NULL منحصر به فرد نیست
nvl2 (e.rowid، e.id، 0) id، nvl2 (e.rowid، e.name، "Conel") نام، nvl2 (e.rowid، e.profession، "cat") حرفه، e.votes از دو d سمت چپ پیوستن به انتخابات e در e.votes>: ترتیب محدود شده توسط e.votes desc

روش 4.مدل با گربه.
شناسه، نام، حرفه، رای‌های انتخابی که در آن رای‌ها انتخاب کنید>: ابعاد مدل محدود شده توسط معیارهای (شناسه، نام، حرفه، آرا) قوانین (نام = nvl2 (شناسه، نام، "سرهنگ")، حرفه = nvl2 (شناسه، حرفه، "گربه")، id = nvl (id، 0)) ترتیب بر اساس آرا کاهش

در زیر نتیجه تضمین شدهبا یک باند با یک مانع بیش از حد بزرگ 10000

واضح است که اگر سطح اولیه 8000 را تنظیم کنید، این کوئری ها نیز به درستی کار خواهند کرد.

فعلاً همین است. نام همه کاراکترها با آنها مطابقت دارد مردم واقعیتصادفی در نظر گرفته شده است.

راه حل برخی دیگر از مشکلات SQL معمولی را می توان مشاهده کرد

هنگام نوشتن پرس‌و‌جوهای sql، اغلب موقعیتی پیش می‌آید که هنگام واکشی داده‌ها، لازم است مقادیر چندین ستون مقایسه شود و ستونی که حاوی داده‌ها نیست (خالی نیست) نمایش داده شود. عبارت Coalesce این مشکل را کاملاً حل می کند. در این مقاله خواهید یافت توضیحات کاملعبارات sql، جزئیات استفاده و مثال ها را با هم ترکیب کنید.

خروجی مقادیر غیر خالی با Coalesce. ویژگی های خاص

ویژگی های استفاده از Sql Coalesce را در نظر بگیرید:

1. اجازه تعیین هر تعداد آرگومان را می دهد (برخلاف Isnull / Nvl / Nvl2 که دارای تعداد محدودی آرگومان هستند).
2. می تواند پرس و جوهای فرعی را به عنوان یک استدلال در نظر بگیرد.
3. نتیجه ای برابر با اولینمقداری غیر از Null یا Null اگر متفاوت باشد مقادیر تهیپیدا نخواهد شد.
4. Sql Coalesce را می توان در یک عبارت Select برای انتخاب یک مقدار غیر خالی و همچنین در Where برای روشن کردن مجموعه ای از ستون ها با مقادیر خالیمجاز نیست (/ مجاز).
5. این عبارت مانند استفاده از عبارت Case است که هر آرگومان را به صورت متوالی در برابر شرط زمانی که argument1 null نباشد، سپس argument1 آزمایش می کند. اساساً، Coalesce یک میانبر برای سهولت استفاده است و در بسیاری از DBMS ها، بهینه سازهای پرس و جو عبارت Coalesce را در Case بازنویسی می کنند.
6. توابع ادغام Sql در تمام رابطه ای پیشرو در دسترس هستند

ترکیب نحو

هر کسی که تا به حال از Coalesce هنگام نوشتن کوئری های sql استفاده کرده باشد می داند که نحو از این بیانفوق العاده ساده به اندازه کافی در پرانتزآرگومان های تست شده برای Null را مشخص کنید که با کاما از هم جدا شده اند. اگر فرض کنیم که آرگومان‌ها arg1، arg2، ... argN نام‌گذاری شده‌اند، سینتکس Coalesce به صورت زیر خواهد بود:

ادغام (arg1, arg2, ... argN).

بیایید چندین جدول برای مطالعه مکانیسم این عبارت آماده کنیم.

آماده کردن جداول

برای درک بهتر Coalesce توضیحات sql، بیایید دو جدول در پایگاه داده حاوی اطلاعات مربوط به اشیاء املاک و مستغلات ایجاد کنیم.

اجازه دهید جدول اول، Area، حاوی نام اشیاء املاک و مساحت آنها باشد. منطقه را می توان مشخص کرد (area_yt) یا اعلام کرد (area_decl).

اجازه دهید دومین جدول Basic_characteristic حاوی اطلاعاتی در مورد ویژگی های اصلی ویژگی باشد - Extension، Depth، Area، Scope، Height.

ما سینتکس Coalesce sql، توضیحات، ویژگی های استفاده را بررسی کردیم و مستقیماً به سراغ مثال ها می رویم.

نمونه هایی از استفاده

سینتکس عبارت Coalesce بسیار ساده است، اما مهم است که به خاطر داشته باشید که دستور FIRST مقدار غیر خالی پیدا شده از لیست آرگومان را برمی گرداند. این تذکر یک نکته بسیار دارد پراهمیتبنابراین، استدلال های موجود در بیان باید به ترتیب اهمیت مرتب شوند. ساده ترین راه برای درک اصل از جدول مساحت است. یک پرس و جو ایجاد کنید که نام ملک و همچنین مقدار منطقه را انتخاب کند:

و نتیجه را بگیرید:

برای اشیاء "ساختمان 1"، "قطعه زمین 1" و "ساخت و ساز 2"، هر دو مقدار منطقه پر شد، اما منطقه در اولویت مشخص شد، زیرا ما آن را ابتدا در لیست آرگومان ها نشان دادیم. عبارت Coalesce اولین مقدار غیر خالی را پیدا کرد و آن را رها کرد و اسکن آرگومان های بیشتر را متوقف کرد. این ساخت و سازدرخواست صحیح است، زیرا منطقه مشخص شده قطعی تر از منطقه اعلام شده است. اگر منطقه اعلام شده را به عنوان آرگومان اول مشخص کرده بودیم، وقتی این فیلد جدول پر شد، اولویت داشت.

علاوه بر استفاده در Select، اغلب از عبارت Coalesce استفاده می شود شرایط کجا... این به شما امکان می دهد خطوطی را که در آنها مقادیر لیست فیلدها خالی است را از نتیجه جدا کنید (یا برعکس، فقط مقادیری را که لیست فیلدها خالی است در نتیجه بگنجانید). این موقعیتدر همه جا رخ می دهد: به عنوان مثال، در شرکت، هنگام ثبت نام یک کارمند جدید، تنها اطلاعات اولیه در مورد او در پایگاه داده وارد شده و پر می شود. اطلاعات دقیق"برای بعد" گذاشت. به تدریج "شکاف" ظاهر می شود - یا قبل از چک یا هنگام اعزام کارمند به تعطیلات / سفر کاری / مرخصی استعلاجی.

بیایید از جدول با ویژگی های اصلی اشیاء املاکی را انتخاب کنیم که هیچ یک از مقادیر مشخصه برای آنها پر نشده است:

ما امیدواریم که شرح مفصل Coalesce sql ما به شما در درک تمام ویژگی های استفاده از این عبارت و همچنین مقابله با تفاوت های ظریف کمک کند.