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

この写真は、住んでいるところの近くにある、関東最古の大社の一つと言われている「鷲宮神社」の鳥居が再建されている様子です。以前、ブログで人気まんが作品『らき☆すた』の舞台となった神社ということで取り上げ、この神社の鳥居の写真を載せたことがありましたが、2018年8月11日に、それまで建っていた鳥居が老朽化のために倒壊してしまいました。倒壊した鳥居は、100年以上前に建てられた木製のものでしたが、老朽化で、根本のほうがボロボロだったようです。それ以降、解体されて何もない状態でしたが、今回鳥居が再建されることになり、12月3日の祭典終了後から、通れるようになるということです。鳥居がなくて寂しい思いがしていましたが、立派な鳥居が再建されて、とても嬉しい気がします。

＜本日の題材＞
ネイティブコンパイルストアド プロシージャ（SQL Server）

前回のブログで、In-Memory OLTP 機能のメモリ最適化テーブル変数というものを取り上げましたが、今回はそれに関連する内容として、ネイティブコンパイルストアドプロシージャについて、試してみたいと思います。これは、メモリ最適化テーブルでの利用を想定したストアドプロシージャーです。
通常のSQL文によるクエリは、実行時にコンパイルしマシン語に変換したのちに実行されますが、ネイティブコンパイルストアドプロシージャーでは、マシン語に変換した状態のストアドプロシージャーをメモリに読み込ませておくため、従来のクエリよりも素早く実行できるのが特徴です。
ネイティブコンパイルとは、プログラミングの構造をネイティブコードに変換する処理であり、追加のコンパイルまたは解釈を必要としないプロセッサ命令で構成されると説明されています。
https://docs.microsoft.com/ja-jp/sql/relational-databases/in-memory-oltp/native-compilation-of-tables-and-stored-procedures?view=sql-server-ver15

例)
今回は、前回のメモリ最適化テーブル変数を使った処理とほぼ同様の内容を、ネイティブコンパイルストアドプロシージャで実行するようにしてみたいと思います。

ネイティブコンパイルストアドプロシージャの基本的な構文は以下となります。

CREATE PROCEDURE ストアドプロシージャ名
パラメーター定義
WITH
   NATIVE_COMPILATION,
   SCHEMABINDING
AS
BEGIN ATOMIC
WITH (
   TRANSACTION ISOLATION LEVEL = SNAPSHOT,
   LANGUAGE = N'japanese')
     -- 実行したいステートメント
END

ネイティブコンパイルストアドプロシージャを作成するには、CREATE PROCEDURE 文で「WITH NATIVE_COMPILATION」、「SCHEMABINDING」を指定する必要があります。「BEGIN ATOMIC」と「END」で行いたい処理を囲むかたちになり、「TRANSACTION ISOLATION LEVEL」でトランザクションの分離レベルを指定します。LANGUAGE は、日付フォーマットやシステムメッセージをどの言語にするかを指定するもので、「japanese」とすることで、日本語の日付フォーマット、およびメッセージを表示できるようになります。

それでは、前回のブログの内容に合わせたネイティブコンパイルストアドプロシージャを作成します。まず最初に、今回は、変数ではなく、メモリ最適化テーブルを作成します。

CREATE TABLE dbo.mem_test_tab(
id numeric(8)
,名前 nvarchar(20)
,区分 nvarchar(2)
,ポイント int
,CONSTRAINT PK_mem_test_tab PRIMARY KEY
NONCLUSTERED HASH (id) WITH ( BUCKET_COUNT = 500000 )
) WITH ( MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)

ここで、プライマリーキーのところでハッシュインデックスを設定し、さらに、WITHの後に「MEMOERY_OPTIMIZES=ON」と、「DURABILITY」を設定します。「DURABILITY」には、「SCHEMA_AND_DATA」と「SCHEMA_ONLY」が指定できます。SCHEMA_AND_DATA は、テーブルに持続性があり、変更がディスクに保存され、再起動またはフェールオーバー後も存続することを示しますが、SCHEMA_ONLY　は、テーブルに持続性がないことを示します。

前回は、変数でしたので、一連の処理の中でデータを変数にセットしましたが、今回は、先ほど作成したメモリ最適化テーブルに、最初に50万件のデータを登録しておきます。

DECLARE @i int = 0
WHILE @i < 500000
BEGIN
   SET @i = @i + 1
   INSERT INTO dbo.mem_test_tab(id, 名前, 区分, ポイント)
   VALUES(@i, '顧客名_'+ RIGHT('000000'+CAST(@i as nvarchar),6), CAST((1+ABS(CHECKSUM(NewId())) % 9)*10 AS NVARCHAR), ABS(CHECKSUM(NewId())) % 100)
END

データを確認してみます。
SELECT * FROM dbo.mem_test_tab
ORDER BY id;

それでは、ネイティブコンパイルストアドプロシージャを作成します。

CREATE PROCEDURE dbo.USP_inmemory_test
  @区分1 nvarchar(2),
  @区分2 nvarchar(2),
  @区分3 nvarchar(2),
  @区分4 nvarchar(2),
  @区分5 nvarchar(2)
WITH
   NATIVE_COMPILATION, SCHEMABINDING
AS
BEGIN ATOMIC
WITH ( TRANSACTION ISOLATION LEVEL = SNAPSHOT,
        LANGUAGE = N'japanese')
 
-- メモリ最適化テーブル変数に格納した結果を集計
      SELECT 区分, COUNT(*) 件数
         FROM dbo.mem_test_tab
      WHERE 区分 = @区分1
                 OR 区分 = @区分2
                 OR 区分 = @区分3
                 OR 区分 = @区分4
                 OR 区分 = @区分5
          GROUP BY 区分
          ORDER BY 区分;
END
GO

ネイティブコンパイルストアドプロシージャを実行します。

DECLARE @区分1 nvarchar(2) = '20',
       @区分2 nvarchar(2) = '40',
       @区分3 nvarchar(2) = '50',
       @区分4 nvarchar(2) = '70',
       @区分5 nvarchar(2) = '90';
     
EXEC dbo.USP_inmemory_test @区分1, @区分2, @区分3, @区分4, @区分5

こちらも、ほとんど時間はかからずに結果が表示されました。
今回は、ネイティブコンパイルストアドプロシージャを作成して、動作することを確認しました。

今日は以上まで

にほんブログ村

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

お久しぶりです。写真は4月の初め頃に撮った桜の写真です。早くブログにアップしようと思いながら、4月から新しく関わったプロジェクトのほうも忙しく、1か月があっという間に過ぎてしまいました。
さて、今年の初めには予想もしていなかったコロナウィルスの影響で、世界が全く違うものになったような現状に、本当に驚き戸惑うことが多い毎日です。ウィルスとの闘いの最先端で苦労されている医療従事者の方には本当に感謝をしなければならないと思います。ゴールデンウイークもほぼ自宅にいるという状況で、映画のDVDを借りてきて家族で見るとか、そんな過ごし方をせざるを得ない状況ではありますが、国民が一つとなってこのときを乗り越えていけるよう、そして1日も早く終息することができるように心からお祈りいたします。

＜本日の題材＞
データベーストリガー（SQL Server）

データベーストリガーについて、以前、Oracleで取り上げましたが、今回はSQL Serverにて同様の設定について確認してみたいと思います。
SQL Serverトリガーには、大きくは「ログオントリガー」「DDLトリガー」「DMLトリガー」の3つの種類があり、それぞれは以下のような内容になります。
「ログオントリガー」は、ログオン時ユーザーセッションが確立されるときに実行するもの
「DDLトリガー」は、DDL操作(CREATE, ALTER, DROPなど)により起動するもの
「DMLトリガー」は、特定の表やビューに対してDML操作(INSERT, UPDATE, DELETEなど)が行われるときに起動するもの

前回、Oracleで「DMLトリガー」の「AFTERトリガー」を使った例を紹介しましたが、今回はSQLServerで同様の設定をしてみたいと思います。

例）
前回同様、ユーザー管理用の「ACCOUNTS」テーブルというものがあったとして、それに対しての、INSERT, UPDATE, DELETE が行われるタイミング(AFTER)で、処理のログを「ACCOUNTS_LOG」テーブルに登録するトリガーを作成してみます。
まず、処理のログを登録する「ACCOUNTS_LOG」テーブルを作成します。

CREATE TABLE accounts_log(
log_date DATE,
old_account_id INT,
new_account_id INT,
old_name NVARCHAR(20),
new_name NVARCHAR(20),
action NVARCHAR(20));

次に、トリガーを作成しますが、「ACCOUNTS」テーブルに対しての、INSERT, UPDATE, DELETE が行われてデータが更新されるたびに、「ACCOUNTS_LOG」テーブルに、処理日、変更前後の「ACCOUNT_ID」、「NAME」そして処理内容を記録させます。

--INSERT、DELETEの履歴登録用
CREATE TRIGGER accounts_ins_del_trigger
ON accounts
AFTER INSERT, DELETE
AS
BEGIN
INSERT INTO accounts_log (log_date, old_account_id, new_account_id, old_name, new_name, action)
SELECT GETDATE(), NULL, account_id, NULL, name, 'INSERT'
FROM inserted
UNION
SELECT GETDATE(), account_id, NULL, name, NULL, 'DELETE'
FROM deleted;
END;
GO

--UPDATEの履歴登録用
CREATE TRIGGER accounts_upd_trigger
ON accounts
AFTER UPDATE
AS
BEGIN
INSERT INTO accounts_log (log_date, old_account_id, new_account_id, old_name, new_name, action)
SELECT GETDATE(), account_id, NULL, name, NULL, 'UPDATE'
FROM deleted
UNION
SELECT GETDATE(), NULL, account_id, NULL, name, 'UPDATE'
FROM inserted
END;
GO

前回同様下記のように、「ACCOUNTS」テーブルへのデータの登録、修正、削除を順に行ってみます。

INSERT INTO accounts(account_id, name) VALUES(101, '山田　太郎');
INSERT INTO accounts(account_id, name) VALUES(102, '高橋　三郎');
UPDATE accounts SET NAME = '高橋　一郎' WHERE account_id = 102;
DELETE FROM accounts WHERE account_id = 101;

それでは、「ACCOUNTS_LOG」テーブルを確認してみます。
SELECT * FROM accounts_log
ORDER BY log_date;

たしかに、「ACCOUNTS_LOG」テーブルへの更新履歴が登録されています。

ここで、SQL Serverの場合、DML トリガーステートメントでは、deleted テーブルおよび inserted テーブルという 2 つの特殊なテーブルが使用されます。そして、deleted テーブルには、DELETE ステートメントと UPDATE ステートメントの実行で影響を受けた行のコピーが格納され、inserted テーブルには、INSERT ステートメントおよび UPDATE ステートメントの実行で影響を受けた行のコピーが格納されるかたちになります。 つまり、UPDATEという更新処理は、DELETEの削除処理とそれに続くINSERTの挿入処理の組み合わせと考えることができ、deleted テーブルに古い行がコピーされ、その後、新しい行がinsertedテーブルにコピーされることになります。

そのため、「ACCOUNTS_LOG」テーブルへの更新履歴には、UPDATEに関しては、from deleted、from inserted からログを取得するようにトリガーを作っています。更新履歴にも、UPDATE処理により削除、登録の2つの行が確認できます。

 

今日は以上まで

にほんブログ村

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

今年は、例年になく暑い5月でしたね。26日には北海道で５月としての全国の観測史上最高気温となる３９・５度を観測するなど、異常な暑さでした。今からこの分だと、今年の夏はどうなるか？と心配してしまいます。

5月の中頃（5/19）はまだそれほどではなかったので、知人のご家族と一緒にバーベキューに行ってきましたが、天気も良く快適でした。
写真は埼玉県の羽生スカイスポーツ公園というところで、公園の名前にもあるように、スカイスポーツへの関心をもってもらおうという目的のもと整備された公園で、毎週土日祝日にはグライダーの飛行活動も行っていて、そこのクラブに入会すれば、実際にグライダーに乗ってスカイスポーツを楽しむことも出来るようです。

当日は、母の日も近かったので、日頃苦労している妻に感謝しようということで、準備は全てお父さんと子供たちで行って、妻には食べて楽しんでもらおうという企画で行いました。サプライズの花のプレゼントも用意して、妻もとても喜んでくれました。
たまにはごまをすっておかないと！

＜本日の題材＞
データベーストリガー2（Oracle）

ORACLEでのデータベーストリガーについて、前回取り上げましたが、前回は代表的なものとして、特定の表に対してDML操作(INSERT, UPDATE, DELETEなど)が行われるときに起動する「DMLトリガー」について取り上げてみました。
今回は、DDL操作(CREATE, ALTER, DROPなど)により起動する「DDLトリガー」について、試してみたいと思います。

例）
今回は、Oracleのデータベースについて、CREATE、DROP、ALTERという操作を行ったときに、どのユーザーが、どのオブジェクトに対して、いつ操作を行ったかをログに記録するというトリガーを作成してみます。
このとき、データベースに対してのトリガーを作成するためには、ADMINISTER DATABASE TRIGGER権限が必要です。
今回は、DBAロールを持つ、「test_admin」というユーザーでトリガーを作成します。

test_adminでログインした後、まず、処理のログを登録する「database_ddl_log」テーブルを作成します。

CREATE TABLE database_ddl_log (
user_name VARCHAR2(30),
activity VARCHAR2(20),
obj_name NVARCHAR2(30),
obj_type VARCHAR2(20),
event_date DATE
);

次に、トリガーを作成します。

CREATE OR REPLACE TRIGGER db_ddl_record
AFTER CREATE OR DROP OR ALTER ON DATABASE
DECLARE
   V_SYSEVENT   VARCHAR2(20);
   V_OBJ_OWNER   VARCHAR2(30);
   V_OBJ_NAME   VARCHAR2(30);
   V_OBJ_TYPE   VARCHAR2(20);
BEGIN
   V_SYSEVENT   := ora_sysevent;         -- イベントの取得
   V_OBJ_OWNER := ora_dict_obj_owner;   -- オブジェクトの所有者の取得
   V_OBJ_NAME   := ora_dict_obj_name;   -- オブジェクト名の取得
   V_OBJ_TYPE   := ora_dict_obj_type;   -- オブジェクトタイプの取得

INSERT INTO database_ddl_log
(user_name, activity, obj_name, obj_type, event_date)
VALUES (V_OBJ_OWNER, V_SYSEVENT, V_OBJ_NAME, V_OBJ_TYPE, SYSDATE);
END;
/

2行目に、AFTER CREATE OR DROP OR ALTER ON DATABASE と記載していますように、DATABASE全体において、CREATE、DROP、ALTER 文が実行された後に、起動するトリガーになります。そして、9行目から12行目までは、ora_ で始まるイベント属性関数を参照することで、必要な情報を一旦変数に格納し、最後にテーブルに登録します。

それでは、「BLOG_TEST」ユーザーでログインして、(1)新しくテーブルを作成、(2)定義の変更、(3)テーブルの削除をしてみます。

(1)   テーブルの作成
CREATE TABLE test_tab(
id INT
,name NVARCHAR2(30)
,CONSTRAINT PK_test_tab PRIMARY KEY (id));

(2)   テーブル定義の変更
ALTER TABLE test_tab
MODIFY name NVARCHAR2(50);

(3)   テーブルの削除
DROP TABLE test_tab;

それでは、上記の操作がログとして残っていることを確認します。
「test_admin」ユーザーで接続して、「database_ddl_log」テーブルを確認してみます。

SET LIN 120
COL user_name FORMAT A15
COL activity FORMAT A15
COL obj_name FORMAT A20
COL obj_type FORMAT A10
COL event_date FORMAT A20

SELECT
user_name, activity, obj_name, obj_type
, TO_CHAR(event_date, 'YYYY/MM/DD HH24:MI:SS') event_date
FROM database_ddl_log
ORDER BY event_date;

ログテーブルを抽出したところ、先ほどの処理（テーブル作成、テーブル定義変更、テーブル削除）が、ログの履歴として登録されていることが確認できました。

今日は以上まで

にほんブログ村

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

桜の時期も終わってしまいました。ただ、今年は桜が咲いてから、少し寒かったので、桜がすぐには散らず、例年より長く、桜を楽しむことができたように思います。
東北や北海道などは、まだこれからがいよいよ本番ということなので、仕事で出張とかあればついでにお花見もできるのですが、そういう機会があるかな～。

＜本日の題材＞
データベーストリガー（Oracle）

データベーストリガーについて、まだブログで取り上げていなかったので、今回、取り上げてみたいと思います。
データベーストリガーとは、プロシージャやファンクションと同じように、データベースに格納されたPL/SQLのプログラムですが、プログラムなどから明示的にコールされて呼び出すのではなく、何らかのイベントをきっかけとして自動的に起動されるものです。

トリガーには、大きくは「DMLトリガー」「DDLトリガー」「その他」の3つの種類があります。
「DMLトリガー」は、特定の表に対してDML操作(INSERT, UPDATE, DELETEなど)が行われるときに起動するもの
「DDLトリガー」は、DDL操作(CREATE, ALTER, DROPなど)により起動するもの
「その他」については、
　・データベースにログインやログオフするとき
　・データベースの起動時や停止時
　・エラーの発生時
などで起動するものがあります。
一般的によく使用される「DMLトリガー」についてですが、文トリガー、行トリガーという種類があり、さらに、処理の前後のどちらで起動させるかという、「BEFOREトリガー」「AFTERトリガー」という分類があります。

例）
今回は、ユーザー管理用の「ACCOUNTS」テーブルというものがあったとして、それに対しての、INSERT, UPDATE, DELETE が行われるタイミング(AFTER)で、処理のログを「ACCOUNTS_LOG」テーブルに登録するトリガーを作成してみます。

まず、処理のログを登録する「ACCOUNTS_LOG」テーブルを作成します。

CREATE TABLE accounts_log(
log_date DATE
,old_account_id INT
,new_account_id INT
,action VARCHAR2(50));

次に、トリガーを作成しますが、「ACCOUNTS」テーブルに対しての、INSERT, UPDATE, DELETE が行われてデータが更新されるたびに、「ACCOUNTS_LOG」テーブルに、処理日、変更前後の「ACCOUNT_ID」、そして処理内容を記録させます。
今回は、行トリガーのAFTERトリガーとして作成します。

CREATE OR REPLACE TRIGGER accounts_change_trigger
  AFTER INSERT OR UPDATE OR DELETE
  ON accounts FOR EACH ROW
DECLARE
  log_action  accounts_log.action%TYPE;
BEGIN
  IF INSERTING THEN
    log_action := 'Insert';
  ELSIF UPDATING THEN
    log_action := 'Update';
  ELSIF DELETING THEN
    log_action := 'Delete';
  ELSE
    DBMS_OUTPUT.PUT_LINE('This code is not reachable.');
  END IF;

  INSERT INTO accounts_log (log_date, old_account_id, new_account_id, action)
    VALUES (SYSDATE, :OLD.account_id, :NEW.account_id, log_action);
END;
/

トリガーの中で、「FOR EACH ROW」を付けることで、行トリガーであることを示しています。そして、:OLD.列名、:NEW.列名とすることで、変更前と変更後の列名の値を取得することができます。
それでは、下記のように、「ACCOUNTS」テーブルへのデータの登録、修正、削除を順に行ってみます。

INSERT INTO accounts(account_id, name) VALUES(101, '山田　太郎');

INSERT INTO accounts(account_id, name) VALUES(102, '高橋　三郎');

UPDATE accounts SET NAME = '高橋　一郎' WHERE account_id = 102;

DELETE FROM accounts WHERE account_id = 101;

それでは、「ACCOUNTS_LOG」テーブルを確認してみます。

「LOG_DATE」項目の表示を時刻まで出すようにフォーマットを変更します。
ALTER SESSION SET NLS_DATE_FORMAT='YYYY/MM/DD HH24:MI:SS';

データを確認します。
SELECT * FROM accounts_log
ORDER BY log_date;

たしかに、「ACCOUNTS_LOG」テーブルへの更新履歴が登録されています。
ここで、INSERTの場合は、変更前はデータがないので、old_accounti_id は NULL であり、DELETEの場合は、変更後はデータがなくなるので、new_accounti_id は NULLになることがわかります。

また、COMMITをする前にROLLBACKをしてみると「ACCOUNTS_LOG」のデータはどうなるでしょうか？

ROLLBACK;

再度、「ACCOUNTS_LOG」テーブルを確認してみます。

先ほど見えていたデータは、処理が取り消されたので、ログからも取り消されているのが確認できます。

今日は以上まで

にほんブログ村

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

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

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

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

　皇居側から見た平川橋

＜本日の題材＞
テーブル値関数（SQL Server）

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

例１）まず、サンプルとして適当かどうか？というのはありますが、こういう使い方もあるということで、指定した日から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 表示順;

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

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

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

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

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

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';

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

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

GRANT READ, WRITE ON DIRECTORY temp_dir TO blog;

 準備はできましたので、「商品マスタ(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

実行後、サーバの c:\temp ディレクトリに作成されたファイル「'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

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

今日は以上まで

にほんブログ村

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

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

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

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

＜本日の題材＞
パッケージ（ORACLE）のオーバーロード

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

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

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

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

ALTER TABLE SYOMST ADD SEQNO INT;

「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);

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

SELECT * FROM SYOMST
ORDER BY SYO_CD;

 「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;
/

これを実行（コンパイル）すると、

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

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

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

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

BEGIN
  pack_test2.syomst_price(9);
END;
/

先ほどと同様に、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;
/

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

今日は以上まで

にほんブログ村

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

夜の浦和駅で撮った写真です。サッカーは好きなので、やはりどうしても地元の浦和レッズに関心が行きますね。昨年は第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;
/ 

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

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

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

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」というプロシージャを定義しています。（どちらも一旦データを削除してから登録します）

これを実行すると、

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

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

BEGIN
  pack_test.customer_total_purchase;
END;
/

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

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

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

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

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

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

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

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

今日は以上まで

にほんブログ村

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

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

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

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

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

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

＜本日の題材＞
スリープ処理

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

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

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

execute DBMS_LOCK.SLEEP(20);

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

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

CONNECT / AS SYSDBA
GRANT EXECUTE ON DBMS_LOCK TO SCOTT;

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

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

SET TIMING ON
execute DBMS_LOCK.SLEEP(20);

 

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

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

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

WAITFOR DELAY ’00:00:20’

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

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

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

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

 今日は以上まで

にほんブログ村

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

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

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

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

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

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

例１）
あるシステムで、年ごとに、いつからいつまでが第何週かを独自に設定している「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;

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

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')

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;

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

例２）
メタボの予防などで、肥満度の話が出てきますが、その肥満度をチェックするのに、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)

 

結果は、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指数が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;

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

今日は以上まで

にほんブログ村