由于公司最近在上PLM系统，要将T100现有的BOM数据全部导出并导入到PLM系统中
但是数据量有点庞大了，在数据库sum了下存bom的表，10w+行！！！
然鹅，最大的问题是的T100的BOM存到数据表中的格式是主件+元件的方式，完全看不到层次，跟PLM实施给的导入模版格式完全不同，要求层次关系如0,0.1，0.1.1.......对应不同料号，如下图
这个靠人手工去整理明显不太现实，只能现学现写搞出了这么个存储过程来批量处理
代码如下：
-- 插表函数
create or replace PROCEDURE ins_temp(l_cj in varchar,
l_liaohao in varchar,
l_bmba011 in number,
l_bmba012 in number,
l_yjlx in varchar,
l_bmba013 in varchar,
l_bmba031 in varchar,
l_bmba030 in varchar,
l_bmba029 in varchar,
l_bmbb011 in number,
l_bmbb009 in number,
l_bmbb010 in number,
l_riqi in timestamp) as
BEGIN
INSERT INTO temp_libinbin3
(cj,
liaohao,
bmba011,
bmba012,
yjlx,
bmba013,
bmba031,
bmba030,
bmba029,
bmbb011,
bmbb009,
bmbb010,
riqi)
values
(l_cj,
l_liaohao,
l_bmba011,
l_bmba012,
l_yjlx,
l_bmba013,
l_bmba031,
l_bmba030,
l_bmba029,
l_bmbb011,
l_bmbb009,
l_bmbb010,
l_riqi);
--('0','test',0,0,'yjlx','bmba013','N','N','bmba029',0,0,0,sysdate)
commit;
END;
--层次 物料编码 bom组成用量 bom主件底数 元件类型 必要 客供料 倒扣料 损耗率型态 变动损耗率 起始生产数量 截止生产数量
--物料名称 物料分类 规格 料件类别 生命周期状态 料件大类
--------------------------------------------------------------------------------------------
--检查是否有回料
create or replace PROCEDURE bmaa_sum(b_bmaa001 in varchar,l_sum OUT NUMBER)
IS
l_num number;
BEGIN
SELECT COALESCE(sum(1),0) into l_num FROM bmaa_t WHERE bmaa001 = b_bmaa001 AND bmaasite = 'YTOP';
IF l_num >= 1 THEN
select COALESCE(sum(1),0) into l_sum from bmac_t where bmac001 = b_bmaa001 and bmacsite = 'YTOP' AND bmac008 is null;
END IF;
END;
---------------------------------------------------------------------------------------------
--损耗率取值
create or replace PROCEDURE get_bmbb(g_bmba001 in varchar,g_bmba003 in varchar,g_bmbb011 out number,g_bmbb009 out number,g_bmbb010 OUT NUMBER)
is
l_num number;
begin
select nvl(sum(1),0) INTO l_num
from bmbb_t
where bmbbsite = 'YTOP'
and bmbb001 = g_bmba001
and bmbb003 = g_bmba003
and rownum = 1
order by bmbb009;
IF l_num >=1 THEN
select bmbb011,bmbb009,bmbb010 INTO g_bmbb011,g_bmbb009,g_bmbb010
from bmbb_t
where bmbbsite = 'YTOP'
and bmbb001 = g_bmba001
and bmbb003 = g_bmba003
and rownum = 1
order by bmbb009;
ELSE
g_bmbb011 := null;
g_bmbb009 := null;
g_bmbb010 := null;
END IF;
end;
---------------------------------------------------------------------------------------------
--main
create or replace PROCEDURE bom_main(l_bmaa001 in varchar)
AS
l_n number;
l_n1 number;
l_n2 number;
l_n3 number;
l_n4 number;
l_n5 number;
l_sum number;
l_num number;
m_bmbb011 number;
m_bmbb009 number;
m_bmbb010 number;
BEGIN
l_n := 0;
SELECT COALESCE(sum(1),0) INTO l_n FROM bmba_t WHERE bmba003 = l_bmaa001 AND bmbasite = 'YTOP';
IF l_n < 1 THEN
--插入0阶
--l_n := l_n+1;
--ins_temp(l_cj => '0',l_liaohao => l_bmaa001);
ins_temp(l_cj => '0',l_liaohao => l_bmaa001,l_bmba011 => null,l_bmba012=>null,l_yjlx=>null,
l_bmba013 => null,l_bmba031 =>null,l_bmba030 =>null,l_bmba029 =>null,l_bmbb011=>null,l_bmbb009=>null,l_bmbb010=>null,l_riqi =>systimestamp);
--1阶
l_n := 0;
for v_emp in (SELECT bmba001,bmba003,bmba011,bmba012,bmba013,bmba031,bmba030,bmba029 FROM bmba_t WHERE bmba001 = l_bmaa001 AND bmbasite = 'YTOP' and bmba006 is null) loop
l_n := l_n+1;
--ins_temp(l_cj => '0.'||l_n,l_zj => v_emp.bmba003);
--获取损耗数据
get_bmbb(g_bmba001=>v_emp.bmba001,g_bmba003 => v_emp.bmba003,g_bmbb011 => m_bmbb011,g_bmbb009 => m_bmbb009,g_bmbb010 => m_bmbb010);
ins_temp(l_cj => '0.'||l_n,l_liaohao => v_emp.bmba003,l_bmba011 => v_emp.bmba011,l_bmba012=>v_emp.bmba012,l_yjlx=>'BOM元件',
l_bmba013 => v_emp.bmba013,l_bmba031 =>v_emp.bmba031,l_bmba030 =>v_emp.bmba030,l_bmba029 =>v_emp.bmba029,l_bmbb011=>m_bmbb011,l_bmbb009=>m_bmbb009,l_bmbb010=>m_bmbb010,l_riqi =>systimestamp);
l_n1 := 0;
--2阶
SELECT COALESCE(sum(1),0) into l_n1 FROM bmaa_t WHERE bmaa001 = v_emp.bmba003 AND bmaasite = 'YTOP';
IF l_n1 = 1 THEN
l_n1 := 0;
for v_emp1 in (SELECT bmba001,bmba003,bmba011,bmba012,bmba013,bmba031,bmba030,bmba029 FROM bmba_t WHERE bmba001 = v_emp.bmba003 AND bmbasite = 'YTOP'and bmba006 is null) loop
l_n1 := l_n1 +1;
--ins_temp(l_cj => '0.'||l_n||'.'||l_n1,l_zj => v_emp1.bmba003);
--获取损耗数据
get_bmbb(g_bmba001=>v_emp1.bmba001,g_bmba003 => v_emp1.bmba003,g_bmbb011 => m_bmbb011,g_bmbb009 => m_bmbb009,g_bmbb010 => m_bmbb010);
ins_temp(l_cj => '0.'||l_n||'.'||l_n1,l_liaohao => v_emp1.bmba003,l_bmba011 => v_emp1.bmba011,l_bmba012=>v_emp1.bmba012,l_yjlx=>'BOM元件',
l_bmba013 => v_emp1.bmba013,l_bmba031 =>v_emp1.bmba031,l_bmba030 =>v_emp1.bmba030,l_bmba029 =>v_emp1.bmba029,l_bmbb011=>m_bmbb011,
l_bmbb009=>m_bmbb009,l_bmbb010=>m_bmbb010,l_riqi =>systimestamp);
--3阶
l_n2 := 0;
SELECT COALESCE(sum(1),0) into l_n2 FROM bmaa_t WHERE bmaa001 = v_emp1.bmba003 AND bmaasite = 'YTOP';
IF l_n2 = 1 THEN
l_n2 := 0;
for v_emp2 in (SELECT bmba001,bmba003,bmba011,bmba012,bmba013,bmba031,bmba030,bmba029 FROM bmba_t WHERE bmba001 = v_emp1.bmba003 AND bmbasite = 'YTOP'and bmba006 is null) loop
l_n2 := l_n2 +1;
--ins_temp(l_cj => '0.'||l_n||'.'||l_n1||'.'||l_n2,l_zj => v_emp2.bmba003);
--获取损耗数据
get_bmbb(g_bmba001=>v_emp2.bmba001,g_bmba003 => v_emp2.bmba003,g_bmbb011 => m_bmbb011,g_bmbb009 => m_bmbb009,g_bmbb010 => m_bmbb010);
ins_temp(l_cj => '0.'||l_n||'.'||l_n1||'.'||l_n2,l_liaohao => v_emp2.bmba003,l_bmba011 => v_emp2.bmba011,l_bmba012=>v_emp2.bmba012,l_yjlx=>'BOM元件',
l_bmba013 => v_emp2.bmba013,l_bmba031 =>v_emp2.bmba031,l_bmba030 =>v_emp2.bmba030,l_bmba029 =>v_emp2.bmba029,l_bmbb011=>m_bmbb011,
l_bmbb009=>m_bmbb009,l_bmbb010=>m_bmbb010,l_riqi =>systimestamp);
--4阶
l_n3 := 0;
SELECT COALESCE(sum(1),0) into l_n3 FROM bmaa_t WHERE bmaa001 = v_emp2.bmba003 AND bmaasite = 'YTOP';
IF l_n3 = 1 THEN
l_n3 := 0;
--4元件插表
for v_emp3 in (SELECT bmba001,bmba003,bmba011,bmba012,bmba013,bmba031,bmba030,bmba029 FROM bmba_t WHERE bmba001 = v_emp2.bmba003 AND bmbasite = 'YTOP'and bmba006 is null) loop
l_n3 := l_n3 +1;
--ins_temp(l_cj => '0.'||l_n||'.'||l_n1||'.'||l_n2||'.'||l_n3,l_zj => v_emp3.bmba003);
--获取损耗数据
get_bmbb(g_bmba001=>v_emp3.bmba001,g_bmba003 => v_emp3.bmba003,g_bmbb011 => m_bmbb011,g_bmbb009 => m_bmbb009,g_bmbb010 => m_bmbb010);
ins_temp(l_cj => '0.'||l_n||'.'||l_n1||'.'||l_n2||'.'||l_n3,l_liaohao => v_emp3.bmba003,l_bmba011 => v_emp3.bmba011,l_bmba012=>v_emp3.bmba012,l_yjlx=>'BOM元件',
l_bmba013 => v_emp3.bmba013,l_bmba031 =>v_emp3.bmba031,l_bmba030 =>v_emp3.bmba030,l_bmba029 =>v_emp3.bmba029,l_bmbb011=>m_bmbb011,
l_bmbb009=>m_bmbb009,l_bmbb010=>m_bmbb010,l_riqi =>systimestamp);
--5阶
l_n4 := 0;
SELECT COALESCE(sum(1),0) into l_n4 FROM bmaa_t WHERE bmaa001 = v_emp3.bmba003 AND bmaasite = 'YTOP';
IF l_n4 = 1 THEN
l_n4 := 0;
--5阶元件插表
for v_emp4 in (SELECT bmba001,bmba003,bmba011,bmba012,bmba013,bmba031,bmba030,bmba029 FROM bmba_t WHERE bmba001 = v_emp3.bmba003 AND bmbasite = 'YTOP'and bmba006 is null) loop
l_n4 := l_n4 +1;
--ins_temp(l_cj => '0.'||l_n||'.'||l_n1||'.'||l_n2||'.'||l_n3||'.'||l_n4,l_zj => v_emp4.bmba003);
--获取损耗数据
get_bmbb(g_bmba001=>v_emp4.bmba001,g_bmba003 => v_emp4.bmba003,g_bmbb011 => m_bmbb011,g_bmbb009 => m_bmbb009,g_bmbb010 => m_bmbb010);
ins_temp(l_cj => '0.'||l_n||'.'||l_n1||'.'||l_n2||'.'||l_n3||'.'||l_n4,l_liaohao => v_emp4.bmba003,l_bmba011 => v_emp4.bmba011,l_bmba012=>v_emp4.bmba012,l_yjlx=>'BOM元件',
l_bmba013 => v_emp4.bmba013,l_bmba031 =>v_emp4.bmba031,l_bmba030 =>v_emp4.bmba030,l_bmba029 =>v_emp4.bmba029,l_bmbb011=>m_bmbb011,
l_bmbb009=>m_bmbb009,l_bmbb010=>m_bmbb010,l_riqi =>systimestamp);
--往下阶在检核一次，还有下阶的话插一行提示
l_n5 := 0;
SELECT COALESCE(sum(1),0) into l_n5 FROM bmaa_t WHERE bmaa001 = v_emp4.bmba003 AND bmaasite = 'YTOP';
IF l_n5 >= 1 THEN
ins_temp(l_cj => '0.'||l_n||'.'||l_n1||'.'||l_n2||'.'||l_n3||'.'||l_n4||'.'||'非尾阶！手动处理',l_liaohao => v_emp4.bmba003,l_bmba011 => null,l_bmba012=>null,l_yjlx=>null,
l_bmba013 => null,l_bmba031 =>null,l_bmba030 =>null,l_bmba029 =>null,l_bmbb011=>null,l_bmbb009=>null,l_bmbb010=>null,l_riqi =>systimestamp);
END IF;
END loop;
--5阶回料插表
bmaa_sum(b_bmaa001 => v_emp3.bmba003,l_sum => l_sum);
IF l_sum >= 1 THEN
for v_emp4a in (select bmac003,bmac005,bmac006 from bmac_t where bmac001 = v_emp3.bmba003 and bmacsite = 'YTOP' AND bmac008 is null) loop
l_n4 := l_n4 +1;
--ins_temp(l_cj => '0.'||l_n||'.'||l_n1||'.'||l_n2||'.'||l_n3||'.'||l_n4,l_zj => v_emp4a.bmac003);
ins_temp(l_cj => '0.'||l_n||'.'||l_n1||'.'||l_n2||'.'||l_n3||'.'||l_n4,l_liaohao => v_emp4a.bmac003,l_bmba011 => v_emp4a.bmac005,l_bmba012=>v_emp4a.bmac006,l_yjlx=>'副产品',
l_bmba013 =>null,l_bmba031 =>null,l_bmba030 =>null,l_bmba029 =>null,l_bmbb011=>null,l_bmbb009=>null,l_bmbb010=>null,l_riqi =>systimestamp);
END loop;
end if;
END IF;
END loop;
--4回料插表
bmaa_sum(b_bmaa001 => v_emp2.bmba003,l_sum => l_sum);
IF l_sum >= 1 THEN
for v_emp3a in (select bmac003,bmac005,bmac006 from bmac_t where bmac001 = v_emp2.bmba003 and bmacsite = 'YTOP' AND bmac008 is null) loop
l_n3 := l_n3 +1;
--ins_temp(l_cj => '0.'||l_n||'.'||l_n1||'.'||l_n2||'.'||l_n3,l_zj => v_emp3a.bmac003);
ins_temp(l_cj => '0.'||l_n||'.'||l_n1||'.'||l_n2||'.'||l_n3,l_liaohao => v_emp3a.bmac003,l_bmba011 => v_emp3a.bmac005,l_bmba012=>v_emp3a.bmac006,l_yjlx=>'副产品',
l_bmba013 =>null,l_bmba031 =>null,l_bmba030 =>null,l_bmba029 =>null,l_bmbb011=>null,l_bmbb009=>null,l_bmbb010=>null,l_riqi =>systimestamp);
END loop;
end if;
END IF;
END loop;
--3回料插表
bmaa_sum(b_bmaa001 => v_emp1.bmba003,l_sum => l_sum);
IF l_sum >= 1 THEN
for v_emp2a in (select bmac003,bmac005,bmac006 from bmac_t where bmac001 = v_emp1.bmba003 and bmacsite = 'YTOP' AND bmac008 is null) loop
l_n2 := l_n2 +1;
--ins_temp(l_cj => '0.'||l_n||'.'||l_n1||'.'||l_n2,l_zj => v_emp2a.bmac003);
ins_temp(l_cj => '0.'||l_n||'.'||l_n1||'.'||l_n2,l_liaohao => v_emp2a.bmac003,l_bmba011 => v_emp2a.bmac005,l_bmba012=>v_emp2a.bmac006,l_yjlx=>'副产品',
l_bmba013 =>null,l_bmba031 =>null,l_bmba030 =>null,l_bmba029 =>null,l_bmbb011=>null,l_bmbb009=>null,l_bmbb010=>null,l_riqi =>systimestamp);
END loop;
end if;
END IF;
END loop;
--2回料插表
bmaa_sum(b_bmaa001 => v_emp.bmba003,l_sum => l_sum);
IF l_sum >= 1 THEN
for v_emp1a in (select bmac003,bmac005,bmac006 from bmac_t where bmac001 = v_emp.bmba003 and bmacsite = 'YTOP' AND bmac008 is null) loop
l_n1 := l_n1 +1;
--ins_temp(l_cj => '0.'||l_n||'.'||l_n1,l_zj => v_emp1a.bmac003);
ins_temp(l_cj => '0.'||l_n||'.'||l_n1,l_liaohao => v_emp1a.bmac003,l_bmba011 => v_emp1a.bmac005,l_bmba012=>v_emp1a.bmac006,l_yjlx=>'副产品',
l_bmba013 =>null,l_bmba031 =>null,l_bmba030 =>null,l_bmba029 =>null,l_bmbb011=>null,l_bmbb009=>null,l_bmbb010=>null,l_riqi =>systimestamp);
END loop;
end if;
END IF;
end loop;
--1回料插表
bmaa_sum(b_bmaa001 => l_bmaa001,l_sum => l_sum);
IF l_sum >= 1 THEN
for v_emp0a in (select bmac003,bmac005,bmac006 from bmac_t where bmac001 = l_bmaa001 and bmacsite = 'YTOP' AND bmac008 is null) loop
l_n := l_n +1;
--ins_temp(l_cj => '0.'||l_n,l_zj => v_emp0a.bmac003);
ins_temp(l_cj => '0.'||l_n,l_liaohao => v_emp0a.bmac003,l_bmba011 => v_emp0a.bmac005,l_bmba012=>v_emp0a.bmac006,l_yjlx=>'副产品',
l_bmba013 =>null,l_bmba031 =>null,l_bmba030 =>null,l_bmba029 =>null,l_bmbb011=>null,l_bmbb009=>null,l_bmbb010=>null,l_riqi =>systimestamp);
END loop;
end if;
END IF;
-- commit;
END;
-----------------------------------------------------------------------------------------------
--执行
declare
l_num number;
BEGIN
FOR v_run in (select bmaa001 from bmaa_t where bmaasite = 'YTOP' and bmaa001 NOT LIKE 'JXT%') loop
select COALESCE(sum(1),0) INTO l_num from imaa_t where imaaent = 88 and imaa001 = v_run.bmaa001;
IF l_num >= 1 THEN
bom_main(l_bmaa001 => v_run.bmaa001);
END IF;
END loop;
END;
--------------------------------------------------------------------------------------------测试单个BOM
begin
-- bom_main(l_bmaa001 => '206-6FE21LHA02');
bom_main(l_bmaa001 => '106100050006');
end;
------------------------------------
select cj as 层级,imaa009 as 产品分类,liaohao as 料号,imaal003 as 品名,imaal004 as 规格,imaa006 as 基本单位,imaa004 as 料件类别,
imaa010 as 生命周期状态,imaaud005 as 料件大类,bmba011 as 组成用量,bmba012 as 主件底数,
yjlx as 元件类型,bmba013 as 主要,bmba031 as 客供料,bmba030 as 倒扣料,bmba029 as 损耗率型态,bmbb011 as 变动损耗率,
bmbb009 as 起始生产数量,bmbb010 as 截止生产数量,riqi as 插表时间
from temp_libinbin3
left join imaal_t on imaalent = 88 and imaal001 = liaohao and imaal002 = 'zh_CN'
left join imaa_t on imaaent = 88 and imaa001 = liaohao
order by riqi
--select * from temp_libinbin3 order by riqi
--清空临时表
call cle_temp();
----------------------------------------
--清除表数据
create or replace PROCEDURE cle_temp
is
begin
delete from temp_libinbin3;
commit;
end;
------------------------------------------
drop table temp_libinbin3
create global temporary table temp_libinbin3
(cj varchar2(40),liaohao varchar2(40),
bmba011 number(20,6),bmba012 number(20,6),yjlx varchar(20),
bmba013 varchar2(10),bmba031 varchar2(1),bmba030 varchar2(1),bmba029 varchar2(10),
bmbb011 number(20,6),bmbb009 number(20,6),bmbb010 number(20,6)
)
on commit preserve rows;
create global temporary table temp_libinbin3
(cj varchar2(40),liaohao varchar2(40), date
)
on commit preserve rows;
ALTER TABLE temp_libinbin3 ADD (riqi DATE)
ALTER TABLE temp_libinbin3 modify (riqi timestamp)
select systimestamp from dual
由于不会写递归- -！，代码比较冗余，只能处理6阶左右的BOM
效果实现：
扫一扫
378
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
