7. blok Funkce. Studijní cíl

7. blok

Funkce

Studijní cíl Tento blok popisuje základní vestavěné funkce databázového prostředí Oracle. Popisuje rozdělení na základní skupiny, na funkce analytické, konverzní, funkce pro práci s datem a časem, matematické, řetězcové a další. Vybrané funkce jsou popsány podrobně včetně příkladů. Doba nutná k nastudování

3 - 4 hodiny

Průvodce studiem Při studiu tohoto bloku se předpokládá, že čtenář je obeznámen se základní syntaxí příkazu SELECT, spojováním tabulek, použitím vyhledávacích podmínek a základních funkcí. 1. Vestavěné funkce Při psaní databázových dotazů se můžeme dostat do situace, kdy nám samotný jazyk SQL nebude dostačovat. Pro tyto případy nabízí databázové servery řadu vestavěných funkcí. Jelikož identifikátory funkcí ani jejich práce není nijak definována standardem SQL, funkce se na různých databázových platformách liší. My si budeme dále povídat o vestavěných funkcích na databázových serverech Oracle. Vestavěné funkce pomáhají řešit problémy, na které SQL nestačí, případně zjednodušují práci. V prostředí databázového serveru Oracle můžeme vestavěné funkce rozdělit do následujících kategorií:        

Analytické Konverzní Funkce pro práci s datem a časem Matematické Funkce pro práci s hodnotou NULL Řetězcové funkce Objektové funkce Funkce pro práci s formátem XML

Ne všemi kategoriemi se budeme zabývat podrobně. Kategorie jako objektové funkce a funkce pro práci s XML přesahují rámec tohoto bloku. David Žák, Jiří Zechmeister, Tomáš Váňa IDAS1/7 - Funkce

1

2. Analytické funkce Jedná se o sadu funkcí pracujících vždy nad množinou dat, které slouží k sumarizaci. Výstupem z analytických funkcí jsou tedy souhrnná data. Blíže se budeme zabývat analytickými funkcemi v některé z dalších kapitol. Zde si uvedeme pouze přehled nejdůležitějších funkcí. Funkce AVG() COUNT() DENSE_RANK() FIRST() LAG() LAST() LEAD() MAX() MIN() RANK() ROW_NUMBER() SUM() VARIANCE()

Popis Průměrná hodnota Počet hodnot Skutečné pořadí hodnoty v řadě hodnot Vrací první hodnotu podle pořadí funkce DENSE_RANK Umožňuje přístup k datům z předchozích řádků výstupu Vrací poslední hodnotu podle pořadí funkce DENSE_RANK Umožňuje přístup k datům z následujících řádků výstupu Maximální hodnota Minimální hodnota Pořadí hodnoty v sadě hodnot Pořadové číslo řádku ve výstupu Součet hodnot Rozptyl

David Žák, Jiří Zechmeister, Tomáš Váňa IDAS1/7 - Funkce

2

3. Konverzní funkce Jak napovídá jejich název, slouží konverzní funkce ke konverzi dat mezi datovými typy. Převádět můžeme čísla na řetězce a zpět, data na řetězce a zpět atd. V přehledu si představíme význam jednotlivých konverzních funkcí a nejpoužívanější si předvedeme na příkladu. Funkce

Popis Převede ASCII řetězec na ASCII řetězec ve znakové ASCIISTR() sadě databáze BIN_TO_NUM() Převede bitový vektor na číslo CAST() Přetypování COMPOSE() Převede řetězec na UNICODE CONVERT() Převede řetězec z jedné znakové sady do druhé DECOMPOSE() Převede UNICODE řetězec do řetězce Převede řetězec obsahující hexadecimální číslice na HEXTORAW() typ RAW NUMTOYMINTERVAL() Převede číslo na typ INTERVAL YEAR TO MONTH SCN_TO_TIMESTAMP() Převádí SCN na časové razítko (typ TIMESTAMP) TIMESTAMP_TO_SCN() Převádí časové razítko na SCN TO_BINARY_DOUBLE() Převede hodnotu na typ BINARY_DOUBLE TO_CHAR() Převádí datum na řetězec TO_DATE() Převádí řetězec na datum TO_DSINTERVAL() Převádí řetězec na typ INTERVAL DAY TO SECOND TO_NUMBER() Převádí řetězec na číslo TO_TIMESTAMP() Převádí řetězec na časové razítko (TIMESTAMP) Převádí řetězec na časové razítko s časovou zónou TO_TIMESTAMP_TZ() (TIMESTAMP WITH TIMEZONE) 3.1. CAST Pro převody mezi jednotlivými vestavěnými datovými typy slouží funkce CAST(). Činnost si předvedeme na několika příkladech. Převeďte aktuální datum na řetězec. SELECT CAST( sysdate AS VARCHAR2(30)) FROM dual;


3

Převeďte číselní řetězec na číslo. SELECT CAST( '1234,5' AS NUMBER(5,1)) FROM dual;

3.2. CONVERT Funkce CONVERT() slouží pro převod řetězců mezi znakovými sadami. Tato funkce je velice užitečná, pokud například chceme data zobrazovat v aplikaci, která používá jinou znakovou sadu, než je znaková sada databáze. SELECT CONVERT('ě š č ř ž ý á í é', 'US7ASCII', 'UTF8') FROM dual;

3.3. TIMESTAMP_TO_SCN, SCN_TO_TIMESTAMP Obě funkce slouží k převodu mezi časovými razítky a SCN. Pod zkratkou SCN rozumíme v Oracle „system change number“. Je to číslo, které se v databázi inkrementuje vždy, když dojde ke změně a tato změna je zapsána do protokolu. Také je inkrementováno po potvrzení transakce. Číslo potom slouží hlavně pro obnovu databáze.


4

3.4. TO_CHAR, TO_DATE Jsou funkce, které slouží pro převod z data na řetězec a zpět. Pro převod se využívá formátovacích řetězců. Symbol D DD DAY HH HH24 MI MM MONTH SS FF YY YYYY Q

Popis Pořadové číslo dne v týdnu (1 – 7) Den v měsíci (1 – 31) Slovní reprezentace dne v týdnu Hodiny ve formátu 0-12 Hodiny ve formátu 0-24 Minuty Měsíc (0 – 12) Slovní reprezentace měsíce Sekundy Milisekundy Rok vyjádřený dvěma číslicemi Rok vyjádřený čtyřmi číslicemi Čtvrtletí vyjádřené číslicí (1-4)

Použití si ukažme na příkladech. Zobrazte aktuální Hodina:minuta.

datum

a

čas

ve

formátu

Den.Měsíc.Rok

SELECT TO_CHAR(sysdate, 'DD.MM.YYYY HH24:MI') FROM dual;

Zobrazte měsíce.

název

aktuálního

dne

v týdnu

a

název

aktuálního

SELECT TO_CHAR(sysdate, 'DAY, MONTH') FROM dual;

Převeďte řetězec „10:11, 01.01.12“ na datum. SELECT TO_DATE( '10:11, 01.01.12', 'HH24:MI, DD.MM.YY') FROM dual;


5

4. Funkce pro práci s datem a časem Část funkcí pro práci s datem a časem jsme již zařadili do kategorie konverzních funkcí. Jedná se o funkce, které slouží k převodu typů data do řetězců a zpět. Tady v této části si naopak ukážeme funkce, které pracují již přímo s datem a časem. Funkce ADD_MONTHS() CURRENT_DATE CURRENT_TIMESTAMP EXTRACT() GREATEST() INTERVAL LEAST() LOCALTIMESTAMP MONTHS_BETWEEM() NEW_TIME() NEXT_DAY() SESSIONTIMEZONE SYSDATE SYSTIMESTAMP ROUND() TRUNC()

Popis K zadanému datu přidá zadaný počet měsíců a nové datum vrátí Vrátí aktuální datum a čas z časové zóny serveru Vrátí aktuální časové razítko včetně časové zóny Extrahuje z data vybranou část Vrátí nejvyšší hodnoty ze zadaného výčtu Přidává k datu zvolený interval Vrátí nejnižší hodnotu ze zadaného výčtu Vrací aktuální časové razítko bez časové zóny Vrací počet měsíců mezi dvěma daty Převádí čas mezi časovými zónami Vrátí datum následujícího zadaného dne v týdnu Vrací aktuální časovou zónu Aktuální systémové datum a čas Aktuální systémové časové razítko Zaokrouhlí datum na zvolenou přesnost Ze zadaného data ořeže čas

4.1. ADD_MONTHS Funkce přičítá k zadanému datu daný počet měsíců. Pokud chceme měsíce odečítat, můžeme zadat záporný počet měsíců. Zadáme-li desetinné číslo, je automaticky oříznuto na nejbližší menší celé číslo. Nelze tedy přičíst polovinu měsíce. Fungování si ukažme na příkladech. K aktuálnímu datu přičtěte 14 měsíců. SELECT ADD_MONTHS(sysdate, 14) FROM dual;

Od aktuálního data odečtěte jeden měsíc. SELECT ADD_MONTHS(sysdate, -1) FROM dual;


6

4.2. CURRENT_DATE, CURRENT_TIMESTAMP, SESSIONTIMEZONE, SYSDATE, SYSTIMESTAMP, LOCALTIMESTAMP Identifikátory CURRENT_DATE, CURRENT_TIMESTAMP, SESSIONTIMEZONE, SYSDATE, SYSTIMESTAMP a LOCALTIMESTAMP nepředstavují funkce jako takové. Jedná se o speciální systémové proměnné, které nám poskytují informace týkající se data a času. Jejich význam si nejlépe ukážeme na příkladu: SELECT CURRENT_DATE, CURRENT_TIMESTAMP, SESSIONTIMEZONE, SYSDATE, SYSTIMESTAMP, LOCALTIMESTAMP FROM dual;

Z výsledků je zřejmé, že proměnné SYSDATE a CURRENT_DATE jsou identické a můžeme je zaměňovat. Proměnná SESSIONTIMEZONE nemá žádný ekvivalent a zobrazuje název časového pásma. Rozdíl mezi CURRENT_TIMESTAMP a SYSTIMESTAMP je také zřejmý. CURRENT_TIMESTAMP nedisponuje přesností na tisíciny sekundy a zobrazuje název časového pásma, zatímco SYSTIMESTAMP disponuje přesností na tisíciny sekundy a zobrazuje informaci o časovém posunu. LOCALTIMESTAMP zobrazuje aktuální časové razítko s přesností na jednotky sekund bez dodatečných informací.


7

4.3. EXTRACT Funkce EXTRACT(), jak již napovídá název, slouží k extrakci jednotlivých části ze zadaného data. Z data můžeme získat rok, měsíc, den, hodiny, minuty a sekundy. Ukažme si všechny možnosti na složitějším příkladu: SELECT 'Aktuální datum a čas', TO_CHAR(systimestamp) FROM dual UNION SELECT 'EXTRACT(YEAR FROM systimestamp)', TO_CHAR(EXTRACT(YEAR FROM systimestamp)) FROM dual //rok UNION SELECT 'EXTRACT(MONTH FROM systimestamp)', TO_CHAR(EXTRACT(MONTH FROM systimestamp)) FROM dual //měsíc UNION SELECT 'EXTRACT(DAY FROM systimestamp)', TO_CHAR(EXTRACT(DAY FROM systimestamp)) FROM dual //den UNION SELECT 'EXTRACT(HOUR FROM systimestamp)', TO_CHAR(EXTRACT(HOUR FROM systimestamp)) FROM dual //hodiny UNION SELECT 'EXTRACT(MINUTE FROM systimestamp)', TO_CHAR(EXTRACT(MINUTE FROM systimestamp)) FROM dual //minuty UNION SELECT 'EXTRACT(SECOND FROM systimestamp)', TO_CHAR(EXTRACT(SECOND FROM systimestamp)) FROM dual;//sekundy


8

4.4. ROUND O funkci ROUND() můžeme tvrdit, že je přetížená. Existuje totiž její verze jak pro čísla, tak pro datum. V této části se budeme zabývat funkcí pro zaokrouhlování data. Datum a čas je možné zaokrouhlit na specifikovanou přesnost. Přesnost je definována zástupným řetězcem stejným, jako používá funkce TO_CHAR(). Nejnižší přesnost, na kterou je možné zaokrouhlovat, jsou minuty. Fungování funkce ROUND si ukažme na několika příkladech. Určete datum, kdy začíná čtvrtletí, ve kterém se nachází datum 14. 8. 2012. SELECT ROUND(TO_DATE('14.08.2012','DD.MM.YYYY'), FROM dual;

'Q')

Určete datum prvního dne aktuálního měsíce. SELECT ROUND(sysdate, 'MM') FROM dual;


9

5. Matematické funkce Sada matematických funkcí obsahuje běžně známé funkce jako goniometrické funkce, funkce pro umocňování, logaritmické funkce a statistické funkce. V následujícím přehledu si ukážeme nejpoužívanější z nich. Funkce ABS() ACOS() ASIN() ATAN() ATAN2() AVG() BITAND() CEIL() CORR() COS() COSH() EXP() FLOOR() GREATEST() LEAST() LN() LOG() MAX() MEDIAN() MIN() MOD() POWER() REMAINDER() ROUND() SIGN() SIN() SINH() SQRT() STDDEV() SUM() TAN() TANH() TRUNC() VARIANCE()

Popis Absolutní hodnota Arc cosinus Arc sinus Arc tangens Arc tangens parametru děleného druhým parametrem Průměrná hodnota Bitový součin Zaokrouhlení na nejbližší menší nebo stejné celé číslo Výpočet korelačního koeficientu Cosinus Hyperbolický cosinus Vrací e umocněné na zadaný exponent Zaokrouhlení na nejbližší větší nebo stejné celé číslo Vrací nejvyšší hodnotu ze zadaných parametrů Vrací nejmenší hodnotu ze zadaných parametrů Přirozený logaritmus Logaritmus bez definovaného základu Maximální hodnota Medián Minimální hodnota Zbytek po celočíselném dělení Umocnění čísla Zbytek po celočíselném dělení Zaokrouhlení Vrací znaménko Sinus Hyperbolický sinus Druhá odmocnina Směrodatná odchylka Suma Tangens Hyperbolický tangens Oříznutí čísla na zvolený počet desetinných míst Rozptyl


10

5.1. Goniometrické funkce Do skupiny goniometrických funkcí patří SIN(), COS(), TAN(), SINH(), COSH(), TANH(). Parametrem těchto funkcí je vždy úhel zadaný v radiánech. Pokud chceme použít jako parametr stupně, musíme provést přepočet: Určete cosinus 135°. SELECT COS(135 * 3.14159265359/180) FROM dual;

5.2. Logaritmické a exponenciální funkce Mezi matematickými funkcemi samozřejmě nesmí chybět funkce pro výpočet logaritmu. K dispozici máme výpočet přirozeného logaritmu LN(), kde základ tvoří Eulerovo číslo e a běžný logaritmus LOG(), kde si základ volíme parametrem. Databázový server také poskytuje inverzní funkci k přirozenému logaritmu EXP(), která slouží k umocňování Eulerova číslo e. SELECT LN(2), LOG(EXP(1), 2), LOG(10,10), EXP(2) FROM dual;

5.3. CEIL, FLOOR Funkce CEIL() a FLOOR() slouží k zaokrouhlování desetinných čísel. Funkce CEIL() zaokrouhluje tak, že výsledkem je nebližší větší nebo stejné celé číslo. Funkce FLOOR() pracuje obráceně a vrací nejbližší menší, nebo stejné číslo. Ukažme si fungování na příkladu. Určete nejbližší menší a nejbližší větší celé číslo od čísla 7.3890560989306502272304274605750078132. SELECT CEIL(7.3890560989306502272304274605750078132), FLOOR(7.3890560989306502272304274605750078132) FROM dual;


11

5.4. GREATEST, LEAST Pokud potřebujeme vybrat ze sady hodnot největší, nebo nejmenší hodnotu, použijeme funkci GREATEST() pro vybrání nejvyšší LEAST()a pro nejnižší hodnotu ze seznamu. Seznam, ze kterého funkce hodnotu vybírá, je zadán formou parametrů. Počet parametrů, tedy délka seznamu, není omezen. Přetížené verze těchto funkcí pracují také s datem a časem. Způsob volání těchto funkcí si předvedeme na triviálním příkladu. SELECT GREATEST(1,2,3,4,5,6,7), LEAST(1,2,3,4,5,6,7) FROM dual;

5.5. Umocňování čísel Pro práci s mocninami máme k dispozici dvě funkce. Funkci POWER(), která slouží pro umocnění čísla na libovolný exponent a funkci SQRT(), která počítá druhou odmocninu. Funkce POWER() vyžaduje dva parametry, kde prvním parametrem je umocňované číslo a druhým parametrem je exponent. Pokud chceme počítat jinou, než druhou odmocninu, musíme použít místo SQRT() funkci POWER() a zadat obrácenou hodnotu exponentu. Ukažme si na jednoduchém příkladu. Určete druhou, třetí a čtvrtou mocninu čísla 3. SELECT POWER(3,2), POWER(3,3), POWER(3,4) FROM dual;

Určete druhou, třetí a čtvrtou odmocninu čísla 4892. SELECT SQRT(4892), POWER(4892,1/3), POWER(4892,1/4) FROM dual;

Výsledky ze druhého dotazu jsou záměrně zaokrouhleny na dvě desetinná místa. Ve skutečnoti pracují funkce SQRT() a POWER() na maximální počet desetinných míst. David Žák, Jiří Zechmeister, Tomáš Váňa IDAS1/7 - Funkce

12

5.6. MOD, REMAINDER Funkce MOD() i funkce REMAINDER() slouží k výpočtu zbytku po celočíselném dělení. Každé funkce však k výpočtu přistupuje rozdílně. Zatímco funkce MOD() využívá při výpočtu funkci FLOOR() a vrací tedy vždycky kladný zbytek, funkce REMAINDER() používá funkci CEIL() a v některých případech vrací zbytek záporný. Jako názornou ukázku si zvolíme jednoduchý příklad 8 / 5. Podívejme se, co bude výstupem z těchto funkcí. SELECT MOD(8,5), REMAINDER(8, 5) FROM dual;

Výstup z funkce MOD() je jasný. Tedy 8 / 5 = 1, zbytek 3. Méně jasný je již výstup z funkce REMAINDER(). Pro pochopení fungování této funkce, si rozebereme jednotlivé kroky výpočtu. 1. Nejdříve se vypočítá podíl zadaných čísel 2. Na tento podíl se aplikuje funkce FLOOR() nebo CEIL() 3. Výsledek této funkce se vynásobí dělitelem a odečte od děleného čísla. SELECT 'CEIL' as "fce", 8/5 as "a/b", CEIL(8/5) as "fce(a/b)", 8-(CEIL(8/5)*5) as "a-(fce(a/b)*a)", 'MOD' as "Alternativa" FROM dual UNION SELECT 'FLOOR', 8/5, FLOOR(8/5), 8-(FLOOR(8/5)*5), 'REMAINDER' FROM dual;


13

6. Hodnota NULL a funkce pro práci s hodnotou NULL Nejdříve si řekněme, co si můžeme představit pod pojmem hodnota NULL. V databázových systémech slouží hodnota NULL k identifikaci stavu, kdy není zadána hodnota, buňka tabulky je tedy prázdná. Můžeme sice namítat, že tato hodnota není třeba, protože místo číselné hodnoty můžeme použít nulu a v řetězcové hodnotě můžeme použít prázdný řetězec. Ale jak bychom potom rozpoznaly, zda uživatelem zadaná nula představuje stav prázdné buňky, nebo zda se jedná o korektní hodnotu? Na další úskalí bychom narazili u složitých datových typů jako je datum a čas, objekty atd. Než si představíme funkce pro práci s hodnotou NULL, povíme si některá pravidla pro práci s touto hodnotou.   



Pokud použijeme hodnotu NULL ve výpočtu, výsledkem bude vždy NULL Pokud spojíme řetězec s hodnotu NULL, hodnota NULL se ignoruje Každá agregační funkce přistupuje k hodnotě NULL jinak. Příkladem je funkce COUNT(*) a COUNT(atribut), kde COUNT(*) hodnotu NULL neignoruje a COUNT(atribut) ano Při použití hodnoty NULL v podmínce není možné použít znaménko „je rovno“, ale je nutné použít konstrukci IS NULL

Dalším využitím hodnoty NULL je:  

Nahrazení chybějících sloupců při použití množinových operátorů Vynucení vyplnění atributu tabulky (použitím definice NOT NULL)

6.1. Funkce pro práci s hodnotou NULL Základní funkce pracující s hodnotou NULL jsou čtyři NVL(), NULLIF() s LNNVL().


NVL2(),

14

6.2. NVL První z funkcí pro práci s hodnotou NULL je funkce NVL(). Prvním vstupním parametrem je název atributu a druhým může být hodnota, nebo také název atributu. Funkce potom vyhodnocuje výraz tak, že pokud hodnota v prvním parametru je rovna NULL, výstupem bude hodnota parametru druhého. Pokud nebude rovna NULL, výstupem bude hodnota prvního parametru. Příklady si budeme demonstrovat na tabulkou PRODUKTY, která má následující strukturu.

Zobrazte označeni a cenu produktů z tabulky PRODUKTY. Pokud nebude cena zadána, vypište nulu. SELECT oznaceni, NVL(cena,0) FROM produkty;

6.3. NVL2 NVL2() má na rozdíl od funkce NVL() parametry tři. Prvním parametrem je název testovaného atributu nebo hodnota. Druhým parametrem je hodnota, která bude zobrazena, pokud nebude první argument nabývat hodnoty NULL. Třetí parametr bude zobrazen, pokud nebude hodnota prvního parametru rovna hodnotě NULL.


15

Zobrazte označeni a cenu produktů z tabulky PRODUKTY. K ceně produktu připočtěte zdražení 14%. Pokud cena není zadána, vypište 0. SELECT oznaceni, NVL2(cena,cena*1.14,0) FROM produkty;

6.4. NULLIF Funkce NULLIF() přijímá dva parametry a vrací hodnotu NULL v případě, že se zadané parametry rovnají. Pokud se nerovnají, vrací první parametr. 6.5. LNNVL LNNVL() jako jediná nepracuje na principu podmínek, ale slouží k vyhodnocování podmínek, kdy se ve výrazu mohou objevit hodnoty NULL. Pokud tuto funkci použijeme do podmínky, dotaz vrátí ty řádky, které ve výrazu obsahují hodnotu NULL a běžnou podmínkou by tedy neprošly. Zobrazte označeni a cenu produktů z tabulky PRODUKTY, kde cena je větší než 0. SELECT oznaceni, cena FROM produkty WHERE cena > 0;

Do výsledku se dostaly všechny produkty, teré mají cenu vyplněnou. Pokud bychom ale chtěly vědět, které se do této sady výsledků nedostaly, použijeme funkce LNNVL(). SELECT oznaceni, cena FROM produkty WHERE LNNVL(cena > 0);


16

7. Řetězcové funkce Funkce ASCII() CHR() CONCAT()

Popis Převádí znak na číselný ASCII kód Převádí číselný ASCII kód na znak Spojení dvou řetězců První písmena slov v řetězci převede na velká, ostatní na INITCAP() malá INSTR() Vyhledávání v řetězci LENGTH() Délka řetězce LOWER() Převod na malá písmena LPAD() Rozšíření řetězců zleva LTRIM() Odstranění bílých znaků zleva REGEXP_COUNT() Počet výskytů podle regulárního výrazu REGEXP_INSTR() Vyhledávání v řetězci podle regulárního výrazu REGEXP_REPLACE() Nahrazení v řetězci podle regulárního výrazu REGEXP_SUBSTR() Získání části řetězce podle regulárního výrazu REPLACE() Nahrazení v řetězci REVERSE() Výpis řetězce odzadu RPAD() Rozšíření řetězců zprava RTRIM() Ořezání bílých znaků zprava SUBSTR() Získání podřetězce TRIM() Ořezání bílých znaků z obou konců řetězce UPPER() Převod na velká písmena ROURY || Spojení řetězců VSIZE() Velikost řetězce v bitech 7.1. ASCII, CHR Jsou vzájemně inverzní funkce. ASCII() slouží k převodu řetězce na číselnou reprezentaci. Funkce CHR() slouží k převodu číselné reprezentace zpět na znak. SELECT ASCII('A'), CHR(65) FROM dual;

7.2. Spojování řetězců Spojení řetězců můžeme realizovat pomocí funkce CONCAT() nebo můžeme použít operátor roury. Princip fungování obou možností je identický. Jelikož funkce CONCAT() umožňuje současné spojení pouze dvou řetězců, je použití operátoru roura komfortnější. Pro spojení více řetězců pomocí funkce CONCAT(), můžeme funkce vnořovat. David Žák, Jiří Zechmeister, Tomáš Váňa IDAS1/7 - Funkce

17

SELECT CONCAT('Funkce pro ', 'práci s řetězci'), 'Funkce'||' pro '|| 'práci'||' s'||' řetězci' FROM dual;

7.3. Změna velikosti znaků v řetězci Na převod velkých znaků na malé a zpět, disponuje databázový server několika funkcemi. Můžeme převádět buď celé řetězce na malá písmena pomocí LOWER(), celé řetězce na velká písmena pomocí UPPER() nebo kombinace obou přístupů pomocí funkce INITCAP(). INITCAP() převede celý řetězec na malá písmena a potom u každého slova první písmeno na velké. SELECT UPPER('Funkce pRo prÁci s řetĚzci') FROM dual;

SELECT LOWER('Funkce pRo prÁci s řetĚzci') FROM dual;

SELECT INITCAP('Funkce pRo prÁci s řetĚzci') FROM dual;

7.4. LPAD, RPAD Slouží pro rozšiřování řetězců na danou velikost přidáváním znaků. LPAD() doplňuje znaky zleva, RPAD() zprava. Znak i velikost, na kterou budeme znak rozšiřovat, zadáme pomocí parametrů. Aby bylo možné řetězec rozšířit, musí být jeho délka větší než 0. Počet, na kolik znaků budeme rozšiřovat, musí být větší než 0.


18

Pomocí těchto funkcí si můžeme vytvořit například jednoduchý graf. SELECT oznaceni, RPAD('|', NVL(cena/1000,0)+1, '*') FROM produkty;

Graf zobrazuje ke každému produktu jednu hvězdičku za každých 1000 jednotek ceny. 7.5. REPLACE V mnoha případech potřebujeme v řetězci nahradit některé znaky, případně potřebujeme nahradit celé části řetězců. Pro tento případ existuje funkce REPLACE(), která nahradí všechny výskyty vzoru v řetězci zadaným řetězcem. Fungování funkce si předvedeme na příkladu, kde ve věte nahradíme znak mezery, znakem podržítko. SELECT REPLACE('Funkce FROM dual;

pro

práci

s

řetězci','

','_')

7.6. INSTR Funkce INSTR() slouží k vyhledávání vzoru uvnitř řetězce a vrací jeho pozici. Pomocí parametrů můžeme definovat, odkud se začne vyhledávat a pozici, kolikátého výskytu si přejeme zobrazit. Prvním parametrem funkce INSTR() je zdrojový řetězec, ve kterém budeme vyhledávat. Druhým parametrem je vzor, který budeme vyhledávat. Třetí parametr je již nepovinný a definuje pozici, od které se začne vyhledávat. Pokud tento parametr necháme prázdný, začne se vyhledávat od začátku řetězce. Chceme-li vyhledávat od konce řetězce, použijeme zápornou hodnotu. Poslední parametr je také nepovinný a slouží pro případ, že chceme určit, kolikátý výskyt nás zajímá. Pokud tedy chceme znát polohu druhého výskytu vzoru, zadáme do parametru David Žák, Jiří Zechmeister, Tomáš Váňa IDAS1/7 - Funkce

19

dvojku. Pokud poslední parametr nevyplníme, automaticky se zobrazuje pozice prvního výskytu. Pokud není vzor nalezen, funkce vrátí hodnotu 0. Nalezněte pozici druhého výskytu písmene „p“ v řetězci „Funkce pro práci s řetězci“ počítáno odzadu. SELECT INSTR('Funkce pro práci s řetězci','p',-1,2) FROM dual;

Fukci INSTR() se hodí pro kombinaci s funkcí SUBSTR(). 7.7. SUBSTR Mohou nastat situace, kdy potřebujeme získat část z řetězce. Například pokud bude v řetězci uložen název souboru a budeme chtít znát jen příponu. K získání části řetězce slouží funkce SUBSTR(). Funkce má tři parametry. První je povinný a představuje řetězec, ze kterého chceme získat část. Druhý, také povinný parametr, je pozice, od které začneme získávat podřetězec. Pokud chceme podřetězec získávat odzadu, zvolíme zápornou hodnotu. Můžeme získat například poslední tři znaky zadáním -3. Poslední parametr není povinný a definuje délku podřetězce. Databázový server Oracle čísluje pozice znaků v řetězci od 1. První znak je tedy na pozici 1. Získejte posledních 10 znaků z řetězce „Funkce pro práci s řetězci“. SELECT SUBSTR('Funkce pro práci s řetězci',-10) FROM dual;


20

Jak bylo zmíněno již dříve, nabízí se kombinace funkcí INSTR() a SUBSTR(). Kombinaci si předveďme na příkladu. Z názvu souboru „my.stats.conf“ získejte příponu. SELECT SUBSTR('my.stats.conf', INSTR('my.stats.conf','.',-1,1)+1 ) FROM dual;

Pomocí funkce INSTR() jsme nalezli první výskyt tečky odzadu a tuto nalezenou pozici použijeme jako počáteční pozici hledané přípony.

Pojmy k zapamatování Příkazy a funkce: TO_DATE(), TO_NUMBER(), TO_CHAR(), CAST(), EXTRACT(), SYSDATE, SYSTIMESTAMP, NVL(), NVL2(), LPAD(), RPAD(), TRIM(), SUBSTR(), ||, AVG(), MIN(), MAX() Problém:

použití základních vestavěných funkcí v běžných dotazech

Shrnutí V této lekci jsme si představili základní dělení vestavěných funkcí databázového systému Oracle.  Analytické funkce slouží k výpočtu součtu, průměru, minimální a maximální hodnoty nebo k určení počtu hodnot ve sloupci nebo ve výsledku dotazu. Mezi analytické funkce patří: AVG(), MIN(), MAX(), RANK(), …  Konverzní funkce slouží k převodu mezi datovými typy. Hlavním využitím je formátování data a času a převod řetězců do data a času. Mezi konverzní funkce patří například: TO_CHAR(), TO_DATE(), CAST(), ...  Funkce pro práci s datem a časem slouží pro úpravu data a času, přidávání časových intervalů a převod mezi časem a intervalem. Mezi tyto funkce patří: EXTRACT(), ADD_MONTH(), INTERVAL() a také pseudoslouce SYSDATE a SYSTIMESTAMP, které poskytují informace o aktuálním datu a času.


21

 Matematické funkce poskytují implementace základních matematických operací, jako jsou goniometrické funkce, logaritmus, exponenciální funkce, výběr minima, maxima a jiné. Konkrétně do této kategorie patří: SIN(), COS(), POWER(), EXP(), SQRT(), MIN(), MAX(), ROUND(), …  Řetězcové funkce slouží pro změnu velikosti písmen, získávání podřetězců a vyhledávání v řetězci, odstraňování bílých znaků a spojování. Konkrétně do této kategorie patří: LOWER(), UPPER(), INSTR(), SUBSTR(), REPLACE(), CONCAT(), ||, TRIM(), …  Funkce pro práci s hodnotou NULL slouží k vyhodnocování podmínek s hodnotou NULL a testování proměnných na hodnotu NULL. Patří sem: NVL(), NVL2(), IFNULL(), …

Otázky na procvičení 1. Pomocí jakého zástupného řetězce byste převedli řetězec „2012-12-23“ na datum? Využijte funkci TO_DATE(). 2. Pomocí jaké funkce můžete převést řetězec na číslo? 3. Jaký je rozdíl mezi hodnotami SYSDATE a SYSTIMESTAMP? 4. K čemu slouží funkce EXTRACT()? 5. Jak byste parametrizovali funkce POWER() pro výpočet 4 odmocniny čísla 6? 6. Jaký je rozdíl mezi funkcí MOD() a REMAINDER()? 7. Jakou funkcí převedete písmen „A“ do číselného kódu ascii? 8. Jaká je výhoda operátoru || proti funkci CONCAT()? 9. K čemu slouží funkce REPLACE()? 10. Jaká je rozdíl mezi funkcí NVL() a NVL2()?

Odkazy a další studijní prameny    

http://www.techonthenet.com/oracle (syntaxe příkazů SQL jazyka a funkcí) http://www.sqlcourse.com (interaktivni SQL trénink) http://www.oracle.com/technetwork/database/enterpriseedition/documentation (dokumentace k databázové platformě Oracle) http://www.penguin.cz/noviny/?id=chip/index (seriál Databáze standardu SQL z časopisu CHIP)


22

Odkazy a další studijní prameny   

ŠIMŮNEK, M. SQL, kompletní kapesní průvodce. Grada Publishing, 1999. ISBN 80-7169-692-7. STEPHENS, K.R., PLEW, R.R. Naučte se SQL za 21 dní. Praha: Computer Press, 2004. ISBN 80-7226-870-8. GROFF, J.R., WEINBERG, P.N. SQL - kompletní průvodce. Praha: Computer Press, 2005. ISBN 80-251-0369-2.


23

7. blok Funkce. Studijní cíl

Recommend Documents