写一个存储过程:
主要实现对发过货的 商品进行数量更新
下面是几个表 :
商品表
我用游标了,感觉不对,没想到太好的思路
解决代码:
主要实现对发过货的 商品进行数量更新
下面是几个表 :
商品表
CODE:
create table t_ItemInfo(
autoId int primary key,
classId int not null,
nclassId int not null,
amount int ,--商品数量
title varchar2(100) not null,
content varchar2(200) not null,
price float not null,
remark varchar2(200),
filePath varchar2(200),
fileName varchar2(200),
addtime date
);
订单表:
CODE:
create table t_OrderInfo(
orderNo int,
dAID int,
oBalance int,--订单总价//
ordertime date,
payType varchar2(50),--付款类型:货到付款、银行转帐
sendType varchar2(50), --发货方式
hasapproved int, --订单可否多几种状态“等待处理、已发货、已结束
result varchar(200),
approvedUserId int,
approvedDate date,
constraint PK_OrderInfo_orderNo primary key(orderNo),
CONSTRAINT FK_OrderInfo_userID FOREIGN KEY (userID) REFERENCES t_UserInfo ( autoId)
);
订单详细表:
CODE:
create table t_orderView(
autoId int,
orderNo int not null,
itemID int not null,
itemName varchar2(100) not null,
itemCount int,
price float not null,
constraint PK_orderView_orderDId primary key(autoId),
CONSTRAINT FK_orderView_orderNo FOREIGN KEY (orderNo)
REFERENCES t_OrderInfo (orderNo)
);
主要是这几个表,因为订单详细表里面的商品数量很多,所以要更新很多条商品表里的数量,大家有什么高见~谢谢大家
我用游标了,感觉不对,没想到太好的思路
解决代码:
CODE:
create or replace procedure Order_processed
(p_oid in t_OrderInfo.orderNo%TYPE )
as
CURSOR c1 IS
SELECT itemId,itemCount
FROM t_orderView
WHERE rderNo = p_oid;
v_itemId int;
v_amount int;
v_itemCount int;
begin
update t_OrderInfo set hasapproved=1, approvedDate=sysdate where rderNo=p_oid;
--更新OrderTab中的字段
FOR r1 IN c1 LOOP
v_itemId :=r1.itemId;
v_itemCount := r1.itemCount;
SELECT amount into v_amount from t_iteminfo where t_iteminfo.autoid = r1.itemid;
v_amount := v_amount-v_itemCount;
UPDATE t_iteminfo SET amount=v_amount WHERE t_iteminfo.autoid = r1.itemid;
END LOOP;
COMMIT;
end Order_processed;