CREATE OR REPLACE FUNCTION OFILM_FN_SHIFTENDTIME --标准下班时间 (P_NUM VARCHAR2, P_DATE DATE) RETURN DATE IS ENDTIME DATE; BEGIN SELECT SHIFTENDDATE INTO ENDTIME FROM VP_SCHEDULE WHERE TO_CHAR(SHIFTENDDATE,'YYYY/MM/DD')=TO_CHAR(P_DATE,'YYYY/MM/DD') AND PERSONNUM = P_NUM AND ROWNUM=1; RETURN ENDTIME; EXCEPTION WHEN OTHERS THEN RETURN ''; END ; TO_CHAR转换导致的么?
SELECT DISTINCT TO_CHAR(PP.COMPANYHIREDTM, 'YYYY-MM-DD') AS 进入集团日期, S.PERSONNUM AS 工号, PP.PERSONFULLNAME AS 姓名, PP.HOMELABORLEVELDSC3 AS 人员类别, CUX_KRONOS_PKG.GET_HIGHER_DEPT(PP.HOMELABORLEVELDSC2, '4' ) AS 事业本部, PP.HOMELABORLEVELDSC2 AS 部门, ofilm_fn_get_personcusdata(S.PERSONNUM, '职务' ) AS 职务, ofilm_fn_get_personcusdata(S.PERSONNUM, '职级' ) AS 职级, ofilm_fn_get_personcusdata(S.PERSONNUM, '工序' ) AS 工序, OFILM_FN_COMENTDSC(S.PERSONNUM, S.EVENTDATE) AS 班别, TO_CHAR(S.EVENTDATE, 'YYYY-MM-DD' ) AS 日期, TO_CHAR(S.INPUNCHDTM,'HH24:MI') AS 上班卡, TO_CHAR(S.OUTPUNCHDTM,'HH24:MI') AS 下班卡, TO_CHAR(VAS.SHIFTENDDATE,'HH24:MI') AS 标准下班时间, OFILM_FN_STATUS(S.PERSONNUM, S.EVENTDATE) || S.PAYCODENAME AS 出勤状态, CASE WHEN (S.OUTPUNCHDTM-VAS.SHIFTENDDATE)*24>0 THEN ROUND((S.OUTPUNCHDTM-VAS.SHIFTENDDATE)*24,2) ELSE 0 END 加班时间 FROM ( SELECT PERSONNUM, PAYCODENAME, EVENTDATE, INPUNCHDTM, OUTPUNCHDTM FROM VP_TIMESHTPUNCHV42 WHERE EVENTDATE BETWEEN :From_timeframe AND :To_timeframe AND (INPUNCHDTM IS NULL AND OUTPUNCHDTM IS NULL) AND PAYCODENAME IS NOT NULL AND TIMESHEETITEMTYPE != 'ManagerJustification' UNION SELECT PERSONNUM, PAYCODENAME, EVENTDATE, INPUNCHDTM, OUTPUNCHDTM FROM VP_TIMESHTPUNCHV42 WHERE EVENTDATE BETWEEN :From_timeframe AND :To_timeframe AND (INPUNCHDTM IS NOT NULL OR OUTPUNCHDTM IS NOT NULL ) AND PAYCODENAME IS NULL) S LEFT JOIN VP_ALLPERSONV42 PP ON S.PERSONNUM = PP.PERSONNUM LEFT JOIN VP_ASSIGNSCHEDULE VAS ON S.PERSONNUM=VAS.PERSONNUM AND TO_CHAR(S.EVENTDATE,'YYYY/MM/DD')=TO_CHAR(VAS.SHIFTSTARTDATE,'YYYY/MM/DD') LEFT JOIN mywtkemployee MWE on PP.employeeid=MWE.employeeid WHERE MWE.SESSIONID= :wtksessionid ORDER BY S.PERSONNUM, TO_CHAR(S.EVENTDATE, 'YYYY-MM-DD' )这是我修改的语句,用的函数少了,可还是很慢,1.5w条数据需要一个小时,从哪里下手修改呢
这个如何解决呢?
CREATE OR REPLACE FUNCTION OFILM_FN_SHIFTENDTIME --标准下班时间
(P_NUM VARCHAR2,
P_DATE DATE)
RETURN DATE IS
ENDTIME DATE;
BEGIN
SELECT SHIFTENDDATE INTO ENDTIME
FROM VP_SCHEDULE
WHERE
TO_CHAR(SHIFTENDDATE,'YYYY/MM/DD')=TO_CHAR(P_DATE,'YYYY/MM/DD')
AND PERSONNUM = P_NUM
AND ROWNUM=1;
RETURN ENDTIME;
EXCEPTION
WHEN OTHERS THEN
RETURN '';
END ;
TO_CHAR转换导致的么?
改为
select max()避免异常就可以了
另外VP_SCHEDULE是怎么个数量级?可以考虑在这个表上适当增加索引
是一个三四万人的考勤系统,schedule是他们的排班表 我那个函数是查出他们的排班的下班时间
S.PERSONNUM AS 工号,
PP.PERSONFULLNAME AS 姓名,
PP.HOMELABORLEVELDSC3 AS 人员类别,
CUX_KRONOS_PKG.GET_HIGHER_DEPT(PP.HOMELABORLEVELDSC2, '4' ) AS 事业本部,
PP.HOMELABORLEVELDSC2 AS 部门,
ofilm_fn_get_personcusdata(S.PERSONNUM, '职务' ) AS 职务,
ofilm_fn_get_personcusdata(S.PERSONNUM, '职级' ) AS 职级,
ofilm_fn_get_personcusdata(S.PERSONNUM, '工序' ) AS 工序,
OFILM_FN_COMENTDSC(S.PERSONNUM, S.EVENTDATE) AS 班别,
TO_CHAR(S.EVENTDATE, 'YYYY-MM-DD' ) AS 日期,
TO_CHAR(S.INPUNCHDTM,'HH24:MI') AS 上班卡,
TO_CHAR(S.OUTPUNCHDTM,'HH24:MI') AS 下班卡,
TO_CHAR(VAS.SHIFTENDDATE,'HH24:MI') AS 标准下班时间,
OFILM_FN_STATUS(S.PERSONNUM, S.EVENTDATE) || S.PAYCODENAME AS 出勤状态,
CASE WHEN (S.OUTPUNCHDTM-VAS.SHIFTENDDATE)*24>0
THEN ROUND((S.OUTPUNCHDTM-VAS.SHIFTENDDATE)*24,2)
ELSE 0 END 加班时间
FROM ( SELECT PERSONNUM,
PAYCODENAME,
EVENTDATE,
INPUNCHDTM,
OUTPUNCHDTM
FROM VP_TIMESHTPUNCHV42
WHERE EVENTDATE BETWEEN :From_timeframe AND :To_timeframe
AND (INPUNCHDTM IS NULL AND OUTPUNCHDTM IS NULL)
AND PAYCODENAME IS NOT NULL
AND TIMESHEETITEMTYPE != 'ManagerJustification'
UNION
SELECT PERSONNUM, PAYCODENAME, EVENTDATE, INPUNCHDTM, OUTPUNCHDTM
FROM VP_TIMESHTPUNCHV42
WHERE EVENTDATE BETWEEN :From_timeframe AND :To_timeframe
AND (INPUNCHDTM IS NOT NULL OR OUTPUNCHDTM IS NOT NULL )
AND PAYCODENAME IS NULL) S
LEFT JOIN VP_ALLPERSONV42 PP
ON S.PERSONNUM = PP.PERSONNUM
LEFT JOIN VP_ASSIGNSCHEDULE VAS
ON S.PERSONNUM=VAS.PERSONNUM AND TO_CHAR(S.EVENTDATE,'YYYY/MM/DD')=TO_CHAR(VAS.SHIFTSTARTDATE,'YYYY/MM/DD')
LEFT JOIN mywtkemployee MWE
on PP.employeeid=MWE.employeeid
WHERE
MWE.SESSIONID= :wtksessionid
ORDER BY S.PERSONNUM, TO_CHAR(S.EVENTDATE, 'YYYY-MM-DD' )这是我修改的语句,用的函数少了,可还是很慢,1.5w条数据需要一个小时,从哪里下手修改呢