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

ラグビーワールドカップで、日本がサモアを破り、2勝目をあげました。ほぼ完ぺきなかたちでサモアの攻撃を抑えて、前半は何と20-0、後半もサモアの攻撃を1トライに抑えての完勝といっていい内容でしたね。見ていてとても興奮しました。次のアメリカ戦も是非頑張ってほしいと思います。

さて、dbSheetClientには、EXCEL版だけでなく、Access版というものもありますが、この夏Access版の体験版がリリースされています。Access版については、「仙ちゃんのシステムフォロー記」という記事でも詳しく説明がありますので、是非興味のある方はそちらを読んでみてください。こちらもExcel版と同様、データベースにはOracle、SQL Serverなどを利用してシステムを構築できます。
http://www.newcom07.jp/dbsheetclient/access/forrow6.html

 ＜本日の題材＞
エクスポート（Oracle11gR2）での注意点（0件のテーブル）

最近、Oracle11gR2にて、テスト環境の作成のため、スキーマごとエクスポート(expコマンド)して、テスト用ユーザにインポート(impコマンド)したときに、一部のテーブルが作成されず、それを使用したプロシージャがコンパイルされないということを経験しました。
よくよく調べてみると、インポート(impコマンド)が失敗しているという訳ではなく、エクスポート（expコマンド）の際に、全てのテーブルがエクスポートされていなかったのです。どういうテーブルがエクスポートされなかったかというと、データ件数が0件のテーブルです。
何故こういうことが起きるのか、原因を調べていくと、Oracle11gR2のデータベース・ユーティリティについてのドキュメントに、以下のような記載がありました。

オリジナルのエクスポート
・Oracle Database 11gリリース2(11.2)では、デフォルトで、DEFERRED_SEGMENT_CREATIONパラメータがTRUEに設定されます。つまり、作成した表は、データの最初の行が表に挿入されるまでセグメントがありません。オリジナルのエクスポートではセグメントのない表が無視されます。したがって新しい表を作成しても、エクスポートの前にデータを挿入しないと、それらの表はエクスポートされません。(データ・ポンプ・エクスポートではこの制限がありません。これはセグメントのない表が無視されないためです。)
http://docs.oracle.com/cd/E16338_01/server.112/b56303/whatsnew.htm

 つまり、作成した表に1行でも行を登録しないとセグメントが作成されず、そのためもともとのexportコマンドでは無視されてしまうということです。
昔から利用していた exportコマンドではなく、データ・ポンプ・エクスポートと言われる expdpコマンドでのエクスポートでは、こういうことは起きないということなので、こういうケースの対応としては、以下のどれかになると思われます。

・expdpコマンドでのエクスポート、及びimpdpコマンドでのデータ・ポンプ・インポートを行う。
・初期化パラメータ「DEFERRED_SEGMENT_CREATION」パラメータを「FALSE」に変更して、exportコマンドを実行する。
・件数が0件というテーブルをなくしてから、エクスポートを行う。

しかし、3番目は現実的には難しいと思われるので、1番目か2番目の対応となりますよね。

実際に試してみます。

例）
SCOTTユーザのスキーマに、以前ブログの自律型トランザクションの題目でも紹介したエラーのログをためるための「ERROR_LOG」テーブルを作成し、それを利用するプロシージャ「RECORD_ERROR」を作成した直後に、通常のexportを実施します。

このとき、scottのスキーマにあるテーブルには何があるか確認します。
SELECT TABLE_NAME FROM USER_TABLES;

ここで、scottユーザのスキーマごとexportを実行します。
exp scott/tiger file=scott.dmp  STATISTICS=none

（※STAISTICS=none は、「EXP-00091: 不審な統計をエクスポートしています。」のエラーが出ないようにするためのもので、統計情報をエクスポートしない設定に今回はしています）

エクスポートされているテーブルに、先ほど作成したデータが0件の「ERROR_LOG」テーブルがないことがわかります。こうなると、このダンプファイルをインポートしても「ERROR_LOG」テーブルは存在しないので、それを使っているプロシージャはエラーになってしまいます。

次に、expdpコマンドによるデータ・ポンプ・エクスポートを実行してみます。

まず、前段階として、ディレクトリの作成とread/write権限の付与を行います。

sqlplus / as sysdba
create or replace directory DP_DIR as ‘\work\dpdir’;
grant read, write on directory DP_DIR to SCOTT;

その後、expdpコマンドで、SCOTTユーザのスキーマ全体をバックアップします。

expdp scott/tiger directory=DP_DIR dumpfile=scott.dmp

先ほどのexportではエクスポートされなかった0行の「ERROR_LOG」テーブルが、今回はエクスポートされているのが確認できます。

このエクスポートファイルを、scott2 というスキーマに丸ごとインポートします。

impdp dbadmin/パスワード directory=DP_DIR dumpfile=scott.dmp REMAP_SCHEMA=scott:scott2

※以前のimpコマンドでは、FROMUSER=scott  TOUSER=scott2 となっていたところが、impdpコマンドでは、REMAP_SCHEMA=scott:scott2 となります。

上記によってインポートされたscott2ユーザのほうでは、「ERROR_LOG」テーブルもインポートされ、それを使ったプロシージャもVALID（使用可能）な状態となっています。

さて、もう一つの初期化パラメータ「DEFERRED_SEGMENT_CREATION」パラメータを「FALSE」に変更して、exportコマンドを実行するほうも試してみます。

sysdbaでログインし、初期化パラメータ「DEFERRED_SEGMENT_CREATION」を確認した後、FALSEに変更します。

sqlplus / as sysdba
SHOW PARAMETER DEFERRED_SEGMENT_CREATION
ALTER SYSTEM SET DEFERRED_SEGMENT_CREATION=FALSE;

この状態で、再度、exportコマンドを実行してみます。

しかし、今回も「ERROR_LOG」テーブルはエクスポートされませんでした。

ここで、初期化パラメータを変更した後に、データが0件の「ERROR_LOG」テーブルを再作成する必要があるようです。

一旦DROP TABLEを行った後に、再度 CREATE TABLEを行います。（プロシージャも再度コンパイルします）
その後、もう一度exportを実行してみます。

 今度は、「ERROR_LOG」テーブルがエクスポートされているのが確認できました。少し手間な部分もありますが。。。

今日は以上まで

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

今回も、ITC関連のネタです。ITコーディネータ協会では、毎年この時期にITCカンファレンスというものを開催しており、今年が14回目です。今年は10月30日(金)～31日(土)で行われます。
今年の大会テーマは「IoT新時代におけるＩＴコーディネータのエボリューション～クラウド・Industrie4.0の可能性～」。ドイツが取り組んでいるIndustrie4.0（第4次産業革命）というものを取り上げつつ、日本版Industrie 4.0と言われる「つながる工場」の動きやITコーディネータ協会としての取り組みを紹介するということです。
HPは以下：
http://www.itc.or.jp/activity/seminar/itc_conf2015/index.html

近年、スマートフォンに代表されるようなコンピュータを内蔵した機器がインターネットに接続され、さらには様々なモノにセンサーなどを取り付け通信ができ、それらがつながる世界というような内容でIOT（Internet of Things）「モノのインターネット」という言葉をよく耳にするようになりました。スマートハウスやスマート家電などのスマート～という名前もよく使われますね。「つながる工場」、今、ものづくりの世界で大きな変革のときを迎えつつあるようです。それらを駆使することで、大きな飛躍のチャンスの可能性があるかも知れません。

＜本日の題材＞
一時テーブル(2)

前回、一時テーブルの例としてOracleの場合を取り上げましたので、今回はSQL Serverについて例を上げて確認してみたいと思います。

SQL Serverの場合のローカル一時テーブル作成の構文：

CREATE TABLE #表名(
  列名  データ型
, 列名  データ型
, …
);

SQL Serverの場合のグローバル一時テーブル作成の構文：

CREATE TABLE ##表名(
  列名  データ型
, 列名  データ型
, …
);

一時テーブルの使用例として、売上テーブルから顧客の初回購入日や購買回数などの購買履歴の情報を抽出して表示するような単純な処理を、ストアド・プロシージャで実行する簡単な例を作成してみます。

CREATE PROCEDURE dbo.顧客購買履歴情報抽出
        @顧客ID         VARCHAR(8)
AS
BEGIN
        -- 一時テーブルの作成
        CREATE TABLE #顧客初回購入(
                顧客ID                  VARCHAR(8),
                初回購入日              DATETIME
                );
 
        CREATE TABLE #顧客購入履歴(
                顧客ID                  VARCHAR(8),
                累計購入回数    decimal(7),
                累計購入金額    decimal(9)
                );
 
-- 初回購入日の抽出結果を一時テーブルに登録
        INSERT INTO #顧客初回購入
        SELECT 顧客ID, MIN(出荷日)
          FROM dbo.売上
         WHERE 顧客ID = @顧客ID
         GROUP BY 顧客ID;
 
-- 累計購入情報の抽出結果を一時テーブルに登録
        INSERT INTO #顧客購入履歴
        SELECT 顧客ID, COUNT(*), SUM(売上金額)
          FROM dbo.売上
         WHERE 顧客ID = @顧客ID
         GROUP BY 顧客ID;
 
-- 顧客購買履歴情報の表示
        SELECT
          A.顧客ID
        , FORMAT(A.初回購入日,'yyyyMMdd') AS 初回購入日
        , B.累計購入回数
        , B.累計購入金額
         FROM #顧客初回購入 A
         JOIN #顧客購入履歴 B ON A.顧客ID = B.顧客ID;
END;
GO

Oracleとは違って、プロシージャの中で一時テーブルを作成し、それらをジョインして抽出結果を表示するサンプルになります。

上記のプロシージャに顧客IDのパラメータを渡して実行してみます。

DECLARE @顧客ID varchar(8)='K0000011'
EXEC dbo.顧客購買履歴情報抽出
  @顧客ID

別の顧客IDをパラメータで渡して、再度実行結果を確認すると、

DECLARE @顧客ID varchar(8)=' K0000054'
EXEC dbo.顧客購買履歴情報抽出
  @顧客ID

さて、SQLServerの場合のグローバル一時テーブルについては、あまり使ってみたことがないですが、どういうときに使うかを調べてみたところ、排他処理で使うケースがあるようです。

悲観的排他制御を行いたいときに、アプリケーションでコントロールする場合には、最初に排他用のフラグを立てて、そのフラグが立っている場合は、他のユーザは更新などの処理が実施できないというようなことを行うことがあると思います。
ただ、このフラグの制御を実テーブルで行うと、予期せぬかたちで処理が強制的に終了してしまった場合に、フラグが立ったままになってしまい、排他の状態が続いてしまって更新ができなくなってしまうなどというケースが出てきてしまうため、何らかのかたちで解除する方法を検討しなければなりません。

このときに、このフラグの制御を実テーブルではなく、グローバル一時テーブルを作成するというかたちで行うと、強制的に終了した場合でも、グローバルテーブルが自動的に削除されることで排他状態ではないと容易に判断できるということになるということです。

簡単に試してみたいと思います。

★排他をかけたいとき

CREATE TABLE ##排他チェック(
        USERID          VARCHAR(20),
        処理時刻                DATETIME
        );

INSERT INTO ##排他チェック
 SELECT '00001', GETDATE();

同一のセッションで、このグローバル一時テーブルの中身を確認してみます。

SELECT * FROM ##排他チェック;

これを、全く別のセッションで異なるUSERIDのメンバーが抽出してみます。

SELECT * FROM ##排他チェック;

 ローカル一時テーブルとは異なり、他のセッションからもデータを確認できます。

ここで、元のグローバル一時テーブルを作成したセッションの接続を解除、もしくは実行していたウィンドウの右上の×の箇所をクリックして閉じてから、再度先ほど確認できた別のセッションからデータを確認してみます。

グローバル一時テーブルが接続の解除とともに自動的に削除されたために、データの抽出ができないことが確認できます。

非常に簡単なテストではありますが、このグローバル一時テーブルの存在チェックを行って、存在していれば排他中のため更新不可であり、存在しなければ排他中ではないため、処理を実行してよいという制御をある程度行うことができそうです。

例えば
IF OBJECT_ID(N'tempdb..##排他チェック', N'U') IS NOT NULL
  print '他のユーザが使用中のため、実行できません'
ELSE
  print '処理を実行します'

上記の処理を、先ほどと同様のタイミングで実行すると、最初に他のセッションでグローバル一時テーブルを作成したタイミングでは、

グローバル一時テーブルを作成したセッションの接続を解除した状態では

実際には、後から接続して処理を実行する側でも、処理の開始時点で同様に排他用のグローバル一時テーブルを作成するという処理を行うかたちになると思います。

今回は、SQL Serverについて、ローカル一時テーブルだけでなく、グローバル一時テーブルについても試してみました。

今日は以上まで

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

ラグビーのワールドカップでは、日本が24年ぶりの勝利、それも世界ランク３位の南アフリカを倒すという歴史的大金星を上げて、世界中が驚いたということがありました。開催地イギリスの地元の新聞も、「UNBELIEVABLE」」という題で大きく日本の勝利を取り上げ、会場やその近辺では日本人というだけで、「素晴らしい試合だった。おめでとう！」という祝福を受けたということです。
本当にスポーツは素晴らしいですね。

さて、マイナンバー制度の施行もいよいよ間近に迫ってきました。会社などでもいろいろと教育を受けられた方も多いと思います。ITコーディネータ協会でも、ITコーディネータ協会が進めるマイナンバーへの取組みという内容でHPで取り上げています。
http://www.itc.or.jp/mynumber/
興味のある方は、是非ご確認ください。

＜本日の題材＞
一時テーブル

ストアドプロシージャなどで複雑な処理を行うような場合に、1つのSQLで一度に結果を導き出すことは難しいため、第一段階としての抽出の結果を一時的に登録する一時テーブルを用意し、その結果を元に次のステップとしての必要な処理を行い、最終的に目的のデータを登録するとか、結果を抽出するということがよくあると思います。

この一時テーブルですが、Oracleの場合とSQL Serverの場合で多少使い方が異なります。
Oracleの場合は、セッション（デフォルトではトランザクション）が終了するとセッションの中で作成したデータは消えますが、テーブル自体は永続的なものであり、セッションが切れてもテーブルは残ります。そのため、プロシージャなどとは別に、あらかじめテーブルを作成しておく必要があります。
※デフォルト(ON COMMIT DELETE ROWS)ではトランザクションがコミットされると一時表のデータは削除されますが、設定によって(ON COMMIT PRESERVE ROWS)は、トランザクションが終了後もデータは残り、セッション終了時にデータが切り捨てられます。

これに対して、SQL Serverの場合、プロシージャなどの中で一時テーブル自体も作成し、データを登録して使うかたちで、セッションが終了するとテーブルも自動的に削除されます。
ただし、SQL Serverでは、ローカル一時テーブル（テーブル名が#で始まる）とグローバル一時テーブル（テーブル名が##で始まる）があり、一時テーブルを作成したセッションのみから参照できるローカル一時テーブルと、別セッションからも参照でき、全ての参照がなくなったときに自動的に削除されるグローバル一時テーブルという違いがあります。

Oracleの場合の一時表作成の構文：

CREATE GLOBAL TEMPORARY TABLE 表名(
  列名  データ型
, 列名  データ型
, …
[ON COMMIT DELETE ROWS | ON COMMIT PRESERVE ROWS]
);

一時テーブルの作成例：
CREATE GLOBAL TEMPORARY TABLE temp_sample1(
 CD VARCHAR2(10) PRIMARY KEY
,NAME VARCHAR2(20)
,SURYO NUMBER(7)
);

以前、ブログで使用した商品マスタ、及び売上テーブルから、2014年11月に購入した商品と数量について、一時テーブルに登録します。

INSERT INTO temp_sample1
SELECT U.商品CD, S.商品名, SUM(U.商品数量)
  FROM 売上テーブル U
  JOIN 商品マスタ S ON U.商品CD = S.商品CD
 WHERE TO_CHAR(受注日,'YYYYMM') = '201411'
 GROUP BY U.商品CD, S.商品名;

そのままデータを確認します。

SELECT * FROM temp_sample1
 ORDER BY CD;

このとき、別のユーザが異なるセッションでこの一時テーブルのデータを確認すると、

SELECT * FROM temp_sample1
 ORDER BY CD;

一時テーブルのデータは、他のセッションからは参照できないことがわかります。

また、データを登録したほうのセッションで、COMMITを実行してからSELECTしてみます。

COMMIT;

SELECT * FROM temp_sample1
 ORDER BY CD;

COMITすると、一時テーブルのデータは削除されることがわかります。

それでは、トランザクション終了後もデータが残る設定（ON COMMIT PRESERVE ROWS）の設定で一時テーブルを作成してみます。

CREATE GLOBAL TEMPORARY TABLE temp_sample2(
 CD VARCHAR2(10) PRIMARY KEY
,NAME VARCHAR2(20)
,SURYO NUMBER(7))
ON COMMIT PRESERVE ROWS
;

同様にデータを登録し、COMMITします。

INSERT INTO temp_sample2
SELECT U.商品CD, S.商品名, SUM(U.商品数量)
  FROM 売上テーブル U
  JOIN 商品マスタ S ON U.商品CD = S.商品CD
 WHERE TO_CHAR(受注日,'YYYYMM') = '201411'
 GROUP BY U.商品CD, S.商品名;

COMMIT;

データを確認してみます。

SELECT * FROM temp_sample2
 ORDER BY CD;

今度は、COMMITしてもデータは消えず、SELECTで確認することができました。
以上、まずOracleについての一時テーブルの例について取り上げてみました。

今日は以上まで

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

筑波山に行ったときに、頂上付近で撮った写真です。私ははじめてだったのですが、何度も行ったことのある知人数人と一緒に、子供たちを連れて行ってきました。
筑波山は関東地方に人が住むようになったころから、信仰の対象として仰がれてきたということで、男体山（標高871m）と女体山（標高877m）という2つの山をいざなぎ、いざなみの神として仰いできたということです。山の中腹に筑波山神社の拝殿があり、頂上付近にも写真のような本殿があります。
頂上からはまわりが一望できて、晴れていれば素晴らしい景色になります。ロープウェイもありましたが、頑張って子供たちと登りました。ただし、結構急な坂もあり、久しぶりの運動で、登るのが大変でした。あらためて年を感じさせられました。

＜本日の題材＞
UNION、UNION ALL演算子

前回、前々回と集合演算の差、積を行う演算子について見ました。順番が逆になってしまった気がしますが、今日は複数の検索結果を統合する和集合を求める演算子について見てみます。

複数のSELECT文での問い合わせの結果に対して、和集合を抽出する演算子として、UNION、UNION ALLがあります。この演算子は、Oracle、SQL Serverともに使用できます。

前回の抽出で使用したtbl_A、tbl_Bを利用して、少なくともどちらかのテーブルに存在するレコードを抽出してみます。このとき、重複するレコードは1行にまとめて表示します。
（SQL Serverで確認）

SELECT * FROM tbl_A
UNION
SELECT * FROM tbl_B;

 前々回、tbl_A、tbl_B のデータを確認していますが、たしかに、tbl_A、tbl_B のどちらかに存在するレコードが全て抽出されました。

ここで、重複する行を1つにまとめずに、そのまま抽出する場合には、「UNION ALL」句を使用します。上記の例を、UNION ALLを使用して実行すると、

SELECT * FROM tbl_A
UNION ALL
SELECT * FROM tbl_B;

 tbl_A、tbl_B のデータが重複データをまとめずに全て抽出されているのが確認できます。

※4パフォーマンスの観点から言うと、重複データを1行にまとめる処理（DISTINCT、UNION、INTERSECT、MINUSなど）は暗黙的にソート処理を行うため、その分処理が重くなり、レスポンスとしては遅くなる傾向があります。重複するデータがないということがあらかじめ分かっている場合には、和集合の場合はUNIONではなくUNION ALLを使用するようにしたほうがよいと言えます。

前々回で、同じ構造の2つのテーブルA、Bがあったとして、その2つのテーブルが完全に一致しているかどうかを確認するために、MINUS演算子を使用する例を上げましたが、一致するかどうかだけを確認したいならば、UNIONを使って確認するという方法もあります。

A, Bどちらも同じ行数のレコードが存在しているという状況がわかっていれば、下記のSQLを実行します。

SELECT COUNT(*)
  FROM
   (
    SELECT * FROM tbl_A
    UNION
    SELECT * FROM tbl_B
    ) AS tbl_C;

この結果が、A, B のテーブルのレコード数と一致していれば、A、Bは一致しているということができることになります。

ちなみに、上のサンプルデータで試した場合、以下のようになり、tbl_A、tbl_Bは一致していないことがわかります。

※Oracleでも同様の処理が可能です。

今日は以上まで

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

久しぶりに新宿のほうに仕事で行く用事があり、昼休みに新宿中央公園から都庁を撮った写真です。

話は変わりますが、先日の母の日に、子供たちが一人ひとり、妻にプレゼントをしていました。娘の一人はイヤリングのようなものをプレゼントしていたようです。まだ中学に入ったばかりの長男は、少し立派なチョコレートをあげてました。妻もまんざらではないようです。
私は妻と一緒にちょっとしたお買いものにお付き合い。妻の頑張りは大きいですよね。感謝しないと！
父の日は、何かあるかな～！

＜本日の題材＞
INTERSECT演算子

前回、集合演算の差を行う関数としての、MINUS、EXCEPT関数について見ましたが、今日はINTERSECTという、積集合（ただし、重複するデータについては1行にまとめる）を取得する演算子について見てみます。
積集合とは、与えられた集合の集まり全てに共通に含まれている要素を集めることにより得られる集合のこと。論理演算子ANDに相当し、共通集合もしくは「交わり」とも呼ばれます。

複数のSELECT文での問い合わせの結果に対して、共通の集合を重複しないかたちで抽出します。
なお、この演算子は、Oracle、SQL Serverともで使用できます。

例１）
前回の抽出で使用したtbl_A、tbl_Bを利用して、どちらのテーブルにも存在する全く同じ値のレコードを抽出してみます。
（SQL Serverで確認）

SELECT * FROM tbl_A
INTERSECT
SELECT * FROM tbl_B;

前回、tbl_A、tbl_B のデータを確認していますが、たしかに、tbl_A、tbl_B のどちらにも存在するレコードが抽出されました。

例２）
あるメーカーの3月に行ったキャンペーンに応募した顧客のデータを、「CP_応募」テーブルに登録したとします。
データは以下：
①
SELECT * FROM dbo.CP_応募
ORDER BY 顧客ID;

 次に、上記のキャンペーンに応募した顧客も含めて、4月の売上金額の多い顧客について、売上金額が1万円以上の顧客を金額の高い順に抽出した結果が以下とします。
②
SELECT
  ROW_NUMBER() OVER (ORDER BY SUM(売上金額) DESC) AS 売上順位
, 顧客ID
, SUM(売上金額) AS 売上合計
  FROM dbo.売上
 WHERE FORMAT(出荷日, 'yyyyMM') = '201504'
 GROUP BY 顧客ID
 HAVING SUM(売上金額) >= 10000
 ORDER BY SUM(売上金額) DESC;

 上記の①と②の結果で、どちらにも存在する顧客IDを抽出したいというときに、INTERSECT関数を使用してみます。

SELECT 顧客ID
   FROM dbo.CP_応募
 INTERSECT
 SELECT 顧客ID
   FROM dbo.売上
  WHERE FORMAT(出荷日, 'yyyyMM') = '201504'
  GROUP BY 顧客ID
  HAVING SUM(売上金額) >= 10000
  ORDER BY 顧客ID;

 3月のCPに応募した顧客が、4月の売上の上位（1万円以上）に5人含まれていることが確認できました。

ただ、これは「CP_応募」テーブルと「売上」テーブルを、下記SQLのように「顧客ID」でジョインして抽出するということでも同様の結果が出ます。というか、どちらかというと、ジョインを使うのが一般的なやり方かとは思われますが。

SELECT A.顧客ID
  FROM dbo.売上 A
  JOIN dbo.CP_応募 B ON A.顧客ID = B.顧客ID
 WHERE FORMAT(A.出荷日, 'yyyyMM') = '201504'
 GROUP BY A.顧客ID
 HAVING SUM(A.売上金額) >= 10000
 ORDER BY A.顧客ID;

 このINTERSECT演算子は、Oracleでも同様に使用することができます。
例１）について、Oracle環境で実施した結果は、以下になります。

ちなみに、MySQLではやはりINTERSECT演算子は対応していないようなので、EXISTS句を利用するなどして、全ての項目が一致するレコードを抽出することになります。

 今日は以上まで