create table 在庫(
SEQ number(1),
区分 char(4),
個数 number(1),
入荷単価 number(3));
insert into 在庫 values(1,'入庫', 9,100);
insert into 在庫 values(2,'入庫', 5,110);
insert into 在庫 values(3,'出庫',-5,null);
insert into 在庫 values(4,'返品',-6,null);
insert into 在庫 values(5,'入庫', 5,120);
insert into 在庫 values(6,'返納', 3,null);
insert into 在庫 values(7,'入庫', 5,100);
commit;
--■■■表関数を使わない方法■■■
with WorkView as (
select SEQ,区分,個数,入荷単価,在庫数,正数計,
Lag(正数計,1,0) over(order by SEQ) as Lag正数計,負数計
from (select SEQ,区分,個数,
decode(区分,'返納',Last_Value(入荷単価 ignore nulls)
over(order by SEQ),入荷単価) as 入荷単価,
sum(個数) over(order by SEQ) as 在庫数,
sum(decode(sign(個数), 1, 個数,0)) over(order by SEQ) as 正数計,
sum(decode(sign(個数),-1,-個数,0)) over(order by SEQ) as 負数計
from 在庫))
select SEQ,区分,個数,入荷単価,在庫数,
((select sum(b.個数*b.入荷単価) from WorkView b
where b.SEQ <= a.SEQ
and b.個数 > 0)
-
case when a.負数計>0
then nvl((select sum(b.個数 * b.入荷単価) from WorkView b
where b.SEQ <= a.SEQ
and b.個数 > 0
and b.正数計 <=a.負数計),0)
+ nvl((select (a.負数計-b.Lag正数計) * b.入荷単価 from WorkView b
where b.SEQ = (select min(c.SEQ) from WorkView c
where c.SEQ <= a.SEQ
and c.正数計 > a.負数計)),0) else 0 end) / 在庫数 as 在庫単価
from WorkView a
order by SEQ;
--■■■表関数を使う方法■■■
create or replace Package Pack10_19 Is
type PrintType is record(
SEQ 在庫.SEQ%type,
区分 在庫.区分%type,
個数 在庫.個数%type,
入荷単価 在庫.入荷単価%type,
在庫数 number(2),
在庫単価 number(5,2));
type PrintTypeSet is table of PrintType;
end;
/
create or replace function 在庫Record return Pack10_19.PrintTypeSet PipeLined is
out_rec Pack10_19.PrintType;
--単価データを保存する配列
type 単価DataTypeArray is table of number(3) index by binary_integer;
単価Array 単価DataTypeArray;
単価ArrayInsPointer binary_integer :=0;
総単価 number(5);
begin
for rec in (select SEQ,区分,個数,
decode(区分,'返納',Last_Value(入荷単価 ignore nulls)
over(order by SEQ),入荷単価) as 入荷単価,
sum(個数) over(order by SEQ) as 在庫数
from 在庫
order by SEQ) Loop
if rec.区分 in('入庫','返納') then --配列に入れる
for I in 1..rec.個数 Loop
単価ArrayInsPointer := 単価ArrayInsPointer + 1;
単価Array(単価ArrayInsPointer) := rec.入荷単価;
end Loop;
else --配列から取り去る
for I in 1..abs(rec.個数) Loop
単価Array.delete(単価Array.FIRST);
end Loop;
end if;
out_rec.SEQ := rec.SEQ;
out_rec.区分 := rec.区分;
out_rec.個数 := rec.個数;
out_rec.在庫数 := rec.在庫数;
out_rec.入荷単価 := rec.入荷単価;
総単価 := 0;
for I in 単価Array.FIRST..単価Array.LAST Loop
総単価 := 総単価 + 単価Array(I);
end Loop;
out_rec.在庫単価 := 総単価/rec.在庫数;
pipe row(out_rec);
end Loop;
end;
/
sho err
select * from table(在庫Record);