with-Organize Complex Queries (Polski)

w inżynierii oprogramowania powszechną praktyką jest grupowanie instrukcji jako małych i łatwo zrozumiałych jednostek-czyli funkcji lub metod. Dzięki temu Kod nadaje się do wielokrotnego użytku i poprawia czytelność.

mimo że SQL ma również funkcje i procedury, nie są one odpowiednimi narzędziami do budowania łatwo zrozumiałych i wielokrotnego użytku jednostek. W SQL ani funkcje, ani procedury nie są obywatelami pierwszej klasy w taki sam sposób, jak zapytania podrzędne.0 elementem składowym SQL są zapytania1-Nie instrukcje.,

aby zapytania były wielokrotnego użytku, SQL-92 wprowadził widoki. Po utworzeniu Widok ma nazwę w schemacie bazy danych, dzięki czemu inne zapytania mogą używać go jak tabeli.

SQL:1999 dodałwith klauzulę definiującą „widoki z zakresu instrukcji”. Nie są one przechowywane w schemacie bazy danych: zamiast tego są ważne tylko w zapytaniu, do którego należą. Umożliwia to ulepszenie struktury instrukcji bez zanieczyszczania globalnej przestrzeni nazw.,

składnia

with klauzula jest po prostu opcjonalnym prefiksem dla select:2

WITH query_name (column_name1, ...) AS (SELECT ...) SELECT ...

składnia po słowie kluczowym with jest taki sam jak dla create view: zaczyna się od nazwy zapytania, a opcjonalnie3 i w nawiasie nazwa zwracanych kolumn. Słowo kluczowe as ostatecznie wprowadza samą definicję (zapytanie) – ponownie w nawiasach.,

With nie jest samodzielnym poleceniem, takim jakcreate view jest: po nim musi następowaćselect. To zapytanie (i zawarte w nim zapytania podrzędne) może odnosić się do właśnie zdefiniowanej nazwy zapytania w klauzuli from.

pojedyncza klauzulawith może wprowadzić wiele nazw zapytań, oddzielając je przecinkiem (słowo kluczowe with nie jest powtarzane)., Każde z tych zapytań może odnosić się do nazw zapytań zdefiniowanych wcześniej w ramach tej samej with clause4 (często zaniedbywana reguła—patrz Zgodność):

WITH query_name1 AS ( SELECT ... ) , query_name2 AS ( SELECT ... FROM query_name1 ... )SELECT ...

nazwy zapytań zdefiniowane za pomocą with maskują istniejące tabele lub widoki o tej samej nazwie.5

względy wydajnościowe

większość baz danych przetwarzawith -zapytania w ten sam sposób, w jaki przetwarzają widoki: zastępują odniesienie do zapytania przez jego definicję i optymalizują ogólne zapytanie.,

baza danych PostgreSQL była inna do wersji 12: zoptymalizowała każde zapytaniewith I niezależne od siebie główne polecenie.

Jeśli zapytaniewith jest wywoływane wielokrotnie, niektóre bazy danych buforują (np. „materializują”) jego wynik, aby zapobiec podwójnemu wykonaniu.

Przeczytaj więcej na ten temat w „with Klauzula: wpływ wydajności”.

przypadki użycia

• Literate SQL

• testy jednostkowe danych przejściowych

• Przypisywanie nazw kolumnom bez znanej nazwy

Zgodność

klauzula with został wprowadzony z SQL:1999 jako zestaw opcjonalnych funkcji. Pomijając wariant recursive, funkcje to T121 dla klauzul najwyższego poziomuwithoraz t122 dla klauzulwith w zapytaniach podrzędnych.

podstawowa funkcjonalnośćwith jest dobrze obsługiwana., Jedynym obszarem, w którym produkty zachowują się inaczej, jest rozdzielczość nazw. Szczególnie warto zauważyć, że withjest często traktowany jakwith recursive.6 bardziej subtelne niezgodności są związane z kwalifikowanymi nazwami tabel (schema.table nie może odnosić się do zapytania with) 7 i widoków używanych w zakresie zapytań with (zapytanie wewnątrz widoku nie „widzi” zewnętrznego with div > klauzula).,8

zgodne alternatywy

widoki mogą obejmować niektóre przypadki użycia. Może to jednak łatwo prowadzić do nieuzasadnionej liczby wyświetleń („namespace pollution”). W takich przypadkach, zapytania podrzędne są często lepszym rozwiązaniem.

rozszerzenia własnościowe

z prefiksem DML (PostgreSQL, SQL Server, SQLite)

niektóre bazy danych akceptująwith jako prefiks do instrukcji DML (docs: PostgreSQL, SQL Server, SQLite).,

SQL Server może również używać zapytaniawith jako celu dla instrukcji DML (zasadniczo budowania widoku aktualizowalnego).

Funkcje W with (Oracle)

baza danych Oracle obsługuje deklaracje funkcji i procedur w klauzuliwith od wersji 12cr1 (dokumentacja).

DML in with (PostgreSQL)

począwszy od wersji 9.1, baza danych PostgreSQL obsługuje instrukcje DML (insert, update, delete) wewnątrz ciała with zapytania (dokumentacja)., Podczas używania (również zastrzeżonej) klauzuli returning instrukcji DML, zapytanie with faktycznie zwraca dane (np. wstawione wiersze).