oracle业务关系sql,关于Oracle-SQL分析函数综合查询业务（物料BOM倒冲逻辑用量计算）...

set feedback off

set define off

prompt Creating T_ITEM_BOM...

create table T_ITEM_BOM

(

material_id   NUMBER(19),

material_code VARCHAR2(200),

material_name VARCHAR2(255),

remark        VARCHAR2(255),

bom_id        NUMBER(19) not null

)

tablespace USERS

pctfree 10

initrans 1

maxtrans 255

storage

(

initial 64K

next 1M

minextents 1

maxextents unlimited

);

alter table T_ITEM_BOM

add constraint PRIMARY_KEY primary key (BOM_ID)

deferrable

using index

tablespace USERS

pctfree 10

initrans 2

maxtrans 255

storage

(

initial 64K

next 1M

minextents 1

maxextents unlimited

);

prompt Creating T_ITEM_BOM_LINE...

create table T_ITEM_BOM_LINE

(

bom_line_id       NUMBER,

bom_id            NUMBER,

sub_material_id   NUMBER,

sub_material_code VARCHAR2(200),

sub_material_name VARCHAR2(200),

quota             NUMBER,

remark            VARCHAR2(255),

backflush_wh_id   NUMBER

)

tablespace USERS

pctfree 10

initrans 1

maxtrans 255

storage

(

initial 64K

next 8K

minextents 1

maxextents unlimited

);

prompt Creating T_ITEM_WH...

create table T_ITEM_WH

(

onhand_id   NUMBER not null,

material_id NUMBER,

lot         VARCHAR2(100),

qty         NUMBER,

wh_id       NUMBER,

remark      VARCHAR2(200)

)

tablespace USERS

pctfree 10

initrans 1

maxtrans 255

storage

(

initial 64K

next 1M

minextents 1

maxextents unlimited

);

alter table T_ITEM_WH

add constraint PRIMARY_ID primary key (ONHAND_ID)

using index

tablespace USERS

pctfree 10

initrans 2

maxtrans 255

storage

(

initial 64K

next 1M

minextents 1

maxextents unlimited

);

prompt Disabling triggers for T_ITEM_BOM...

alter table T_ITEM_BOM disable all triggers;

prompt Disabling triggers for T_ITEM_BOM_LINE...

alter table T_ITEM_BOM_LINE disable all triggers;

prompt Disabling triggers for T_ITEM_WH...

alter table T_ITEM_WH disable all triggers;

prompt Deleting T_ITEM_WH...

delete from T_ITEM_WH;

commit;

prompt Deleting T_ITEM_BOM_LINE...

delete from T_ITEM_BOM_LINE;

commit;

prompt Deleting T_ITEM_BOM...

delete from T_ITEM_BOM;

commit;

prompt Loading T_ITEM_BOM...

insert into T_ITEM_BOM (material_id, material_code, material_name, remark, bom_id)

values (11001, 'X', 'X总成', null, 10000);

insert into T_ITEM_BOM (material_id, material_code, material_name, remark, bom_id)

values (11002, 'Y', 'Y总成', null, 10001);

commit;

prompt 2 records loaded

prompt Loading T_ITEM_BOM_LINE...

insert into T_ITEM_BOM_LINE (bom_line_id, bom_id, sub_material_id, sub_material_code, sub_material_name, quota, remark, backflush_wh_id)

values (11100, 10000, 11007, 'A', 'A子件', 1, null, 10011);

insert into T_ITEM_BOM_LINE (bom_line_id, bom_id, sub_material_id, sub_material_code, sub_material_name, quota, remark, backflush_wh_id)

values (11101, 10000, 11008, 'B', 'B子件', .5, null, 10011);

insert into T_ITEM_BOM_LINE (bom_line_id, bom_id, sub_material_id, sub_material_code, sub_material_name, quota, remark, backflush_wh_id)

values (11102, 10000, 11009, 'C', 'C子件', 1, null, 10011);

insert into T_ITEM_BOM_LINE (bom_line_id, bom_id, sub_material_id, sub_material_code, sub_material_name, quota, remark, backflush_wh_id)

values (11105, 10001, 11009, 'C', 'C子件', 1, null, 10011);

insert into T_ITEM_BOM_LINE (bom_line_id, bom_id, sub_material_id, sub_material_code, sub_material_name, quota, remark, backflush_wh_id)

values (11104, 10001, 11008, 'B', 'B子件', .5, null, 10011);

insert into T_ITEM_BOM_LINE (bom_line_id, bom_id, sub_material_id, sub_material_code, sub_material_name, quota, remark, backflush_wh_id)

values (11103, 10001, 11007, 'A', 'A子件', 1, null, 10011);

commit;

prompt 6 records loaded

prompt Loading T_ITEM_WH...

insert into T_ITEM_WH (onhand_id, material_id, lot, qty, wh_id, remark)

values (10000, 11007, 'P1', 500, 10011, null);

insert into T_ITEM_WH (onhand_id, material_id, lot, qty, wh_id, remark)

values (10007, 11009, 'P2', 100, 10011, null);

insert into T_ITEM_WH (onhand_id, material_id, lot, qty, wh_id, remark)

values (10006, 11009, 'P2', 500, 10011, null);

insert into T_ITEM_WH (onhand_id, material_id, lot, qty, wh_id, remark)

values (10005, 11008, 'P3', 900, 10011, null);

insert into T_ITEM_WH (onhand_id, material_id, lot, qty, wh_id, remark)

values (10004, 11008, 'P2', 100, 10011, null);

insert into T_ITEM_WH (onhand_id, material_id, lot, qty, wh_id, remark)

values (10003, 11008, 'P1', 200, 10011, null);

insert into T_ITEM_WH (onhand_id, material_id, lot, qty, wh_id, remark)

values (10002, 11007, 'P3', 900, 10011, null);

insert into T_ITEM_WH (onhand_id, material_id, lot, qty, wh_id, remark)

values (10001, 11007, 'P2', 400, 10011, null);

commit;

prompt 8 records loaded

prompt Enabling triggers for T_ITEM_BOM...

alter table T_ITEM_BOM enable all triggers;

prompt Enabling triggers for T_ITEM_BOM_LINE...

alter table T_ITEM_BOM_LINE enable all triggers;

prompt Enabling triggers for T_ITEM_WH...

alter table T_ITEM_WH enable all triggers;

set feedback on

set define on

prompt Done.