テーブル値関数(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技術ブログへ
にほんブログ村

横に並んだ項目を縦の行データに変換

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

DSC_4130

新年あけましておめでとうございます。
写真は、今まで何回か写真をアップしている、加須はなさき水上公園の池のところを撮った写真です。池の水面に木々が写ってとてもきれいです。何気なく見ている景色も、見る角度や季節、時刻などが変わると、全然違うように感じることがありますね。

さて、新しい1年が出発しました。また1つ年を取ってしまったという思いもありますが、今年こそはやってやる!という夢と気概を持って、何事にも挑戦して行ければと思います。
本年も、よろしくお願いいたします。

<本日の題材>
横に並んだ項目を縦の行データに変換

以前、「複数行のデータを集計して横展開」という題名で、複数行の縦に並んだデータを、横に項目を並べて表示させるためのSQLを取り上げましたが、その反対に、最初から横に項目を並べて登録されているデータを、複数行の縦に並んだデータに変換するやり方については取り上げていませんでした。
実際のシステム開発において、そういうケースにも時折出会うことがあるため、今回一度取り上げてみたいと思います。

様々なシステムにおいて、縦には商品であったり、勘定科目であったり、そのシステムで管理したいデータを並べ、横の列に、例えば4月~翌年3月までの各月を項目として持たせて、年度毎の一覧を表示/修正させるようなケースがあると思います。そのときに、データベースのテーブル自体の列に、4月、5月、…12月、..3月というように、表示に合わせて、各月の項目を持たせるという設計になる場合もあります。
このようにして登録された横に並んだ各列のデータを、今度はひと月ずつのデータとして複数行に分けて処理したいという場合に、どのようなSQLにすればよいのか? 以下に例を示します。

例)
商品ごとの各月の売上実績を登録するテーブルを以下のように定義、作成します。

CREATE TABLE dbo.商品売上(
  商品CD     VARCHAR(20)
, 年度       VARCHAR(4)
, 売上4月    DECIMAL(12)
, 売上5月    DECIMAL(12)
, 売上6月    DECIMAL(12)
, 売上7月    DECIMAL(12)
, 売上8月    DECIMAL(12)
, 売上9月    DECIMAL(12)
, 売上10月   DECIMAL(12)
, 売上11月   DECIMAL(12)
, 売上12月   DECIMAL(12)
, 売上1月    DECIMAL(12)
, 売上2月    DECIMAL(12)
, 売上3月    DECIMAL(12)
, CONSTRAINT PK_商品売上 PRIMARY KEY (商品CD, 年度))
;

データを以下のように登録します。

INSERT INTO dbo.商品売上('A0001', 2015, 495280, 503400, 485400, 534800, 521300, 494600, 538200, 482600, 546820, 483240, 452600, 517800);
INSERT INTO dbo.商品売上('B0001', 2015, 213300, 246400, 220480, 253100, 262300, 247200, 251800, 236700, 262400, 254300, 223800, 248900);
INSERT INTO dbo.商品売上('C0021', 2015, 165800, 139200, 181040, 166400, 176300, 168300, 192400, 168000, 201400, 187600, 176500, 194700);

このテーブルから、2015年度の商品CD毎の売上を普通に抽出すると

DECLARE
@年度 VARCHAR(4) = 2015

SELECT * FROM dbo.商品売上
 WHERE 年度 = @年度
 ORDER BY 商品CD;

58_商品売上抽出

 このデータを、商品CD毎、一月毎の売上データとして、各月のデータを複数行に分けて抽出する場合、例えば以下のようにします。

DECLARE
@年度 VARCHAR(4) = 2015

SELECT SU.商品CD, SU.月, SU.売上
  FROM
       (SELECT 商品CD, 4 AS 月, ISNULL(売上4月,0) AS 売上, 1 表示順  FROM dbo.商品売上 WHERE 年度 = @年度
  UNION SELECT 商品CD, 5 AS 月, ISNULL(売上5月,0) AS 売上, 2 表示順  FROM dbo.商品売上 WHERE 年度 = @年度
  UNION SELECT 商品CD, 6 AS 月, ISNULL(売上6月,0) AS 売上, 3 表示順  FROM dbo.商品売上 WHERE 年度 = @年度
  UNION SELECT 商品CD, 7 AS 月, ISNULL(売上7月,0) AS 売上, 4 表示順  FROM dbo.商品売上 WHERE 年度 = @年度
  UNION SELECT 商品CD, 8 AS 月, ISNULL(売上8月,0) AS 売上, 5 表示順  FROM dbo.商品売上 WHERE 年度 = @年度
  UNION SELECT 商品CD, 9 AS 月, ISNULL(売上9月,0) AS 売上, 6 表示順  FROM dbo.商品売上 WHERE 年度 = @年度
  UNION SELECT 商品CD, 10 AS 月,ISNULL(売上10月,0) AS 売上, 7 表示順 FROM dbo.商品売上 WHERE 年度 = @年度
  UNION SELECT 商品CD, 11 AS 月,ISNULL(売上11月,0) AS 売上, 8 表示順 FROM dbo.商品売上 WHERE 年度 = @年度
  UNION SELECT 商品CD, 12 AS 月,ISNULL(売上12月,0) AS 売上, 9 表示順 FROM dbo.商品売上 WHERE 年度 = @年度
  UNION SELECT 商品CD, 1 AS 月, ISNULL(売上1月,0) AS 売上, 10 表示順  FROM dbo.商品売上 WHERE 年度 = @年度
  UNION SELECT 商品CD, 2 AS 月, ISNULL(売上2月,0) AS 売上, 11 表示順  FROM dbo.商品売上 WHERE 年度 = @年度
  UNION SELECT 商品CD, 3 AS 月, ISNULL(売上3月,0) AS 売上, 12 表示順  FROM dbo.商品売上 WHERE 年度 = @年度
       ) SU
 ORDER BY 商品CD, 表示順;

58_商品売上抽出_横縦

 各月のデータを抽出し、UNIONで結合したものを1つのテーブルのようにみなして、そこからデータを抽出するというかたちです。
※結果を4月から順に上から並べたいので、表示順という項目をつけてみました。

上記はORACLEでも同様に行うことができます。

SELECT SU.商品CD, SU.月, SU.売上
  FROM
       (SELECT 商品CD, 4 AS 月, NVL(売上4月,0) AS 売上, 1 表示順  FROM 商品売上 WHERE 年度 = '2015'
  UNION SELECT 商品CD, 5 AS 月, NVL(売上5月,0) AS 売上, 2 表示順  FROM 商品売上 WHERE 年度 = '2015'
  UNION SELECT 商品CD, 6 AS 月, NVL(売上6月,0) AS 売上, 3 表示順  FROM 商品売上 WHERE 年度 = '2015'
  UNION SELECT 商品CD, 7 AS 月, NVL(売上7月,0) AS 売上, 4 表示順  FROM 商品売上 WHERE 年度 = '2015'
  UNION SELECT 商品CD, 8 AS 月, NVL(売上8月,0) AS 売上, 5 表示順  FROM 商品売上 WHERE 年度 = '2015'
  UNION SELECT 商品CD, 9 AS 月, NVL(売上9月,0) AS 売上, 6 表示順  FROM 商品売上 WHERE 年度 = '2015'
  UNION SELECT 商品CD, 10 AS 月,NVL(売上10月,0) AS 売上, 7 表示順 FROM 商品売上 WHERE 年度 = '2015'
  UNION SELECT 商品CD, 11 AS 月,NVL(売上11月,0) AS 売上, 8 表示順 FROM 商品売上 WHERE 年度 = '2015'
  UNION SELECT 商品CD, 12 AS 月,NVL(売上12月,0) AS 売上, 9 表示順 FROM 商品売上 WHERE 年度 = '2015'
  UNION SELECT 商品CD, 1 AS 月, NVL(売上1月,0) AS 売上, 10 表示順  FROM 商品売上 WHERE 年度 = '2015'
  UNION SELECT 商品CD, 2 AS 月, NVL(売上2月,0) AS 売上, 11 表示順  FROM 商品売上 WHERE 年度 = '2015'
  UNION SELECT 商品CD, 3 AS 月, NVL(売上3月,0) AS 売上, 12 表示順  FROM 商品売上 WHERE 年度 = '2015'
       ) SU
 ORDER BY 商品CD, 表示順;

58_商品売上抽出_横縦_ora

SQL Serverのときと同様の結果が表示されるのが確認できました。

今日は以上まで

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

DATEFROMPARTS, DATETIME2FROMPARTS関数

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

DSC_2085

今年は暖冬と言われており、スキー場では雪不足のところも結構あって心配されていますが、急に寒くなったりもしますね。暖冬というのは、暖かい日が続くという意味ではなく、平均して暖かいということなので、寒暖の差が激しい場合も多いようです。寒暖の差が激しいと、自律神経なども乱れがちになり、体調を崩しやすいそうです。
年末も近づいてきましたが、体調の管理には十分注意して、よい年を迎えたいものですね。
また、ブログを見て頂いた皆さん、この1年、どうもありがとうございました。来年も頑張りますので、よろしくお願いいたします!

<本日の題材>
DATEFROMPARTS, DATETIME2FROMPARTS関数

前回、日付の曜日を取得する関数として、SQL ServerのDATEPART関数、DATENAME関数というものを取り上げましたが、今回も日付に関連する内容です。

いろいろなシステムにおいて、年や月を指定してその期間内のデータについての処理を行うことがあると思います。そのとき、数値などで指定した年、月から日付型に変換するのにCAST関数などを使用して文字に変換してそれぞれをつなげ、最後にCONVERT関数などで日付に変換して対応するようなことを行う必要が出てくると思いますが、少し便利な関数を見つけましたので、今回はこれを取り上げてみたいと思います。(SQL Serverの場合)

その関数は、DATEFROMPARTS関数、DATETIMEFROMPARTS関数、DATETIME2FROMPARTS関数というものです。SQL Server2012から有効な関数です。

まず、DATEFROMPARTS関数についてですが、構文は、
DATEFROMPARTS (year, month, day)
引数の year, month, day のところに、それぞれ年、月、日を示す整数を設定することで、日付に変換して結果を返します。

例)
年、月、日をそれぞれ変数を持たせて値を設定し、それらの値に該当する日付を日付型で抽出します。

DECLARE
  @年 INT = 2015
, @月 INT = 12
, @日 INT = 21

SELECT DATEFROMPARTS (@年, @月, @日);

datefromparts

簡単に日付型のデータに変換できました。
これは、EXCELのDATE関数と同じようなイメージですね。

excel_date

※SQL Server 2008で上記の処理を実行すると、下記のようにエラーになります。

datefromparts_2008err

 ※また、月の引数部分を13にするとか、日の引数部分を32など日付として存在しない値を設定した場合もエラーになります。

上記を、SQL Server2012より以前の場合など、この関数を使用しないで別のやり方でやろうとすると、例えば以下のようにする方法があります。

DECLARE
 @年 INT = 2015
, @月 INT = 12
, @日 INT = 21
, @年月日 VARCHAR(8) 

SET @年月日 = CAST(@年 AS VARCHAR)+CAST(@月 AS VARCHAR)+CAST(@日 AS VARCHAR)

SELECT CONVERT(date, @年月日)

convert_date1

 ただ、月や日が1桁の場合や、年が4桁に満たないような場合には、以下のようにエラーになってしまいますので、もう一工夫必要になります。

DECLARE
  @年 INT = 2015
, @月 INT = 9
, @日 INT = 1
, @年月日 VARCHAR(8) 

SET @年月日 = CAST(@年 AS VARCHAR)+CAST(@月 AS VARCHAR)+CAST(@日 AS VARCHAR)

SELECT CONVERT(date, @年月日)

convert_date2_err

これは、上記の @年月日 が「201591」となって日付に変換しようとしてもできないためです。
ですので、以下のように、左側に「0」を詰めるかたちにする必要があります。

SET @年月日 = RIGHT('0000'+CAST(@年 AS VARCHAR), 4) + RIGHT('00'+CAST(@月 AS VARCHAR), 2) + RIGHT('00'+CAST(@日 AS VARCHAR), 2)

実際にやってみると、

convert_date3PNG

上記のようにすると、@年月日は「20150901」となるので、その後のCONVERT関数でエラーにならずに、日付に変換できました。

次に、時刻についても時間や分、秒を同様に引数で渡して datetime2型などで表示したい場合には、DATETIME2FROMPARTS関数というものもあります。
(datetime型にする場合は DATETIMEFROMPARTS関数。datetime型とdatetime2型の違いについては、以前の投稿(Oracle・SQL Server 日付型について)で記載しています)

下記に例を示します。

DECLARE
  @年 INT = 2015
, @月 INT = 12
, @日 INT = 21
, @時 INT = 16
, @分 INT = 42
, @秒 INT = 34
, @秒_小数 INT = 525

SELECT DATETIME2FROMPARTS (@年, @月, @日, @時, @分, @秒, @秒_小数, 3);

datetime2fromparts

指定した値を元に、datetime2型で表示されているのがわかります。
構文は、

DATETIME2FROMPARTS (year, month, day, hour, minute, seconds, fractions, precision)

引数については下記:
year     :年を指定する整数式。
month    :月を指定する整数式。
day      :日を指定する整数式。
hour     :時間を指定する整数式。
minute   :分を指定する整数式。
seconds  :秒を指定する整数式。
fractions:小数部分を指定する整数式。
precision:返される datetime2 値の有効桁数を指定する整数リテラル。

さて、ORACLEで同様のことをする場合、下記のように、それぞれの数値を一旦TO_CHAR関数で文字でつなげた後に、日付型(今回はミリ秒があるのでTIMESTAMP型)にTO_TIMESTAMP関数で変換するなどの処理が必要になります。

※今回は、TIMESTAMP型に変換した後、SQL Serverのときと同様の形式で結果を表示したかったので、最後にTO_CHAR関数で書式を整えています。

DECLARE
  V_年 INT := 2015;
  V_月 INT := 9;
  V_日 INT := 1;
  V_時 INT := 16;
  V_分 INT := 42;
  V_秒 INT := 34;
  V_秒_小数 INT := 525;
  V_日付文字 VARCHAR2(30);
  V_日付 TIMESTAMP;
  V_日付変換 VARCHAR2(30);

BEGIN
  V_日付文字 := TRIM(TO_CHAR(V_年,'0999'))||TRIM(TO_CHAR(V_月,'09'))||TRIM(TO_CHAR(V_日,'09'))||TRIM(TO_CHAR(V_時,'09'))||TRIM(TO_CHAR(V_分,'09'))||TRIM(TO_CHAR(V_秒,'09'))||TRIM(TO_CHAR(V_秒_小数,'099'));

  DBMS_OUTPUT.PUT_LINE(V_日付文字); 

  SELECT TO_TIMESTAMP(V_日付文字, 'YYYY-MM-DD HH24:MI:SS FF3') INTO V_日付 FROM DUAL;

  DBMS_OUTPUT.PUT_LINE(V_日付);

  SELECT TO_CHAR(V_日付, 'YYYY-MM-DD HH24:MI:SS.FF3') INTO V_日付変換 FROM DUAL;

  DBMS_OUTPUT.PUT_LINE(V_日付変換);
END;
/

oracle_timestamp2

数値で設定した年、月、日、時間、分、秒、ミリ秒の値を元に、V_日付という TIMESTAMP型の変数に変換された値が設定されていることが確認できます。

※月で1桁のものを2桁の文字で表示する場合などに、書式としてTO_CHAR(項目, ‘09’)というようにすると、2桁に足らない場合には、頭に「0」を埋めてくれます。

また、ミリ秒を設定するときの書式は、「FF[1-9]」というものがあり、今回の小数点以下3桁の場合は、「FF3」と設定します。

今日は以上まで

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

DATEPART, DATENAME関数

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

IMG_0075

 先日、長女がイルミージュという西武遊園地で行われているイルミネーションを見に行って、それを撮った写真をくれたので、アップしてみました。10月24日から来年の4月10日まで16:00~21:00の間、開催しているようですね。いろいろと写真をくれたのですが、どれもとってもきれいでした。この時期は、各地でイルミネーションの催しがありますが、西武遊園地については知らなかったです。

西武遊園地と言えば、妻と一緒になる前に二人で行ったことのある特別な思い出の場所でもあり、そのときのことを懐かしく思い出します。あの頃は、妻に会えることが本当に嬉しくて、二人でアトラクションに乗ったことも結構鮮明に覚えていますね。
まだこういうイルミネーションなどはなかったですけどね。
しばらく忘れていた記憶を甦らせてくれた娘に感謝します。

さて話は変わりますが、dbSheetのユーザ事例として、Access版を採用した産業用電気機器卸売業の会社の事例がホームページに上がっています。既存のAccessの営業支援システムを、データをRDBMSで一元管理しつつ、国内10拠点で運用可能なシステムに短期間で移行させることができたという内容です。興味のある方はぜひご参照ください。
http://www.newcom07.jp/dbsheetclient/usrvoice/electric_wholesale.html

<本日の題材>
DATEPART, DATENAME関数

最近関わったシステムで、日付とともに曜日を各列の列名の部分に表示しつつ、各日毎の計画数量などの集計値を各行に表示する帳票を作成するというものがありました。
対象のDBがSQL Serverであったので、対象の日付の曜日を表示するのに、DATEPART関数というものを使用しました。今回はこれを取り上げてみたいと思います。

構文は、
DATEPART(datepart, date)

引数としての datepart には日付の要素を指定し、その後ろに確認したい日付を指定します。この関数の戻り値は int型になります。
日付の要素としての datepart には以下のようなものがあります。

  year, yyyy, yy  :年
  quarter, qq, q  :4半期
 month, mm, m :月
 dayofyear, dy, y :年の何日目
 day, dd, d      :日
 week, wk, ww  :年の第何週
 weekday, dw   :曜日
 hour, hh       :時刻の何時
 minute, n      :時刻の分
 second, ss, s    :時刻の秒
 millisecond, ms :時刻のミリ秒
 microsecond, mcs :時刻のマイクロ秒
 nanosecond, ns :時刻のナノ秒
 …..他

今回は、曜日を確認したいので、weekday または dw を使います。
ただし、戻り値は、SET DATEFIRST を使って設定された値に依存します。SET DATEFIRST は、週の最初の曜日を示す整数値を指定するもので、SET DATEFIRST 1 というようにしますが、
  1:月曜日
  2:火曜日
  3:水曜日
  4:木曜日
  5:金曜日
  6:土曜日
  7:日曜日(デフォルト)
となります。カレンダーを見ても、1週間の始まりが日曜日というのが既定値ですね。

例)
それでは、今日の日付が何曜日なのかを確認します。
まず、今日が何日かを確認します。

SELECT CONVERT(VARCHAR, GETDATE(), 111)

datepart_getdate

「2015-12-13」は日曜日ですが、SQLでDATEPART関数を使って確認すると、

SELECT DATEPART(dw, GETDATE())

デフォルトの状態(SET DATEFIRST 7)では、日曜日を週の最初と見なすので、結果は以下のように「1」となります。

datepart_dw

ついでに、今年の第何週目かを確認すると、

SELECT DATEPART(wk, GETDATE())

detepart_wk

また、DATEPART関数は、戻り値が int型でしたが、戻り値が nvarchar型で同様に日付の要素についての結果を戻す関数として、DATENAME関数というものがあります。

SELECT DATENAME(dw, GETDATE())

datename

「日曜日」ではなく、最初の「日」だけでよければ、最初の1文字のみを取得すればよいと思います。

datename2

ときに、システムによっては、月曜日を週の開始日として、曜日や第何週目かを確認したい場合もあるかも知れません。このときは SET DATEFIRST 1 としてから上記の処理を実施します。確認してみると、

SET DATEFIRST 1
SELECT DATEPART(dw,GETDATE()), DATEPART(wk,GETDATE());

SELECT DATENAME(dw, GETDATE()), LEFT(DATENAME(dw, GETDATE()),1);

firstdate7_datepart

 週の始まりが月曜日という指定をすることで、今日「2015-12-13」は、DATEPART(dw, GETDATE())では「7」、そして第50週めという結果になりました。DATENAMEでの曜日は日曜日で同じ結果になります。

上記は、ORACLEでも TO_CHAR関数を使用することで同様な内容を行うことができます。
まず、今日の日付を確認します。

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') FROM DUAL;

56_ora_sysdate

次に、今日が何曜日か、年初から第何週目か、また月での何週目かを確認します。

SELECT
  TO_CHAR(SYSDATE, 'D')    AS 曜日
, TO_CHAR(SYSDATE, 'DY')   AS 曜日名略
, TO_CHAR(SYSDATE, 'DAY')  AS 曜日名
, TO_CHAR(SYSDATE, 'WW')   AS 年初からの週
, TO_CHAR(SYSDATE, 'W')    AS 月初からの週
  FROM DUAL;

56_ora_to_char

Oracleでは、TO_CHAR関数を利用して様々な内容を取得してくることができますね。

今日は以上まで

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

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