第一次寫oracle的存盤程序，如下，為什么只回圈了第一筆v_workid就跳出去了。
CREATE OR REPLACE PROCEDURE SP0002
(
p_tdate in date
)
IS
rsCursor SYS_REFCURSOR; --定義遊標
v_workid varchar2(15);--工號
v_worktime date; --上班時間
v_closetime date; --下班時間
v_lackcard number(2); --缺卡
v_late number(5); --遲到
v_leave number(5);--早退
v_workhour number(5);
v_workidCount number(2); --是否已有考勤記錄
v_serial number(10); --總記錄數
BEGIN
OPEN rsCursor for SELECT tc_emp02 FROM TC_EMP_FILE where tc_emp03=1;
LOOP
FETCH rsCursor into v_workid; exit when rsCursor%NOTFOUND;
begin
SELECT worktime,closetime,lackcard,late,leave,
(CASE WHEN workhour > 8.0 THEN 8.0 ELSE workhour END) AS workhour
into v_workid,v_closetime,v_lackcard,v_late,v_leave,v_workhour
FROM
(SELECT
WorkID, cdate, worktime, closetime, lackcard, late, leave,
CASE WHEN (workhour-trunc(workhour)-0.5 >= 0) THEN trunc(workhour)+0.5 ELSE trunc(workhour) END AS workhour
FROM
(SELECT WorkID, cdate, worktime, closetime,
CASE WHEN worktime IS NULL AND closetime IS NOT NULL THEN 1
WHEN worktime IS NOT NULL AND closetime IS NULL THEN 1
WHEN worktime IS NULL AND closetime IS NULL THEN 2
ELSE NULL END AS lackcard,
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 late,
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,
CASE WHEN whh<='08:00' AND chh >= '18:00' THEN 8.0
WHEN whh <= '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)
WHEN whh>='08:00' AND chh >= '18:00' THEN trunc((to_date(to_char(closetime,'yyyy/mm/dd ')||'18:00:00','yyyy/mm/dd hh24:mi:ss')-worktime) * 24 - 1,1)
WHEN whh>='08:00' AND chh < '18:00' THEN trunc((closetime-worktime) * 24 - 1,1) END AS workhour
FROM (Select WorkID,cdate,worktime,closetime,to_char(worktime,'hh24:mi') whh,to_char(closetime,'hh24:mi') chh 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 '07:00' AND '12:00' THEN tc_brc04 ELSE NULL END) AS worktime,
MAX(CASE WHEN to_char(tc_brc04,'hh24:mi') BETWEEN '12:00' AND '20:00' THEN tc_brc04 ELSE NULL END) AS closetime
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')
)) tba) tbb);
exception
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);
else
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 if;
COMMIT;
END LOOP;
END SP0002;
uj5u.com熱心網友回復：
語法上是沒有問題的，你查一直你的結果，看看是不是就查出來了一條資料；uj5u.com熱心網友回復：
workid(tc_emp02)嗎，不只一筆啊，真是奇怪了。uj5u.com熱心網友回復：
SELECT tc_emp02 FROM TC_EMP_FILE where tc_emp03=1;這個，是不是，只有一條啊？
uj5u.com熱心網友回復：
當然不是了，有5條uj5u.com熱心網友回復：
你可以打一下日志， 用dbms_output ；轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/112086.html
標籤：基礎和管理