CREATE OR REPLACE PROCEDURE PP_SETPPORDER (MONUM_1 in VARCHAR2,--订单号
MTCODE_1 IN VARCHAR2,--整车号
MOQTYS_1 IN VARCHAR2,
PSDATE_1 IN VARCHAR2,
PFDATE_1 IN VARCHAR2,
CPCODE_1 IN VARCHAR2,
CPQTYS_1 IN VARCHAR2
)IS
ISSAVE INT;
ISCCX INT;
ISRK INT;
ORDSTATE INT;
COMPENTNUM INT;
ISEDIT VARCHAR2(20);
BEGIN
-------------------------------------------------------------首先判断订单头数量
select count(*) into ISSAVE from PP_MANUORDER p where P.Monum = MONUM_1;
SELECT COUNT(*) INTO ORDSTATE FROM PP_MANUORDER P WHERE P.Monum = MONUM_1 AND P.PLCODE = 'SMAZZ01' AND P.MOSTA = 'REL';IF ISSAVE = 0 THEN
--总装流水线
insert into pp_manuorder (FCCODE,MONUM,MOSTA,MOTYPE,MTCODE,PLCODE,MOQTYS,PSDATE,PFDATE,CRUSER,CRTIME,COMPQTYS)
VALUES ('8090',MONUM_1,'CREA','Z001',MTCODE_1,'SMAZZ01',TO_NUMBER(MOQTYS_1),to_char(to_date(PSDATE_1,'yyyy-MM-DD hh24:mi:ss'),'yyyy-MM-DD hh24:mi:ss'),to_char(to_date(PFDATE_1,'yyyy-MM-DD hh24:mi:ss'),'yyyy-MM-DD hh24:mi:ss'),'MESSAP',to_char(SYSDATE,'yyyy-MM-DD'),0);
--存储线
insert into pp_manuorder (FCCODE,MONUM,MOSTA,MOTYPE,MTCODE,PLCODE,MOQTYS,PSDATE,PFDATE,CRUSER,CRTIME,COMPQTYS)
VALUES ('8090',MONUM_1,'REL','ZZ00',MTCODE_1,'SMAZZ00',TO_NUMBER(MOQTYS_1),to_char(to_date(PSDATE_1,'yyyy-MM-DD hh24:mi:ss'),'yyyy-MM-DD hh24:mi:ss'),to_char(to_date(PFDATE_1,'yyyy-MM-DD hh24:mi:ss'),'yyyy-MM-DD hh24:mi:ss'),'MESSAP',to_char(SYSDATE,'yyyy-MM-DD'),0);
--检测线
insert into pp_manuorder
(FCCODE,MONUM,MOSTA,MOTYPE,MTCODE,PLCODE,MOQTYS,PSDATE,PFDATE,CRUSER,CRTIME,COMPQTYS)
VALUES ('8090',MONUM_1,'REL','ZZJC',MTCODE_1,'SMAZZ02',TO_NUMBER(MOQTYS_1),to_char(to_date(PSDATE_1,'yyyy-MM-DD hh24:mi:ss'),'yyyy-MM-DD hh24:mi:ss'),to_char(to_date(PFDATE_1,'yyyy-MM-DD hh24:mi:ss'),'yyyy-MM-DD hh24:mi:ss'),'MESSAP',to_char(SYSDATE,'yyyy-MM-DD'),0);
--入库线
insert into pp_manuorder
(FCCODE,MONUM,MOSTA,MOTYPE,MTCODE,PLCODE,MOQTYS,PSDATE,PFDATE,CRUSER,CRTIME,COMPQTYS)
VALUES ('8090',MONUM_1,'REL','ZZRK',MTCODE_1,'SMAZZ03',TO_NUMBER(MOQTYS_1),to_char(to_date(PSDATE_1,'yyyy-MM-DD hh24:mi:ss'),'yyyy-MM-DD hh24:mi:ss'),to_char(to_date(PFDATE_1,'yyyy-MM-DD hh24:mi:ss'),'yyyy-MM-DD hh24:mi:ss'),'MESSAP',to_char(SYSDATE,'yyyy-MM-DD'),0);
end if; -----------------------------------------------------------处理订单组件
select COUNT(*) into COMPENTNUM from pp_mocomponent s where s.cpcode = CPCODE_1 and s.MONUM = MONUM_1;
IF COMPENTNUM = 0 THEN --新订单插入组件
insert into pp_mocomponent(FCCODE,MONUM,CPCODE,CPQTYS,crtime,cruser)
VALUES('8090',MONUM_1,CPCODE_1,TO_NUMBER(CPQTYS_1*MOQTYS_1),to_char(SYSDATE,'yyyy-MM-DD hh24:mi:ss'),'MESSAP');
end if;
END;
MTCODE_1 IN VARCHAR2,--整车号
MOQTYS_1 IN VARCHAR2,
PSDATE_1 IN VARCHAR2,
PFDATE_1 IN VARCHAR2,
CPCODE_1 IN VARCHAR2,
CPQTYS_1 IN VARCHAR2
)IS
ISSAVE INT;
ISCCX INT;
ISRK INT;
ORDSTATE INT;
COMPENTNUM INT;
ISEDIT VARCHAR2(20);
BEGIN
-------------------------------------------------------------首先判断订单头数量
select count(*) into ISSAVE from PP_MANUORDER p where P.Monum = MONUM_1;
SELECT COUNT(*) INTO ORDSTATE FROM PP_MANUORDER P WHERE P.Monum = MONUM_1 AND P.PLCODE = 'SMAZZ01' AND P.MOSTA = 'REL';IF ISSAVE = 0 THEN
--总装流水线
insert into pp_manuorder (FCCODE,MONUM,MOSTA,MOTYPE,MTCODE,PLCODE,MOQTYS,PSDATE,PFDATE,CRUSER,CRTIME,COMPQTYS)
VALUES ('8090',MONUM_1,'CREA','Z001',MTCODE_1,'SMAZZ01',TO_NUMBER(MOQTYS_1),to_char(to_date(PSDATE_1,'yyyy-MM-DD hh24:mi:ss'),'yyyy-MM-DD hh24:mi:ss'),to_char(to_date(PFDATE_1,'yyyy-MM-DD hh24:mi:ss'),'yyyy-MM-DD hh24:mi:ss'),'MESSAP',to_char(SYSDATE,'yyyy-MM-DD'),0);
--存储线
insert into pp_manuorder (FCCODE,MONUM,MOSTA,MOTYPE,MTCODE,PLCODE,MOQTYS,PSDATE,PFDATE,CRUSER,CRTIME,COMPQTYS)
VALUES ('8090',MONUM_1,'REL','ZZ00',MTCODE_1,'SMAZZ00',TO_NUMBER(MOQTYS_1),to_char(to_date(PSDATE_1,'yyyy-MM-DD hh24:mi:ss'),'yyyy-MM-DD hh24:mi:ss'),to_char(to_date(PFDATE_1,'yyyy-MM-DD hh24:mi:ss'),'yyyy-MM-DD hh24:mi:ss'),'MESSAP',to_char(SYSDATE,'yyyy-MM-DD'),0);
--检测线
insert into pp_manuorder
(FCCODE,MONUM,MOSTA,MOTYPE,MTCODE,PLCODE,MOQTYS,PSDATE,PFDATE,CRUSER,CRTIME,COMPQTYS)
VALUES ('8090',MONUM_1,'REL','ZZJC',MTCODE_1,'SMAZZ02',TO_NUMBER(MOQTYS_1),to_char(to_date(PSDATE_1,'yyyy-MM-DD hh24:mi:ss'),'yyyy-MM-DD hh24:mi:ss'),to_char(to_date(PFDATE_1,'yyyy-MM-DD hh24:mi:ss'),'yyyy-MM-DD hh24:mi:ss'),'MESSAP',to_char(SYSDATE,'yyyy-MM-DD'),0);
--入库线
insert into pp_manuorder
(FCCODE,MONUM,MOSTA,MOTYPE,MTCODE,PLCODE,MOQTYS,PSDATE,PFDATE,CRUSER,CRTIME,COMPQTYS)
VALUES ('8090',MONUM_1,'REL','ZZRK',MTCODE_1,'SMAZZ03',TO_NUMBER(MOQTYS_1),to_char(to_date(PSDATE_1,'yyyy-MM-DD hh24:mi:ss'),'yyyy-MM-DD hh24:mi:ss'),to_char(to_date(PFDATE_1,'yyyy-MM-DD hh24:mi:ss'),'yyyy-MM-DD hh24:mi:ss'),'MESSAP',to_char(SYSDATE,'yyyy-MM-DD'),0);
end if; -----------------------------------------------------------处理订单组件
select COUNT(*) into COMPENTNUM from pp_mocomponent s where s.cpcode = CPCODE_1 and s.MONUM = MONUM_1;
IF COMPENTNUM = 0 THEN --新订单插入组件
insert into pp_mocomponent(FCCODE,MONUM,CPCODE,CPQTYS,crtime,cruser)
VALUES('8090',MONUM_1,CPCODE_1,TO_NUMBER(CPQTYS_1*MOQTYS_1),to_char(SYSDATE,'yyyy-MM-DD hh24:mi:ss'),'MESSAP');
end if;
END;
SELECT COUNT(*) FROM PP_MANUORDER P WHERE P.Monum = MONUM_1 AND P.PLCODE = 'SMAZZ01' AND P.MOSTA = 'REL';
select COUNT(*) from pp_mocomponent s where s.cpcode = CPCODE_1 and s.MONUM = MONUM_1;
这三条查询语句的执行计划发出来看看
每个订单组件有700多条数据,现在通过存储过程写入的时候测试的时候还可以,放在正式使用的机器上速度就很慢。20个订单的数据量就是20*700条这么多,数据插入速度很慢。是通过.net调用存储过程来实现的。
SELECT COUNT(*) FROM PP_MANUORDER P WHERE P.Monum = '11011260' AND P.PLCODE = 'SMAZZ01' AND P.MOSTA = 'REL';
select COUNT(*) from pp_mocomponent s where s.cpcode = 'SMAZZ01' and s.MONUM = '11011260';
出来的数据分别是4,1,712
,sum(case when P.PLCODE = 'SMAZZ01' AND P.MOSTA = 'REL' then 1 else 0 end) INTO ORDSTATE
from PP_MANUORDER p where P.Monum = MONUM_1;
扫描这个表一遍就可以了。
加并行 /*+ parallel(p,3)*/
如果select * 才比 select 字段 速度慢。