17,086
社区成员
发帖
与我相关
我的任务
分享
SQL> exec p_goods_dtl(to_date('2014/10/10 15:40:00','yyyy/mm/dd hh24:mi:ss'),to_date('2014/10/10 16:40:00','yyyy/mm/dd hh24:mi:ss'));
PL/SQL procedure successfully completed
create or replace procedure p_goods_dtl(bgntm date,endtm date)
is
i number;
begin
for v_goods in (select * from t_goods where t_time between bgntm and endtm) loop
for i in 1..v_goods.n_num loop
insert into t_goods_dtl values (v_goods.c_goods,v_goods.c_mechine,v_goods.t_time,v_goods.c_type,1);
end loop;
end loop;
commit;
end;
n_num是你货物表的数量字段,需要根据你的表结构进行调整
附上我测试用的建表语句
create table t_goods (
c_goods char(1),
c_mechine char(2),
t_time date,
c_type char(1),
n_num number
);
create table t_goods_dtl (
c_goods char(1),
c_mechine char(2),
t_time date,
c_type char(1),
n_num number
);
declare
i number;
begin
for v_goods in (select * from t_goods) loop
for i in 1..v_goods.n_num loop
insert into t_goods_dtl values (v_goods.c_goods,v_goods.c_mechine,v_goods.t_time,v_goods.c_type,1);
end loop;
end loop;
commit;
end;