select * from V$VERSION;

create table Orders (ID, DateStart, DateEnd, Status, ShopId, WarehouseId) as select 1, date'2021-06-12', date'2021-06-12', 0, 1, 1 from dual

create table AmountIn (ID, Amount, GoodsId, OrderId) as select 1, 20, 1, 1 from dual

create table AmountOut (ID, Amount, GoodsId, WarehouseId) as select 1, 100, 1, 1 from dual

create or replace procedure setOrderStatus (id int, newstatus int) is WarehouseId int; GoodsId int; Amount int; begin update orders set status = newstatus where id = setOrderStatus.id returning WarehouseId into WarehouseId; delete from amountin a where a.orderid = setOrderStatus.id returning Amount, GoodsId into Amount, GoodsId; if (newstatus = 1) then update amountout set Amount = Amount - setOrderStatus.Amount where GoodsId = setOrderStatus.GoodsId and WarehouseId = setOrderStatus.WarehouseId; end if; end; /

select * from AmountIn union all select * from AmountOut

begin setOrderStatus (1, 1); end; /

select * from AmountOut union all select * from AmountIn

rollback;

create or replace trigger orders_status_update after update of status on orders for each row when (old.status = 0 and new.status in (1,2)) begin <<my>> declare GoodsId int; Amount int; begin delete from amountin a where a.orderid = :new.id returning Amount, GoodsId into my.Amount, my.GoodsId; if (:new.status = 1) then update amountout set Amount = Amount - my.Amount where GoodsId = my.GoodsId and WarehouseId = :new.WarehouseId; end if; end; end; /

update orders set status = 1 where id = 1

select * from AmountOut union all select * from AmountIn