法人番号のデータが作成できましたので、器となるテーブルやロードに必要な資源を準備します。本編の事例では次の条件を想定しています。実際に試される場合は、読者の環境に会うよう読み替えて、入手したスクリプト等を編集してご利用下さい。
| ユーザ名 | HOUJIN |
|---|---|
| パスワード | HOUJIN |
| Oracle Netサービス名 | ORCL |
| 作成する表の名前 | HOUJIN_BANGOU |
| パーティション化 | ハッシュ・パーティショニング |
テーブルを作成するスクリプトです。提供しているサンプルは HOUJIN_BANGOU.sql で確認できます。項目の定義については国税庁のサイトにあるダウンロードファイルのデータ定義をご確認下さい。
create table HOUJIN_BANGOU ( SEQUENCE_NUMBER number(9) , CORPORATE_NUMBER char(13) , PROCESS char(2) , CORRECT char(1) , UPDATE_DATE date , CHANGE_DATE date , NAME varchar(600) , NAME_IMAGE_ID char(8) , KIND char(3) , PREFECTURE_NAME varchar2(40) , CITY_NAME varchar2(80) , STREET_NUMBER varchar2(1200) , ADDRESS_IMAGE_ID char(8) , PREFECTURE_CODE char(2) , CITY_CODE char(3) , POST_CODE char(7) , ADDRESS_OUTSIDE varchar2(1200) , ADDRESS_OUTSIDE_IMAGE_ID char(8) , CLOSE_DATE date , CLOSE_CAUSE char(2) , SUCCESSOR_CORPORATE_NUMBER char(13) , CHANGE_CAUSE varchar2(2000) , ASSIGNMENT_DATE date , LATEST char(1) , EN_NAME varchar2(300) , EN_PREFECTURE_NAME varchar2(9) , EN_CITY_NAME varchar2(600) , EN_ADDRESS_OUTSIDE varchar2(600) , FURIGANA varchar2(2000) , CONSTRAINT HOUJIN_BANGOU_PK PRIMARY KEY (CORPORATE_NUMBER) ) partition by hash (PREFECTURE_CODE) ( partition p01 , partition p02 , partition p03 , partition p04 , partition p05 , partition p06 , partition p07 , partition p08 );
差分データをインポートすると、法人が別な都道府県へ移動するケースが時々有ります。テーブルが都道府県コードでパーティション化されている為、上記の定義のままだと下記のエラーで差分データをが拒否されてしまいます。
ORA-14402. It makes rows moveable across each partition.
これを回避する為、次の変更文を実行します。
ALTER TABLE HOUJIN_BANGOU ENABLE ROW MOVEMENT;
差分データを SQL*Loader を使って適用する場合にはひと工夫必要です。SQL*Loader は内部的には INSERT を実行していますので、そのまま HOUJIN_BANGOU 表をターゲットとしてしまうと、HOUJIN_BANGOU_PK について主キー制約違反となってしまいます。実際には、マージを行いたいので、HOUJIN_BANGOU に法人番号が存在すれば UPDATE を、存在しなければ INSERT を実行するようにしなければなりません。
この操作を実現する為、更新可能なビュー(HOUJIN_BANGOU_VIEW)を作成し、INSTEAD OF ビュートリガーを追加します。
更新可能なビュー：
create or replace view HOUJIN_BANGOU_VIEW as select * from HOUJIN_BANGOU with check option constraint HOUJIN_BANGOU_V ;
INSTEAD OF ビュートリガー：
create or replace trigger TRG_HOUJIN_BANGOU_INS instead of insert on HOUJIN_BANGOU_VIEW begin update HOUJIN_BANGOU -- SEQUENCE_NUMBER = :new.SEQUENCE_NUMBER 更新対象外 -- CORPORATE_NUMBER = :new.CORPORATE_NUMBER 更新対象外 set PROCESS = :new.PROCESS , CORRECT = :new.CORRECT -- UPDATE_DATE = :new.UPDATE_DATE 更新対象外 , CHANGE_DATE = :new.CHANGE_DATE , NAME = :new.NAME , NAME_IMAGE_ID = :new.NAME_IMAGE_ID , KIND = :new.KIND , PREFECTURE_NAME = :new.PREFECTURE_NAME , CITY_NAME = :new.CITY_NAME , STREET_NUMBER = :new.STREET_NUMBER , ADDRESS_IMAGE_ID = :new.ADDRESS_IMAGE_ID , PREFECTURE_CODE = :new.PREFECTURE_CODE , CITY_CODE = :new.CITY_CODE , POST_CODE = :new.POST_CODE , ADDRESS_OUTSIDE = :new.ADDRESS_OUTSIDE , ADDRESS_OUTSIDE_IMAGE_ID = :new.ADDRESS_OUTSIDE_IMAGE_ID , CLOSE_DATE = :new.CLOSE_DATE , CLOSE_CAUSE = :new.CLOSE_CAUSE , SUCCESSOR_CORPORATE_NUMBER = :new.SUCCESSOR_CORPORATE_NUMBER , CHANGE_CAUSE = :new.CHANGE_CAUSE , ASSIGNMENT_DATE = :new.ASSIGNMENT_DATE , LATEST = :new.LATEST , EN_NAME = :new.EN_NAME , EN_PREFECTURE_NAME = :new.EN_PREFECTURE_NAME , EN_CITY_NAME = :new.EN_CITY_NAME , EN_ADDRESS_OUTSIDE = :new.EN_ADDRESS_OUTSIDE , FURIGANA = :new.FURIGANA where CORPORATE_NUMBER = :new.CORPORATE_NUMBER and CHANGE_DATE < :new.CHANGE_DATE-- 直近の更新以降に生じた差分 and :new.PROCESS in ( '11' -- 商号又は名称の変更 , '12' -- 国内所在地の変更 , '13' -- 国外所在地の変更 , '21' -- 登記記録の閉鎖等 , '22' -- 登記記録の復活等 , '71' -- 吸収合併 , '72' -- 吸収合併無効 , '81' -- 商号の登記の抹消 ) ; if sql%notfound then insert into HOUJIN_BANGOU ( SEQUENCE_NUMBER , CORPORATE_NUMBER , PROCESS , CORRECT , UPDATE_DATE , CHANGE_DATE , NAME , NAME_IMAGE_ID , KIND , PREFECTURE_NAME , CITY_NAME , STREET_NUMBER , ADDRESS_IMAGE_ID , PREFECTURE_CODE , CITY_CODE , POST_CODE , ADDRESS_OUTSIDE , ADDRESS_OUTSIDE_IMAGE_ID , CLOSE_DATE , CLOSE_CAUSE , SUCCESSOR_CORPORATE_NUMBER , CHANGE_CAUSE , ASSIGNMENT_DATE , LATEST , EN_NAME , EN_PREFECTURE_NAME , EN_CITY_NAME , EN_ADDRESS_OUTSIDE , FURIGANA ) select :new.SEQUENCE_NUMBER , :new.CORPORATE_NUMBER , :new.PROCESS , :new.CORRECT , :new.UPDATE_DATE , :new.CHANGE_DATE , :new.NAME , :new.NAME_IMAGE_ID , :new.KIND , :new.PREFECTURE_NAME , :new.CITY_NAME , :new.STREET_NUMBER , :new.ADDRESS_IMAGE_ID , :new.PREFECTURE_CODE , :new.CITY_CODE , :new.POST_CODE , :new.ADDRESS_OUTSIDE , :new.ADDRESS_OUTSIDE_IMAGE_ID , :new.CLOSE_DATE , :new.CLOSE_CAUSE , :new.SUCCESSOR_CORPORATE_NUMBER , :new.CHANGE_CAUSE , :new.ASSIGNMENT_DATE , :new.LATEST , :new.EN_NAME , :new.EN_PREFECTURE_NAME , :new.EN_CITY_NAME , :new.EN_ADDRESS_OUTSIDE , :new.FURIGANA from DUAL where not exists ( -- 既存の法人番号がない select null from HOUJIN_BANGOU where CORPORATE_NUMBER = :new.CORPORATE_NUMBER ); end if; end; /
加えて SQL*Loader のターゲットにHOUJIN_BANGOU_VIEWを指定します。
HOUJIN_BANGOU_VIEW をターゲットにする制御ファイル HOUJIN_BANGOU_VIEW.ctl：
LOAD CHARACTERSET AL32UTF8 BYTEORDERMARK NOCHECK INFILE "00_houjin_bangou_sabun.csv" "VAR 10" INTO TABLE "HOUJIN_BANGOU_VIEW" APPEND REENABLE FIELDS TERMINATED BY ',' TRAILING NULLCOLS ( "SEQUENCE_NUMBER" DECIMAL EXTERNAL(10) , "CORPORATE_NUMBER" CHAR(13) ENCLOSED BY '"' , "PROCESS" CHAR(2) ENCLOSED BY '"' , "CORRECT" CHAR(1) ENCLOSED BY '"' , "UPDATE_DATE" DATE(14) "YYYY-MM-DD" ENCLOSED BY '"' , "CHANGE_DATE" DATE(14) "YYYY-MM-DD" ENCLOSED BY '"' , "NAME" CHAR(600) ENCLOSED BY '"' , "NAME_IMAGE_ID" CHAR(8) ENCLOSED BY '"' , "KIND" CHAR(3) ENCLOSED BY '"' , "PREFECTURE_NAME" CHAR(40) ENCLOSED BY '"' , "CITY_NAME" CHAR(80) ENCLOSED BY '"' , "STREET_NUMBER" CHAR(1200) ENCLOSED BY '"' , "ADDRESS_IMAGE_ID" CHAR(8) ENCLOSED BY '"' , "PREFECTURE_CODE" CHAR(2) ENCLOSED BY '"' , "CITY_CODE" CHAR(3) ENCLOSED BY '"' , "POST_CODE" CHAR(7) ENCLOSED BY '"' , "ADDRESS_OUTSIDE" CHAR(1200) ENCLOSED BY '"' , "ADDRESS_OUTSIDE_IMAGE_ID" CHAR(8) ENCLOSED BY '"' , "CLOSE_DATE" DATE(14) "YYYY-MM-DD" ENCLOSED BY '"' , "CLOSE_CAUSE" CHAR(2) ENCLOSED BY '"' , "SUCCESSOR_CORPORATE_NUMBER" CHAR(13) ENCLOSED BY '"' , "CHANGE_CAUSE" CHAR(2000) ENCLOSED BY '"' , "ASSIGNMENT_DATE" DATE(14) "YYYY-MM-DD" ENCLOSED BY '"' , "LATEST" CHAR(1) ENCLOSED BY '"' , "EN_NAME" CHAR(300) ENCLOSED BY '"' , "EN_PREFECTURE_NAME" CHAR(9) ENCLOSED BY '"' , "EN_CITY_NAME" CHAR(600) ENCLOSED BY '"' , "EN_ADDRESS_OUTSIDE" CHAR(600) ENCLOSED BY '"' , "FURIGANA" CHAR(2000) ENCLOSED BY '"' )
SQL*Plus を使ってスクリプトを実行します。
sqlplus HOUJIN/HOUJIN@ORCL @HOUJIN_BANGOU
スクリプトが正しく実行されると次のようなメッセージが表示されます。
SQL*Plus: Release 11.2.0.2.0 Production on 月 10月 15 19:35:54 2018
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
に接続されました。
drop table HOUJIN_BANGOU
*
行1でエラーが発生しました。:
ORA-00942: 表またはビューが存在しません。
表が作成されました。
表が変更されました。
drop view HOUJIN_BANGOU_VIEW
*
行1でエラーが発生しました。:
ORA-00942: 表またはビューが存在しません。
ビューが作成されました。
トリガーが作成されました。
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing optionsとの接続が切断されました。
引数を与えて SQL*Loader を実行します。ロード時のパフォーマンスを考慮し、ダイレクト・パス・ロードを有効化し、一度に読取るデータ量を 50 万件としています。
sqlldr USERID=HOUJIN/HOUJIN@ORCL control=HOUJIN_BANGOU DIRECT=Y ROWS=500000
ロードが正しく実行されると次のようなメッセージが表示されます。
SQL*Loader: Release 11.2.0.2.0 - Production on 月 10月 15 19:47:51 2018 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. セーブ・データ・ポイントに達しました。 - 論理レコード件数500000 セーブ・データ・ポイントに達しました。 - 論理レコード件数1000000 セーブ・データ・ポイントに達しました。 - 論理レコード件数1500000 セーブ・データ・ポイントに達しました。 - 論理レコード件数2000000 セーブ・データ・ポイントに達しました。 - 論理レコード件数2500000 セーブ・データ・ポイントに達しました。 - 論理レコード件数3000000 セーブ・データ・ポイントに達しました。 - 論理レコード件数3500000 セーブ・データ・ポイントに達しました。 - 論理レコード件数4000000 セーブ・データ・ポイントに達しました。 - 論理レコード件数4500000 ロードは完了しました。 - 論理レコード件数4664375
引数を与えて SQL*Loader を実行します。INSTEAD OF ビュートリガーが作動するよう、従来パス・ロードを使用し、バインド変数用の領域と読み取りバッファを多めに与えて最適化します。
sqlldr USERID=HOUJIN/HOUJIN@ORCL control=HOUJIN_BANGOU_VIEW DIRECT=N ROWS=1000 READSIZE=10000000 BINDSIZE=10000000
ロードが正しく実行されると次のようなメッセージが表示されます。
SQL*Loader: Release 11.2.0.2.0 - Production on 月 10月 15 19:59:54 2018 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. コミット・ポイントに達しました。 - 論理レコード件数1000 コミット・ポイントに達しました。 - 論理レコード件数2000 コミット・ポイントに達しました。 - 論理レコード件数3000 コミット・ポイントに達しました。 - 論理レコード件数4000 コミット・ポイントに達しました。 - 論理レコード件数5000 コミット・ポイントに達しました。 - 論理レコード件数6000 コミット・ポイントに達しました。 - 論理レコード件数7000 コミット・ポイントに達しました。 - 論理レコード件数8000 コミット・ポイントに達しました。 - 論理レコード件数9000 コミット・ポイントに達しました。 - 論理レコード件数10000 コミット・ポイントに達しました。 - 論理レコード件数11000 コミット・ポイントに達しました。 - 論理レコード件数12000 コミット・ポイントに達しました。 - 論理レコード件数13000 コミット・ポイントに達しました。 - 論理レコード件数14000 コミット・ポイントに達しました。 - 論理レコード件数15000 コミット・ポイントに達しました。 - 論理レコード件数16000 コミット・ポイントに達しました。 - 論理レコード件数17000 コミット・ポイントに達しました。 - 論理レコード件数18000 コミット・ポイントに達しました。 - 論理レコード件数19000 コミット・ポイントに達しました。 - 論理レコード件数20000 コミット・ポイントに達しました。 - 論理レコード件数21000 コミット・ポイントに達しました。 - 論理レコード件数22000 コミット・ポイントに達しました。 - 論理レコード件数23000 コミット・ポイントに達しました。 - 論理レコード件数24000 コミット・ポイントに達しました。 - 論理レコード件数25000 コミット・ポイントに達しました。 - 論理レコード件数25316
本編を通じて次の事を確認する事が出来ました。
by 開発１号