• 设为首页
  • 点击收藏
  • 手机版
    手机扫一扫访问
    迪恩网络手机版
  • 关注官方公众号
    微信扫一扫关注
    公众号
登陆 注册

oracle大数据匹配处理C#

原作者: [db:作者] 来自: [db:来源] 收藏 邀请

忙碌了几天写出来的oracle存储过程在作业中执行。

写的oracle存储过程如果有什么不好的地方大家指点指点。

oracle存储过程其中使用到游标嵌套、if、if嵌套、数据插入表、select插入表、退出游标循环。

CREATE OR REPLACE 
PROCEDURE "ENTKK" 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,POS

TAL_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_SAL

E,IS_SINCE_WINE_SALE,PRO_OP_ITEM_DETAIL,ISS_AUTH_DETAIL,
    PRO_OP_TYPE_DETAIL,CGLX,EAT_TYPE,IS_SCHOOL_CANT,LIC_STATE_ID FROM 

SP_JY_LIC_INFO WHERE ROWNUM<81 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;  
        --获取各种值
        ENT_NAME:=one_row.FOOD_OPER_NAME;        
        select count(*) into isdet from ENT_BASIC where 

ENT_NAME=ENT_NAMEZ;
        if isdet=1 THEN
            insert into AA(Name) VALUES(ENT_NAMEZ);
            --不符合条件的退出return;
            return;
        end IF;
        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,P

ER_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=55  THEN NATION_ID := '55'; NATION := '某字符串'; 
    ELSIF NATION_ID=56  THEN NATION_ID := '56'; 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_PR

OPERTY_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_ID, PRIL_NAME, ENT_LEGAL_REP_ID);
PK_IDZ:=one_row.PK_ID;
insert into ENT_FOOD_BIZ_EXT

(EXTEND_ID,ent_code,BIZ_PLACE_ADDR,WORKER_COUNT,PHYSICAL_COUNT,SEATS_COUNT,

IS_HAVE_NETWORK_BIZ,IS_HAVE_CENTRAL_KITCHEN,IS_MEAL_DELIVER,WEB_URL,STORAGE

_ADDRESS,HAS_AUTO_SELL_EQUIP,HAS_STORES,IS_BULK_COOKED_FOOD,IS_HOME_BREWED,

IS_SCHOOL) 
SELECT  RAWTOHEX(sys_guid

()),ent_code,OPLOC,EMP_NUM,HEALTH_EXAM_NUM,DINING_SEATS,NET_OP,CENTRAL_KIT,

TRANSPORT_DEPT,NET_ADDR,STORAGE,AUTO_SELL,IS_ENTITY_SHOP,IS_BULK_DELI_SALE,

IS_SINCE_WINE_SALE,IS_SCHOOL_CANT  from sp_jy_lic_info where PK_ID=PK_IDZ ;
WORK_SCOPE:=one_row.PRO_OP_ITEM_DETAIL;
insert into ENT_TYPE_RELATION

(ID,ENT_CODE,ENT_TYPE_ID,ENT_TYPE_NAME,WORK_SCOPE) 
values(LOWER(RAWTOHEX(sys_guid())), ent_code, PRO_OP_TYPE, ENT_TYPE_NAME, 

WORK_SCOPE);
insert into ENT_EQUIP

(ID,ENT_CODE,ENT_TYPE_ID,EQUIP_NAME,EQUIP_COUNT,EQUIP_POSITION,REMARK) 
SELECT  RAWTOHEX(sys_guid

()),ent_code,PRO_OP_TYPE,NAME,COUNT_NO,PLACE,REMARK  from SP_JY_SAFE_INFO 

where LIC_ID=one_val; 

update  SP_JY_LIC_INFO set FAX='1' where LIC_ID=one_val;
dbms_output.put_line(ent_code);

END LOOP;  
--结尾处的
dbms_output.put_line('结尾');
END; 
END;
oracle

 

鲜花
握手
雷人
路过
鸡蛋
该文章已有0人参与评论

请发表评论

全部评论

专题导读
More+
上一篇：
crealloc函数和malloc函数发布时间：2022-07-13
下一篇：
Codeforces 549C（博弈）发布时间：2022-07-13
热门推荐
More+
热门话题
More+
阅读排行榜

扫描微信二维码

查看手机版网站

随时了解更新最新资讯

139-2527-9053

在线客服（服务时间 9:00～18:00）

在线QQ客服
地址：深圳市南山区西丽大学城创智工业园
电邮：jeky_zhao#qq.com
移动电话：139-2527-9053

Powered by 互联科技 X3.4© 2001-2213 极客世界.|Sitemap