oracle大数据匹配处理C#
时间：2021-07-01 10:21:17
帮助过：16人阅读
OR REPLACE
PROCEDURE "ENTNAIMP" AS
BEGIN
DECLARE
one_val VARCHAR2(50);
ent_code NUMBER;
ENT_NAME VARCHAR2(500);
PRO_OP_TYPE VARCHAR2(10);
ENT_TYPE_NAME VARCHAR2(100);
EAT_TYPE VARCHAR2(10);
CGLX VARCHAR2(10);
LIC_ID VARCHAR2(50);
LIC_CODE VARCHAR2(50);
VALID_FROM_DATE DATE;
VALID_TO_DATE DATE;
COPY_COUNT NUMBER;
UNIT_ID VARCHAR2(20);
UNIT_IDNUM NUMBER;
LIC_STATE_ID VARCHAR2(10);
lic_state VARCHAR2(10);
LIC_ISSUE_TIME DATE;
SIGNER VARCHAR2(200);
LIC_ISSUE_AUTH VARCHAR2(200);
Guid VARCHAR2(50);
PRIL_NAME VARCHAR2(200);
JOB_ID VARCHAR2(20);
PER_ID VARCHAR2(50);
ENT_LEGAL_REP_ID VARCHAR2(50);
JOB_NAME VARCHAR2(50);
WORK_TYPE VARCHAR2(50);
PER_NAME VARCHAR2(80);
SEX VARCHAR2(10);
NATION_ID VARCHAR2(10);
NATION VARCHAR2(50);
CER_TYPE_ID VARCHAR2(10);
CER_TYPE_NAME VARCHAR2(50);
OFFICE_TEL VARCHAR2(100);
MOBILE VARCHAR2(100);
ENT_ID VARCHAR2(80);
REG_ADDR VARCHAR2(250);
AREA_CODE VARCHAR2(50);
REG_ADDR_POSTAL_CODE VARCHAR2(50);
ENT_EMAIL VARCHAR2(100);
ENT_PROPERTY_ID VARCHAR2(10);
ENT_PROPERTY_NAME VARCHAR2(30);
UNITE_CREDIT_NO VARCHAR2(50);
PK_IDZ VARCHAR2(50);
WORK_SCOPE VARCHAR2(4000);
--游标
cursor var_one is SELECT PK_ID,LIC_ID,LIC_NO,FOOD_OPER_NAME,DOM,DOM_CODE,OPLOC,PRIL_NAME,PRIL_NO,POSTAL_CODE,FIX_TEL,FAX,EMAIL,PRO_OP_TYPE,
VAL_FROM,VAL_TO,COPY_NUM,ISS_AUTH,STATE,APPRO_DATE,EMP_NUM,HEALTH_EXAM_NUM,DINING_SEATS,ECO_NATURE,NET_OP,CENTRAL_KIT,TRANSPORT_DEPT,
NET_ADDR,ALLOW_PER,"STORAGE",AUTO_SELL,XYDM,IS_ENTITY_SHOP,IS_BULK_DELI_SALE,IS_SINCE_WINE_SALE,PRO_OP_ITEM_DETAIL,ISS_AUTH_DETAIL,
PRO_OP_TYPE_DETAIL,CGLX,EAT_TYPE,IS_SCHOOL_CANT FROM SP_JY_LIC_INFO WHERE ROWNUM<2 and FAX is null;
--游标
cursor var_two is SELECT PER_KIND,"TYPE","NAME",SEX,NATION,CER_TYPE,TEL,MOB_TEL from SP_JY_LIC_PER_INFO where lic_id= one_val;
BEGIN
for one_row in var_one LOOP
dbms_output.put_line(‘单程外层循环代码处‘);
one_val:= one_row.LIC_ID;
update SP_JY_LIC_INFO set FAX=‘2‘ where LIC_ID=one_val;
--获取各种值
ENT_NAME:=one_row.FOOD_OPER_NAME;
PRO_OP_TYPE:=one_row.PRO_OP_TYPE;
EAT_TYPE:=one_row.EAT_TYPE;
CGLX:=one_row.CGLX;
If PRO_OP_TYPE=‘1‘ then
PRO_OP_TYPE := ‘120401‘;
ENT_TYPE_NAME := ‘食品销售经营者‘;
Elsif PRO_OP_TYPE=‘2‘ then
PRO_OP_TYPE := ‘120402‘ + CGLX;
if CGLX=‘01‘ then
ENT_TYPE_NAME:=‘特大型餐饮‘;
Elsif CGLX=‘02‘ then
ENT_TYPE_NAME:=‘大型餐饮‘;
Elsif CGLX=‘03‘ then
ENT_TYPE_NAME:=‘中型餐饮‘;
Elsif CGLX=‘04‘ then
ENT_TYPE_NAME:=‘小型餐饮‘;
Else
ENT_TYPE_NAME:=‘其他餐饮‘;
PRO_OP_TYPE := ‘12040205‘;
end if;
Elsif PRO_OP_TYPE=‘3‘ then
PRO_OP_TYPE := ‘1204030‘ + EAT_TYPE;
if EAT_TYPE=‘1‘ THEN
ENT_TYPE_NAME := ‘学校（含托幼机构）食堂‘;
Elsif EAT_TYPE=‘2‘ THEN
ENT_TYPE_NAME := ‘机关企事业单位食堂‘;
Elsif EAT_TYPE=‘3‘ THEN
ENT_TYPE_NAME := ‘工地食堂‘;
ELSE
ENT_TYPE_NAME := ‘其他食堂‘;
PRO_OP_TYPE := ‘12040304‘;
end if;
End if;
LIC_ID:=one_row.LIC_ID;
LIC_CODE:=one_row.LIC_NO;
--12-9月 -16
VALID_FROM_DATE:=to_date(one_row.VAL_FROM,‘yyyy-MM-dd‘);
VALID_TO_DATE:=to_date(one_row.VAL_TO,‘yyyy-MM-dd‘);
COPY_COUNT:=one_row.COPY_NUM;
UNIT_ID:=one_row.ISS_AUTH;
UNIT_IDNUM:="LENGTH"(UNIT_ID);
if UNIT_IDNUM=6 THEN
UNIT_ID := UNIT_ID+‘00‘;
ELSIF UNIT_IDNUM=4 THEN
UNIT_ID := UNIT_ID+‘0000‘;
ELSIF UNIT_IDNUM=2 THEN
UNIT_ID := UNIT_ID+‘000000‘;
END if;
LIC_STATE_ID:=one_row.STATE;
if LIC_STATE_ID=‘1‘ THEN
LIC_STATE_ID := ‘10‘;
lic_state := ‘有效‘;
ELSIF LIC_STATE_ID=‘2‘ THEN
LIC_STATE_ID := ‘30‘;
lic_state := ‘被撤销‘;
ELSIF LIC_STATE_ID=‘5‘ THEN
LIC_STATE_ID := ‘50‘;
lic_state := ‘注销‘;
end if;
LIC_ISSUE_TIME:=to_date(one_row.APPRO_DATE,‘yyyy-MM-dd‘);
SIGNER:=one_row.ALLOW_PER;
LIC_ISSUE_AUTH:=one_row.ISS_AUTH_DETAIL;
PRIL_NAME:=one_row.PRIL_NAME;
Guid:=LOWER(RAWTOHEX(sys_guid()));
dbms_output.put_line(Guid);
--获取ENT_CODE
SELECT CODEID into ent_code from code;
ent_code :=ent_code+1;
UPDATE code set CODEID=ent_code ;
for two_row in var_two LOOP
--处理内循环
dbms_output.put_line(‘内层循环代码处‘);
JOB_ID:=two_row.PER_KIND;
PER_ID:=LOWER(RAWTOHEX(sys_guid()));
if JOB_ID=‘1‘ THEN
JOB_ID := ‘1‘;
JOB_NAME := ‘法定代表人‘;
ENT_LEGAL_REP_ID := PER_ID;
ELSIF JOB_ID=‘2‘ THEN
JOB_ID := ‘79‘;
JOB_NAME := ‘食品安全专业技术人员‘;
ELSIF JOB_ID=‘3‘ THEN
JOB_ID := ‘80‘;
JOB_NAME := ‘食品安全管理人员‘;
ELSE
JOB_ID := ‘78‘;
JOB_NAME := ‘其他‘;
end if;
WORK_TYPE:=two_row."TYPE";
insert into ENT_PERSON_JOB(ID,ENT_CODE,JOB_ID,JOB_NAME,WORK_TYPE,ENT_NAME,ENT_TYPE_ID,ENT_TYPE_NAME,PER_ID)
values( RAWTOHEX(sys_guid()),ent_code, JOB_ID, JOB_NAME, WORK_TYPE, ENT_NAME, PRO_OP_TYPE, ENT_TYPE_NAME, PER_ID);
PER_NAME:=two_row."NAME";
SEX:=two_row.SEX;
if SEX=‘2‘ THEN
SEX := ‘0‘;
end if;
if NATION_ID=1 THEN NATION_ID := ‘01‘; NATION := ‘汉族‘;
ELSIF NATION_ID=2 THEN NATION_ID := ‘02‘; NATION := ‘蒙古族‘;
ELSIF NATION_ID=3 THEN NATION_ID := ‘03‘; NATION := ‘回族‘;
ELSIF NATION_ID=4 THEN NATION_ID := ‘04‘; NATION := ‘藏族‘;
ELSIF NATION_ID=5 THEN NATION_ID := ‘05‘; NATION := ‘维吾尔族‘;
ELSIF NATION_ID=6 THEN NATION_ID := ‘06‘; NATION := ‘苗族‘;
ELSIF NATION_ID=7 THEN NATION_ID := ‘07‘; NATION := ‘彝族‘;
ELSIF NATION_ID=8 THEN NATION_ID := ‘08‘; NATION := ‘壮族‘;
ELSIF NATION_ID=9 THEN NATION_ID := ‘09‘; NATION := ‘布衣族‘;
ELSIF NATION_ID=10 THEN NATION_ID := ‘10‘; NATION := ‘朝鲜族‘;
ELSIF NATION_ID=11 THEN NATION_ID := ‘11‘; NATION := ‘满族‘;
ELSIF NATION_ID=12 THEN NATION_ID := ‘12‘; NATION := ‘侗族‘;
ELSIF NATION_ID=13 THEN NATION_ID := ‘13‘; NATION := ‘瑶族‘;
ELSIF NATION_ID=14 THEN NATION_ID := ‘14‘; NATION := ‘白族‘;
ELSIF NATION_ID=15 THEN NATION_ID := ‘15‘; NATION := ‘土家族‘;
ELSIF NATION_ID=16 THEN NATION_ID := ‘16‘; NATION := ‘哈尼族‘;
ELSIF NATION_ID=17 THEN NATION_ID := ‘17‘; NATION := ‘哈萨克族‘;
ELSIF NATION_ID=18 THEN NATION_ID := ‘18‘; NATION := ‘傣族‘;
ELSIF NATION_ID=19 THEN NATION_ID := ‘19‘; NATION := ‘黎族‘;
ELSIF NATION_ID=20 THEN NATION_ID := ‘20‘; NATION := ‘傈傈族‘;
ELSIF NATION_ID=21 THEN NATION_ID := ‘21‘; NATION := ‘佤族‘;
ELSIF NATION_ID=22 THEN NATION_ID := ‘22‘; NATION := ‘畲族‘;
ELSIF NATION_ID=23 THEN NATION_ID := ‘23‘; NATION := ‘高山族‘;
ELSIF NATION_ID=24 THEN NATION_ID := ‘24‘; NATION := ‘拉祜族‘;
ELSIF NATION_ID=25 THEN NATION_ID := ‘25‘; NATION := ‘水族‘;
ELSIF NATION_ID=26 THEN NATION_ID := ‘26‘; NATION := ‘东乡族‘;
ELSIF NATION_ID=27 THEN NATION_ID := ‘27‘; NATION := ‘纳西族‘;
ELSIF NATION_ID=28 THEN NATION_ID := ‘28‘; NATION := ‘景颇族‘;
ELSIF NATION_ID=29 THEN NATION_ID := ‘29‘; NATION := ‘柯尔克孜族‘;
ELSIF NATION_ID=30 THEN NATION_ID := ‘30‘; NATION := ‘土族‘;
ELSIF NATION_ID=31 THEN NATION_ID := ‘31‘; NATION := ‘达斡尔族‘;
ELSIF NATION_ID=32 THEN NATION_ID := ‘32‘; NATION := ‘仫佬族‘;
ELSIF NATION_ID=33 THEN NATION_ID := ‘33‘; NATION := ‘羌族‘;
ELSIF NATION_ID=34 THEN NATION_ID := ‘34‘; NATION := ‘布朗族‘;
ELSIF NATION_ID=35 THEN NATION_ID := ‘35‘; NATION := ‘撒拉族‘;
ELSIF NATION_ID=36 THEN NATION_ID := ‘36‘; NATION := ‘毛难族‘;
ELSIF NATION_ID=37 THEN NATION_ID := ‘37‘; NATION := ‘仡佬族‘;
ELSIF NATION_ID=38 THEN NATION_ID := ‘38‘; NATION := ‘锡伯族‘;
ELSIF NATION_ID=39 THEN NATION_ID := ‘39‘; NATION := ‘阿昌族‘;
ELSIF NATION_ID=40 THEN NATION_ID := ‘40‘; NATION := ‘普米族‘;
ELSIF NATION_ID=41 THEN NATION_ID := ‘41‘; NATION := ‘塔吉克族‘;
ELSIF NATION_ID=42 THEN NATION_ID := ‘42‘; NATION := ‘怒族‘;
ELSIF NATION_ID=43 THEN NATION_ID := ‘43‘; NATION := ‘乌孜别克族‘;
ELSIF NATION_ID=44 THEN NATION_ID := ‘44‘; NATION := ‘俄罗斯族‘;
ELSIF NATION_ID=45 THEN NATION_ID := ‘45‘; NATION := ‘鄂温克族族‘;
ELSIF NATION_ID=46 THEN NATION_ID := ‘46‘; NATION := ‘崩龙族‘;
ELSIF NATION_ID=47 THEN NATION_ID := ‘47‘; NATION := ‘保安族‘;
ELSIF NATION_ID=48 THEN NATION_ID := ‘48‘; NATION := ‘裕固族‘;
ELSIF NATION_ID=49 THEN NATION_ID := ‘49‘; NATION := ‘京族‘;
ELSIF NATION_ID=50 THEN NATION_ID := ‘50‘; NATION := ‘塔塔尔族‘;
ELSIF NATION_ID=51 THEN NATION_ID := ‘51‘; NATION := ‘独龙族‘;
ELSIF NATION_ID=52 THEN NATION_ID := ‘52‘; NATION := ‘鄂伦春族‘;
ELSIF NATION_ID=53 THEN NATION_ID := ‘53‘; NATION := ‘赫哲族‘;
ELSIF NATION_ID=54 THEN NATION_ID := ‘54‘; NATION := ‘门巴族‘;
ELSIF NATION_ID=55 THEN NATION_ID := ‘55‘; NATION := ‘珞巴族‘;
ELSIF NATION_ID=56 THEN NATION_ID := ‘56‘; NATION := ‘基诺族‘;
ELSIF NATION_ID=97 THEN NATION_ID := ‘97‘; NATION := ‘其他‘;
ELSIF NATION_ID=98 THEN NATION_ID := ‘98‘; NATION := ‘外国血统中国籍人士‘;
END IF;
CER_TYPE_ID:=trim(two_row.CER_TYPE);
if CER_TYPE_ID=‘1‘ THEN
CER_TYPE_NAME := ‘身份证‘;
ELSIF CER_TYPE_ID=‘4‘ THEN
CER_TYPE_NAME := ‘护照‘;
ELSIF CER_TYPE_ID=‘7‘ THEN
CER_TYPE_NAME := ‘港澳台‘;
ELSE
CER_TYPE_ID := ‘9‘;
CER_TYPE_NAME := ‘其他‘;
END IF;
OFFICE_TEL:=two_row.TEL;
MOBILE:=two_row.MOB_TEL;
insert into ENT_PERSON(PER_ID,PER_NAME,SEX,NATION_ID,NATION,CER_TYPE_ID,CER_TYPE_NAME,OFFICE_TEL,MOBILE)
values(PER_ID, PER_NAME, SEX, NATION_ID, NATION, CER_TYPE_ID, CER_TYPE_NAME, OFFICE_TEL, MOBILE);
dbms_output.put_line(‘内循环完毕‘);
END LOOP;
--单程循环
dbms_output.put_line(‘单程外层循环代码处‘);
insert into ENT_LICENSE(LIC_ID,LIC_CODE,VALID_FROM_DATE,VALID_TO_DATE,COPY_COUNT,UNIT_ID,LIC_STATE_ID,lic_state,LIC_ISSUE_TIME,SIGNER,LIC_ISSUE_AUTH,ENT_CODE)
values(Guid, LIC_CODE, VALID_FROM_DATE, VALID_TO_DATE, COPY_COUNT, UNIT_ID, LIC_STATE_ID, lic_state, LIC_ISSUE_TIME, SIGNER, LIC_ISSUE_AUTH, ent_code);
ENT_ID:=LOWER(RAWTOHEX(sys_guid()));
REG_ADDR:=one_row.DOM;
AREA_CODE:=one_row.DOM_CODE;
REG_ADDR_POSTAL_CODE:=one_row.POSTAL_CODE;
ENT_EMAIL:=one_row.POSTAL_CODE;
ENT_PROPERTY_ID:=one_row.ECO_NATURE;
if ENT_PROPERTY_ID=‘4‘ THEN
ENT_PROPERTY_NAME:=‘其他‘;
ELSIF ENT_PROPERTY_ID=‘1‘ THEN
ENT_PROPERTY_NAME:=‘企业‘;
ELSIF ENT_PROPERTY_ID=‘2‘ THEN
ENT_PROPERTY_NAME:=‘个体工商户‘;
ELSIF ENT_PROPERTY_ID=‘3‘ THEN
ENT_PROPERTY_NAME:=‘农民专业合作社‘;
END IF;
UNITE_CREDIT_NO:=one_row.XYDM;
insert into ENT_BASIC(ENT_CODE,ENT_NAME,REG_ADDR,AREA_CODE,REG_ADDR_POSTAL_CODE,ENT_EMAIL,ENT_PROPERTY_ID,ENT_PROPERTY_NAME,UNITE_CREDIT_NO,ENT_ID,ENT_LEGAL_REP,ENT_LEGAL_REP_ID)
values(ent_code, ENT_NAME, REG_ADDR, AREA_CODE, REG_ADDR_POSTAL_CODE,ENT_EMAIL, ENT_PROPERTY_ID, ENT_PROPERTY_NAME, UNITE_CREDIT_NO,ENT_
