外部表(2)(ORACLE)

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

img_0418

写真は、前回も載せましたが、富山の五箇山という世界遺産合掌造り集落に行ったときの写真です。

さて、とうとうブラジル・リオデジャネイロで行われていたパラリンピックも終わりました。オリンピック、パラリンピックとも、多くの感動を私たちに与えて頂いたと、選手の方々に本当に感謝します。
パラリンピックでは金メダルはなかったということですが、過去最高のメダル数を獲得。また、ちょっと話題は古くなりますが、先月のオリンピックでは、体操団体での悲願の金メダル、個人総合2連覇の内村選手の最後の大逆転は圧巻でしたね。伊調馨選手のレスリング4連覇は、女子個人競技で史上初めてのこと。卓球でも男子団体で銀メダル、女子団体も銅メダル、女子バドミントンダブルスの日本初の金メダル、本当に手に汗握って応援しました。柔道も男子は全階級でメダル、男子の陸上4*100メートルリレー銀メダル、競泳陣の活躍、数え上げればきりがないです。

次はいよいよ東京オリンピック、パラリンピック! 4年間という長い努力と訓練の期間、想像を絶する戦いの末に、栄光があるのでしょう。私たちも、道は違いますが、選手の方々に負けないよう、与えられた環境の中で精いっぱい頑張って行かなければと背中を押される気がします。
選手の皆さん、本当にお疲れ様でした。そして、感動をありがとう!

<本日の題材>
Oracleの外部表(2)

前回、ORACLEの外部表についてORACLE_LOADER型を取り上げましたが、今回は、ORACLE_DATAPUMP型というものを試してみたいと思います。ORACLE_LOADER型では、CSVファイルからの読込しかできませんが、ORACLE_DATAPUMP型では、ダンプファイルにデータを書き込んだり、読込んだりすることができます。

例)
商品売上という、商品コード毎、年度毎の各月の売上金額を集計したテーブルがあり、そこに、2014年、2015年、2016年のデータが登録されているとします。
テーブルの定義は以下のようになっています。
DESC 商品売上

desc_%e5%95%86%e5%93%81%e5%a3%b2%e4%b8%8a

データとしてはこんな感じ。

%e5%95%86%e5%93%81%e5%a3%b2%e4%b8%8a%e3%83%87%e3%83%bc%e3%82%bf

 年度毎の件数は、
SELECT 年度, COUNT(*) FROM 商品売上
GROUP BY 年度
ORDER BY 年度;

%e5%b9%b4%e5%ba%a6%e3%81%94%e3%81%a8%e4%bb%b6%e6%95%b0

サンプルデータなので件数は少ないです。
それで、今年度(2016)のデータだけ残して、過去の年度のデータは、テーブルとしては持たないでダンプファイルに保存し、必要なときには外部表としてアクセスするようにしたいと思います。

まず、2014年のデータの外部表を作成し、外部表のダンプファイルにデータを登録します。

CREATE TABLE SYO_URIAGE_2014
ORGANIZATION EXTERNAL
(
  TYPE ORACLE_DATAPUMP
  DEFAULT DIRECTORY temp_dir
  LOCATION ('uriage_2014.dmp')
)
AS SELECT * FROM 商品売上
    WHERE 年度 = '2014';

syo_uriage_2014

 次に、同様にして、2015年のデータの外部表を作成します。

CREATE TABLE SYO_URIAGE_2015
ORGANIZATION EXTERNAL
(
  TYPE ORACLE_DATAPUMP
  DEFAULT DIRECTORY temp_dir
  LOCATION ('uriage_2015.dmp')
)
AS SELECT * FROM 商品売上
    WHERE 年度 = '2015';

syo_uriage_2015

実際に、ダンプファイルが指定のフォルダに作成されていることを確認してみます。今回ディレクトリに指定した「temp_dir」は、前回のブログで作成したように、「C:\temp」フォルダになりますので、エクスプローラで確認すると、

temp_dir

確かに、「URIAGE_2014.DMP」「URIAGE_2015.DMP」ファイルが作成されていました。

それぞれ作成した外部表のデータ件数を確認してみます。
SELECT COUNT(*) FROM SYO_URIAGE_2014;

syo_uri_2014_count

SELECT COUNT(*) FROM SYO_URIAGE_2015;

syo_uri_2015_count

元の商品売上テーブルからは、過去の年度のデータは削除し、2016年度のデータのみを残すことにします。

DELETE FROM 商品売上
WHERE 年度 < '2016';

del_%e5%95%86%e5%93%81%e5%a3%b2%e4%b8%8a

 処理の上で、2016年度のデータを見ればよい場合は「商品売上」テーブルに対してSELECTを実施しますが、過去のデータも見たい場合には、外部表を使うことで検索できます。

SELECT 商品CD, 年度, 売上4月, 売上5月, 売上6月, 売上7月, 売上8月, 売上9月
  FROM 商品売上
 WHERE 商品CD = 'A0001'
UNION
SELECT 商品CD, 年度, 売上4月, 売上5月, 売上6月, 売上7月, 売上8月, 売上9月
  FROM SYO_URIAGE_2015
 WHERE 商品CD = 'A0001'
UNION
SELECT 商品CD, 年度, 売上4月, 売上5月, 売上6月, 売上7月, 売上8月, 売上9月
  FROM SYO_URIAGE_2014
 WHERE 商品CD = 'A0001'
ORDER BY 年度;

%e5%a4%96%e9%83%a8%e8%a1%a8_union

 また、上記でユーザ「BLOG」によって作成された外部ファイル(ダンプファイル)を、別のユーザで使用するようなこともできます。

今回、「BLOG2」というユーザを作成し、「temp_dir」をBLOG2ユーザからも読み書きできるようにします。

CREATE USER BLOG2
IDENTIFIED BY< password>    -- パスワードを設定します
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
QUOTA UNLIMITED ON USERS;

GRANT READ, WRITE ON DIRECTORY temp_dir TO BLOG2;

grant_read_write_blog2

作成したユーザー「BLOG2」でログインし、上記で作成した2015年度の商品売上データのダンプファイル(uriage_2015.dmp)を元にBLOG2ユーザ用の外部表「SYO_URIAGE2_2015」を作成してみます。

  CREATE TABLE SYO_URIAGE2_2015
   ("商品CD" VARCHAR2(20 BYTE),
              "年度" VARCHAR2(4 BYTE),
              "売上4月" NUMBER(12,0),
              "売上5月" NUMBER(12,0),
              "売上6月" NUMBER(12,0),
              "売上7月" NUMBER(12,0),
              "売上8月" NUMBER(12,0),
              "売上9月" NUMBER(12,0),
              "売上10月" NUMBER(12,0),
              "売上11月" NUMBER(12,0),
              "売上12月" NUMBER(12,0),
              "売上1月" NUMBER(12,0),
              "売上2月" NUMBER(12,0),
              "売上3月" NUMBER(12,0)
   )
   ORGANIZATION EXTERNAL
    ( TYPE ORACLE_DATAPUMP
      DEFAULT DIRECTORY TEMP_DIR
      LOCATION
       ( 'uriage_2015.dmp'
       )
    );

 %e5%a4%96%e9%83%a8%e8%a1%a8%e4%bd%9c%e6%88%902

作成した外部表を検索してみます。

SELECT 商品CD, 年度, 売上4月, 売上5月, 売上6月, 売上7月, 売上8月, 売上9月
 FROM SYO_URIAGE2_2015
ORDER BY 商品CD;

select_%e5%a4%96%e9%83%a8%e8%a1%a82

 データの検索ができました。
上記のように、一度作成した外部ファイル(ダンプファイル)は、別のユーザや別のデータベース環境に移動してそこで使用することも可能なんですね。
さらに、PREPROCESSOR句を使用すると、外部ファイルを圧縮したファイルに対して解凍しながら使用するということも可能なようです。

(※)注意点として、DATAPUMP(expdp)で作成したdmpファイルは外部表のダンプファイルとしては使用できないようです。

 今日は以上まで

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

外部表(ORACLE)

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

IMG_0441

夏休みに、妻の実家に行って来ました。長女だけは仕事の都合がつかず、一緒に行けなかったのが残念でしたが、あとの子供たちを引き連れて行ってきました。また、実家からそれほど遠くはないところに、世界遺産があるということで、そこに行ってきたのが、上の写真です。

ここは世界遺産合掌造り集落といって、岐阜県の白川郷・荻町集落、富山県の五箇山・菅沼集落、五箇山・相倉集落があり、今回行ってみたのは五箇山・菅沼集落です。東海北陸自動車道の五箇山インターチェンジからわりとすぐのところにあり、合掌造りの家屋が9棟くらいの3つの集落では一番小さな集落です。
そこに行くのに、駐車場のあるところからはエレベータで降りて行くのですが、別世界というか、昔にタイムスリップしたような不思議な感覚になりました。屋根がとても高く独特で、両手を合わせたような形になっていることから、合掌造りと呼ばれるようになったと思われるとのことです。
1軒1軒がとても大きかったですね。妻や子供たちも感動しながら、いっぱい写真を撮ってました。
それなりに多くの観光客(外国の方も結構いました)の人も来ていて、結構人気があるんですね。できれば、白川郷や相倉集落など他の集落も見て見たかったのですが、時間も遅くなってしまいそうだったので、菅沼集落だけを見て帰りました。
今度時間があれば、また妻と一緒に行ってみようっと!

IMG_0412_

 <本日の題材>
Oracleの外部表

ORACLEの場合に、テーブルにCSVファイル用のようなデータを読込む処理にSQL*Loaderというツールがあります(前回の投稿の中で、ちょこっと使っています)が、実際のテーブルには読込まずに、あたかもテーブルが存在するかのようにそういった外部ファイルにアクセスすることができるツールとして、外部表というものがあります。今回はそれを取り上げてみたいと思います。

まず、準備として、外部表によって読取りまたは書込みが行われるファイルのデフォルトの位置としてのディレクトリを作成します。以前、UTL_FILEパッケージについて取り上げたときに、ディレクトリの作成を行いましたが、今回も同様に設定します。

構文:
CREATE [OR REPLACE] DIRECTORY <
ディレクトリ名>
 AS ‘<
ディレクトリ・パス>

) SYSDBA権限でログインし、ディレクトリを作成します。

CREATE OR REPLACE DIRECTORY temp_dir
AS 'C:\temp';

create_directory

※このディレクトリは、サーバ上のディレクトリになります。

実行ユーザ「BLOG」にディレクトリに対する読込権限と書き込み権限を付与します。

GRANT READ, WRITE ON DIRECTORY temp_dir TO blog;

grant_read_write

 外部表の作成は、CREATE TABLE...ORGANIZATION EXTERNAL文を使用します。
今まで何度か使用している商品マスタについて、その内容をCSVファイルにしたものを外部表としてアクセスしてみたいと思います。

例)
CREATE TABLE syomst_load
  (syo_cd         VARCHAR2(10),
   syo_name   VARCHAR2(20),
   bnrui             VARCHAR2(20),
   price             NUMBER(10))
ORGANIZATION EXTERNAL
  (TYPE ORACLE_LOADER
   DEFAULT DIRECTORY temp_dir
   ACCESS PARAMETERS
     (RECORDS DELIMITED BY NEWLINE
      FIELDS TERMINATED BY ','
             (syo_cd,
              syo_name,
              bnrui,
              price
             )
     )
   LOCATION ('syomst.txt')
  )
REJECT LIMIT UNLIMITED;

cre_外部表

 上記のSQL文にあるように、temp_dir というディレクトリ(実際は、c:\temp )に、「syomst.txt」 というカンマ区切りの以下のようなCSVファイルを置いておきます。

A0001,チョコレート,お菓子,120
A0002,ビスケット,お菓子,200
A0003,ガム,お菓子,100
A0004,スナック,お菓子,140
B0001,りんご,果物,100
B0002,桃,果物,160
B0003,みかん,果物,80
B0004,梨,果物,120
C0001,キャベツ,野菜,160
C0002,にんじん,野菜,150
C0003,じゃがいも,野菜,100
C0004,玉ねぎ,野菜,150

ここまでの設定で、以下のSQLを実行すると、CSVファイルのデータを、あたかもテーブルが既に存在するかのように表示することができます。

SELECT * FROM syomst_load;

select_外部表

CSVデータの中身を変更してみます。
1行目と2行目のPRICEを130,210と変更して、CSVファイルを保存します。その後、再度先ほどのSQL文を実行します。

SELECT * FROM syomst_load;

select_外部表変更後

結果は、たしかに、1行目の金額が130円、2行目が210円に変わっていることが確認できました。

上記は、カンマ区切りのCSVファイルでしたが、当然固定長のファイルでも可能です。
また今回は、CREATE TABLE のときの外部表の型として、ORACLE_LOADER型を使用しましたが、もう一つ、ORACLE_DATAPUMP型というのもあります。ORACLE_LOADER型では、CSVファイルからの読込しかできませんが、ORACLE_DATAPUMP型では、ダンプファイルを使用しての処理が可能となります。これについては、また機会があれば、試してみたいと思います。

今日は以上まで

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

テーブルの項目数、レコード長の制限

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

IMG_0301

先日、会社の同僚の誘いで海釣りに行く機会がありました。写真は、そのときにボートから撮った写真です。小学生の息子も連れて行って来ました。ボートに乗る30分から1時間くらい前には酔い止めの薬を飲んで(妻から、忘れちゃダメと何度も言われたので、そこはしっかりとやって)、いよいよ乗船! 

最初のポイントでは、キスとかが釣れたらと思っていたところ、私も息子も同じく得体のしれない20cm超の魚をゲット! 釣れたのはたぶん「オキエソ」と言われるあまりかっこうのよくない魚。知人が「外道」が釣れたねと言っていたので、すごい名前の魚もいるものだと思いましたが、実は、狙っていた魚ではないとき、外道が釣れたと言うんですね!

次のポイントの岩場で、息子はカサゴをゲット。私も結構な引きがあったので、もう少しでカサゴが釣れそうだったけれど、リールを巻いているときに逃げられてしまい、とても残念。
息子は初めての海釣りの経験だったのですが、なかなか釣れない中、頑張っていました。その日は潮時としては「小潮」と言われるときで、魚のエサの捕食行動が鈍り、釣りにくい日だったようで、確かに全体的に釣れなかったですね。
それでも、いい経験ができてよかったです。家に帰ったら、釣った魚を妻に料理してもらって、その日はおいしい魚料理でした。

オコゼ  アイソメ

<本日の題材>
テーブルの項目数、レコード長の制限

仕事でEXCELを使って様々な業務を行う場合に、列数がものすごく多いシートを扱わざるを得ないケースが出てきますよね。そのようなEXCELのシートをそのまま使ってWebシステム化するためのツールとして、dbSheetClientというものがあることは、以前ご紹介したと思います。
http://www.newcom07.jp/dbsheetclient/dbsclan/index.html

そのソフトを使って開発する際に、EXCELの内容をDBと連結するための設計を行うのですが、たくさんの列数をそのままのかたちでデータベースに持たせたいと思う場合もときにあります。
そんなときに、1つのテーブルに持てる列数っていくつまでなんだろう? 1レコードに持てるレコード長に制限はあるのだろうか? などということを考える場合があるので、今回その内容を取り上げてみます。

SQL Serverの場合、調べてみると、通常のテーブルでは、列数は1024まで、行ごとの最大のバイト数は8060バイトとなっています。
https://msdn.microsoft.com/ja-jp/library/ms143432.aspx

それでは、実際に1024個の列を持つテーブルを作成してみます。

CREATE TABLE test_col_max(
    col_001key VARCHAR(10) NOT NULL
  , col_002 VARCHAR(10)
  , col_003 VARCHAR(10)
  , col_004 VARCHAR(10)
  , col_005 VARCHAR(10)
  , col_006 VARCHAR(10)
  , col_007 VARCHAR(10)
.....  途中省略
  , col_1020 VARCHAR(10)
  , col_1021 VARCHAR(10)
  , col_1022 VARCHAR(10)
  , col_1023 VARCHAR(10)
  , col_1024 VARCHAR(10)
  , CONSTRAINT PK_test_col_max PRIMARY KEY (col_001key)
) ;
列数1024

 テーブルは作成できました。
ただし、以下のような警告が表示されます。
「警告: 行の最大長が許容制限の 8060 バイトを超えています。一部の大きな値の組み合わせでは、挿入操作または更新操作が失敗します。」。これは、各列のタイプを VARCHAR(10)としたので、各列にMAXで値を詰め込んだ場合、10 * 1000 = 10000 バイトになってしまうので、このような警告が出るということです。

それでは、列数が 1025個ではテーブル作成がどうなるか試してみます。CREATE TABLE文で項目を1つ追加して、再度作成を実行してみると、下記のようなエラーが出て作成できません。
列数1025エラー

 エラーメッセージは、「CREATE TABLE が失敗しました。テーブル 'test_col_max' の列 'col_1025' が、列の最大数 1024 列を超えています。」。やはり、1024列までしか作成できないことが確認できました。(これは、一時テーブルでも同じ)

では、1024列までの項目でテーブルの作成まではできたとしても、行サイズのトータルが8060バイトを超える場合はどうなるでしょうか? それも試してみます。

まずは、8060バイトを超えない場合として、EXCELで作成したCSVファイルを、1024個の列を持つ「test_col_max」テーブルに登録してみます。前々回、BCPコマンドを紹介しましたが、今回は、BULK INSERTコマンドで登録してみます。各列に「1」、「2」など、1文字のみを設定したCSVファイル(サンプルとして3行)を読込んでみます。

BULK INSERT dbo.test_col_max
FROM 'c:\WORK\blog\blog_1024.csv'
WITH
(
   FIELDTERMINATOR = ',',
   ROWTERMINATOR = '\n'
);

bulk_insert

登録できました。データを確認してみます。

SELECT * FROM dbo.test_col_max
 ORDER BY col_001key;

列数1024_確認

データが3行作成されているのが確認できます。
次に、各列に「1000000000」など10文字ずつの値が設定されたcsvファイルを読込んでみます。

先ほどと同様に、BULK INSERTを実行してみると、下記のようなエラーが発生してデータを取込めません。

行バイト数超過エラー

 これは、行の合計のレコード長が10 * 1024 = 10240 バイトとなり、8060バイトを超えてしまっているためです。

なお、テーブルの列数の制限は、1024ですが、SELECT文での列数の最大値は 4096ということですので、以前取り上げた縦横変換などで、縦のデータを横に項目を並べて抽出する場合など、SELECT文で多くの項目を抽出する場合、1024を超えて4096列まで行うことができるということです。

<ORACLEの場合>
ちなみに、ORACLEではどうかというと、1つのテーブルに持てる最大列数は、1000列です。
上記と同様に、1001列を持つテーブルを作成しようとすると、下記のようにエラーになります。

oracle_行するMAX

 「ORA-01792:表またはビューに指定できる最大列数は1000です。」というエラーが出て、テーブルを作成できません。

上記から1列減らして1000列を持つテーブルを作成しようとすると、下記のように無事作成することができました。

oracle_retu

ここで、SQL Serverでは行の合計サイズの制限のためエラーとなった、各項目が全て10バイトまで詰まったCSVファイルを取込んでみます。ORACLEのSQL*Loaderというツールで行ってみます。
制御ファイルは以下のような感じ:

oracle_sqlldr_ctl

(項目が多いので、上記の制御ファイルの画面コピーは、途中で項目が切れてます。)

それでは実行してみます。
sqlldr userid=blog control=blog.ctl

ora_sqlldr_exec

問題なく取り込めました。結果を確認してみます。
SELECT * FROM TEST_COL_MAX
ORDER BY COL_001KEY;

ora_col1000

1000個のすべての列に、10バイトずつ登録されたデータが確認できます。

ちなみに、ORACLEの場合は、行の合計レコード長が 8060バイトのような制限は特にはなく、例えば全ての項目がVARCHAR2型であれば、1項目で4000バイトまで持てるので、1000列 * 4000 バイト = 4000000 バイトまでは持てることになりますね。

※SQL Serverでも SQL Server 2005から、大きな値のデータ型とよばれる、varchar(max)、nvarchar(max)型というものが機能として増えています。これについては、別の機会に取り上げてみたいと思います。

 今日は以上まで

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

テーブル値関数(SQL Server)

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

IMG_0250

お久しぶりです。ここのところ、仕事のほうがなかなか忙しく、ブログをアップすることができませんでした。写真は、皇居の平川門を撮ったものです。東西線竹橋駅のすぐそばにあり、たまたま仕事で近くまで行ったので行ってみたところ、平川橋という橋を渡ると警備の方がいらっしゃいましたが、中に入れるということで、寄ってみました(皇居東御苑は、火曜日~木曜日なら年末年始以外なら基本的にはいつでも公開しているとのこと)。時間がなくてすぐに出てきてしまいましたが、テレビで見たことのあるようなお堀があって、季節によっては花とかとてもきれいではないかと感じました。外国の方も結構見にいらっしゃっていましたね。

ずっと前、学生の頃に、正月1月2日の新年の一般参賀に一度皇居に行ったことがあり、おぼろげにそのときのことを覚えていますが、それ以外は一度も行ったことがなかったです。普通の日でも入れるんですね~。今度休暇でもとって、ゆっくりと歩いてみたいな~!(嫁を誘ってみる?)

ちなみに平川門とは、江戸城の裏門で、大奥に最も近く、奥女中の通用門であったことから「お局門」とも呼ばれていたそうです。また、平川門の脇に小さな門があって、城内で罪人や死人が出るとこの門から出されたので「不浄門」ともいわれていたとのこと。
うんちくネタになるかな!

IMG_0254 皇居側から見た平川橋


<本日の題材>
テーブル値関数(SQL Server)

今回は、テーブル値関数を取り上げてみたいと思います。私も今まで使ったことはなかったのですが、今回途中から応援に入ったプロジェクトでは、SQL Serverを利用していましたが、テーブル値関数がよく使われていて、結構便利なものだと感じました。
どういうものか調べてみると、ユーザー定義テーブル値関数と呼ばれ、ストアドプロシージャのように様々なロジックを組めますが、結果としてtableデータ型の値を返すことができ、ビューに変わる強力なツールになるようです。

例1)まず、サンプルとして適当かどうか?というのはありますが、こういう使い方もあるということで、指定した日から1ヶ月(31日間)の日付と曜日をそれぞれ1行目、2行目に表示しつつ、日付を横並びに表示するためのものを作成してみます。

CREATE FUNCTION dbo.ufn_calender(
        @p開始日                datetime
)
RETURNS TABLE
AS
RETURN(
SELECT
        1 表示順,N'日付'表示名
        ,CONVERT(VARCHAR(10),@p開始日+0,111) 日付1
        ,CONVERT(VARCHAR(10),@p開始日+1,111) 日付2
        ,CONVERT(VARCHAR(10),@p開始日+2,111) 日付3
        ,CONVERT(VARCHAR(10),@p開始日+3,111) 日付4
        ,CONVERT(VARCHAR(10),@p開始日+4,111) 日付5
        ,CONVERT(VARCHAR(10),@p開始日+5,111) 日付6
        ,CONVERT(VARCHAR(10),@p開始日+6,111) 日付7
        ,CONVERT(VARCHAR(10),@p開始日+7,111) 日付8
        ,CONVERT(VARCHAR(10),@p開始日+8,111) 日付9
        ,CONVERT(VARCHAR(10),@p開始日+9,111) 日付10
        ,CONVERT(VARCHAR(10),@p開始日+10,111) 日付11
        ,CONVERT(VARCHAR(10),@p開始日+11,111) 日付12
        ,CONVERT(VARCHAR(10),@p開始日+12,111) 日付13
        ,CONVERT(VARCHAR(10),@p開始日+13,111) 日付14
        ,CONVERT(VARCHAR(10),@p開始日+14,111) 日付15
        ,CONVERT(VARCHAR(10),@p開始日+15,111) 日付16
        ,CONVERT(VARCHAR(10),@p開始日+16,111) 日付17
        ,CONVERT(VARCHAR(10),@p開始日+17,111) 日付18
        ,CONVERT(VARCHAR(10),@p開始日+18,111) 日付19
        ,CONVERT(VARCHAR(10),@p開始日+19,111) 日付20
        ,CONVERT(VARCHAR(10),@p開始日+20,111) 日付21
        ,CONVERT(VARCHAR(10),@p開始日+21,111) 日付22
        ,CONVERT(VARCHAR(10),@p開始日+22,111) 日付23
        ,CONVERT(VARCHAR(10),@p開始日+23,111) 日付24
        ,CONVERT(VARCHAR(10),@p開始日+24,111) 日付25
        ,CONVERT(VARCHAR(10),@p開始日+25,111) 日付26
        ,CONVERT(VARCHAR(10),@p開始日+26,111) 日付27
        ,CONVERT(VARCHAR(10),@p開始日+27,111) 日付28
        ,CONVERT(VARCHAR(10),@p開始日+28,111) 日付29
        ,CONVERT(VARCHAR(10),@p開始日+29,111) 日付30
        ,CONVERT(VARCHAR(10),@p開始日+30,111) 日付31
UNION ALL SELECT
        2,N'曜日'
        ,LEFT(DATENAME(dw,@p開始日+0),1)
        ,LEFT(DATENAME(dw,@p開始日+1),1)
        ,LEFT(DATENAME(dw,@p開始日+2),1)
        ,LEFT(DATENAME(dw,@p開始日+3),1)
        ,LEFT(DATENAME(dw,@p開始日+4),1)
        ,LEFT(DATENAME(dw,@p開始日+5),1)
        ,LEFT(DATENAME(dw,@p開始日+6),1)
        ,LEFT(DATENAME(dw,@p開始日+7),1)
        ,LEFT(DATENAME(dw,@p開始日+8),1)
        ,LEFT(DATENAME(dw,@p開始日+9),1)
        ,LEFT(DATENAME(dw,@p開始日+10),1)
        ,LEFT(DATENAME(dw,@p開始日+11),1)
        ,LEFT(DATENAME(dw,@p開始日+12),1)
        ,LEFT(DATENAME(dw,@p開始日+13),1)
        ,LEFT(DATENAME(dw,@p開始日+14),1)
        ,LEFT(DATENAME(dw,@p開始日+15),1)
        ,LEFT(DATENAME(dw,@p開始日+16),1)
        ,LEFT(DATENAME(dw,@p開始日+17),1)
        ,LEFT(DATENAME(dw,@p開始日+18),1)
        ,LEFT(DATENAME(dw,@p開始日+19),1)
        ,LEFT(DATENAME(dw,@p開始日+20),1)
        ,LEFT(DATENAME(dw,@p開始日+21),1)
        ,LEFT(DATENAME(dw,@p開始日+22),1)
        ,LEFT(DATENAME(dw,@p開始日+23),1)
        ,LEFT(DATENAME(dw,@p開始日+24),1)
        ,LEFT(DATENAME(dw,@p開始日+25),1)
        ,LEFT(DATENAME(dw,@p開始日+26),1)
        ,LEFT(DATENAME(dw,@p開始日+27),1)
        ,LEFT(DATENAME(dw,@p開始日+28),1)
        ,LEFT(DATENAME(dw,@p開始日+29),1)
        ,LEFT(DATENAME(dw,@p開始日+30),1)
);

7月の日付について試したいので、パラメータとして「2016/07/01」を渡して抽出してみます。

SELECT * FROM [dbo].[ufn_calender]('2016/07/01')
ORDER BY 表示順;

結果は以下のようになります。

blog65_ufn_calender結果

カレンダーのように、日付が横並びになり、2行目に曜日が表示されますね。

他の使い方として、最初にテーブルの定義を指定して、その後いろいろな処理や条件を組込みながら、その定義にあった結果をINSERTして、それを利用するようなやり方があります。

例2)以前も使用したことがある売上テーブルについて、指定した年月の商品別の売上金額の高いものからランクをつけながら抽出する処理を行うテーブル値関数を作成してみます。

CREATE FUNCTION dbo.ufn_商品CD別月次出荷
(
        @p出荷年月      VARCHAR(6)
) RETURNS @月次出荷 TABLE (
        出荷年月 VARCHAR(6),
        ランキング DECIMAL(3,0),
        商品CD NVARCHAR(20),
        商品名 NVARCHAR(20),
        出荷数量 INT,
        売上金額 DECIMAL(10,0)
)
AS
BEGIN
        INSERT @月次出荷
        SELECT
                 FORMAT(U.受注日, 'yyyyMM')
                ,RANK() OVER (ORDER BY SUM(U.売上金額) DESC)
                ,U.商品CD
                ,S.商品名
                ,SUM(U.商品数量)
                ,SUM(U.売上金額)
        FROM dbo.売上テーブル U
        JOIN dbo.商品マスタ S ON U.商品CD = S.商品CD
        WHERE FORMAT(U.受注日, 'yyyyMM') = @p出荷年月
        GROUP BY FORMAT(U.受注日, 'yyyyMM'), U.商品CD, S.商品名;
       
        RETURN;
END;

このテーブル値関数を使って、2016年05月の売上について、商品別の売上金額を高い順に表示します。

SELECT * FROM dbo.ufn_商品CD別月次出荷('201605')
ORDER BY ランキング;

商品別出荷金額_テーブル値関数

ストアドプロシージャのようにいろいろな処理を組み込んで、テーブルのかたちで結果を返すことができるので、使い方によっては結構便利かと思います。今まで、ワーク用のテーブルを用意して、そこに一旦データを登録してから処理をしていたような場合でも、ワークのテーブルを作成せずに、テーブル値関数を使って対応するようなことも可能な場合もあると思います。

ちなみに、ORACLEでも、同じように使用することができるユーザー定義レコードというようなものが結構以前からあるようですね。

今日は以上まで

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

bcpユーティリティ(SQL Server)

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

IMG_0218

お久しぶりです。あっという間にゴールデンウィークも終盤になってしまいました。写真は、去年も行った加須市玉敷神社の騎西藤まつりに行ったときの写真です。きれいでしたね!

話しは変わりますが、GW中に息子を連れて近くの温水プールに行ったところ、プロのインストラクターと思われるお姉さんが現れて、これからエクササイズをします!とのこと。プールに来ていた大半の方(おばちゃんやおばあちゃんが多かったけど)が参加するようだったので、ものは試しにと一緒に参加しました。そうしたら、みっちり1時間、水の中でステップを踏んだり手足をいろいろと動かしてのエクササイズ、結構疲れました!! 体力無いな~。 他の方は慣れているのか、おばちゃんやおばあちゃんが元気にこなしているのを見ると、すごいというか、女性は強し。自分ももっと体力を付けないと!と、強く感じさせられました。

<本日の題材>
Bcpユーティリティ(SQL Server)

前回、ORACLEのUTL_FILEパッケージを取り上げました。PL/SQLでテキストファイルの読み書きができるというものですが、SQL ServerのT-SQLで同様のことができるかどうかを調べてみたところ、xp_cmdshellというシステム拡張ストアドプロシージャを使用して、bcpコマンドなどを呼び出してテーブルのデータをテキストファイルへ書き出したり、テキストファイルのデータをテーブルに読込んだりすることは可能という情報がありました。ただし、このストアドプロシージャxp_cmdshellは様々なOSコマンドを実行できるということから、セキュリティ的にはとても危険だということで、デフォルトでは無効となっています。これを有効にする方法もあるとのことですが、それは次の機会に回すとして、今回は、bcpコマンドのほうを取り上げてみたいと思います。

BCPユーティリティでは、テーブルやビューのデータをテキストに出力する、テキストデータをテーブルへ取込むなど、一般にエクスポート/インポートと言われる内容ができますが、queryoutオプションを使用すると、指定したクエリーの結果を出力することが可能になります。

構文は、以下:

bcp [database_name.] schema.{table_name | view_name | "query" {in data_file | out data_file | queryout data_file | format nul}
   [-a packet_size]
   [-b batch_size]
   [-c]
   [-C { ACP | OEM | RAW | code_page } ]
   [-d database_name]
   [-e err_file]
   [-E]
   [-f format_file]
   [-F first_row]
   [-h"hint [,...n]"]
   [-i input_file]
   [-k]
   [-K application_intent]
   [-L last_row]
   [-m max_errors]
   [-n]
   [-N]
   [-o output_file]
   [-P password]
   [-q]
   [-r row_term]
   [-R]
   [-S [server_name[\instance_name]]
   [-t field_term]
   [-T]
   [-U login_id]
   [-v]
   [-V (80 | 90 | 100 | 110)]
   [-w]
   [-x]

※パラメータはアルファベット順。

ちなみに、コマンドプロンプト画面で、bcp /? と実行すると以下のような画面が出ます。

bcpヘルプ

構文の上記のパラメータを全て説明するのはとても大変なので、詳細はMicrosoft社のコマンドの説明のHPに任せて、サンプルを使ってよく使う箇所について説明したいと思います。

1)テーブルやビューのデータをテキストファイルに出力する

※SQL ServerにはWindows認証でアクセスするとします。Windows認証の場合は -T を使用します。BLOGデータベースの「商品マスタ」のデータを、「c:\temp」ディレクトリに「商品マスタ.txt」という名前のファイルにカンマ区切りで出力します。

bcp BLOG.dbo.商品マスタ out "C:\temp\商品マスタ.txt" -c -t, -S サーバ名 –T

bcp_ファイル出力

作成されたテキストファイルを確認してみると、

more_bcptxt

商品マスタテーブルのデータが出力されています。
カンマ区切りではなく、カンマの代わりに空白で試してみると

bcp BLOG.dbo.商品マスタ out "C:\temp\商品マスタ2.txt" -c –t  -S サーバ名 –T

bcp_ファイル出力2

 作成された中身を確認すると、

more_bcptxt2

たしかにカンマ区切りではなく、空白で区切られていることが確認できました。

※ちなみに、SQL Server認証では、-T ではなく、-U ユーザ名 –P パスワードとなります。

2)テキストファイルのデータをテーブルに取込む

先ほど出力したテキストファイルをテーブルに読込んでみます。
まず、商品マスタテーブルのデータをtruncateコマンドで削除します。

bcp_truncate

次に、bcpコマンドでテキストファイルを読込みます。パラメータとして、out ではなく in を使います。

bcp BLOG.dbo.商品マスタ in "C:\temp\商品マスタ.txt" -c -t, -S サーバ名–T

bcp_ファイル入力

 テーブルの中身を確認してみると、
SELECT * FROM dbo.商品マスタ;

bcp_商品マスタ

先ほどデータを削除した後、bcpコマンドによってテキストファイルからデータが取込まれたことが確認できました。

3)テーブルやビューから、指定したクエリで取得したデータをテキストファイルに出力する

今度は、テーブルやビューのデータを全て出力するのではなく、指定した条件でSELECTした結果をテキストファイルに出力する場合です。
これは、bcpコマンドの後に、”で囲んだSELECT文を記載し、パラメータとして out ではなく queryout を使用します。例として、商品マスタの分類が「果物」のデータをテキストファイルに出力してみます。

bcp "SELECT 商品CD, 商品名, 分類, 値段 FROM BLOG.dbo.商品マスタ WHERE 分類='果物'" queryout "C:\temp\商品マスタ3.txt" -c -t, -S サーバ名 –T

bcp_select出力

作成されたテキストファイルを確認します。

more_bcptxt3

指定したSELECT文の条件通りの結果がテキストファイルに出力されていることが確認できました。

今日は以上まで

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

UTL_FILEパッケージ

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

DSC_2634_2

4月に入り、学校や多くの職場で年度が新しくなって、心機一転出発された方も多いと思います。
我が家の長女も、専門学校を卒業して、新しく社会人として出発しました。また、娘の一人は高校受験を何とかクリアして、高校生として出発しました。
自分のその当時のことを思い出しながら、子供たちもひとりひとり、人生の様々な試練や関門にぶつかりながら、少しずつ成長していく姿を見ると、感慨深いものがあります。

<本日の題材>
UTL_FILEパッケージ

前回、前々回と、ORACLEのパッケージについて取り上げました。今回もその続きで、ORACLE側で用意されているユーティリティ・パッケージの一つである、UTL_FILEパッケージについて見てみたいと思います。

UTL_FILEパッケージを使うことで、PL/SQLでOSのテキストファイルの読み書きができます。
そのためには、あらかじめアクセス可能なディレクトリを設定しておかなければなりません。この、ディレクトリ・オブジェクトの作成は、管理ユーザで行い、その後、そのディレクトリの読み書きを行う権限を、実行するユーザに与える必要があります。

構文:
CREATE [OR REPLACE] DIRECTORY <ディレクトリ名>
 AS ‘<ディレクトリ・パス>’

例) SYSDBA権限でログインし、ディレクトリを作成します。

CREATE OR REPLACE DIRECTORY temp_dir
AS 'C:\temp';

create_directory

※このディレクトリは、サーバ上のディレクトリになります。

UTL_FILEパッケージを使ってファイルを読み書きするユーザ「BLOG」にディレクトリに対する読込権限と書き込み権限を付与します。

GRANT READ, WRITE ON DIRECTORY temp_dir TO blog;

grant_read_write

 準備はできましたので、「商品マスタ(syomst)」のデータを、C:\temp ディレクトリ上に、「syomst.txt」という名前のテキストファイルに出力して保存するというプロシージャを、UTL_FILEパッケージを使って作成してみます。

CREATE OR REPLACE PROCEDURE file_output_syomst
IS
  CURSOR cur_syomst IS
     SELECT
       syo_cd||','||syo_name||','||bnrui||','||price||','||seqno AS syomst_data
       FROM syomst
      ORDER BY syo_cd;

  --ファイル・ハンドルを受け取る変数の定義(1)
   write_file  UTL_FILE.FILE_TYPE;

BEGIN
  --ファイルのオープン(2) (パラメータとして、ディレクトリ、ファイル名、オープンモード)
   write_file := UTL_FILE.FOPEN('TEMP_DIR', 'syomst.txt', 'a');
  --カーソルのループ
   FOR syomst_rec IN cur_syomst LOOP
   --データを1行ずつファイルに書き込む(3)
     UTL_FILE.PUT_LINE(write_file, syomst_rec.syomst_data);
   END LOOP;
  --ファイルのクローズ(4)
  UTL_FILE.FCLOSE(write_file);

EXCEPTION
 WHEN OTHERS THEN
   DBMS_OUTPUT.PUT_LINE('SQLCODE');
   DBMS_OUTPUT.PUT_LINE('SQLERRM');
   UTL_FILE.FCLOSE(write_file);
END;
/

使い方としては、まず、ファイル操作を行うためにはファイルごとにファイル・ハンドルというものが必要であり、それを格納する変数の定義を、UTL_FILEパッケージのFILE_TYPE型で定義します(1)。
その次に、FOPENファンクションを使用してファイルをオープンします。このFOPENファンクションは、戻り値としてファイルハンドルを戻します。構文は以下です。

UTL_FILE.FOPEN (
   location     IN VARCHAR2
  ,filename     IN VARCHAR2
  ,open_mode    IN VARCHAR2
   [,max_linesize IN BINARY_INTEGER]
)
  RETURN file_type;

各パラメータについて説明します。
 ・location:
 ファイルのディレクトリ位置。この文字列はディレクトリのオブジェクト名で、大/小文字が区別される。デフォルトは大文字。ユーザーがFOPENを実行するには、このディレクトリに対する読取り権限が付与されている必要がある。

・filename: 拡張子(ファイル・タイプ)も含めたファイル名。
・open_mode: ファイルのオープン方法を指定
    r -- テキストの読込み
    w -- テキストの書込み
    a -- テキストの追加
    rb -- バイトの読込み
    wb -- バイトの書込み
    ab -- バイトの追加

 ・max_linesize:
  改行文字を含むこのファイルの1行当たりの最大文字数(最小値は1、最大値は32767)。デフォルトは1024文字。

ファイルをオープンしたら、PUT_LINEプロシージャで1行のデータを書き込みます(3)。
※ファイルへの書き込みには、いくつかのプロシージャがあります。
  UTL_FILE.PUT_LINE(ファイルハンドル, 文字列)
       --ファイルにデータを書き込み、最後に改行コードも書き込む。
    UTL_FILE.PUT(ファイルハンドル, 文字列)
       --改行コードを付けずに、ファイルにデータを書き込む。
    UTL_FILE.NEW_LINE(ファイルハンドル, 行数)
       --改行コードのみをファイルに書き込む。

書込みが終了したら、FCLOSEプロシージャでOSファイルをクローズします(4)。

それでは、このプロシージャを実行してみます。
EXECUTE file_output_syomst

exec_utl_file

実行後、サーバの c:\temp ディレクトリに作成されたファイル「'syomst.txt」の中身を確認してみます。

syomst_txt

商品マスタのデータが、テキストファイルに出力されていることが確認できました。

次に、先ほど作成したテキストファイル「syomst.txt」を読込んで、画面に出力するプロシージャを作成します。

CREATE OR REPLACE PROCEDURE file_read_txt
IS
  --ファイル・ハンドルを受け取る変数の定義(1)
     read_file  UTL_FILE.FILE_TYPE;
     V_DATA     VARCHAR2(32767);

BEGIN
  --ファイルのオープン(2)
   read_file := UTL_FILE.FOPEN('TEMP_DIR', 'syomst.txt', 'r', 32767);
  --ループ
   LOOP
   --ファイルハンドルから1行ずつデータを読込む(3)
      UTL_FILE.GET_LINE(read_file, V_DATA, 32767);
   --画面に表示する
      DBMS_OUTPUT.PUT_LINE(V_DATA);
   END LOOP;

EXCEPTION
   WHEN NO_DATA_FOUND THEN
  --読込む行がなくなると、ファイルをクローズする(4)
      UTL_FILE.FCLOSE(read_file);
END;
/

ここでも、まずファイル・ハンドルを受け取る変数の定義を行い(1)、その後、FOPENファンクションで読込モードでテキストファイルをオープンします(2)。その後、GET_LINEプロシージャで1行ずつデータを読込み(3)、DBMS_OUTPUT.PUT_LINEで画面に出力します。
※GET_LINEで読込むファイルは、読込モード(r)でオープンしておく必要があります。違うモードの場合には、実行時INVALID_OPERATION例外が発生します。
読込む行がなくなると「NO_DATA_FOUND例外」が発生するので、FCLOSEプロシージャでファイルをクローズします(4)。

これを実行してみます。
SET SERVEROUTPUT ON
EXECUTE file_read_txt

execute_file_read

テキストファイル「syomst.txt」の内容を読込んで画面に表示することができました。

今日は以上まで

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

パッケージ(ORACLE)のオーバーロード

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

DSC_2529

3月も後半に入りましたが、気温の寒暖差がまだ結構ありますね。歳もそれなりに取ってきたので、気温の変化に体がついて行かず、体調を崩しやすくなってきた気がします。花粉も結構飛んでますし!

また、体だけでなく、住まいについても長く住んでいるといろいろなところが傷んできますよね。家電製品などが調子が悪くなると、妻が私に対して、「せっかくそれなりの学校の電気工学科を出たんだから、何とかならないの?」 と言いますが、あまり機械いじりなどが得意でない私は、結局何もできないでお手上げ状態になることがままあります。
そんなとき、「実際の生活にはなんも役に立たないんだから!」と、妻の厳しい~ 一言。
一応、僕は、ソフトウェア開発者なんだけどな~ (ΠΔΠ)

前回、ORACLEのパッケージについて簡単に取り上げてみましたが、続きとして、今回はパッケージのオーバーロードの機能について取り上げてみたいと思います。

<本日の題材>
パッケージ(ORACLE)のオーバーロード

オーバーロードとは、同じ名前のサブプログラムを定義できる機能です。JavaやC++などのオブジェクト指向言語にもオーバーロードという仕組みがあり、同名のメソッドや演算子を複数定義し,プログラムの文脈応じて,その場面に合ったメソッドや演算子を選択させることで,内部的な処理の手法が違うものに対して同一の処理手法を提供することができます。
パッケージのオーバーロードも同じような意味合いであり、同じ名前のサブプログラムを複数定義しておくことで、パラメータの数や順序、データ型が異なっている場合でも、同じ名前のサブプログラムを呼び出して実行することができます。

簡単な例を作成してみます。

例)
前々回で使用した商品マスタ(syomst)について、商品の金額を検索するのに、パラメータとして商品CDを渡して検索するのと、商品名を渡して検索するのを同じサブプログラムで定義して実行してみます。
ただ、よくよく考えると、商品CDと商品名はともにVARCHAR2型であり、今回の例としてはパラメータの型が違うものである必要があるため、SEQNOというINT型の項目を追加して、SEQNOで検索するのと、商品名で検索するのを同じサブプログラム名で定義してみたいと思います。

まず、商品マスタテーブルに「SEQNO」項目を追加します。

ALTER TABLE SYOMST ADD SEQNO INT;

alter_table_syomst

「SEQNO」項目には、商品CD(syo_cd)でソートした順の番号を設定します。

UPDATE SYOMST SET
  SEQNO =
        (SELECT A.SEQNO
             FROM
             (SELECT
                 syo_cd
              ,  ROW_NUMBER() OVER (ORDER BY SYO_CD) AS SEQNO                       FROM syomst) A
            WHERE A.syo_cd = SYOMST.syo_cd);

blog62_upd_seqno

更新後のデータを見てみます。

SELECT * FROM SYOMST
ORDER BY SYO_CD;

blog62_select

 「SEQNO」が追加され、SYO_CD順に番号が振られていることが確認できます。

それでは、この商品マスタから商品の金額を検索するのに、商品名で検索するのとSEQNOで検索するのを同じサブプログラムで定義するパッケージを作成します。

CREATE OR REPLACE PACKAGE pack_test2
IS
    PROCEDURE syomst_price(p_syo_name VARCHAR2);
    PROCEDURE syomst_price(p_seqno INT);
    op_price NUMBER := 0;
END;
/

パッケージの本体部分は、

CREATE OR REPLACE PACKAGE BODY pack_test2
IS 
  PROCEDURE syomst_price(p_syo_name VARCHAR2)
  IS
  BEGIN
    SELECT price INTO op_price
      FROM syomst
     WHERE syo_name = p_syo_name;  

    DBMS_OUTPUT.PUT_LINE(op_price);
  END syomst_price;

  PROCEDURE syomst_price(p_seqno INT)
  IS
  BEGIN
    SELECT price INTO op_price
      FROM syomst
     WHERE seqno = p_seqno;

    DBMS_OUTPUT.PUT_LINE(op_price);
  END syomst_price;
END;
/

これを実行(コンパイル)すると、
cre_pack_over

パッケージは作成されました。
それでは、実際に実行してみます。
最初に、商品名「キャベツ」で検索します。
※プロシージャの中でDBMS_OUTPUT.PUT_LINEを使用して金額を出力するかたちにしていますので、SQL*Plusで実行する場合には、初めにSERVEROUTPUTシステム変数をONにします。

SET SERVEROUTPUT ON
BEGIN
pack_test2.syomst_price('キャベツ');
END;
/

exec_pack2_syoname

 160円というキャベツの金額が表示されました。

次に、このキャベツのSEQNOは「9」なので、「9」の値で検索してみます。

BEGIN
  pack_test2.syomst_price(9);
END;
/

exec_pack2_seqno

先ほどと同様に、160円というキャベツの金額が表示されました。
同じ pack_test2.syomst_price というサブプログラムを実行しますが、パラメータの型を認識して、自動的にどちらのプロシージャを実行するかを判断しているということですね。

ちなみに、最初にやりかけた、商品CDと商品名というどちらも同じVARCHAR2型のパラメータを渡すものでちょっと試してみます。

CREATE OR REPLACE PACKAGE pack_test3
IS
    PROCEDURE syomst_price(p_syo_cd VARCHAR2);
    PROCEDURE syomst_price(p_syo_name VARCHAR2);
    op_price NUMBER := 0;
END;
/

CREATE OR REPLACE PACKAGE BODY pack_test3
IS
  PROCEDURE syomst_price(p_syo_cd VARCHAR2)
  IS
  BEGIN
      SELECT price INTO op_price
          FROM syomst
       WHERE syo_cd = p_syo_cd;

       DBMS_OUTPUT.PUT_LINE(op_price);
  END syomst_price;

  PROCEDURE syomst_price(p_syo_name VARCHAR2)
  IS
  BEGIN
       SELECT price INTO op_price
         FROM syomst
       WHERE syo_name = p_syo_name;

       DBMS_OUTPUT.PUT_LINE(op_price);
  END syomst_price;
END;
/

このパッケージは、コンパイルはできますが、実行すると「PLS-00307」のエラーが表示されて実行はできません。

BEGIN
  pack_test3.syomst_price('キャベツ');
END;
/

exec_pack_err

このように、オーバーロードが可能となるためには、パラメータの数やデータ型の違い、またファンクションの場合はリターンするデータ型などの違いだけでも必要になるということです。

今日は以上まで

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

パッケージ(ORACLE)

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

IMG_0142

夜の浦和駅で撮った写真です。サッカーは好きなので、やはりどうしても地元の浦和レッズに関心が行きますね。昨年は第1ステージは優勝でしたが、年間では3位、ナビスコ杯はベスト8、天皇杯やゼロックス杯は準優勝と、頑張ってはいるんですが、あと一歩、なかなか最後勝ちきれずに、悔しい思いをしたファンが多かったのではないでしょうか。今年は、是非頑張ってほしいです!

今日は、ORACLEのパッケージについて取り上げてみたいと思います。今まで何度か例で使用してきたDBMS_OUTPUTパッケージや、DBMS_LOCKパッケージなど、ORACLE側で事前に用意されているユーティリティ・パッケージもそれに該当しますが、そのパッケージについて詳しく見てみたいと思います。

<本日の題材>
パッケージ(ORACLE)

パッケージは、複数のサブプログラムを1つにまとめるためのオブジェクトです。プロシージャやファンクションとは異なり、仕様部と本体を別々に作成します。仕様部には本体に含めているプログラム名などをまとめて記述し、本体には各プログラムのソースコードを個別に記述していきます。

●パッケージの構造

  パッケージ仕様部
    PROCEDURE  proc_1 (para_1 VARCHAR2);
    FUNCTION  func_1 (para_2 NUMBER);

 パッケージ本体
    PROCEDURE proc_1 (para_1 VARCHAR2)
    IS
    BEGIN
       …

    FUNCTION  func_1 (para_2 NUMBER)
    IS
    BEGIN
       …

パッケージの場合、この仕様部さえ定義できていれば、パッケージ本体が未完成であっても、コンパイルは正常になされます。このプロシージャ proc_1 を呼び出すプログラムは、proc_1 のパッケージ本体を直接参照するのではなく、proc_1を実行するのに必要な情報をパッケージの仕様部から得ることができるので、プロシージャ proc_1 の本体部が作成されていなくてもコンパイルできるわけです。

パッケージの仕様部の作成は以下のようになります。

CREATE  [ OR REPLACE ] PACKAGE <パッケージ名>
{ IS | AS }
  <仕様部>
END [ <パッケージ名> ] ;

例)
CREATE OR REPLACE PACKAGE pack_test
IS
    PROCEDURE customer_month_purchase(年月 VARCHAR2);
    PROCEDURE customer_total_purchase;
END;

この仕様部の作成の処理を実行すると、

cre_package

パッケージの仕様部は作成できました。

次に、パッケージの本体を作成してみます。本体の作成は以下のようになります。

CREATE  [ OR REPLACE ] PACKAGE BODY <パッケージ名>
{ IS | AS }
  <本体>
END [ <パッケージ名> ] ;

先ほど仕様部を作成したパッケージの本体を作成します。パッケージ名は、仕様部と本体で一致している必要があります。

CREATE OR REPLACE PACKAGE BODY pack_test
IS
    PROCEDURE customer_month_purchase(V年月 VARCHAR2)
      IS
      BEGIN
        DELETE FROM 顧客月別購入履歴
         WHERE 年月 = V年月;
 
        INSERT INTO 顧客月別購入履歴
        (顧客NO, 年月, 購入回数, 購入金額)
        SELECT 顧客NO, TO_CHAR(出荷日,'YYYYMM'),
                            COUNT(DISTINCT 売上NO), SUM(売上金額)
          FROM 売上TBL
         WHERE TO_CHAR(出荷日, 'YYYYMM') = V年月
         GROUP BY 顧客NO, TO_CHAR(出荷日,'YYYYMM');
      END customer_month_purchase;
     
    PROCEDURE customer_total_purchase
      IS
      BEGIN
        DELETE FROM 顧客購入履歴;
       
        INSERT INTO 顧客購入履歴
        (顧客NO, 累計購入回数, 累計購入金額)
        SELECT 顧客NO, COUNT(DISTINCT 売上NO), SUM(売上金額)
          FROM 売上TBL
         GROUP BY 顧客NO;
      END customer_total_purchase;
END;
/

これは、売上TBLのデータから、指定した年月についての顧客の月別購入回数、金額を抽出して、「顧客月別購入履歴」テーブルに登録する「customer_month_purchase」というプロシージャと、売上TBLから顧客の過去のトータルの購入回数、金額を抽出して、「顧客購入履歴」テーブルに登録する「customer_total_purchase」というプロシージャを定義しています。(どちらも一旦データを削除してから登録します)

これを実行すると、

cre_package_body

 パッケージ本体も作成されました。

では、実際に実行してみます。
最初に、パッケージの中の「customer_total_purchase」プロシージャを実行します。

BEGIN
  pack_test.customer_total_purchase;
END;
/

exec_pack_total

 処理結果を確認するため、顧客購入履歴テーブルを抽出します。

SELECT * FROM 顧客購入履歴
ORDER BY 顧客NO;

顧客購入履歴

 次に、パッケージの中の「customer_month_purchase」プロシージャを実行します。

BEGIN
  pack_test.customer_month_purchase('201601');
END;
/

exec_pack_monthl

処理結果を確認するため、顧客月別購入履歴テーブルを抽出します。

SELECT * FROM 顧客月別購入履歴
 WHERE 年月 = '201601'
ORDER BY 顧客NO;

顧客月別購入履歴

データが作成されていることが確認できました。

※なお、パッケージの利点、使用する理由については、ここでは詳しくは記述しませんが、日本オラクル社のOTNのサイトに記載があります。(ORACLE 11g2のマニュアルにそのような説明がありました)
https://docs.oracle.com/cd/E16338_01/appdev.112/b56260/packages.htm#i2408


今日は以上まで

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

FIRST / LAST 関数

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

SONY DSC

この写真も、先回に続き、鳥の写真を撮るのに凝り始めたという知人の方から頂いたものです。たぶん白鷺だと思いますが、電線の上に止まっているところをきれいに撮っています。よく田んぼとかで白鷺を見かけることはありますが、電線に乗ることもあるんですね。

 そういえば、先日これはおもしろいと知人に紹介されたドラマで「夢をかなえるゾウ」のスペシャル男の成功編というのをDVDを借りて見ました。その話では人間の体にゾウの鼻、4本の腕を持ったインドのガネーシャという神様が、主人公の男性(小栗旬)に与える様々な課題を実践していく中で、人生をよい方向へと切り開いていく様子が描かれていますが、笑いあり、感動ありの内容でとてもよかったです。そこで出される課題は、以下のようなものだったと思います。(覚えている範囲内で)
・靴を磨く
・コンビニで(お釣りを)募金する
・食事は腹八分目にする
・人の欲しがる物を先取りしてあげる
・会った人を笑わせる
・トイレ掃除をする
・まっすぐ帰宅する
・その日がんばった自分を褒める
・一日何かをやめてみる
・毎朝、全身鏡を見て身なりを整える
・夢を楽しく想像する
・運が良いと口に出して言う
・明日の準備をする
・身近にいる大切な人を喜ばせる
・人のいい所を見つけ褒める
・人の長所を盗む
・サプライズをして喜ばせる

全ての課題に取り組んで、最後には、神様がそばにいなくてもやって行けるまで、どんどん主人公が成長していくという内容でしたが、それぞれの課題に対して、主人公が素直に取り組むのがとてもえらいと思いながら見ていました。
そのドラマの中で、仕事の会議中、最悪のように思える状況で、「運がいい」と口に出して言い、実際にそう思うことで発想を転換できて、厳しいと思っていた状況がよい方向に変わっていくシーンがありました。フィクションだからな!という思いが湧きつつも、確かにやってみる価値はあるなと考えさせられるところもいろいろとありました。

家族で一緒にこのビデオを見たので、妻や子供も、この中で出された課題を紙に書きとめて、自分も実践しようと意気込んでいましたが、さて、やっているのやら。

本題に移りましょうか。今回は、FIRST/LAST関数について取り上げてみたいと思います。

<本日の題材>
FIRST/LAST関数 (ORACLE)

以前、順位付の関数として、RANK関数やDENSE_RANK関数を取り上げたことがありましたが、本日は、DENSE_RANK関数と一緒に使うかたちで使用するFIRST関数、LAST関数を取り上げてみたいと思います。(ORACLEの環境)

構文は、以下のようになります。

グループ関数 KEEP
 ( DENSE_RANK FIRST/LAST ORDER BY ソート列1,[ソート列2,・・・] )
      OVER( [ PARTITION BY 項目1,[項目2,・・・]] )

以前にDENSE_RANK関数を取り上げたときに使ったのが商品マスタでしたので、今回もそれを使ってみます。今回はORACLEで試します。

CREATE TABLE syomst(
  syo_cd   VARCHAR2(10)
, syo_name VARCHAR2(20)
, bnrui    VARCHAR2(20)
, price    NUMBER(10)
, CONSTRAINT PK_syomst PRIMARY KEY (syo_cd));

データを登録します。

INSERT INTO syomst VALUES('A0001', 'チョコレート', 'お菓子', 120);
INSERT INTO syomst VALUES('B0001', 'りんご', '果物', 100);
INSERT INTO syomst VALUES('C0001', 'キャベツ', '野菜', 160);
INSERT INTO syomst VALUES('A0002', 'ビスケット', 'お菓子', 200);
INSERT INTO syomst VALUES('B0002', '桃', '果物', 160);
INSERT INTO syomst VALUES('C0002', 'にんじん', '野菜', 150);
INSERT INTO syomst VALUES('A0003', 'ガム', 'お菓子', 100);
INSERT INTO syomst VALUES('B0003', 'みかん', '果物', 80);
INSERT INTO syomst VALUES('C0003', 'じゃがいも', '野菜', 100);
INSERT INTO syomst VALUES('A0004', 'スナック', 'お菓子', 140);
INSERT INTO syomst VALUES('B0004', '梨', '果物', 120);
INSERT INTO syomst VALUES('C0004', '玉ねぎ', '野菜', 150);
COMMIT;

ここで、商品の分類毎の値段が最も高いものと低いものを出す場合、金額だけ抽出すればよいのであれば、普通は以下のようにします。

SELECT bnrui, MIN(price), MAX(price)
  FROM syomst
 GROUP BY bnrui
 ORDER BY bnrui;

syomst_min_max

ここで、商品の分類毎の値段が最も高いものと低いものの金額とともに商品も抽出したいという場合には、例えば以下のようにすることができます。

SELECT
  bnrui AS 分類
, MIN(syo_name) KEEP (DENSE_RANK FIRST ORDER BY price) AS 商品名_最安
, MIN(price) KEEP (DENSE_RANK FIRST ORDER BY price) AS 最安価格
, MIN(syo_name) KEEP (DENSE_RANK LAST ORDER BY price) AS 商品名_最高
, MIN(price) KEEP (DENSE_RANK LAST ORDER BY price) AS 最高価格
  FROM syomst
 GROUP BY bnrui
 ORDER BY bnrui;

syomst_first_last

実際のデータを確認してみると、

SELECT
  bnrui AS 分類
, syo_name AS 商品名
, price AS 価格
  FROM syomst
 ORDER BY bnrui, price;

syomst_order

確かに、各分類の最も安い価格のものと高い価格のものが抽出されていたことが確認できます。

次に、各商品の金額を表示するとともに、各分類の最も安い金額と高い金額を同じ行で表示するということを行ってみます。先ほどの、FIRST/LAST関数に、OVER(PARTITION BY )句を使用することで可能になります。

SELECT
  bnrui AS 分類
, syo_cd AS 商品CD
, syo_name AS 商品名
, price AS 価格
, MIN(price) KEEP (DENSE_RANK FIRST ORDER BY price)
   OVER(PARTITION BY bnrui) AS 分類の最安価格
, MIN(price) KEEP (DENSE_RANK LAST ORDER BY price)
   OVER(PARTITION BY bnrui) AS 分類の最高価格
  FROM syomst
 ORDER BY bnrui, syo_cd;

syomst_price_first_last

それぞれの商品の価格を、同じ分類の最も安い金額と高い金額と比較して見ることができるようになりました。

今日は以上まで

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

スリープ処理

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

SONY DSC

最近、鳥の写真を撮るのに凝り始めたという知人の方から、おもしろい写真が撮れたということで頂いたものです。結構大きめの鳥ということすが、名前まではわかりませんでした。月をバックに鳥がうまく収まっていますよね。

今日は、普段の生活について気の向くままに書いてみたいと思います。だんだん年を取ってくると、何かと健康が気になりますし、毎年行っている健康診断でも、昔は問題なかったところが、少しずつ正常でない数値になってきていることがあります。
今年もまた、健康診断の時期がやってきましたが、結果を見るのが少し怖いというか、何もなければうれしいけど、何かあると落ち込んでしまいますよね。

私は見た目は太っていないけれども、運動不足もあって、内臓脂肪が割と高めのタイプなんですが、結果が送られてくると、妻がそれを細かくチェック。「あなた、コレステロールの値がまた上がってるわよ! 間食で甘いものとかまたたくさん食べてるんじゃないの!」と厳しい追及が!!
しかし、そういう妻のほうが、結構見た目からもわかるほど太ってきているのも事実なんですが~、そこを言うとこじれてしまいそうなので、。。。

子供もまだまだ育ちざかりなので、健康にも十分気を付けないといけないですよね。

さて、しばらく思いつくままに書いてきましたが、1つだけSQLについてトピックを上げてみたいと思います。

<本日の題材>
スリープ処理

以前、ORACLEでのストアドプロシージャの処理がとても長くかかってしまうために、あるツールのほうでタイムアウトのエラーが起きてしまうということがお客様のほうで発生したことがあり、それを確認するためにどうしたらよいか?と調べてみたところ、ORACLEには、処理を待機させるパッケージが用意されているのを知り、試してみました。

PL/SQLのDBMS_LOCK パッケージに含まれる、DBMS_LOCK.SLEEPです。DBMS_LOCKパッケージは、その名の如く、特定モードのロックを要求したり、別のプロシージャ内で識別できる一意の名前をロックに付けたり、ロック・モードの変更およびロックの解放を行うことができるというものとのことですが、その中に、SLEEPプロシージャというものがあって、パラメータで指定した秒数だけスリープ(処理を待機)させることができます。

SCOTTユーザで実行してみます。
20秒スリープさせたいので、

execute DBMS_LOCK.SLEEP(20);

dbms_lock_err

 このパッケージはロックに関連するものであるので、PUBLICロールに実行権限は含まれていないため、別途実行権限を与えてあげないとエラーになってしまいます。

DBA権限のあるユーザで権限を付与します。

CONNECT / AS SYSDBA
GRANT EXECUTE ON DBMS_LOCK TO SCOTT;

dbms_lock_権限付与

 再度、SCOTTユーザでログインして実行してみます。

なお、SQLの実行時間を計測して表示したいので、TIMINGシステム変数をONにします。

SET TIMING ON
execute DBMS_LOCK.SLEEP(20);

 dbms_lock_sleep実行

実行すると、今度はエラーは出ず、たしかに処理を待機して、20秒後に結果が返ってきました。

<<SQL Serverでは>>
上記をSQL Serverでやろうとすると、WAITFOR DELAY というコマンドがあることがわかりました。試してみます。

同様に、20秒待機させたいので、’00:00:20’ をパラメータで指定します。

WAITFOR DELAY ’00:00:20’

waitfor_delay

 たしかに、20秒ほどしたら、結果が返ってきました。

 Oracleのように、処理時間を表示させたいので、開始時刻と終了時刻をPRINTすることにします。

 PRINT '開始時刻:' + convert(nvarchar, getdate(), 114);
WAITFOR DELAY '00:00:20';
PRINT '終了時刻:' + convert(nvarchar, getdate(), 114);

waitfor_delay2

 処理の開始時刻と終了時刻が表示されて、20秒ほどスリープしていたことがわかりました。

 今日は以上まで

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

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