clear markdown compare help best fiddles feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3601543 fiddles created (47960 in the last week).

select * from V$VERSION;
BANNER
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
 hidden batch(es)


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
1 rows affected
 hidden batch(es)


create table AmountIn (ID, Amount, GoodsId, OrderId) as select 1, 20, 1, 1 from dual
1 rows affected
 hidden batch(es)


create table AmountOut (ID, Amount, GoodsId, WarehouseId) as select 1, 100, 1, 1 from dual
1 rows affected
 hidden batch(es)


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; /
 hidden batch(es)


select * from AmountIn union all select * from AmountOut
ID AMOUNT GOODSID ORDERID
1 20 1 1
1 100 1 1
 hidden batch(es)


begin setOrderStatus (1, 1); end; /
1 rows affected
 hidden batch(es)


select * from AmountOut union all select * from AmountIn
ID AMOUNT GOODSID WAREHOUSEID
1 80 1 1
 hidden batch(es)


rollback;
 hidden batch(es)


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; /
 hidden batch(es)


update orders set status = 1 where id = 1
1 rows affected
 hidden batch(es)


select * from AmountOut union all select * from AmountIn
ID AMOUNT GOODSID WAREHOUSEID
1 80 1 1
 hidden batch(es)