SAP SQL Anywhere で MERGE 文を使用する (過去のブログ記事より)
このページは、以下の英語ページの抄訳です。最新の情報については、英語ページを参照してください。
この記事のオリジナルは、Glenn Paulley が sybase.com に 2009 年 4 月に掲載したものです。その中で、Glenn は SQL Anywhere における MERGE 文について解説しています。MERGE 文に関する最新の情報はこちらを参照ください。
MERGE 文は、SQL:2003 ANSI/ISO の F312 の機能として登場しました。MERGE は、行のセットを挿入する必要があり、かつ、クリーニングまたは他の ETL (Extract-Transform-Load) プロセスが必要な時に便利です。MERGE を使用すると、1 つの文で新規データ挿入と既存の行の更新をセットで対応できる洗練されたロジックを実装することができます。
このブログ記事では、MERGE 文の機能について簡単に概要を説明するとともに、SQL:2003 と SQL:2008 間でどのような変更があったのか、さらに SQL Anywhere でサポートされている拡張機能について説明します。
ここでは、私の同僚である Anil Goel が 2008 年 8月にラスベガスで開催された Sybase Techwave においてプレゼンした MERGE 文とマテリアライズドビューに関する資料を借りて説明したいと思います。
例
テーブルに行を挿入する処理を行う場合、「INSERT-else-UPDATE」処理を実行する必要がある場合があります。これこそが、MERGE 文を使用する理由です。さらには以下のようなメリットがあります。
INSERT の代替アクションを設定することができます。例えば
• 特定の行において条件次第で INSERT をスキップ、または UPDATE を行う
• 既存の行を DELETE する
などのように任意の条件を設定し、代替アクションを行わせることができます。
空ではないテーブルTがあり、以下のように入力行データ S を処理するとします。
- FOR each input row in S DO
- IF EXISTING THEN // もし、行Sと同一の主キーの行が既にテーブルTに存在する場合
- IF condition-1 THEN SKIP // 条件 1 にマッチした場合は処理をスキップ
- ELSE IF condition-2 THEN ERROR //条件 2 にマッチした場合はエラーを返す
- ELSE IF condition-3 THEN DELETE FROM T //条件 3 にマッチした場合はその行をテーブル T から削除
- ELSE UPDATE T //条件 1 ～ 3 にマッチしなかったらその行を更新する
- ELSE // 行 S と同一の行がテーブル T に存在しなかった場合
- IF condition-4 THEN SKIP //条件 4 にマッチした場合は処理をスキップ
- ELSE IF condition-5 THEN ERROR //条件 5 にマッチした場合はエラーを返す
- ELSE INSERT INTO T //条件 4 ～ 5 にマッチしなかったら行 S を挿入する
- END
このロジック全体を、以下のような 1 つの MERGE 文として実行することが可能です。以下の例では、テーブル T にクライアントマシン上のテキストファイルを読み込み、それに含まれる行をマージしています。これは、version 11 の OPENSTRING 句を使用することでテーブルのように扱うことができます。
- MERGE INTO T
- USING (SELECT * FROM OPENSTRING( FILE ‘/usr/paulley/techwave/2008/data1.txt’)
- WITH( id INT, qty INT, cmt LONG VARCHAR) S) S //CSVファイルを読み込み、区切り文字毎にid,qty,cmtという変数で以下扱えるようにします。行全体はSという変数となりますので各カラムにはS.カラム名、例えばidカラムならS.idという形でアクセスする事ができます。
- ON PRIMARY KEY
- WHEN NOT MATCHED AND S.cmt = ‘ignore’ THEN SKIP //主キーの重複が発生せず、テキストファイル上の行のcmtカラムに相当するデータがignoreではなかった場合はこの行をスキップ
- WHEN NOT MATCHED AND S.qty < 0 THEN RAISERROR //主キーの重複が発生せず、テキストファイル上の行のqtyカラムに相当するデータが０以下ではなかった場合はエラーを返す
- WHEN NOT MATCHED AND S.qty = 0 THEN INSERT VALUES( S.id, 1 ) //主キーの重複が発生せず、テキストファイル上の行のqtyカラムに相当するデータが０の場合はテーブルTに( S.id, 1 ) を挿入する
- WHEN NOT MATCHED THEN INSERT VALUES( S.id, S.qty ) //主キーの重複が発生せず、上記の３条件に合致しなかった場合はテーブルTに( S.id, S.qty )を挿入する
- WHEN MATCHED AND S.cmt = ‘ignore’ THEN SKIP
- WHEN MATCHED AND S.cmt = ‘new’ THEN RAISERROR //主キーの重複が発生し、テキストファイル上の行のcmtカラムに相当する場所のデータがignoreの場合はこの行をスキップ
- WHEN MATCHED AND T.qty + S.qty
- …
MERGE 文 – 概要
SQL:2003 で定義されている MERGE 文は、最大でも WHEN MATCHED 1つと WHEN NOT MATCHED 句1つしか許していません。
SQL:2003 MERGE 文は、以下のとおりです。
• target-object の各行で、ソースとターゲットから ‘matching’ # 行を見つけるために、MERGE サーチ条件が source-object の各行を TRUE と評価するかどうか決める
• マッチする行が発見された場合には、エラーを生成する。
• 見つかった場合には、マッチング行 1 行を行のセットに追加し、存在する場合 WHEN MATCHED 句で処理する。
• 存在する場合、WHEN NOT MATCHED 句が target-object 行にマッチできない source-object の行を処理する。
SQL:2008 の Feature F313 では、複数の WHEN [NOT] MATCHED 句をオプショナルのサーチ条件で許可しています。この場合、WHEN 句の順番が問題になります。実行されるサーチ条件を満足させるのは、最初の WHEN 句のマージアクションです。しかしながら、Feature F313 でも、標準の MERGE 文はそれほどフレキシブルではありません。WHEN MATCHED 句が実行できる唯一のオペレーションは、UPDATE であり、WHEN NOT MATCHED 句が実行できる唯一のオペレーションは INSERT です。SQL Anywhere 11 では、MERGE 文の機能をいくつかの方法で拡張しています。それらのいくつかは、2002年の SQL Anywhere version 8 で実装された、それまでの INSERT ... ON EXISTING 文を引き継いでいます。
• OPENSTRING 構造 を使用して、クライアントマシン上のテキストファイルのデータを MERGE に使用できるように拡張しています。
• 明示的なカラムリストを使用して入力行データのカラムとMARGE 先のテーブルカラムのマッチングを変更できるように拡張しています。
• WITH AUTO NAME 構文を使用してカラム順ではなくカラム名によるカラムのマッチングができるように拡張しています。
• MERGE サーチ条件は、プライマリーキー重複に基づく場合、省略形として ON PRIMARY KEY が使用可能です。
• WHEN MATCHED で実行できるアクションが拡張されています。
• 既存の行を DELETE
• RAISERROR [ error-number ]
•SKIP
• 既存の行を UPDATE [ DEFAULTS { ON | OFF }
• WHEN NOT MATCHED で実行できるアクションが拡張されています。
• 入力行データを INSERT する
•RAISERROR[ error-number ]
• SKIP
ターゲットテーブルの INSERT、DELETE、UPDATE 、トリガーの構文と並び、MERGE 文の構文の詳細は、SQL Anywhere のマニュアルに記載されています。
SQL Anywhere 11 でサポートされている MERGE 文を BNF で記述すると以下のとおりです。
- merge ::= “MERGE” “INTO” merge_into_tabterm merge_using
- simple_tabterm merge_on_clause merge_op_list
- merge_using ::= “USING” | “USING WITH AUTO NAME”
- merge_into_tabterm ::= ( tabref | “(“ query_expr “)” as identifier
- | “(” “WITH” with_list query_expr “)” as identifier ) derived_collist
- merge_on_clause ::= “ON” merge_search_cond
- merge_search_cond ::= searchcond
- | “PRIMARY” “KEY”
- | “INDEX” indexname
- merge_op_list ::= merge_op { “,” merge_op }
- merge_op ::= “WHEN MATCHED” “THEN” merge_matched_op
- | “WHEN MATCHED” “AND” searchcond “THEN”
- merge_matched_op
- | “WHEN NOT” “MATCHED” “THEN”
- merge_not_matched_op
- | “WHEN NOT” “MATCHED” “AND” searchcond
- “THEN” merge_not_matched_op
- merge_common_op ::= “SKIP”
- | “RAISERROR”
- | “RAISERROR” integer_or_var
- merge_matched_op ::= merge_common_op
- | “UPDATE SET” setlist
- | “UPDATE”
- | “UPDATE” “DEFAULTS” “ON”
- | “UPDATE” “DEFAULTS” “OFF”
- | “DELETE”
- merge_not_matched_op ::= merge_common_op
- | “INSERT” “VALUES” “(“ ins_vallist “)”
- | “INSERT” “(“ inscollist “)” “VALUES” “(“ ins_vallist “)”
- | “INSERT”
- ins_vallist ::= { ins_valitem “,” } ins_valitem
- ins_valitem ::= “DEFAULT” | expression
- inscollist ::= { inscolname “,” } inscolname
- inscolname ::= identifier [ “.” identifier ]
Techwave 2008 での我々のプレゼンに上記のコンテンツの大半を入れてくれた Anil Goel に感謝します。
===
SAP SQL Anywhere に関する詳細情報は、SAP SQL Anywhere Communityページ<英語> を参照してください。
上記のコミュニティーに掲載されている技術情報は、順次SQL Anywhere 日本語コミュニティ
に掲載しています。
SQL Anywhere に関してはまずはこちらをご参照ください。無期限でご利用いただける無償の Developers Edition もこちらからダウンロードが可能です。
SQL Anywhere に関して技術的な質問のある方はコミュニティに登録し、
「Ask a Question」機能をご利用ください。
Language には「Japanese」、
Primary Tag には「SAP SQL Anywhere」を選択
User Tagに「sql anywhere」「sql anywhere Japanese question」
を入力してください。
不具合につきましては、サポート契約者様専用の問い合わせ方法にてお問い合わせください。
======================
ご購入に関するお問い合わせ
こちらよりお問い合わせください。