Re:SALOON & VBA

ORACLE 住所分解コード化ツール(PL/SQL)のパッケージ化

次は、ストアドプロシージャのパッケージ化ということで、
物は試しとやってみました。
速度は、変わりません(良くもなく悪くもなく)
勉強です、勉強。慣れとかないと・・・

create or replace PACKAGE ADRS_EDIT
IS

/** 住所分析表分解 **/
PROCEDURE PRC_MAIN;

/** 住所分解コード化処理 **/
PROCEDURE PRC_BUNKAI ( P_ADRS   IN   VARCHAR2,
             P_REIGAI  IN   NUMBER,
             P_SIKUMEI   OUT VARCHAR2,
             P_AZAMEI   OUT VARCHAR2,
             P_BANTI    OUT VARCHAR2,
             P_KATAGAKI  OUT VARCHAR2);

/** 市区町村コード設定 **/
PROCEDURE PRC_SIKUCD ( P_SIKUMEI IN OUT VARCHAR2,
             P_KENCD    OUT NUMBER,
             P_SIKUCD   OUT NUMBER );

/** 大字・小字コード設定 **/
PROCEDURE PRC_AZACD  ( P_KENCD  IN   NUMBER,
             P_SIKUCD  IN   NUMBER,
             P_AZAMEI  IN OUT VARCHAR2,
             P_YUBIN  IN OUT VARCHAR2,
             P_BANTI  IN OUT VARCHAR2,
             P_OAZACD   OUT VARCHAR2,
             P_KOAZACD   OUT VARCHAR2 );

/** 番地コード設定 **/
PROCEDURE PRC_BANTICD ( P_BANTI  IN   VARCHAR2,
             P_BANTICD   OUT VARCHAR2 );
            
FUNCTION FNC_ASU2KSU  (ASU_STR IN VARCHAR2) RETURN VARCHAR2;

FUNCTION FNC_ISZNUMBER (NUM_STR IN VARCHAR2) RETURN BOOLEAN;

FUNCTION FNC_ISANUMBER (NUM_STR IN VARCHAR2) RETURN BOOLEAN;

FUNCTION FNC_ISKNUMBER (NUM_STR IN VARCHAR2) RETURN BOOLEAN;

FUNCTION FNC_ISBANTI  (BAN_STR IN VARCHAR2) RETURN BOOLEAN;

FUNCTION FNC_ITAIJI  (ITAI_STR IN VARCHAR2) RETURN VARCHAR2;

END ADRS_EDIT;
/

create or replace PACKAGE BODY ADRS_EDIT
IS

/**********************************************************************************/
/* 処理名:住所分析表分解                        PRC_MAIN */
/*  ①住所を、市区名(県名+市区町村名)、字名(大字名+小字名)、番地、方書に分類する */
/*  ②各々の構成要素ごとにコード化する(住所マスタ使用)              */
/**********************************************************************************/
PROCEDURE PRC_MAIN
IS
  CURSOR csr IS
    SELECT ADRS,YUBIN,ROWID
     FROM ADRS_ANALYZE
    ORDER BY ADRS;

-- 制御ワーク
  L     PLS_INTEGER;     -- 文字長
  UPDCNT  NUMBER;        -- 更新件数
  RECCNT  NUMBER;        -- 件数ワーク
  WERRMSG  VARCHAR2(255);    -- エラーメッセージ
  WERRNO  NUMBER;        -- エラー№

-- 編集ワーク
  WADRS   VARCHAR2(200);    -- 住所中間編集ワーク
  WYUBIN  VARCHAR2(8);     -- 郵便番号編集ワーク
  WKENCD  NUMBER;        -- 都道府県コード
  WSIKUCD  NUMBER;        -- 市区町村コード
  WOAZACD  VARCHAR2(4);     -- 大字コード
  WKOAZACD VARCHAR2(4);     -- 小字コード
  WSIKUMEI VARCHAR2(100);    -- 市区町村名編集ワーク
  WAZAMEI  VARCHAR2(160);    -- 字名編集ワーク
  WKATAGAKI VARCHAR2(100);    -- 方書編集ワーク
  WBANTI  VARCHAR2(60);     -- 番地コード編集ワーク
  DISPTIME VARCHAR2(20);     -- 時刻編集ワーク
  WBANTICD VARCHAR2(20);     -- 番地コード
  WREIGAI  NUMBER;

-- 市区町村省略住所検索用
  MYKENCD  NUMBER    := 25; -- 自自治体県コード(滋賀県)
  MYSIKUCD NUMBER    := 201; -- 自自治体市区町村コード(大津市)

-- 前データ保持
  BKENCD  NUMBER    := 23;
  BKENMEI  VARCHAR2(12) := '愛知県';
  BSIKUCD  NUMBER    := 0;
  BSIKUMEI VARCHAR2(60) := NULL;
  BOAZACD  VARCHAR2(4)  := NULL;
  BKOAZACD VARCHAR2(4)  := NULL;
  BYUBIN  VARCHAR2(8)  := NULL;
  BAZAMEI  VARCHAR2(144) := NULL;

-- 市区名分割例外表…市・区・町・村の文字で単純に分割できない地名
  CURSOR csr_reigai IS
   SELECT A2.KEYKENCD,  -- 都道府県
      A2.KEYSIKUCD, -- 市区コード
      A2.SIKUMEI,  -- 例外市区名
      A1.KENMEI,   -- 都道府県名
      A2.KENMEIKBN  -- 県名有無区分
    FROM MST_ADRS1 A1,MST_ADRS2 A2
   WHERE A2.KEYKENCD = A1.KEYKENCD
    AND A2.KEYKENCD || A2.KEYSIKUCD IN (1409,3215,4322,7212,10464,
      12203,12219,13361,13362,13363,13364,13381,13382,13401,13402,
      13421,17212,21219,23214,24202,29203,29212,29402,34213,41423);
  REIGAI_REC csr_reigai%ROWTYPE;
  TYPE REIGAI_TYPE IS TABLE OF csr_reigai%ROWTYPE INDEX BY BINARY_INTEGER;
  REIGAI REIGAI_TYPE;
  IX   PLS_INTEGER := 0; -- 配列カウンタ

BEGIN
  SELECT TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS') INTO DISPTIME FROM DUAL;
  DBMS_OUTPUT.PUT_LINE('開始時刻 ' || DISPTIME);
  UPDCNT  := 0;

-- 例外分割市区名配列設定
  FOR REIGAI_REC IN csr_reigai
  LOOP
   IX := IX + 1;
   REIGAI(IX) := REIGAI_REC;
  END LOOP;

  FOR rec IN csr
  LOOP
 --【ワーク項目の初期化】
   WKENCD  := -1;   -- 都道府県コード
   WSIKUCD  := -1;   -- 市区町村コード
   WOAZACD  := '0000'; -- 大字コード
   WKOAZACD := '0000'; -- 小字コード
   WBANTICD := NULL;  -- 番地コード
   WSIKUMEI := NULL;  -- 市区町村名
   WAZAMEI  := NULL;  -- 字名
   WKATAGAKI := NULL;  -- 方書
   WBANTI  := NULL;  -- 番地

  -- ①例外市区名
   WADRS  := rec.ADRS;
   WREIGAI := -1;
   FOR I IN 1..IX LOOP
     IF INSTR(WADRS,REIGAI(I).SIKUMEI) > 0 THEN
      WREIGAI := REIGAI(I).KEYKENCD || REIGAI(I).KEYSIKUCD;
      EXIT;
     END IF;
   END LOOP;

 --【市区町村字名と字名、番地を分離】
   L := LENGTH(BSIKUMEI || BAZAMEI);
   IF BSIKUMEI || BAZAMEI = SUBSTR(WADRS,1,L) THEN
     WSIKUMEI := BSIKUMEI;
     WAZAMEI := BAZAMEI;
     WBANTI  := SUBSTR(WADRS,L + 1);
   ELSE
     PRC_BUNKAI(WADRS,WREIGAI,WSIKUMEI,WAZAMEI,WBANTI,WKATAGAKI);
   END IF;

 --【コード化】
   IF WSIKUMEI = BSIKUMEI THEN
     WKENCD  := BKENCD;
     WSIKUCD := BSIKUCD;
   ELSE
     PRC_SIKUCD(WSIKUMEI,WKENCD,WSIKUCD);
   END IF; 
   IF WAZAMEI  = BAZAMEI THEN
     WOAZACD := BOAZACD;
     WKOAZACD := BKOAZACD;
     WYUBIN  := BYUBIN;
   ELSE
     PRC_AZACD(WKENCD,WSIKUCD,WAZAMEI,WYUBIN,WBANTI,WOAZACD,WKOAZACD);
   END IF;
   IF WBANTI IS NOT NULL THEN
     PRC_BANTICD(WBANTI,WBANTICD);
   ELSE
     WBANTICD := '00000000000000000000';
   END IF; 

 -- 次件比較用に退避
   BKENCD  := WKENCD;
   BKENMEI := SUBSTR(WSIKUMEI,1,3);
   BSIKUCD := WSIKUCD;
   BSIKUMEI := WSIKUMEI;
   BOAZACD := WOAZACD;
   BKOAZACD := WKOAZACD;
   BYUBIN  := WYUBIN;
   BAZAMEI := WAZAMEI;

 --【住所分解テーブルの更新】
   UPDATE ADRS_ANALYZE
     SET YUBIN    = WYUBIN,
       TODOUFUKENCD = WKENCD,
       SICHOSONCD  = WSIKUCD,
       OAZACD    = WOAZACD,
       KOAZACD   = WKOAZACD,
       BANTICD   = WBANTICD,
       SICHOSONMEI = WSIKUMEI,
       AZAMEI    = WAZAMEI,
       BANTI    = WBANTI,
       KATAGAKI   = WKATAGAKI
    WHERE ROWID    = rec.ROWID;
   UPDCNT := UPDCNT + 1;
   IF UPDCNT = TRUNC(UPDCNT / 100) * 100 THEN
     COMMIT;
   END IF;

  END LOOP;
  COMMIT;
 
  SELECT TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS') INTO DISPTIME FROM DUAL;
  DBMS_OUTPUT.PUT_LINE('終了時刻 ' || DISPTIME);
  DBMS_OUTPUT.PUT_LINE('更新件数 ' || UPDCNT);

END PRC_MAIN;

/**********************************************************************************/
/* 処理名:住所分解コード化処理                    PRC_BUNKAI */
/*   住所を、市区名(県名+市区町村名)、字名(大字名+小字名)、番地、方書に分類する */
/**********************************************************************************/
PROCEDURE PRC_BUNKAI ( P_ADRS   IN   VARCHAR2,
            P_REIGAI  IN   NUMBER,
            P_SIKUMEI   OUT VARCHAR2,
            P_AZAMEI   OUT VARCHAR2,
            P_BANTI    OUT VARCHAR2,
            P_KATAGAKI  OUT VARCHAR2)
IS
-- 制御ワーク
  J     PLS_INTEGER;      -- 文字位置
  L     PLS_INTEGER;      -- 文字長
  WERRMSG  VARCHAR2(255);     -- エラーメッセージ
  WERRNO   NUMBER;         -- エラー№

-- 市区町村省略住所検索用
  MYKENCD  NUMBER    := 25;  -- 自自治体県コード(滋賀県)
  MYSIKUCD  NUMBER    := 201;  -- 自自治体市区町村コード(大津市)

-- 編集ワーク
  WADRS   VARCHAR2(200);     -- 住所中間編集ワーク
  WKENCD   NUMBER;         -- 都道府県コード
  WSIKUCD  NUMBER;         -- 市区町村コード
  WKENMEI  VARCHAR2(16) := NULL; -- 都道府県名編集ワーク
  WSIKUMEI  VARCHAR2(100) := NULL; -- 市区町村名編集ワーク
  WKENMEIKBN NUMBER := 0;      -- 都道府県名有無区分ワーク 

BEGIN

--【出力項目の初期化】
  P_SIKUMEI := NULL;  -- 市区町村名
  P_AZAMEI  := NULL;  -- 字名
  P_KATAGAKI := NULL;  -- 方書
  P_BANTI  := NULL;  -- 番地

--【整形処理】
-- ①全角化
  WADRS := TO_MULTI_BYTE(P_ADRS);

-- ②カンマ除去
  WADRS := REPLACE(WADRS,'.','');

-- ③空白除去
  WADRS := REPLACE(REPLACE(WADRS,' ',''),' ','');

  L := LENGTH(WADRS);

--【市区町村字名と番地を分離】
-- ①丁目で分割
  J := INSTR(WADRS,'丁目');
  CASE
  WHEN J > 0 THEN
   CASE
   WHEN J = L - 1 THEN
     NULL;
   WHEN SUBSTR(WADRS,J + 2,1) IN ('東','西','南','北') THEN
     P_BANTI := SUBSTR(WADRS,J + 3);
     WADRS  := FNC_ASU2KSU(SUBSTR(WADRS,1,J + 2));
   ELSE
     P_BANTI := SUBSTR(WADRS,J + 2);
     WADRS  := FNC_ASU2KSU(SUBSTR(WADRS,1,J + 1));
   END CASE;

-- ②北海道は、条も字名
  WHEN SUBSTR(WADRS,1,3) = '北海道'
  AND (INSTR(WADRS,'条') > 1 OR
     INSTR(WADRS,'線') > 1) THEN
   J := INSTR(WADRS,'条');
   IF J < 1 THEN
     J := INSTR(WADRS,'線');
   END IF; 
   IF FNC_ISZNUMBER(SUBSTR(WADRS,J - 1,1)) THEN
   -- 条・線の前後が数字なら条・線で分割
     CASE
     WHEN FNC_ISANUMBER(SUBSTR(WADRS,J + 1,1)) THEN
      P_BANTI := SUBSTR(WADRS,J + 1);
      WADRS  := FNC_ASU2KSU(SUBSTR(WADRS,1,J));
   -- 条・線の前と1字後が数字なら1字後で分割
     WHEN FNC_ISANUMBER(SUBSTR(WADRS,J + 2)) THEN
      P_BANTI := SUBSTR(WADRS,J + 2);
      WADRS  := FNC_ASU2KSU(SUBSTR(WADRS,1,J + 1));
   -- 条・線の前と2字後が数字なら2字後で分割
     WHEN FNC_ISANUMBER(SUBSTR(WADRS,J + 3)) THEN
      P_BANTI := SUBSTR(WADRS,J + 3);
      WADRS  := FNC_ASU2KSU(SUBSTR(WADRS,1,J + 2));
     ELSE
      NULL;
     END CASE;
   END IF;
   WKENCD := 1;             -- 北海道

-- ③大阪府堺市は、丁で分割 (丁ありは丁目なし)
  WHEN SUBSTR(WADRS,1,5) = '大阪府堺市'
  AND INSTR(WADRS,'丁') > 1 THEN
   J := INSTR(WADRS,'丁');
  -- 丁の前後が数字なら丁で分割
   IF FNC_ISZNUMBER(SUBSTR(WADRS,J - 1,1)) AND
     FNC_ISZNUMBER(SUBSTR(WADRS,J + 1,1)) THEN
     P_BANTI := SUBSTR(WADRS,J + 1);
     WADRS  := FNC_ASU2KSU(SUBSTR(WADRS,1,J));
   END IF;
   WKENCD := 27;            -- 大阪府
    
-- ④岩手県の「地割」は字名
  WHEN SUBSTR(WADRS,1,3) = '岩手県'
  AND INSTR(WADRS,'地割') > 1 THEN
   J := INSTR(WADRS,'地割');
   P_BANTI := SUBSTR(WADRS,J + 2);
   WADRS  := FNC_ASU2KSU(SUBSTR(WADRS,1,J + 1));
   WKENCD := 3;             -- 岩手県
-- ⑤○番町という字名
  WHEN INSTR(WADRS,'番町') > 0 THEN
   J := INSTR(WADRS,'番町');
   IF FNC_ISZNUMBER(SUBSTR(WADRS,J + 2,1)) THEN
     P_BANTI := SUBSTR(WADRS,J + 2);
     WADRS  := FNC_ASU2KSU(SUBSTR(WADRS,1,J + 1));
   ELSE
     WADRS := FNC_ASU2KSU(SUBSTR(WADRS,1,J)) || SUBSTR(WADRS,J + 1);
   END IF;
-- ⑥アラビア数字で分割
  WHEN P_BANTI IS NULL THEN      -- 未分割
   FOR I IN 1..L LOOP
     IF FNC_ISANUMBER(SUBSTR(WADRS,I,1)) THEN
      P_BANTI := SUBSTR(WADRS,I);
      WADRS  := SUBSTR(WADRS,1,I - 1);
      EXIT;
     END IF;
   END LOOP;
  ELSE
   NULL;
  END CASE;

-- ⑦丁目なしの'-'が二つ以上の場合、一つ目の'-'の以前は丁目とみなす
  IF INSTR(WADRS,'丁') = 0 AND
   INSTR(P_BANTI,'-') > 0 AND
   INSTR(P_BANTI,'-') < INSTR(P_BANTI,'-',-1) THEN
   CASE
   WHEN SUBSTR(P_BANTI,2,1) = '-' THEN
     WADRS  := WADRS || FNC_ASU2KSU(SUBSTR(P_BANTI,1,1)) || '丁目';
     P_BANTI := SUBSTR(P_BANTI,3);
   WHEN SUBSTR(P_BANTI, 3, 1) = '-' THEN
     WADRS  := WADRS || FNC_ASU2KSU(SUBSTR(P_BANTI,1,2)) || '丁目';
     P_BANTI := SUBSTR(P_BANTI,4);
   ELSE
     NULL;
   END CASE;
  END IF;

--【都道府県コードの設定】
  IF WKENCD = -1 THEN
   CASE
   WHEN SUBSTR(WADRS,1,3) = '北海道'  THEN
     WKENCD := 1;
   WHEN SUBSTR(WADRS,1,3) = '東京都'  THEN
     WKENCD := 13;
   WHEN SUBSTR(WADRS,1,4) = '神奈川県' THEN
     WKENCD := 14;
   WHEN SUBSTR(WADRS,1,3) = '京都府'  THEN
     WKENCD := 26;
   WHEN SUBSTR(WADRS,1,3) = '大阪府'  THEN
     WKENCD := 27;
   WHEN SUBSTR(WADRS,1,4) = '和歌山県' THEN
     WKENCD := 30;
   WHEN SUBSTR(WADRS,1,4) = '鹿児島県' THEN
     WKENCD := 46;
   WHEN SUBSTR(WADRS,3,1) = '県'    THEN
     BEGIN
      SELECT A1.KEYKENCD
       INTO WKENCD
       FROM MST_ADRS1 A1
       WHERE A1.KENMEI = SUBSTR(WADRS,1,3);
     EXCEPTION
      WHEN NO_DATA_FOUND THEN NULL;
     END;
   ELSE
     NULL;
   END CASE;
  END IF;

--【市区名と字名を分離】
-- ①例外市区名
  IF P_REIGAI <> -1 THEN
   BEGIN
     SELECT A2.SIKUMEI,A1.KENMEI,A2.KENMEIKBN
      INTO WSIKUMEI,WKENMEI,WKENMEIKBN
      FROM MST_ADRS1 A1,MST_ADRS2 A2
     WHERE A2.KEYKENCD = A1.KEYKENCD
      AND A2.KEYKENCD = DECODE(WKENCD,-1,WKENCD,A2.KEYKENCD)
      AND A2.KEYKENCD || A2.KEYSIKUCD = P_REIGAI;
     J     := INSTR(WADRS,WSIKUMEI);
     L     := LENGTH(WSIKUMEI);
     P_SIKUMEI := SUBSTR(WADRS,1,J + L -1);
     IF WKENMEIKBN = 1 THEN
      P_SIKUMEI := REPLACE(P_SIKUMEI,WKENMEI,'');
     ELSE
      P_SIKUMEI := WKENMEI || REPLACE(P_SIKUMEI,WKENMEI,'');
     END IF;
     P_AZAMEI := SUBSTR(WADRS,J + L);
   EXCEPTION
     WHEN NO_DATA_FOUND THEN NULL;
     WHEN OTHERS THEN
      WERRMSG := SQLERRM;
      WERRNO := SQLCODE;
      DBMS_OUTPUT.PUT_LINE('ERR:' || WERRNO || ' ' || WERRMSG || ' ' || WADRS);    
   END;
  END IF;

  CASE
  WHEN P_AZAMEI IS NOT NULL THEN      -- 例外市区名
   NULL;
-- ②町で分割
  WHEN INSTR(WADRS,'郡') > 0 AND INSTR(WADRS,'町') > 0 AND
    INSTR(WADRS,'郡') < INSTR(WADRS,'町') AND
    (INSTR(WADRS,'村') < 1 OR
    INSTR(WADRS,'村') > INSTR(WADRS,'町') OR
    INSTR(WADRS,'村') < INSTR(WADRS,'郡')) THEN
   P_SIKUMEI := SUBSTR(WADRS,1,INSTR(WADRS,'町'));
   P_AZAMEI := SUBSTR(WADRS,INSTR(WADRS,'町') + 1);

-- ③区で分割 (郡を含む)
  WHEN INSTR(WADRS,'区') > 0 THEN
   P_SIKUMEI := SUBSTR(WADRS,1,INSTR(WADRS,'区'));
   P_AZAMEI := SUBSTR(WADRS,INSTR(WADRS,'区') + 1);

-- ④市で分割
  WHEN INSTR(WADRS,'市') > 0 THEN
   P_SIKUMEI := SUBSTR(WADRS,1,INSTR(WADRS,'市'));
   P_AZAMEI := SUBSTR(WADRS,INSTR(WADRS,'市') + 1);

-- ⑤村で分割 (郡を含む)
  WHEN INSTR(WADRS,'郡') > 0 AND INSTR(WADRS,'村') > 0 AND
    INSTR(WADRS,'郡') < INSTR(WADRS,'村') THEN
   P_SIKUMEI := SUBSTR(WADRS,1,INSTR(WADRS,'村'));
   P_AZAMEI := SUBSTR(WADRS,INSTR(WADRS,'村') + 1);

-- ⑥上記なし…自市区町村の字名で検索し、自市区名を補完
  ELSE
   BEGIN
     SELECT DECODE(A2.KENMEIKBN,1,A2.SIKUMEI,A1.KENMEI || A2.SIKUMEI)
      INTO P_SIKUMEI
      FROM MST_ADRS1 A1,MST_ADRS2 A2,MST_ADRS3 A3
     WHERE A3.KEYKENCD = A1.KEYKENCD
      AND A3.KEYKENCD = A2.KEYKENCD
      AND A3.KEYKENCD = MYKENCD
      AND A3.KEYSIKUCD = A2.KEYSIKUCD
      AND A3.KEYSIKUCD = MYSIKUCD
      AND A3.OAZAMEI || A3.KOAZAMEI
       = SUBSTR(WADRS,1,LENGTH(A3.OAZAMEI || A3.KOAZAMEI));
     P_AZAMEI := WADRS;  
   EXCEPTION
     WHEN NO_DATA_FOUND THEN NULL;
     WHEN OTHERS THEN
      WERRMSG := SQLERRM;
      WERRNO := SQLCODE;
      DBMS_OUTPUT.PUT_LINE('ERR:' || WERRNO || ' ' || WERRMSG || ' ' || WADRS);
   END;
  END CASE;

--【字名と番地を分割】
  IF P_BANTI IS NULL THEN
   WADRS   := P_AZAMEI;
   L     := LENGTH(WADRS);
   IF L > 0 THEN
     P_BANTI := WADRS;
     FOR I IN REVERSE 1..L LOOP
      IF NOT FNC_ISANUMBER(SUBSTR(WADRS,I,1)) AND
        NOT FNC_ISBANTI(SUBSTR(WADRS,I,1)) THEN
        P_AZAMEI := SUBSTR(WADRS,1,I);
        P_BANTI := SUBSTR(WADRS,I + 1);
        EXIT;
      END IF;
     END LOOP;
   END IF;
  END IF; 
  P_AZAMEI := FNC_ASU2KSU(P_AZAMEI);

--【番地と方書の分割】
  WADRS := P_BANTI;
  L   := LENGTH(WADRS);
-- ①数字と番地用漢字以外でないのは方書とする
  IF L > 0 THEN
   FOR I IN 1..L LOOP
     IF NOT FNC_ISANUMBER(SUBSTR(WADRS,I,1)) AND
      NOT FNC_ISBANTI(SUBSTR(WADRS,I,1)) THEN
      P_BANTI  := SUBSTR(WADRS,1,I - 1);
      P_KATAGAKI := SUBSTR(WADRS,I);
      EXIT;
     END IF;
   END LOOP;
  END IF;
END PRC_BUNKAI;

/****************************************************************/
/* 処理名:市区町村コード設定            PRC_SIKUCD */
/*   市区町村名より、都道府県コード、市区町村コードを設定する */
/****************************************************************/
PROCEDURE PRC_SIKUCD ( P_SIKUMEI IN OUT VARCHAR2,
            P_KENCD   OUT NUMBER,
            P_SIKUCD   OUT NUMBER )
IS
  WERRMSG VARCHAR2(255);  -- エラーメッセージ
  WERRNO NUMBER;      -- エラー№
  L    NUMBER;
BEGIN
  P_KENCD  := -1;
  P_SIKUCD  := -1;
  P_SIKUMEI := P_SIKUMEI;

--【都道府県コードの設定】
  CASE
  WHEN SUBSTR(P_SIKUMEI,1,3) = '北海道'  THEN
   P_KENCD := 1;
  WHEN SUBSTR(P_SIKUMEI,1,3) = '東京都'  THEN
   P_KENCD := 13;
  WHEN SUBSTR(P_SIKUMEI,1,4) = '神奈川県' THEN
   P_KENCD := 14;
  WHEN SUBSTR(P_SIKUMEI,1,3) = '京都府'  THEN
   P_KENCD := 26;
  WHEN SUBSTR(P_SIKUMEI,1,3) = '大阪府'  THEN
   P_KENCD := 27;
  WHEN SUBSTR(P_SIKUMEI,1,4) = '和歌山県' THEN
   P_KENCD := 30;
  WHEN SUBSTR(P_SIKUMEI,1,4) = '鹿児島県' THEN
   P_KENCD := 46;
  WHEN SUBSTR(P_SIKUMEI,3,1) = '県'    THEN
   BEGIN
     SELECT A1.KEYKENCD
      INTO P_KENCD
      FROM MST_ADRS1 A1
     WHERE A1.KENMEI = SUBSTR(P_SIKUMEI,1,3);
   EXCEPTION
     WHEN NO_DATA_FOUND THEN NULL;
   END;
  ELSE
   NULL;
  END CASE;

--【市区町村コードの設定】
-- ①市区名で住所テーブルを検索 (県コードありなしの2パターン)
  -- 市区名も県名ありなしの2バターン
  BEGIN
   IF P_KENCD = -1 THEN
     SELECT A2.KEYKENCD,A2.KEYSIKUCD,
        CASE A2.KENMEIKBN
        WHEN 1 THEN
           A2.SIKUMEI
        ELSE
           A1.KENMEI || A2.SIKUMEI
        END
      INTO P_KENCD,P_SIKUCD,P_SIKUMEI
        FROM MST_ADRS1 A1,MST_ADRS2 A2
        WHERE A2.KEYKENCD = A1.KEYKENCD
         AND A2.SIKUMEI = P_SIKUMEI;
   ELSE
     SELECT A2.KEYSIKUCD,
        CASE A2.KENMEIKBN
        WHEN 1 THEN
          REPLACE(P_SIKUMEI,A1.KENMEI,'')
        ELSE
          A1.KENMEI || REPLACE(P_SIKUMEI,A1.KENMEI,'')
        END
      INTO P_SIKUCD,P_SIKUMEI
      FROM MST_ADRS1 A1,MST_ADRS2 A2
     WHERE A2.KEYKENCD = A1.KEYKENCD
      AND A2.KEYKENCD = P_KENCD
      AND(A2.SIKUMEI = P_SIKUMEI OR
        A1.KENMEI || A2.SIKUMEI = P_SIKUMEI);
   END IF;
  EXCEPTION
   WHEN NO_DATA_FOUND THEN NULL;
  END;  

-- ②郡名を除いた市区名で再検索
  IF P_SIKUCD = -1 AND INSTR(P_SIKUMEI,'郡') > 0 THEN
   BEGIN
     IF P_KENCD = -1 THEN
      SELECT A2.KEYKENCD,A2.KEYSIKUCD,
          CASE A2.KENMEIKBN
          WHEN 1 THEN
           REPLACE(P_SIKUMEI,A1.KENMEI,'')
          ELSE
           A1.KENMEI || REPLACE(P_SIKUMEI,A1.KENMEI,'')
          END
       INTO P_KENCD,P_SIKUCD,P_SIKUMEI
       FROM MST_ADRS1 A1,MST_ADRS2 A2
       WHERE A2.KEYKENCD = A1.KEYKENCD
        AND A2.SIKUMEI
         = SUBSTR(P_SIKUMEI,INSTR(P_SIKUMEI,'郡')+ 1)
        AND A2.HAISIFLG = 0;
     ELSE
      SELECT A2.KEYKENCD,A2.KEYSIKUCD,
          CASE A2.KENMEIKBN
          WHEN 1 THEN
           REPLACE(P_SIKUMEI,A1.KENMEI,'')
          ELSE
           A1.KENMEI || REPLACE(P_SIKUMEI,A1.KENMEI,'')
          END
       INTO P_KENCD,P_SIKUCD,P_SIKUMEI
       FROM MST_ADRS1 A1,MST_ADRS2 A2
       WHERE A2.KEYKENCD = A1.KEYKENCD
        AND A2.KEYKENCD = P_KENCD
        AND A2.SIKUMEI
         = SUBSTR(P_SIKUMEI,INSTR(P_SIKUMEI,'郡')+ 1)
        AND A2.HAISIFLG = 0;
     END IF;
   EXCEPTION
     WHEN NO_DATA_FOUND THEN NULL;
     WHEN OTHERS THEN
      WERRMSG := SQLERRM;
      WERRNO := SQLCODE;
      DBMS_OUTPUT.PUT_LINE('ERR:' || WERRNO || ' ' || WERRMSG || ' ' || P_SIKUMEI);
      DBMS_OUTPUT.PUT_LINE('市区名検索ERR:' || SUBSTR(P_SIKUMEI,INSTR(P_SIKUMEI,'郡')+ 1));
   END;  
  END IF;

-- ③異体字での再検索…異体字を見つけたら追加する
  IF P_SIKUCD = -1 THEN
   P_SIKUMEI := FNC_ITAIJI(P_SIKUMEI);
   IF P_SIKUMEI <> P_SIKUMEI THEN       -- 置換されているとき
     L := LENGTH(P_SIKUMEI);
     IF INSTR(P_SIKUMEI,'郡') > 0 THEN
      P_SIKUMEI := SUBSTR(P_SIKUMEI,INSTR(P_SIKUMEI,'郡') + 1);
     END IF;
     IF INSTR(P_SIKUMEI,'県') > 0 THEN
      P_SIKUMEI := SUBSTR(P_SIKUMEI,INSTR(P_SIKUMEI,'県') + 1);
     END IF;
     P_SIKUMEI := REPLACE(P_SIKUMEI,'大阪府','');
     P_SIKUMEI := REPLACE(P_SIKUMEI,'京都府','');
     P_SIKUMEI := REPLACE(P_SIKUMEI,'東京都','');
     P_SIKUMEI := REPLACE(P_SIKUMEI,'北海道','');
   -- DBMS_OUTPUT.PUT_LINE('異体字市区名:' || P_KENCD || ' ' || P_SIKUMEI);
     BEGIN
      SELECT A2.KEYKENCD,A2.KEYSIKUCD,
          CASE A2.KENMEIKBN
          WHEN 1 THEN
           REPLACE(P_SIKUMEI,A1.KENMEI,'')
          ELSE
           A1.KENMEI || REPLACE(P_SIKUMEI,A1.KENMEI,'')
          END
       INTO P_KENCD,P_SIKUCD,P_SIKUMEI
       FROM MST_ADRS2 A2,MST_ADRS1 A1
       WHERE A2.KEYKENCD = A2.KEYKENCD
        AND A2.KEYKENCD = P_KENCD
        AND A2.SIKUMEI = P_SIKUMEI
        AND ROWNUM < 2;
     -- DBMS_OUTPUT.PUT_LINE('異体字市区CD:' || P_SIKUCD || ' ' || P_SIKUMEI);
     EXCEPTION
      WHEN NO_DATA_FOUND THEN NULL;
     END;
   END IF;
  END IF;
END;

/**************************************************/
/* 処理名:大字・小字コード設定     PRC_AZACD */
/*   字名より、大字コード、小字コードを設定する */
/**************************************************/
PROCEDURE PRC_AZACD ( P_KENCD  IN   NUMBER,
            P_SIKUCD IN   NUMBER,
            P_AZAMEI IN OUT VARCHAR2,
            P_YUBIN  IN OUT VARCHAR2,
            P_BANTI  IN OUT VARCHAR2,
            P_OAZACD   OUT VARCHAR2,
            P_KOAZACD  OUT VARCHAR2)
IS
  J    NUMBER;
  RECCNT NUMBER;
  WAZAMEI VARCHAR2(160);
BEGIN
  P_OAZACD := '0000';
  P_KOAZACD := '0000';

-- 郵便番号の整形(XXX-XXXX)
  CASE
  WHEN LENGTH(P_YUBIN) = 8
  AND SUBSTR(P_YUBIN, 4, 1) = '-' THEN
   P_YUBIN := REPLACE(P_YUBIN,' ','0');
  WHEN LENGTH(P_YUBIN) = 7
  AND LENGTH(TRANSLATE(P_YUBIN,'0123456789','')) = 0 THEN
   P_YUBIN := SUBSTR(P_YUBIN,1,3) || '-' || NVL(SUBSTR(P_YUBIN,-4),P_YUBIN);
  ELSE
   NULL;
  END CASE;

--【字コードの設定】
-- ①県コード、市区町村コードと字名で住所テーブルを検索
  IF P_KENCD <> -1 AND P_SIKUCD <> -1 AND P_OAZACD = '0000' THEN
   BEGIN
     SELECT A3.YUBIN,A3.KEYOAZACD,A3.KEYKOAZACD
      INTO P_YUBIN,P_OAZACD,P_KOAZACD
      FROM MST_ADRS3 A3
     WHERE A3.KEYKENCD = P_KENCD
      AND A3.KEYSIKUCD = P_SIKUCD
      AND REPLACE(REPLACE(A3.OAZAMEI || A3.KOAZAMEI,'大字',''),'字','')
       = REPLACE(REPLACE(P_AZAMEI,'大字',''),'字','')
      AND A3.HAISIFLG = 0;
   EXCEPTION
     WHEN NO_DATA_FOUND THEN NULL;
   END;
  END IF;

-- ②郵便番号での検索 (但し、複数存在する時は採用しない)
  IF P_OAZACD = '0000' AND
   P_YUBIN IS NOT NULL AND
   P_YUBIN <> '000-0000' THEN
   SELECT COUNT(*) INTO RECCNT
    FROM MST_ADRS3 A3
    WHERE A3.YUBIN = P_YUBIN
     AND A3.HAISIFLG = 0;
   IF RECCNT = 1 THEN
     SELECT A3.KEYOAZACD,A3.KEYKOAZACD
      INTO P_OAZACD,P_KOAZACD
      FROM MST_ADRS3 A3
     WHERE A3.YUBIN  = P_YUBIN
      AND A3.HAISIFLG = 0;
   END IF;
  END IF;

-- ③大字名の異体字での検索
  IF P_OAZACD = '0000' AND
   P_KENCD <> -1 AND
   P_SIKUCD <> -1 THEN
   WAZAMEI := FNC_ITAIJI(P_AZAMEI);
   IF WAZAMEI <> P_AZAMEI THEN        -- 置換されているとき
     BEGIN
      SELECT A3.YUBIN,A3.KEYOAZACD,A3.KEYKOAZACD
       INTO P_YUBIN,P_OAZACD,P_KOAZACD
       FROM MST_ADRS3 A3
       WHERE A3.KEYKENCD = P_KENCD
        AND A3.KEYSIKUCD = P_SIKUCD
        AND REPLACE(REPLACE(A3.OAZAMEI || A3.KOAZAMEI,'大字',''),'字','')
         = REPLACE(REPLACE(WAZAMEI,'大字',''),'字','')
        AND A3.HAISIFLG = 0;
     EXCEPTION
      WHEN NO_DATA_FOUND THEN NULL;
     END;
   END IF;
  END IF;

-- ④丁目追加で再検索
  IF P_OAZACD = '0000' AND
   P_KENCD <> -1 AND
   P_SIKUCD <> -1 AND
   INSTR(P_AZAMEI,'丁目') < 1 AND
   INSTR(P_BANTI,'-') > 0 THEN
   J := INSTR(P_BANTI, '-');
   WAZAMEI := P_AZAMEI || FNC_ASU2KSU(SUBSTR(P_BANTI,1,J - 1)) || '丁目';
   BEGIN
     SELECT A3.YUBIN,A3.KEYOAZACD,A3.KEYKOAZACD
      INTO P_YUBIN,P_OAZACD,P_KOAZACD
      FROM MST_ADRS3 A3
     WHERE A3.KEYKENCD = P_KENCD
      AND A3.KEYSIKUCD = P_SIKUCD
      AND REPLACE(REPLACE(A3.OAZAMEI || A3.KOAZAMEI,'大字',''),'字','')
       = REPLACE(REPLACE(WAZAMEI,'大字',''),'字','')
      AND A3.HAISIFLG = 0;
     P_AZAMEI := WAZAMEI;
     P_BANTI := SUBSTR(P_BANTI,J + 1);
   EXCEPTION
     WHEN NO_DATA_FOUND THEN NULL;
   END;   
  END IF;

-- ⑤小字なしでの検索(小字不備の住所マスタのみ曖昧設定)
  IF P_OAZACD = '0000' AND
   P_KENCD <> -1 AND
   P_SIKUCD <> -1 THEN
   WAZAMEI := REPLACE(P_AZAMEI,'大字','');
   IF SUBSTR(WAZAMEI,1,1) = '字' THEN
     WAZAMEI := SUBSTR(WAZAMEI,2);
   END IF;
   CASE
   WHEN INSTR(WAZAMEI,'小字') > 0 THEN
     WAZAMEI := SUBSTR(WAZAMEI,1,INSTR(WAZAMEI,'小字')-1);
   WHEN INSTR(WAZAMEI,'字') > 0 THEN
     WAZAMEI := SUBSTR(WAZAMEI,1,INSTR(WAZAMEI,'字')-1);
   WHEN INSTR(WAZAMEI,'丁目') > 0 THEN
     WAZAMEI := SUBSTR(WAZAMEI,1,INSTR(WAZAMEI,'丁目')-2);
   WHEN INSTR(WAZAMEI,'町') > 0 THEN
     WAZAMEI := SUBSTR(WAZAMEI,1,INSTR(WAZAMEI,'町'));
   ELSE NULL;
   END CASE;
   IF WAZAMEI <> REPLACE(P_AZAMEI,'大字','') THEN
     BEGIN
      SELECT A3.YUBIN,A3.KEYOAZACD,A3.KEYKOAZACD
       INTO P_YUBIN,P_OAZACD,P_KOAZACD
       FROM MST_ADRS3 A3
       WHERE A3.KEYKENCD = P_KENCD
        AND A3.KEYSIKUCD = P_SIKUCD
        AND A3.KOAZAMEI IS NULL
        AND REPLACE(REPLACE(A3.OAZAMEI,'大字',''),'字','') = WAZAMEI
        AND A3.HAISIFLG = 0;
     EXCEPTION
      WHEN NO_DATA_FOUND THEN NULL;
     END;
   END IF; 
  END IF;
END PRC_AZACD;

/****************************************/
/* 処理名:番地コード設定  PRC_BANTICD */
/*   番地より、番地コードを設定する  */
/****************************************/
PROCEDURE PRC_BANTICD ( P_BANTI  IN   VARCHAR2,
             P_BANTICD  OUT VARCHAR2)
IS
  K    PLS_INTEGER;
  L    PLS_INTEGER;
  WBANTI VARCHAR2(60);
  TEMPBAN VARCHAR2(10) := NULL; -- 番地コード編集用ワーク
BEGIN
  IF P_BANTI IS NULL THEN
   P_BANTICD := '00000000000000000000';
  ELSE 
   L := LENGTH(P_BANTI);
   WBANTI := P_BANTI;
   K := 1;
   FOR I IN 1..L LOOP
     IF FNC_ISANUMBER(SUBSTR(WBANTI,I,1)) THEN      -- 数字ならば
      TEMPBAN := TEMPBAN || TO_SINGLE_BYTE(SUBSTR(WBANTI,I,1));  -- 末尾に追加する
     END IF;
     IF I = L OR NOT FNC_ISANUMBER(SUBSTR(WBANTI,I,1)) THEN
      IF TEMPBAN IS NOT NULL THEN
        IF K < 3 THEN
         TEMPBAN := LPAD(TEMPBAN,5,'0');
         TEMPBAN := SUBSTR(TEMPBAN,LENGTH(TEMPBAN) - 4,5);
        ELSE
         TEMPBAN := LPAD(TEMPBAN,10,'0');
         TEMPBAN := SUBSTR(TEMPBAN,LENGTH(TEMPBAN) - 9,10);
        END IF;
        P_BANTICD := P_BANTICD || TEMPBAN;
        TEMPBAN := NULL;
        K := K + 1;
        IF K > 3 THEN       -- フルセットなら終了
         EXIT;
        END IF;
      END IF;
     END IF;
   END LOOP;
   P_BANTICD := RPAD(P_BANTICD,20,'0');
  END IF;
END PRC_BANTICD;

FUNCTION FNC_ASU2KSU (ASU_STR IN VARCHAR2) RETURN VARCHAR2
IS
  WK_STR VARCHAR2(60);
  I   PLS_INTEGER;
  L   PLS_INTEGER;
BEGIN
  IF ASU_STR IS NULL THEN
   RETURN NULL;
  END IF; 
  WK_STR := TRIM(TRANSLATE(ASU_STR,'1234567890','一二三四五六七八九十'));
-- 丁目漢数字の10以上編集(十を挿入)
  L   := LENGTH(WK_STR) - 1;
  IF L < 3 THEN
   RETURN WK_STR;
  END IF;
  I := 1;
  WHILE I < L LOOP
   IF FNC_ISKNUMBER(SUBSTR(WK_STR,I,1)) THEN
     IF FNC_ISKNUMBER(SUBSTR(WK_STR,I + 1,1)) THEN 
      WK_STR := SUBSTR(WK_STR,1,I) || '十' || TRIM(SUBSTR(WK_STR,I + 1,L));
      I := I + 1;
     END IF;
   END IF;
   I := I + 1;
  END LOOP;
  WK_STR := REPLACE(WK_STR,'十十','十');
  WK_STR := REPLACE(WK_STR,'一十','十');
  WK_STR := REPLACE(WK_STR,'十十','十');
  RETURN WK_STR;
END FNC_ASU2KSU;

FUNCTION FNC_ISZNUMBER (NUM_STR IN VARCHAR2) RETURN BOOLEAN
IS
BEGIN
  IF FNC_ISANUMBER(NUM_STR) OR FNC_ISKNUMBER(NUM_STR) THEN
   RETURN TRUE;
  ELSE
   RETURN FALSE;
  END IF;
END FNC_ISZNUMBER;

FUNCTION FNC_ISANUMBER (NUM_STR IN VARCHAR2) RETURN BOOLEAN
IS
BEGIN
  IF INSTR(TRANSLATE(NUM_STR,'0123456789','@@@@@@@@@@'),'@') > 0 THEN
   RETURN TRUE;
  ELSE
   RETURN FALSE;
  END IF;
END FNC_ISANUMBER;

FUNCTION FNC_ISKNUMBER (NUM_STR IN VARCHAR2) RETURN BOOLEAN
IS
BEGIN
  IF INSTR(TRANSLATE(NUM_STR,'一二三四五六七八九十','@@@@@@@@@@'),'@') > 0 THEN
   RETURN TRUE;
  ELSE
   RETURN FALSE;
  END IF;
END FNC_ISKNUMBER;

FUNCTION FNC_ISBANTI (BAN_STR IN VARCHAR2) RETURN BOOLEAN
IS
  WK_STR VARCHAR2(50);
BEGIN
  WK_STR := REPLACE(REPLACE(BAN_STR,' ',''),' ','');
  WK_STR := TRANSLATE(WK_STR,'0123456789','@@@@@@@@@@');
  WK_STR := TRANSLATE(WK_STR,'番地号の-割街区部線・','@@@@@@@@@@@');
  IF LENGTH(REPLACE(WK_STR,'@','')) > 0 THEN
   RETURN FALSE;
  ELSE 
   RETURN TRUE;
  END IF; 
END FNC_ISBANTI;

FUNCTION FNC_ITAIJI (ITAI_STR IN VARCHAR2) RETURN VARCHAR2
IS
  SEIJ_STR VARCHAR2(32767);
BEGIN
  SEIJ_STR := ITAI_STR;
  SEIJ_STR := TRANSLATE(SEIJ_STR,'ヶッ塚斎檜與澤櫻嵩圓應參實釋條眞數淺曾臺邊槇籔藪豫',
                 'ケツ斉桧与沢桜高円応参実釈条真数瀬浅曽台辺槙薮薮予');
  IF SEIJ_STR = ITAI_STR THEN
   SEIJ_STR := TRANSLATE(SEIJ_STR,'ケツ斉桧与沢桜高円応参実釈条真数瀬浅曽台辺槙薮薮予',
                   'ヶッ塚斎檜與澤櫻嵩圓應參實釋條眞數淺曾臺邊槇籔藪豫');
  END IF;
  RETURN SEIJ_STR;
END FNC_ITAIJI;

END ADRS_EDIT;
/
名前:
コメント:

※文字化け等の原因になりますので顔文字の投稿はお控えください。

コメント利用規約に同意の上コメント投稿を行ってください。

 

※ブログ作成者から承認されるまでコメントは反映されません。

  • Xでシェアする
  • Facebookでシェアする
  • はてなブックマークに追加する
  • LINEでシェアする

最新の画像もっと見る

最近の「PL/SQL」カテゴリーもっと見る

最近の記事
バックナンバー
人気記事