制作三个表 库存表,商品表,备份表库存表字段为:id,商品id,数量,实际库存量,库存日期 商品表字段: id,商品名称,条码,有效标志 备份表字段: 库存id,商品id,数量
书写两个存储过程:1.自动生成1万个商品 ,自动生成与之匹配1万库存2 自动统计当前有效商品库存,输出到备份表 有效标志应该是随机的吧,用循环的话存储过程该怎么写呢?--建表
create table inventory_tab (
id number ,
goods_id varchar2(20),
goods_num number ,
real_num number ,
inven_date date ,
primary key (id) );create table goods_tab (
goods_id varchar2(20),
goods_desc varchar2(80),
barcode varchar2(80),
flag varchar2(10),
primary key (goods_id) );create table goods_backup_tab (
inven_id number ,
goods_id varchar2(20),
inven_num number ,
primary key (inven_id,goods_id));--创建序列
create sequence your_seq
nocycle
maxvalue 9999999999
start with 1;--自增ID
create or replace trigger tri_inven
before insert on inventory_tab
for each row
declare
next_id number;
begin
select your_seq.nextval into next_id from dual;
:new.id := next_id;
end;
/--自动生成1万个商品 ,自动生成与之匹配1万库存
create or replace procedure pro_auto_goods
as
i number ;
is_flag varchar2(10);
begin
for i in 1..10000 loop
if (mod(i,3) = 0 ) then
is_flag ='Y';
else
is_flag ='N';
end if ;
insert into goods_tab (
goods_id ,
goods_desc,
barcode ,
flag )
values(to_char(i)||'xx',
'yyyy'||to_char(i),
'zzzzzzz' ,
is_flag );
insert into inventory_tab(
goods_id ,
goods_num ,
real_num ,
inven_date )
values(to_char(i)||'xx',
I,
round(i/10),
sysdate );
end loop;
commit;
end ;
/
--自动统计当前有效商品库存,输出到备份表
create or replace procedure pro_real_inven
as
begin
insert into goods_backup_tab(
inven_id ,
goods_id ,
inven_num )
select a.id ,
a.goods_id ,
a.real_num
from inventory_tab a,goods_tab b
where a.goods_id = b.goods_id
and b.flag = 'Y' ;
commit ;
end ;
/
书写两个存储过程:1.自动生成1万个商品 ,自动生成与之匹配1万库存2 自动统计当前有效商品库存,输出到备份表 有效标志应该是随机的吧,用循环的话存储过程该怎么写呢?--建表
create table inventory_tab (
id number ,
goods_id varchar2(20),
goods_num number ,
real_num number ,
inven_date date ,
primary key (id) );create table goods_tab (
goods_id varchar2(20),
goods_desc varchar2(80),
barcode varchar2(80),
flag varchar2(10),
primary key (goods_id) );create table goods_backup_tab (
inven_id number ,
goods_id varchar2(20),
inven_num number ,
primary key (inven_id,goods_id));--创建序列
create sequence your_seq
nocycle
maxvalue 9999999999
start with 1;--自增ID
create or replace trigger tri_inven
before insert on inventory_tab
for each row
declare
next_id number;
begin
select your_seq.nextval into next_id from dual;
:new.id := next_id;
end;
/--自动生成1万个商品 ,自动生成与之匹配1万库存
create or replace procedure pro_auto_goods
as
i number ;
is_flag varchar2(10);
begin
for i in 1..10000 loop
if (mod(i,3) = 0 ) then
is_flag ='Y';
else
is_flag ='N';
end if ;
insert into goods_tab (
goods_id ,
goods_desc,
barcode ,
flag )
values(to_char(i)||'xx',
'yyyy'||to_char(i),
'zzzzzzz' ,
is_flag );
insert into inventory_tab(
goods_id ,
goods_num ,
real_num ,
inven_date )
values(to_char(i)||'xx',
I,
round(i/10),
sysdate );
end loop;
commit;
end ;
/
--自动统计当前有效商品库存,输出到备份表
create or replace procedure pro_real_inven
as
begin
insert into goods_backup_tab(
inven_id ,
goods_id ,
inven_num )
select a.id ,
a.goods_id ,
a.real_num
from inventory_tab a,goods_tab b
where a.goods_id = b.goods_id
and b.flag = 'Y' ;
commit ;
end ;
/
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货