MySQLとdbSheetClient

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

autumn-leaves_beiz.jp_M06383

最近は、朝晩はとても冷え込んできました。そろそろ暖房器具を出している方も多いようです。風邪を引かないように注意しないと。。。

<本日の題材>
MySQLとdbSheetClient

今回は、ちょっと大きく内容を変えて、MySQLについて取り上げたいと思います。 MySQLは、世界でもっとも普及しているオープンソース・データベースとして知られていて、ウィキペディアの情報では、市場シェアでは他のオープンソース・データベースを圧倒しているとのこと。
元はスウェーデンの「MySQL AB」という企業が開発していましたが、2008年2月26日にサン・マイクロシステムズに買収され、さらに2010年1月27日、サン・マイクロシステムズがオラクルに買収されたため、現在はOracleの製品ですね。
今まであまり試したことはなかったのですが、そのMySQLを、私のほうで普段仕事で使用しているdbSheetClientというツールで使えるということなので、ちょっと簡単に試してみました。

MySQLをテストのためWindows仮想サーバにインストールした直後、サンプル用のデータベースを作成:

MySQLの対話型インターフェースで、OracleのSQL*Plusのようなツールがあるので、それを使ってみます。

まず、rootユーザで接続します。
mysql –u root –p
(※MySQLではユーザーをユーザー名+接続ホスト名で管理していますので、本来はユーザー名には「root@localhost」と指定する必要があるのですが、ホスト名が「localhost」の場合に限って省略することが可能)
そうすると、パスワードを聞いてきますので、インストール時に設定したパスワードを入力します。

mysql_接続
接続できました。

次に、サンプル用のデータベース「sampledb」を作成します。
Create database sampled;

create_database

データベースを確認してみると
Show databases;

show_database-crop

Sampledbが作成されたのが確認できます。

ユーザを確認してみると
SELECT user, host FROM mysql.user;

mysql_user_select

rootユーザのみです。
dbSheetClientでアクセスして使うためのユーザ dbsuser (sampledbへのすべての権限(ALL)を付与)を作成:
GRANT ALL ON sampledb.* TO dbsuser@localhost IDENTIFIED BY ‘passwd’;

mysql_user_add

(CREATE USER文でもユーザは作成できますが、上記のGRANT文にすると権限を与える文でユーザを作成することが可能です) 簡単にデータベース、ユーザの作成ができました。

さて、普段業務で使用しているExcelシートをそのまま利用しつつ、データはデータベース化して管理し、複数のメンバーで共有しながらセキュリティ対策も可能なシステムがわりと簡単に作成できるツールがあればいいと思いませんか?
ここで、私が普段使用しているツールで、上記で説明したようなシステムを作成できるツールとして、dbSheetClientというものがあるのですが、それを使ってMySQLのデータを扱えるという内容を、今回は紹介してみたいと思います。

dbsuserでログインしてから、データベース「sampledb」に接続します。
use sampledb;

mysql_use_database

テーブル「item_mst」を作成します。

CREATE TABLE item_mst(
item_cd VARCHAR(10)
,
item_name VARCHAR(20)
,
price int
,
DBS_STATUS VARCHAR(2) default '1'
,
DBS_CREATE_USER VARCHAR(60) default 'SYSTEM'
,
DBS_CREATE_DATE VARCHAR(30)
,
DBS_UPDATE_USER VARCHAR(60) default 'SYSTEM'
,
DBS_UPDATE_DATE VARCHAR(30)
,
CONSTRAINT PK_item_mst PRIMARY KEY (item_cd));

(DBS_STATUS以降の項目は、dbSheetClientの制御用フィールドになり、更新をdbSheetの機能で行う際には必要な項目となります)

さて、dbSheetClientというツールを起動して、データベースの設定を行います。開発版を起動して、「DB」の設定のところで、下記のように MySQLの設定をします。

dbsheet_mysql設定_2

その後、「IOTG」というdbSheetClientの機能を使うと、先ほどのテーブル「item_mst」をEXCEL上に読込んだり、更新したりできるプログラムを簡単に作成できます。

mysql_iotg_2

その後、作成したプログラムをメニューから起動して、「読込」ボタンを押下します。最初はデータは1件もないので、データは表示されません。

mysql_iotg_読込み1_2

では、この画面で、EXCEL上から項目 item_cd, item_name, price の値を5件ほど入力してみましょう。りんご、みかん、なし、かき、ももとそれぞれデータを入力します。その後、「更新」ボタンを押して更新を実行します。

mysql_データ更新しますか2

OKを押して更新を行うと、MySQLデータベース上のデータが更新できます。 MySQL側でデータを確認:

select item_cd, item_name, price
from item_mst
order by item_cd;

mysql_item_select

MySQL側でExcelで登録したデータが確認できました。

通常、dbSheetClientは商用のDBであるOracle、SQL Serverを利用されることが多いですが、オープン・ソースデータベースであるMySQLについても対応しているようですね。 dbSheetClientの詳細については、以下のHPを確認してみてください http://www.newcom07.jp/dbsheetclient/index.html

今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村

カーソルについて(SQL Server)

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

紅葉

秋も深まってきて、紅葉の季節になってきました。なかなか時間が取れないですが、ひとときでも、美しい自然の中を散策したいですね。

<本日の題材>
カーソル(SQL Server)

Oracleの場合のカーソルについて、前々回、前回と題材としてアップしてきましたが、今回はSQL Serverの場合を上げてみようと思います。カーソル処理の基本的な流れは同じですが、SQL Serverの場合は以下のようになります。

【カーソル処理の流れ】
カーソルの定義
(DECLARE <カーソル名> CURSOR FOR <SELECT文>)
カーソルのオープン (OPEN <カーソル名>)
カーソルから1行データの取出し
(FETCH NEXT FROM <カーソル名> INTO <変数名>)
@@FETCH_STATUS が 0 (success) の間ループ
(WHILE @@FECTH STATUS = 0
BEGIN
    1行ごとに行いたい処理
    次の行の取得
(FETCH NEXT FROM <カーソル名> INTO <変数名>)
END)
カーソルのクローズ (CLOSE <カーソル名>)
カーソル参照を削除 (DEALLOCATE <カーソル名>)

ちょっとくどいかも知れませんが、前回のOracleで行ったカーソル処理を SQL Serverで試してみます。

DECLARE
jyutyu_cur CURSOR FOR
SELECT J01.受注番号, J01.顧客番号, J01.受注日, J02.商品CD, J02.商品数
FROM dbo.受注テーブル J01
JOIN dbo.受注明細テーブル J02 ON J01.受注番号 = J02.受注番号
WHERE CONVERT(VARCHAR, J01.受注日, 111) = CONVERT(VARCHAR, GETDATE(), 111)
ORDER BY J01.受注番号, J02.受注明細番号;

DECLARE
@受注番号 VARCHAR(10)
, @顧客番号 VARCHAR(8)
, @受注日 DATE
, @商品CD VARCHAR(6)
, @商品数 DECIMAL(4)
, @在庫数 DECIMAL(8);

OPEN jyutyu_cur;
FETCH NEXT FROM jyutyu_cur INTO @受注番号, @顧客番号, @受注日, @商品CD, @商品数;

WHILE (@@fetch_status = 0)
BEGIN

SELECT @在庫数 =在庫数 FROM dbo.在庫テーブル
WHERE 商品CD = @商品CD;

IF @在庫数 >= @商品数
BEGIN
Print '受注番号='+ @受注番号+',商品CD='+ @商品CD+',商品数='+ CAST(@商品数 AS VARCHAR)+', 引当OK';

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

ELSE
Print '受注番号='+ @受注番号+',商品CD='+ @商品CD+',商品数='+ CAST(@商品数 AS VARCHAR)+', 引当不可';

FETCH NEXT FROM jyutyu_cur INTO @受注番号, @顧客番号, @受注日, @商品CD, @商品数;
END;
CLOSE jyutyu_cur;
DEALLOCATE jyutyu_cur;

結果は、以下
カーソル結果_sqlsv

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

SELECT 商品CD, 在庫数
FROM dbo.在庫テーブル
ORDER BY 商品CD;
在庫テーブル結果_sqlsv

SQLの記述の仕方が、OracleとSQL Serverでは多少異なることがわかると思いますが、行いたい処理は同様になすことができて、結果もOracleのときと同じになります。

今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村

カーソルFORループ(Oracle)

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

IMG_2561

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

<本日の題材>
カーソルFORループ(Oracle)

今回は、前回のカーソルに関連して、カーソルFORループの使用方法を題材とします。
1行1行をフェッチし、最後の行まで同じ処理を繰り返すということをカーソル処理で行いますが、それをよりシンプルに記述することができるのが、カーソル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;
/

結果は、以下
カーソル結果2

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

今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村

カーソルについて(Oracle)

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

IMG_2539

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

<本日の題材>
カーソル(Oracle)

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

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

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

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

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

この場合、受注(明細)データ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;

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

結果は、以下
カーソル結果2

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

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

カーソル用在庫データ結果

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

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

今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村

動的SQL(4)

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

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

<本日の題材>
動的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

結果は、以下
executesql_2結果

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

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

executesql_2結果error2

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

今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村

動的SQL(3)

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

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

<本日の題材>
動的SQL(SQL Server)

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

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

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

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

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」と省略することもできます。

結果は、以下
受注テーブル結果1ms

受注日が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)

結果は、以下
受注テーブル結果2ms

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

今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村

動的SQL(2)

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

<本日の題材>
動的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;
受注テーブル結果1

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

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

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

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

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

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

今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村

動的SQL(1)

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

IMG_0895

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

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

IMG_1042

<本日の題材>
動的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(‘商品マスタ’);
execute1

SELECT COUNT(*) FROM 商品マスタ;
execute_結果1

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

にほんブログ村 IT技術ブログへ
にほんブログ村

RANK, DENSE_RANK

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

IMG_1063

関東最古の神社の一つと言われる鷲宮神社の『土師祭』(はじさい)が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.商品マスタ;
rank順位

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

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

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.商品マスタ;
parttion_by順位付

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

今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村

MERGE文(2)

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

1395797133260_2

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

<本日の題材>
MERGE文(SQL Server編)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村

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