Korzystanie z funkcji konwersji

Oprócz funkcji narzędziowych SQL, wbudowana biblioteka funkcji Oracle zawiera funkcje konwersji typów. Mogą istnieć scenariusze, w których zapytanie oczekuje danych wejściowych w określonym typie danych, ale otrzymuje je w innym typie danych. W takich przypadkach Oracle niejawnie próbuje przekonwertować nieoczekiwaną wartość na zgodny typ danych, który można zastąpić w miejscu, a ciągłość aplikacji nie jest zagrożona. Konwersja typów może być wykonana niejawnie przez firmę Oracle lub jawnie przez programistę.

Niejawna konwersja typu danych działa w oparciu o macierz, która przedstawia obsługę Oracle dla wewnętrznego rzutowania typu. Oprócz tych reguł firma Oracle oferuje funkcje konwersji typów, których można używać w zapytaniach do jawnej konwersji i formatowania. W rzeczywistości zaleca się wykonanie jawnej konwersji zamiast polegania na inteligencji oprogramowania. Chociaż niejawna konwersja działa dobrze, ale w celu wyeliminowania prawdopodobieństwa pochylenia, w których złe dane wejściowe mogą być trudne do wewnętrznego typowania.

Niejawna konwersja typu danych

Wartość VARCHAR2 lub CHAR może zostać niejawnie przekonwertowana na wartość typu NUMBER lub DATE przez Oracle. Podobnie wartość typu NUMBER lub DATA może być automatycznie konwertowana na dane znakowe przez serwer Oracle. Należy zauważyć, że niejawna konwersja międzykonwersyjna ma miejsce tylko wtedy, gdy znak reprezentuje odpowiednio prawidłową liczbę lub wartość typu daty.

Na przykład przeanalizuj poniższe zapytania SELECT. Oba zapytania dadzą ten sam wynik, ponieważ Oracle wewnętrznie traktuje 15000 i „15000” jako takie same.

Zapytanie-1

SELECT employee_id,first_name,salary
FROM employees
WHERE salary > 15000;

Zapytanie-2

SELECT employee_id,first_name,salary
FROM employees
WHERE salary > '15000';

Jawna konwersja typu danych

Funkcje konwersji SQL to funkcje jednowierszowe, które mogą rzutować na typ wartość kolumny, literał lub wyrażenie. TO_CHAR, TO_NUMBER i TO_DATE to trzy funkcje, które wykonują krzyżową modyfikację typów danych.

Funkcja TO_CHAR

Funkcja TO_CHAR służy do rzutowania wartości numerycznej lub daty na typ znakowy za pomocą modelu formatu (opcjonalnie).

Składnia

TO_CHAR(number1, [format], [nls_parameter])

W przypadku konwersji liczb na znaki parametry nls mogą służyć do określania znaków dziesiętnych, separatora grup, modelu waluty lokalnej lub modelu waluty międzynarodowej. Jest to specyfikacja opcjonalna - jeśli nie jest dostępna, zostaną użyte ustawienia nls na poziomie sesji. W przypadku konwersji daty na znaki parametr nls może służyć do określania odpowiednio nazw dni i miesięcy.

Daty można formatować w wielu formatach po konwersji na typy znaków za pomocą funkcji TO_CHAR. Funkcja TO_CHAR służy do wyświetlania dat w Oracle 11g w określonym formacie. Modele formatu uwzględniają wielkość liter i muszą być ujęte w pojedyncze cudzysłowy.

Rozważ poniższe zapytanie SELECT. Kwerenda formatuje kolumny HIRE_DATE i SALARY tabeli EMPLOYEES za pomocą funkcji TO_CHAR.

SELECT first_name,
       TO_CHAR (hire_date, 'MONTH DD, YYYY') HIRE_DATE,
	   TO_CHAR (salary, '$99999.99') Salary FROM employees WHERE rownum < 5; FIRST_NAME HIRE_DATE SALARY -------------------- ------------------ ---------- Steven JUNE 17, 2003 $24000.00
Neena                SEPTEMBER 21, 2005  $17000.00 Lex JANUARY 13, 2001 $17000.00
Alexander            JANUARY   03, 2006   $9000.00

Pierwszy TO_CHAR służy do konwersji daty wynajmu na format daty MIESIĄC DD, RRRR, tj. Miesiąc zapisany i dopełniony spacjami, następnie dwucyfrowy dzień miesiąca, a następnie czterocyfrowy rok. Jeśli wolisz wyświetlać nazwę miesiąca w postaci znaków mieszanych (czyli „grudzień”), po prostu użyj tego przypadku w argumencie formatu: („Miesiąc DD, RRRR”).

Druga funkcja TO_CHAR na rysunku 10-39 służy do formatowania WYNAGRODZENIA w celu wyświetlenia znaku waluty i dwóch miejsc dziesiętnych.

Oracle oferuje kompleksowy zestaw modeli formatów. Poniższa tabela przedstawia listę modeli formatów, których można użyć do zapisywania dat i wartości liczbowych jako znak przy użyciu TO_CHAR.

Formatuj model	Opis
,(przecinek)	Zwraca przecinek w określonej pozycji. W modelu formatu liczbowego można określić wiele przecinków. Ograniczenia: element przecinka nie może rozpoczynać modelu formatu liczb. Przecinek nie może pojawić się po prawej stronie znaku dziesiętnego lub kropki w modelu formatu liczb.
.(Kropka)	Zwraca przecinek dziesiętny będący kropką (.) W określonej pozycji. Ograniczenie: w modelu formatu liczbowego można określić tylko jeden okres
$	Zwraca wartość z początkowym znakiem dolara
0	Zwraca wiodące zera. Zwraca zera końcowe.
9	Zwraca wartość z określoną liczbą cyfr ze spacją wiodącą, jeśli jest dodatnia, lub z wiodącym minusem, jeśli jest ujemna. Zera wiodące są puste, z wyjątkiem wartości zerowej, która zwraca zero dla części całkowitej liczby stałoprzecinkowej.
b	Zwraca puste miejsca dla części całkowitej liczby stałoprzecinkowej, gdy część całkowita jest równa zero (niezależnie od „0” sw modelu formatu).
do	Zwraca w określonej pozycji symbol waluty ISO (bieżącą wartość parametru NLS_ISO_CURRENCY).
re	Zwraca na określonej pozycji znak dziesiętny, który jest bieżącą wartością parametru NLS_NUMERIC_CHARACTER. Wartość domyślna to kropka (.). Ograniczenie: w modelu formatu liczbowego można określić tylko jeden znak dziesiętny.
EEE	Zwraca wartość używaną w notacji naukowej.
FM	Zwraca wartość bez wiodących lub końcowych spacji.
sol	Zwraca w określonej pozycji separator grupy (bieżąca wartość parametru NLS_NUMERIC_CHARACTER). W modelu formatu liczbowego można określić wiele separatorów grup. Ograniczenie: Separator grupy nie może pojawić się po prawej stronie znaku dziesiętnego lub kropki w modelu formatu liczb
L	Zwraca w określonej pozycji symbol waluty lokalnej (bieżącą wartość parametru NLS_CURRENCY).
MI	Zwraca wartość ujemną z końcowym znakiem minus (-). Zwraca wartość dodatnią z końcową spacją. Ograniczenie: element formatu MI może pojawić się tylko na ostatniej pozycji modelu formatu liczb.
PR	Zwraca wartość ujemną w. Może pojawić się tylko na końcu modelu formatu liczbowego.
RN, rm	Zwraca wartość jako cyfry rzymskie zapisane wielkimi literami. Zwraca wartość w postaci cyfr rzymskich zapisanych małymi literami. Wartość może być liczbą całkowitą z przedziału od 1 do 3999.
S	Zwraca wartość ujemną z początkowym lub końcowym znakiem minus (-). Zwraca wartość dodatnią z początkowym lub końcowym znakiem plus (+). Ograniczenie: element formatu S może pojawić się tylko na pierwszej lub ostatniej pozycji modelu formatu liczb.
TM	„Minimum tekstu”. Zwraca (w postaci dziesiętnej) najmniejszą możliwą liczbę znaków. W tym elemencie wielkość liter nie jest rozróżniana.
U	Zwraca w określonej pozycji symbol podwójnej waluty „Euro” (lub inny) (bieżąca wartość parametru NLS_DUAL_CURRENCY).
V	Zwraca wartość pomnożoną przez 10n (i jeśli to konieczne, zaokrągla ją w górę), gdzie n to liczba 9 po „V”.
X	Zwraca wartość szesnastkową określonej liczby cyfr.

TO_NUMBER, funkcja

Funkcja TO_NUMBER konwertuje wartość znakową na numeryczny typ danych. Jeśli konwertowany ciąg zawiera znaki nieliczbowe, funkcja zwraca błąd.

Składnia

TO_NUMBER (string1, [format], [nls_parameter])

Poniższa tabela przedstawia listę modeli formatów, których można użyć do typowania wartości znaków jako liczby przy użyciu TO_NUMBER.

Formatuj model	Opis
CC	Stulecie
SCC	Wiek pne z prefiksem -
RRRR	Rok z 4 liczbami
SYYY	Rok pne poprzedzony ciągiem -
IYYY	Rok ISO z 4 liczbami
YY	Rok z 2 liczbami
RR	Rok z 2 numerami z kompatybilnością z Y2k
ROK	Rok w postaciach
SYEAR	Rok w znakach, BC z prefiksem -
pne	Wskaźnik BC / AD
Q	Kwartał w liczbach (1,2,3,4)
MM	Miesiąc roku 01, 02 ... 12
MIESIĄC	Miesiąc w znakach (np. Styczeń)
PON	JAN, LUT
W W	Numer tygodnia (tj. 1)
W.	Numer tygodnia miesiąca (tj. 5)
IW	Numer tygodnia w roku w standardzie ISO.
DDD	Dzień roku w liczbach (tj. 365)
DD	Dzień miesiąca w liczbach (tj. 28)
re	Dzień tygodnia w liczbach (np. 7)
DZIEŃ	Dzień tygodnia w znakach (np. Poniedziałek)
FMDAY	Dzień tygodnia w znakach (np. Poniedziałek)
DY	Dzień tygodnia w krótkim opisie postaci (np. SŁOŃCE)
jot	Dzień juliański (liczba dni od 1 stycznia 4713 pne, gdzie 1 stycznia 4713 pne to 1 w Oracle)
HH, H12	Numer godziny dnia (1-12)
HH24	Numer godziny dnia z zapisem 24-godzinnym (0-23)
AM, PM	Rano lub wieczorem
TĘSKNIĆ	Liczba minut i sekund (tj. 59),
SSSSS	Liczba sekund tego dnia.
DS	Format daty krótkiej. Zależy od ustawień NLS. Używaj tylko z sygnaturą czasową.
DL	Format daty długiej. Zależy od ustawień NLS. Używaj tylko z sygnaturą czasową.
mi	Skrócona nazwa ery. Dotyczy tylko kalendarzy: Imperial Imperial, ROC Official, Thai Buddha.
EE	Pełna nazwa ery
FF	Ułamki sekund. Używaj z sygnaturą czasową.
FF1..FF9	Ułamki sekund. Używaj z sygnaturą czasową. Cyfra określa liczbę cyfr dziesiętnych używanych do ułamków sekund.
FM	Tryb wypełnienia: eliminuje puste miejsca w wyjściu z konwersji
FX	Format Exact: wymaga dokładnego dopasowania wzorca między danymi a modelem formatu.
IYY LUB IY LUB I	Ostatnie 3,2,1 cyfry standardowego roku ISO. Tylko wyjście
RM	Rzymska reprezentacja miesiąca (I .. XII)
RR	Ostatnie 2 cyfry roku.
RRRR	Ostatnie 2 cyfry roku używanego do celów wyjściowych. Akceptuje lata fout-cyfrowe, gdy są używane jako dane wejściowe.
SP	Format ortograficzny. Może pojawiać się na końcu elementu liczbowego. Wynik jest zawsze w języku angielskim. Na przykład miesiąc 10 w formacie MMSP zwraca „dziesięć”
SPTH	Format ortograficzny i porządkowy; 1 wyników w pierwszej.
TH	Konwertuje liczbę na jej format porządkowy. Na przykład 1 jest pierwszym.
TS	Krótki format czasu. Zależy od ustawień NLS. Używaj tylko z sygnaturą czasową.
TZD	Skrócona nazwa strefy czasowej. tj. PST.
TZH, TZM	Przesunięcie godziny / minuty strefy czasowej.
TZR	Region strefy czasowej
X	Lokalny znak radix. W Ameryce jest to okres (.)

Poniższe zapytania SELECT akceptują liczby jako dane wejściowe i drukuje je po specyfikatorze formatu.

SELECT  TO_NUMBER('121.23', '9G999D99') 
FROM DUAL

TO_NUMBER('121.23','9G999D99')
------------------------------
                        121.23

SELECT  TO_NUMBER('1210.73', '9999.99') 
FROM DUAL;

TO_NUMBER('1210.73','9999.99')
------------------------------
                       1210.73

TO_DATE

Funkcja przyjmuje wartości znakowe jako dane wejściowe i zwraca sformatowaną datę odpowiadającą temu samemu. Funkcja TO_DATE umożliwia użytkownikom wprowadzenie daty w dowolnym formacie, a następnie konwertuje wpis do domyślnego formatu używanego przez Oracle 11g.

Składnia:

TO_DATE( string1, [ format_mask ], [ nls_language ] )

Argument format_mask składa się z szeregu elementów reprezentujących dokładnie to, jak powinny wyglądać dane, i musi być wprowadzony w pojedynczych cudzysłowach.

Formatuj model	Opis
ROK	Rok, przeliterowany
RRRR	4-cyfrowy rok
YYY, YY, Y	Ostatnie 3, 2 lub 1 cyfra roku.
IYY, IY, I	Ostatnie 3, 2 lub 1 cyfra roku w standardzie ISO.
IYYY	4-cyfrowy rok na podstawie normy ISO
RRRR	Akceptuje dwucyfrowy rok i zwraca czterocyfrowy rok.
Q	Kwartał roku (1, 2, 3, 4; JAN-MAR = 1).
MM	Miesiąc (01-12; JAN = 01).
PON	Skrócona nazwa miesiąca.
MIESIĄC	Nazwa miesiąca dopełniona spacjami do długości 9 znaków.
RM	Miesiąc oznaczony cyfrą rzymską (I-XII; JAN = I).
W W	Tydzień roku (1-53), w którym tydzień 1 rozpoczyna się pierwszego dnia roku i trwa do siódmego dnia roku.
W.	Tydzień miesiąca (1-5), w którym tydzień 1 rozpoczyna się pierwszego dnia miesiąca, a kończy siódmego.
IW	Tydzień roku (1-52 lub 1-53) na podstawie normy ISO.
re	Dzień tygodnia (1-7).
DZIEŃ	Nazwa dnia.
DD	Dzień miesiąca (1-31).
DDD	Dzień roku (1-366).
DY	Skrócona nazwa dnia.
jot	Dzień juliański; liczba dni od 1 stycznia 4712 rpne.
HH12	Pora dnia (1-12).
HH24	Pora dnia (0-23).
TĘSKNIĆ	Minuta (0-59).
SSSSS	Sekundy po północy (0-86399).
FF	Ułamki sekund. Użyj wartości od 1 do 9 po FF, aby wskazać liczbę cyfr w ułamkach sekund. Na przykład „FF4”.
AM, PM	Wskaźnik południka
AD, BC	Wskaźnik AD, BC
TZD	Informacje na temat czasu letniego. Na przykład „PST”
TZH, TZM, TZR	Strefa czasowa godzina / minuta / region.

Poniższy przykład konwertuje ciąg znaków na datę:

SELECT TO_DATE('January 15, 1989, 11:00 A.M.',  'Month dd, YYYY, HH:MI A.M.',  'NLS_DATE_LANGUAGE = American')
FROM DUAL;

TO_DATE('
---------
15-JAN-89

Funkcje ogólne

Funkcje ogólne służą do obsługi wartości NULL w bazie danych. Celem ogólnych funkcji obsługi wartości NULL jest zastąpienie wartości NULL wartością alternatywną. Poniżej pokrótce omówimy te funkcje.

NVL

Funkcja NVL zastępuje wartość alternatywną wartością NULL.

Składnia:

NVL( Arg1, replace_with )

W składni oba parametry są obowiązkowe. Należy pamiętać, że funkcja NVL działa ze wszystkimi typami danych. A także, że typ danych oryginalnego ciągu i zastąpienia musi być w stanie zgodnym, tj. Taki sam lub niejawnie konwertowany przez Oracle.

Jeśli arg1 jest wartością znakową, to oracle konwertuje łańcuch zastępczy na typ danych zgodny z arg1 przed ich porównaniem i zwraca VARCHAR2 w zestawie znaków wyrażenia1. Jeśli argument arg1 jest liczbowy, Oracle określa argument o najwyższym priorytecie liczbowym, niejawnie konwertuje drugi argument na ten typ danych i zwraca ten typ danych.

Poniższa instrukcja SELECT wyświetli „n / a”, jeśli pracownik nie został jeszcze przypisany do żadnego stanowiska, tj. JOB_ID ma wartość NULL. W przeciwnym razie wyświetli rzeczywistą wartość JOB_ID.

SELECT  first_name, NVL(JOB_ID, 'n/a')
FROM employees;

NVL2

Jako ulepszenie w stosunku do NVL, Oracle wprowadziło funkcję zastępowania wartości nie tylko dla wartości kolumn NULL, ale także dla kolumn NOT NULL. Funkcji NVL2 można użyć do zastąpienia wartości alternatywnej dla wartości NULL lub innej niż NULL.

Składnia:

NVL2( string1, value_if_NOT_null, value_if_null )

Poniższa instrukcja SELECT wyświetli „Bench”, jeśli JOB_CODE dla pracownika ma wartość NULL. Dla określonej niezerowej wartości JOB CODE wskazywałoby stałą wartość „Job Assigned”.

SQL> SELECT NVL2(JOB_CODE, 'Job Assigned', 'Bench')
FROM employees;

NULLIF

Funkcja NULLIF porównuje dwa argumenty wyr1 i wyr2. Jeśli wyr1 i wyr2 są równe, zwraca NULL; w przeciwnym razie zwraca wyr1. W przeciwieństwie do innych funkcji obsługujących wartości null, pierwszy argument nie może mieć wartości NULL.

Składnia:

NULLIF (expr1, expr2)

Zauważ, że pierwszy argument może być wyrażeniem, którego wynikiem jest NULL, ale nie może to być literał NULL. Oba parametry są obowiązkowe, aby funkcja mogła zostać wykonana.

Poniższe zapytanie zwraca NULL, ponieważ obie wartości wejściowe, 12, są równe.

SELECT	NULLIF (12, 12)
FROM DUAL;

Podobnie, poniższe zapytanie zwraca „SUN”, ponieważ oba łańcuchy nie są równe.

SELECT NULLIF ('SUN', 'MOON')
FROM DUAL;

ŁĄCZYĆ

Funkcja COALESCE, bardziej ogólna forma NVL, zwraca pierwsze niezerowe wyrażenie na liście argumentów. Wymaga co najmniej dwóch obowiązkowych parametrów, ale maksymalna liczba argumentów nie ma ograniczeń.

Składnia:

COALESCE (expr1, expr2, ... expr_n )

Rozważ poniższe zapytanie SELECT. Wybiera pierwszą niezerową wartość wprowadzoną do pól adresowych dla pracownika.

SELECT COALESCE (address1, address2, address3) Address
FROM  employees;

Co ciekawe, działanie funkcji COALESCE jest podobne do konstrukcji IF..ELSIF..ENDIF. Powyższe zapytanie można ponownie zapisać jako -

IF address1 is not null THEN
   result := address1;
ELSIF address2 is not null THEN
   result := address2;
ELSIF address3 is not null THEN
   result := address3;
ELSE
   result := null;
END IF;

Funkcje warunkowe

Oracle udostępnia funkcje warunkowe DECODE i CASE do narzucania warunków nawet w instrukcji SQL.

Funkcja DECODE

Funkcja jest odpowiednikiem SQL warunkowej instrukcji proceduralnej IF..THEN..ELSE. DECODE działa z wartościami / kolumnami / wyrażeniami wszystkich typów danych.

Składnia:

DECODE (expression, search, result [, search, result]... [, default])

Funkcja DECODE porównuje po kolei wyrażenie z każdą wyszukiwaną wartością. Jeśli istnieje równość między wyrażeniem a argumentem wyszukiwania, zwraca odpowiedni wynik. W przypadku braku dopasowania zwracana jest wartość domyślna, jeśli została zdefiniowana, w przeciwnym razie NULL. W przypadku niezgodności zgodności dowolnego typu oracle wewnętrznie wykonuje możliwą niejawną konwersję w celu zwrócenia wyników.

W rzeczywistości Oracle uważa, że ​​dwie wartości null są równoważne podczas pracy z funkcją DECODE.

SELECT DECODE(NULL,NULL,'EQUAL','NOT EQUAL') 
FROM DUAL;

DECOD
-----
EQUAL

Jeśli wyrażenie ma wartość null, Oracle zwraca wynik pierwszego wyszukiwania, który również jest pusty. Maksymalna liczba komponentów w funkcji DECODE to 255.

SELECT	first_name, salary, DECODE (hire_date, sysdate,'NEW JOINEE','EMPLOYEE')
	FROM employees;

Wyrażenie CASE

Wyrażenia CASE działają na tej samej koncepcji co DECODE, ale różnią się składnią i zastosowaniem.

Składnia:

CASE  [ expression ]
   WHEN condition_1 THEN result_1
   WHEN condition_2 THEN result_2
   ...
   WHEN condition_n THEN result_n
   ELSE result
END

Wyszukiwanie Oracle rozpoczyna się od lewej i przesuwa w prawo, aż znajdzie prawdziwy warunek, a następnie zwraca skojarzone z nim wyrażenie wynikowe. Jeśli żaden warunek nie zostanie spełniony, a istnieje klauzula ELSE, Oracle zwraca wynik zdefiniowany za pomocą else. W przeciwnym razie Oracle zwraca wartość null.

Maksymalna liczba argumentów w wyrażeniu CASE wynosi 255. Wszystkie wyrażenia wliczają się do tego limitu, w tym początkowe wyrażenie prostego wyrażenia CASE i opcjonalne wyrażenie ELSE. Każda para KIEDY ... WTEDY liczy się jako dwa argumenty. Aby uniknąć przekroczenia tego limitu, możesz zagnieździć wyrażenia CASE, tak aby samo wyrażenie return_expr było wyrażeniem CASE.

SELECT first_name, CASE	WHEN salary < 200 THEN 'GRADE 1'
			WHEN salary > 200 AND salary < 5000 THEN 'GRADE 2'
			ELSE 'GRADE 3'
		   END CASE
FROM employees;	

ENAM  	CASE
----    -------
JOHN    GRADE 2
EDWIN   GRADE 3
KING    GRADE 1