-----取扩展字段函数——————————————————————————————
create or replace function BRField(end2guid varchar2,
name varchar2,
vclassduid varchar2) return varchar2 as
v_value varchar2(600);
s_field varchar2(200);
v_fieldname varchar2(20);
begin
select t.COLUMNNAME
into v_fieldname
from MA_FIELD t
where classguid = vclassduid
and fieldname = name;
s_field := 'select ' || v_fieldname ||
' from ma_foundation m,
product_rg n
where m.guid = n.foundationfk and m.guid ='''||end2guid||'''';
execute immediate s_field
into v_value;
return v_value;
end BRField;
---------------------------创建临时表----------------------------------------------------------------------------------------------------------------
begin
execute immediate 'drop table z_mid_report';
execute immediate 'create table z_test_t (col clob)';
execute immediate 'create table z_mid_report
(ID varchar2(128), REVISIONID varchar2(128), drawno varchar2(128), NAME varchar2(128),
Specification varchar2(128), Material varchar2(128), BOMStructure_Quantity varchar2(128), surface varchar2(128),
Weight varchar2(128), SourceType varchar2(128), StockUnit varchar2(128), OWNERUSER varchar2(128),
twod_end2_id varchar2(128), twod_end2_REVISIONID varchar2(128), twod_end2_filename varchar2(128), threed_end2_id varchar2(128),
threed_end2_REVISIONID varchar2(128), threed_end2_filename varchar2(128), brand varchar2(128), CLASSIFICATION varchar2(128),
status varchar2(128), end1_instantstring varchar2(128), rmarks varchar2(128)
)';
exception
when others then
null;
end;
--------------------------------------------------------------------------------------------------------------------------------------------------
declare
v_classguid char(32);
v_guid char(32);
v_bomname varchar2(128) := 'BOM';
cur_bomreport_row z_mid_report%rowtype;
cursor cur_bomreport(classguid varchar2,
tguid varchar2,
bomname varchar2) is
select f.id id,
f.revisionid revisionid,
f.name name,
c.quantity quantity,
f.owneruser owneruser,
f.classification classification,
f.oobsoletetime oobsoletetime,
f.effectivetime effectivetime,
f.status status,
d.name end1name,
BRField(tguid, 'drawno', classguid) drawno,
BRField(tguid, 'material', classguid) material,
BRField(tguid, 'surface', classguid) surface,
BRField(tguid, 'weight', classguid) weight,
BRField(tguid, 'sourcetype', classguid) sourcetype,
BRField(tguid, 'stockunit', classguid) stockunit,
BRField(tguid, 'brand', classguid) brand,
BRField(tguid, 'rmarks', classguid) rmarks
from bomview_r b
inner join bomstructure c
on c.viewfk = b.foundationfk
inner join ma_foundation d
on d.guid = b.end1
and d.obsoletetime is null
inner join ma_foundation e
on e.guid = b.foundationfk
inner join ma_foundation f
on (f.guid = c.end2 or
(f.masterfk = c.end2masterguid and f.latestrevision like '%m%'))
start with d.guid = tguid
and e.name = bomname
connect by d.guid = prior f.guid;
begin
select classguid, guid
into v_classguid, v_guid
from ma_foundation
where guid = 'B76FF3DBF4C041C79271E525EB74FE1F';
open cur_bomreport(v_classguid, v_guid, v_bomname);
loop
fetch cur_bomreport
into cur_bomreport_row;
if cur_bomreport%found then
insert into z_mid_report
(id,
revisionid,
name,
bomstructure_quantity,
owneruser,
classification,
status,
end1_instantstring,
drawno,
specification,
material,
surface,
weight,
sourcetype,
stockunit,
brand,
rmarks)
values
(cur_bomreport_row.id,
cur_bomreport_row.revisionid,
cur_bomreport_row.name,
cur_bomreport_row.quantity,
(select p.name
from sa_user p
where p.guid = cur_bomreport_row.owneruser),
cur_bomreport_row.classification,
cur_bomreport_row.status,
cur_bomreport_row.end1name,
cur_bomreport_row.drawno,
cur_bomreport_row.specification,
cur_bomreport_row.material,
cur_bomreport_row.surface,
cur_bomreport_row.weight,
cur_bomreport_row.sourcetype,
cur_bomreport_row.stockunit,
cur_bomreport_row.brand,
cur_bomreport_row.rmarks);
else
dbms_output.put_line('已取出所有数据!共' || cur_bomreport%rowcount || '条记录');
exit;
end if;
end loop;
end;