Korzystanie z wyrażeń warunkowych

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. Zauważ, ż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