I always thought that the following statement (Execution 1) is an implicit cursor, which is not much slower than the explicit definition cursor (execution 2. For the sake of convenience and simplicity, I usually use the method of executing one to write loop operations. I don't think there is anything when the data volume is small, just one day, the results of a massive data update run for one night, so that you can know how many mistakes you have made:
Run 1:
Begin
For C in (
Select ca.org _ code, M. material_code, sum (CD. Num) ammount
From mas_admin.asc_stock_change_bill C
Join mas_admin.asc_stock_change_detail CD on C. stock_change_bill_id =
CD. stock_change_bill_id
Join mas_admin.asc_materiel m on M. materiel_id = CD. material_id -- Material Table
Join mas_admin.asc_stock s on S. stock_id = C. stock_id
Join mas_admin.asc_agent Ca ON Ca. org_id = s.org _ id
Where C. enable_flag = 'y'
And S. stock_type_id = 1
And C. bill_state = 3
And substr (ca.org _ code, 1, 3) <> 'bak'
And C. stock_change_reason_id = 3 -- 3 = System Error
Group by ca.org _ code, M. material_code
) Loop
Update asc_ca_stock_good_usable
Set adjust_num_error = C. ammount
Where org_code = c.org _ code and material_code = C. material_code and action_date = curr_date;
End loop;
Commit;
End;
Execution 2:
Declare
Var_org_code varchar2 (50 );
Var_material_code varchar2 (50 );
Var_ammount number;
Cursor C is
Select ca.org _ code, M. material_code, sum (CD. Num) ammount
From mas_admin.asc_stock_change_bill C
Join mas_admin.asc_stock_change_detail CD on C. stock_change_bill_id =
CD. stock_change_bill_id
Join mas_admin.asc_materiel m on M. materiel_id = CD. material_id -- Material Table
Join mas_admin.asc_stock s on S. stock_id = C. stock_id
Join mas_admin.asc_agent Ca ON Ca. org_id = s.org _ id
Where C. enable_flag = 'y'
And S. stock_type_id = 1
And C. bill_state = 3
And substr (ca.org _ code, 1, 3) <> 'bak'
And C. stock_change_reason_id = 3 -- 3 = System Error
Group by ca.org _ code, M. material_code;
Begin
Open C;
Loop
Fetch C into var_org_code, var_material_code, var_ammount;
Exit when C % notfound;
Update asc_ca_stock_good_usable
Set adjust_num_error = var_ammount
Where org_code = var_org_code and material_code = var_material_code and action_date = trunc (sysdate );
Commit;
End loop;
End;
It takes only 0.2 seconds to execute the task for more than 10 minutes.