エンタープライズ コンソーシアム 技 術 部 会 WG#2 SQL 移 行 調 査 編 製 作 者 担 当 企 業 名 SRA OSS, Inc. 日 本 支 社 2013 Enterprise Consortium
改 訂 履 歴 版 改 訂 日 変 更 内 容 1.0 2013/04/22 初 版 ライセンス 本 作 品 は CC-BY ライセンスによって 許 諾 されています ライセンスの 内 容 を 知 りたい 方 は http://creativecommons.org/licenses/by/2.1/jp/ でご 確 認 ください 文 書 の 内 容 表 記 に 関 する 誤 り ご 要 望 感 想 等 につきましては PGECons のサイトを 通 じてお 寄 せいただきます ようお 願 いいたします サイト URL https://www.pgecons.org/contact/ Microsoft Windows Server 米 国 Microsoft Corporation の 米 国 及 びその 他 の 国 における 登 録 商 標 または 商 標 です は Corporation 及 びその 子 会 社 関 連 会 社 の 米 国 及 びその 他 の 国 における 登 録 商 標 です 文 中 の 社 名 商 品 名 等 は 各 社 の 商 標 または 登 録 商 標 である 場 合 があります は Community Association of Canada のカナダにおける 登 録 商 標 およびその 他 の 国 における 商 標 です その 他 本 資 料 に 記 載 されている 社 名 及 び 商 品 名 はそれぞれ 各 社 が 商 標 または 登 録 商 標 として 使 用 している 場 合 があります 2/18 2013 Enterprise Consortium
はじめに 本 資 料 の 目 的 本 資 料 は 異 種 DBMS から へ SQL を 移 行 する 作 業 の 難 易 度 およびボリュームの 事 前 判 断 と 実 際 に 書 き 換 えを 行 う 際 の 参 考 資 料 として 利 用 されることを 想 定 しています 本 資 料 で 記 載 する 範 囲 本 資 料 では 移 行 元 の 異 種 DBMS として Database および Microsoft を 想 定 し これらの DBMS から へSQLを 移 行 する 際 に 書 き 換 えが 必 要 である 箇 所 とその 書 き 換 え 方 針 について DML とトランザク ション 処 理 を 中 心 に 記 載 します スキーマ ストアドプロシージャ 組 み 込 み 関 数 に 関 する SQL については 本 資 料 では 取 り 扱 っていません これらに 関 しては それぞれ スキーマ 移 行 調 査 編 ストアドプロシージャ 移 行 調 査 編 組 み 込 み 関 数 移 行 調 査 編 を 参 照 してください 本 資 料 で 扱 う 用 語 の 定 義 資 料 で 記 述 する 用 語 について 以 下 に 定 義 します 表 1: 用 語 定 義 用 語 意 味 1 DBMS データベース 管 理 システムを 指 します ここでは および 異 種 DBMS の 総 称 と して 利 用 します 2 異 種 DBMS ではない データベース 管 理 システムを 指 します 本 資 料 では Database Microsoft が 該 当 します 3 データベース 管 理 システムの Database を 指 します 4 データベース 管 理 システムの Microsoft を 指 します 本 資 料 で 扱 う DBMS およびツール 本 書 では 以 下 の DBMS を 前 提 にした 調 査 結 果 を 記 載 します 表 2: 本 書 で 扱 う DBMS DBMS 名 称 バージョン 9.2.1 Database 11gR2 11.2.0.2.0 Microsoft 2008 R2 3/18 2013 Enterprise Consortium
目 次 1.SQL 移 行 調 査 の 概 要...5 1.1. 対 象 とする SQL について...5 1.2.SQL の 差 異 と 書 き 換 え 方 針 について...5 1.3. 標 準 SQL について...5 2. から への 移 行...6 2.1.SELECT 文... 6 2.2. 更 新 系... 9 2.3.その 他 の 書 き 換 え...10 2.4.トランザクション...10 3. から への 移 行...12 3.1.SELECT 文... 12 3.2. 更 新 系... 12 3.3.その 他 の 書 き 換 え...14 3.4.トランザクション...15 4. 別 紙 一 覧... 17 4/18 2013 Enterprise Consortium
1. SQL 移 行 調 査 の 概 要 本 資 料 では Database( 以 下 と 呼 称 )および Microsoft ( 以 下 と 呼 称 )から へアプリケーションを 移 行 する 際 に 問 題 となるSQLの 差 異 とその 書 き 換 え 方 針 について 記 載 します 本 章 では 調 査 内 容 の 概 要 について 説 明 します 1.1. 対 象 とする SQL について 本 資 料 では アプリケーションプログラムの 中 で 使 用 される 頻 度 が 高 いと 思 われる DML(SELECT, INSERT, UPDATE, DELETE など) およびトランザクション 処 理 関 連 SQL を 対 象 としています その 他 のスキーマ ストアドプロ シージャに 関 する SQL と 組 み 込 み 関 数 の 差 異 および 移 行 方 針 については 本 資 料 では 取 り 扱 っていません これらについ ては それぞれ スキーマ 移 行 調 査 編 ストアドプロシージャ 移 行 調 査 編 組 み 込 み 関 数 移 行 調 査 編 を 参 照 してく ださい 1.2. SQL の 差 異 と 書 き 換 え 方 針 について 異 種 DBMS と では SQL の 構 文 や 仕 様 に 違 いがあるため 移 行 元 の SQL 文 が 移 行 先 の では 動 作 しない 場 合 があります 本 資 料 では そのような SQL を 書 き 換 え 移 行 先 の にて 同 等 の 機 能 で 動 作 させる 際 の 参 考 となる 方 針 を 記 載 しています ただし これは 書 き 換 えの 前 後 で 完 全 に 同 じ 動 作 を 保 証 するものではあ りません また 本 資 料 は 異 種 DBMS と の 間 に 生 じる 全 ての SQL の 差 異 および 書 き 換 え 方 針 を 網 羅 した ものではなく 本 資 料 の 内 容 以 外 の 書 き 換 えが 必 要 になるケースも 存 在 します さらに ケースによっては SQL 文 の 書 き 換 えのみではなく SQL 文 を 発 行 するアプリケーション 側 での 対 応 が 適 切 な 場 合 もあるかもしれません 実 際 の 移 行 の 際 はこの 可 能 性 も 含 めて 検 討 する 必 要 があります 本 資 料 で 対 象 とする SQL の,, の3つの DBMS における 差 異 は 別 紙 SQL 差 異 表 にまとめましたので 参 照 してください 各 SQL 機 能 への 対 応 を で 非 対 応 を で 表 し 備 考 欄 にその 他 の 参 考 となる 情 報 を 記 載 してあります 1.3. 標 準 SQL について 標 準 SQL とは SQL の 国 際 標 準 規 格 であり 公 式 な 名 称 は ISO/IEC 9075 "Database Language SQL"です 2013 年 3 月 現 在 2011 年 に 改 定 されたものが 最 新 版 であり これは SQL:2011 と 呼 ばれています の 開 発 では 最 新 の 標 準 SQL に 準 拠 しようとしており 実 際 に 9.2 は SQL:2011 の 主 な 機 能 のほとんどをサポートしています SQL:2011 の 機 能 の 内 何 がサポートされており 何 がサポートされていないのかに ついては のドキュメントから 知 ることができます また 標 準 SQL に 対 して が 独 自 の 拡 張 を 加 えた 機 能 についてもドキュメントの SQL リファレンスに 記 載 されています 以 後 本 資 料 で 標 準 SQL といった 場 合 には SQL:2011 のことを 指 します 別 紙 SQL 差 異 表 には 各 SQL 機 能 が 標 準 SQL に 準 拠 しているかの 情 報 を 付 与 しています 本 資 料 および SQL 差 異 表 の 作 成 にあたり 各 機 能 が 標 準 SQL に 準 拠 しているかどうかの 判 断 は のドキュメントを 参 考 としまし た 5/18 2013 Enterprise Consortium
2. から への 移 行 本 章 では から へ 移 行 の 際 に 生 じる SQL の 書 き 換 え 方 針 を 紹 介 します 2.1. SELECT 文 2.1.1. 独 自 形 式 の 外 部 結 合 には 独 自 の 外 部 結 合 演 算 子 (+) が 存 在 します この 演 算 子 を 使 った 結 合 は 標 準 SQL 準 拠 の LEFT (RIGHT) OUTER JOIN 構 文 を 用 いて 以 下 のように 書 き 換 えます テーブル foo, bar の 右 外 部 結 合 SELECT * FROM foo, bar WHERE foo.id = bar.id (+) SELECT * FROM foo LEFT OUTER JOIN bar ON foo.id = bar.id テーブル foo, bar の 左 外 部 結 合 SELECT * FROM foo, bar WHERE foo.id (+) = bar.id SELECT * FROM foo RIGHT OUTER JOIN bar ON foo.id = bar.id また 以 下 のように 外 部 結 合 演 算 子 (+) と UNION 句 を 併 用 して 完 全 外 部 結 合 を 行 っている 場 合 があります こ のようなクエリは FULL OUTER JOIN を 用 いて 書 き 換 えます テーブル foo, bar の 完 全 外 部 結 合 SELECT * FROM foo, bar WHERE foo.id = bar.id (+) UNION SELECT * FROM foo, bar WHERE foo.id (+) = bar.id SELECT * FROM foo FULL OUTER JOIN bar ON foo.id = bar.id 2.1.2. DUAL 表 では SELECT 文 の FROM 句 を 省 略 できないため 表 を 必 要 としない 処 理 の 場 合 には DUAL 表 が 使 わ れます では FROM 句 が 省 略 可 能 であるので DUAL 表 は 存 在 しません SELECT 文 より FROM DUAL を 取 り 除 く 必 要 があります 現 在 日 時 の 表 示 SELECT current_timestamp FROM DUAL SELECT current_timestamp あるいは 該 当 する 全 ての SELECT 文 を 書 き 換 える 代 わりに 以 下 のような1 件 のレコードのみを 持 つ dual テーブ ルを 側 で 定 義 しておく 方 法 もあります dual テーブルの 定 義 CREATE TABLE dual (dummy VARCHAR(1)); INSERT INTO dual VALUES ('X'); SELECT current_timestamp FROM DUAL; 2.1.3. ROWNUM 擬 似 列 では 検 索 結 果 の 行 番 号 を 取 得 するのに ROWNUM 擬 似 列 を 用 いることができます で 検 索 結 果 の 表 示 件 数 を 制 限 する 場 合 には これを 用 いるのが 一 般 的 です しかし ROWNUM 擬 似 列 は 固 有 の 機 能 であり には 存 在 しません 6/18 2013 Enterprise Consortium
では 標 準 SQL の Window 関 数 の1つである row_number()により 行 番 号 の 取 得 が 可 能 です こ れを 用 いると ROWNUM 擬 似 列 を 用 いた 表 示 件 数 制 限 は 以 下 のように 書 き 換 えることができます tbl テーブルのデータを id で 昇 順 ソートし 最 初 から 10 行 のレコードを 取 得 する SELECT * FROM SELECT * FROM (SELECT * FROM tbl ORDER BY id) (SELECT row_number() OVER (ORDER BY id) WHERE ROWNUM <= 10 AS rownum, id FROM tbl) AS t WHERE rownum <= 10 独 自 の 機 能 である LIMIT OFFSET を 用 いると より 簡 素 な SQL 文 で 表 示 件 数 を 制 御 することも 可 能 です tbl テーブルのデータを id で 昇 順 ソートし 11 番 目 から 15 番 目 までの 5 行 のレコードを 取 得 する(LIMIT) SELECT * FROM tbl ORDER BY id LIMIT 5 OFFSET 10 また これと 同 じ 処 理 は 標 準 SQL 準 拠 である FETCH 句 を 用 いて 書 き 換 えることもできます 標 準 SQL への 準 拠 を 重 視 する 場 合 は FETCH 句 を 用 いるのがよいでしょう tbl テーブルのデータを id で 昇 順 ソートし 11 番 目 から 15 番 目 までの 5 行 のレコードを 取 得 する(FETCH) SELECT * FROM tbl ORDER BY id OFFSET 10 ROWS FETCH FIRST 5 ROWS ONLY 2.1.4. 階 層 型 問 い 合 わせ 独 自 の 機 能 である 階 層 問 い 合 わせを 用 いると 階 層 構 造 となっているデータから 階 層 順 にデータを 取 り 出 すことができます 階 層 構 造 データとは 例 えば 表 2.1 に 示 した staff テーブルのような 上 司 と 部 下 の 関 係 です こ の 構 造 をツリーで 表 すと 図 2.1 のようになります 表 2.1: staff テーブル ID NAME MANAGER_ID John 1 John 2 Paul 1 Paul Anna 3 Anna 1 4 Peter 2 Peter Ken Bob 5 Steve 4 6 Ken 3 Steve 7 Bob 3 図 2.1: staff テーブルの 階 層 構 造 の 階 層 問 い 合 わせでは START BY 句 でルートとなるデータを CONNECT BY 句 でノード 間 の 親 子 関 係 を 指 定 すると ルートから 辿 れるデータを 深 さ 優 先 探 索 の 順 に 取 り 出 すことができます でこれと 同 様 の 機 能 は 標 準 SQL の WITH 句 を 使 用 した 再 帰 的 問 い 合 わせを 用 いることで 実 現 可 能 です ただし 再 帰 問 い 合 わせを 用 いた 場 合 には データ 取 り 出 し 順 序 が とは 異 なって 幅 優 先 探 索 となることに 注 意 してくださ い 7/18 2013 Enterprise Consortium
staff テーブルに 対 する 階 層 問 い 合 わせ SELECT name FROM staff START WITH name = 'John' CONNECT BY manager_id = PRIOR id WITH RECURSIVE rec (id, name, manager_id) AS (SELECT id, name, manager_id FROM staff WHERE name = 'John' UNION ALL 結 果 : 深 さ 優 先 順 序 1. John 2. Paul 3. Peter 2 Paul 4. Steve 5. Anna 3 Peter 6. Ken 7. Bob 4 Steve 1 John 5 Anna 6 Ken 7 Bob SELECT staff.id, staff.name, staff.manager_id FROM staff, rec AS prior WHERE staff.manager_id = prior.id ) SELECT name FROM rec 結 果 : 幅 優 先 順 序 1. John 1 John 2. Paul 3. Anna 2 Paul 3 Anna 4. Peter 5. Ken 4 Peter 5 Ken 6 Bob 6. Bob 7. Steve 7 Steve また 標 準 SQL 準 拠 ではありませんが connectby 関 数 を 用 いると の CONNECT BY と 同 様 に 深 さ 優 先 順 序 の 階 層 問 い 合 わせが 可 能 です connectby 関 数 を 使 用 するには の 追 加 モジュールの contrib/tablefunc をデータベースにインストールする 必 要 があります staff テーブルに 対 する 階 層 問 い 合 わせ(connectby 関 数 ) SELECT name FROM connectby('staff', 'id', 'manager_id', 'id', '1', 0) AS t (id int, manager_id int, lavel int, pos int) JOIN staff ON staff.id=t.id ORDER BY pos 2.1.5. UNIQUE による 重 複 行 の 除 去 では 検 索 結 果 から 重 複 行 を 取 り 除 く 際 に UNIQUE を 使 うことができます これは 標 準 SQL の DISTINCT と 同 じ 働 きをします ではこのような UNIQUE の 使 い 方 はできないので DISTINCT に 書 き 直 します 重 複 する 行 の 除 去 SELECT UNIQUE * FROM tbl SELECT DISTINCT * FROM tbl 2.1.6. MINUS 演 算 子 による 差 集 合 計 算 で 検 索 結 果 の 差 集 合 を 求 める 場 合 に MINUS 演 算 子 を 使 いますが この 演 算 子 は 独 自 のもので には 存 在 しません は MINUS 演 算 子 と 同 等 の 機 能 を 持 つ EXCEPT 演 算 子 に 対 応 して います なお EXCEPT 演 算 子 は SQL 標 準 に 従 ったものです 2.1.7. FROM 句 中 のサブクエリの 別 名 ではサブクエリには 別 名 は 必 須 ではありませんが では FROM 句 の 中 のサブクエリには 別 名 を 付 ける 必 要 があります FROM 句 の 中 のサブクエリの 別 名 は 必 須 SELECT * FROM (SELECT * FROM tbl) AS sub 8/18 2013 Enterprise Consortium
2.2. 更 新 系 2.2.1. MERGE 文 MERGE 文 はテーブルに 既 存 の 行 がある 場 合 には 更 新 を ない 場 合 には 新 規 に 挿 入 を 行 う SQL 文 です 標 準 SQL に 従 ったものですが はこれに 対 応 していません では WITH 句 の 中 で UPDATE 文 を 用 いることにより これと 同 等 の 機 能 を 実 現 することができます なお 更 新 を 含 む WITH 句 は 独 自 の 拡 張 です diff テーブルの 値 を master テーブルにマージする (master テーブルに ID が 一 致 する 行 があったら diff.val を master.val に 足 し 加 える ID が 一 致 する 行 がない 場 合 には diff の 内 容 を master に 新 規 登 録 する ) MERGE INTO master USING diff ON master.id = diff.id WHEN MATCHED THEN UPDATE SET master.val = master.val + diff.val WHEN NOT MATCHED THEN INSERT VALUES (diff.id, diff.val) WITH inpt AS (SELECT * FROM diff), updt AS (UPDATE master.val = master.val + inpt.val FROM inpt WHERE master.id = inpt.id RETURNING master.id) INSERT INTO master (SELECT * FROM inpt WHERE id NOT IN (SELECT id FROM updt)) 2.2.2. ビューに 対 する 更 新 ではビューに 対 する 更 新 が 可 能 ですが ではビューに 対 して 更 新 することはできません ただ し RULE もしくはトリガーと 組 み 合 わせることで 更 新 可 能 なビューと 同 等 な 機 能 を 実 現 することが 可 能 です その 方 法 は スキーマ 移 行 調 査 編 の 第 5 章 で 述 べられていますので そちらを 参 照 してください 2.2.3. マルチテーブル INSERT の INSERT 文 では 複 数 のテーブルに 対 してデータを 挿 入 することが 可 能 です しかし の INSERT 文 にはそのような 機 能 はなく 個 々のテーブルに 毎 に INSERT 文 を 実 行 する 必 要 があります ただし のマルチテーブル INSERT を 利 用 すると1つのテーブルに 複 数 行 を 挿 入 することが 可 能 であり そ のようなクエリは 標 準 SQL に 従 い 以 下 のように 書 き 換 え 可 能 です テーブル tbl にレコードを 3 行 挿 入 する INSERT ALL INTO tbl VALUES (1, 'one') INTO tbl VALUES (2, 'two') INTO tbl VALUES (3, 'three') SELECT * FROM DUAL INSERT INTO tbl VALUES (1, 'one'), (2, 'two'), (3, 'three') 2.2.4. DELETE 文 の FROM の DELETE 文 では FROM キーワードが 省 略 可 能 ですが では 省 略 することはできません もし FROM が 省 略 されている 場 合 には 書 き 足 す 必 要 があります DELETE 文 の FROM は 省 略 できない DELETE tbl WHERE id = 2 DELETE FROM tbl WHERE id = 2 9/18 2013 Enterprise Consortium
2.3. その 他 の 書 き 換 え 2.3.1. NULL と 空 文 字 列 では 空 の 文 字 列 は NULL と 同 値 として 扱 われますが これは 標 準 SQL に 準 拠 したものではありません においてはこれらは 区 別 されます 文 字 列 の 結 合 や 検 索 をふくむクエリの 実 行 結 果 が 移 行 の 前 後 で 異 なる 場 合 があります また ではテーブルに 空 文 字 列 を 挿 入 すると NULL に 自 動 変 換 されますが では 変 換 されず 空 文 字 列 のまま 格 納 されます で と 同 じように 空 文 字 列 を NULL とみなさせるには ISNULL 関 数 を 使 って 変 換 する 方 法 が あります 空 文 字 列 を NULL とみなす 検 索 SELECT * FROM staff WHERE ISNULL(name, '') IS NOT NULL 2.3.2. REGEXP_LIKE 条 件 による 正 規 表 現 マッチング は REGEXP_LIKE 条 件 を 使 って 正 規 表 現 マッチングを 行 います この 条 件 は POSIX 正 規 表 現 規 格 に 準 拠 しています で POSIX 正 規 表 現 のマッチングを 行 う 場 合 には 正 規 表 現 マッチ 演 算 子 ( 表 2.2)を 用 います 演 算 子 表 2.2: 正 規 表 現 マッチ 演 算 子 説 明 例 ( 結 果 はすべて 真 ) ~ 正 規 表 現 に 一 致 大 文 字 小 文 字 の 区 別 あり 'thomas' ~ '.*thomas.*' ~* 正 規 表 現 に 一 致 大 文 字 小 文 字 の 区 別 なし 'thomas' ~* '.*Thomas.*'!~ 正 規 表 現 に 一 致 しない 大 文 字 小 文 字 の 区 別 あり 'thomas'!~ '.*Thomas.*'!~* 正 規 表 現 に 一 致 しない 大 文 字 小 文 字 の 区 別 なし 'thomas'!~* '.*vadim.*' POSIX 正 規 表 現 を 使 って p で 始 まるか e が2 回 現 れる 名 前 を 検 索 SELECT * FROM staff SELECT * FROM staff WHERE REGEXP_LIKE(lower(name), '^p (e.*){2}') WHERE name ~* '^p (e.*){2}' 2.3.3. 比 較 演 算 子 ^= では 不 等 を 表 す 演 算 子 に ^= が 使 用 できますが この 演 算 子 は には 存 在 しません <> 演 算 子 で 置 き 換 えてください 2.4. トランザクション と におけるトランザクション 処 理 に 違 いがあり 移 行 の 際 には 注 意 が 必 要 です 本 節 ではその 違 いについて 概 説 します 2.4.1. トランザクションの 開 始 と 自 動 コミット ではトランザクションは SQL の 実 行 によって 暗 黙 的 に 開 始 されます 一 方 では 標 準 SQL 準 拠 の START TRANSACTION 文 か あるいは 独 自 の BEGIN 文 を 実 行 して 明 示 的 にトランザクショ ンを 開 始 する 必 要 があります ではトランザクションを 開 始 せずに 発 行 されたコマンドは 暗 黙 的 にコ ミットされます これは 自 動 コミット と 呼 ばれています 2.4.2. DDL の 暗 黙 コミットとロールバック では CREATE TABLE などの DDL 実 行 の 前 後 で 自 動 的 に COMMIT が 発 行 されます DDL が 実 行 され た 時 点 で DDL 実 行 前 に 成 功 したコマンド 全 てと DDL の 結 果 がコミットされます そのため DDL をロールバック 10/18 2013 Enterprise Consortium
することはできません 一 方 では DDL が 自 動 的 にコミットを 発 行 することはなく トランザクション 中 で 発 行 された DDL はロールバックすることが 可 能 です 2.4.3. トランザクションの 終 了 とトランザクション 中 のエラー COMMIT 文 あるいは ROLLBACK 文 の 実 行 でトランザクションが 終 了 します また では 前 節 で 説 明 し た 理 由 により DDL の 実 行 によってもトランザクションが 終 了 します なお には 標 準 SQL 準 拠 の COMMIT 文 ROLLBACK 文 の 他 それぞれ 同 じ 意 味 をもつ END 文 ABORT 文 が 存 在 します では COMMIT 文 が 実 行 されると トランザクション 内 で 成 功 したコマンドの 結 果 のみをコミットし 失 敗 し たコマンドは 単 に 無 視 されます 一 方 では トランザクション 内 でエラーが 発 生 した 場 合 はそのトラン ザクション 全 体 が 失 敗 とみなされます エラーが 発 生 したトランザクションではそれ 以 降 のコマンド 実 行 ができず COMMIT 文 を 実 行 した 場 合 には 自 動 的 に ROLLBACK が 発 行 されます 11/18 2013 Enterprise Consortium
3. から への 移 行 本 章 では から へ 移 行 の 際 に 生 じる SQL の 書 き 換 え 方 針 を 紹 介 します 3.1. SELECT 文 3.1.1. TOP 句 ではクエリ 結 果 の 件 数 を 制 限 するために 独 自 の 拡 張 である TOP 句 を 用 います の SELECT 文 には TOP 句 はありません Window 関 数 LIMIT 句 FETCH 句 のいずれかを 用 いて 書 き 換 える 必 要 が あります 詳 しくは 本 資 料 の 2.1.3 節 を 参 照 して 下 さい 以 下 は 標 準 SQL 準 拠 である FETCH 句 を 用 いた 書 き 換 え 例 です tbl テーブルのデータを id で 昇 順 ソートし 最 初 から 10 行 のレコードを 取 得 する SELECT TOP 10 * FROM tbl ORDER BY id SELECT * FROM tbl ORDER BY id FETCH FIRST 10 ROWS ONLY 3.1.2. 列 の 別 名 は 独 自 の 構 文 により = を 使 った 列 の 別 名 を 指 定 することができます はこの 構 文 に 対 応 していません 標 準 SQL の AS ( 省 略 可 )を 用 いて 以 下 のように 書 き 換 える 必 要 があります 列 に 別 名 を 付 ける(SELECT 文 ) SELECT STAFF = s.name, MANAGER = m.name FROM staff AS s LEFT OUTER JOIN staff AS m ON s.manager_id = m.id SELECT s.name AS STAFF, m.name AS MANAGER FROM staff AS s LEFT OUTER JOIN staff AS m ON s.manager_id = m.id 3.2. 更 新 系 3.2.1. TOP 句 の TOP 句 は 更 新 系 のクエリにも 用 いることができます SELECT の 結 果 を 挿 入 する INSERT 文 で TOP 句 を 使 用 すると SELECT された 結 果 の 上 位 から 指 定 された 行 数 だけ 取 得 して 挿 入 します で 同 等 の 動 作 は SELECT 部 分 を LIMIT 句 または FETCH 句 を 用 いて 書 き 換 えることで 実 現 可 能 です tbl テーブルから 偶 数 の id を 昇 順 に 10 個 取 得 して nums テーブルに 挿 入 INSERT TOP (10) INTO nums INSERT INTO nums SELECT * FROM tbl WHERE id % 2 = 0 SELECT * FROM tbl WHERE id % 2 = 0 ORDER BY id ORDER BY id LIMIT 10 UPDATE 文 で TOP 句 を 用 いると 指 定 された 行 数 のレコードがランダムに 選 ばれ 更 新 されます で は random 関 数 と UPDATE 文 の FROM 句 を 用 いて 同 様 の 機 能 を 実 現 できます なお UPDATE 文 の FROM 句 は 独 自 の 拡 張 機 能 です nums テーブルのレコードをランダムに 10 つ 選 んで 更 新 UPDATE TOP (10) nums UPDATE nums AS t1 SET value = value * 10 SET value = value * 10 FROM (SELECT value FROM nums ORDER BY random() LIMIT 10) AS t2 WHERE t1.value = t2.value 12/18 2013 Enterprise Consortium
DELETE 文 で TOP 句 を 用 いると 指 定 された 行 数 のレコードがランダムに 選 ばれ 削 除 されます で は random 関 数 と DELETE 文 の USING 句 を 用 いて 同 様 の 機 能 を 実 現 できます なお DELETE 文 の USING 句 は 独 自 の 拡 張 機 能 です nums テーブルのレコードをランダムに 10 つ 選 んで 削 除 DELETE TOP (3) FROM nums DELETE FROM nums AS t1 USING (SELECT value FROM nums ORDER BY random() LIMIT 10) AS t2 WHERE t1.value = t2.value 3.2.2. INSERT 文 の INTO の INSERT 文 では INTO キーワードが 省 略 可 能 ですが では 省 略 することはできませ ん もし INTO が 省 略 されている 場 合 には 書 き 足 す 必 要 があります INSERT 文 の INTO は 省 略 できない INSERT tbl VALUES (10, 'ten') INSERT INTO tbl VALUES (10, 'ten') 3.2.3. DELETE 文 の FROM の DELETE 文 では FROM キーワードが 省 略 可 能 ですが では 省 略 することはできませ ん もし FROM が 省 略 されている 場 合 には 書 き 足 す 必 要 があります DELETE 文 の FROM は 省 略 できない DELETE tbl WHERE id = 10 DELETE FROM tbl WHERE id = 10 3.2.4. OUTPUT 句 では 更 新 された 行 の 結 果 を 返 すのに OUTPUT 句 を 使 用 することができます には OUTPUT 句 は 存 在 しません 代 わりに 独 自 の 拡 張 である RETURNING 句 を 用 います なお では UPDATE 文 で OUTPUT 句 を 用 いて 更 新 される 前 の 値 を 返 すこともできますが にはそのような 機 能 はありません 更 新 前 の 値 が 必 要 な 場 合 には 事 前 に 退 避 させておく 必 要 がありま す INSERT された 行 を 返 す INSERT INTO tbl OUTPUT INSERTED.* VALUES (11, 'eleven') INSERT INTO tbl VALUES (11, 'eleven') RETURNING * UPDATE された 行 の 結 果 を 返 す UPDATE tbl SET value = 'ELEVEN' OUTPUT INSERTED.* WHERE id = 11 UPDATE tbl SET value='eleven' WHERE id = 11 RETURNING * DELETE された 行 を 返 す DELETE FROM tbl OUTPUT DELETED.* WHERE id = 11 DELETE FROM tbl WHERE id = 11 RETURNING * 13/18 2013 Enterprise Consortium
3.2.5. MERGE MERGE 文 はテーブルに 既 存 の 行 がある 場 合 には 更 新 を ない 場 合 には 新 規 に 挿 入 を 行 う SQL 文 です 標 準 SQL に 従 ったものですが はこれに 対 応 していません では WITH 句 の 中 で UPDATE 文 を 用 いることにより これと 同 等 の 機 能 を 実 現 することができます なお 更 新 を 含 む WITH 句 は 独 自 の 拡 張 です diff テーブルの 値 を master テーブルにマージする (master テーブルに ID が 一 致 する 行 があったら diff.val を master.val に 足 し 加 える ID が 一 致 する 行 がない 場 合 には diff の 内 容 を master に 新 規 登 録 する ) MERGE INTO master USING diff ON master.id = diff.id WHEN MATCHED THEN UPDATE SET master.val = master.val + diff.val WHEN NOT MATCHED THEN INSERT VALUES (diff.id, diff.val) WITH inpt AS (SELECT * FROM diff), updt AS (UPDATE master.val = master.val + inpt.val FROM inpt WHERE master.id = inpt.id RETURNING master.id) INSERT INTO master (SELECT * FROM inpt WHERE id NOT IN (SELECT id FROM updt)) 3.2.6. ビューに 対 する 更 新 ではビューに 対 する 更 新 が 可 能 ですが ではビューに 対 して 更 新 することはできません ただし RULE もしくはトリガーと 組 み 合 わせることで 更 新 可 能 なビューと 同 等 な 機 能 を 実 現 することが 可 能 です その 方 法 は スキーマ 移 行 調 査 編 の 第 5 章 で 述 べられていますので そちらを 参 照 してください 3.3. その 他 の 書 き 換 え 3.3.1. 文 字 列 リテラルの 区 切 り 文 字 では SET QUOTED_IDENTIFIER が OFF の 場 合 には 文 字 列 リテラルを 表 すのに 二 重 引 用 符 (... )を 使 用 可 能 です しかし では 二 重 引 用 符 をこの 用 途 では 使 用 することはできません 代 わりに 引 用 符 ('...') で 書 き 換 える 必 要 があります 文 字 列 リテラルの 区 切 り 文 字 に 二 重 引 用 符 は 使 えない SELECT * FROM tbl WHERE value= nine SELECT * FROM tbl WHERE value= 'nine' 3.3.2. 識 別 子 の 区 切 り 文 字 では 識 別 名 を 表 すのに 角 括 弧 ([...])を 使 用 可 能 ですが では 角 括 弧 をこの 用 途 で 使 う ことはできません 代 わりに 二 重 引 用 符 (... )で 書 き 換 える 必 要 があります 識 別 子 の 区 切 り 文 字 に 引 用 符 は 使 えない SELECT * FROM [long name table] SELECT * FROM long name table 3.3.3. 文 字 列 連 結 演 算 子 では 文 字 列 の 連 結 に + 演 算 子 を 用 います では + 演 算 子 はこの 用 途 に 使 用 すること できません 演 算 子 で 置 き 換 えてください 文 字 列 の 連 結 SELECT 'Elephants' + ' never ' + 'forget.' SELECT 'Elephants' ' never ' 'forget.' 3.3.4. LIKE 演 算 子 では LIKE 演 算 子 の 中 で 文 字 クラスを 使 うことができますが の LIKE 演 算 子 は 文 字 ク 14/18 2013 Enterprise Consortium
ラスに 対 応 していません かわりに SQL 正 規 表 現 を 扱 える SIMILAR TO 演 算 子 で 置 き 換 えます A~G 以 外 の 文 字 から 始 まり A~G で 終 わる 名 前 を 検 索 SELECT name FROM staff SELECT name FROM staff WHERE name LIKE '[^a g]%[a g]' WHERE lower(name) SIMILAR TO '[^a g]%[a g]' 3.3.5. 大 文 字 / 小 文 字 全 角 / 半 角 平 仮 名 / 片 仮 名 の 区 別 では 照 合 順 序 の 設 定 によっては 文 字 列 比 較 の 際 に 大 文 字 / 小 文 字 全 角 / 半 角 平 仮 名 / 片 仮 名 が 区 別 されません ではこれらは 区 別 されますので 移 行 の 際 には 注 意 が 必 要 です アプリケーション 側 を 変 更 することで 対 応 する 他 に SQL の 書 き 換 えでもある 程 度 の 対 応 は 可 能 です 大 文 字 / 小 文 字 を 区 別 しない 文 字 列 比 較 のためには 独 自 の ILIKE 演 算 子 や ~* 演 算 子 が 用 意 され ています (~* 演 算 子 については 2.3.2 節 を 参 照 してください) その 他 に upper 関 数 または lower 関 数 を 使 って 文 字 列 を 予 め 大 文 字 か 小 文 字 のどちらかに 変 換 してから 比 較 を 行 う 方 法 があります lower 関 数 を 使 った 例 は 前 節 の LIKE 演 算 子 の 書 き 換 え 例 を 参 照 してください 全 角 / 半 角 の 変 換 は 以 下 のように translate 関 数 を 用 いることで 可 能 です 同 様 の 処 理 を 行 うユーザ 関 数 を 作 成 しておくことで lower 関 数 と 同 じ 要 領 で 使 うことができます 同 様 の 方 法 で 他 の 記 号 の 全 角 / 半 角 変 換 や 平 仮 名 / 片 仮 名 の 変 換 も 可 能 です 全 角 文 字 を 含 むデータからの 検 索 SELECT name FROM products WHERE code LIKE '%PGQL 5432 xxx%' SELECT * FROM products WHERE translate(upper(code), '-0123456789ABCDEFGHIJKLMNO PQRSTUVWXYZ', ' 0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ ' ) LIKE '%PGQL 5432 xxx%' 3.3.6. 比 較 演 算 子!<!> では 小 さくない 大 きくない を 表 す 演 算 子!<!> が 使 用 できますが この 演 算 子 は には 存 在 しません 以 上 以 下 を 表 す 演 算 子 >= <= で 置 き 換 えてください 3.3.7. 排 他 的 論 理 和 演 算 子 でビット 演 算 の 排 他 的 論 理 和 (XOR) を 表 す 演 算 子 は ^ ですが ではこの 演 算 子 は 数 の 累 乗 を 表 します の 排 他 的 論 理 和 演 算 子 # で 置 き 換 えてください 3.4. トランザクション 本 節 では と のトランザクションに 関 する SQL の 差 異 について 概 説 します 3.4.1. BEGIN TRANSACTION ではトランザクションの 開 始 に BEGIN TRANSACTION 文 を 用 います これは 標 準 SQL に 準 拠 した ものではありませんが でも 同 じ 名 前 の 文 が 存 在 します ではこの 文 を 略 して BEGIN TRAN と 書 くことが 可 能 ですが この 構 文 は に 存 在 しま せん 略 さない 形 式 か 単 に BEGIN と 書 き 直 す 必 要 があります トランザクションの 開 始 BEGIN TRAN BEGIN また では BEGIN TRANSACTION 文 のオプションとしてトランザクションに 名 前 を 付 けることができ ますが の BEGIN 文 にはそのような 機 能 はありません 15/18 2013 Enterprise Consortium
3.4.2. COMMIT TRANSACTION トランザクションのコミットは COMMIT TRANSACTION 文 で 行 います COMMIT 文 は 標 準 SQL に 準 拠 していま すが 標 準 SQL が 規 定 しているのは COMMIT と COMMIT WORK の2 種 類 の 構 文 のみです しかしながら は COMMIT TRANSACTION という 構 文 にも 対 応 しています ではこの 文 を 略 して COMMIT TRAN と 書 くことが 可 能 ですが この 構 文 は に 対 応 し ていません 略 さない 形 式 か 単 に COMMIT と 書 き 直 してください トランザクションのコミット COMMIT TRAN COMMIT また SQL Serve の COMMIT TRANSACTION ではトランザクションの 名 前 を 指 定 することができますが の COMMIT 文 にはそのような 機 能 はありません 3.4.3. SAVE TRANSACTION ではセーブポイントの 保 存 に SAVE TRANSACTION 文 を 用 います これは 標 準 SQL には 存 在 しな い 文 です では 標 準 SQL に 準 拠 した SAVEPOINT 文 を 用 います セーブポイントの 設 定 SAVE TRANSACTION savepoint_name SAVEPOINT savepoint_name 3.4.4. ROLLBACK TRANSACTION トランザクションのロールバックは ROLLBACK TRANSACTION 文 で 行 います ROLLBACK 文 は 標 準 SQL に 準 拠 していますが 標 準 SQL が 規 定 しているのは ROLLBACK と ROLLBACK WORK の2 種 類 の 構 文 のみです しかしながら は ROLLBACK TRANSACTION という 構 文 にも 対 応 しています ではこの 文 を 略 して ROLLBACK TRAN と 書 くことが 可 能 ですが この 構 文 は に 対 応 していません 略 さない 形 式 か 単 に ROLLBACK と 書 き 直 してください トランザクションのロールバック ROLLBACK TRAN ROLLBACK また では ROLLBACK TRANSACTION 文 にトランザクションの 名 前 を 指 定 することができますが の ROLLBACK 文 にはそのような 機 能 はありません はセープポイントへの 復 帰 にも RALLBACK TRANSACTION 文 を 用 います この 場 合 にはトランザク ション 名 のかわりにセーブポイント 名 を 指 定 します では 標 準 SQL 準 拠 の ROLLBACK TO SAVEPOINT 文 を 用 います セーブポイントへの 復 帰 ROLLBACK TRAN savepoint_name ROLLBACK TO SAVEPOINT savepoint_name 16/18 2013 Enterprise Consortium
4. 別 紙 一 覧 別 紙 :SQL 差 異 表 17/18 2013 Enterprise Consortium
著 者 版 所 属 企 業 団 体 名 部 署 名 氏 名 SQL 移 行 調 査 編 第 1.0 版 (2012 年 度 WG2) SRA OSS, Inc. 日 本 支 社 技 術 開 発 部 長 田 悠 吾 18/18 2013 Enterprise Consortium