建立專案情境 11

這是我針對某一個[Module]內的[Work Items]的程式錯誤做的Data patch :

declare
cursor fixrows is
SELECT
t3.[pk_filed1]
FROM
[main_table_1] t3
WHERE
1 = 1
AND t3.[pk_filed1] IN (
SELECT
t1.[pk_filed1]
FROM [sub_module_main_table_1] t1
WHERE 1=1
AND t1.[sub_module_main_table_1_key_field_1] IN (
SELECT
t2.[sub_module_detail_table_1_key_field_1]
FROM [sub_module_detail_table_1] t2
WHERE 1=1
AND t2.[type_field_1] = '1' -- [type1]
)
AND t1.status = '3'
);

cursor fixrows2 is
SELECT
status.[sub_module_detail_table_1_key_field_1],
status.[type_field_1],
status.[sub_module_detail_table_1_key_field_2]
FROM [sub_module_detail_table_1] status
WHERE 1=1
AND status.[sub_module_detail_table_1_key_field_1] IN
(
SELECT
acceptance.[sub_module_main_table_1_key_field_1]
FROM [sub_module_main_table_1] acceptance
WHERE 1=1
AND acceptance.[pk_filed1] IN
(
SELECT
t3.[pk_filed1]
FROM
[main_table_1] t3
WHERE
1 = 1
AND t3.[pk_filed1] IN (
SELECT
t1.[pk_filed1]
FROM [sub_module_main_table_1] t1
WHERE 1=1
AND t1.[sub_module_main_table_1_key_field_1] IN (
SELECT
t2.[sub_module_detail_table_1_key_field_1]
FROM [sub_module_detail_table_1] t2
WHERE 1=1
AND t2.[type_field_1] = '1' -- [type1]
)
AND t1.status = '3'
)
)
);
begin
 
   for fixrow in fixrows loop
dbms_output.put_line ('[sub_module_main_table_1] : [pk_filed1]=' || fixrow.[pk_filed1]);
UPDATE [sub_module_main_table_1]
SET status = '5'
WHERE [pk_filed1] = fixrow.[pk_filed1];
   end loop;
 
   for fixrow2 in fixrows2 loop
dbms_output.put_line ('[sub_module_detail_table_1] : [sub_module_detail_table_1_key_field_1] =' || fixrow2.[sub_module_detail_table_1_key_field_1] ||', [type_field_1] =' || fixrow2.[type_field_1] ||', [sub_module_detail_table_1_key_field_2] =' || fixrow2.[sub_module_detail_table_1_key_field_2]);
UPDATE [sub_module_detail_table_1]
SET status = '5'
WHERE 1=1
AND [sub_module_detail_table_1_key_field_1] = fixrow2.[sub_module_detail_table_1_key_field_1]
AND [type_field_1] = fixrow2.[type_field_1]
AND [sub_module_detail_table_1_key_field_2] = fixrow2.[sub_module_detail_table_1_key_field_2]
;
   end loop; 
end ;