дома › Проблеми › 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, враќа алтернативна стандардна вредност што не е 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. Ако вредноста не е NULL, тогаш се враќа вториот параметар, во спротивно се враќа третиот параметар, за разлика од функцијата NVL, која во овој случај ја враќа оригиналната вредност.

Функцијата NVL2 има три потребни параметри и синтаксата е NVL2 (оригинал, ifnotnull, ifnull), каде што оригиналот е вредноста што се тестира, ifnotnull е вредноста што се враќа ако оригиналот не е NULL, и ifnull е вредноста што се враќа ако оригиналот е NULL. Типовите на податоци на параметрите 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 бидејќи параметрите се идентични. Низите во Query 2 не се претвораат во датум, туку се споредуваат како низи. Бидејќи низите се со различна должина, параметарот ако нееднаков е вратен 24-ЈУЛ-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 ако оригиналот не е NULL инаку ifnull. Збунетоста доаѓа од фактот дека вториот параметар на функцијата NVL е ifnull, додека функцијата NVL2 е ifnotnull. Затоа, не се потпирајте на позицијата на параметарот во функцијата.

Функција ДЕКОДИРАЊЕ

Функцијата 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 е имплицитно претворена во типот на податоци на параметарот comp1. Сите други достапни комп параметри 1...compN исто така имплицитно се претвораат во тип комп 1. DECODE третира NULL вредност како еднаква на друга NULL вредност, т.е. ако expr1 е NULL, а comp3 е NULL и comp2 не е NULL, тогаш iftrue3 се враќа. Ајде да погледнеме неколку примери

Прашање 1: изберете декодирање (1234, 123, „123 е совпаѓање“) од двојна;

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

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

Првото барање ги споредува вредностите 1234 и 123. Бидејќи тие не се еднакви, iftrue1 се игнорира и бидејќи вредноста iffalse не е дефинирана, се враќа NULL. Барањето два е идентично со барањето 1, освен што вредноста iffalse е дефинирана. Бидејќи 1234 не е еднаква на 123, враќа неточно - „Нема натпревар“. Барањето три ги проверува вредностите на параметрите за да одговараат на вредноста на пребарувањето. Параметрите comp1 и comp2 не се еднакви на 'пребарување' така што резултатите од iftrue1 и iftrue2 се прескокнуваат. Се наоѓа совпаѓање во третата споредбена операција на елементот comp3 (позиција на параметарот 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. Ако n е негативна, враќа -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. На пример:

SELECT 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

Функција 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. На пример:

SELECT 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 во форматот прикажан на сл. 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 обично се користи најчесто. Функцијата добива два параметри: NVL (expr1, expr2). Ако првиот параметар expr1 не е NULL, тогаш функцијата ја враќа својата вредност. Ако првиот параметар е NULL, тогаш функцијата наместо тоа ја враќа вредноста на вториот параметар expr2.

Пример: Изберете 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 конверзија за разни видови:

БРОЈ - 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.

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