wyrazenia, WAT, semestr III, Bazy danych

Michał Kostecki

Olsztyn 19.03.2005 r.

Wyrażenia i funkcje języka SQL

WYRAŻENIA języka SQL stanowią kombinację kolumn, literałów, operatorów, i funkcji. Wyrażenia te mogą wchodzić w skład listy polecenia SELECT, można posługiwać się nimi jako wartościami w klauzulach INSERT lub UPDATE, czy wreszcie mogą stanowić warunek w klauzuli WHERE.

KOLUMNY i obiekty - maksymalna długość nazwy dla obiektu, schematu lub kolumny bazy danych Oracle, wynosi 30 znaków. Łącze do bazy danych może składać się z nie więcej niż 128 znaków. W nazwach obiektów nie wolno używać cudzysłowu.

LITERAŁY mogą składać się z liczb lub łańcuchów, ujętych w pojedynczy cudzysłów. Zapis liczby nie powinien zawierać znaków formatujących (takich jak przecinki rozdzielające części tysięczne, czy symbole walut); może natomiast uwzględniać pozycje dziesiętne i może być przedstawiony w notacji naukowej (np.: 1e6, 100000, 100000.01).

OPERATORY wykonują działania na wyrażeniach lub porównują je, dając w efekcie nową wartość. Operatory arytmetyczne przeprowadzają odpowiednie operacje matematyczne i zwracają ich wynik. W operatorach logicznych i działaniach na łańcuchach każda wartość różna od zera i NULL odpowiada prawdzie, a więc NULL i zero reprezentują wartość „fałsz”. Złożone wyrażenia logiczne, zbudowane za pomocą operatorów logicznych, zwracają jedynkę jako reprezentację wartości prawdziwej, a zero jako reprezentację fałszu.

Porównywać można ze sobą liczby. jak również wyrażenia napisowe. Pamiętać należy jednak, że wynik porównania wyrażeń napisowych zależeć będzie od tego, czy w ich budowie występują wartości zadeklarowane jako BINARY, co znosi utożsamienie małych i wielkich liter. Poza tym, w wypadku porównań wartości, które nie są obie napisowe ani całkowitoliczbowe, Oracle dokonuje się konwersji typów, co na ogół prowadzi do porównania wyrażeń jako wartości zmiennoprzecinkowych. Jeżeli wartością któregokolwiek z argumentów porównania jest NULL, porównanie zwraca jako wynik NULL (oczywiście za wyjątkiem funkcji IS NULL).

Poniżej przedstawiona jest lista najważniejszych operatorów:

- arytmetyczne:

-	wyrazenie1 - wyrażenie2 Odejmuje wyrażenie2 od wyrazenie1
+	wyrazenie1 + wyrażenie2 Zwraca suę wyrażenia1 i wyrażenia2
*	wyrazenie1 * wyrażenie2 Zwraca iloczyn wyrażenia1 i wyrażenia2
/	wyrazenie1 / wyrażenie2 Dzieli wyrażenie1 przez wyrażenie2

- porównania:

<	wyrazenie1 < wyrażenie2 Zwraca wartość true, jeżeli wyrażenie1 jest mniejsze niż wyrazenie2
<=	wyrazenie1 <= wyrażenie2 Zwraca wartość true, jeżeli wyrażenie1 jest mniejsze lub równe wyrazeniu2
=	wyrazenie1 = wyrażenie2 Zwraca wartość true, jeżeli wyrażenie1 i wyrazenie2 są identyczne. Zwraca wartość null jeżeli jedno z wyrażeń jest wartością null. W przeciwnym wypadku zwraca wartość false.
>	wyrazenie1 > wyrażenie2 Zwraca wartość true, jeżeli wyrażenie1 jest większe niż wyrazenie2
=>	wyrazenie1 => wyrażenie2 Zwraca wartość true, jeżeli wyrażenie1 jest większe lub równe wyrazeniu2
!= lub <>	wyrazenie1 != wyrażenie2 Zwraca wartość false, jeżeli wyrażenie1 i wyrazenie2 są identyczne. Zwraca wartość null jeżeli jedno z wyrażeń jest wartością null. W przeciwnym wypadku zwraca wartość true.
BETWEEN	wartosc1 IS BETWEEN wartosc2 AND wartosc3 Zwraca wartość true, jeśli wartosc1 znajduje się pomiędzy wartosc2 i wartosc3
IS NULL	wyrazenie1 IS NULL Zwraca wartość true, jeżeli wyrazenie1 jest wartością pustą

- dotyczące łańcuchów:

\|\|	string1 \|\| string2 Łączy ciągi znaków string1 i string2
ALL	wyrazenie1 operator ALL (lista) Zwraca wartość true, jeżeli wyrażenie1 jest prawdziwe dla wszystkich wartości z listy zadanej argumentem. Operator powinien wskazywać działanie, które może w wyniku zwrócić wartość true dla wielu argumentów, np: „>”, „!=”, itp.
IN	wyrażenie1 IN (wartość1, ...) Zwraca jedynkę, jeżeli wyrażenie1 jest równe którejkolwiek wartości z listy podanej w nawiasach, w przeciwnym wypadku zwraca zero. Wartość wyrażenia1 może być numeryczna, a jej typ narzuca sposób porównania.
ANY lub SOME	wyrazenie1 = ANY (lista) Operator równoważny z „IN”, ale musi być poprzedzony symbolem „=”
EXISTS	EXISTS (subquery) Zwraca true, jeżeli w wyniku wykonania podzapytania subquery zostanie zwrócony, chociaż jeden rekord.
LIKE	wyrażenie1 LIKE wzór Porównanie wzorców, w których budowie można korzystać z metaznaków „%” (oznaczającego dowolną liczbę, w tym zero, dowolnych znaków), oraz „_” (oznaczającego dowolny pojedynczy znak). Aby we wzorcu umieścić któryś z metaznaków jako znak dosłowny, należy go poprzedzić znakiem „\”

- operatory logiczne:

NOT

NOT wyrazenie1

Negacja logiczna. Jeżeli wyrażenie1 jest prawdziwe, to NOT zwraca wartość false i na odwrót. Operatora NOT można używać z innymi operatorami - np. EXISTS, LIKE, IN

wyrazenie1 OR wyrażenie2

Zwraca wartość true, jeżeli chociaż jedno z wyrażeń jest prawdziwe

AND

wyrazenie1 AND wyrażenie2

Zwraca wartość true, jeżeli obydwa wyrażenia są prawdziwe. Zwraca wartość null, jeżeli obydwa wyrażenia mają wartość null.

Przykład:

SELECT imie, zarobki FROM urzędnicy WHERE (imie = „Jacek” AND zarobki IS BETWEEN 1000 AND 2000) OR stanowisko = „Szef” OR nazwisko LIKE „_ck%”

Można również definiować własne operatory. Wykorzystuje się do tego funkcje PL/SQL. Operatorami niestandardowymi można posługiwać się w taki sam sposób jak operatorami predefiniowanymi.

FUNKCJE. Istnieje zbiór predefiniowanych funkcji języka SQL. Dzielimy je na funkcje:

- matematyczne:

ABS	ABS (liczba) Wylicza wartość bezwzględną podanej liczby. Przykład: SELECT ABS(-100) FROM dual;
AVG	AVG (wyrażenie) oblicza średnią wartość wyrażenia dla uwzględnionych wierszy; Przykład: SELECT AVG(DISTINCT initial_extent) FROM user_tables;
COUNT	COUNT (wyrażenie) zlicza wiersze dla których wyrażenie przyjmuje wartość różną od NULL; Przykład: SELECT COUNT(*) FROM all_objects;
MAX i MIN	MIN (wyrażenie) MAX (wyrażenie) podają odpowiednio minimalną i maksymalną wartość wyrażenia dla uwzględnionych wierszy; Przykład: SELECT MAX(initial_extent) FROM all_tables;
ROUND	ROUND (wartość, dokładność) Funkcja zaokrągla podaną wartość do wskazanej przez argument dokładność ilości miejsc po przecinku. Jeżeli pominie się wielkość pola dokładność wtedy zostanie przyjęta wartość domyślna „0”. Oznacza to zaokrąglenie do najbliższej liczby całkowitej. Funkcja ROUND może zostać również użyta do zaokrąglania dat. W tym przypadku, dokładność będzie maską określającą poziom zaokrąglenia. Przykład: SELECT ROUND(3.1415926, 4) FROM dual;
SUM	SUM (wyrażenie) sumuje wyrażenie po uwzględnionych wierszach; Przykład: SELECT SUM(initial_extent) FROM all_tables;

- pracujące na łańcuchach:

CONCAT	CONCAT (string1, string2, ...) zwraca złączenie napisów podanych jako argumenty Przykład: SELECT CONCAT('Dan ', 'Morgan') FROM dual;
REPLACE	REPLACE (string1, string2, string2) zwraca napis utworzony z napisu string1 poprzez zastąpienie wszystkich wystąpień pod-napisu string2 napisem string3.
LOCATE	LOCATE (string1, string2) jeżeli napis string1 stanowi część napisu string2, zwraca pozycję początku pierwszego wystąpienia A w B. W przeciwnym wypadku zwraca zero. Synonimem jest POSITION (string2 IN string1).
LENGTH	LENGHT (string) zwraca ilość znaków w łańcuchu string. Przykład: SELECT LENGTH('Dan Morgan') FROM dual;
UPPER	UPPER (string) Funkcja UPPER zmienia wszystkie litery łańcucha string na wielkie. Przykład: SELECT UPPER('Dan Morgan') FROM dual;
LOWER	LOWER (string) Funkcja LOWER zmienia wszystkie litery łańcucha string na małe. Przykład: SELECT LOWER('Dan Morgan') FROM dual;
INITCAP	INITCAP (string) Funkcja INITCAP zmienia wszystkie pierwsze litery każdego wyrazu na wielkie. Przykład: SELECT INITCAP('dan morgan') FROM dual;
TO_NUMBER	TO_NUMBER (string1 [, format] [, nls_parametry]) Funkcja TO_NUMBER przekształca łańcuch znaków string1 na format liczbowy. Jeżeli łańcuch zawiera znaki formatujące, takie jak przecinek lub symbol waluty, to argument format powinien opisywać odpowiedni model formatowania liczby

- daty:

SYSDATE	Funkcja zwraca bieżącą datę i godzinę Przykład: SELECT SYSDATE FROM dual;
ADD_MONTHS	ADD_MONTHS (data, liczba) Funkcja dodaje wskazaną przez liczba ilość miesięcy do daty początkowej data. Przykład: SELECT add_months(SYSDATE, 2) FROM dual;
ROUND	ROUND (wartość, dokładność) Funkcja zaokrągla podaną wartość do wskazanej przez argument dokładność ilość miejsc po przecinku. Jeżeli pominie się wielkość pola dokładność wtedy zostanie przyjęta wartość domyślna „0”. Oznacza to zaokrąglenie do najbliższej liczby całkowitej. Funkcja ROUMD może zostać również użyta do zaokrąglania dat. W tym przypadku, dokładność będzie maską określającą poziom zaokrąglenia. Przykład: SELECT ROUND(TO_DATE ('27-OCT-00'),'YEAR') NEW_YEAR FROM dual;
EXTRACT	EXTRACT (type FROM datetime) Pobiera określoną wartość pola type z pola datetime. Przykład: SELECT EXTRACT(YEAR FROM DATE '2005-01-04') FROM dual;
MONTHS_BETWEEN	MONTHS_BETWEEN(latest_date, earliest_date) Zwraca ilość miesięcy oddzielających dwie daty. Przykład: SELECT MONTHS_BETWEEN(SYSDATE+365, SYSDATE-365) FROM dual;
TO_DATE	TO_DATE (string, [format] [,nls_parameter]) Funkcja TO_DATE pozwala na przekształcenie łańcucha string w datę. Dane zawarte w łańcuchu mogą odpowiadać domyślnemu formatowi daty, obowiązującemu dal bazy danych. W takim przypadku argument format nie jest potrzebny. W przeciwnym razie należy podać format łańcucha. Argument nls_parameters jest łańcuchem zawierającym parametry NLS (National Language Support), które mogą mieć wpływ na postać dat i liczb. Przykład: SELECT TO_DATE('01/01/2004', 'MM/DD/YYYY') FROM dual;
TO_CHAR	TO_CHAR (string, [format] [,nls_parameter]) Funkcja TO_CHAR przekształca wartość pola string, w jej reprezentację znakową. Jeżeli nie zostanie określony format to funkcja przyjmie wartości domyślne. Argument nls_parameters jest łańcuchem zawierającym parametry NLS (National Language Support), które mogą mieć wpływ na postać dat i liczb. Przykład: SELECT TO_CHAR(datecol1, 'DAY-MONTH-YY') COL1, TO_CHAR(datecol2, 'D-MM/YYYY HH24:MI:SS') COL2, TO_CHAR(datecol2, 'YYYY-MON-DD HH24') COL3 FROM t;

- warunkowe:

DECODE

SELECT DECODE (wyrażenie, wartosc1, wynik1, wartosc2, wynik2, ... , wartość_else)

Funkcja DECODE umożliwia zmianę wyrażenia na inną wartość. Pod pewnymi względami przypomina ona funkcję IF. Wartość wyrażenia jest porównywana z każdą wartością. Jeżeli znajdzie się wartość równa wyrażeniu, to zwracany jest odpowiadający jej wynik. W przeciwnym wypadku zwracany jest wynik_else.

Przykład:

SELECT program_id,

DECODE(customer_id,

'AAL', 'American Airlines',

'ILC', 'Intl. Leasing Corp.',

'NWO', 'Northwest Orient',

'SAL', 'Southwest Airlines',

'SWA', 'Sweptwing Airlines',

'USAF', 'United States Airforce',

'Not Known') AIRLINE,

delivered_date

FROM airplanes

WHERE ROWNUM < 11;

CASE

CASE WHEN (wyrazenie1) THEN wynik1 WHEN (wyrazenie2) THEN wynik2 … ELSE wynik_else END

Funkcja podobna w działaniu do DECODE. Jeżeli wartość w wyrażeniu jest prawdziwa, wtedy wykonywany jest wynik po odpowiednim THEN. W przypadku gdy każde z wyrażeń zwróci wartość false, zostanie wykonany wynik_else.

Przykład:

SELECT line_number,

CASE WHEN (line_number < 10) THEN 'Ones'

WHEN (line_number < 100) THEN 'Tens'

WHEN (line_number < 1000) THEN 'Hundreds'

ELSE 'Thousands'

END RESULT_SET

FROM airplanes;

Do dat możemy również dodawać i odejmować liczby - zwiększając lub zmniejszając datę o podaną liczbę dni.

Na przykład:

SELECT pracuje_od+7 FROM pracownik;

Przykład tego samego polecenia zapisanego za pomocą DECODE i CASE:

SELECT parameter, DECODE(SIGN(parameter-1000),-1,'C','P') AS BAND

FROM parameter_table;

SELECT parameter,

CASE WHEN parameter < 1000 THEN 'C' ELSE 'P' END AS BAND

FROM parameter_table;

MASKI - Służą formatowaniu łańcuchów w funkcjach. Funkcje TO_DATE, TO_CHAR i TO_NUMBER wykorzystują maski formatów, które określają postać danych wejściowych lub wyjściowych.

Podane poniżej maski formatu wykorzystuje się w funkcjach TO_CHAR i TO_DATE. Maski można łączyć na różne sposoby.

D	Dzień tygodnia w postaci liczbowej (1-7)
DAY	Nazwa dnia tygodnia
DD	Dzień miesiąca (1-31).
DDD	Dzień roku (1-366).
DY	Skrócona nazwa dnia tygodnia, np. „MON" (poniedziałek).
FM	Zapobiega uzupełnianiu wolnych miejsc zerami i spacjami
EX	Zapewnia ścisłą zgodność pomiędzy zadanym formatem i przedstawieniem daty. W typowym zapisie niektóre różnice, takie jak poprzedzające zera i spacje, są ignorowane. Jeżeli natomiast w masce znajdzie się modyfikator EX, to zapis i wzorzec muszą dokładnie pasować do siebie.
HH	Godzina podana w formacie 12-godzinnym.
HH24	Godzina podana w formacie 24-godzinnym.
I	Jako prefix przed Y, YY lub YYY, informuje, że zapis roku powinien być zgodny ze standardem ISO.
IW	Tydzień roku (standard ISO).
LITERALS	Pewne znaki mogą być użyte w zapisie maski formatu i powtórzone bez zmian w prezentowanej dacie. Następujące znaki podaje się bez cudzysłowu: - / , . ; :. Ponadto każdy łańcuch innych znaków ujęty w podwójny cudzysłów, pojawi się wśród danych wyjściowych w niezmienionej formie.
MI	Podaje ilość minut (bez godziny).
MM	Numer miesiąca, w razie potrzeby uzupełniony zerem .Na przykład czerwiec = „06".
MON	Skrócona nazwa miesiąca. Na przykład DEC, SEP {grudzień, październik).
MONTH	Pełna nazwa miesiąca.
RR	Jako format danych wejściowych dla funkcji TO_DATE, RR zwraca datę w następnym stuleciu jeśli rok jest <50, lub rok bieżący dla roku >50. W przeciwnym razie RR zwraca rok w obecnym stuleciu.
RRRR	Akceptuje zarówno dwucyfrowy, jak i czterocyfrowy format roku. W przypadku zapisu dwucyfrowego konwersja roku przebiega zgodnie z mechanizmem RR
SS	Liczba sekund w przedziale minuty (1-59).
SSSS	Liczba sekund w przedziale doby, tzn. ilość sekund, jaka minęła od północy.
W	Numer tygodnia w miesiącu
WW	Numer tygodnia w roku.
YEAR	Rok zapisany słownie, np. „Tysiąc dziewięćset dziewięćdziesiąty ósmy".
YY	Rok w zapisie dwucyfrowym
YYYY	Rok w zapisie czterocyfrowym

Przykład (zwróci That date is 05/29/2004):

SELECT DISTINCT TO_CHAR(59684,'That date is MM/DD/YYYY')

FROM Sample.Person

Maski formatu liczb mają one zastosowanie w funkcjach TO_NUMBER i TO_CHAR. Maski służą tutaj do przekształcania różnych zapisów liczb na wartości typu liczbowego Oracle i odwrotnie.

$	Poprzedza liczby znakiem dolara
,	Drukuje przecinek na podanej pozycji
.	Drukuje kropkę dziesiętną na podanej pozycji.
0	Oznacza symbol zastępujący cyfrę. Jeżeli pozycja nie zostanie wykorzystana w zapisie (np. liczba ma mniejszą ilość cyfr, niż dopuszczalne maksimum), to w to miejsce zostanie wstawione zero.
9	Oznacza symbol zastępujący cyfrę. Jeżeli pozycja nie zostanie wykorzystana w zapisie (np. liczba ma mniejszą ilość cyfr, niż dopuszczalne maksimum), to w to miejsce zostanie wstawiony znak odstępu.
9.99EEEE	Zwraca liczbę w notacji naukowej. Ilość cyfr po kropce dziesiętnej określa precyzję liczby.
B	W przypadku liczb mniejszych, niż jeden, zamienia początkowe zero na znak odstępu. Znak B jest „silniejszy", niż wszystkie inne zapisy w maskach, dotyczące tej pozycji.
C	Zwraca symbol waluty według norm ISO, zgodnie z wartością zadaną parametrem NLS_ISO_CURRENCY.
D	Określa separator dziesiętny, zgodnie z wartością zadaną parametrem NLS_NUMERIC_CHABACTER.
FM	Powoduje likwidację spacji wiodących lub uzupełniających, niezależnie od użytej innej maski.
G	Powoduje wpisanie znaku - separatora grupującego tysiące — w odpowiednich pozycjach. Znak ten jest określony przez wartość parametru NLS_NUMERIC_CHARACTER Na przykład dla znaku „," odpowiednie zapisy wyglądałyby następująco: „1,000", „1,000,000".
L	Zwraca symbol lokalnej waluty, zdefiniowany przy pomocy parametru NLS_CURRENCY.
MI	Dla liczb ujemnych pojawia się znak „-" przed liczbą.
PR	Liczby ujemne ujęte są w nawiasy „<>".
RM	Zwraca liczbę w notacji rzymskiej.
S	Drukuje znak plus lub minus, zależnie od wartości liczby.
TM	Przedstawia liczbę w takiej postaci, aby zachować maksymalną precyzję przy minimalnej ilości znaków. Za maską TM może wystąpić znak „e" lub „9", co wskazuje na zapis naukowy lub postać ułamka dziesiętnego. Jeżeli nie podano żadnego z tych argumentów, maska spowoduje wydruk liczby jako ułamka dziesiętnego, o ile zapis nie będzie dłuższy niż 64 znaki. W przeciwnym przypadku program zastosuje notację naukową.
U	Zwraca symbol waluty zdefiniowany przy pomocy parametru NLS_UNION_CURRENCY.
V	Zwraca liczbę pomnożoną przez 10^n, gdzie n jest to ilość dziesiątek zapisana po V.
X	Zwraca cześć całkowitą liczby wejściowej, przekształconej w zapis szesnastkowy.

Przykład (1 - zwraca 1000, 2 - zamienia stringa na wartość liczbową):

SELECT DISTINCT TO_NUMBER('+-+-01000.00+') AS Num FROM Sample.Person

UPDATE employees SET salary = salary + TO_NUMBER('1500.00') WHERE lname = 'WILLIAMS'