Informacje o SQL

Ś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”.

Spis treści

  1. Funkcje agregujące
  2. Funkcje na napisach
  3. Komentarze do tabel i kolumn
  4. Zapytania hierarchiczne: START WITH i CONNECT BY (Oracle)
  5. Format zapisu daty (Oracle)
  6. Funkcje na datach (Oracle)
  7. Wartość zastępcza i wybór warunkowy
  8. Użytkownicy, role i uprawnienia w Oracle
  9. Format zapisu liczb (Oracle)
  10. Funkcje na liczbach
  11. Po czym można sortować w ORDER BY?
  12. Zmiana nazwy i opróżnianie tabeli
  13. Transakcje, punkty powrotu i DDL
  14. UPDATE i DELETE ze skorelowanym podzapytaniem
  15. Zapisywanie wyniku do pliku (SQL*Plus)
  16. Wybrane konstrukcje w IBM Db2
  17. NULL i typy kolumn w UNION
  18. Tworzenie perspektyw (widoków) w Oracle

Funkcje agregujące

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
FunkcjaCo 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.

Funkcje na napisach

OraclePrzykład lub znaczenie
LOWER, UPPER, INITCAPLOWER('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.

Komentarze do tabel i kolumn

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ć.

Zapytania hierarchiczne: START WITH i CONNECT BY (Oracle)

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.

Format zapisu daty (Oracle)

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 maskiZnaczenie
YYYY, MM, DDRok kalendarzowy, miesiąc, dzień miesiąca.
Q, DDDKwartał i dzień roku. D oznacza numer dnia tygodnia; to, który dzień ma numer 1, zależy od NLS_TERRITORY.
MONTH, MON, DAY, DYNazwy i skróty w języku sesji; np. fmMonth usuwa dopełnienie spacjami. Wielkość liter w masce steruje wielkością liter nazwy.
IYYY, IWRok 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, SSGodzina 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, JLiczba sekund od północy; numer dnia juliańskiego. To dwa różne elementy.
CC, SYYYY, BC, ADWiek; rok ze znakiem minus dla lat p.n.e.; oznaczenie ery. Znak w SYYYY nie jest napisem BC.
FM, FXFM 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 rokuWejście 00–49Wejście 50–99
00–49Bieżący przedział stu latPoprzedni przedział stu lat
50–99Następny przedział stu latBieżą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.

Funkcje na datach (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.

FunkcjaReguł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.

Wartość zastępcza i wybór warunkowy

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.

Użytkownicy, role i uprawnienia 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 SEQUENCE
Pozwalają tworzyć odpowiednie obiekty we własnym schemacie. Przy zapisie danych właściciel tabeli potrzebuje również miejsca w przestrzeni tabel (np. przydzielonej kwoty).
CREATE PROCEDURE
Obejmuje procedury, funkcje i pakiety we własnym schemacie. Do wywołania procedury innego właściciela można otrzymać uprawnienie obiektowe EXECUTE. 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 DBA
Nadanie uprawnienia PUBLIC 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.

Format zapisu liczb (Oracle)

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.

ElementZnaczenie przy wyświetlaniu
9, 0Miejsce na cyfrę; 0 wymusza zero, gdy cyfry brakuje. Np. TO_CHAR(12,'FM0000') daje '0012'.
D, GLokalne 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, PRFM usuwa dopełnienie; MI umieszcza minus po liczbie ujemnej, a PR ujmuje ją w nawiasy kątowe.
$, LSymbol dolara lub lokalny symbol waluty.
EEEE, VNotacja 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.

Funkcje na liczbach

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.

Po czym można sortować w 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.

Zmiana nazwy i opróżnianie tabeli

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.

Usuwanie wierszy ze zwykłej tabeli w Oracle
PolecenieSkutek
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.

Transakcje, punkty powrotu i DDL

SAVEPOINT etap;
Zapamiętuje punkt w bieżącej transakcji.
ROLLBACK TO SAVEPOINT etap;
Cofa własne zmiany wykonane po tym punkcie. Nie kończy transakcji; wcześniejsze zmiany nadal można zatwierdzić lub wycofać.
ROLLBACK;
Cofa niezatwierdzone zmiany bieżącej transakcji i ją kończy. Nie przywraca całej bazy do dawnej chwili.
COMMIT;
Zatwierdza zmiany i kończy transakcję; jej punkty powrotu przestają istnieć.

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.

UPDATE i DELETE ze skorelowanym podzapytaniem

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.

Zapisywanie wyniku do pliku (SQL*Plus)

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.

Wybrane konstrukcje w IBM Db2

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.

  1. Multizbiory. Jeżeli pewien wiersz występuje po lewej stronie trzy razy, a po prawej dwa razy, 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.
  2. Złączenia. INNER JOIN zwraca dopasowane pary, a LEFT, RIGHT i FULL OUTER JOIN zachowują też niedopasowane wiersze z odpowiednich stron. Słowo OUTER jest opcjonalne.
  3. Rekurencja. Pierwsza część wspólnego wyrażenia tabelowego (CTE) wyznacza wiersze początkowe. Część rekurencyjna wytwarza z nich kolejne wiersze. Obliczanie kończy się, gdy kolejny krok nie zwraca żadnego wiersza. W tej składni Db2 używamy 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ń.
  4. Zależne podzapytanie w 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;
  5. Hierarchie tabel typowanych. Db2 pozwala zdefiniować podtabelę klauzulą UNDER; typ strukturalny podtabeli musi odpowiadać jej położeniu w hierarchii typów. Jeżeli osoba jest taką tabelą nadrzędną:
  6. Funkcja tabelowa. Jeżeli zdefiniowano funkcję tabelową 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.
  7. Grupowanie na kilku poziomach. 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 UNION

Obie 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.

Tworzenie perspektyw (widoków) w Oracle

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)
Tabele bazowe muszą istnieć, a właściciel widoku musi mieć wymagane uprawnienia do nich. Przy tworzeniu widoku nad cudzą tabelą nie wystarcza uprawnienie odczytu uzyskane jedynie przez rolę.
FORCE
W pewnych sytuacjach pozwala zapisać niepoprawny jeszcze widok, np. gdy nie istnieje tabela bazowa. Nie naprawia jego definicji ani nie nadaje praw. Przy próbie odczytu brakujące zależności muszą już być dostępne; nie każdy błąd daje się odroczyć. Przykład składni: CREATE FORCE VIEW nazwa AS ….
WITH CHECK OPTION
Przez powyższy widok nie można wstawić ani zmienić wiersza tak, by przestał spełniać wielkosc >= 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 ONLY
Alternatywa dla WITH 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.

Sprawdź poprawność XHTML 1.0 Strict


SQL — strona główna