導入員工績效存儲過程

導入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;
/