導入7000多筆資料,如果寫Java程式的話就算優化到每筆資料一條SQL語句,半個小時都更新不完
換成存儲過程的話1分多鐘就OK了
具體做法是遍歷Excel表中的每一行,把所有資料讀進來,先判斷數據是否合法,不合法的剔除,然後把所有員工工號用stringBuffer串起來,中間用一個特殊字符分割開,大概三四百筆的時候調用一次存儲過程,然後用存儲過程完成字符串的分割和數據更新。
CREATE OR REPLACE procedure HTCHR.import_Employee_Performance(--導入員工績效
employeeNumberStr in varchar2,
performanceRatingStr in varchar2,
defaultProposedMonthStr in varchar2,
defaultProposedAmountStr in varchar2,
annualPlanningId in number
) is
allEmployeeNumber varchar2(4000);--所有員工工號
allPerformanceRating varchar2(4000);--所有員工績效
allDefaultProposedMonth varchar2(4000);--所有發放月數
allDefaultProposedAmount varchar2(4000);--所有發放金額
empNumber varchar2(4000);--員工工號
rating varchar2(4000);--員工績效
month varchar2(4000);--發放月數
amount varchar2(4000);--發放金額
employeeNumber_index int;--當前員工工號在所有員工工號中的位置
performanceRating_index int;--當前員工績效在所有員工績效中的位置
defaultProposedMonth_index int;--當前發放月數在所有發放月數中的位置
defaultProposedAmount_index int;-- 當前發放金額在所有發放金額中的位置
begin
allEmployeeNumber := employeeNumberStr;
allPerformanceRating := performanceRatingStr;
allDefaultProposedMonth := defaultProposedMonthStr;
allDefaultProposedAmount := defaultProposedAmountStr;
employeeNumber_index := instr(allEmployeeNumber,'*',2);
while(employeeNumber_index>0) loop
employeeNumber_index := instr(allEmployeeNumber,'*',2);
performanceRating_index := instr(allPerformanceRating,'*',2);
defaultProposedMonth_index := instr(allDefaultProposedMonth,'*',2);
defaultProposedAmount_index := instr(allDefaultProposedAmount,'*',2);
empNumber := substr(allEmployeeNumber,2,employeeNumber_index-2);
rating := substr(allPerformanceRating,2,performanceRating_index-2);
month := substr(allDefaultProposedMonth,2,defaultProposedMonth_index-2);
amount := substr(allDefaultProposedAmount,2,defaultProposedAmount_index-2);
update salaryfinaldetail set performanceRating = rating,
defaultProposedMonth = month,
defaultProposedAmount = amount
where annualPlanning_id = annualPlanningId and employeenumber = empNumber;
allEmployeeNumber := substr(allEmployeeNumber,employeeNumber_index);
allPerformanceRating := substr(allPerformanceRating,performanceRating_index);
allDefaultProposedMonth := substr(allDefaultProposedMonth,defaultProposedMonth_index);
allDefaultProposedAmount := substr(allDefaultProposedAmount,defaultProposedAmount_index);
end loop;
empNumber := substr(allEmployeeNumber,2);
rating := substr(allPerformanceRating,2);
month := substr(allDefaultProposedMonth,2);
amount := substr(allDefaultProposedAmount,2);
update salaryfinaldetail set performanceRating = rating,
defaultProposedMonth = month,
defaultProposedAmount = amount
where annualPlanning_id = annualPlanningId and employeenumber = empNumber;
commit;
end;
/