ITC シュウちゃん のすべての投稿

SEQUENCE(シーケンス)の現在値の変更(SQL Server)

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

IMG_3308

お久しぶりです。この写真は2か月前くらいに、島根の実家に帰省する際に乗った、寝台特急「サンライズ出雲」の、ノビノビ座席に乗ったときに撮ったものです。寝台車というと、個室を思い浮かべますが、最近は頭の部分だけが仕切られていて、そんなにそれぞれのスペースは広くはないですが、カーペットの座敷で横になってしっかり寝れるようなタイプの格安の席があります。
ちょうど、高速バスがコロナ禍で運行していない時期で、帰省する手段を探したところ見つかり、寝台車に久しぶりに乗りましたが、横になって休める分、バスよりもゆっくり眠れるような気がします。

最近再びコロナの感染者が増えてきており、様々心配は尽きませんが、こういうときこそ絆を大切にし、励ましあいながら、頑張って乗り越えて行きたいものです。
コロナウィルスを始め、様々なことがあったこの1年もいよいよ終わろうとしています。皆様も健康に気を付けてよいお年をお迎えください。

<本日の題材>
SEQUENCE(シーケンス)の現在値の変更(SQL Server

以前、SEQUENCEについてブログで取り上げましたが、SEQUENCEの現在の値を修正したいというケースが発生する場合があります。例えば、主キーの値をシーケンスを使って採番するようにしていたが、データのメンテナンスが必要になり、データをコピーして追加したところ、主キーの値がシーケンスの値と大きくかけ離れた大きな値になってしまったというようなケースです。私も実際に複数のテーブルでそういうケースにぶつかってしまい、一つずつ、シーケンスの設定を変更しなければいけないという状況が発生しました。このとき、テーブルの主キーの値の最大値をSQLで確認した後、SQL ServerManagement Studioで1つ1つのシーケンスの現在値を変更していきましたが、もっとスムーズに設定の変更ができる方法がないかを探したときに、対応した方法です。

例)
「testA」テーブルの主キー「id」(int型)をSEQUENCE「test_seqA」を使って採番するかたちにします。
100番までSEQUENCEを使って採番した後、データのメンテナンスが発生して、120番までデータを追加しましたが、そのときにはSEQUENCEを使って採番しなかったので、現在値が100のままになっているので、そのままでは次にSEQUENCEを使ってデータを登録すると、主キーの重複でエラーになってしまいます。ですので、SEQUENCEの現在の値を120に修正する必要があります。

まず、SEQUENCE「test_seqA」の現在の値を確認してみます。

SELECT current_value FROM sys.sequences WHERE name = 'test_seqA';

blog100_1

100という結果が出ました。

次に、テーブル「testA」の主キー「id」の最大値を確認してみます。

SELECT MAX(id) FROM testA;

blog100_2

120という値が確認されました。

SEQUENCEの現在の値を120に修正する方法の1つは、ALTER SEQUENCE文で修正を行う方法です。

ALTER SEQUENCE test_seqA
RESTART WITH 120
INCREMENT BY 1;

(ちなみに、SQL Server Management Studioでシーケンスのプロパティで、「シーケンスの再開」から変更することも可能です)

上記の作業を順に行えば修正はできますが、毎回、いちいち上記の処理を順番に行うのも面倒なので、今回は、上記のALTER SEQUENCE文を使用するのではなく、下記のようなSQL文を作成して、それを実行するかたちにします。

DECLARE
@id_MAX   INT
,@Seq_cur_val   sql_variant
,@count         INT = 0;

SELECT @id_MAX = MAX(id)
FROM testA;

SELECT @Seq_cur_val = CURRENT_VALUE FROM sys.sequences
WHERE name = ‘test_seqA’;

IF @id_MAX > CAST(@Seq_cur_val AS INT)
BEGIN
WHILE @count < @id_MAX
BEGIN
              SELECT @count = NEXT VALUE FOR test_seqA;
END;
END;

実行します。

blog100_3

実行後、SEQUENCE「test_seqA」の現在の値を確認してみます。

SELECT current_value FROM sys.sequences WHERE name = 'test_seqA';

blog100_4 

現在値が120に変更されているのが確認できます。
この状態であれば、「tetA」テーブルにSEQUENCE「test_seqA」を使って主キーを採番することが可能です。次の採番データを登録して見ます。

INSERT INTO testA(id) VALUES(NEXT VALUE FOR test_seqA);

blog100_5

データは登録されました。データの主キーの値が大きいものから10件確認してみます。

SELECT TOP 10 * FROM testA
ORDER BY id DESC;

blog100_6

正しく採番されていることが確認できました。
ちなみに、dbSheetClientでは、上記のSQLのテーブル名、主キー名、SEQUENCE名などを、Excelのシートから値を渡すことができますので、上記のSQLを実行するタスクを1つ作成して、それを各テーブルで必要なタイミングで呼び出すようにすることで、複数のテーブルにおいてもSEQUENCEの現在値を修正することが可能です。これを使うことで、何とか問題なく処理をすることが可能になり、とても助かりました。

今日は以上まで

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

CASE式をWHERE句で使う(SQL Server)

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

IMG_3316

お久しぶりです。写真は妻が買ってきて家に置いていたハイビスカスという花が綺麗に咲いたところを撮ったものです。ハイビスカスは花の色によって花言葉が違うようですね。白は「艶美」、ピンクは「華やか」、黄色は「輝き」、赤は「勇敢」とのこと。
家に花があるのはいいものですね。あまり花とかに興味がなかった私でしたが、妻のおかげで自然の美しさを意識することが多くなったと感謝しています。
一生懸命に咲いて美を表わそうとする花のように、私たちも情熱をもって一生懸命に生きていきたいものです。

それから、dbSheetClientのブログに、はるパパさんのdbSheetClient千夜一夜というブログができています。是非そちらもご覧ください。
https://www.newcom07.jp/dbsheetclient/dbsc-blog/

<本日の題材>
CASE式をWHERE句で使う(SQL Server

CASE WHENの式を、以前、SELECT文で使用するサンプルは何度かブログで取り上げましたが、WHERE句の中で、ケースによって条件文自体を変更したいという場合のやり方を今回試してみたいと思います。

例)
検索用の項目を設定し、そこに値を設定すれば、その項目に対しては指定した値で検索するが、何も設定しなければ、その項目に対しては条件を設定せずに抽出を行いたいというケースはよくあると思います。

今回は簡単なケースとして、社員マスタの検索の際に、「社員番号」「社員名」「拠点」の検索項目に対して、設定した内容があればその値で検索し、なければ条件としては加えないかたちの例を取り上げてみたいと思います。

上記の3項目をパラメータとして渡し、値が NULL でなければその値を条件とするが、NULLの場合には条件に加えないというかたちにします。

例えば、社員番号、社員名は何も値を設定せず、拠点を「ニューヨーク」として検索したい場合、以下のように設定できます。

-- 変数を定義
DECLARE
            @社員番号     DECIMAL(5),
            @社員名       NVARCHAR(20),
            @拠点         NVARCHAR(20);

--変数設定:画面で選択した値がセットされるようにします
SET @社員番号 = NULL;
SET @社員名 = NULL;
SET @拠点 = 'ニューヨーク' ;

--社員マスタ検索
SELECT * FROM dbo.社員マスタ
WHERE 1=1
AND 社員番号 = CASE WHEN @社員番号 IS NULL THEN 社員番号 ELSE @社員番号 END
AND 社員名 = CASE WHEN @社員名 IS NULL THEN 社員名 ELSE @社員名 END
AND 拠点 = CASE WHEN @拠点 IS NULL THEN 拠点 ELSE @拠点 END;

SQL1

上記は、拠点が「ニューヨーク」の社員を全て抽出してきます。
もちろん、社員名にも値を設定すれば2つの条件で抽出することになります。

SQL2

 結果は、2つの条件を満たすデータのみが抽出されることを確認できました。

今日は以上まで

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

データベーストリガー(SQL Server)

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

IMG_2842

お久しぶりです。写真は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

たしかに、「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技術ブログへ
にほんブログ村

1年の終わりに

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

IMG_2088_Original

今年もいよいよ残り2日。
会社も休みに入り、1年間を振り返り、反省する時期になりました。時間を取って1年を総括し、来年に向けてしっかりと準備していければと思いますが、今年は正月に実家にも帰って、成長した孫の姿を親に見せたいと思います。
写真は、会社で7月に行った、飯能市のムーミンバレーパークで撮った写真です。家族も連れて行ってもいいということで、子供たちも写真を撮りまくっていましたね。感謝です。

さて、今年のブログは、ほんの数回しか上げることができなかったのですが、データベーストリガーについて、参考になったという方がいましたので、次は早いうちにSQL Server版も取り上げていけたらと思っています。

来年も、皆様にとって良い年となりますように、心からお祈りいたします。

今日は以上まで

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

照合順序(SQL Server)の異なるデータベース間のジョイン

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

 出雲大社

今年もあっという間にあと数日というところまで来てしまいました。今年の後半は仕事もかなり忙しくなり、なかなか時間も取れず、ブログの更新ができませんでしたが、気力を振り絞って書いています。
写真は、今年田舎に帰省した時に寄った出雲大社です。久しぶりに父親と一緒にお参りしたのですが、父親が子供のころは、正月に自転車で数時間をかけてお参りに行ったという話を聞き、今は車で1時間もかからずに行けるので、昔は大変だったんだなと改めて感じました。中を歩くと、旧暦の10月に全国から出雲大社に集う八百万の神様がお泊りになるという十九社という社があるのも初めて知りました。出雲地方以外、旧暦の10月のことを普通は神無月といいますが、出雲地方では神在月と言います。おもしろいですね。

さて、今年は元号が新しく令和になった記念すべき年でしたね。私としては、特にラグビーワールドカップでは、ONE TEAMという言葉がとても心に残りました。思い出せばいろいろなことがありましたが、大晦日に1年を振り返ってよかったと言えるように、最後まで頑張ろうと思います。皆さんも頑張りましょう!

<本日の題材>
照合順序(SQL Server)の異なるデータベース間のジョイン

SQL Serverの照合順序について、以前取り上げましたが、今回は、異なる照合順序のデータベース間でのデータのジョインをしたいケースが発生した場合に、可能なのかどうかについて確認してみました。これは、新しく作成したデータベースから、元々運用していた基幹のデータベースのテーブルを参照して抽出を行おうとしたら、照合順序が違うためにエラーが発生したという問合せを受けたことがあったので、自分でも一度試してみたいと思ったためです。照合順序とは、データの文字の大小関係を比較する場合の基準となるものをいうということで、前回説明しましたように、複数のテーブル間のジョインを行う際にも、重要な要素になります。

例)
照合順序が「Japanese_CI_AS」の既存のデータベース「BLOG」とは別に、「Japanese_90_BIN」という新しいデータベース「BLOG2」を作成します。

データベース作成時の照合順序設定

 ここで、元々「Japanese_CI_AS」の照合順序のデータベース「BLOG」にあるテーブル「BUMON_M」と同じレイアウトのテーブルを、新しく作成した照合順序が「Japanese_90_BIN」のデータベース「BLOG2」に作成して、データも登録します。

その後、「SYAIN_M」と「BUMON_M」とをジョインしてデータを抽出する処理を行ってみます。

まず、「BLOG」データベース内でジョインする場合は、

SELECT b.BUMON_CD, b.BUMON_NAME, s.SYAIN_CD, s.SYAIN_NAME
FROM dbo.SYAIN_M s
JOIN dbo.BUMON_M b
ON s.BUMON_CD = b.BUMON_CD
ORDER BY b.BUMON_CD, s.SYAIN_CD;

sql1

 上記を、「BLOG」データベースの「SYAIN_M」と先ほど作成した「BLOG2」データベースの「BUMON_M」とをジョインしてデータを抽出する処理を行ってみます。

すると、下記のようなエラーが返ってきます。
「メッセージ 468、レベル 16、状態 9、行 4
equal to 操作の "Japanese_90_BIN" と "Japanese_CI_AS" 間での照合順序の競合を解決できません。」

sqlエラー

上記の場合に正しく結果を出す方法は、データベースの照合順序を同じになるように、データベースを作り直すという方法もありますが、ジョインする項目について、COLLATE句を使用する方法があります。

SELECT b.BUMON_CD, b.BUMON_NAME, s.SYAIN_CD, s.SYAIN_NAME
FROM BLOG.dbo.SYAIN_M s
JOIN BLOG2.dbo.BUMON_M b
ON s.BUMON_CD = b.BUMON_CD COLLATE Japanese_CI_AS
ORDER BY b.BUMON_CD, s.SYAIN_CD;

ジョイン成功

エラーなく、結果が抽出されたことが確認できます。ただし、ジョインなどで必要な項目については全て、SQLの中でCOLLATE句の設定が必要になるため、面倒な部分もあります。

ちなみに、データベース自体の照合順序を変更する場合は、前回のときに取り上げたように、ただデータベースの照合順序を変更するだけではなく、既存のテーブルの各項目について、COLLATE文で照合順序を指定してALTER TABLE文を実行する必要がありますので、照合順序を正しく設定した空の新規データベースを作成した後に、テーブルの作成と、既存データの登録を行ったほうが早いケースもあると思います。

今日は以上まで

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

データベーストリガー2(Oracle)

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

11216014_51

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

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_log

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

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)テーブルの削除をしてみます。

connect_blog_test

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

cre_test_tab

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

alter_tab

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

drop_tab

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

connect_admin

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;

select_log

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

今日は以上まで

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

データベーストリガー(Oracle)

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

IMG_1978

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

<本日の題材>
データベーストリガー(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_log

次に、トリガーを作成しますが、「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;
/

trigger

トリガーの中で、「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;

DML

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

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

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

select_accounts_log2

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

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

ROLLBACK;
rollback

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

select_accounts_log_sai

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

今日は以上まで

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

データベースリンク(Oracle)

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

IMG_1878

だいぶ前になってしまいますが、お正月に、田舎に帰省したときに、飛行機から富士山が綺麗に見えたため、撮った写真です。飛行機の搭乗員の方も、これほど富士山が綺麗に見えるのは珍しいと言われていましたので、とても得をした気分になりましたね。
今年は縁起がいいかも!
いろいろと仕事が忙しかったため、前回の投稿からかなり時間が空いてしまいましたが、今年も頑張っていければと思います。よろしくお願いいたします。

<本日の題材>
データベースリンク(Oracle

システム開発の仕事をしていると、時折、複数のORACLEのサーバーがあり、それぞれに格納されているテーブルを同時にアクセスしてデータを抽出するなどの処理を行いたいという依頼を受けることがあります。
こういうときにORACLEデータベース側で、他のデータベースにアクセスできるデータベースリンクというものを作成することで、そういった操作が可能になります。
今回は、それを試してみたいと思います。

例)
今回は、Oracle12cのデータベースから、Oracle11gR2 のデータベースへのデータベースリンクを作成して、Oracle12c側からOracl11gR2側のデータを抽出できるようにしたいと思います。

設定の内容としては、大きくは以下になります。
①    ORACLEデータベースサーバー同士で接続できるように、
  ネットサービス名を設定します。
  今回は、Oracle12c側で設定を行います。
②    データベースリンクを作成するための権限を、該当のユーザ
  ーに与えます。
③ 該当のユーザーでデータベースリンクを作成します。

それでは始めて行きます。
①ネットサービス名の設定は、ツールでも可能ですが、今回はOracle12c側の「tnsnames.ora」ファイルを直接編集することで行います。

追加するネットサービス名を「ORCL11G」として、以下の内容を追加します。

ORCL11G =
(DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xxx.xxx.xxx)(PORT = 1521))
   )
   (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SERVICE_NAME = サービス名)
   )
 )

※「HOST=」の後には、Oracl11gR2サーバーのIPアドレス(ホスト名でもよい)を設定し、「PORT=」の後にはデフォルトのポート番号を記載しています。最後の「SERVICE_NAME =」の後には、Oracle11gR2データベースのサービス名を設定します。

②    データベースリンクを作成するための権限を、該当のユーザーに与えます。以前、「Oracle12cでのユーザー作成でエラー「ORA-65096」」という内容でブログで取り上げたときの内容を使って、Oracle12cの「BLOG_TEST」というユーザーに権限を与えます。

Sqlplusでsysdba でログインした後、「PDBORCL」というプラガブルデータベースに接続します。

ALTER SESSION SET CONTAINER = PDBORCL;

alter_session

 BLOG_TEST というユーザーでデータベースリンクを作成したいのですが、データベースリンク作成には権限が必要なので、このユーザーに権限を与えます。

GRANT CREATE DATABASE LINK TO BLOG_TEST;

cre_databaselink

それでは、「BLOG_TEST」ユーザーに接続して、データベースリンクを作成します。

CONNECT BLOG_TEST/passwd@PDBORCL
(passwdのところは、パスワード)
connect_2

Oracle11gR2のデータベースに対するデータベースリンク「ORCL11DB」を作成します。

CREATE DATABASE LINK ORCL11DB CONNECT TO BLOG_TEST IDENTIFIED BY passwd USING ‘ORCL11G’;

cre_databaselink_2
(IDENTIFIED BY の後には、Oracle11gR2側のBLOG_TESTユーザーのパスワードを設定します。)

 データベースリンクが作成されましたので、Oracle11gR2側の「CUSTOMER」テーブルのデータを抽出してみます。

SELECT * FROM CUSTOMER@ORCL11DB;

select_dblink

毎回、「CUSTOMER@ORCL11DB」のように書くのが面倒だという場合などには、シノニムを作成することもできます。

まず、シノニムを作成できる権限を、「BLOG_TEST」に与えます。一度、SYS ユーザーに戻ってから下記コマンドを実行します。

GRANT CREATE SYNONYM TO BLOG_TEST;
grant_cre_synonym

再度BLOG_TEST で接続して、シノニムを作成してみます。

CONNECT BLOG_TEST/passwd@PDBORCL
connect_2

CREATE SYNONYM OR11_CUSTOMER FOR CUSTOMER@ORCL11DB;

cre_synonym

それでは、シノニムを使って、データベースリンクを貼ったOracle11gR2側データベースの「CUSTOMER」データを抽出します。

SELECT * FROM OR11_CUSTOMER;
select_synonym

シノニムを使って、データベースリンクを貼ったテーブルのデータを抽出できることが確認されました。

ただし、注意点として、リモートのデータベースへ接続するため、ネットワークの状態や、データ件数やデータを取得するやり方により、ローカルのデータベース内のみでの操作に比べて、パフォーマンスが低下する場合があります。

今日は以上まで

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

計算列(SQL Server)

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

IMG_1826

先月、会社の同僚と海釣りに行ってきました。
横浜の金沢八景の近くの海で、釣り船を予約して楽しんできました。その日はとてもよい天気で、アジ、サバ、イシモチなどが結構たくさん釣れましたね。私は最初なかなか釣れなくて、悪戦苦闘していたのですが、隣にいた釣り好きの先輩においては、竿を投げればすぐに引きが来る感じで、本当にびっくりです。同じようにやっているつもりなのに、どこかが違うんですね。やはり釣りは奥が深い!それでも、後半は多少釣れたこともあり、楽しむことができました。
上の写真は、船から八景島シーパラダイスのジェットコースターを撮ったものです。
釣れた魚は、その日のうちにさばいて刺身にして食べました。アジがぷりぷりしてとっても美味しかった。魚好きの妻も、美味しいと喜んでくれたので、よかった。\(^▽^)/

さて、今年も残りわずかとなりました。風邪など引かないで、よい年を迎えられますことをお祈りいたします。来年も、よろしくお願いいたします。

<本日の題材>
計算列(SQL Server

SQL Serverで、計算列というものがあるということなので、試してみたいと思います。

例)
以前、身長と体重からBMI(肥満指数)をストアド・ファンクションを使って求めたことがありましたが、今回は計算列でこれを行ってみたいと思います。

CREATE TABLE dbo.syain_health(
id      INT
,height  DECIMAL(5,1)
,weight  DECIMAL(5,1)
,bmi     AS (
      weight /(height/100 * height/100))
,CONSTRAINT PK_syain_health PRIMARY KEY (id));

テーブル作成後に、テーブルの定義を、SQL Server Management Studio で確認すると、

定義

最後の「bmi」項目が計算列になります。
それでは、データを登録してみます。このとき、計算列にはInsertしません。

INSERT INTO dbo.syain_health(id, height, weight) VALUES(1, 165.1, 61.2);
INSERT INTO dbo.syain_health(id, height, weight) VALUES(2, 174.4, 65.6);
INSERT INTO dbo.syain_health(id, height, weight) VALUES(3, 182.3, 71.3);
INSERT INTO dbo.syain_health(id, height, weight) VALUES(4, 168.8, 63.1);
INSERT INTO dbo.syain_health(id, height, weight) VALUES(5, 172.0, 84.9);

Insert

計算列にデータを登録しようとすると、下記のようにエラーが出ます。

INSERT INTO dbo.syain_health(id, height, weight, bmi) VALUES(6, 176.0, 73.5, 25.3);

insert_err

 それでは、データをSELECTしてみます。

SELECT * FROM dbo.syain_health
ORDER BY id;

syain_select

計算列で指定した「bmi」列は、自動的に計算されて抽出されていることが確認できます。

また、計算列にはインデックスを作成することもできます。
データを100万件ほど作成して、インデックスの有無によるレスポンスを比較してみます。

身長は、140センチ以上200センチ以内、体重は40キロ以上100キロ以内でランダムに作成してみます。このとき、RAND関数を利用してみます。RAND関数は、0~1までの範囲の乱数をfloat型で取得するものです。

DECLARE
  @v_count INT = 5;     -- id は既に1~5は作成済なので初期値を5

WHILE @v_count < 1000000
BEGIN
    SET @v_count = @v_count + 1;
 
   INSERT INTO dbo.syain_health(id, height, weight)VALUES
    (@v_count, ROUND(140+RAND() * 600/10,1), ROUND(40+RAND() * 600/10,1));
END;

insert_300万

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

SELECT * FROM dbo.syain_health
ORDER BY id;

select_300

 件数を確認すると、

SELECT COUNT(*) FROM dbo.syain_health;

select_count

それでは、bmi の値が 20 ~ 25 の人の件数を確認します。
時間を計測するため、以下のコマンドを実行します。

SET STATISTICS TIME ON

SELECT COUNT(*) FROM dbo.syain_health
WHERE bmi BETWEEN 20 AND 25;

count_25_30

メッセージタブで表示されている時間は、
SQL Server 実行時間:
、CPU 時間 = 671 ミリ秒、経過時間 = 673 ミリ秒。

次に、インデックスを作成してみます。

CREATE INDEX IX_syain_bmi ON dbo.syain(bmi);

cre_index

一度、データバッファキャッシュをクリアします。
DBCC DROPCLEANBUFFERS

再度、先ほどの処理を実行してみます。
SELECT COUNT(*) FROM dbo.syain_health
WHERE bmi BETWEEN 20 AND 25;

count_25_30

このときの時間を確認すると、
SQL Server 実行時間:
、CPU 時間 = 16 ミリ秒、経過時間 = 26 ミリ秒。

インデックスを作成することで、処理は速くなっていることが確認できました。

また、計算列は物理的にデータを保存することもできるということで、その場合には、テーブル作成時に、計算列の後ろにPRESISTED を付けます。

CREATE TABLE dbo.syain_health2(
id      INT
,height  DECIMAL(5,1)
,weight  DECIMAL(5,1)
,bmi     AS (
      weight /(height/100 * height/100)) PERSISTED
,CONSTRAINT PK_syain_health2 PRIMARY KEY (id));

インデックスも追加します。
CREATE INDEX IX_syain_health_bmi2 ON dbo.syain_health2(bmi);

その後、同様にデータを100万件作成して、先ほどと同様のことを行ってみます。

count_25_30_2

SQL Server 実行時間:
、CPU 時間 = 16 ミリ秒、経過時間 = 17 ミリ秒。

物理的にデータが保存されている場合とそうでない場合での処理時間の違いについては、それほど違いは無いようですが、多少速い感じでしょうか。

今日は以上まで

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

DELETEと領域の解放(ORACLE)

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

防災疑似体験

先日、新橋のほうに仕事で行った際に、近くのビルでVR(バーチャルリアリティ)映像を使った災害疑似体験ができる防災体験車に乗って体験する機会がありました。
昼休みくらいに、近くに来ているということでせっかくだからと行ってみたところ、東京消防庁が保有する防災体験車が止まっていて、8人くらいずつが乗って、ヘッドマウントディスプレイを被って、マンションにいるときに、震度7程度の地震が起きた場合の疑似体験ができました。
時間は3分ほどでしたが、上から物が落ちるは家具が倒れるは、ガラスは割れ、ドアは開かなくなるし、外の景色も大変なことに! いやあ~想像していたよりも迫力もあり、酔いそうな気分に! とても立っていられません。震度7だとこんなに凄いんですね。

地震大国である日本、いつそういう大きな地震に遭遇するか知れないので、とっさにどう動いたらいいか? なかなか想像がつかないですよね。一度は訓練として体験してみる価値があると思いました。

<本日の題材>
DELETEと領域の解放(ORACLE)

ORACLEを使用している場合に、使用している表領域のサイズがかなり大きくなってしまったので、データを削除して使用できる領域を増やそうと考える場合があると思います。しかし、DELETE文で削除してコミットしたのに、表領域のサイズが変わらないという状況に出くわして、困ってしまう場合があると思います。今回は、この内容について取り上げてみたいと思います。

例)
できるだけサイズが大きいテーブルで確認したいので、以前、ビットマップインデックスの題材のときに作成した、「T_CUSTOMER」という300万件のテーブルを使ってみます。

テーブルの定義は、
DESCRIBE T_CUSTOMER

describe

件数を確認すると
SELECT COUNT(*) FROM T_CUSTOMER;

件数確認

 件数は300万件。データの中身を一部確認してみると、
SELECT * FROM T_CUSTOMER
WHERE C_ID <= 30
ORDER BY C_ID;

 データ中身

C_ID列は、シーケンシャルに番号が増えていってます。
このテーブルが占めているサイズを確認すると、

SELECT SEGMENT_NAME, SEGMENT_TYPE, SUM(BYTES)/1024/1024 "MB"
FROM USER_SEGMENTS
WHERE SEGMENT_NAME LIKE '%T_CUSTOMER%'
GROUP BY SEGMENT_NAME, SEGMENT_TYPE
ORDER BY SEGMENT_TYPE DESC, SEGMENT_NAME;

テーブルサイズ確認

テーブル「T_CUSTOMER」が64MB、インデックス(主キー)「PK_T_CUSTOMER」が47MBであることが確認できます。

このテーブルから、200万件のデータを削除してみます。
DELETE FROM T_CUSTOMER
WHERE C_ID <= 2000000;

データDelete

この時点で、このテーブルの占めているサイズを再度確認してみます。

テーブルサイズ確認

確認したところ、サイズは全く変化がないことが確認できました。
この理由は、DELETE文では、ORACLEのハイウォーターマーク(HWM)に変化はなく、一度確保した領域は解放されないためです。ハイウォーターマーク(HWM)とは、テーブルなどに割り当てたブロックの中で、今までデータが挿入されたことがある最後尾のブロックのことで、その位置が変わらないと領域は解放されません。

このハイウォーターマーク(HWM)を低下させる方法としては、以下のような方法があります。
1.TRUNCATE でデータを削除する。
  ただし、データを全件削除することになります。
  (※最初に CREATE TABLE AS SELECT で元のテーブルを別テ
   ーブルにコピーしておけば、TRUNCATE後にデータを流し
   込むことはできます)
2.Export/Importでデータを復元する。
  Exportした後に、一度テーブルをDROPして削除してから
  Importします。
3.ALTER TABLE MOVE でテーブルを新しいセグメントに移動し
  て再作成する。
4.ALTER TABLE SHRINK SPACE による断片化の解消。

それでは、1.のTRUNCATE文でデータを削除してみます。
その前に、データを戻せるように別テーブルにコピーします。

CREATE TABLE COPY_T_CUSTOMER NOLOGGING
AS SELECT * FROM T_CUSTOMER;

Create_table_as_select

TRUNCATE文でデータを削除します。
TRUNCATE TABLE T_CUSTOMER;

truncate

処理時間もDELETE文に比べたら、全然速いです。

この時点で再度、テーブルのサイズを確認してみます。

truncate後のテーブルサイズ

TRUNCATEしたら、テーブル、インデックスとも、0.625MB ということで、領域が大きく解放されたことが確認できます。また、最初にデータをコピーして作成した COPY_T_CUSTOMER テーブルも、22MBということで、元の64MBよりは小さく作成されることもわかります。

この後、コピーしたデータを戻してCOMMITし、コピーした不要なテーブルを削除します。
INSERT INTO T_CUSTOMER SELECT * FROM COPY_T_CUSTOMER;
COMMIT;
DROP TABLE COPY_T_CUSTOMER;

データinsert

再度、テーブルのサイズを確認してみます。

insert後のテーブルサイズ

確かに、T_CUSTOMERのテーブルのサイズは、22MB、インデックスも30MBとなりました。

次に、2.のExport/Importを試してみます。

再度、データを300万件作成します。これは、以前、バルク処理の題材のときの方法で作成します。

300万件登録

再度、テーブルのサイズを確認してみます。

テーブルサイズ確認

このテーブルから、再度200万件のデータを削除します。

200万件削除

この後、Export/Importによってテーブルを作成しなおします。

expdp blog_test/パスワード directory=DP_DIR tables=t_customer dumpfile=t_customer.dmp
(※処理を行う前にディレクトリの作成とディレクトリへのread/write権限の付与が必要)

expdp

 表を削除します。
DROP TABLE T_CUSTOMER;

Drop_table

先ほどエクスポートしたデータをインポートします。

impdp blog_test/パスワード directory=DP_DIR dumpfile=t_customer.dmp

impdp

ここで、テーブルのサイズを確認してみます。

import後のテーブルサイズ

T_CUSTOMERのテーブルのサイズは、22MB、インデックスも18MBとなり、領域が解放されたことが確認できました。

次に、3.のテーブルを新しいセグメントに複製し、既存のものを削除するという方法(テーブルの移動)による再作成を行ってみます。

まずは、再度300万件のデータに戻し、その後、200万件のデータを削除します。(動作については省略)
このときのテーブル・インデックスのサイズは、テーブルが64MB、インデックスが47MB。

ALTER TABLE T_CUSTOMER MOVE;

alter_table_move

再度、テーブルのサイズを確認してみます。

alter_move後のテーブルサイズ

テーブルのサイズは、テーブルが64MBから22MBに小さくなっていることが確認できました。
LONG / LONG RAW のある表は、この方法での移動はできないとのこと。

最後に、4.のALTER TABLE SHRINK SPACE による断片化の解消を試してみたいと思います。
こちらもまずは、再度300万件のデータに戻し、その後、200万件のデータを削除します。(動作については省略)
このときのテーブル・インデックスのサイズは、テーブルが64MB、インデックスが47MB。

断片化の解消をしたいテーブル「T_CUSTOMER」に対して、以下のSQLを順に実行します。

ALTER TABLE T_CUSTOMER ENABLE ROW MOVEMENT;
ALTER TABLE T_CUSTOMER SHRINK SPACE CASCADE;
ALTER TABLE T_CUSTOMER DISABLE ROW MOVEMENT;

alter_table_shrink

2番目のSHRINK SPACE CASCADE の処理に多少時間がかかりました。
ここで、テーブルのサイズを確認してみます。

alter_shrink後のテーブルサイズ

T_CUSTOMERのテーブルのサイズは、21.625MB、インデックスも16MBとなり、領域が解放されたことが確認できました。
なお、レコード数が少ない場合においては、ハイウォーターマークが思ったより低下しない場合もあり、これは、行移行や行連鎖の状態によって効果は異なるようです。連鎖状態を解消してからであれば、効果は高いということです。

今日は以上まで

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