[oracle] CREATE, PROCEDURE

위 파일로 작업~

* KIKcd_B 파일을 DB에 올리기

CREATE TABLE TMP_UPLOAD_COL10_TBL

(

  COL1   VARCHAR2(100 BYTE),

  COL2   VARCHAR2(100 BYTE),

  COL3   VARCHAR2(100 BYTE),

  COL4   VARCHAR2(100 BYTE),

  COL5   VARCHAR2(100 BYTE),

  COL6   VARCHAR2(100 BYTE),

  COL7   VARCHAR2(100 BYTE),

  COL8   VARCHAR2(100 BYTE),

  COL9   VARCHAR2(100 BYTE),

  COL10  VARCHAR2(100 BYTE)

);

 

 

* 파일을 올린 후, 주소를 최대 몇 개 까지의 space를 나눌 수 있는 지 파악하고 다른 테이블에 코드/시/구/동으로 각각의 필드에 넣어준다.

 

CREATE TABLE BJD_INFO (

    BJD_CODE NUMBER(19, 2),

    SI VARCHAR2(100),

    DONG VARCHAR2(100),

    GU VARCHAR2(100),

    RI VARCHAR2(100)

);

 

-- 2012.05.18

CREATE OR REPLACE PROCEDURE SCOTT.chap10

IS

    v_do varchar2(100) default '';

    v_si varchar2(100) default '';

    v_gu varchar2(100) default '';

    v_dong varchar2(100) default '';

    v_ri varchar2(100) default '';

BEGIN

    FOR v_list IN (

        SELECT

            col1, col2,

            regexp_substr(col2, '[^ ]+', 1, 1) col2_first,

            regexp_substr(col2, '[^ ]+', 1, 2) col2_second,

            regexp_substr(col2, '[^ ]+', 1, 3) col2_third,

            regexp_substr(col2, '[^ ]+', 1, 4) col2_fourth,

            regexp_substr(col2, '[^ ]+', 1, 5) col2_fifth

        FROM TMP_UPLOAD_COL10_TBL

    ) LOOP

        --  first: '시' or '도' 추출

        IF instr(v_list.col2_first, '도', 3) > 0 THEN

            v_do := v_list.col2_first;

        ELSIF instr(v_list.col2_first, '시', 3) > 0 THEN

            v_si := v_list.col2_first;

        END IF;

           

        -- second:

        IF instr(v_list.col2_second, '구', 2) > 0 THEN

            v_gu := v_list.col2_second;

        ELSIF instr(v_list.col2_second, '시', 3) > 0 THEN

            v_si := v_list.col2_second;

        END IF;

 

        -- third

        IF instr(v_list.col2_third, '구', 2) > 0 THEN

            v_gu := v_list.col2_third;

        ELSIF instr(v_list.col2_third, '동', 2) > 0 THEN

            v_dong := v_list.col2_third;

        ELSIF instr(v_list.col2_third, '읍', 2) > 0 THEN

            v_dong := v_list.col2_third;

        ELSIF instr(v_list.col2_third, '면', 2) > 0 THEN

            v_dong := v_list.col2_third;

        END IF;

 

        -- fourth

        IF instr(v_list.col2_fourth, '리', 3) > 0 THEN

            v_ri := v_list.col2_fourth;

        ELSIF instr(v_list.col2_fourth, '동', 2) > 0 THEN

            v_dong := v_list.col2_fourth;

        ELSIF instr(v_list.col2_fourth, '읍', 2)  > 0 THEN

            v_dong := v_list.col2_fourth;

        ELSIF instr(v_list.col2_fourth, '면', 2) > 0 THEN

            v_dong := v_list.col2_fourth;

        END IF;

 

      -- f ifth

        IF instr(v_list.col2_fifth, '리', 3) > 0 THEN

            v_ri := v_list.col2_fifth;

        END IF;

 

        INSERT INTO BJD_INFO

            SELECT

                col1,

                v_do,

                v_si,

                v_gu,

                v_dong,

                v_ri

            FROM TMP_UPLOAD_COL10_TBL t

            WHERE t.col1 = v_list.col1;

           

        v_do := '';

        v_si := '';

        v_gu := '';

        v_dong := '';

        v_ri := '';

    END LOOP;

END chap10;

/

 

 

 

-- 2012.05.18

*도/시, 시/군/구, 동/읍/면, 동, 리 순으로 정렬

CREATE OR REPLACE PROCEDURE SCOTT.chap10

IS

    v_do varchar2(100) default '';

    v_si varchar2(100) default '';

    v_gu varchar2(100) default '';

    v_dong varchar2(100) default '';

    v_ri varchar2(100) default '';

BEGIN

    FOR v_list IN (

        SELECT

            col1, col2,

            regexp_substr(col2, '[^ ]+', 1, 1) col2_first,

            regexp_substr(col2, '[^ ]+', 1, 2) col2_second,

            regexp_substr(col2, '[^ ]+', 1, 3) col2_third,

            regexp_substr(col2, '[^ ]+', 1, 4) col2_fourth,

            regexp_substr(col2, '[^ ]+', 1, 5) col2_fifth

        FROM TMP_UPLOAD_COL10_TBL

    ) LOOP

        --  first: '시' or '도' 추출

        IF instr(v_list.col2_first, '도', 3) > 0 THEN

            v_do := v_list.col2_first;

        ELSIF instr(v_list.col2_first, '시', 3) > 0 THEN

            v_si := v_list.col2_first;

        END IF;

           

        -- second:

        IF instr(v_list.col2_second, '구', 2) > 0 THEN

            v_gu := v_list.col2_second;

        ELSIF instr(v_list.col2_second, '시', 3) > 0 THEN

            v_si := v_list.col2_second;

        END IF;

 

        -- third

        IF instr(v_list.col2_third, '구', 2) > 0 THEN

            v_gu := v_list.col2_third;

        ELSIF instr(v_list.col2_third, '동', 2) > 0 THEN

            v_dong := v_list.col2_third;

        ELSIF instr(v_list.col2_third, '읍', 2) > 0 THEN

            v_dong := v_list.col2_third;

        ELSIF instr(v_list.col2_third, '면', 2) > 0 THEN

            v_dong := v_list.col2_third;

        END IF;

 

        -- fourth

        IF instr(v_list.col2_fourth, '리', 3) > 0 THEN

            v_ri := v_list.col2_fourth;

        ELSIF instr(v_list.col2_fourth, '동', 2) > 0 THEN

            v_dong := v_list.col2_fourth;

        ELSIF instr(v_list.col2_fourth, '읍', 2)  > 0 THEN

            v_dong := v_list.col2_fourth;

        ELSIF instr(v_list.col2_fourth, '면', 2) > 0 THEN

            v_dong := v_list.col2_fourth;

        END IF;

 

      -- f ifth

        IF instr(v_list.col2_fifth, '리', 3) > 0 THEN

            v_ri := v_list.col2_fifth;

        END IF;

 

        INSERT INTO BJD_INFO

            SELECT

                col1,

                v_do,

                v_si,

                v_gu,

                v_dong,

                v_ri

            FROM TMP_UPLOAD_COL10_TBL t

            WHERE t.col1 = v_list.col1;

           

        v_do := '';

        v_si := '';

        v_gu := '';

        v_dong := '';

        v_ri := '';

    END LOOP;

END chap10;

/

 

 

법정동코드예시

법정동코드_예시.txt

0.93MB