建立專案情境 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 ;
1. fixrows, fixrows2 這兩個 cursor 一定要先讀取好:
因為當要Data Batch的table有主從關係時,如果先行個別Update 某一個Table,
就會形成無主單的資料,或是沒有從屬關係 Detail 的資料。
2. Data Batch 的過程,最好使用 Oracle PL/SQL 的 DBMS LOG 功能,使用 dbms_output.put_line。
3. 需要 Data Batch 的原因:因為 sub_module 的主程式 的 Main Work Item 在 Update Data 時,Key 值的來源集合是單筆的。
這是我針對某一個[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 ;
1. fixrows, fixrows2 這兩個 cursor 一定要先讀取好:
因為當要Data Batch的table有主從關係時,如果先行個別Update 某一個Table,
就會形成無主單的資料,或是沒有從屬關係 Detail 的資料。
2. Data Batch 的過程,最好使用 Oracle PL/SQL 的 DBMS LOG 功能,使用 dbms_output.put_line。
3. 需要 Data Batch 的原因:因為 sub_module 的主程式 的 Main Work Item 在 Update Data 時,Key 值的來源集合是單筆的。