PL\SQL语法参考实例

CREATE procedure pro_dd_JOB_PD /****** 名称：派单JOB ******/ /****** 功能：实现据不同业务对应施工环节派发施工单 ******/ /****** 备注：考虑施工环节并发,体现为单用pro_dd_job_PD_UPD ******/ /****** 创建：1999.09.13 ******/ iS cursor c_dlb is select * from dlb_pd where clzt='S245' and jb in(select jb from xtb_sfdyyq where UPPER(dyyq)='Y') --and slbh='20010702A300001' order by slbh ; cursor c_dlb1(M_JBLX CHAR,M_YW LB CHAR,M_SLFS CHAR,M_HJSX NUMBER,M_LCBH VARCHAR2) is select * from XTB_YWLB WHERE JBLX=M_JBLX AND YWLB=M_YWLB AND CLFS=M_SLFS AND HJSX=M_HJSX AND LCBH=M_LCBH ; C_DLB1%ROWTYPE; T_hjsx xtb_ywlb.hjsx%type; T_dlbm xtb_ywlb.dlbm%type; T_hjbh xtb_ywlb.hjbh%type; V_CLZT varchar2(4); --V_GDBH DLB_WXSG.GDBH%TYPE; n_count number; M_YYJ XTB_JBMC.YYJ%TYPE; M_JBLX XTB_JBMC.JBLX%TYPE; M_YWX XTB_YWLSH.YWX%TYPE; v_wcbz1 varchar2(1); v_wcbz VARCHAR2(10); V_JCZTZ1 varchar2(1); V_JCZTZ VARCHAR2(10); v_gdbh1 varchar2(10); v_gdbh varchar2(17); V_GDBH_STR varchar2(8); v_errorcode number; v_errortext varchar2(200); W_BZ VARCHAR2(1):='0'; W_JJXH ZYB_ZGXX.JJXH%TYPE; W_FXHH ZYB_PXXX.FXHH%TYPE; W_ZGXXH ZYB_ZGXX.ZGXXH%TYPE; W_PXXXH ZYB_PXXX.PXXXH%TYPE; W_JB ZYB_ZGXX.JB%TYPE; W_YDHHM GDB_DH.YDHHM%TYPE; I NUMBER; begin for T_dlb in c_dlb loop begin --dbms_output.put_line('1'||T_DLB.SLBH||'*'); W_BZ:='0'; IF substr(t_dlb.ywlb,2,1)<>'Y' then --非小灵通 IF SUBSTR(T_DLB.YWLB,3,1) IN ('2','3','5') THEN SELECT YDHHM,JB INTO W_YDHHM ,W_JB FROM GDB_DH WHERE SLBH=T_DLB.SLBH; BEGIN SELECT JJXH,ZGXXH INTO W_JJXH,W_ZGXXH FROM ZYB_ZGXX WHERE DHHM=W_YDHHM AND JB=W_JB; SELECT PXXXH,FXHH INTO W_PXXXH,W_FXHH FROM ZYB_PXXX WHERE DHHM=W_YDHHM AND JB=W_JB; W_BZ:='0'; DELETE FROM ZYB_WXDA WHERE SLBH=T_dlb.SLBH AND JB=W_JB; exception when others then ROLLBACK; BEGIN SELECT ZGXXH,PXXXH INTO W_ZGXXH,W_PXXXH FROM ZYB_ZBSB WHERE DHHM=W_YDHHM AND JB=W_JB; W_BZ:='0'; DELETE FROM ZYB_WXDA WHERE SLBH=T_dlb.SLBH AND JB=W_JB; exception when others then ROLLBACK; W_BZ:='0'; SELECT COUNT(*) INTO I FROM ZYB_WXDA WHERE SLBH=T_dlb.SLBH AND JB=W_JB; IF I=0 THEN INSERT INTO ZYB_WXDA (SLBH,DHHM,JJXH,FXHH,ZGXXH,PXXXH,CLZT,JRSJ,JB) SELECT T_DLB.SLBH,W_YDHHM,W_JJXH,W_FXHH,W_ZGXXH,W_PXXXH,'S245',SYSDATE,W_JB FROM DUAL; END IF; COMMIT; END; END; END IF; end if; IF W_BZ='0' THEN ---3333 V_JCZTZ:=' '; v_wcbz:=' '; --生成工单编号 BEGIN SELECT SUBSTR(GDBH,6,4) INTO V_GDBH_STR FROM xtb_jb_gdbh WHERE JB=T_DLB.JB AND YWX=SUBSTR(T_DLB.YWLB,2,2) AND ROWNUM<2; exception when others then INSERT INTO xtb_jb_gdbh VALUES(T_DLB.JB,SUBSTR(T_DLB.YWLB,2,2),SUBSTR(T_DLB.JB,3,3)||SUBSTR(T_DLB.YWLB,2,2)|| char(sysdate,'YYYY')||'00000'); V_GDBH_STR:=to_char(sysdate,'YYYY'); END; IF v_gdbh_str<to_char(sysdate,'YYYY') then --跨年处理 update xtb_jb_gdbh set gdbh=substr(gdbh,1,5)||to_char(sysdate,'YYYY')||'00000' WHERE JB=T_DLB.JB AND YWX=SUBSTR(T_DLB.YWLB,2,2); --因其为主程序，则可提交 commit; end if; select YYJ,JBLX into m_YYJ,M_JBLX from xtb_jbmc where JB=t_DLB.jb and rownum<2; select hjsx+1 into T_hjsx from xtb_ywlb where jblx=M_JBLX and ywlb=T_DLB.ywlb and clfs=T_DLB.slfs and hjbh='H315' AND LCBH=T_DLB.LCBH ; --生成工单编号 select gdbh into v_gdbh from xtb_jb_gdbh WHERE JB=T_DLB.JB AND YWX=SUBSTR(T_DLB.YWLB,2,2) for update of gdbh; v_gdbh1:=ltrim(rtrim(to_char(to_number(substr(v_gdbh,10,5))+1,'00000'))); UPDATE xtb_jb_gdbh SET GDBH=substr(gdbh,1,9)||ltrim(rtrim(V_GDBH1)) WHERE JB=T_DLB.JB AND YWX=SUBSTR(T_DLB.YWLB,2,2) ; v_gdbh:=substr(v_gdbh,1,9)||v_gdbh1; --修改派单队列表状态 insert into LSB_PD(jb,slbh,gdbh,ywlb,gh,clsj,CZJG) select jb,slbh,v_gdbh,ywlb,' ',jrsj,substr(clzt,1,2)||'1'||substr(clzt,4,1) from dlb_pd where slbh=t_dlb.slbh; --最好用动态,但因加此表就以捆绑则...(反对.) insert into DLB_HL(jb,slbh,ywlb,slfs,lcbh,clgh,cljb,clyy,clzt,jrsj) select jb,slbh,ywlb,slfs,lcbh,clgh,cljb,clyy,'S24B',jrsj from dlb_pd where slbh=t_dlb.slbh; delete from dlb_pd where slbh=T_DLB.slbh; update gdb_dh set jcztz=fun_pub_jcztz(jcztz,'H315','S215'),GDBH=V_GDBH,pdsj=sysdate where slbh=T_DLB.slbh; update gdb_dh set jcztz=fun_pub_jcztz(jcztz,'H31B','S24B'),GDBH=V_GDBH where slbh=T_DLB.slbh; dbms_output.put_line('8'); OPEN C_DLB1(m_JBLX,T_DLB.YWLB,T_DLB.SLFS,T_HJSX,T_DLB.LCBH); LOOP FETCH C_DLB1 INTO CS; EXIT WHEN C_DLB1%NOTFOUND OR C_DLB1%NOTFOUND IS NULL; V_JCZTZ1:=' '; v_wcbz1:=' '; V_CLZT:='S24'||substr(CS.hjbh,4,1); --调用PRO_INS_DLB过程插入相应队列表中 pro_ins_DLB_PD(CS.DLBM,T_DLB.jb,T_DLB.slbh,V_GDBH,T_DLB.ywlb,T_DLB.SLfs, T_DLB.cljb,V_CLZT,T_DLB.LCBH,CS.HJBH,v_wcbz1,V_JCZTZ1); --写三方的收单时间 UPDATE GDB_DH SET WXSDSJ=SYSDATE WHERE SLBH IN(SELECT SLBH FROM DLB_WXSG WHERE SLBH=T_DLB.slbh); UPDATE GDB_DH SET CLHDSJ=SYSDATE WHERE SLBH IN(SELECT SLBH FROM DLB_CLSG WHERE SLBH=T_DLB.slbh); UPDATE GDB_DH SET JFSDSJ=SYSDATE WHERE SLBH IN(SELECT SLBH FROM DLB_JFSG WHERE SLBH=T_DLB.slbh); V_WCBZ:=V_WCBZ||V_WCBZ1; V_JCZTZ:=V_JCZTZ||V_JCZTZ1; END LOOP; CLOSE C_DLB1; dbms_output.put_line('9'||'*'||V_WCBZ||'*'||V_JCZTZ); IF V_JCZTZ=V_WCBZ THEN COMMIT; ELSE ROLLBACK; END IF; END IF; exception when others then rollback; v_errorcode:=sqlcode; v_errortext:=substr(sqlerrm,1,190); update dlb_PD set clzt=substr(clzt,1,2)||'6'||substr(clzt,4,1) where slbh=T_DLB.slbh; select count(*) into n_count from xtb_job_error where slbh=T_DLB.slbh and cwgc='PRO_DD_JOB_PD' and rownum<2; if n_count>0 then update xtb_job_error set cwcs=cwcs+1 where slbh=T_DLB.slbh and cwgc='PRO_DD_JOB_PD'; else insert into xtb_job_error (slbh,cssj,hjbh,cwGC,cwcs,cwmc,code,message) values (T_DLB.slbh,sysdate,T_hjbh,'PRO_DD_JOB_PD',1,'派单派发过程失败!', v_errorcode,v_errortext); end if; commit; end; end loop; end;