カテゴリー別アーカイブ: ストアドプログラム

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

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技術ブログへ
にほんブログ村

スリープ処理

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コーディネータのシュウです。

IMG_0017_高麗1

先日、妻の知人の誘いで、埼玉県日高市のほうで行われた、馬射戲(マサヒ)騎射競技大会、それから高麗神社に行ってきました。馬射戲(マサヒ)というのは、高句麗古墳壁画の世界を再現した騎射競技ということで、日本の流鏑馬(やぶさめ)のルーツではないかと言われているようです。上の写真は、馬上から射た弓が的に的中して、まさに的が割れたところを撮った写真です。
埼玉県の日高市、飯能市の辺りは、716年に建郡された高麗郡といわれた地域であり、来年2016年が、建郡1300年ということで、いろいろな記念事業が行われていくようです。
実は、歴史やルーツというものが結構好きな私は、今回高麗(こま)神社も初めて行ってきたのですが、歴史を感じるとともに、埼玉県というのは渡来人とのつながりが深い地域だということを改めて知った1日でした。久しぶりに妻と一緒に歩いたのも、昔に戻ったようでうれしかったですね!

<本日の題材>
ストアド・ファンクション

今まで、何度かストアド・プロシージャを使用するような例を上げたことがありますが、ストアド・ファンクションについてはあまり取り上げて来なかったと思いますので、今回、題材に上げてみたいと思います。

ファンクションは、処理後に計算結果を1つだけ戻すもので、戻り値を持っています。戻り値は、Oracleの場合はRETURN句、SQL Serverの場合はRETURNS句でデータ型を設定します。戻す値については、ORACLE、SQL Serverともに、BEGIN ~ ENDで囲まれる部分の中でRETURN文によって設定します。
なお、プロシージャの場合は、引数でOUTパラメータを設定することで、複数の値を戻すことができますが、それは引数であって戻り値ではないということです。

また、プロシージャの場合は、CALL文やEXEC文で実行しますが、ファンクションの場合は、通常のSQLのSELECT文に直接記述して結果を得ることができます。
SELECT ファンクション名(xx) … FROM …

例1)
あるシステムで、年ごとに、いつからいつまでが第何週かを独自に設定している「WEEKマスタ」というものを準備し、指定した日付が第何週目かを確認するファンクションを作成します。

SQL Serverの場合:
テーブルの定義は、
CREATE TABLE [dbo].[WEEKマスタ](
        [年] [decimal](4, 0) NOT NULL,
        [WEEK] [decimal](2, 0) NOT NULL,
        [開始日] [date] NOT NULL,
        [終了日] [date] NOT NULL,
CONSTRAINT [PK_WEEKマスタ] PRIMARY KEY CLUSTERED ([年] ASC,[WEEK] ASC)
/

データを確認すると、
SELECT年, WEEK, 開始日,終了日
  FROM dbo.WEEKマスタ
 ORDER BY 年, WEEK;

WEEKマスタ抽出

ファンクションは例えば以下のようになります。

CREATE FUNCTION dbo.USF001_WEEK取得(
    @P年月日   DATE
)   RETURNS    DECIMAL(2,0)
AS
BEGIN
    DECLARE    @WEEK    DECIMAL(2,0) = 0;
    SELECT @WEEK = WEEK
      FROM dbo.WEEKマスタ
     WHERE @P年月日 BETWEEN開始日 AND終了日;
    RETURN    @WEEK
END;
GO

実際にこのファンクションを使って、指定した日付が第何週になるかを確認してみます。

SELECT dbo.USF001_WEEK取得('2015-11-16')

week_fn_取得

46週めという結果が出ました。
これを、Oracleで同じように試してみます。

テーブル定義は、
CREATE TABLE WEEKマスタ(
年     NUMBER(4) NOT NULL,
WEEK NUMBER(2) NOT NULL,
開始日 DATE NOT NULL,
終了日 DATE NOT NULL,
 CONSTRAINT PK_WEEKマスタ PRIMARY KEY(年, WEEK));

ファンクションは先ほどと同様にすると、以下のようにできます。

CREATE OR REPLACE FUNCTION WEEK取得(P_年月日 IN DATE)
RETURN NUMBER
AS
  V_WEEK    DECIMAL(2,0) := 0;
BEGIN
  SELECT WEEK INTO V_WEEK
    FROM WEEKマスタ
    WHERE P_年月日 BETWEEN 開始日 AND 終了日;
  RETURN    V_WEEK;
END;
/

このファンクションをSELECT文の中で使用して、WEEKを取得すると

SELECT WEEK取得('2015-11-16') FROM DUAL;

week_fn_取得_Ora

 結果は先ほどと同じく46週めですね。

例2)
メタボの予防などで、肥満度の話が出てきますが、その肥満度をチェックするのに、BMI(肥満指数)というものを用いることが多いと思います。今回は、その肥満指数BMIの計算をファンクションで実行してみたいと思います。
ファンクションは以下のようにできます。(SQL Server)

CREATE FUNCTION dbo.BMI取得(
    @体重      DECIMAL(5,2)
   ,@身長      DECIMAL(5,2)
)   RETURNS    DECIMAL(7,5)
AS
BEGIN
    DECLARE    @BMI    DECIMAL(7,5) = 0;
    SELECT @BMI = @体重 /(@身長/100 * @身長/100);
    RETURN    @BMI
END;
GO

実際に試してみると、体重:61.0kg、身長:165.5cmの場合

SELECT dbo.BMI取得(61.0, 165.5)

 BMI実行_sqlsv

結果は、22.27070 ということで、肥満度は普通ということですね。
調べてみると、BMI指数の値によって、以下のように言われているようです。
・18.5未満 =痩せ
・18.5~25=普通
・25~30 =肥満レベル1
・30~35 =肥満レベル2
・35~40 =肥満レベル3
・40~ =肥満レベル4

試しに、知人の値を確認してみます。

SELECT dbo.BMI取得(82.0, 164.0)

BMI実行_sqlsv2

 BMI指数が30を超えているので、肥満レベル2ですね。生活習慣病には是非気を付けてほしいものです。

ちなみに、ORACLEでも同様のファンクションを作成すると、以下のようになります。

CREATE OR REPLACE FUNCTION BMI取得 (P_体重 NUMBER, P_身長 NUMBER)
RETURN NUMBER
AS
  V_BMI    NUMBER(7,5) := 0;
BEGIN
    SELECT P_体重 / (P_身長/100 * P_身長/100) INTO V_BMI
      FROM DUAL;
    RETURN    V_BMI;
END;
/

実行してみると、

SELECT BMI取得(61.0, 165.5) AS BMI FROM DUAL;

BMI実行_ora

結果はSQL Serverのときと同じ値が出ました。

今日は以上まで

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

動的SQL(4)

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

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

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

今回は、SQL Serverの場合の動的SQLの続きで、sp_executesql システムストアドプロシージャを利用した例です。

構文
sp_executesql [ @stmt = ] statement
[
{ , [ @params = ] N'@parameter_name data_type [ OUT | OUTPUT ][ ,...n ]' }
{ , [ @param1 = ] 'value1' [ ,...n ] }
]

@stmtにはパラメータを含めることができ、パラメータ定義リスト(@params=..)とパラメータ値リスト(@param1=..)の両方に指定する必要があります。

例)受注テーブルから、指定した受注日と決済金額の条件に合った受注データを抽出する例で、条件句(WHERE句)にパラメータ変数を用いています。

DECLARE @sql_stmt nvarchar(500)
DECLARE @tab_Name nvarchar(30) = 'dbo.受注テーブル'
DECLARE @param nvarchar(200)
SET @sql_stmt = N'SELECT * FROM ' + @tab_Name
SET @sql_stmt = @sql_stmt + N' WHERE 受注日= @p1_date'
SET @sql_stmt = @sql_stmt + N' AND 決済金額>= @p2_kingaku'
SET @param = N'@p1_date varchar(8), @p2_kingaku int'

EXEC sp_executesql@sql_stmt
, @param
, @p1_date = '20141029'
, @p2_kingaku = 300000

結果は、以下
executesql_2結果

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

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

executesql_2結果error2

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

今日は以上まで

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

動的SQL(3)

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

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

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

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

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

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

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

SQLは以下:

DECLARE @tab_name VARCHAR(30)
DECLARE @Col_name VARCHAR(100)
DECLARE @Clause VARCHAR(200)
SELECT @tab_name = 'dbo.受注テーブル'
SELECT @Col_name = '顧客番号, 受注日, 決済金額'
SELECT @Clause = '受注日 BETWEEN ''20141028'' AND ''20141029'''

EXECUTE ('SELECT '+@Col_name+' FROM '+ @tab_name+' WHERE '+ @Clause)

パラメータとして、@tab_name にテーブル名、@Col_nameにカラム名、@Clauseには条件を渡し、それを実行します。
(SQL Serverでも、'で囲む文字列の中に'(シングルクォーテーション)を含む場合には、'を2つ連続して記述します)
また、EXECUTEは「EXEC」と省略することもできます。

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

受注日が20141028~20141029の期間内での顧客番号、受注日、決済金額を抽出しています。
この各パラメータを変更すれば、動的に対象のテーブル、項目、条件を設定できます。以下は変更した場合の例。

DECLARE @tab_name VARCHAR(30)
DECLARE @Col_name VARCHAR(100)
DECLARE @Clause VARCHAR(200)
DECLARE @sql_stmt VARCHAR(400)
SELECT @tab_name = 'dbo.受注テーブル'
SELECT @Col_name = '顧客番号, 決済金額'
SELECT @Clause = '決済金額 > 300000'
SELECT @sql_stmt = 'SELECT '+@Col_name+' FROM '+ @tab_name+' WHERE '+ @Clause

EXEC (@sql_stmt)

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

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

今日は以上まで

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