ＩＴＣシュウちゃんのすべての投稿

カーソルFORループ(Oracle)

2014年11月12日ＩＴＣシュウちゃんコメントをどうぞ

ITコーディネータのシュウです。

前回に引き続き、EXCELブログのモカちゃんさんより頂いた、長野県安曇野のわさび園の写真です。とてものどかで心が休まる景色ですね。

＜本日の題材＞
カーソルFORループ(Oracle)

今回は、前回のカーソルに関連して、カーソルFORループの使用方法を題材とします。
１行１行をフェッチし、最後の行まで同じ処理を繰り返すということをカーソル処理で行いますが、それをよりシンプルに記述することができるのが、カーソルFORループです。カーソル処理で必要な以下の処理が自動化され、処理全体が単純化されます。
・OPEN、FETCH、CLOSE文の指定
・取り出した行データを格納するための変数の定義

【カーソルFORループの記述】
DECLARE
カーソルの定義 (CURSOR <カーソル名> IS <SELECT文>)
BEGIN
FOR <ループ索引名> IN <カーソル名> LOOP
処理
END LOOP;
END;

前回の例をカーソルFORループで置き換えます。

DECLARE
CURSOR jyutyu_cur IS
SELECT J01.受注番号, J01.顧客番号, J01.受注日, J02.商品CD, J02.商品数
FROM 受注テーブル J01
JOIN 受注明細テーブル J02 ON J01.受注番号 = J02.受注番号
WHERE TO_CHAR(J01.受注日, 'YYYYMMDD') = TO_CHAR(SYSDATE, 'YYYYMMDD')
ORDER BY J01.受注番号, J02.受注明細番号;

V_在庫数在庫テーブル.在庫数%TYPE;

BEGIN
FOR jyutyu_rec IN jyutyu_cur LOOP

SELECT 在庫数 INTO V_在庫数 FROM 在庫テーブル
WHERE 商品CD = jyutyu_rec.商品CD;

IF V_在庫数 >= jyutyu_rec.商品数 THEN
DBMS_OUTPUT.PUT_LINE('受注番号='|| jyutyu_rec.受注番号||',商品CD='|| jyutyu_rec.商品CD||',商品数='|| jyutyu_rec.商品数||', 引当OK');

UPDATE 在庫テーブル SET
在庫数 = 在庫数 - jyutyu_rec.商品数
WHERE 商品CD = jyutyu_rec.商品CD;

ELSIF V_在庫数 < jyutyu_rec.商品数 THEN
DBMS_OUTPUT.PUT_LINE('受注番号='|| jyutyu_rec.受注番号||',商品CD='|| jyutyu_rec.商品CD||',商品数='|| jyutyu_rec.商品数||', 引当不可');
END IF;
END LOOP;
END;
/

結果は、以下

結果は前回と同じです。記述がいくぶんシンプルになりました。

今日は以上まで

にほんブログ村

カーソル

カーソルについて(Oracle)

2014年11月10日ＩＴＣシュウちゃん 1件のコメント

ITコーディネータのシュウです。

EXCELブログのモカちゃんさんより頂いた写真です。長野県の安曇野にある、わさび園というところで、とても水がきれいなところだそうです。この風景を見ていると何年か前のNHKの朝ドラ「おひさま」によく出てきていた場面を思い出しますね。なつかし～い！
日本には美しいところが本当に多いですね。この美しい自然環境を如何に保存して後世に残していくか、ということも本当に重要な問題だと思います。

＜本日の題材＞
カーソル(Oracle)

題材の順番としては、少し遅くなってしまった感がありますが、今回は、カーソルについての使用方法についてです。
プログラムにおいては、SELECT文によって抽出したデータを変数に代入して処理をするということがいろいろな場面で必要とされるケースがあります。カーソルとは、検索したデータを変数に代入する方法の１つで、検索結果が複数行の場合も処理できるという特徴があります。

【カーソル処理の流れ】
DECLARE
カーソルの定義 (CURSOR <カーソル名> IS <SELECT文>)
BEGIN
カーソルのオープン (OPEN <カーソル名>)
LOOP
データの取出し (FETCH <カーソル名> INTO <変数名>)
END LOOP;
データのクローズ (CLOSE <カーソル名>)
END;

それでは、どういう場合にカーソルを使用するのか？ということですが、抽出した行を１件ずつ順番に処理したい場合などに使用します。1行目の処理の結果が、次行以降の処理に影響を与えるような場合には、カーソル処理でしかうまく対応できないということがあります。

例えば、よく例で上げられるのが在庫引き当て処理。
受注データが複数件あった場合に、以下のような処理を受注した順番に行うようなケース。

１）受注(明細)の各レコードについて、対象の商品について在庫の問い合わせを実施
２）在庫数が受注数より多ければ、在庫の引当を実施。（在庫テーブルのUPDATE）
３）在庫の引当を実施したものについては、受注のステータスを「出荷」に変更して、出荷テーブルにデータを登録。在庫数が不足していれば、受注のステータスを在庫引当エラーにして出荷をとめる。

この場合、受注(明細)データ1レコード毎に在庫数を更新して行くことになり、途中で在庫数が不足するということも考えられるため、1行ずつ順に処理をして行く必要があります。

SQLの例としては、1行ごとの受注データについて、在庫テーブルの在庫数を抽出して、在庫数が受注数以上の場合には、結果がわかるように引当OKのメッセージを出しながら、在庫数から受注数を差し引く処理を行うもの（簡略化したもの)を以下に記します。

その前に、当日の受注データと、在庫テーブルの情報を示します。
・受注データの内訳
SELECT
J01.受注番号, J01.顧客番号, J01.受注日, J02.商品CD, J02.商品数
FROM 受注テーブル J01
JOIN 受注明細テーブル J02
ON J01.受注番号 = J02.受注番号
WHERE TO_CHAR(J01.受注日, 'YYYYMMDD') = TO_CHAR(SYSDATE, 'YYYYMMDD')
ORDER BY J01.受注番号, J02.受注明細番号;
（当日が 2014/11/07 になってます）

・在庫テーブルの情報
SELECT 商品CD, 在庫数
FROM 在庫テーブル
ORDER BY 商品CD;

上記で示したカーソルを使った例：

DECLARE
CURSOR jyutyu_cur IS
SELECT
J01.受注番号, J01.顧客番号, J01.受注日, J02.商品CD, J02.商品数
FROM 受注テーブル J01
JOIN 受注明細テーブル J02
ON J01.受注番号 = J02.受注番号
WHERE TO_CHAR(J01.受注日, 'YYYYMMDD') = TO_CHAR(SYSDATE, 'YYYYMMDD')
ORDER BY J01.受注番号, J02.受注明細番号;

jyutyu_rec jyutyu_cur%ROWTYPE;
V_在庫数在庫テーブル.在庫数%TYPE;

BEGIN
OPEN jyutyu_cur;
LOOP
FETCH jyutyu_cur INTO jyutyu_rec;
EXIT WHEN jyutyu_cur%NOTFOUND;

SELECT 在庫数 INTO V_在庫数 FROM 在庫テーブル
WHERE 商品CD = jyutyu_rec.商品CD;

IF V_在庫数 >= jyutyu_rec.商品数 THEN
DBMS_OUTPUT.PUT_LINE('受注番号='|| jyutyu_rec.受注番号||',商品CD='|| jyutyu_rec.商品CD||',商品数='|| jyutyu_rec.商品数||', 引当OK');

UPDATE 在庫テーブル SET
在庫数 = 在庫数 - jyutyu_rec.商品数
WHERE 商品CD = jyutyu_rec.商品CD;

結果は、以下

処理後の在庫テーブルの各商品の在庫数は以下になります。

SELECT 商品CD, 在庫数
FROM 在庫テーブル
ORDER BY 商品CD;

在庫数が受注数以上の場合は、在庫の引当OKとなるが、在庫数が少なくなって、受注の商品数のほうが在庫数より大きくなった場合（受注番号の J000010009、J100010010では商品CD=S00005、S00002が在庫不足）は引当不可となっています。

※「%ROWTYPE」属性は、Oracleですでに定義されている表などの行構造を参照するもので、列名やデータ型はそのまま元の表のものを使用します。そして、「カーソル名%ROWYPE」とすれば、カーソルで指定している列を持つ変数というかたちになり、一つ一つ変数を設定する必要がなくなり、SQLの記載がシンプルになります。SELECT文で抽出する項目が多いほど、記述はらくになりますね。

今日は以上まで

にほんブログ村

ストアドプログラム, 動的SQL

動的SQL(4)

2014年11月5日ＩＴＣシュウちゃんコメントをどうぞ

ITコーディネータのシュウです。

会社の人が広島に行ったときに、夕方近くに平和公園に散歩がてら立ち寄って撮った、原爆ドームの写真です。昔、小学校の修学旅行で広島の平和公園に行ったことがあり、千羽鶴がたくさんつるしてあったのを見た記憶がありますね。子供心に、戦争や原子爆弾の被害が二度とあってはならないと強く感じました。

＜本日の題材＞
動的SQL(SQL Server)

今回は、SQL Serverの場合の動的SQLの続きで、sp_executesql システムストアドプロシージャを利用した例です。

構文
sp_executesql [ @stmt = ] statement
[
{ , [ @params = ] N'@parameter_name data_type [ OUT | OUTPUT ][ ,...n ]' }
{ , [ @param1 = ] 'value1' [ ,...n ] }
]

@stmtにはパラメータを含めることができ、パラメータ定義リスト(@params=..)とパラメータ値リスト(@param1=..)の両方に指定する必要があります。

例）受注テーブルから、指定した受注日と決済金額の条件に合った受注データを抽出する例で、条件句(WHERE句)にパラメータ変数を用いています。

DECLARE @sql_stmt nvarchar(500)
DECLARE @tab_Name nvarchar(30) = 'dbo.受注テーブル'
DECLARE @param nvarchar(200)
SET @sql_stmt = N'SELECT * FROM ' + @tab_Name
SET @sql_stmt = @sql_stmt + N' WHERE 受注日= @p1_date'
SET @sql_stmt = @sql_stmt + N' AND 決済金額>= @p2_kingaku'
SET @param = N'@p1_date varchar(8), @p2_kingaku int'

EXEC sp_executesql@sql_stmt
, @param
, @p1_date = '20141029'
, @p2_kingaku = 300000

結果は、以下

条件として、受注日(@p1_date)に「20141029」、決済金額(@p2_kingaku)に「300000」以上をパラメータとして与えた場合の、受注テーブルのデータが抽出されました。

※）注意点として、最終的に変数@sql_stmtに設定したSQL文が正しいSQL文になるように、FROM句やWHERE句、AND句などの前後に空白をきちんと入れておく必要があります。（前回投稿の例でもこの点は同じですが）
もし、上記の例でWHEREの前の空白を入れていないと、エラーになります。上記をストアドプロシージャにした場合は、プロシージャのコンパイルはできるけれども、実行時にエラーになってしまうということが発生します。

また、パラメータを繰り返し変更しながらデータを取得するようなケースで、sp_executesql を利用することでかなりレスポンスを改善できる場合がありますが、それについては次回以降にまた機会を改めて、題材として取り上げようと思います。

今日は以上まで

にほんブログ村

ストアドプログラム, 動的SQL

動的SQL（3）

2014年10月30日ＩＴＣシュウちゃんコメントをどうぞ

ITコーディネータのシュウです。

コスモスがいっぱい咲いてました。ちょっと前の台風で、結構倒れてしまっていたのですが、何とか復活して頑張って咲いています。

＜本日の題材＞
動的SQL(SQL Server)

今回は、SQL Serverの場合の動的SQLについての内容です。SQL Serverの場合は、EXECUTEステートメント、もしくは、sp_executesql システムストアドプロシージャを利用します。

例）テーブル名や項目名を変数化して、指定したテーブルから必要な項目についてのデータを抽出する動的SQLの例です。EXECUTEステートメントを使用した場合です。

受注テーブルのデータを指定した条件で抽出します。
その前に、データを確認
SELECT * FROM dbo.受注テーブル
ORDER BY 受注NO;

受注テーブルの定義は、Oracleのときと同じ

SQLは以下：

DECLARE @tab_name VARCHAR(30)
DECLARE @Col_name VARCHAR(100)
DECLARE @Clause VARCHAR(200)
SELECT @tab_name = 'dbo.受注テーブル'
SELECT @Col_name = '顧客番号, 受注日, 決済金額'
SELECT @Clause = '受注日 BETWEEN ''20141028'' AND ''20141029'''

EXECUTE ('SELECT '+@Col_name+' FROM '+ @tab_name+' WHERE '+ @Clause)

パラメータとして、@tab_name にテーブル名、@Col_nameにカラム名、@Clauseには条件を渡し、それを実行します。
（SQL Serverでも、'で囲む文字列の中に'（シングルクォーテーション）を含む場合には、'を2つ連続して記述します）
また、EXECUTEは「EXEC」と省略することもできます。

結果は、以下

受注日が20141028～20141029の期間内での顧客番号、受注日、決済金額を抽出しています。
この各パラメータを変更すれば、動的に対象のテーブル、項目、条件を設定できます。以下は変更した場合の例。

DECLARE @tab_name VARCHAR(30)
DECLARE @Col_name VARCHAR(100)
DECLARE @Clause VARCHAR(200)
DECLARE @sql_stmt VARCHAR(400)
SELECT @tab_name = 'dbo.受注テーブル'
SELECT @Col_name = '顧客番号, 決済金額'
SELECT @Clause = '決済金額 > 300000'
SELECT @sql_stmt = 'SELECT '+@Col_name+' FROM '+ @tab_name+' WHERE '+ @Clause

EXEC (@sql_stmt)

結果は、以下

ここでは、最終的に @sql_stmt に組み合わせたSELECT文セットして、それを実行しています。決済金額が 300000円以上の顧客について、顧客番号と決済金額を表示できました。

今日は以上まで

にほんブログ村

動的SQL

動的SQL(2)

2014年10月27日ＩＴＣシュウちゃんコメントをどうぞ

ITコーディネータのシュウです。

きれいな白い渡り鳥？が川辺に止まっているのを偶然撮った写真です。シラサギでしょうか？
昔から、シラサギは縁起がいい鳥と言われているようで、ときに、田んぼにシラサギがいっぱいいるのを見たことがあって、ものすごく縁起がいい！と感動したことを覚えています。
シラサギは、汚い田んぼや河川には住まないとされていて、シラサギのいるところはきれいなところだといえるようですね。

＜本日の題材＞
動的SQL(Oracle)

今回は、前回に引き続いて動的SQLについての内容です。前回は動的なDDL文をEXECUTE IMMEDIATE文を使用して実行する例を取り上げましたが、今回は、動的なDML文を実行する例です。

例）対象レコードの件数を確認した後に、レコードを削除するSQLで、対象のテーブル名とWhereの条件文をパラメータとして渡すプロシージャ（動的なDML文）

CREATE OR REPLACE PROCEDURE del_table(tab_name VARCHAR2, clause VARCHAR2)
IS
Sql_stmt1 VARCHAR2(100);
Sql_stmt2 VARCHAR2(100);
V_COUNT NUMBER(4);

BEGIN
Sql_stmt1 := 'SELECT COUNT(*) FROM '||tab_name||' WHERE ' || clause;
DBMS_OUTPUT.PUT_LINE(Sql_stmt1);
EXECUTE IMMEDIATE Sql_stmt1 INTO V_COUNT;
DBMS_OUTPUT.PUT_LINE('件数は '||V_COUNT||' 件です');

Sql_stmt2 := 'DELETE FROM '||tab_name||' WHERE ' || clause;
DBMS_OUTPUT.PUT_LINE(Sql_stmt2);
EXECUTE IMMEDIATE Sql_stmt2;
COMMIT;
END;
/

※DBMS_OUTPUT パッケージは標準出力にメッセージを表示することができ、デバッグ目的などでよく使用されます。

受注テーブルのデータを指定した条件で削除します。
その前に、データを確認

SELECT * FROM 受注テーブル
ORDER BY 受注NO;

受注テーブルの定義は以下：

プロシージャの実行
パラメータとして、'受注テーブル'、'受注日 = ''20141024''' を渡します。
（'で囲む文字列の中に'（シングルクォーテーション）を含む場合には、'を2つ連続して記述する必要があります）

EXECUTE del_table('受注テーブル','受注日 = ''20141024''');

処理の結果、受注テーブルを再度確認します。
SELECT * FROM 受注テーブル
ORDER BY 受注NO;

受注日が「20141024」のデータが削除され、delete文が実行されたことが確認できました。

パラメータのテーブル名、条件を別の値にすれば、異なるテーブルから異なる条件のデータを削除することができます。

今日は以上まで

にほんブログ村

動的SQL

動的SQL（１）

2014年10月14日ＩＴＣシュウちゃんコメントをどうぞ

ITコーディネータのシュウです。

娘がこの夏にボランティアの関係でカンボジアに行ってきたときに撮った写真です。

写真はメコン川で、中国チベット高原を源流としてミャンマー・ラオス国境、タイ・ラオス国境、カンボジア・ベトナムを通り南シナ海に抜ける、世界でも有数の国際河川の一つです。
カンボジアではこの夏も、豪雨のためメコン川流域で洪水が起きて、大変だったようです。
娘が訪問した村では、下の写真のように、水害で家の下のほうは水につかってしまい、船にたまった水をかきだしている様子を撮っています。（どの家も交通手段として船を持っているとのこと）
日本も台風や地震、火山噴火など様々な災害が起きており、犠牲になった方のご冥福を祈るばかりですが、世界でもいろいろと災害があって、その中で暮らして行かなければならないので、苦労が多いですね。

＜本日の題材＞
動的SQL(Oracle)

通常のSQLでは、UPDATEやINSERTなどの処理をどの表のどの項目に対して行うのか、というのがあらかじめ明確になっているのですが、一部のケースでは、様々なSQL文を実行時に作成、またはパラメータとして受けて処理を行うという場合があります。
この場合は、SQL文の構成は実行時まで明確ではなく、実行のたびに変わる可能性があり、これを動的SQLといいます。この動的SQLは実行時にプログラムに入力、または作成される文字列に格納されるかたちになります。

動的SQLを実行する方法としては、EXECUTE IMMEDIATE文を使用する方法、カーソル変数を使用する方法などがありますが、今回は、EXECUTE IMMEDIATE文を使用する方法を取り上げます。

例）パラメータとして、削除するテーブル名を渡すプロシージャ（動的なDDL文）

CREATE OR REPLACE PROCEDURE truncate_table(tab_name VARCHAR2)
IS
Sql_stmt VARCHAR2(100) := 'TRUNCATE TABLE '|| tab_name;
BEGIN
EXECUTE IMMEDIATE Sql_stmt;
END;
/

テーブル：商品マスタのデータを削除します。
その前に、データを確認

SELECT * FROM 商品マスタ;

プロシージャの実行
パラメータとして、「商品マスタ」を渡します。

EXECUTE truncate_table(‘商品マスタ’);

SELECT COUNT(*) FROM 商品マスタ;

データが1件もなくなり、truncate文が実行されたことが確認できました。
今日は以上まで

にほんブログ村

関数

RANK, DENSE_RANK

2014年9月27日ＩＴＣシュウちゃんコメントをどうぞ

ITコーディネータのシュウです。

関東最古の神社の一つと言われる鷲宮神社の『土師祭』(はじさい)が9月最初の日曜日(9/7)に行われたときの写真です。千貫神輿という大きな神輿を担いで通りを練り歩く姿は結構迫力があります。
また、鷲宮神社は人気まんが作品『らき☆すた』の舞台となった神社であり、聖地と呼ばれるだけあって、お祭りには地元の方だけでなく、『らき☆すた』ファンの皆さんや、アニメの登場人物やキャラクターに扮するコスプレ姿の若者などが大勢訪れ、賑わっていましたね。

　　
普段の鷲宮神社の鳥居と本殿です。

＜本日の題材＞
RANK、DENSE_RANK

順位付（ランキング）関数に、以前題材にあげたことのあるROW_NUMBER関数、それにRANK関数、DENSE_RANK関数などがあります。
ROW_NUMBER関数が単純な連番であるのに対し、同じ値があったときに、同じ順位を付けることができるのが、RANK、DENSE_RANK関数です。両者の違いは、同じ値があったときの次の値の順位を飛ばした値にするのがRANK、連続した値にするのがDENSE_RANKです。

例として下記のような商品マスタを作成 (SQL Server2008の環境)します。

CREATE TABLE dbo.商品マスタ(
商品CD VARCHAR(10)
, 商品名 VARCHAR(20)
, 分類 VARCHAR(20)
, 値段 DECIMAL(10)
, CONSTRAINT PK_商品マスタ PRIMARY KEY (商品CD));

データが以下のような場合：
SELECT * FROM dbo.商品マスタ
ORDER BY 商品CD;

商品マスタの商品を値段の高い順に表示したいときに、同じ値があったときの次の値の順位を飛ばした値にするRANK関数を使用した場合：

SELECT
RANK() OVER (ORDER BY 値段 DESC) RANK順位
, 商品CD, 商品名, 分類, 値段
FROM dbo.商品マスタ;

同じ値があったときの次の値の順位を連続した値にするDENSE_RANK関数を使用したとき、

SELECT
DENSE_RANK() OVER (ORDER BY 値段 DESC) DENSE_RANK順位
, 商品CD, 商品名, 分類, 値段
FROM dbo.商品マスタ;

ROW_NUMBER、RANK、DENSE_RANKを一緒に並べて使用した場合、
SELECT
ROW_NUMBER() OVER (ORDER BY 値段 DESC) ROW_NUMBER順位
, RANK() OVER (ORDER BY 値段 DESC) RANK順位
, DENSE_RANK() OVER (ORDER BY 値段 DESC) DENSE_RANK順位
, 商品CD, 商品名, 分類, 値段
FROM dbo.商品マスタ;

また、これらの関数は、PARTITION BY句と一緒に使用すると、グループ化してランキングを抽出することができます。

SELECT
ROW_NUMBER() OVER (PARTITION BY 分類 ORDER BY 値段 DESC) ROW_NUMBER順位
, RANK() OVER (PARTITION BY 分類 ORDER BY 値段 DESC) RANK順位
, DENSE_RANK() OVER (PARTITION BY 分類 ORDER BY 値段 DESC) DENSE_RANK順位
,商品CD,商品名,分類,値段
FROM dbo.商品マスタ;

上記結果のように、分類ごとにグループ化して、それぞれの関数のランキングを取得することができます。今回はSQL Serverで確認しましたが、基本的にOracleでも同様です。

今日は以上まで

にほんブログ村

MERGE

MERGE文（2）

2014年9月18日ＩＴＣシュウちゃんコメントをどうぞ

ITコーディネータのシュウです。

前回の題材に上げたMERGE文について、SQL Serverではできないと思っている方もいるのではないかという話を聞き、今回はSQL Serverでもできることを報告します。

＜本日の題材＞
MERGE文（SQL Server編）

MERGE文の説明は前回既に行なったので、今回は省きます。
前回と同じ例をSQL Server2008で試してみました。

元データを商品テーブルＡ、登録・更新したいテーブルを商品テーブルＢとします。

CREATE TABLE dbo.商品テーブルＡ(
商品CD VARCHAR(10)
, 商品名 VARCHAR(20)
, CONSTRAINT PK_商品テーブルＡ PRIMARY KEY (商品CD));

CREATE TABLE dbo.商品テーブルＢ(
商品CD VARCHAR(10)
, 商品名 VARCHAR(20)
, CONSTRAINT PK_商品テーブルＢ PRIMARY KEY (商品CD));

データがそれぞれ以下のような場合：
SELECT * FROM dbo.商品テーブルＡ;

SELECT * FROM dbo.商品テーブルＢ;

商品テーブルＡのデータを元に、商品テーブルＢに既に商品CDが存在するデータは、商品名を変更し、商品CDが存在しないものは商品テーブルＢにデータを登録する場合のMERGE文を実行します。

MERGE INTO dbo.商品テーブルＢ
USING dbo.商品テーブルＡ
ON (dbo.商品テーブルＡ.商品CD = dbo.商品テーブルＢ.商品CD)
WHEN MATCHED THEN
UPDATE SET
商品名 = dbo.商品テーブルＡ.商品名
WHEN NOT MATCHED THEN
INSERT (商品CD,商品名)
VALUES (商品テーブルＡ.商品CD,商品テーブルＡ.商品名);
-- (2 行処理されました)

上記を実行した後に、商品テーブルＢを確認すると、
SELECT * FROM 商品テーブルＢ;

商品CD：00001 については、商品名のみ更新し、はじめは存在していなかった商品CD：00002 のデータが追加されているのが、SQL Server2008でも確認できました。

※SQL Server2008からMERGE文は対応するようになっていますので、それ以降のバージョンでは問題なく動作します。

別の例）
上記のような同じ構造のテーブルからの更新・登録ではなく、ユーザが何かのアプリで入力した場合のような定数値の情報からデータを更新・登録するケースもあるので、その際のMERGE文の例を以下に追記します。

例えば、変数@商品CD、@商品名を使用し、商品CD「00001」、商品名「商品１(甘さ控えめ)」という値を代入して使用する場合：

DECLARE @商品CD VARCHAR(10);
DECLARE @商品名 VARCHAR(20);

SET @商品CD = '00001';
SET @商品名 = '商品１(甘さ控えめ)';

MERGE INTO dbo.商品テーブルＢ
USING (SELECT @商品CD AS 商品CD, @商品名 AS 商品名) AS 商品情報
ON (dbo.商品テーブルＢ.商品CD = 商品情報.商品CD)
WHEN MATCHED THEN
UPDATE SET
商品名 =商品情報.商品名
WHEN NOT MATCHED THEN
INSERT (商品CD,商品名)
VALUES (商品情報.商品CD,商品情報.商品名);
--(1 行処理されました)

上記を実行した後に、商品テーブルＢを確認すると、
SELECT * FROM 商品テーブルＢ;

商品CD：00001 については、商品名が更新されているのが確認できます。

今日は以上まで

にほんブログ村

MERGE

MERGE文

2014年9月16日ＩＴＣシュウちゃんコメントをどうぞ

ITコーディネータのシュウです。

本当に久しぶりのアップになります。
システムの納品の対応で、ここ1ヶ月、精神的にも肉体的にもかなり忙しくて大変な状態でした。受託開発は、途中で変更や内容が膨らんで、予定通りに進めるのが難しく、本当に納期に間に合うのか？と冷や汗をいつもかくことが多いと思うのは私だけでしょうか？
そこのスコープの管理を如何にお客様との間できちんと行うのかがとても重要であることはいつも思っているのですが。。。
必死になってときに残業も惜しまず、頑張るのですが、なぜそうなっているのか事情がわかりにくい分野でもあり、意外に周りの目は冷たかったりして．．．トホホ。
でも、問題を一つ一つ解決していき、納期が見えてきたときの達成感も、我々しか味わえないものかも知れませんね。といっても、最後の検収が上がるまでは何があるかわからないので、まだ緊張が解けないこの頃です。

心の余裕が出てきたところで、またブログ再開します。

＜本日の題材＞
MERGE文

ある抽出結果の情報を指定のテーブルに登録したいけれど、既にそのデータ（キー情報が同じデータ）が登録したいテーブルに存在していたら、値の違う部分を更新し、まだ存在していなければ登録する、というような要件に出くわすときが時折あります。

このとき、データが既に存在しているかどうかを確認して、IF文でUPDATE、INSERTを切り分けるということは、結構面倒ですが、これを一度のSQLで実行することができるのが、MERGE文です。

簡単な例として、同一構造のテーブルを元データとして、データを更新、なければ登録する場合は、下記のようになります。

元データを商品テーブルＡ、登録・更新したいテーブルを商品テーブルＢとします。

CREATE TABLE 商品テーブルＡ(
商品CD VARCHAR2(10)
, 商品名 VARCHAR2(20)
, CONSTRAINT PK_商品テーブルＡ PRIMARY KEY (商品CD));

CREATE TABLE 商品テーブルＢ(
商品CD VARCHAR2(10)
, 商品名 VARCHAR2(20)
, CONSTRAINT PK_商品テーブルＢ PRIMARY KEY (商品CD));

データがそれぞれ以下のような場合：

SELECT * FROM 商品テーブルＡ;

SELECT * FROM 商品テーブルＢ;

商品テーブルＡのデータを元に、商品テーブルＢに既に商品CDが存在するデータは、商品名を変更し、商品CDが存在しないものは商品テーブルＢにデータを登録する場合に以下のMERGE文を実行します。

MERGE INTO 商品テーブルＢ
USING 商品テーブルＡ
ON (商品テーブルＡ.商品CD = 商品テーブルＢ.商品CD)
WHEN MATCHED THEN
UPDATE SET
商品名 = 商品テーブルＡ.商品名
WHEN NOT MATCHED THEN
INSERT (商品CD, 商品名)
VALUES (商品テーブルＡ.商品CD, 商品テーブルＡ.商品名)
;

上記を実行した後に、商品テーブルＢを確認すると、

SELECT * FROM 商品テーブルＢ;

商品CD：00001 については、商品名のみ更新し、はじめは存在していなかった商品CD：00002 のデータが追加されているのが確認できます。（Oracleで検証）

今日は以上まで

にほんブログ村

縦横変換

縦に並んだ複数行のデータをカンマ区切りで横並びに表示

2014年8月10日ＩＴＣシュウちゃん 4件のコメント

ITコーディネータのシュウです。

写真は、前回と同じく「古代蓮の里」で撮った古代蓮の花です。
今年も暑い夏が続くと思えば、大型台風の影響で大雨などもあり、とても変わりやすく難しい天候ですね。台風などでこれ以上被害が出ないことを祈りつつ、天気予報をいつもチェックして注意しないといけないですね。

＜本日の題材＞
縦に並んだ複数行のデータをカンマ区切りで横並びに表示する方法(SQL Server)

縦に並んだ複数行のデータを、カンマ区切りで横並びに表示する方法について、最近SQL Serverで確認したので、忘れないようにアップしておきます。
SQL Serverでは、SQLクエリにFOR XML句というものを使うことで、SQLクエリの結果をXML形式で取得することができるのですが、今回はそれを使ってみる方法です。

例えば、地方とその地方に属する都道府県を、それぞれ、地域マスタ、府県マスタということで、下記のように持たせてみます。

SELECT * FROM dbo.地域マスタ
ORDER BY 地域CD;

SELECT * FROM dbo.府県マスタ
ORDER BY 府県CD;

… 続きは省略

ここで、上記の地域ごとの都道府県の情報を、カンマ区切りで横並びに表示させたいときに、以下のようにすると可能です。

SELECT
A.地域CD
, A.地域名
,(SELECT都道府県名 + ','
FROM dbo.府県マスタ
WHERE 地域CD = A.地域CD
ORDER BY 府県CD
FOR XML PATH('')) AS 都道府県名
FROM dbo.地域マスタ A
ORDER BY A.地域CD
;

FOR XML句をPATHモードで指定し、スペース区切りの文字列を生成しながら、カンマでつなげるというかたちにすると、上記の結果のようになります。
今日は以上まで

にほんブログ村

DB & SQL 技術ブログ

ＩＴＣシュウちゃんのすべての投稿

カーソルFORループ(Oracle)

カーソルについて(Oracle)

動的SQL(4)

動的SQL（3）

動的SQL(2)

動的SQL（１）

RANK, DENSE_RANK

MERGE文（2）

MERGE文

縦に並んだ複数行のデータをカンマ区切りで横並びに表示

主にSQLについて書いていきたいと思います。