Ściągawka do ćwiczeń. Podstawą przykładów jest Oracle Database 19c.
Polecenia klienta SQL*Plus, różnice względem PostgreSQL oraz osobna sekcja o IBM Db2 LUW
są oznaczone w tekście. Przykłady odwołujące się do emp i dept
zakładają klasyczne tabele pracowników i działów ze schematu ćwiczeniowego SCOTT.
Przykładowe zapytania SQL — skrypty do pracy na zestawach „Las” i „Ewidencja ludności”.
W poniższym przykładzie są trzy wiersze, ale tylko dwa mają podaną wartość. Dlatego średnia wynosi 15, a nie 10:
WITH dane (x) AS (
SELECT 10 FROM dual UNION ALL
SELECT NULL FROM dual UNION ALL
SELECT 20 FROM dual
)
SELECT COUNT(*) AS wiersze, COUNT(x) AS wartosci,
SUM(x) AS suma, AVG(x) AS srednia
FROM dane;
-- wiersze = 3, wartosci = 2, suma = 30, srednia = 15
| Funkcja | Co liczy? |
|---|---|
COUNT(*) | Wszystkie wiersze grupy. |
COUNT(x), COUNT(DISTINCT x) | Odpowiednio: liczbę wartości innych niż NULL (z powtórzeniami) albo liczbę różnych takich wartości. |
MIN(x), MAX(x) | Najmniejszą i największą z podanych wartości. |
SUM(x), AVG(x) | Sumę i średnią podanych wartości liczbowych. Brak wartości nie jest zerem. |
VAR_POP(x), STDDEV_POP(x) | Wariancję i odchylenie standardowe populacji. |
VAR_SAMP(x), STDDEV_SAMP(x) | Wariancję i odchylenie standardowe próby; dla jednej obserwacji wynikiem jest NULL. Funkcje Oracle VARIANCE i STDDEV dają wtedy 0. |
Przy pustym wejściu agregacja bez GROUP BY i bez HAVING daje jeden wiersz:
COUNT(*) = 0, COUNT(x) = 0, lecz SUM(x) i AVG(x) dają NULL.
Zwykłe GROUP BY x nie utworzy wtedy żadnej grupy.
Zob. funkcje agregujące w Oracle.
| Oracle | Przykład lub znaczenie |
|---|---|
LOWER, UPPER, INITCAP | LOWER('SQL Kurs') daje 'sql kurs', UPPER('SQL Kurs') daje 'SQL KURS', a INITCAP('SQL Kurs') — 'Sql Kurs'. |
a || b, CONCAT(a,b) | Łączą napisy: 'A' || 'la' daje 'Ala'. |
LENGTH(s) | Liczba znaków, np. LENGTH('String') daje liczbę 6. LENGTHB liczy bajty. |
INSTR(s, szukany) | Pozycja pierwszego wystąpienia całego podnapisu, liczona od 1; jeśli go nie ma, wynikiem jest 0. INSTR('ananas','nan') daje 2. |
LPAD(s,n,w), RPAD(s,n,w) | Dopełniają z lewej lub prawej do szerokości n wzorcem w; jeśli s jest za długi, skracają go. LPAD('12',5,'0') daje '00012'. |
s LIKE wzorzec | % zastępuje dowolny ciąg znaków (także pusty), _ jeden znak. Aby szukać dosłownego podkreślenia: kod LIKE 'A!_%' ESCAPE '!' oznacza „zaczyna się od A_”. |
SUBSTR(s, pozycja [, długość]): pozycje liczymy od 1; Oracle traktuje 0 jak 1.
Ujemna pozycja wskazuje miejsce liczone od końca, ale wycinek nadal biegnie w prawo.
Pominięta długość oznacza „do końca napisu”; długość mniejsza od 1 daje NULL.
SELECT SUBSTR('ANANAS', -3, 2) AS wycinek, -- 'NA'
SUBSTR('ANANAS', -3) AS koncowka, -- 'NAS'
SUBSTR('ANANAS', 3,-2) AS brak -- NULL
FROM dual;
W Oracle pusty napis '' jest traktowany jak NULL; w PostgreSQL są to dwie różne wartości.
Także reguł ujemnej pozycji w SUBSTR nie należy przenosić z Oracle do PostgreSQL.
Dokumentacja: Oracle: SUBSTR,
PostgreSQL: funkcje napisowe.
Instrukcja COMMENT ON zapisuje opis w słowniku danych.
Komentarz do tabeli lub kolumny pozostaje tam po zakończeniu sesji.
Poniższy przykład zakłada, że w swoim schemacie mają Państwo tabelę emp z kolumną empno.
COMMENT ON TABLE emp IS 'Pracownicy firmy'; COMMENT ON COLUMN emp.empno IS 'Identyfikator pracownika'; SELECT column_name, comments FROM user_col_comments WHERE table_name = 'EMP' AND column_name = 'EMPNO';
W zapytaniu do słownika nazwy są wielkimi literami, bo tak Oracle zapisuje identyfikatory utworzone bez cudzysłowów.
Komentarz w kodzie (-- lub /* ... */) służy innemu celowi: objaśnia skrypt, a nie obiekt bazy.
COMMENT ON w Oracle jest DDL; proszę nie wykonywać go w środku transakcji, którą zamierzają Państwo wycofać.
START WITH wybiera korzenie; CONNECT BY określa,
które wiersze można dołączyć jako dzieci. PRIOR odwołuje się do wartości w wierszu rodzica,
a LEVEL wynosi 1 dla korzenia, 2 dla jego dziecka itd.
Jeśli zapytanie zawiera złączenie, Oracle uwzględnia je przed budowaniem hierarchii.
Pozostałe warunki WHERE filtrują poszczególne wiersze gotowej hierarchii.
WITH ludzie (id, szef, imie) AS ( SELECT 1, CAST(NULL AS NUMBER), 'Ada' FROM dual UNION ALL SELECT 2, 1, 'Jan' FROM dual UNION ALL SELECT 3, 2, 'Ewa' FROM dual ) SELECT LEVEL AS poziom, imie, PRIOR imie AS imie_szefa FROM ludzie WHERE id <> 2 START WITH id = 1 CONNECT BY PRIOR id = szef;
Wynik zawiera Adę (poziom 1) i Ewę (poziom 3). Jana nie wyświetlono, ale to przez niego zapytanie dotarło do Ewy.
Jeśli zamiast WHERE id <> 2 dopiszemy AND id <> 2 na końcu warunku CONNECT BY,
Jan nie zostanie przyjęty jako dziecko Ady, więc zapytanie nie odwiedzi również Ewy. W wyniku zostanie sama Ada.
Warunek na dopuszczalne korzenie należy pisać w START WITH.
Do sortowania rodzeństwa służy ORDER SIBLINGS BY. Jeśli dane mogą zawierać cykl,
Oracle oferuje CONNECT BY NOCYCLE i CONNECT_BY_ISCYCLE.
Zob. zapytania hierarchiczne w Oracle
oraz przykład drzewa genealogicznego.
Wartość typu DATE nie ma formatu tekstowego. W Oracle zawiera datę i czas
z dokładnością do sekundy, bez strefy czasowej. Dla porównania: date
w PostgreSQL zawiera wyłącznie datę. Do zapisania stałej daty w obu systemach wystarcza
DATE '2026-11-14'; w Oracle taki literał wskazuje północ.
-- Napis → DATE: jawnie podajemy format wejścia.
SELECT TO_DATE('14.11.2026 17:05', 'DD.MM.YYYY HH24:MI') FROM dual;
-- DATE → napis: jawnie wybieramy postać wyniku.
SELECT TO_CHAR(DATE '2026-11-14', 'YYYY-MM-DD') FROM dual;
-- wynik: '2026-11-14' (kalendarz gregoriański)
Bez maski domyślny format daty zależy od sesji (NLS_DATE_FORMAT);
nie ma jednej gwarantowanej wartości. Język nazw miesięcy i dni zależy od
NLS_DATE_LANGUAGE. SYSDATE zwraca datę i czas zegara serwera,
a CURRENT_DATE — datę i czas w strefie sesji. Obie wartości są w Oracle typu DATE.
| Element maski | Znaczenie |
|---|---|
YYYY, MM, DD | Rok kalendarzowy, miesiąc, dzień miesiąca. |
Q, DDD | Kwartał i dzień roku. D oznacza numer dnia tygodnia; to, który dzień ma numer 1, zależy od NLS_TERRITORY. |
MONTH, MON, DAY, DY | Nazwy i skróty w języku sesji; np. fmMonth usuwa dopełnienie spacjami. Wielkość liter w masce steruje wielkością liter nazwy. |
IYYY, IW | Rok i tydzień ISO. Tworzą parę: np. 1 stycznia 2021 należy do tygodnia 53 roku ISO 2020. WW numeruje tygodnie od 1 stycznia, W od pierwszego dnia miesiąca. |
HH24, HH12, MI, SS | Godzina w skali 0–23 lub 1–12, minuta, sekunda. W skali 12-godzinnej można dołączyć AM lub PM. MM oznacza miesiąc, a nie minutę. |
SSSSS, J | Liczba sekund od północy; numer dnia juliańskiego. To dwa różne elementy. |
CC, SYYYY, BC, AD | Wiek; rok ze znakiem minus dla lat p.n.e.; oznaczenie ery. Znak w SYYYY nie jest napisem BC. |
FM, FX | FM wyłącza domyślne dopełnienie fragmentu wyniku (kolejne FM je przełącza). Przy konwersji tekstu FX wymaga ścisłego dopasowania do maski. |
Pułapka dwóch cyfr roku. Przy odczycie YY przyjmuje dwie pierwsze cyfry bieżącego roku.
W przypadku RR znaczenie mają zarówno dwie końcowe cyfry roku bieżącego, jak i dwie cyfry podane na wejściu:
| Dwie ostatnie cyfry bieżącego roku | Wejście 00–49 | Wejście 50–99 |
|---|---|---|
| 00–49 | Bieżący przedział stu lat | Poprzedni przedział stu lat |
| 50–99 | Następny przedział stu lat | Bieżący przedział stu lat |
Przykładowo, dla sesji pracującej w 2026 roku „95” z maską YY daje rok 2095,
a z maską RR — 1995. W nowych plikach danych najprościej zapisywać wszystkie cztery cyfry.
Pełne zestawienie: modele formatów Oracle.
Dla dwóch wartości typu DATE wyrażenie koniec - poczatek daje liczbę dni,
także niecałkowitą. Dodanie liczby do DATE przesuwa chwilę o tyle dni: d + 1 to dzień później,
a d + 1/24 — godzinę później. Nie należy stosować tej samej reguły bez sprawdzenia typu:
np. różnica dwóch wartości TIMESTAMP w Oracle jest interwałem.
| Funkcja | Reguła |
|---|---|
ADD_MONTHS(d,n) | Przesuwa o n miesięcy. Jeśli d jest ostatnim dniem miesiąca albo żądany dzień nie istnieje w docelowym miesiącu, wynikiem jest ostatni dzień docelowego miesiąca. |
MONTHS_BETWEEN(d1,d2) | Mierzy różnicę w kierunku d1 − d2. Te same numery dni lub dwa końce miesięcy dają wynik całkowity. W pozostałych przypadkach część ułamkowa opiera się na 31 dniach i uwzględnia czas. |
LAST_DAY(d) | Ostatni dzień miesiąca. Dla tej funkcji oraz ADD_MONTHS i MONTHS_BETWEEN miesiąc jest wyznaczany według NLS_CALENDAR. |
NEXT_DAY(d, nazwa) | Pierwszy wskazany dzień tygodnia po dacie d, o tej samej porze. Należy podać pełną nazwę albo rozpoznawany skrót w języku dat sesji, a nie zakładać, że wystarczy numer lub pierwsza litera dnia. |
TRUNC(d, 'MM') | Początek miesiąca, godzina 00:00:00. Bez maski TRUNC(d) daje początek dnia. |
ROUND(d, 'MM') | Zaokrągla do początku miesiąca, od 16. dnia w górę. Dla roku ('YYYY') granicą jest 1 lipca. Bez maski zaokrągla do dnia. |
-- Przy kalendarzu gregoriańskim: SELECT TO_CHAR(ADD_MONTHS(DATE '2024-01-31', 1), 'YYYY-MM-DD') FROM dual; -- Wynik: '2024-02-29' SELECT DATE '2026-11-16' - DATE '2026-11-14' AS dni FROM dual; -- Wynik: 2
Wyszukując zdarzenia z listopada, można objąć również ostatni dzień z dowolną godziną:
data_zdarzenia >= DATE '2026-11-01' AND data_zdarzenia < DATE '2026-12-01'.
Porównanie <= DATE '2026-11-30' kończy zakres o północy na początku 30 listopada.
Dokumentacja: NEXT_DAY, MONTHS_BETWEEN, ROUND i TRUNC dla dat.
NVL(x, y) w Oracle zwraca x, jeśli ma ono wartość,
a w przeciwnym razie y. W Oracle i PostgreSQL można użyć
COALESCE(x, y, ...) — zwraca pierwszy argument różny od NULL
albo NULL, jeżeli wszystkie są nieznane. Należy świadomie dobierać typy argumentów.
Na przykład COALESCE(prowizja, 0) ma sens wtedy, gdy brak prowizji rzeczywiście chcemy potraktować jak zero.
DECODE(wyrażenie, wartość1, wynik1, wartość2, wynik2, domyślny) w Oracle wybiera
wynik pierwszego dopasowania. Brak dopasowania i brak wartości domyślnej daje NULL.
Poniżej dwa zapisy tej samej reguły dla przykładowej tabeli emp:
SELECT job, sal,
DECODE(job, 'ANALYST', sal * 1.10,
'CLERK', sal * 1.15,
'MANAGER', sal * 1.20, sal) AS nowa_pensja
FROM emp;
SELECT job, sal,
CASE job WHEN 'ANALYST' THEN sal * 1.10
WHEN 'CLERK' THEN sal * 1.15
WHEN 'MANAGER' THEN sal * 1.20
ELSE sal END AS nowa_pensja
FROM emp;
CASE działa również w PostgreSQL. Przy przepisywaniu trzeba uważać na
NULL: Oracle DECODE(x, NULL, ...) potrafi dopasować brak wartości,
a CASE x WHEN NULL THEN ... nie. W takim przypadku należy użyć
CASE WHEN x IS NULL THEN ....
Zob. DECODE w Oracle.
Trzy różne pytania: kto się loguje (użytkownik), jaką operację może wykonać w bazie (uprawnienie systemowe) i na którym obiekcie wolno mu pracować (uprawnienie obiektowe). Rola grupuje uprawnienia, które można potem nadać kilku osobom.
Przykład dla administratora: lokalne konto w bazie PDB. Nazwy ujęte poniżej w nawiasy kątowe trzeba zastąpić rzeczywistymi wartościami. Samo utworzenie konta nie wystarcza do zalogowania.
CREATE USER uczestnik IDENTIFIED BY <haslo>; GRANT CREATE SESSION TO uczestnik; ALTER USER uczestnik IDENTIFIED BY <nowe_haslo>;
CREATE TABLE, CREATE VIEW, CREATE SEQUENCECREATE PROCEDUREEXECUTE.
W PL/SQL domyślne AUTHID DEFINER oznacza prawa właściciela;
AUTHID CURRENT_USER wybiera prawa wywołującego. Przy kompilacji
statycznego SQL odwołującego się do cudzych tabel nie wystarczy dostęp
właściciela wyłącznie przez zwykłą rolę.PUBLIC i DBAPUBLIC udostępnia je wszystkim użytkownikom.
DBA to szeroka rola administracyjna; do ćwiczeń wystarczą
konkretne, potrzebne uprawnienia. Uprawnienia systemowe ze słowem
ANY mają szerszy zasięg niż własny schemat.Przykład: administrator tworzy rolę i przydziela ją istniejącemu użytkownikowi, a właściciel tabeli z zestawu „Las” daje tej roli prawo odczytu:
-- Administrator CREATE ROLE czytelnik_lasu; GRANT czytelnik_lasu TO uczestnik; -- Właściciel tabeli LASY GRANT SELECT ON lasy TO czytelnik_lasu; GRANT UPDATE (nazwa) ON lasy TO uczestnik; REVOKE UPDATE ON lasy FROM uczestnik;
SELECT, INSERT, UPDATE i DELETE
na tabeli to odrębne uprawnienia. W Oracle listę kolumn wolno wskazać dla
INSERT, UPDATE i REFERENCES, ale nie dla
SELECT; do udostępnienia tylko części kolumn można użyć widoku.
Przy nadaniu uprawnienia obiektowego użytkownikowi WITH GRANT OPTION
pozwala mu nadać je dalej; tej klauzuli nie dodaje się przy nadaniu roli
uprawnienia obiektowego. Przy odbieraniu REFERENCES
CASCADE CONSTRAINTS usuwa zależne klucze obce utworzone dzięki
temu uprawnieniu.
Dokumentacja Oracle: GRANT i tabela uprawnień, AUTHID w PL/SQL.
TO_CHAR(liczba, maska) zamienia liczbę na napis.
W drugą stronę działa TO_NUMBER(napis, maska). Wartość liczbowa nie przechowuje
separatorów tysięcy ani przecinka dziesiętnego. Separatory w masce trzeba dopasować
do zapisu tekstowego.
| Element | Znaczenie przy wyświetlaniu |
|---|---|
9, 0 | Miejsce na cyfrę; 0 wymusza zero, gdy cyfry brakuje. Np. TO_CHAR(12,'FM0000') daje '0012'. |
D, G | Lokalne separatory części dziesiętnej i grup cyfr. Parametr NLS_NUMERIC_CHARACTERS określa je w tej właśnie kolejności. Znaki maski . i , wskazują odpowiednio dosłowną kropkę dziesiętną i przecinek grupujący. |
FM, MI, PR | FM usuwa dopełnienie; MI umieszcza minus po liczbie ujemnej, a PR ujmuje ją w nawiasy kątowe. |
$, L | Symbol dolara lub lokalny symbol waluty. |
EEEE, V | Notacja naukowa (np. 1,2 × 10³); V skaluje wartość do wyświetlenia przez potęgę dziesięciu zależną od liczby miejsc po V. |
SELECT TO_CHAR(1234.5, 'FM9G999D00',
'NLS_NUMERIC_CHARACTERS = '',.''') AS napis
FROM dual;
-- Wynik: '1.234,50'
SELECT TO_NUMBER('1.234,50', '9G999D99',
'NLS_NUMERIC_CHARACTERS = '',.''') AS kwota
FROM dual;
-- Wynik: liczba 1234.5; sposób wyświetlenia zależy od klienta
Dokumentacja: TO_CHAR dla liczb i TO_NUMBER.
ROUND(x,n) zaokrągla do n miejsc po przecinku;
TRUNC(x,n) obcina w kierunku zera. Pominięcie drugiego argumentu oznacza
zero miejsc po przecinku; ujemne n działa na lewo od przecinka.
SELECT ROUND(45.926, 2) AS a, TRUNC(45.926, 2) AS b,
ROUND(45.923,-1) AS c, TRUNC(-45.923,-1) AS d
FROM dual;
-- a = 45.93, b = 45.92, c = 50, d = -40
MOD(dzielna, dzielnik) zwraca resztę z dzielenia pierwszego
argumentu przez drugi: MOD(1600,300) = 100, a MOD(300,1600) = 300.
Wynik dla ujemnej dzielnej może być ujemny: MOD(-11,4) = -3.
Osobliwość Oracle: MOD(7,0) zwraca 7. Nie jest to reguła dzielenia przez zero w SQL.
Zob. MOD w Oracle.
ORDER BY?W zwykłym zapytaniu można sortować według kolumny tabeli, wyrażenia, aliasu kolumny wynikowej
lub jej pozycji na liście SELECT. Pozycje liczymy od 1: ORDER BY 2
oznacza „według drugiej kolumny wyniku”. Alias jest czytelniejszy i zachowuje znaczenie,
gdy zmieni się kolejność kolumn.
SELECT empno, sal * 12 AS roczna_pensja FROM emp ORDER BY roczna_pensja DESC NULLS LAST, empno;
Przy tej samej rocznej pensji o kolejności decyduje empno. Bez końcowego
ORDER BY nie ma gwarancji kolejności, nawet jeśli wynik poprzednio wyglądał
na posortowany.
W prostym SELECT ename FROM emp ORDER BY sal pensji nie trzeba wyświetlać.
Przy SELECT DISTINCT ename ta sama reguła już nie działa:
kilku pracowników o tym samym imieniu może mieć różne pensje. Jeśli chodzi o sortowanie
imion według najwyższej pensji, proszę zapisać to wprost:
SELECT ename FROM emp GROUP BY ename ORDER BY MAX(sal) DESC NULLS LAST, ename.
W Oracle 19c i PostgreSQL końcowe sortowanie wyniku UNION lub
UNION ALL może używać nazw kolumn wynikowych lub ich pozycji. Aby sortować według
nowego wyrażenia, można ująć zapytanie złożone w podzapytanie i posortować wynik w zewnętrznym
SELECT.
Dokumentacja: Oracle — sortowanie wyników;
PostgreSQL — ORDER BY.
W Oracle RENAME stara TO nowa zmienia nazwę własnej tabeli,
perspektywy, sekwencji lub prywatnego synonimu. Nie jest to polecenie dla
każdego rodzaju obiektu. Dla tabeli można też napisać
ALTER TABLE robocza RENAME TO archiwum — ta postać działa
również w PostgreSQL.
| Polecenie | Skutek |
|---|---|
DELETE FROM robocza WHERE nr = 1; |
Usuwa wiersze spełniające warunek. Bez WHERE usuwa wszystkie.
Jest to DML: przed zatwierdzeniem można cofnąć zmiany. |
TRUNCATE TABLE robocza; |
Opróżnia całą tabelę; nie przyjmuje WHERE.
Jest to DDL: w Oracle zwykłego TRUNCATE TABLE nie można cofnąć
przez ROLLBACK. |
Po DELETE miejsce w segmencie zwykłej tabeli Oracle pozostaje
do ponownego użycia. TRUNCATE domyślnie zwalnia miejsce
z zastrzeżeniem minimalnego przydziału; REUSE STORAGE zachowuje
przydzieloną przestrzeń. W PostgreSQL TRUNCATE można wycofać
w tej samej, jeszcze niezatwierdzonej transakcji.
Dokumentacja: Oracle: RENAME, Oracle: TRUNCATE TABLE, PostgreSQL: TRUNCATE.
SAVEPOINT etap;ROLLBACK TO SAVEPOINT etap;ROLLBACK;COMMIT;Oracle, zwykłe obiekty schematu: przed poprawnym składniowo DDL
Oracle wykonuje niejawny COMMIT, nawet jeśli wykonanie DDL zakończy
się błędem. Drugi COMMIT następuje po pomyślnym
wykonaniu DDL. Jeśli więc po UPDATE wydamy poprawne składniowo
CREATE TABLE, a tabela o tej nazwie już istnieje, nowe
CREATE się nie powiedzie, ale wcześniejszego UPDATE
nie cofniemy. Reguły dla szczególnych obiektów mogą się różnić: przykładowo
opróżnienie prywatnej tabeli tymczasowej nie zatwierdza bieżącej transakcji.
PostgreSQL: zwykłe CREATE TABLE,
DROP TABLE i TRUNCATE TABLE można objąć transakcją
BEGIN … ROLLBACK. Nie każda operacja administracyjna
jest w takim bloku dozwolona (np. CREATE DATABASE).
Dokumentacja: Oracle: COMMIT, Oracle: ROLLBACK, PostgreSQL: CREATE DATABASE.
Podzapytanie jest skorelowane, gdy odwołuje się do bieżącego
wiersza zapytania zewnętrznego. W UPDATE może wyliczać wartość
przypisywaną w SET albo wybierać wiersze przez WHERE;
w DELETE służy do filtrowania wierszy. W Oracle 19c alias
tabeli docelowej podaje się bez AS.
Przykład na bazie „Las”: usuń paśniki w lasach, w których nie odnotowano
żadnego gatunku. Przed zmianą można sprawdzić dokładnie ten sam warunek za
pomocą SELECT. Poniższy przykład dla Oracle zakłada wyłączony
autocommit i po pokazaniu operacji ją wycofuje.
SELECT p.nr, p.nr_lasu
FROM pasniki p
WHERE NOT EXISTS (SELECT 1 FROM wystapienia w
WHERE w.nr_lasu = p.nr_lasu);
SAVEPOINT przed_usunieciem;
DELETE FROM pasniki p
WHERE NOT EXISTS (SELECT 1 FROM wystapienia w
WHERE w.nr_lasu = p.nr_lasu);
ROLLBACK TO SAVEPOINT przed_usunieciem;
Podzapytanie skalarne po prawej stronie przypisania do jednej kolumny
może zwrócić najwyżej jeden wiersz. Brak wiersza daje wartość
NULL, a więcej niż jeden wiersz powoduje błąd.
Zobacz też przykład skorelowanego UPDATE
oraz opis UPDATE w Oracle.
SPOOL jest poleceniem klienta SQL*Plus. Zapisuje wyświetlany
wynik do pliku po stronie klienta. CREATE oznacza nowy plik,
APPEND dopisuje do istniejącego, a domyślne REPLACE
nadpisuje plik. SPOOL OFF kończy zapis. Widoczność wyniku na
ekranie zależy od ustawień klienta, np. SET TERMOUT OFF przy
wykonywaniu skryptu. Poleceń klienta nie kończymy średnikiem.
Ćwiczenie: wynikiem SELECT może być tekst
polecenia SQL. dropall.sql wypisuje propozycje
DROP TABLE dla zwykłych tabel właściciela w schemacie ćwiczebnym.
Samo wygenerowanie napisu nie usuwa tabeli. Aby zapisać propozycje,
proszę pobrać plik i w osobnej sesji SQL*Plus uruchomionej w tym katalogu wpisać:
SET ECHO OFF SET HEADING OFF SET FEEDBACK OFF SET PAGESIZE 0 SET LINESIZE 1000 SET TRIMSPOOL ON SPOOL przeglad-tabel.sql CREATE @dropall.sql SPOOL OFF
Wygenerowany plik przeglad-tabel.sql można otworzyć w edytorze. Przed
usunięciem wybranych tabel ćwiczebnych proszę sprawdzić właściciela i nazwy, pozostawić tylko
potrzebne polecenia i ułożyć je tak, aby tabele z kluczami obcymi usuwać przed
tabelami, na które wskazują. Dopiero przejrzaną kopię można osobno uruchomić
przez @nazwa_kopii.sql. Generator nie dopisuje
CASCADE CONSTRAINTS ani PURGE. Pomija m.in. nazwy
z nietypowymi znakami, tabele tymczasowe i tabele przechowujące widoki
zmaterializowane. Do usunięcia konkretnego zestawu z kursu najprościej użyć
jego własnego skryptu *.drop.sql.
Drugi przykład, droptotal.sql, wypisuje polecenia
tylko dla wybranych samodzielnych obiektów: widoków, sekwencji, pakietów,
funkcji i procedur. Nie każdy wiersz USER_OBJECTS oznacza
samodzielny obiekt z poleceniem DROP typ nazwa; w słowniku są
również np. partycje i wewnętrzne obiekty bazy. Żaden z obu przykładów
nie wykonuje wygenerowanych poleceń ani nie usuwa plików z dysku.
Dokumentacja: SPOOL w SQL*Plus.
Poniższe przykłady dotyczą Db2 11.5 dla Linux, UNIX i Windows (LUW).
Część tych konstrukcji występuje także w innych systemach. W przykładach
emp i dept oznaczają pracowników i działy.
UNION ALL zwraca go pięć razy, INTERSECT ALL
dwa razy, a EXCEPT ALL (lewa strona minus prawa) — raz. Bez ALL
wynik każdej z tych operacji jest zbiorem.INNER JOIN zwraca dopasowane pary, a
LEFT, RIGHT i FULL OUTER JOIN zachowują też
niedopasowane wiersze z odpowiednich stron. Słowo OUTER jest opcjonalne.UNION ALL; sam fakt powtórzenia wiersza nie zatrzymuje rekurencji.
Przykład bez tabel użytkownika:
WITH liczby (n) AS ( SELECT 1 FROM SYSIBM.SYSDUMMY1 UNION ALL SELECT n + 1 FROM liczby WHERE n < 3 ) SELECT n FROM liczby ORDER BY n;Wynik to 1, 2, 3. W drzewie bez końcowego sortowania nie należy oczekiwać ani porządku przeszukiwania w głąb (DFS), ani wszerz (BFS). Przy danych z cyklami trzeba zadbać o zakończenie obliczeń.
FROM. LATERAL pozwala
prawej stronie odwołać się do kolumn pozycji stojącej po jej lewej stronie. Db2 LUW
dopuszcza tu także słowo TABLE zamiast LATERAL.
Poniżej każdy dział otrzymuje swój licznik pracowników, także zero:
SELECT d.deptno, x.ilu
FROM dept d,
LATERAL (SELECT COUNT(*) AS ilu
FROM emp e
WHERE e.deptno = d.deptno) AS x
ORDER BY d.deptno;UNDER; typ strukturalny podtabeli musi odpowiadać jej położeniu
w hierarchii typów. Jeżeli osoba jest taką tabelą nadrzędną:
SELECT * FROM osoba obejmuje również wiersze podtabel, ale zwraca
kolumny określone dla osoba;SELECT * FROM ONLY(osoba) pomija wiersze należące do podtabel;SELECT * FROM OUTER(osoba) obejmuje wiersze i dodatkowe kolumny
z całej hierarchii. Dla kolumny, która nie występuje w danym podtypie, zwraca
NULL. Ta konstrukcja nie oznacza OUTER JOIN.podwladni(nr) zwracającą m.in. kolumnę empno, zapis
FROM emp e, TABLE(podwladni(e.empno)) AS p udostępnia jej wynik jako
tabelę p. Argument funkcji może pochodzić z bieżącego wiersza e.
Gdy funkcja zwróci dla niego zero wierszy, taka para tabel nie da w wyniku żadnego
wiersza dla tej osoby.GROUP BY GROUPING SETS ((a,b),(a),())
daje wyniki dla par, dla każdego a i dla całości. Ten sam zestaw poziomów
daje ROLLUP(a,b). CUBE(a,b) dodaje jeszcze grupy dla samego
b. W wyniku GROUPING(b) = 1 oznacza, że w tym zestawie grupującym
pominięto b. Dzięki temu można odróżnić NULL w wierszu sumy
od NULL pochodzącego z danych.Dokumentacja IBM: operacje na zbiorach,
rekurencyjne CTE,
odwołania do tabel i funkcji w FROM,
warianty GROUP BY.
NULL i typy kolumn w UNIONObie gałęzie muszą mieć tyle samo kolumn. Silnik uzgadnia typ osobno dla każdej
pary kolumn na tej samej pozycji. Sam literał NULL nie oznacza błędu:
w prostym połączeniu może przyjąć typ z drugiej gałęzi. Gdy typ ma być oczywisty
dla czytelnika albo zapytanie zawiera kilka zagnieżdżonych operacji, warto podać go jawnie.
SELECT deptno, ename AS nazwa FROM emp UNION SELECT CAST(NULL AS INTEGER), dname FROM dept;
Przy liczbowym deptno rzutowanie opisuje brakujący numer działu.
Typowana wartość nadal jest NULL, a nie zerem. W Oracle można też zapisać
TO_NUMBER(NULL); CAST z powyższego przykładu działa
również w PostgreSQL. Wybór UNION albo UNION ALL decyduje
o usuwaniu powtórzeń, ale nie zwalnia z uzgodnienia typów.
Przypadek PostgreSQL. W wyrażeniu
SELECT NULL UNION SELECT NULL UNION SELECT 1 dwie pierwsze gałęzie
zostają połączone najpierw. Ponieważ nie mają określonego typu, ich wspólnym typem staje się
text. Połączenie tego wyniku z liczbą całkowitą powoduje błąd.
Wystarczy napisać w pierwszej gałęzi SELECT CAST(NULL AS INTEGER).
Dokumentacja: Oracle — typy w operacjach na zbiorach; PostgreSQL — ustalanie typu wyniku.
Zwykły widok zapisuje definicję zapytania. Przykład na tabeli
lasy: widok pokazuje tylko lasy o wielkości co najmniej 100.
CREATE VIEW duze_lasy AS SELECT nr, nazwa, rodzaj, wielkosc FROM lasy WHERE wielkosc >= 100 WITH CHECK OPTION CONSTRAINT duze_lasy_zakres;
NOFORCE (domyślnie)FORCECREATE FORCE VIEW nazwa AS ….WITH CHECK OPTIONwielkosc >= 100. Bez tej klauzuli zmieniony wiersz mógłby
po prostu zniknąć z widoku. To ograniczenie nie dotyczy zmian dokonywanych
bezpośrednio w tabeli.WITH READ ONLYWITH CHECK OPTION: zakazuje modyfikowania danych przez ten widok.
Sam brak tej klauzuli nie gwarantuje, że dany widok jest modyfikowalny
(np. wynik grupowania nie daje naturalnej instrukcji aktualizacji wierszy).Zobacz przykłady aktualizacji przez widok oraz dokumentację Oracle: CREATE VIEW.