今週の名言
「無知を恐れてはいけない。偽りの知識を恐れよ。」
ブレーズ・パスカル
「独立・依存エンティティ」をIdentity Columnで実装する
それでは、前回紹介した「独立・依存エンティティ（テーブル）」をIdentity Columnで実装してみましょう。
まずは、親表であるT_PAR表から作成します。Identity Columnのオプションは「ALWAYS」です。
忘れずに主キーも作成します。
SQL> CREATE TABLE t_par 2 (pid NUMBER GENERATED ALWAYS AS IDENTITY 3 ,pval VARCHAR2(10) 4 ); 表が作成されました。 SQL> ALTER TABLE t_par ADD CONSTRAINT pk_t_par PRIMARY KEY (pid); 表が変更されました。
次に子表となるT_CHD表を作成します。親表とほぼ同じですが、PID列に参照整合性制約（外部キー）を作成するのがポイントです。
SQL> CREATE TABLE t_chd 2 (cid NUMBER GENERATED ALWAYS AS IDENTITY 3 ,pid NUMBER NOT NULL 4 ,cval VARCHAR2(10) 5 ); 表が作成されました。 SQL> ALTER TABLE t_chd ADD CONSTRAINT pk_t_chd PRIMARY KEY (cid); 表が変更されました。 SQL> ALTER TABLE t_chd ADD CONSTRAINT fk_t_pid FOREIGN KEY (pid) 2 REFERENCES t_par(pid); 表が変更されました。
このタイミングで、表とともに作成された順序の名前も確認しておきましょう。
SQL> SELECT TABLE_NAME,COLUMN_NAME,SEQUENCE_NAME 2 FROM USER_TAB_IDENTITY_COLS 3 ORDER BY TABLE_NAME; TABLE_NAME COLUMN_NAME SEQUENCE_NAME --------------- --------------- --------------- T_CHD CID ISEQ$$_91944 T_PAR PID ISEQ$$_91941
レコード作成スクリプト
連続したレコードを作成する以下のPL/SQLスクリプトを実行します。
ハイライト行に示すように、上で確認した順序名を直接指定しています。
SQL> get ins.sql
1 DECLARE
2 i NUMBER;
3 j NUMBER;
4 BEGIN
5 FOR i IN 1..5 LOOP
6 INSERT INTO t_par(pval) VALUES ('Parent');
7 FOR j IN 1..5 LOOP
8 INSERT INTO t_chd(pid,cval) VALUES (ISEQ$$_91941.currval,'Child');
9 END LOOP;
10 COMMIT;
11 END LOOP;
12* END;
SQL> @ins.sql
PL/SQLプロシージャが正常に完了しました。
SQL> SELECT * FROM t_par;
PID PVAL
---------- ----------
1 Parent
2 Parent
3 Parent
4 Parent
5 Parent
SQL> SELECT * FROM t_chd;
CID PID CVAL
---------- ---------- ----------
1 1 Child
2 1 Child
3 1 Child
................................
23 5 Child
24 5 Child
25 5 Child
25行が選択されました。
親レコード１行毎に子レコードが５行作成され、全部で親レコード５行、子レコード２５行が作成されていることがわかります。
共有プールをフラッシュさせてみる
順序が生成したシーケンス番号は、共有プール上に「CACHE_SIZE」パラメータで指定された数だけキャッシュされています。
デフォルト値は２０となっており、次に生成されるシーケンス番号は以下のSQL文の「LAST_NUMBER」列で確認することができます。
SQL> SELECT SEQUENCE_NAME,INCREMENT_BY,CACHE_SIZE,LAST_NUMBER 2 FROM USER_SEQUENCES; SEQUENCE_NAME INCREMENT_BY CACHE_SIZE LAST_NUMBER --------------- ------------ ---------- ----------- ISEQ$$_91941 1 20 21 ISEQ$$_91944 1 20 41
ここで、共有プールをフラッシュさせるとどのようになるかを確認します。
SQL> ALTER SYSTEM FLUSH SHARED_POOL; システムが変更されました。
再度、レコード作成スクリプトを実行し新たな２５行をInsertします。
SQL> @ins.sql
PL/SQLプロシージャが正常に完了しました。
SQL> SELECT * FROM t_par;
PID PVAL
---------- ----------
1 Parent
2 Parent
3 Parent
4 Parent
5 Parent
21 Parent
22 Parent
23 Parent
24 Parent
25 Parent
10行が選択されました。
SQL> SELECT * FROM t_chd;
CID PID CVAL
---------- ---------- ----------
1 1 Child
2 1 Child
3 1 Child
................................
23 5 Child
24 5 Child
25 5 Child
41 21 Child
42 21 Child
43 21 Child
................................
63 25 Child
64 25 Child
65 25 Child
50行が選択されました。
上で確認した「LAST_NUMBER」列の値にシーケンス番号が飛んで採番されていることがわかります。（PID=21,CID=41）
飛び番は発生しますが重複が発生しているわけではないので、問題なくInsertできます。
レコード作成スクリプト改
「ISEQ$$_91941」のようにシステムが生成した順序の名前は、プログラムの中で使用するにはあまり適切ではありません。
できれば以下のハイライト行のように、開発者が認識できる順序の名前を使用できることが望ましいと考えます。
SQL> get ins1.sql
1 DECLARE
2 i NUMBER;
3 j NUMBER;
4 BEGIN
5 FOR i IN 1..5 LOOP
6 INSERT INTO t_par(pval) VALUES ('Parent');
7 FOR j IN 1..5 LOOP
8 INSERT INTO t_chd(pid,cval) VALUES (SEQ_PAR.currval,'Child');
9 END LOOP;
10 COMMIT;
11 END LOOP;
12* END;
順序は改名できない
テーブル名は「ALTER TABLE <テーブル名> RENAME <新テーブル名>」で改名することができます。
しかし、残念ながら順序は「ALTER SEQUENCE」文で名称を変更することはできません。
改名はできませんが、シノニムを作成することで「別名」を使用することができます。
順序名を再確認し、早速シノニムを作成してみましょう。
SQL> SELECT SEQUENCE_NAME,INCREMENT_BY,CACHE_SIZE,LAST_NUMBER 2 FROM USER_SEQUENCES; SEQUENCE_NAME INCREMENT_BY CACHE_SIZE LAST_NUMBER --------------- ------------ ---------- ----------- ISEQ$$_91941 1 20 41 ISEQ$$_91944 1 20 81 SQL> CREATE SYNONYM seq_par FOR ISEQ$$_91941; シノニムが作成されました。
レコード作成スクリプト改を実行する
それでは、順序名をシノニムに置き換えたスクリプトを実行してみます。
SQL> @ins1.sql
PL/SQLプロシージャが正常に完了しました。
SQL> SELECT * FROM t_par;
PID PVAL
---------- ----------
1 Parent
2 Parent
3 Parent
4 Parent
5 Parent
21 Parent
22 Parent
23 Parent
24 Parent
25 Parent
26 Parent
27 Parent
28 Parent
29 Parent
30 Parent
15行が選択されました。
SQL> SELECT * FROM t_chd;
CID PID CVAL
---------- ---------- ----------
1 1 Child
2 1 Child
3 1 Child
................................
63 25 Child
64 25 Child
65 25 Child
66 26 Child
67 26 Child
68 26 Child
................................
88 30 Child
89 30 Child
90 30 Child
75行が選択されました。
問題なく、新規レコードが作成されました。
もし、Identity Columnを使用しなければ
Oracle12c以前の環境で、同様のレコード作成スクリプトを書くと以下のようになります。
ハイライト行を冗長と見るか否かがIdentity Columnを使う判断基準となるのではないでしょうか？
SQL> get ins2.sql 1 DECLARE 2 i NUMBER; 3 j NUMBER; 4 BEGIN 5 FOR i IN 1..5 LOOP 6 INSERT INTO t_par(pid,pval) VALUES (SEQ_PAR.nextval,'Parent'); 7 FOR j IN 1..5 LOOP 8 INSERT INTO t_chd(cid,pid,cval) VALUES (SEQ_CHD.nextval,SEQ_PAR.currval,'Child'); 9 END LOOP; 10 COMMIT; 11 END LOOP; 12* END;
Identity Columnは使える機能か？
私がIdentity Columnという新機能を知った時、今回検証した親子関係のテーブルにおいて問題なく使用できるのであれば、プログラムを簡単にすることで開発効率と品質を向上させることができる注目すべき機能である予感がしました。
上で確認したように、一旦テーブルを作成して順序名を確認してからシノニム（別名）を作成するという、ちょっと面倒な手順を踏めば何とか使えそうな気がしますが、このままでは今一な感じもします。
せめて、以下のようにテーブル作成時に任意の順序名を指定できる仕様であればよいのですが。。。
SQL> CREATE TABLE t_par 2 (pid NUMBER GENERATED ALWAYS AS IDENTITY "SEQ_PAR" 3 ,pval VARCHAR2(10) 4 );
Identity Columnは、現時点では他RDBMSからの移行を容易にするために実装された機能だと認識しています。
しかし、将来的により使いやすいように改良されることを期待しつつ、このシリーズを終えたいと思います。
終わり