WITH句と共通テーブル式(CTE)

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

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

先日、ITCのポイント取得も兼ねて、≪MCPCモバイルソリューションフェア2014≫のセミナーに参加してきました。
http://www.mcpc-jp.org/fair2014/index.html
モバイル端末を業務で使用するケースが増えてきていますが、それに伴って紛失や盗難による情報漏洩のリスクも高まってきています。紛失した際にすぐに位置情報を確認し、端末をロックしたり、データを消去したり、消去したというレポートを受信できるようなサービスについての紹介のセミナーも数社行っていました。また、端末の電源が落ちていても、独自のBIOSによりリモートから操作することができるようにしているものも出てきていますね。お金目当てで盗難するケースも出てきている中、業務でモバイルを使うためには、このようなサービスが必要な時代であることを感じます。

＜本日の題材＞
WITH句と共通テーブル式(CTE)

標準SQL規格 SQL99 より、WITH句が導入されて、SELECT文の中で記述するインラインビュー（FROMの後に指定する問合せ）をWITH句で記述し、そのSQL文中に限り繰り返し使用できるようになりました。このWITH句に記述したものを「共通テーブル式」(CTE)といいます。
(OracleではOracle9iから対応、ただし、再帰WITH句はOracle11gR2から対応)
前回の階層構造のデータ表示（Oracle）で例として挙げたSQLの外部結合で使用している部分をWITH句で共通テーブル式を使うかたちにすると、以下のようになります。

WITH E2(EMPNO, ENAME) AS(
SELECT EMPNO, ENAME FROM EMP)
SELECT
E1.EMPNO AS 従業員番号
, E1.ENAME AS 従業員名
, LPAD(' ',(LEVEL-1)*2,' ')||E1.JOB AS 職務名
, E1.MGR AS 上司従業員番号
, E2.ENAME AS 上司従業員名
, LEVEL AS 階層LEVEL
FROM EMP E1
LEFT OUTER JOIN E2 ON E1.MGR = E2.EMPNO
START WITH E1.JOB = 'PRESIDENT'
CONNECT BY PRIOR E1.EMPNO = E1.MGR
ORDER SIBLINGS BY E1.ENAME;

※結果は前回と同じになります。また、SQL自体も上から下に順に読めるので、わかりやすくなります。

次に、再帰WITH句についてですが、基本的な書き方は以下のようになります。

WITH 共通テーブル式(CTE)名(column1, column2, ...)
AS (
/* 元の SELECT文 */
UNION ALL
/* CTE名を参照する SELECT文 */
)
SELECT column_x1, column_x2, ... from CTE名;

例として、これもよく挙げられる例ではあると思いますが、1から20までの正の数を順に出力する場合のSQLです。

WITH RECUR_SEISU(val) AS (
SELECT 1 FROM DUAL
UNION ALL
SELECT val+1
FROM RECUR_SEISU
WHERE val+1 <= 20)
SELECT val FROM RECUR_SEISU;

再帰WITH句で、最初にUNION ALLまでのSELECT文を実行して、その結果を使用してUNION ALLの下のSELECT文を実行し、条件を満たすあいだそれを繰り返して処理をしているのが確認できます。

SQL ServerではSQL Server2005から共通テーブル式を使用できるようになっています。
上記のSQLは下記のようになります。

WITH RECUR_SEISU(val) AS (
SELECT 1
UNION ALL
SELECT val+1
FROM RECUR_SEISU
WHERE val+1 <= 20)
SELECT val FROM RECUR_SEISU;

※結果は同じです。

ちなみに、MySQLでは、WITH句を使っての共通テーブル式についてはまだ未対応のようです。

今日は以上まで

にほんブログ村

SELECT, 階層問合せ

階層構造のデータ表示（Oracle）

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

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

ITコーディネータ関連の情報です。
ITCA（ITコーディネータ協会）のホームページには、ＩＴ活用を考えている中小企業の皆様へと題して（経済産業省からのご案内ということで）、IT導入時に利用可能な予算・融資制度の紹介やIT導入を検討するにあたって、ＩＴ活用に成功した企業の事例をいろいろと紹介しているサイトが載せてあります。
http://www.itc.or.jp/kakushinit/
その中の一つに、中小企業・小規模事業者の未来をサポートするサイト「ミラサポ」というものが昨年開設され、国や公的機関の支援情報・支援施策等いろいろな情報を提供していますね。
https://www.mirasapo.jp/index.html
また、ITコーディネータによる支援事例も下記のサイトにあります。
http://wwk.itc.or.jp/itkeiei/
ＩＴＣＡの宣伝のようになってしまいましたが、ちょっと紹介まで

＜本日の題材＞
階層構造のデータ表示（Oracle）

今回は、時折社内でも話題になる階層構造のデータ表示について取り上げてみます。
Oracleでは、階層構造のデータを表示するのに便利な START WITH句、CONNECT BY句というのが用意されています。よく、例として上げられるのが、SCOTTユーザのテーブル「emp」表で、このテーブルには各従業員の上司の従業員番号が項目としてあり、それを使って上司と部下の関係を階層構造で表示する例を見てみます。例えば下記のようなSQLを使います。

SELECT
E1.EMPNO AS 従業員番号
, E1.ENAME AS 従業員名
, LPAD(' ',(LEVEL-1)*2,' ')||E1.JOB AS 職務名
, E1.MGR AS 上司従業員番号
, E2.ENAME AS 上司従業員名
, LEVEL AS 階層LEVEL
FROM EMP E1
LEFT OUTER JOIN (SELECT EMPNO, ENAME FROM EMP) E2
ON E1.MGR = E2.EMPNO
START WITH E1.JOB = 'PRESIDENT'
CONNECT BY PRIOR E1.EMPNO = E1.MGR
ORDER SIBLINGS BY E1.ENAME;

START WITH句で、E1.JOB='PRESIDENT' (社長) を起点（ルート）として階層表示し、CONNECT BY句で親子関係を条件付けしています。PRIOR E1.EMPNO = E1.MGR で親のレコードのEMPNO = 子のレコードのMGR という条件になります。
また、LEVEL は階層レベルを意味していて、職務名のところの頭に階層LEVEL*2ずつの空白を付けて階層が分かりやすくしています。
ORDER SIBLINGS BY句は、階層問い合わせの場合に、CONNECT BYの結果の階層順序を壊さずソートして表示するためのものになります。

START WITH句で、ルートとなるのが JOB='PRESIDENT' という情報がわからない場合は、MGRがNULL（上司の従業員番号がない）のメンバーが階層のSTARTということから、下記のように設定することもできます。

※結果は同じです。
また、SYS_CONNECT_BY_PATH(column, char) 句を使うと、ルートからノードへの列(column)の値の経路を、char で指定した文字で区切って返すかたちになります。

SELECT
EMPNO AS 従業員番号
, SYS_CONNECT_BY_PATH (JOB,'/') AS 職務パス
, ENAME AS 従業員名
, CONNECT_BY_ROOT ENAME AS 最終上司名
FROM EMP
START WITH JOB = 'PRESIDENT'
CONNECT BY PRIOR EMPNO = MGR
ORDER SIBLINGS BY ENAME;

職務パスということで、社長からの階層を区切り文字「/」を使って表示しています。
※CONNECT_BY_ROOT を使用すると、階層のルートの値（この場合は、一番の上司）を表示することができます。（階層のルートが複数あるような場合はそれぞれのルートの値が取れることになります）
ちなみに、上記の内容は、Oracleの場合に限定した内容になります。

次回からは、他のデータベースでも可能な方法として、再帰SQLについて、及びそれを使って階層構造を表示する方法について取り上げてみたいと思います。

今日は以上まで

にほんブログ村

MySQL

MySQLのINSERT

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

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

11月も残りわずかになってきました。安倍首相の決断により、衆議院が解散されました。解散については賛否両論があり、今、激しく論争が展開されていますね。世界の先がよく読めないご時世ですから、このような時は、政治のリーダーがとても大切だと思います。私も、12月14日の総選挙には、しっかりと日本のことを考えて投票するつもりです。
また、先日、高倉健さんが逝去されましたね。私のとても好きな俳優さんの一人でした。最後の映画作品「あなたへ」をテレビで見ました。とても感動しました。「健さん、ありがとう！」と思わず言いたくなりました。ご冥福を心よりお祈りいたします。

＜本日の題材＞
MySQLのINSERT

前回、MySQLについて取り上げたので、今回もMySQLのことをもう少し取り上げてみようと思います。MySQLのINSERT文には興味深い機能がありましたので、取り上げてみました。

●一括INSERT
テーブルに複数のレコードを追加する場合に、OracleやSQL Serverなどでは１行１行INSERT文を記述すると思いますが、MySQLの場合には、以下のような書式で複数のデータをまとめて登録することができます。

INSERT INTO tbl_name (col_name1, col_name2, ...)
VALUES (value1, value2, ...), (value1, value2, ...), (value1, value2, ...);

試してみます。dbsuserユーザでsampledbに接続します。
mysql –u dbsuser –p
パスワードを入力
use sampledb

前回作成した「item_mst」に、追加でデータを3行登録します。Item_mst のテーブル定義を再度確認します。
DESC item_mst;

これはOracleとの互換のためにサポートされているようです。
他にも以下の方法などでテーブルの定義を確認できるようです。
・SHOW FULL COLUMNS FROM item_mst;
・SHOW CREAT TABLE item_mst;

それでは、今回は商品として野菜を3レコード登録します。
INSERT INTO item_mst(item_cd, item_name, price) values('Y0001', 'キャベツ', 160), ('Y0002', '玉ねぎ', 200), ('Y0003', '人参', 150);

「item_mst」テーブルのレコードを確認します。
SELECT item_cd, item_name, price
FROM item_mst
ORDER BY item_cd;

新しく野菜の3行がまとめて登録されてることが確認できました。

●IGNOREオプション
主キー制約や一意性制約を設定した列には、重複した値は制約違反のため挿入することができません。エラーが返ってきます。しかし、IGNOREオプションを指定した場合はエラーを返さずに（データは挿入されず）、終了させることができます。

通常のINSERT文では重複データの登録時にはエラーが返ってきます。
INSERT INTO item_mst(item_cd, item_name, price) values('K0005', 'マンゴー', 300);

ここで、IGNOREオプションを付けてINSERTを実行します。
INSERT IGNORE INTO item_mst(item_cd, item_name, price) values('K0005', 'マンゴー', 300);

エラーを出さずに終了しました。ただエラーが発生していないだけでデータは追加されていません。確認してみます。

SELECT item_cd, item_name, price
FROM item_mst
ORDER BY item_cd;

item_cdが「K0005」は、もともとの「もも」のままであることが確認できます。

●ON DUPLICATE KEY UPDATE句
ON DUPLICATE KEY UPDATE を使うと、テーブルの PRIMARY KEY 、もしくは UNIQUE インデックスの値と、INSERT文で挿入しようとしているデータの値が異なれば INSERTを行うが、同じであれば、ON DUPLICATE KEY UPDATE句で指定した値でUPDATE を実行します。

INSERT INTO item_mst(item_cd, item_name, price) values('K0005', 'キウイ', 100)
ON DUPLICATE KEY UPDATE item_name = VALUES(item_name), price = VALUES(price);

結果を確認してみます。
SELECT item_cd, item_name, price
FROM item_mst
ORDER BY item_cd;

主キーである「item_cd」が「K0005」で一致していたので、item_name, price がON DUPLICATE KEY UPDATE句で設定された（今回はINSERT文で指定した値）で更新されていることが確認されました。
※更新する値を指定する部分でVALUES(col_name)を使用すると、引数のカラムに対して新規でデータが追加する場合に格納するはずの値を参照できます。

なお、ON DUPLICATE KEY UPDATE句を使う際には、いくつか下記のような制限はあるようです。
・テーブルに主キーか、UNIQUEインデックスがないと、ON DUPLICATE KEY UPDATE は利用できない
・キーが複数行に一致する場合、MySQL は1行のみ更新する
・ON DUPLICATE KEY UPDATE を使用する場合は DELAYED オプションは無視される

今日は以上まで

にほんブログ村

MySQL

MySQLとdbSheetClient

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

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

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

＜本日の題材＞
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」の場合に限って省略することが可能)
そうすると、パスワードを聞いてきますので、インストール時に設定したパスワードを入力します。

接続できました。

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

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

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

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

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

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

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

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

テーブル「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の設定をします。

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

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

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

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

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

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

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

今日は以上まで

にほんブログ村

カーソル

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

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

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;

結果は、以下

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

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

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

今日は以上まで

にほんブログ村

カーソル

カーソル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 を利用することでかなりレスポンスを改善できる場合がありますが、それについては次回以降にまた機会を改めて、題材として取り上げようと思います。

今日は以上まで

にほんブログ村

DB & SQL 技術ブログ

月別アーカイブ: 2014年11月

WITH句と共通テーブル式(CTE)

階層構造のデータ表示（Oracle）

MySQLのINSERT

MySQLとdbSheetClient

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

カーソルFORループ(Oracle)

カーソルについて(Oracle)

動的SQL(4)

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