дома › Проблеми › NVL трансформации за различни типови на податоци. Изјави за гранка во опис на командата SELECT Oracle nvl

NVL трансформации за различни типови на податоци. Изјави за гранка во опис на командата SELECT Oracle nvl

Ги допревме вгнездените функции малку порано, сега да ги разгледаме малку подетално. Ќе ги разгледаме и функциите за работа со вредноста NULL и функциите кои помагаат да се имплементира операцијата за разгранување во барање.

вгнездени функции

Вгнездените функции ја користат повратната вредност на една функција како влезен параметар на друга функција. Функциите секогаш враќаат само една вредност. Затоа, резултатот од повик на функција може да го третирате како буквална вредност кога го користите како параметар за друг повик на функција. Функциите на редови може да се вгнездат до кое било ниво на вгнездување. Повик за една функција изгледа вака

Функција 1 (параметар 1, параметар 2, ...) = резултат

Замената на параметар на функција со повик до друга функција може да резултира со изрази како

F1(парам1.1, F2(парам2.1, парам2.2, F3(парам3.1)), парам1.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) каде што оригиналот е оригиналната вредност за проверка и ifnull е резултатот што го враќа функцијата ако оригиналот е NULL. Типот на податоци на параметрите ifnull и оригиналот мора да биде компатибилен. Односно, или типот на податоци мора да биде ист или мора да биде можно имплицитно да се конвертираат вредностите од еден тип во друг. Функцијата NVL враќа вредност од истиот тип на податоци како и типот на податоци на оригиналниот параметар. Размислете за три прашања

Прашање 1: изберете nvl(1234) од dual;

Прашање 2: изберете nvl(null, 1234) од dual;

Прашање 3: изберете nvl(substr('abc', 4), 'Не постои подниза') од dual;

Бидејќи функцијата NVL бара два параметри, барањето 1 ќе врати ORA-00909: грешка со неважечки број на аргументи. Барањето 2 ќе врати 1234 бидејќи NULL се проверува и е NULL. Барањето три користи вгнездена функција SUBSTR која се обидува да го извлече четвртиот знак од низа со три знаци, враќа NULL, а функцијата NVL ја враќа низата „Не постои sbusstring“.

Функцијата NVL е многу корисна кога работите со бројки. Се користи за претворање на NULL вредности во 0, така што аритметичките операции на броевите не враќаат NULL.

Функција NVL2

Функцијата NVL2 обезбедува повеќе функционалност од NVL, но исто така се справува со NULL. Ја проверува вредноста на колона или израз од кој било тип за нула вредност. Ако вредноста не е NULL, тогаш се враќа вториот параметар, во спротивно се враќа третиот параметар, за разлика од функцијата NVL, која во овој случај ја враќа оригиналната вредност.

Функцијата NVL2 има три потребни параметри и синтаксата е NVL2 (оригинал, ifnotnull, ifnull), каде што оригиналот е вредноста што треба да се тестира, ifnotnull е вредноста што треба да се врати ако оригиналот не е NULL, и ifnull е вредноста што треба да се врати ако оригиналот е НУЛА. Типовите на податоци на параметрите ifnotnull и ifnull мора да бидат компатибилни и не можат да бидат од типот LONG. Типот на податоци што го враќа функцијата NVL2 е еднаков на типот на податоци на параметарот ifnotnull. Ајде да погледнеме неколку примери

Прашање 1: изберете nvl2(1234, 1, 'низа') од dual;

Прашање 2: изберете nvl2(null, 1234, 5678) од dual;

Прашање 3: изберете nvl2(substr('abc', 2), 'Not bc', 'No substring') од dual;

Параметарот ifnotnull во барањето 1 е број, а параметарот ifnull е низа. Бидејќи типовите на податоци се некомпатибилни, се враќа грешката „ORA-01722: неважечки број“. Барањето два враќа параметар ifnull бидејќи оригиналот е NULL, а резултатот е 5678. Барањето три ја користи функцијата SUBSTR која враќа 'bc' и повикува NVL2('bc', 'Not bc', 'No substring') - што враќа ifnotnull параметар – „Не bc“.

Функција NULLIF

Функцијата NULLIF тестира две вредности за истите. Ако се исти, се враќа NULL, во спротивно се враќа првиот параметар. Функцијата NULLIF има два потребни параметри и синтаксата е NULLIF (ifunequal, споредбено_точка). Функцијата споредува два параметри и ако се идентични, враќа NULL, инаку ако нееднаков параметар. Размислете за барањата

Прашање 1: изберете nullif(1234, 1234) од dual;

Барањето еден враќа NULL бидејќи параметрите се идентични. Низите во барањето 2 не се претвораат во датум, туку се споредуваат како низи. Бидејќи низите се со различна должина, се враќа параметарот ifunequal 24-JUL-2009.

На Слика 10-4, функцијата NULLIF е вгнездена во функцијата NVL2. Функцијата NULLIF за возврат ги користи функциите SUBSTR и UPPER како дел од изразот во ако нееднаков параметар. Колоната EMAIL се споредува со овој израз, кој ја враќа првата буква од името поврзана со презимето за вработените чие име е долго 4 знаци. Кога овие вредности се еднакви, NULLIF ќе ја врати NULL, во спротивно ќе ја врати вредноста на ако нееднаков параметар. Овие вредности се користат како параметар за функцијата NVL2. NVL2, пак, враќа опис дали споредените елементи се совпаѓаат или не.

Слика 10-4 - Користење на функцијата NULLIF

Функција COALESCE

Функцијата COALESCE ја враќа првата вредност што не е NULL од списокот со параметри. Ако сите параметри се NULL, тогаш се враќа NULL. Функцијата COALESCE има два потребни параметри и кој било број на опционални параметри, а синтаксата е COALESCE(expr1, expr2, ..., exprn) каде резултатот е expr1 ако вредноста на expr 1 не е NULL, во спротивно резултатот е 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. Размислете за три примери

Прашање 1: изберете coalesce(null, null, null, 'a string') од dual;

Прашање 2: изберете coalesce(null, null, null) од dual;

Прашање 3: изберете спојување(substr('abc', 4), 'Not bc', 'No substring') од dual;

Барањето 1 го враќа четвртиот параметар: низа, бидејќи е првиот параметар кој не е NULL. Барањето два враќа NULL бидејќи сите параметри се NULL. Барањето 3 го оценува првиот параметар, добива NULL и го враќа вториот параметар бидејќи е првиот параметар кој не е NULL.

Параметрите на функцијата NVL2 може да бидат збунувачки ако веќе сте запознаени со функцијата NVL. NVL (оригинал, ifnull) го враќа оригиналот ако вредноста не е NULL, инаку ifnull. NVL2 (оригинал, ifnotnull, ifnull) враќа ifnotnull ако оригиналот не е нула инаку ifnull. Забуната доаѓа од фактот што вториот параметар на функцијата NVL е ifnull, додека NVL2 има ifnotnull. Затоа, не се потпирајте на позицијата на параметарот во функцијата.

Функција DECO

Функцијата DECODE имплементира ако-тогаш-инаку логика со проверка на првите два параметри за еднаквост и враќање на третата вредност ако се еднакви, или различна вредност ако не. Функцијата DECODE има три потребни параметри и синтаксата е DECODE(expr1, comp1, iftrue1, , ). Овие параметри се користат како што е прикажано во следниот пример на псевдокод.

АКО expr1=comp1 тогаш вратете го iftrue1

Во спротивно, ако expr1=comp2, тогаш вратете го iftrue2

Во спротивно, ако exprN=compN, тогаш вратете го iftrueN

Во спротивно врати NULL|неправилно;

Прво, expr1 се споредува со comp1. Ако се еднакви, се враќа iftrue1. Ако expr1 не е еднаков на comp1, тогаш што ќе се случи понатаму зависи од тоа дали се наведени параметрите comp2 и iftrue2. Ако е дадена, тогаш вредноста на expr1 се споредува со comp2. Ако вредностите се еднакви, тогаш се враќа iftrue2. Ако не, тогаш ако има compN, iftrueN параметри парови, expr1 и compN се споредуваат, а ако е точно, iftrueN се враќа ако е еднакво. Ако не е пронајдено совпаѓање во кое било множество параметри, тогаш или ако е наведен овој параметар се враќа или ако е погрешно, или NULL.

Сите параметри во функцијата DECODE може да бидат изрази. Типот на повратната вредност е еднаков на типот на првиот валидатор - параметароткомп 1. Израз експр 1 имплицитно се претвора во тип на податочен параметар комп1. Сите други комп опции на располагање 1 … компН исто така имплицитно се претвораат во тип комп 1. DECODE третира NULL вредност како еднаква на друга NULL вредност, т.е. ако expr1 е NULL, а comp3 е NULL и comp2 не е NULL, тогаш iftrue3 се враќа. Ајде да погледнеме неколку примери

Прашање 1: изберете decode(1234, 123, '123 е совпаѓање') од dual;

Прашање 2: изберете декодирање (1234, 123, '123 е совпаѓање', 'Нема совпаѓање') од двојна;

Прашање 3: изберете decode('пребарување', 'comp1', 'true1', 'comp2', 'true2', 'пребарување', 'true3', substr('2search', 2, 6), 'true4', ' неточно') од двојна;

Првото барање ги споредува вредностите 1234 и 123. Бидејќи тие не се еднакви, iftrue1 се игнорира, а бидејќи iffalse не е дефиниран, се враќа NULL. Барањето два е идентично со барањето 1, освен што е дефинирана ако неточната вредност. Бидејќи 1234 не е еднаков на 123, ако е погрешно - „Нема натпревар“ се враќа. Барањето три ги проверува вредностите на параметрите во однос на вредноста за пребарување. Параметрите comp1 и comp2 не се еднакви на 'пребарување' така што резултатите од iftrue1 и iftrue2 се прескокнуваат. Се наоѓа совпаѓање во операцијата за споредба на третата ком3 елемент (позиција на параметарот 6) и повратната вредност на iftrue3 (параметар 7) е 'true3'. Откако е пронајден натпревар, повеќе не се прават пресметки. Односно, и покрај фактот што вредноста на comp4 (параметар 8) исто така се совпаѓа со expr1, овој израз никогаш не се оценува бидејќи совпаѓањето беше пронајдено во претходната споредба.

CASE изразување

Сите програмски јазици од третата и четвртата генерација ја имплементираат конструкцијата на случајот. Како и функцијата DECODE, изразот CASE ви овозможува да имплементирате логика ако-тогаш-инаку. Постојат две опции за користење на изразот CASE. Едноставната изјава CASE го поставува изворниот елемент што треба да се споредува еднаш, а потоа ги наведува сите потребни услови за тестирање. Комплексниот (пребаруван) CASE ги оценува двата искази за секоја состојба.

Изразот CASE има три потребни параметри. Синтаксата на изразот зависи од видот. За едноставен израз CASE изгледа вака

CASE search_expr

КОГА споредба_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. На пример:

ИЗБЕРИ ЗНАК (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 „Аргумент надвор од опсегот“.

Функција SQRT(n)

Оваа функцијаго враќа квадратниот корен од n. На пример:

ИЗБЕРЕТЕ SQRT(100) X

ОД DUAL

Функциите EXP(n) и LN(n).

Функцијата EXP го подигнува e на моќност од n, а функцијата LN го пресметува природниот логаритам од n (со n мора да биде поголемо од нула). Пример:

ИЗБЕРИ EXP(2) X1, LN(1) X2, LN(EXP(2)) X3

Функција NVL

Функцијата NVL е генерално најчесто користена. Функцијата добива два параметри: NVL(expr1, expr2). Ако првиот параметар expr1 не е NULL, тогаш функцијата ја враќа својата вредност. Ако првиот параметар е NULL, тогаш функцијата наместо тоа ја враќа вредноста на вториот параметар expr2.

Да разгледаме практичен пример. Полето COMM во табелата EMP може да содржи NULL вредности. При извршување на барање како:

ИЗБЕРЕТЕ EMPNO, ENAME, COMM, NVL(COMM, 0) NVL_COMM

ОД SCOTT.EMP

вредноста NULL ќе се замени со нула. Забележете дека ако вредноста се генерира со помош на функција, ѝ се доделува псевдоним. Резултатите од барањето ќе изгледаат вака:

ЕМПНО	ENAME	КОМП	NVL_COMM
7369	СМИТ		0
7499	АЛЕН	300	300
7521	ВАРД	500	500
7566	Џонс		0
7654	МАРТИН	1400	1400
7698	БЛЕЈК		0
7782	КЛАРК		0
7839	КРАЛ		0
7844	ТРУТЕР	0	0
7900	Џејмс		0
7902	FORD		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])

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

TRUNC(100,25678, 2) X4

ОД DUAL

Функција SIGN(n).

ИЗБЕРИ ЗНАК (100,22) X1, SIGN (-100,22) X2, SIGN (0) X3

ОД DUAL

Интересна карактеристика на оваа функција е можноста да се помине m еднакво на нула без грешка со делење со 0.

Функција 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

Функција SQRT(n)

Оваа функција го враќа квадратниот корен од n. На пример:

ИЗБЕРЕТЕ SQRT(100) X

ОД DUAL

Функциите EXP(n) и LN(n).

ИЗБЕРИ 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 се променети за да не одговараат на вашите вредности.

Одлука:

ИЗБЕРЕТЕ име КАКО „Име“, презиме како „Презиме“, TO_CHAR(ПЛАТА, „L999999999.99“) Како „Плата“ ОД hr.вработени ПОРАЧАЈ ПО ПЛАТА ОПИШИ.

Функции TO_NUMBER и TO_DATE

Функција за конвертирање на низа во датум TO_DATE (низа, формат). Можните вредности на форматот веќе се дискутирани погоре, па ќе дадам неколку примери за користење на оваа функција. Примери:

ИЗБЕРИ TO_DATE („01.01.2010“, „ДД.ММ.ГГГГ“) ОД DUAL ќе го врати датумот `01.01.2010“;

ИЗБЕРИ TO_DATE („01.ЈАН.2010“,`ДД.ПОН.ГГГГГ“) ОД DUAL ќе го врати датумот `01.01.2009“;

ИЗБЕРИ TO_DATE („15-01-10“,` ДД-ММ-ГГ“) ОД DUAL ќе го врати датумот „15.01.2010“.

Функција за претворање на низа во нумеричка вредност TO_NUMBER (низа, формат). Најчестите вредности на форматот се наведени во табелата, па ајде да ја разгледаме употребата на оваа функција користејќи примери. Примери:

ИЗБЕРИ TO_NUMBER (`100“) ОД DUAL ќе го врати бројот 100 SELECT TO_NUMBER(`0010.01“, „9999D99“) ОД DUAL ќе го врати бројот 10.01;

ИЗБЕРИ TO_NUMBER („500.000“, „999G999“) ОД DUAL ќе го врати бројот 500000.

RR елемент во формат на датум

Елементот за формат на датум и време RR е сличен на елементот формат на датум и време на YY, но обезбедува дополнителна флексибилност за складирање на вредностите на датумот во други векови. Елементот за формат RR datetime ви овозможува да ги зачувате датумите од 20 век во 21 век со наведување само на последните две цифри од годината.

Ако последните две цифри од тековната година се од 00 до 49, тогаш вратената година ги има истите први две цифри како и тековната година.

Ако последните две цифри од тековната година се помеѓу 50 и 99, тогаш првите 2 цифри од вратената година се за 1 поголеми од првите 2 цифри од тековната година.

Ако последните две цифри од тековната година се од 00 до 49, тогаш првите 2 цифри од вратената година се за 1 помалку од првите 2 цифри од тековната година.

Ако последните две цифри од тековната година се помеѓу 50 и 99, тогаш вратената година ги има истите први две цифри како и тековната година.

Функција NVL

Пример: Изберете NVL (град_добавувач, n/a") од добавувачите:

Во изјавата SQL погоре, таа ќе врати n/" ако полето снабдувач_град содржи нула вредност. Во спротивно, ќе ја врати вредноста на снабдувачот_град.

Друг пример за користење на функцијата NVL во Oracle/PLSQL е:

изберете снабдувач_ид, NVL (добавувач_деск, име_добавувач) од добавувачите.

Оваа SQL изјава ќе се врати Име на Добавувачотполе ако добавувач_описсодржи нулта вредност. Во спротивно ќе се врати добавувач_опис.

Последен пример: користењето на функцијата NVL во Oracle/PLSQL е: изберете NVL(провизија, 0) од продажбата;

Оваа SQL изјава ја врати вредноста 0 ако комисијаполето содржи нула вредност. Во спротивно, ќе се врати провизииПоле.

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 AS "First Name", last_name како "Last Name", NVL (COMMISSION_PCT, 0) како "Commission Rate" FROM hr.employees.

Популарни во категоријата: