Wyobraźmy sobie następujący scenariusz. Mamy jakąś bazę danych i chcemy do niej wstawić gotowe rekordy lub zaktualizować już istniejące. Załóżmy, że chcemy zaktualizować tabelę User
i wstawić testowe wartości do kolumn Name i Surname. Nasz zbiór danych testowych wygląda następująco:
Chcemy wstawić te dane do istniejącej tabeli. Jak możemy to zrobić?
Zwykły skrypt
Możemy napisać program, który nam wygeneruje listę skryptów podmieniając kolejno parametry dla pojedynczego rekordu:
UPDATE User SET Name = '{username}', Surname = '{surname}' WHERE Id = {id};
Cały skrypt wyglądałby następująco:
UPDATE User SET Name = 'TestName1', Surname = 'TestSurname1' WHERE Id = 1; UPDATE User SET Name = 'TestName2', Surname = 'TestSurname2' WHERE Id = 2;
Oczywiście dla tak prostego przykładu jak najbardziej można użyć to rozwiązanie. Jednakże jeśli pojedynczy skrypt byłby bardziej rozbudowany (np. zawierałby JOINY do kilku innych tabel), to jego czytelność drastycznie by zmalała. Każdy UPDATE to byłoby x takich samych, powtarzanych linijek skryptu. A po co się powtarzać, gdy można to zrobić inaczej?
Użycie CTE i magicznego słówka WITH
Z pomocą przychodzą wspólne wyrażenia tablicowe (CTE – Common Table Expressions) i magiczne słówko WITH. Pozwala ono utworzyć zbiór danych jako swojego rodzaju tabelę pośrednią. Wygląda to następująco:
WITH Source(Id, UserName, UserSurname) AS ( SELECT 1, 'TestName1', 'TestSurname1' UNION ALL SELECT 2, 'TestName2'', 'TestSurname2' ) UPDATE TableName SET Name = Source.UserName, Surname = Source.UserSurname WHERE Id = Source.Id
Jak widać, na początku zdefiniowaliśmy Source jako źródło danych. Następnie napisaliśmy jednorazowo skrypt UPDATE, który wywoła się dla każdego rekordu w SOURCE.
Podsumowanie
Jak widać, w przypadku bardziej skomplikowanych skryptów, użycie CTE może w znaczący sposób zmniejszyć liczbę linijek skryptu i zwiększyć jego czytelność. Skrypt jest podzielony na część definiującą dane wejściowe i część wykonującą konkretne zapytanie.
Szczerze mówiąc nie widzę tu nigdzie rekurencji. Czy mógłbym prosić o jakieś wyjaśnienie.
PolubieniePolubienie
Zgadzam się, że to wygląda bardziej na iterację. Aczkolwiek w dokumentacji jest używane sformułowanie rekurencja. Np. tutaj https://docs.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-2017
PolubieniePolubienie
Nie każde wyrażnie CTE musi być rekurencyjne, to które podałaś jako przykład nie ma nic wspólnego z rekurencją.
PolubieniePolubienie
No to mnie zagiąłeś 😉 w takim razie zbadam sprawę w weekend i zrobię update.
PolubieniePolubienie
Posiedziałam, pogrzebałam i faktycznie, masz rację. Post już zaktualizowałam, a dla wszystkich zainteresowanych podysłam link do jednego z artykułów (dla ułatwienia w języku polskim) https://technet.microsoft.com/pl-pl/library/arsenal-programisty-t-sql–common-table-expression-cte.aspx#4
PolubieniePolubienie