create table system.orders //商品订单表
(ID number(10) primary key,
customer_id number(10) not null,
product_id number(10) not null,
order_quantity number(5),
order_date char(30),
state char(15) CHECK(state IN ('untreated','treated')),
FOREIGN KEY(customer_id) references system.customer On delete Cascade,
FOREIGN KEY(product_id) references system.product On delete Cascade);
我创建的销售统计表和存储过程
销售统计报表
create table system.sell
(product_id number(10) not null,
product_name char(30),
month number(2) default 0,
quantity number(30) default 0,
total_value number(10),
state char(15) CHECK(state IN (‘settled')),
primary key(product_id,month),
FOREIGN KEY(product_ID) references system.product On delete Cascade);
//销售统计存储过程
create or replace procedure SellCount
(product_id in number,quantity out number)
as
begin
select count(order_quantity)
into quantity from system.ORDERS
where product_id=product_id;
end SellCount;
我想请问一下如何通过oraclejob 调用存储器过程让其在每个月末自动统计商品销售的数量,急用啊,谢谢!
回复 更多评论