<案例> 工作排程－Eason [資料科學//Python學習/資料庫] & [拍片&剪片]

案例來源: PTT

請問我有個資料表(work_job)如下：

工作項目前置工作項目1 前置工作項目2 工作天數
=======================================
A 1
B A 3
C A B 5
D A 2

我想得到(final_job)：
工作項目第幾天開始第幾天結束
=======================================
A 0 1
B 1 4
C 4 9
D 1 3

解法: 窮舉各種情況(不負責任解法)

*********************

create table work_job (item char(2), i1 char(2), i2 char(2), days char(2));
insert into work_job values('D','A',NULL,'2');
commit;
create table final_job (item char(2),begin1 char(2), end1 char(2) );
insert into final_job values('D',NULL,NULL);
SELECT * FROM final_job;
commit;

create or replace procedure proc_jobs is
v_item char(2);
v_1 char(2);
v_2 char(2);
v_3 char(2);
v_a char(2):='0';--控制工作A的天數
v_b char(2):='0';--控制工作A的天數
v_c char(2):='0';--控制工作A的天數
v_d char(2):='0';--控制工作A的天數
cursor c_1 is
select * from work_job;
begin
open c_1;
loop
fetch c_1 into v_item,v_1,v_2,v_3;
EXIT when c_1%NOTFOUND;
if v_item='A' then
update final_job set begin1=v_a,end1=v_3 where item=v_item;
v_a:=v_a+v_3;
end if;

if v_item='B' AND v_1='A' then
update final_job set begin1=v_a,end1=v_a+v_3 where item=v_item;
v_b:=v_a+v_3;
elsif v_item='B' then
update final_job set begin1=v_b,end1=v_3 where item=v_item;
v_b:=v_b+v_3;
end if;

if v_item='C' AND (v_b>v_a) then
update final_job set begin1=v_b,end1=v_b+v_3 where item=v_item;
v_c:=v_b+v_3;
elsif v_item='C' AND (v_b<v_a) then
update final_job set begin1=v_a,end1=v_a+v_3 where item=v_item;
v_c:=v_a+v_3;
elsif v_item='C' then
update final_job set begin1=v_c,end1=v_3 where item=v_item;
v_c:=v_c+v_3;
end if;

if v_item='D' AND v_1='A' then
update final_job set begin1=v_a,end1=v_a+v_3 where item=v_item;
v_d:=v_a+v_3;
elsif v_item='D' AND v_1='B' then
update final_job set begin1=v_b,end1=v_b+v_3 where item=v_item;
v_d:=v_b+v_3;
elsif v_item='D' AND v_1='C' then
update final_job set begin1=v_c,end1=v_c+v_3 where item=v_item;
v_d:=v_c+v_3;
elsif v_item='D' AND v_2='B' then
update final_job set begin1=v_b,end1=v_b+v_3 where item=v_item;
v_d:=v_b+v_3;
elsif v_item='D' AND v_2='C' then
update final_job set begin1=v_c,end1=v_c+v_3 where item=v_item;
v_d:=v_c+v_3;
elsif v_item='D' AND (v_b>v_a) then
update final_job set begin1=v_b,end1=v_b+v_3 where item=v_item;
v_d:=v_b+v_3;
elsif v_item='D' AND (v_b<v_a) then
update final_job set begin1=v_a,end1=v_a+v_3 where item=v_item;
v_d:=v_a+v_3;
elsif v_item='D' AND (v_c<v_a) then
update final_job set begin1=v_a,end1=v_a+v_3 where item=v_item;
v_d:=v_a+v_3;
elsif v_item='D' AND (v_c>v_a) then
update final_job set begin1=v_c,end1=v_c+v_3 where item=v_item;
v_d:=v_c+v_3;
elsif v_item='D' AND (v_c<v_b) then
update final_job set begin1=v_b,end1=v_b+v_3 where item=v_item;
v_d:=v_b+v_3;
elsif v_item='D' AND (v_c>v_b) then
update final_job set begin1=v_c,end1=v_c+v_3 where item=v_item;
v_d:=v_c+v_3;
end if;
end loop;
CLOSE c_1;
end;
/
show error;