部屋テーブル
部屋ID 許容人数
------ --------
101 1
102 1
103 1
104 1
201 2
202 2
仮予約テーブル
予約ID 到着予定日 出発予定日 予約人数 予約完了フラグ キャンセルフラグ
------ ---------- ---------- -------- -------------- ----------------
1 2003/08/01 2003/08/03 2 null null
2 2003/08/02 2003/08/03 2 null null
3 2003/08/03 2003/08/04 2 null null
4 2003/08/01 2003/08/04 2 null null
5 2003/08/04 2003/08/08 3 null null
6 2003/08/04 2003/08/08 1 null null
(1)
出来るだけ「2人は2人部屋に1人は1人部屋に」というように割り当てる。
(候補となる部屋が複数あったら、部屋IDの小さいほうを優先して割り当てる)
優先順位は予約IDの古いものからとする。
(2)
上記で予約状況が作成できたら仮予約の予約完了を'Y'にします。
更新結果
予約状況テーブル
予約ID 部屋ID 到着予定日 出発予定日
------ ------ ---------- ----------
1 201 2003/08/01 2003/08/03
2 202 2003/08/02 2003/08/03
3 201 2003/08/03 2003/08/04
4 101 2003/08/01 2003/08/04
4 102 2003/08/01 2003/08/04
5 201 2003/08/04 2003/08/08
5 101 2003/08/04 2003/08/08
6 102 2003/08/04 2003/08/08
create table 部屋(
部屋ID number(3) primary key,
許容人数 number(1) check (許容人数 between 1 and 2));
insert into 部屋 values(101,1);
insert into 部屋 values(102,1);
insert into 部屋 values(103,1);
insert into 部屋 values(104,1);
insert into 部屋 values(201,2);
insert into 部屋 values(202,2);
create table 仮予約(
予約ID number(2) primary key,
到着予定日 date,
出発予定日 date,
予約人数 number(1),
予約完了フラグ char(1), --予約完了の場合 'Y'
キャンセルフラグ char(1) --キャンセルの場合 'Y'
);
insert into 仮予約(予約ID,到着予定日,出発予定日,予約人数)
select 1,to_date('2003-08-01','YYYY-MM-DD'),to_date('2003-08-03','YYYY-MM-DD'),2 from dual
union select 2,to_date('2003-08-02','YYYY-MM-DD'),to_date('2003-08-03','YYYY-MM-DD'),2 from dual
union select 3,to_date('2003-08-03','YYYY-MM-DD'),to_date('2003-08-04','YYYY-MM-DD'),2 from dual
union select 4,to_date('2003-08-01','YYYY-MM-DD'),to_date('2003-08-04','YYYY-MM-DD'),2 from dual
union select 5,to_date('2003-08-04','YYYY-MM-DD'),to_date('2003-08-08','YYYY-MM-DD'),3 from dual
union select 6,to_date('2003-08-04','YYYY-MM-DD'),to_date('2003-08-08','YYYY-MM-DD'),1 from dual;
create table 予約状況(
部屋ID number(3) references 部屋(部屋ID),
予約ID number(2) references 仮予約(予約ID),
到着予定日 date,
出発予定日 date);
declare
Books pls_Integer;
wk_部屋ID 部屋.部屋ID%type;
wk_許容人数 部屋.許容人数%type;
wk_許容人数合計 pls_Integer;
begin
for r1 in (select 予約ID,到着予定日,出発予定日,予約人数
from 仮予約
order by 予約ID) Loop
Books := r1.予約人数;
while Books > 0 loop
select 部屋ID,許容人数,許容人数合計 into wk_部屋ID,wk_許容人数,wk_許容人数合計
from (select 部屋ID,許容人数,sum(許容人数) over() as 許容人数合計,
Row_Number() over(order by case when 許容人数 <= Books
then -許容人数
else 許容人数 end,部屋ID) as Rn
from 部屋 a
where not exists(select 1 from 予約状況 b
where b.部屋ID = a.部屋ID
and r1.到着予定日 <= b.出発予定日
and b.到着予定日 <= r1.出発予定日
and b.出発予定日 != r1.到着予定日))
where Rn= 1;
--空室不足で予約不可
exit when wk_許容人数合計 < r1.予約人数;
if Books > 0 then
insert into 予約状況(予約ID,部屋ID,到着予定日,出発予定日)
values(r1.予約ID,wk_部屋ID,r1.到着予定日,r1.出発予定日);
Books := Books - wk_許容人数;
exit when Books <= 0;
end if;
end Loop;
end Loop;
end;
/
空室を探すロジックでの部屋の許容人数の優先順位は、 許容人数 <= 予約人数 の降順 許容人数 > 予約人数 の昇順 の順番としています。 例として、 5以下を降順、6以上を昇順に出力するSQLはこうなります。 select Rn from (select RowNum as Rn from dict where RowNum <= 10) order by case when Rn <= 5 then -Rn else Rn end; Rn -- 5 4 3 2 1 6 7 8 9