Popis Nvl sql. Transformace NVL pro různé typy dat

Vnořených funkcí jsme se dotkli o něco dříve, nyní se na ně podíváme trochu podrobněji. Podíváme se také na funkce pro práci s hodnotou NULL a funkce, které pomáhají implementovat operaci větvení v dotazu.

vnořené funkce

Vnořené funkce používají návratovou hodnotu jedné funkce jako vstupní parametr jiné funkce. Funkce vrací vždy pouze jednu hodnotu. Proto můžete výsledek volání funkce považovat za doslovnou hodnotu, když jej použijete jako parametr pro volání jiné funkce. Řádkové funkce lze vnořovat až do libovolné úrovně vnoření. Jedno volání funkce vypadá takto

Funkce1(parametr1, parametr2, …) = výsledek

Nahrazení parametru funkce voláním jiné funkce může vést k výrazům jako

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

Vnořené funkce jsou nejprve vyhodnoceny, než jsou jejich výsledky použity jako vstupní hodnoty pro další funkce. Funkce se vyhodnocují od nejhlubší úrovně vnoření po nejvyšší zleva doprava. Předchozí výraz se provede následovně

Funkce F3(param1) je vyhodnocena a návratová hodnota je použita jako třetí parametr pro funkci 2, nazvěme ji param2.3
Poté se vyhodnotí funkce F2(param1, param2.2, param2.3) a vrácená hodnota se použije jako druhý parametr funkce F1 - param1.2
Nakonec je vyhodnocena funkce F1(param1, param2, param1.3) a výsledek je vrácen volajícímu programu.

Funkce F3 je tedy na třetí úrovni vnoření.

Zvažte dotaz

select next_day(last_day(sysdate)-7, 'út') from dual;

V tomto dotazu jsou tři funkce, odspodu nahoru - SYSDATE, LAST_DAY, NEXT_DAY. Žádost se podává takto
Provede se vnořená funkce SYSDATE. Vrací aktuální systémový čas. Řekněme, že aktuální datum je 28. října 2009
Dále se vypočítá výsledek funkce druhé úrovně LAST_DAY. LAST_DATE('28-OCT-2009') vrací poslední den října 2009, tj. 31. října 2009.
Poté se od tohoto data odečte sedm dní – získá se 24. říjen.
Nakonec je vyhodnocena funkce NEXT_DAY('24-OCT-2009', 'út') a dotaz vrátí poslední úterý v říjnu - což je v našem příkladu 27-OCT-2009.

Je poměrně obtížné pochopit a vytvořit složité výrazy pomocí mnoha vnořených volání funkcí, ale to přichází s časem a praxí. Takové výrazy můžete rozdělit na části a testovat samostatně. Tabulka DUAL je velmi užitečná pro testování dotazů a výsledků volání funkcí. Můžete testovat a ladit malé komponenty, které se pak spojí do jednoho velkého požadovaného výrazu.

Funkce větve

Funkce větvení, také známé jako IF-THEN-ELSE, se používají k určení cesty provádění v závislosti na určitých okolnostech. Funkce větvení vracejí různé výsledky na základě výsledku vyhodnocení podmínky. Ve skupině takových funkcí jsou funkce pro práci s hodnotou NULL: NVL, NVL2, NULLIF a COALESCE. A také obecné funkce reprezentované funkcí DECODE a výrazem CASE. Funkce DECODE je funkcí Oracle, zatímco výraz CASE je ve standardu ANSI SQL.

Funkce NVL

Funkce NVL kontroluje hodnotu sloupce nebo výrazu libovolného datového typu, zda neobsahuje hodnotu null. Pokud je hodnota NULL, vrátí alternativní výchozí hodnotu jinou než NULL, jinak se vrátí původní hodnota.

Funkce NVL má dva požadované parametry a syntaxe je NVL(original, ifnull), kde original je původní hodnota ke kontrole a ifnull je výsledek vrácený funkcí, pokud je originál NULL. Datový typ parametrů ifnull a původních parametrů musí být kompatibilní. To znamená, že buď datový typ musí být stejný, nebo musí být možné implicitně převádět hodnoty z jednoho typu na druhý. Funkce NVL vrací hodnotu stejného datového typu, jako je datový typ původního parametru. Zvažte tři dotazy

Dotaz 1: vyberte nvl(1234) z dual;

Dotaz 2: vyberte nvl(null, 1234) z dual;

Dotaz 3: vyberte nvl(substr('abc', 4), 'Žádný podřetězec neexistuje') z dual;

Protože funkce NVL vyžaduje dva parametry, dotaz 1 vrátí chybu ORA-00909: neplatný počet argumentů. Dotaz 2 vrátí 1234, protože se kontroluje NULL a je NULL. Dotaz 3 používá vnořenou funkci SUBSTR, která se pokouší extrahovat čtvrtý znak ze tříznakového řetězce, vrací hodnotu NULL a funkce NVL vrací řetězec 'Neexistuje žádný sbustring.

Funkce NVL je velmi užitečná při práci s čísly. Používá se k převodu hodnot NULL na 0, takže aritmetické operace přes čísla nevrátilo NULL

Funkce NVL2

Funkce NVL2 poskytuje více funkcí než NVL, ale také zpracovává NULL. Kontroluje hodnotu sloupce nebo výrazu jakéhokoli typu, zda neobsahuje hodnotu null. Pokud hodnota není NULL, pak je vrácen druhý parametr, v opačném případě je vrácen třetí parametr, na rozdíl od funkce NVL, která v tomto případě vrací původní hodnotu.

Funkce NVL2 má tři požadované parametry a syntaxe je NVL2(original, ifnotnull, ifnull), kde original je hodnota k testování, ifnotnull je hodnota, která se má vrátit, pokud původní není NULL, a ifnull je hodnota, která se má vrátit, pokud je původní NULA. Datové typy parametrů ifnotnull a ifnull musí být kompatibilní a nemohou být typu LONG. Datový typ vrácený funkcí NVL2 se rovná datovému typu parametru ifnotnull. Podívejme se na pár příkladů

Dotaz 1: vyberte nvl2(1234, 1, 'řetězec') z dual;

Dotaz 2: vyberte nvl2(null, 1234, 5678) z dual;

Dotaz 3: vyberte nvl2(substr('abc', 2), 'Not bc', 'No substring') z dual;

Parametr ifnotnull v dotazu 1 je číslo a parametr ifnull je řetězec. Protože datové typy jsou nekompatibilní, vrátí se chyba „ORA-01722: neplatné číslo“. Dotaz 2 vrací parametr ifnull, protože originál je NULL a výsledek je 5678. Dotaz 3 používá funkci SUBSTR, která vrací 'bc' a volá NVL2('bc', 'Not bc', 'No substring') - což vrací ifnotnull parametr – 'Ne bc'.

funkce NULLIF

Funkce NULLIF testuje dvě hodnoty na totéž. Pokud jsou stejné, vrátí se NULL, jinak se vrátí první parametr. Funkce NULLIF má dva požadované parametry a syntaxe je NULLIF(ifunequal, Compare_item). Funkce porovná dva parametry, a pokud jsou totožné, vrátí NULL, jinak parametr ifunequal. Zvažte dotazy

Dotaz 1: vyberte nullif(1234, 1234) z dual;

Dotaz jedna vrátí NULL, protože parametry jsou identické. Řetězce v dotazu 2 se nepřevádějí na datum, ale porovnávají se jako řetězce. Protože řetězce mají různé délky, vrátí se parametr ifunequal 24-JUL-2009.

Na obrázku 10-4 je funkce NULLIF vnořena do funkce NVL2. Funkce NULLIF zase používá funkce SUBSTR a UPPER jako součást výrazu v parametru ifunequal. S tímto výrazem je porovnán sloupec EMAIL, který u zaměstnanců, jejichž křestní jméno je dlouhé 4 znaky, vrací první písmeno křestního jména spojené s příjmením. Když jsou tyto hodnoty stejné, NULLIF vrátí hodnotu NULL, jinak vrátí hodnotu parametru ifunequal. Tyto hodnoty se používají jako parametr pro funkci NVL2. NVL2 zase vrátí popis, zda se porovnávané prvky shodovaly nebo ne.

Obrázek 10-4 - Použití funkce NULLIF

funkce COALESCE

Funkce COALESCE vrátí první hodnotu ze seznamu parametrů, která není NULL. Pokud jsou všechny parametry NULL, vrátí se NULL. Funkce COALESCE má dva požadované parametry a libovolný počet volitelných parametrů a syntaxi COALESCE(výraz1, výraz 2, ..., exprn), kde je výsledkem výraz expr1, pokud hodnota výrazu 1 není NULL, jinak je výsledkem výraz výraz 2 není NULL a tak dále. COALESCE se svým významem rovná vnořeným funkcím NVL

COALESCE(výraz1; výraz2) = NVL(výraz1; výraz2)

COALESCE(výraz1, výraz2, výraz3) = NVL(výraz1,NVL(výraz2, výraz3))

Datový typ vrácené hodnoty, pokud je nalezena hodnota jiná než NULL, se rovná datovému typu první hodnoty, která není NULL. Aby se předešlo chybě 'ORA-00932: nekonzistentní datové typy', musí být všechny parametry, které nejsou NULL, kompatibilní s prvním parametrem, který není NULL. Zvažte tři příklady

Dotaz 1: select coalesce(null, null, null, 'a string') z dual;

Dotaz 2: vyberte coalesce(null, null, null) z dual;

Dotaz 3: vyberte coalesce(substr('abc', 4), 'Not bc', 'No substring') z dual;

Dotaz 1 vrací čtvrtý parametr: řetězec, protože jde o první parametr, který nemá hodnotu NULL. Dotaz 2 vrátí hodnotu NULL, protože všechny parametry jsou NULL. Dotaz 3 vyhodnotí první parametr, získá hodnotu NULL a vrátí druhý parametr, protože se jedná o první parametr, který nemá hodnotu NULL.

Parametry funkce NVL2 mohou být matoucí, pokud jste již obeznámeni s funkcí NVL. NVL(original, ifnull) vrátí původní, pokud hodnota není NULL, jinak ifnull. NVL2(original, ifnotnull, ifnull) vrátí ifnotnull, pokud originál není null, jinak ifnull. Zmatek pochází ze skutečnosti, že druhý parametr funkce NVL je ifnull, zatímco NVL2 má ifnotnull. Nespoléhejte se tedy na pozici parametru ve funkci.

Funkce DECO

Funkce DECODE implementuje logiku if-then-else kontrolou rovnosti prvních dvou parametrů a vrácením třetí hodnoty, pokud jsou stejné, nebo jiné hodnoty, pokud ne. Funkce DECODE má tři požadované parametry a syntaxe je DECODE(výraz1, comp1, iftrue1, , ). Tyto parametry se používají tak, jak je uvedeno v následujícím příkladu pseudokódu.

IF expr1=comp1 pak vrátí iftrue1

Else if expr1=comp2 pak vrátí iftrue2

Else if exprN=compN pak vraťte iftrueN

Else return NULL|iffalse;

Nejprve se porovná expr1 s comp1. Pokud jsou stejné, vrátí se iftrue1. Pokud se expr1 nerovná comp1, pak co se stane dále závisí na tom, zda jsou zadány parametry comp2 a iftrue2. Pokud je zadán, pak se hodnota expr1 porovná s comp2. Pokud jsou hodnoty stejné, vrátí se iftrue2. Pokud ne, pak pokud existují compN, jsou porovnány páry parametrů iftrueN, expr1 a compN, a pokud je rovno, je vráceno iftrueN. Pokud nebyla nalezena žádná shoda v žádné sadě parametrů, vrátí se buď iffalse, pokud byl tento parametr zadán, nebo NULL.

Všechny parametry ve funkci DECODE mohou být výrazy. Typ návratové hodnoty je roven typu prvního validátoru - parametru sestava 1. Výraz expr 1 se implicitně převede na datový typ parametru comp1. Všechny ostatní dostupné možnosti kompenzace 1 … souč jsou také implicitně převedeny na typ comp 1. DECODE považuje hodnotu NULL za rovnou jiné hodnotě NULL, tzn. pokud je výraz1 NULL a comp3 je NULL a comp2 není NULL, vrátí se iftrue3. Podívejme se na pár příkladů

Dotaz 1: select decode(1234, 123, '123 is a match') from dual;

Dotaz 2: select decode(1234, 123, '123 is a match', 'No match') from dual;

Dotaz 3: select decode('search', 'comp1', 'true1', 'comp2', 'true2', 'search', 'true3', substr('2search', 2, 6), 'true4', ' nepravda') z duálu;

Dotaz jedna porovnává hodnotu 1234 a 123. Protože se nerovnají, ignoruje se iftrue1 a protože iffalse není definováno, vrátí se NULL. Dotaz 2 je totožný s dotazem 1 s tím rozdílem, že je definována hodnota iffalse. Protože 1234 se nerovná 123, je vrácena hodnota iffalse - 'No match'. Dotaz tři kontroluje hodnoty parametrů oproti hledané hodnotě. Parametry comp1 a comp2 se nerovnají 'search', takže výsledky iftrue1 a iftrue2 jsou přeskočeny. Byla nalezena shoda ve třetí operaci porovnání prvků comp3 (pozice parametru 6) a návratová hodnota iftrue3 (parametr 7) je 'true3'. Protože je nalezena shoda, již se neprovádějí žádné výpočty. To znamená, že přestože hodnota comp4 (parametr 8) odpovídá také expr1, tento výraz není nikdy vyhodnocen, protože shoda byla nalezena v předchozím srovnání.

výraz CASE

Všechny programovací jazyky třetí a čtvrté generace implementují konstrukci případu. Stejně jako funkce DECODE vám výraz CASE umožňuje implementovat logiku if-then-else. Existují dvě možnosti použití výrazu CASE. Jednoduchý příkaz CASE nastaví zdrojový prvek, který se má jednou porovnat, a poté vypíše všechny nezbytné podmínky testu. Komplexní (hledaný) CASE vyhodnocuje oba příkazy pro každou podmínku.

Výraz CASE má tři povinné parametry. Syntaxe výrazu závisí na typu. Pro jednoduchý výraz CASE to vypadá takto

CASE search_expr

KDYŽ Compare_expr1 THEN iftrue1

)

Funkce TRUNC vrátí číslo n zkrácené na m desetinných míst. Parametr m může být vynechán, v takovém případě je n zkráceno na celé číslo.

SELECT TRUNC(100,25678) X1, TRUNC(-100,25678) X2, TRUNC(100,99) X3,

TRUNC(100,25678; 2) X4

OD DUALU

Funkce SIGN(n).

Funkce SIGN určuje znaménko čísla. Pokud je n kladné, funkce vrátí 1. Pokud je záporné, vrátí se -1. Pokud je nula, vrátí se 0. Například:

SELECT SIGN(100,22) X1, SIGN(-100,22) X2, SIGN(0) X3

OD DUALU

Zajímavou vlastností této funkce je schopnost předat m rovné nule – v tomto případě nedochází k chybě dělení 0.

Funkce POWER(n, m).

Funkce POWER zvýší číslo n na mocninu m. Stupeň může být zlomkový a záporný, což výrazně rozšiřuje možnosti této funkce.

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

POWER(1000; 1/3) X3, POWER(1000, -1/3) X4

OD DUALU

X1	X2	X3	X4
100	10	10	0,1

V některých případech může při volání této funkce dojít k výjimce. Například:

SELECT POWER(-100, 1/2) X2

OD DUALU

V tento případ dojde k pokusu vypočítat druhou odmocninu záporného čísla, což povede k chybě ORA-01428 "Argument mimo rozsah".

Funkce SQRT(n)

Tato funkce se vrací Odmocnina od čísla n. Například:

SELECT SQRT(100) X

OD DUALU

Funkce EXP(n) a LN(n).

Funkce EXP zvýší e na mocninu n a funkce LN vypočítá přirozený logaritmus n (přičemž n musí být větší než nula). Příklad:

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

Funkce TO_CHAR s čísly

Funkce pro převod dat na jiné datové typy. TO_CHAR(číslo) převede číslo na text. TO_NUMBER(řetězec) převede text na číslo.

SELECT TO_CHAR (123) FROM DUAL vrátí řetězec 123, SELECT TO_NUMBER (`12345") FROM DUAL vrátí číslo 12345.

Laboratorní práce. Změna formátu výstupních čísel

Změny číselného formátu v Oracle SQL, funkce TO_CHAR pro práci s číselnými hodnotami.

Úkol:

Napište dotaz, který zobrazí informace o jménu, příjmení a mzdě zaměstnanců z tabulky hr.employees ve formátu zobrazeném na obrázku 1. 3,4-1:

Rýže. 3.4 -1

Zároveň by měla být data řazena tak, aby se jako první zobrazily řádky pro zaměstnance s nejvyšší mzdou.

Poznámka:

Některé hodnoty platů na obr. 3.4-1 byly změněny, takže nemusí odpovídat vašim hodnotám.

Řešení:

SELECT jméno_jméno JAKO "Jméno", příjmení Jako "Příjmení", TO_CHAR(PLAT, "L999999999,99") Jako "Plat" OD hr.zaměstnanců OBJEDNÁVKA PODLE MLADU DESC.

Funkce TO_NUMBER a TO_DATE

Funkce pro převod řetězce na datum TO_DATE(řetězec, formát). Možné hodnoty formátu již byly diskutovány výše, takže uvedu několik příkladů použití této funkce. Příklady:

VYBRAT TO_DATE("01/01/2010", `DD.MM.RRRR") Z DUAL vrátí datum `01.01.2010";

VYBRAT TO_DATE("01.JAN. 2010",`DD.MON.YYYY") Z DUAL vrátí datum `01.01.2009";

VYBRAT TO_DATE("15-01-10",`DD-MM-RR") Z DUAL vrátí datum `01/15/2010'.

Funkce pro převod řetězce na číselnou hodnotu TO_NUMBER (řetězec, formát). Nejběžnější hodnoty formátu jsou uvedeny v tabulce, proto zvažte použití této funkce na příkladech. Příklady:

VYBRAT TO_NUMBER(`100") Z DUAL vrátí číslo 100 SELECT TO_NUMBER(`0010.01", "9999D99") Z DUAL vrátí číslo 10,01;

VYBRAT TO_NUMBER("500 000"; "999G999") Z DUAL vrátí číslo 500 000.

Prvek RR ve formátu data

Prvek formátu data a času RR je podobný prvku formátu data a času YY, ale poskytuje další flexibilitu pro ukládání hodnot data v jiných stoletích. Prvek formátu datetime RR umožňuje uložit data 20. století v 21. století zadáním pouze posledních dvou číslic roku.

Pokud jsou poslední dvě číslice aktuálního roku 00 až 49, pak má vrácený rok stejné první dvě číslice jako aktuální rok.

Pokud jsou poslední dvě číslice aktuálního roku mezi 50 a 99, pak jsou první 2 číslice vráceného roku o 1 větší než první 2 číslice aktuálního roku.

Pokud jsou poslední dvě číslice aktuálního roku 00 až 49, pak jsou první 2 číslice vráceného roku o 1 menší než první 2 číslice aktuálního roku.

Pokud jsou poslední dvě číslice aktuálního roku mezi 50 a 99, pak má vrácený rok stejné první dvě číslice jako aktuální rok.

Funkce NVL

Funkce NVL je obecně nejpoužívanější. Funkce přijímá dva parametry: NVL(výraz1, výraz2). Pokud první parametr expr1 není NULL, pak funkce vrátí jeho hodnotu. Pokud je první parametr NULL, pak funkce místo toho vrátí hodnotu druhého parametru exp2.

Příklad: Vyberte NVL (město_dodavatele, n/a") od dodavatelů:

V příkazu SQL výše vrátí n/", pokud pole dodavatel_město obsahuje hodnotu null. V opačném případě vrátí hodnotu město_dodavatele.

Dalším příkladem použití funkce NVL v Oracle/PLSQL je:

vyberte dodavatele_id, NVL (dodavatel_desc, název_dodavatele) z dodavatelů.

Tento příkaz SQL se vrátí Jméno dodavatele pole pokud dodavatel_desc obsahuje hodnotu null. Jinak se to vrátí dodavatel_desc.

Poslední příklad: použití funkce NVL v Oracle/PLSQL je: vyberte NVL(provize, 0) z prodeje;

Tento příkaz SQL vrátil hodnotu 0 if komise pole obsahuje hodnotu null. Jinak by se to vrátilo provize pole.

Transformace NVL pro různé typy dat

Chcete-li převést nedefinovanou hodnotu na skutečnou hodnotu, použijte funkci NVL: NVL ( výraz1, výraz2), kde:

výraz1- Původní nebo vypočítaná hodnota, která může být nulová.

výraz2- Hodnota, která je nahrazena hodnotou null.

Poznámka: Funkci NVL lze použít pro převod libovolného datového typu, ale výsledek bude vždy stejný jako výraz1.

Převod NVL pro různé typy:

ČÍSLO-NVL (číselný sloupec, 9).

CHAR nebo VARCHAR2 - NVL (znaky|sloupec,"Nedostupné").

Laboratorní práce. Použití funkce NVL

Funkce NVL pro práci s hodnotami null v Oracle SQL.

Úkol:

Napište dotaz, který zobrazí informace o jménech a příjmení zaměstnanců z tabulky hr.zaměstnanci a také sazbu provize (sloupec COMMISSION_PCT) pro zaměstnance. V tomto případě u těch zaměstnanců, pro které není definována provize, je potřeba zobrazit hodnotu 0. Výsledek dotazu by měl být stejný jako na Obr. 3,5-1.

Rýže. 3.5 -1 (hodnoty zobrazené od řádku 51)

Řešení:

Odpovídající kód požadavku může vypadat takto:

SELECT first_name AS "First Name", last_name As "Last Name", NVL (COMMISSION_PCT, 0) As "Provize" FROM hr.employees.

Funkce NVL

Zvážit praktický příklad. Pole COMM v tabulce EMP může obsahovat hodnoty NULL. Při provádění dotazu jako:

SELECT EMPNO, ENAME, COMM, NVL(COMM, 0) NVL_COMM

OD SCOTT.EMP

hodnota NULL bude nahrazena nulou. Všimněte si, že pokud je hodnota generována pomocí funkce, je jí přiřazen alias. Výsledky dotazu budou vypadat takto:

EMPNO	ENAME	COMM	NVL_COMM
7369	KOVÁŘ		0
7499	ALLEN	300	300
7521	WARD	500	500
7566	JONES		0
7654	MARTIN	1400	1400
7698	BLAKE		0
7782	CLARK		0
7839	KRÁL		0
7844	SOUSTRUŽNÍK	0	0
7900	JAMES		0
7902	BROD		0
7934	MLYNÁŘ		0