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 調用存儲器過程讓其在每個月末自動統計商品銷售的數量，急用啊，謝謝！
回復 更多評論