The CREATE OR REPLACE PROCEDURE SP0002
(
P_tdate date in
)
IS
RsCursor SYS_REFCURSOR; - define the cursor
V_workid varchar2 (15); - working
V_worktime date; - work time
V_closetime date; - work time
V_lackcard number (2); - lack of card
V_late number (5); Late -
V_leave number (5); - leave early
V_workhour number (5);
V_workidCount number (2); - whether for attendance record
V_serial number (10); - the total number of records
The BEGIN
The OPEN rsCursor for SELECT tc_emp02 FROM TC_EMP_FILE where tc_emp03=1;
LOOP
The FETCH rsCursor into v_workid; Exit the when rsCursor % NOTFOUND;
The begin
The SELECT worktime, closetime lackcard, newest, leave,
(CASE WHEN workhour & gt; 8.0 THEN 8.0 ELSE workhour END) AS workhour
Into v_workid v_closetime, v_lackcard v_late, v_leave, v_workhour
The FROM
(SELECT
WorkID, cdate worktime, closetime lackcard, newest, leave,
The CASE WHEN (workhour - trunc (workhour) to 0.5 & gt;=0) THEN trunc (workhour) + 0.5 ELSE trunc (workhour) END AS workhour
The FROM
(SELECT WorkID, cdate worktime, closetime,
CASE the WHEN worktime IS NULL AND closetime IS NOT NULL THEN 1
The WHEN worktime IS NOT NULL AND closetime IS NULL THEN 1
The WHEN worktime IS NULL AND closetime IS NULL THEN 2
The ELSE NULL END AS lackcard,
The CASE WHEN whh> '09:00 THEN Round ((worktime - to_date (to_char (worktime,' yyyy/mm/dd) | | '09:00:00', 'yyyy/mm/dd hh24: mi: ss')) * 24 * 60) else 0 END newest,
The CASE WHEN chh<'17:00 THEN Round ((to_date (to_char (closetime,' yyyy/mm/dd) | | '17:00:00', 'yyyy/mm/dd hh24: mi: ss') - closetime) * 24 * 60) else 0 END leave,
The CASE WHEN whh<='08:00 AND CHH & gt; THEN 8.0
='18:00'The WHEN WHH & lt;='08:00 AND chh<'18:00 THEN trunc ((closetime - to_date (to_char (worktime,' yyyy/mm/dd) | | '08:00:00', 'yyyy/mm/dd hh24: mi: ss')) * 24-1, 1)
The WHEN whh>='08:00 AND CHH & gt; THEN='18:00' trunc ((to_date (to_char (closetime, 'yyyy/mm/dd) | |' 18:00:00 ', 'yyyy/mm/dd hh24: mi: ss') - worktime) * 24-1, 1)
The WHEN whh>='08:00 AND CHH & lt; '18:00 THEN trunc ((closetime - worktime) * 24-1, 1) END AS workhour
The FROM (Select WorkID, cdate worktime, closetime, to_char (WHH worktime, 'hh24: mi), to_char (CHH closetime,' hh24: mi) FROM
(SELECT tc_brc02 tc_brc03 as WorkID, to_char (tc_brc04, 'yyyy/mm/dd) cdate,
MIN (CASE WHEN to_char (tc_brc04, 'hh24: mi) BETWEEN the' from 'AND' 12:00 THEN tc_brc04 ELSE NULL END) AS worktime,
MAX (CASE WHEN to_char (tc_brc04, 'hh24: mi) BETWEEN 12:00' 'AND' 8 'THEN tc_brc04 ELSE NULL END) AS closetime
The FROM TC_BRC_FILE where tc_brc03=v_workid and to_char (tc_brc04, 'yyyy/mm/dd)=p_tdate
GROUP BY tc_brc03, to_char (tc_brc04, 'yyyy/mm/dd)
)) associates) TBB);
The exception
The WHEN NO_DATA_FOUND THEN
V_lackcard:=2;
end;
Select count (tc_kqr03) into v_workidCount from TC_KQR_FILE where tc_kqr03=v_workid and tc_kqr02=p_tdate;
Select (NVL (Max (tc_kqr01), 0) + 1) into v_serial from TC_KQR_FILE where tc_kqr03=v_workid and tc_kqr02=p_tdate;
If v_workidCount=0 then
Insert into TC_KQR_FILE (tc_kqr01, tc_kqr02 tc_kqr03, tc_kqr04, tc_kqr05, tc_kqr06, tc_kqr07, tc_kqr08, tc_kqr09) values (v_serial p_tdate, v_workid, v_worktime, v_closetime, v_workhour, v_lackcard, v_late, v_leave);
The else
The update TC_KQR_FILE set tc_kqr04=v_worktime, tc_kqr05=v_closetime, tc_kqr06=v_workhour, tc_kqr07=v_lackcard, tc_kqr08=v_late, tc_kqr09=v_leave where tc_kqr03=v_workid and tc_kqr02=p_tdate;
End the if;
COMMIT;
END LOOP;
END SP0002;
CodePudding user response:
Grammar is no problem, you look at your results, and see if it is found out to a data;CodePudding user response:
Workid (tc_emp02), is not only a ah, is really strange.CodePudding user response:
The SELECT tc_emp02 FROM TC_EMP_FILE where tc_emp03=1;There is only one, this, isn't it?
CodePudding user response:
Of course not, there are fiveCodePudding user response:
You can call log, use dbms_output;