忙碌了几天写出来的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;