Kiedy zacząłem się do Structured Query Language (SQL) około 2014 roku, pamiętam przeszukanie Internetu w poszukiwaniu przykładów. Widziałem, jak ludzie mówią takie rzeczy, jak „to używa dynamicznego SQL” lub ” ten skrypt jest dynamiczny.”Chociaż widziałem przykłady, tak naprawdę nie rozumiałem komponentów, które uczyniły go „dynamicznym”, ponieważ byłem nowy. W miarę jak zdobywałem więcej doświadczenia w tej dziedzinie, nauczyłem się nie tylko trzech komponentów składających się na dynamiczny SQL, ale także Jak zastosować go do różnych przypadków użycia., To krótkie wprowadzenie ma na celu pomóc początkującym SQL lub komukolwiek innemu, lepiej zrozumieć składniki dynamicznego SQL.

dane do przykładów

korzystam z danych o transakcjach na rynku opcji dostępnych na Kaggle, jeśli jestem zainteresowany.

czym jest dynamiczny SQL?

chociaż jego aplikacja może być skomplikowana, definicja dynamicznego SQL jest w rzeczywistości prosta:

Dynamic SQL jest instrukcją SQL, która buduje się w czasie wykonywania.

pozwala to na zbudowanie zapytania ogólnego przeznaczenia i skonstruowanie instrukcji, którą musisz wykonać w locie za pomocą zmiennych., Poniżej zdekonstruuję ten przykład super basic dynamic SQL:

DECLARE @dynamic_sql NVARCHAR(max)select @dynamic_sql = 'select * from orderFlow'EXEC(@dynamic_sql)

przeciwieństwem dynamicznego SQL jest statyczny SQL, który jest instrukcją SQL, która nie zmienia się w czasie wykonywania. Na przykład:

SELECT * FROM orderFlow

zauważyłeś różnicę? Statyczny SQL nie jest uruchamiany przy użyciu polecenia execute ani budowany przy użyciu zmiennych.

Trzy Kroki pisania dynamicznego SQL

Jeśli chcesz pisać dynamiczny SQL, pamiętaj o trzech składnikach :

1. deklaruj zmienne
   pomyśl o użyciu zmiennych jako bloków konstrukcyjnych dla instrukcji SQL., W przykładzie zadeklarowałem zmienną o nazwie dynamic_sql i podałem jej typ danych nvarchar (max). Używam max, aby dać mi najwięcej miejsca do wprowadzenia instrukcji SQL.

DECLARE @dynamic_sql NVARCHAR(max)

2. Construct SQL String
fakt, że można wprowadzić dowolny SQL chcesz do zmiennej dynamic_sql jest częścią tego, co sprawia, że ten przykład dynamiczny SQL w pierwszej kolejności! Ponieważ chcę zwrócić wszystkie wyniki z tabeli, orderFlow, ustawiłem zmienną na prostą instrukcję select, która może być wykonana bez błędu.

select @dynamic_sql = 'select * from orderFlow '

3., Polecenie Execute SQL
Instrukcja zbudowana w kroku drugim musi zostać wykonana. Polecenie EXEC jest zwykle używane do uruchamiania procedur przechowywanych, ale może uruchamiać dowolne legalne polecenie SQL przechowywane jako ciąg znaków, jak ten przechowywany w zmiennej dynamic_sql.

EXEC(@dynamic_sql)

wyobraź sobie pracę z interfejsem użytkownika, który ma pole tekstowe, które pozwala użytkownikowi wprowadzić surowe polecenie SQL, a następnie wykonuje to polecenie, gdy użytkownik kliknie Uruchom. Podobną funkcjonalność wykorzystałem w produktach Business Intelligence!, Teraz, gdy znasz dynamiczny SQL, łatwo sobie wyobrazić, jak może on działać za kulisami.

gratulacje! Teraz znasz 3 składniki dynamicznego SQL!, przykłady:

wyobraź sobie, że tworzysz interfejs użytkownika, który pozwala użytkownikom na interakcję z danymi, ale zamiast pozwalać im pisać czysty SQL, pozwalasz im konstruować instrukcję za pomocą 2 List:

za kulisami użyj procedury składowanej i dynamicznego SQL do wykonania instrukcji SQL zbudowanej w interfejsie:

zauważ, że procedura składowana używa danych wejściowych użytkownika do wypełnienia dwóch zmiennych., Ponieważ zmienne są już zdefiniowane w makiecie interfejsu użytkownika, procedura składowana musi po prostu skonstruować łańcuch znaków i wykonać polecenia. Dodałem spację, aby użytkownik nie musiał pamiętać o wpisaniu jej podczas wykonywania procedury., Na przykład:

przypadek użycia 2:

wyobraź sobie, że chcesz użyć procedury składowanej do zapytania tabeli orderflow o konkretny symbol zapasów:

CREATE PROCEDURE sp_example2 
@stock_symbol NVARCHAR(6) = NullAS
SELECT * FROM orderFlow where sym = @stock_symbol

zauważ, że zmienna jest używana w klauzuli WHERE w przypadku użycia 2. W ten sposób mogę wprowadzić symbol akcji i uzyskać wyniki bez konieczności używania statycznego zapytania w kółko.,

The Downsides with Dynamic

There can be some downsides to using dynamic SQL., Może być podatny na SQL injection, co czyni go potencjalnym problemem bezpieczeństwa. SQL injection zwykle występuje, gdy poprosisz użytkownika o wprowadzenie, a zamiast oczekiwanego wejścia, takiego jak nazwa użytkownika, użytkownik wprowadza polecenie SQL, które będzie uruchamiane w bazie danych, powodując nieoczekiwane zdarzenia. SQL injection może powodować poważne problemy i jest powszechną techniką hakerską.

wydajność może być kolejnym potencjalnym problemem, ponieważ plan wykonania nie może być buforowany. Sposobem obejścia tego problemu jest użycie procedur składowanych i polecenia sp_executesql (przynajmniej w Microsoft SQL server)., Dynamiczne instrukcje SQL mogą stać się skomplikowane szybko i mogą być trudne do debugowania, co zastrasza początkujących.

myśli końcowe

chociaż może to wydawać się trudne lub żmudne na początku, dynamiczny SQL jest potężną techniką, ponieważ pozwala na konstruowanie i wykonywanie poleceń SQL w czasie wykonywania., Dynamiczny SQL składa się z trzech komponentów:

1. deklaruj zmienne
2. Konstruuj ciąg SQL
3. wykonuj SQL

mogą wystąpić pewne minusy, takie jak utrudnianie debugowania kodu, ale elastyczność, jaką daje użytkownikom, może przeważyć nad problemami, zwłaszcza gdy jest odpowiednio zaimplementowana.

Jeśli chcesz dowiedzieć się więcej o SQL lub Data Science, sprawdź moje inne tutoriale: