Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
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


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

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

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

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


ID
AMOUNT
GOODSID
ORDERID


1
20
1
1

1
100
1
1


begin setOrderStatus (1, 1); end;
/
1 rows affected

select * from AmountOut union all
select * from AmountIn


ID
AMOUNT
GOODSID
WAREHOUSEID


1
80
1
1


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
1 rows affected

select * from AmountOut union all
select * from AmountIn


ID
AMOUNT
GOODSID
WAREHOUSEID


1
80
1
1