Ｒｅ：ＳＡＬＯＯＮ　＆　ＶＢＡ

住所マスタを作成する(ストアドプロシージャ)…前半

PL/SQL

2014年06月06日 16時42分00秒

無名PL/SQLで住所マスタを登録する処理を作って来ましたが、その次の段階は、ストアドプロシージャでしょう
ということで、CREATE PROCEDURE で書きなおしてみました。他、CREATE TABLE等もまとめて一式掲載しました。

□EXCEL VBA:国土交通省国土政策局　国土情報課 > 位置参照情報ファイルをソート、コピーする
これは、事前準備のEXCEL VBA
Option Explicit
Sub AdrsCSV_SortCopy()
　 Sheets("フォルダ").Select
　 Dim FromDir　As String: FromDir = Cells(2, 3).Value '複写元フォルダ
　 Dim ToDir　　As String: ToDir = Cells(3, 3).Value　 '複写先フォルダ
　
　 Dim Filename As String
　 Dim Sh0　　　As Worksheet
　 Dim Sh　　　 As Worksheet
　 Dim i　　　　As Long: i = 0
　 Set Sh0 = ActiveSheet
　 Filename = Dir(FromDir & "\*.csv")
　 Do Until Filename = ""
　　　Set Sh = Workbooks.Open(FromDir & "\" & Filename).Sheets(1)
　　　Worksheets(1).Activate
　　　Columns("E:E").NumberFormatLocal = "0"
　　　Range(Range("A1:J1"), Selection.End(xlDown)).Select
　　　Selection.Sort Key1:=Range("E2"), Order1:=xlAscending, Header:=xlYes
　　　ActiveWorkbook.SaveAs Filename:=ToDir & "\" & Filename, _
　　　　　　　　　　　　　　FileFormat:=xlCSV, _
　　　　　　　　　　　　　　CreateBackup:=False
　　　Application.DisplayAlerts = False
　　　ActiveWindow.Close
　　　Application.DisplayAlerts = True
　　　Filename = Dir()
　 Loop
　 MsgBox "住所データ整列複写終了！"
End Sub

↓ここから、PL/SQL
■住所マスタ(都道府県)DDL
※　KENCD と KEYKENCD と地域コード系のコード領域を２つにしているのは、
　　統廃合を考えてのことです。県単位で統廃合は今のところありませんが、
　　道州制とかあれば、どうなるか分かりませんしね(ないとは思うが…)
　　今、廃止された方のコードはロードしていないので尚更意味なしです。
　　他の地区コードも同じですが、無駄というお考えであれば、一つにするのもありだと思います。
CREATE TABLE MST_ADRS1
( KENKNO　　 NUMBER(2,0),
　 KENCD　　　NUMBER(2,0),
　 KKENMEI　　VARCHAR2(24 BYTE),
　 KENMEI　　 VARCHAR2(16 BYTE),
　 KEYKENCD　 NUMBER(2,0),
　 HAISIFLG　 NUMBER(1,0),
　 PRIMARY KEY (KENKNO)
);
/
　COMMENT ON COLUMN MST_ADRS1.KENKNO　　 IS '都道府県管理番号';
　COMMENT ON COLUMN MST_ADRS1.KENCD　　　IS '都道府県コード';
　COMMENT ON COLUMN MST_ADRS1.KKENMEI　　IS '都道府県名カナ';
　COMMENT ON COLUMN MST_ADRS1.KENMEI　　 IS '都道府県名';
　COMMENT ON COLUMN MST_ADRS1.KEYKENCD　 IS '新都道府県コード';
　COMMENT ON COLUMN MST_ADRS1.HAISIFLG　 IS '廃止フラグ';
　COMMENT ON TABLE　MST_ADRS1　　　　　　IS '都道府県マスタ';
　CREATE INDEX INX_ADRS1_1 ON MST_ADRS1 (KENCD);
　CREATE INDEX INX_ADRS1_2 ON MST_ADRS1 (KKENMEI);

■住所マスタ(市区町村)DDL
CREATE TABLE MST_ADRS2
( SIKUKNO　　NUMBER(6,0),
　 KENKNO　　 NUMBER(2,0),
　 SIKUCD　　 NUMBER(3,0),
　 KSIKUMEI　 VARCHAR2(72 BYTE),
　 SIKUMEI　　VARCHAR2(48 BYTE),
　 KEYKENCD　 NUMBER(2,0),
　 KEYSIKUCD　NUMBER(3,0),
　 HAISIFLG　 NUMBER(1,0),
　 KENMEIKBN　NUMBER(1,0),
　 PRIMARY KEY (SIKUKNO)
);
/
　COMMENT ON COLUMN MST_ADRS2.SIKUKNO　　IS '市区町村管理番号';
　COMMENT ON COLUMN MST_ADRS2.KENKNO　　 IS '都道府県管理番号';
　COMMENT ON COLUMN MST_ADRS2.SIKUCD　　 IS '市区町村コード';
　COMMENT ON COLUMN MST_ADRS2.KSIKUMEI　 IS '市区町村名カナ';
　COMMENT ON COLUMN MST_ADRS2.SIKUMEI　　IS '市区町村名';
　COMMENT ON COLUMN MST_ADRS2.KEYKENCD　 IS '新都道府県コード';
　COMMENT ON COLUMN MST_ADRS2.KEYSIKUCD　IS '新市区町村コード';
　COMMENT ON COLUMN MST_ADRS2.HAISIFLG　 IS '廃止フラグ';
　COMMENT ON COLUMN MST_ADRS2.KENMEIKBN　IS '県名不要区分';
　COMMENT ON TABLE　MST_ADRS2　　　　　　IS '市区町村マスタ';
　CREATE INDEX INX_ADRS2_1 ON MST_ADRS2 (KENKNO, SIKUCD);
　CREATE INDEX INX_ADRS2_2 ON MST_ADRS2 (KSIKUMEI);

■住所マスタ(大字小字)DDL
CREATE TABLE MST_ADRS3
( AZAKNO　　　 VARCHAR2(12 BYTE),
　 SIKUKNO　　　NUMBER(5,0),
　 OAZACD　　　 VARCHAR2(4 BYTE),
　 KOAZACD　　　VARCHAR2(4 BYTE),
　 OAZAMEI　　　VARCHAR2(72 BYTE),
　 KOAZAMEI　　 VARCHAR2(72 BYTE),
　 YUBIN　　　　VARCHAR2(8 BYTE),
　 KEYKENCD　　 NUMBER(2,0),
　 KEYSIKUCD　　NUMBER(3,0),
　 KEYOAZACD　　VARCHAR2(4 BYTE),
　 KEYKOAZACD　 VARCHAR2(4 BYTE),
　 HAISIFLG　　 NUMBER(1,0),
　 PRIMARY KEY (AZAKNO)
);
/
　COMMENT ON COLUMN MST_ADRS3.AZAKNO　　　 IS '大字小字管理番号';
　COMMENT ON COLUMN MST_ADRS3.SIKUKNO　　　IS '市区町村管理番号';
　COMMENT ON COLUMN MST_ADRS3.OAZACD　　　 IS '大字・通称名コード';
　COMMENT ON COLUMN MST_ADRS3.KOAZACD　　　IS '字・丁目コード';
　COMMENT ON COLUMN MST_ADRS3.OAZAMEI　　　IS '大字・通称名';
　COMMENT ON COLUMN MST_ADRS3.KOAZAMEI　　 IS '字・丁目名';
　COMMENT ON COLUMN MST_ADRS3.YUBIN　　　　IS '郵便番号';
　COMMENT ON COLUMN MST_ADRS3.KEYKENCD　　 IS '新都道府県コード';
　COMMENT ON COLUMN MST_ADRS3.KEYSIKUCD　　IS '新市区町村コード';
　COMMENT ON COLUMN MST_ADRS3.KEYOAZACD　　IS '新大字・通称名コード';
　COMMENT ON COLUMN MST_ADRS3.KEYKOAZACD　 IS '新字・丁目コード';
　COMMENT ON COLUMN MST_ADRS3.HAISIFLG　　 IS '廃止フラグ';
　COMMENT ON TABLE　MST_ADRS3　　　　　　　IS '字・丁目マスタ';
　CREATE INDEX INX_ADRS3_1 ON MST_ADRS3 (SIKUKNO, OAZACD, KOAZACD);
　CREATE INDEX INX_ADRS3_2 ON MST_ADRS3 (YUBIN);

■郵便番号データDDL
CREATE TABLE ZIP_DATA
( ADRSCD　　 NUMBER(10,0) NOT NULL ENABLE,
　 KENCD　　　NUMBER(2,0),
　 SIKUCD　　 NUMBER(5,0),
　 CYOCD　　　NUMBER(9,0),
　 YUBIN　　　VARCHAR2(8 BYTE),
　 JIGFLG　　 NUMBER(1,0),
　 HAISIFLG　 NUMBER(1,0),
　 KENMEI　　 VARCHAR2(20 BYTE),
　 KKENMEI　　VARCHAR2(20 BYTE),
　 SIKUMEI　　VARCHAR2(30 BYTE),
　 KSIKUMEI　 VARCHAR2(40 BYTE),
　 CYOMEI　　 VARCHAR2(50 BYTE),
　 KCYOMEI　　VARCHAR2(80 BYTE),
　 CYOHOSOKU　VARCHAR2(30 BYTE),
　 TOURINA　　VARCHAR2(50 BYTE),
　 CYOME　　　VARCHAR2(50 BYTE),
　 KCYOME　　 VARCHAR2(60 BYTE),
　 HOSOKU　　 VARCHAR2(800 BYTE),
　 JIGYOSYO　 VARCHAR2(75 BYTE),
　 KJIGYOSYO　VARCHAR2(150 BYTE),
　 JIGADRS　　VARCHAR2(100 BYTE),
　 NEWADRSCD　VARCHAR2(8 BYTE),
　 CONSTRAINT ZIP_DATA_PK PRIMARY KEY (ADRSCD)
);
　COMMENT ON COLUMN ZIP_DATA.ADRSCD　　IS '住所CD';
　COMMENT ON COLUMN ZIP_DATA.KENCD　　 IS '都道府県CD'
　COMMENT ON COLUMN ZIP_DATA.SIKUCD　　IS '市区町村CD';
　COMMENT ON COLUMN ZIP_DATA.CYOCD　　 IS '町域CD';
　COMMENT ON COLUMN ZIP_DATA.YUBIN　　 IS '郵便番号';
　COMMENT ON COLUMN ZIP_DATA.JIGFLG　　IS '事業所フラグ';
　COMMENT ON COLUMN ZIP_DATA.HAISIFLG　IS '廃止フラグ';
　COMMENT ON COLUMN ZIP_DATA.KENMEI　　IS '都道府県';
　COMMENT ON COLUMN ZIP_DATA.KKENMEI　 IS '都道府県カナ';
　COMMENT ON COLUMN ZIP_DATA.SIKUMEI　 IS '市区町村';
　COMMENT ON COLUMN ZIP_DATA.KSIKUMEI　IS '市区町村カナ';
　COMMENT ON COLUMN ZIP_DATA.CYOMEI　　IS '町域';
　COMMENT ON COLUMN ZIP_DATA.KCYOMEI　 IS '町域カナ';
　COMMENT ON COLUMN ZIP_DATA.CYOHOSOKU IS '町域補足';
　COMMENT ON COLUMN ZIP_DATA.TOURINA　 IS '京都通り名';
　COMMENT ON COLUMN ZIP_DATA.CYOME　　 IS '字丁目';
　COMMENT ON COLUMN ZIP_DATA.KCYOME　　IS '字丁目カナ';
　COMMENT ON COLUMN ZIP_DATA.HOSOKU　　IS '補足';
　COMMENT ON COLUMN ZIP_DATA.JIGYOSYO　IS '事業所名';
　COMMENT ON COLUMN ZIP_DATA.KJIGYOSYO IS '事業所名カナ';
　COMMENT ON COLUMN ZIP_DATA.JIGADRS　 IS '事業所住所';
　COMMENT ON COLUMN ZIP_DATA.NEWADRSCD IS '新住所CD';
　COMMENT ON TABLE　ZIP_DATA　　　　　 IS '郵便番号データ';

■郵便番号ヴューの作成
CREATE OR REPLACE FORCE VIEW ZIP_VIEW (ADRSCD, KENCD, SIKUCD, CYOCD, YUBIN, OAZAMEI, KOAZAMEI) AS
　SELECT ADRSCD,
　　　 KENCD,
　　　 SIKUCD - (KENCD * 1000) AS SIKUCD,
　　　 CYOCD,
　　　 YUBIN,
　　　 DECODE(CYOMEI,NULL,NULL,REPLACE(REPLACE(TRANSLATE(
　　　 SUBSTR(CYOMEI,1,INSTR(TRANSLATE(CYOMEI,'０１２３４５６７８９','@@@@@@@@@@'),'@@')) ||
　　　 DECODE(INSTR(TRANSLATE(CYOMEI,'０１２３４５６７８９','@@@@@@@@@@'),'@@'),0,'','十') ||
　　　 SUBSTR(CYOMEI,INSTR(TRANSLATE(CYOMEI,'０１２３４５６７８９','@@@@@@@@@@'),'@@')+1,
　　　 LENGTH(CYOMEI) - INSTR(TRANSLATE(CYOMEI,'０１２３４５６７８９','@@@@@@@@@@'),'@@'))
　　　 ,'０１２３４５６７８９','十一二三四五六七八九'),'一十','十'),'十十','十')) AS OAZAMEI,
　　　 DECODE(CYOME || TOURINA,NULL,NULL,REPLACE(REPLACE(TRANSLATE(
　　　 SUBSTR(CYOME || TOURINA,1,INSTR(TRANSLATE(CYOME || TOURINA,'０１２３４５６７８９','@@@@@@@@@@'),'@@')) ||
　　　 DECODE(INSTR(TRANSLATE(CYOME || TOURINA,'０１２３４５６７８９','@@@@@@@@@@'),'@@'),0,'','十') ||
　　　 SUBSTR(CYOME || TOURINA,INSTR(TRANSLATE(CYOME || TOURINA,'０１２３４５６７８９','@@@@@@@@@@'),'@@')+1,
　　　 LENGTH(CYOME || TOURINA) - INSTR(TRANSLATE(CYOME || TOURINA,'０１２３４５６７８９','@@@@@@@@@@'),'@@'))
　　　 ,'０１２３４５６７８９','十一二三四五六七八九'),'一十','十'),'十十','十')) AS KOAZAMEI
　FROM ZIP_DATA Z
WHERE JIGFLG = 0
　 AND HAISIFLG = 0
　 AND CYOMEI IS NOT NULL
　 AND HOSOKU IS NULL
ORDER BY CYOCD;
/
　COMMENT ON COLUMN ZIP_VIEW.ADRSCD　 IS '住所CD';
　COMMENT ON COLUMN ZIP_VIEW.KENCD　　IS '都道府県CD';
　COMMENT ON COLUMN ZIP_VIEW.SIKUCD　 IS '市区町村CD';
　COMMENT ON COLUMN ZIP_VIEW.CYOCD　　IS '町域CD';
　COMMENT ON COLUMN ZIP_VIEW.YUBIN　　IS '郵便番号';
　COMMENT ON COLUMN ZIP_VIEW.OAZAMEI　IS '大字名';
　COMMENT ON COLUMN ZIP_VIEW.KOAZAMEI IS '小字名';
　COMMENT ON VIEW ZIP_VIEW　　　　　IS '郵便番号ヴュー';

■CSV行分割ファンクション
create or replace FUNCTION SPLIT ( INTEXT VARCHAR2,
　　　　　　　　　　　　　　　　　 ITEMNO INTEGER,
　　　　　　　　　　　　　　　　　 DELIM VARCHAR2)
RETURN VARCHAR2
IS
MYITEM VARCHAR2(255);
BEGIN
　　IF INTEXT IS NULL OR ITEMNO IS NULL OR DELIM IS NULL THEN
　　　　RETURN 'INVALID PARAMETERS !';
　　END IF;
　　IF ITEMNO = 1 THEN
　　　　MYITEM := SUBSTR( INTEXT, 1, INSTR( INTEXT || DELIM, DELIM, 1, 1 ) - 1 );
　　ELSE
　　　　MYITEM := SUBSTR( INTEXT, INSTR( INTEXT, DELIM, 1, ITEMNO-1 ) + 1,
　　　　INSTR( INTEXT || DELIM, DELIM, 1, ITEMNO ) - INSTR( INTEXT, DELIM, 1, ITEMNO-1 ) - 1);
　　END IF;
　　RETURN MYITEM;
END;
/

■住所マスタ登録(都道府県・市区町村)
create or replace PROCEDURE INS_ADRS IS
/**************************************************************************/
/* 住所マスタ登録(都道府県・市区町村)　　　　　　　　　　　　　　INS_ADRS */
/* 総務省トップ > 政策 > 地方行財政 > 電子自治体 > 全国地方公共団体コード */
/*　 http://www.soumu.go.jp/denshijiti/code.html より　　　　　　　　　　 */
/*　　　　　　　　　　ダウンロードし、各シートをCSV形式に変換し入力とする */
/**************************************************************************/
　 DIR_NAME　　　 VARCHAR2(30);
　 FILE_NAME1　　 VARCHAR2(14);
　 FILE_NAME2　　 VARCHAR2(14);
　 FH　　　　　　 UTL_FILE.FILE_TYPE;
　 READ_LINE　　　VARCHAR2(1024);
　 V_DANTAI　　　 VARCHAR2(6);
　 T_KENCD　　　　MST_ADRS1.KENCD%TYPE;
　 T_KKENMEI　　　MST_ADRS1.KKENMEI%TYPE;
　 T_KENMEI　　　 MST_ADRS1.KENMEI%TYPE;
　 T_SIKUCD　　　 MST_ADRS2.SIKUCD%TYPE;
　 T_KSIKUMEI　　 MST_ADRS2.KSIKUMEI%TYPE;
　 T_SIKUMEI　　　MST_ADRS2.SIKUMEI%TYPE;
　 READCNT1　　　 NUMBER;
　 READCNT2　　　 NUMBER;
　 INSCNT1　　　　NUMBER;
　 INSCNT2　　　　NUMBER;
　 UPDCNT　　　　 NUMBER;
BEGIN
　 DIR_NAME　 := 'INOUTFILE';
　 FILE_NAME1 := '000285753D.csv';　　　　　　 -- 現在の団体
　 FILE_NAME2 := '000285753S.csv';　　　　　　 -- 政令指定都市
　 INSCNT1　　:= 0;
　 INSCNT2　　:= 0;
-- 都道府県の削除
　 DELETE FROM MST_ADRS1;
-- 市区町村の削除
　 DELETE FROM MST_ADRS2;
-- 団体の登録
　 FH　　　　　　　　　:= UTL_FILE.FOPEN(DIR_NAME, FILE_NAME1, 'R');
　 READCNT1　　　　　　:= 0;
　 BEGIN
　　　LOOP
　　　　 UTL_FILE.GET_LINE(FH, READ_LINE);
　　　　 READCNT1　　　:= READCNT1 + 1;
　　　　 IF READCNT1 > 1 THEN　　　　　　　　　 -- 一行目は見出し
　　　　　　V_DANTAI　 := SUBSTR(READ_LINE,1,6);
　　　　　　T_KENCD　　:= TO_NUMBER(SUBSTR(V_DANTAI,1,2));
　　　　　　T_KENMEI　 := SPLIT(READ_LINE,2,',');
　　　　　　T_KKENMEI　:= SPLIT(READ_LINE,4,',');
　　　　　　T_SIKUCD　 := TO_NUMBER(SUBSTR(V_DANTAI,3,3));
　　　　　　T_SIKUMEI　:= SPLIT(READ_LINE,3,',');
　　　　　　T_KSIKUMEI := SPLIT(READ_LINE,5,',');
　　　　　　IF V_DANTAI IS NOT NULL THEN
　　　　　　IF T_SIKUCD = 0 THEN
　　　　　　-- 都道府県の登録
　　　　　　　 INSERT INTO MST_ADRS1(KENKNO,
　　　　　　　　　　　　　　　　　　　KENCD,
　　　　　　　　　　　　　　　　　　　KKENMEI,
　　　　　　　　　　　　　　　　　　　KENMEI,
　　　　　　　　　　　　　　　　　　　KEYKENCD,
　　　　　　　　　　　　　　　　　　　HAISIFLG)
　　　　　　　　　　　　　　　 VALUES(T_KENCD,　　　　　 -- 都道府県管理番号
　　　　　　　　　　　　　　　　　　　T_KENCD,　　　　　 -- 都道府県コード
　　　　　　　　　　　　　　　　　　　T_KKENMEI,　　　　 -- 都道府県名カナ
　　　　　　　　　　　　　　　　　　　T_KENMEI,　　　　　-- 都道府県名
　　　　　　　　　　　　　　　　　　　T_KENCD,　　　　　 -- 新都道府県コード
　　　　　　　　　　　　　　　　　　　0);　　　　　　　　-- 廃止フラグ
　　　　　　　 INSCNT1 := INSCNT1 + 1;
　　　　　　ELSE
　　　　　　-- 市区町村の登録
　　　　　　　 INSERT INTO MST_ADRS2(SIKUKNO,
　　　　　　　　　　　　　　　　　　　KENKNO,
　　　　　　　　　　　　　　　　　　　SIKUCD,
　　　　　　　　　　　　　　　　　　　KSIKUMEI,
　　　　　　　　　　　　　　　　　　　SIKUMEI,
　　　　　　　　　　　　　　　　　　　KEYKENCD,
　　　　　　　　　　　　　　　　　　　KEYSIKUCD,
　　　　　　　　　　　　　　　　　　　HAISIFLG,
　　　　　　　　　　　　　　　　　　　KENMEIKBN)
　　　　　　　　　　　　　　　 VALUES(TO_NUMBER(V_DANTAI),　-- 市区町村管理番号
　　　　　　　　　　　　　　　　　　　T_KENCD,　　　　　　　-- 都道府県管理番号
　　　　　　　　　　　　　　　　　　　T_SIKUCD,　　　　　　 -- 市区町村コード
　　　　　　　　　　　　　　　　　　　T_KSIKUMEI,　　　　　 -- 市区町村名カナ
　　　　　　　　　　　　　　　　　　　T_SIKUMEI,　　　　　　-- 市区町村名
　　　　　　　　　　　　　　　　　　　T_KENCD,　　　　　　　-- 新都道府県コード
　　　　　　　　　　　　　　　　　　　T_SIKUCD,　　　　　　 -- 新市区町村コード
　　　　　　　　　　　　　　　　　　　0,　　　　　　　　　　-- 廃止フラグ
　　　　　　　　　　　　　　　　　　　0);　　　　　　　　　 -- 県名不要区分
　　　　　　　 INSCNT2 := INSCNT2 + 1;
　　　　　　END IF;
　　　　　　ELSE
　　　　　　　 DBMS_OUTPUT.PUT_LINE( 'V_DANTAI：' || V_DANTAI );
　　　　　　END IF;
　　　　 END IF;
　　　END LOOP;
　 EXCEPTION
　　　WHEN NO_DATA_FOUND THEN NULL;
　 END;
　 UTL_FILE.FCLOSE(FH);
-- 政令指定都市の登録
　 FH　　　　　　　 := UTL_FILE.FOPEN(DIR_NAME, FILE_NAME2, 'R');
　 READCNT2　　　　 := 0;
　 UPDCNT　　　　　 := 0;
　 BEGIN
　　　LOOP
　　　　 UTL_FILE.GET_LINE(FH, READ_LINE);
　　　　 READCNT2　 := READCNT2 + 1;
　　　　 V_DANTAI　 := SUBSTR(READ_LINE,1,6);
　　　　 T_KENCD　　:= TO_NUMBER(SUBSTR(V_DANTAI,1,2));
　　　　 T_SIKUCD　 := TO_NUMBER(SUBSTR(V_DANTAI,3,3));
　　　　 T_SIKUMEI　:= SPLIT(READ_LINE,2,',');
　　　　 T_KSIKUMEI := SPLIT(READ_LINE,3,',');
　　　　 IF V_DANTAI IS NOT NULL THEN
　　　　 IF SUBSTR(T_SIKUMEI,LENGTH(T_SIKUMEI),1) = '区' THEN
　　　　 -- 政令指定都市区の登録
　　　　　　INSERT INTO MST_ADRS2(SIKUKNO,
　　　　　　　　　　　　　　　　　 KENKNO,
　　　　　　　　　　　　　　　　　 SIKUCD,
　　　　　　　　　　　　　　　　　 KSIKUMEI,
　　　　　　　　　　　　　　　　　 SIKUMEI,
　　　　　　　　　　　　　　　　　 KEYKENCD,
　　　　　　　　　　　　　　　　　 KEYSIKUCD,
　　　　　　　　　　　　　　　　　 HAISIFLG,
　　　　　　　　　　　　　　　　　 KENMEIKBN)
　　　　　　　　　　　　　　VALUES(TO_NUMBER(V_DANTAI),　-- 市区町村管理番号
　　　　　　　　　　　　　　　　　 T_KENCD,　　　　　　　-- 都道府県管理番号
　　　　　　　　　　　　　　　　　 T_SIKUCD,　　　　　　 -- 市区町村コード
　　　　　　　　　　　　　　　　　 HIRA2HAN(T_KSIKUMEI), -- 市区町村名カナ
　　　　　　　　　　　　　　　　　 T_SIKUMEI,　　　　　　-- 市区町村名
　　　　　　　　　　　　　　　　　 T_KENCD,　　　　　　　-- 新都道府県コード
　　　　　　　　　　　　　　　　　 T_SIKUCD,　　　　　　 -- 新市区町村コード
　　　　　　　　　　　　　　　　　 0,　　　　　　　　　　-- 廃止フラグ
　　　　　　　　　　　　　　　　　 1);　　　　　　　　　 -- 県名不要区分
　　　　　　INSCNT2 := INSCNT2 + 1;
　　　　 ELSE
　　　　 -- 都道府県有無フラグの更新
　　　　　　UPDATE MST_ADRS2 SET KENMEIKBN = 1
　　　　　　 WHERE KEYKENCD　= T_KENCD
　　　　　　　 AND KEYSIKUCD = T_SIKUCD;
　　　　　　UPDCNT := UPDCNT + 1;
　　　　 END IF;
　　　　 ELSE
　　　　　　DBMS_OUTPUT.PUT_LINE( 'V_DANTAI：' || V_DANTAI );
　　　　 END IF;
　　　　　　
　　　END LOOP;
　　　COMMIT;
　 EXCEPTION
　　　WHEN NO_DATA_FOUND THEN NULL;
　 END;
　 UTL_FILE.FCLOSE(FH);
　 DBMS_OUTPUT.PUT_LINE( '結果=========================' );
　 DBMS_OUTPUT.PUT_LINE( '団体CSV読込件数：' || READCNT1 );
　 DBMS_OUTPUT.PUT_LINE( '政令CSV読込件数：' || READCNT2 );
　 DBMS_OUTPUT.PUT_LINE( 'ADRS1　登録件数：' || INSCNT1 );
　 DBMS_OUTPUT.PUT_LINE( 'ADRS2　登録件数：' || INSCNT2 );
　 DBMS_OUTPUT.PUT_LINE( 'ADRS2　更新件数：' || UPDCNT );
　 COMMIT;
EXCEPTION
　 WHEN OTHERS THEN
　　　DBMS_OUTPUT.PUT_LINE( 'エラーが発生しました。：' || SQLERRM);
END;

■住所マスタ登録(大字・小字)
create or replace PROCEDURE INS_ADRS3_ALL IS
/*************************************************************************/
/* 住所マスタ登録(大字・小字)全都道府県　　　　　　　　　　INS_ADRS3_ALL */
/* 国土交通省国土政策局　国土情報課 > 位置参照情報ダウンロードサービス　 */
/*　 http://nlftp.mlit.go.jp/cgi-bin/isj/dls/_view_cities_wards.cgi より */
/*　　　　　　　　　　　　　　　　　ダウンロードしたCSV形式を入力とする */
/*************************************************************************/
BEGIN
　 FOR KENCD IN 1..47 LOOP
　　　INS_ADRS3(KENCD);
　 END LOOP;　
END;
/
create or replace PROCEDURE INS_ADRS3 ( V_KENCD IN NUMBER) IS
/*************************************************************************/
/* 住所マスタ登録(大字・小字)　　　　　　　　　　　　　　　　　INS_ADRS3 */
/* 国土交通省国土政策局　国土情報課 > 位置参照情報ダウンロードサービス　 */
/*　 http://nlftp.mlit.go.jp/cgi-bin/isj/dls/_view_cities_wards.cgi より */
/*　　　　　　　　　　　　　　　　　ダウンロードしたCSV形式を入力とする */
/*************************************************************************/
　 KENCD　　　　 VARCHAR2(2)　:= LPAD(TO_CHAR(V_KENCD),2,'0');
　 DIR_NAME　　　VARCHAR2(30) := 'INOUTFILE';
　 FILE_NAME　　 VARCHAR2(14) := KENCD || '_2012.csv';
　 FH　　　　　　UTL_FILE.FILE_TYPE;
　 READ_LINE　　 VARCHAR2(1024);
　 V_AZACYOCD　　VARCHAR2(12);
　 B_AZACYOCD　　VARCHAR2(12);
　 T_KENCD　　　 MST_ADRS3.KEYKENCD%TYPE;
　 T_SIKUCD　　　MST_ADRS3.KEYSIKUCD%TYPE;
　 T_OAZACD　　　MST_ADRS3.KEYOAZACD%TYPE;
　 T_KOAZACD　　 MST_ADRS3.KEYKOAZACD%TYPE;
　 B_OAZACD　　　MST_ADRS3.KEYOAZACD%TYPE;
　 B_KOAZACD　　 MST_ADRS3.KEYKOAZACD%TYPE;
　 V_AZACYOMEI　 VARCHAR2(160);
　 F_OAZAMEI　　 VARCHAR2(160);
　 T_OAZAMEI　　 MST_ADRS3.OAZAMEI%TYPE;
　 T_KOAZAMEI　　MST_ADRS3.KOAZAMEI%TYPE;
　 READCNT　　　 NUMBER;
　 INSCNT　　　　NUMBER;
　 UPDCNT　　　　NUMBER;
　 i　　　　　　 NUMBER;
BEGIN
　 DBMS_OUTPUT.PUT_LINE( 'KENCD = ' || KENCD);
　 FH　　　　 := UTL_FILE.FOPEN(DIR_NAME,FILE_NAME,'R');
　 READCNT　　:= 0;
　 INSCNT　　 := 0;
　 UPDCNT　　 := 0;
　 B_AZACYOCD := '000000000000';
　 B_OAZACD　 := '0000';
　 B_KOAZACD　:= '0000';
-- 大字・小字の削除
　 DELETE FROM MST_ADRS3 WHERE KEYKENCD = TO_NUMBER(KENCD);
　 BEGIN
　　　UTL_FILE.GET_LINE(FH, READ_LINE);　　-- 1件目は見出し(読み飛し)
　　　LOOP
　　　　 READCNT　　 := READCNT + 1;
　　　　 UTL_FILE.GET_LINE(FH, READ_LINE);
　　　　 V_AZACYOCD　:= LPAD(REPLACE(SPLIT(READ_LINE,5,','),'',''),12,'0');
　　　　 EXIT WHEN V_AZACYOCD IS NULL;
　　　　 V_AZACYOMEI := REPLACE(SPLIT(READ_LINE,6,','),'','');
　　　　 T_KENCD　　 := TO_NUMBER(SUBSTR(V_AZACYOCD,1,2));
　　　　 T_SIKUCD　　:= TO_NUMBER(SUBSTR(V_AZACYOCD,3,3));
　　　　 T_OAZACD　　:= SUBSTR(V_AZACYOCD,6,4);
　　　　 T_KOAZACD　 := '0' || SUBSTR(V_AZACYOCD,10,3);
　　　　 T_OAZAMEI　 := NULL;
　　　　 T_KOAZAMEI　:= NULL;
　　　　 IF T_KOAZACD　 = '0000' THEN
　　　　　　T_OAZAMEI　:= V_AZACYOMEI;
　　　　　　T_KOAZAMEI := NULL;
　　　　 ELSE
　　　　　　i := INSTR(V_AZACYOMEI,
　　　　　　　　　　　 CASE SUBSTR(T_KOAZACD,1,3)
　　　　　　　　　　　 WHEN '000' THEN
　　　　　　　　　　　　　TRANSLATE(TO_NUMBER(T_KOAZACD),'123456789','一二三四五六七八九')
　　　　　　　　　　　 ELSE
　　　　　　　　　　　　　REPLACE(TRANSLATE(SUBSTR(T_KOAZACD,3,1),'123456789','十二三四五六七八九') ||
　　　　　　　　　　　　　　　　　'十' ||
　　　　　　　　　　　　　　　　　TRANSLATE(SUBSTR(T_KOAZACD,4,1),'1234567890','一二三四五六七八九'),'十十','十')
　　　　　　　　　　　 END,-1);
　　　　　　IF i > 0 THEN
　　　　 -- 小字数字の最初から小字名
　　　　　　　 T_OAZAMEI　:= SUBSTR(V_AZACYOMEI,1,i -1); -- 大字名
　　　　　　　 T_KOAZAMEI := TRIM(SUBSTR(V_AZACYOMEI,i,LENGTH(V_AZACYOMEI)));
　　　　　　　 F_OAZAMEI　:= T_OAZAMEI;　　　　　　　　　-- 比較用大字名
　　　　　　ELSE
　　　　　　　 IF T_OAZACD　<> B_OAZACD THEN
　　　　　　-- 大字でブレークの最初か？
　　　　　　　　　T_OAZAMEI　:= V_AZACYOMEI;　-- 大字名
　　　　　　　　　T_KOAZAMEI := V_AZACYOMEI;　-- 小字名
　　　　　　　　　F_OAZAMEI　:= V_AZACYOMEI;　-- 比較用大字名
　　　　　　　　　B_KOAZACD　:= T_KOAZACD;　　-- ブレーク最初の小字コード
　　　　　　　 ELSE
　　　　　　　 -- 比較用と文字チェックし違う部分より小字名とする
　　　　　　　　　FOR i IN 1..LENGTH(V_AZACYOMEI)
　　　　　　　　　LOOP
　　　　　　　　　　 IF SUBSTR(F_OAZAMEI,i,1) = SUBSTR(V_AZACYOMEI,i,1) THEN
　　　　　　　　　　　　T_OAZAMEI　:= T_OAZAMEI || SUBSTR(V_AZACYOMEI,i,1);
　　　　　　　　　　 ELSE
　　　　　　　　　　　　T_KOAZAMEI := T_KOAZAMEI || SUBSTR(V_AZACYOMEI,i,LENGTH(V_AZACYOMEI) - i + 1);
　　　　　　　　　　　　EXIT;
　　　　　　　　　　 END IF;
　　　　　　　　　END LOOP;
　　　　　　　 -- 最初の小字名を戻って修正する
　　　　　　　　　IF F_OAZAMEI <> T_OAZAMEI THEN
　　　　　　　　　　 UPDATE MST_ADRS3
　　　　　　　　　　　　SET OAZAMEI　= T_OAZAMEI,
　　　　　　　　　　　　　　KOAZAMEI = SUBSTR(KOAZAMEI,　 LENGTH(T_OAZAMEI) + 1,
　　　　　　　　　　　　　　　　　　　 LENGTH(KOAZAMEI) - LENGTH(T_OAZAMEI))
　　　　　　　　　　　WHERE KEYKENCD　 = T_KENCD
　　　　　　　　　　　　AND KEYSIKUCD　= T_SIKUCD
　　　　　　　　　　　　AND KEYOAZACD　= T_OAZACD
　　　　　　　　　　　　AND KEYKOAZACD = B_KOAZACD;
　　　　　　　　　　 F_OAZAMEI := T_OAZAMEI;
　　　　　　　　　　 UPDCNT　　:= UPDCNT + 1;
　　　　　　　　　END IF;
　　　　　　　 END IF;
　　　　　　END IF;
　　　　 END IF;
　　　　 IF V_AZACYOCD > B_AZACYOCD THEN
　　　　　　INSERT INTO MST_ADRS3(AZAKNO,　　　 -- 大字小字管理番号
　　　　　　　　　　　　　　　　　 SIKUKNO,　　　-- 市区町村管理番号
　　　　　　　　　　　　　　　　　 OAZACD,　　　 -- 大字・通称名コード
　　　　　　　　　　　　　　　　　 KOAZACD,　　　-- 字・丁目コード
　　　　　　　　　　　　　　　　　 OAZAMEI,　　　-- 大字・通称名
　　　　　　　　　　　　　　　　　 KOAZAMEI,　　 -- 字・丁目名
　　　　　　　　　　　　　　　　　 YUBIN,　　　　-- 郵便番号
　　　　　　　　　　　　　　　　　 KEYKENCD,　　 -- 新都道府県コード
　　　　　　　　　　　　　　　　　 KEYSIKUCD,　　-- 新市区町村コード
　　　　　　　　　　　　　　　　　 KEYOAZACD,　　-- 新大字・通称名コード
　　　　　　　　　　　　　　　　　 KEYKOAZACD,　 -- 新字・丁目コード
　　　　　　　　　　　　　　　　　 HAISIFLG)　　 -- 廃止フラグ
　　　　　　　　　　　　　　VALUES(V_AZACYOCD,
　　　　　　　　　　　　　　　　　 TO_NUMBER(SUBSTR(V_AZACYOCD,1,5)),
　　　　　　　　　　　　　　　　　 T_OAZACD,
　　　　　　　　　　　　　　　　　 T_KOAZACD,
　　　　　　　　　　　　　　　　　 T_OAZAMEI,
　　　　　　　　　　　　　　　　　 T_KOAZAMEI,
　　　　　　　　　　　　　　　　　 NULL,
　　　　　　　　　　　　　　　　　 T_KENCD,
　　　　　　　　　　　　　　　　　 T_SIKUCD,
　　　　　　　　　　　　　　　　　 T_OAZACD,
　　　　　　　　　　　　　　　　　 T_KOAZACD,
　　　　　　　　　　　　　　　　　 0);
　　　　　　INSCNT　　 := INSCNT + 1;
　　　　　　B_AZACYOCD := V_AZACYOCD;
　　　　　　B_OAZACD　 := T_OAZACD;
　　　　 END IF;
　　　END LOOP;
　 EXCEPTION
　　　WHEN NO_DATA_FOUND THEN NULL;
　　　WHEN OTHERS THEN
　　　　　 DBMS_OUTPUT.PUT_LINE( 'エラーが発生しました。：' || SQLERRM);
　　　　　 DBMS_OUTPUT.PUT_LINE( 'AZACYOCD = ' || V_AZACYOCD);
　 END;
　 UTL_FILE.FCLOSE(FH);
-- 大字に単一の小字しかない場合の救済(大字名と小字名の分割)
　 UPDATE MST_ADRS3 SET
　　OAZAMEI = SUBSTR(OAZAMEI,1,
　　　　　　　 INSTR(KOAZAMEI,
　　　　　　　　 CASE SUBSTR(KOAZACD,1,3)
　　　　　　　　 WHEN '000' THEN
　　　　　　　　　 TRANSLATE(TO_NUMBER(KOAZACD),'123456789','一二三四五六七八九')
　　　　　　　　 ELSE
　　　　　　　　　 REPLACE(
　　　　　　　　　 TRANSLATE(SUBSTR(KOAZACD,3,1),'123456789','十二三四五六七八九') || '十' ||
　　　　　　　　　 TRANSLATE(SUBSTR(KOAZACD,4,1),'1234567890','一二三四五六七八九'),'十十','十')
　　　　　　　　 END,-1) -1),
　　KOAZAMEI = TRIM(SUBSTR(KOAZAMEI,
　　　　　　　 INSTR(KOAZAMEI,
　　　　　　　　 CASE SUBSTR(KOAZACD,1,3)
　　　　　　　　 WHEN '000' THEN
　　　　　　　　　 TRANSLATE(TO_NUMBER(KOAZACD),'123456789','一二三四五六七八九')
　　　　　　　　 ELSE
　　　　　　　　　 REPLACE(
　　　　　　　　　 TRANSLATE(SUBSTR(KOAZACD,3,1),'123456789','十二三四五六七八九') || '十' ||
　　　　　　　　　 TRANSLATE(SUBSTR(KOAZACD,4,1),'1234567890','一二三四五六七八九'),'十十','十')
　　　　　　　　 END,-1),LENGTH(KOAZAMEI)))
　　WHERE KEYKENCD = TO_NUMBER(KENCD) AND KOAZAMEI = OAZAMEI;
　 IF SQL%ROWCOUNT > 0 THEN
　　　UPDCNT := UPDCNT + SQL%ROWCOUNT;
　　　DBMS_OUTPUT.PUT_LINE(T_OAZACD || ':' || T_OAZAMEI || ' ' || T_KOAZACD || ':' || T_KOAZAMEI);
　 END IF;
　 DBMS_OUTPUT.PUT_LINE( '読込件数：' || LPAD(READCNT,6,' '));
　 DBMS_OUTPUT.PUT_LINE( '登録件数：' || LPAD(INSCNT ,6,' '));
　 DBMS_OUTPUT.PUT_LINE( '更新件数：' || LPAD(UPDCNT ,6,' '));
　 COMMIT;
EXCEPTION
　 WHEN OTHERS THEN
　　　DBMS_OUTPUT.PUT_LINE( FILE_NAME || ' NOT EXIST' );
END;
/

-- http://blog.goo.ne.jp/frontflug/e/acadd94ea8c37735db7195e2dcb5ba4a に続く