5分でより良いSQLを書く–動的SQLの紹介

admin

2014年頃に構造化クエリ言語（SQL）に慣れ始めたとき、私は例をインターネットで精練したことを覚えて 私は人々が”これは動的SQLを使用しています”または”そのスクリプトは動的です。”私は例を見ましたが、私は新しかったので、それを”動的”にするコンポーネントを本当に理解していませんでした。 この分野でより多くの経験を積むにつれて、動的SQLを構成する三つのコンポーネントだけでなく、さまざまなユースケースに適用する方法も学びました。, この簡単な紹介は、SQL初心者や他の誰かが動的SQLのコンポーネントをよりよく理解できるように設計されています。

例のデータ

興味がある場合は、Kaggleで利用可能なオプション市場の取引データを使用しています。

動的SQLとは何ですか？

そのアプリケーションは複雑になる可能性がありますが、動的SQLの定義は実際には簡単です。

動的SQLは実行時にビルドするSQL文です。

これにより、汎用クエリを構築し、変数を使用してオンザフライで実行する必要があるステートメントを構築できます。, 私は以下のスーパー基本的な動的SQLのこの例を分解します：

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

動的SQLの反対は、実行時に変更されないSQLステートメントである静的SQLです。 たとえば、

SELECT * FROM orderFlow

違いに注意してください？ 静SQLは運用実行コマンドも用の変数.

動的SQLを書くための三つのステップ

動的SQLを書きたい場合は、次の三つのコンポーネントを覚えておいてください。

  1. 変数を宣言
    変数をSQL文のビルディングブロックとして使用することを考えてください。, この例では、dynamic_sqlという変数を宣言し、それにデータ型nvarchar(max)を与えました。 私はmaxを使ってSQL文を入力するためのスペースを最も多く与えます。
DECLARE @dynamic_sql NVARCHAR(max)

2. Sql文字列を構築します
変数dynamic_sqlに必要なSQLを入力できるという事実は、この例の動的SQLを最初に作るものの一部です！ テーブルorderFlowからすべての結果を返したいので、変数をエラーなしで実行できる単純なselectステートメントに設定します。

select @dynamic_sql = 'select * from orderFlow '

3., Execute SQL statement
ステップツーでビルドされたステートメントを実行する必要があります。 EXECコマンドは通常、ストアドプロシージャを実行するために使用されますが、変数dynamic_sqlに格納されているように、文字列として格納されている正当なSQL

EXEC(@dynamic_sql)

ユーザーが生のSQLステートメントを入力できるテキストフィールドを持つユーザーインターフェイスを使用して、ユーザーがrunをクリックしたときにそのステートメントを実行することを想像してみてください。 私はBusiness Intelligence製品で同様の機能を使用しました！, 動的SQLをいくつか知っているので、それが舞台裏でどのように機能しているのか想像するのは簡単です。

想像ユーザーインターフェイス

おめでとうございます！ なお、現在知らの3コンポーネントの動的SQL!, 例：

ユーザーがデータを操作できるようにするユーザーインターフェイスを作成しているとしますが、純粋なSQLを書く代わりに、2つのドロップリストを使用してステートメントを構築させます。

imaginary ui2

舞台裏では、ストアドプロシージャと動的sqlを使用して、uiで構築されたsqlステートメントを実行します。

ストアドプロシージャは、ユーザー入力を使用し, 以降、変数で定義されるなど、ユーザインタフェースモックアップの手続きるだけの構築に文字列を実行します。 ユーザーが手順を実行するときにスペースを入力することを忘れないように、スペースを追加しました。, たとえば、

Microsoft SQL Server Management Studiosでストアドプロシージャを実行している

ユースケース2:

ストアドプロシージャを使用してorderflowテーブルに特定のストックシンボルを照会するとします。

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

変数はユースケース2のwhere句で使用されていることに注意してください。 そうすれば、静的クエリを何度も使用することなく、株式シンボルを入力して結果を得ることができます。,

Running stored procedure in Microsoft SQL Server Management Studios

Results of sp_example2

The Downsides with Dynamic

There can be some downsides to using dynamic SQL., できやすいSQLインジェクションでは、潜在的なセキュリティ問題です。 SQLインジェクションは通常、ユーザーに入力を要求し、ユーザー名のような予期される入力の代わりに、ユーザーがデータベース上で実行されるSQLステートメントを入力 SQLインジェクショ

実行計画をキャッシュできないため、パフォーマンスが別の潜在的な懸念になる可能性があります。 これを回避する方法は、ストアドプロシージャとsp_executesqlコマンド（少なくともMicrosoft sql serverでは）を使用することです。, 動的SQLステートメントは、高速に複雑になる可能性があり、デバッグが困難なため、初心者にとっては威圧的です。

最終的な考え

最初は難しいか退屈に見えるかもしれませんが、動的SQLは実行時にSQL文を構築および実行できるため、強力な技術です。, 動的SQLは三つのコンポーネントで構成されています。

  1. 変数を宣言する
  2. Sql文字列を構築する
  3. SQLを実行する

コードをデバッグするのが難しくなるなど、いくつかの欠点がありますが、ユーザーに与える柔軟性は、特に適切に実装された場合の懸念を上回る可能性があります。

SQLまたはデータサイエンスについての詳細を学ぶことに興味がある場合は、私の他のチュートリアルをチェックしてください：

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です