導入員工績效存儲過程

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

 

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值