国民经济行业导入数据库码表-优快云博客

1、先去下载word文档图片如下

2、粘贴到excel中形成insert 语句

3、创建临时表存excel中数据

1、先去下载word文档图片如下

国家统计局智能云搜索 (stats.gov.cn)

2、粘贴到excel中形成insert 语句

3、创建临时表存excel中数据

create table DIC_CODE_TMP
(
a INTEGER,
b VARCHAR2(5),
c VARCHAR2(5),
d VARCHAR2(5),
e VARCHAR2(5),
f VARCHAR2(500)
)

存入后表如下：

4、创建码表保存信息

create table DIC_CODE_INDUSTRY
(
id INTEGER not null,
code_type VARCHAR2(32),
code_key VARCHAR2(32),
code_value VARCHAR2(64),
code_name VARCHAR2(256),
parent_id INTEGER,
code_desc VARCHAR2(1000),
code_group VARCHAR2(64),
ordinal NUMBER(3),
can_modify_ind CHAR(1),
status CHAR(1),
version VARCHAR2(32)
)

5、编写存储过程放入表中

5.1存储第四级码值及父机构

CREATE OR REPLACE PROCEDURE DIC_CODE_TMP_PRO_LEV4
IS
V_A NUMBER;
V_B VARCHAR2(10);
V_C VARCHAR2(10);
V_D VARCHAR2(10);
V_E VARCHAR2(10);
V_F VARCHAR2(2000);
V_PARENTID NUMBER;
BEGIN
FOR CUR IN(
select A,B,C,D,E,F from DIC_CODE_TMP LEV4 where LEV4.E is not null AND LEV4.D IS NULL) LOOP
DBMS_OUTPUT.PUT_LINE(V_A||V_B||V_C||V_D||V_E||V_F||V_PARENTID);
V_A :=CUR.A;
V_B :=CUR.B;
V_C :=CUR.C;
V_D :=CUR.D;
V_E :=CUR.E;
V_F :=CUR.F;
SELECT Max(A) into V_PARENTID FROM DIC_CODE_TMP WHERE A <v_A AND D IS NOT NULL;
insert into dic_code_industry (ID, CODE_TYPE, CODE_KEY, CODE_VALUE, CODE_NAME, PARENT_ID, CODE_DESC, CODE_GROUP, ORDINAL, CAN_MODIFY_IND, STATUS, VERSION)
values (V_A, null, CUR.E, CUR.E, CUR.F,V_PARENTID, null, null, null, null, null, null);
COMMIT;
END LOOP;
END DIC_CODE_TMP_PRO_LEV4;

5.2存储第三级码值及父机构

CREATE OR REPLACE PROCEDURE DIC_CODE_TMP_PRO_LEV3
IS
V_A NUMBER;
V_B VARCHAR2(10);
V_C VARCHAR2(10);
V_D VARCHAR2(10);
V_E VARCHAR2(10);
V_F VARCHAR2(2000);
V_PARENTID NUMBER;
BEGIN
FOR CUR IN(
select A,B,C,D,E,F from DIC_CODE_TMP LEV3 where LEV3.D is not null AND LEV3.C IS NULL) LOOP
DBMS_OUTPUT.PUT_LINE(V_A||V_B||V_C||V_D||V_E||V_F||V_PARENTID);
V_A :=CUR.A;
V_B :=CUR.B;
V_C :=CUR.C;
V_D :=CUR.D;
V_E :=CUR.E;
V_F :=CUR.F;
SELECT Max(A) into V_PARENTID FROM DIC_CODE_TMP WHERE A <v_A AND C IS NOT NULL;
insert into dic_code_industry (ID, CODE_TYPE, CODE_KEY, CODE_VALUE, CODE_NAME, PARENT_ID, CODE_DESC, CODE_GROUP, ORDINAL, CAN_MODIFY_IND, STATUS, VERSION)
values (V_A, null, CUR.D, CUR.D, CUR.F,V_PARENTID, null, null, null, null, null, null);
COMMIT;
END LOOP;
END DIC_CODE_TMP_PRO_LEV3;

5.3存储第二级码值及父机构

CREATE OR REPLACE PROCEDURE DIC_CODE_TMP_PRO_LEV2
IS
V_A NUMBER;
V_B VARCHAR2(10);
V_C VARCHAR2(10);
V_D VARCHAR2(10);
V_E VARCHAR2(10);
V_F VARCHAR2(2000);
V_PARENTID NUMBER;
BEGIN
FOR CUR IN(
select A,B,C,D,E,F from DIC_CODE_TMP LEV3 where LEV3.C is not null AND LEV3.B IS NULL) LOOP
DBMS_OUTPUT.PUT_LINE(V_A||V_B||V_C||V_D||V_E||V_F||V_PARENTID);
V_A :=CUR.A;
V_B :=CUR.B;
V_C :=CUR.C;
V_D :=CUR.D;
V_E :=CUR.E;
V_F :=CUR.F;
SELECT Max(A) into V_PARENTID FROM DIC_CODE_TMP WHERE A <v_A AND B IS NOT NULL;
insert into dic_code_industry (ID, CODE_TYPE, CODE_KEY, CODE_VALUE, CODE_NAME, PARENT_ID, CODE_DESC, CODE_GROUP, ORDINAL, CAN_MODIFY_IND, STATUS, VERSION)
values (V_A, null, CUR.C, CUR.C, CUR.F,V_PARENTID, null, null, null, null, null, null);
COMMIT;
END LOOP;
END DIC_CODE_TMP_PRO_LEV2;

5.4存储第一级码值

CREATE OR REPLACE PROCEDURE DIC_CODE_TMP_PRO_LEV1
IS
V_A NUMBER;
V_B VARCHAR2(10);
V_C VARCHAR2(10);
V_D VARCHAR2(10);
V_E VARCHAR2(10);
V_F VARCHAR2(2000);
V_PARENTID NUMBER;
BEGIN
FOR CUR IN(
select A,B,C,D,E,F from DIC_CODE_TMP LEV1 where LEV1.B is not null) LOOP
DBMS_OUTPUT.PUT_LINE(V_A||V_B||V_C||V_D||V_E||V_F||V_PARENTID);
V_A :=CUR.A;
V_B :=CUR.B;
V_C :=CUR.C;
V_D :=CUR.D;
V_E :=CUR.E;
V_F :=CUR.F;
insert into dic_code_industry (ID, CODE_TYPE, CODE_KEY, CODE_VALUE, CODE_NAME, PARENT_ID, CODE_DESC, CODE_GROUP, ORDINAL, CAN_MODIFY_IND, STATUS, VERSION)
values (V_A, null, CUR.B, CUR.B, CUR.F,'', null, null, null, null, null, null);
COMMIT;
END LOOP;
END DIC_CODE_TMP_PRO_LEV1;

5.5存储第三级第四级类别一样的数据

创建一个序列存id

1、先去下载word文档图片如下

2、粘贴到excel中形成insert 语句

create sequence SEQ_dic_code_industry
minvalue 1
maxvalue 9999999999999999999999999999
start with 1776
increment by 1;

CREATE OR REPLACE PROCEDURE DIC_CODE_TMP_PRO_LEV4_3
IS
V_A NUMBER;
V_B VARCHAR2(10);
V_C VARCHAR2(10);
V_D VARCHAR2(10);
V_E VARCHAR2(10);
V_F VARCHAR2(2000);
V_PARENTID NUMBER;
BEGIN
FOR CUR IN(
select A,B,C,D,E,F from DIC_CODE_TMP LEV4 where LEV4.E is not null AND LEV4.D IS not NULL) LOOP
DBMS_OUTPUT.PUT_LINE(V_A||V_B||V_C||V_D||V_E||V_F||V_PARENTID);
V_PARENTID :=CUR.A;
V_B :=CUR.B;
V_C :=CUR.C;
V_D :=CUR.D;
V_E :=CUR.E;
V_F :=CUR.F;
insert into dic_code_industry (ID, CODE_TYPE, CODE_KEY, CODE_VALUE, CODE_NAME, PARENT_ID, CODE_DESC, CODE_GROUP, ORDINAL, CAN_MODIFY_IND, STATUS, VERSION)
values (SEQ_dic_code_industry.nextval, null, CUR.E, CUR.E, CUR.F,V_PARENTID, null, null, null, null, null, null);
COMMIT;
END LOOP;
END DIC_CODE_TMP_PRO_LEV4_3;