ding20688
- 浏览: 63308 次
- 性别:
- 来自: 烟台
-
最新评论
为什么我说它标准呢？因为这个存储过程中包括了，游标的使用，for语句，if语句，定义变量的使用，第二个存储过程还有传入参数的使用，做个模版，以后仿照来写就可以了。
CREATE OR REPLACE PROCEDURE NEWHR.update_boykq_ding
IS
/******************************************************************************
創建人：丁樂進
創建時間：2010/7/9
用途：保密
******************************************************************************/
v_dt VARCHAR (20);
v_workno VARCHAR (20);
v_begintime DATE;
v_workshift NUMBER;
v_temp NUMBER;
v_ondutytime NUMBER;
v_otoffdutytime NUMBER;
v_r1 NUMBER;
v_r2 NUMBER;
v_a1 NUMBER;
v_a2 NUMBER;
v_a3 NUMBER;
v_a4 NUMBER;
CURSOR find_workno
IS
SELECT dt, workno
FROM (SELECT TO_CHAR (TO_NUMBER (TO_CHAR (borndate, 'yyyy')) + 18
)
|| TO_CHAR (borndate, 'mmdd') dt,
borndate, workno
FROM hrm_employees)
WHERE dt >= '20090801';
BEGIN
OPEN find_workno;
LOOP
FETCH find_workno
INTO v_dt, v_workno;
EXIT WHEN find_workno%NOTFOUND;
SELECT COUNT (*)
INTO v_workshift
FROM kqm_employeeshift
WHERE SUBSTR (shiftno, 0, 1) = 'C'
AND workno = v_workno
AND TO_CHAR (startdate, 'yyyy/MM/dd') >= '2009/08/01'
AND TO_CHAR (enddate, 'yyyyMMdd') <= v_dt;
IF v_workshift > 0
THEN
UPDATE kqm_employeeshift
SET shiftno = 'A611'
WHERE workno = v_workno
AND SUBSTR (shiftno, 0, 1) = 'C'
AND TO_CHAR (startdate, 'yyyy/MM/dd') >= '2009/08/01'
AND TO_CHAR (enddate, 'yyyyMMdd') <= v_dt;
END IF;
FOR rec IN (SELECT ondutytime, offdutytime, kqdate
FROM kqm_kaoqindata
WHERE workno = v_workno
AND TO_CHAR (kqdate, 'yyyy/MM/dd') >= '2009/08/01'
AND TO_CHAR (kqdate, 'yyyyMMdd') <= v_dt
AND SUBSTR (shiftno, 0, 1) = 'C'
AND othours = 0
ORDER BY kqdate)
LOOP
SELECT ondutytime
INTO v_begintime
FROM kqm_kaoqindata
WHERE workno = v_workno AND ondutytime = rec.ondutytime;
IF rec.ondutytime IS NOT NULL
THEN
SELECT COUNT (*)
INTO v_r1
FROM kqm_bellcarddata
WHERE workno = v_workno
AND cardtime <= rec.ondutytime + 1 / 24
AND cardtime >= rec.ondutytime - 1 / 24;
IF v_r1 > 0
THEN
SELECT COUNT (*)
INTO v_a1
FROM kqm_bellcarddata
WHERE workno = v_workno
AND cardtime =
TO_DATE (TO_CHAR (v_begintime, 'yyyymmdd') || '07:50',
'yyyymmdd HH24:mi:ss'
);
IF v_a1 > 0
THEN
UPDATE kqm_bellcarddata
SET cardtime =
cardtime
- 12 / 24
- 1 / 1440
- ( v_begintime
- TO_DATE ( TO_CHAR (v_begintime, 'yyyymmdd')
|| '19:50',
'yyyymmdd HH24:mi:ss'
)
),
readtime =
readtime
- 12 / 24
- ( v_begintime
- TO_DATE ( TO_CHAR (v_begintime, 'yyyymmdd')
|| '19:50',
'yyyymmdd HH24:mi:ss'
)
)
WHERE workno = v_workno
AND cardtime <= rec.ondutytime + 1 / 24
AND cardtime >= rec.ondutytime - 1 / 24;
ELSE
UPDATE kqm_bellcarddata
SET cardtime =
cardtime
- 12 / 24
- ( v_begintime
- TO_DATE ( TO_CHAR (v_begintime, 'yyyymmdd')
|| '19:50',
'yyyymmdd HH24:mi:ss'
)
),
readtime =
readtime
- 12 / 24
- ( v_begintime
- TO_DATE ( TO_CHAR (v_begintime, 'yyyymmdd')
|| '19:50',
'yyyymmdd HH24:mi:ss'
)
)
WHERE workno = v_workno
AND cardtime <= rec.ondutytime + 1 / 24
AND cardtime >= rec.ondutytime - 1 / 24;
END IF;
END IF;
END IF;
IF rec.offdutytime IS NOT NULL
THEN
SELECT COUNT (*)
INTO v_r2
FROM kqm_bellcarddata
WHERE workno = v_workno
AND cardtime <= rec.offdutytime + 1 / 24
AND cardtime >= rec.offdutytime - 1 / 24;
IF v_r2 > 0
AND TO_CHAR (rec.offdutytime, 'yyyy/MM/dd') <> '2010/01/01'
THEN
SELECT COUNT (*)
INTO v_a2
FROM kqm_bellcarddata
WHERE workno = v_workno
AND cardtime =
TO_DATE (TO_CHAR (v_begintime, 'yyyymmdd') || '07:50',
'yyyymmdd HH24:mi:ss'
);
IF v_a2 > 0
THEN
UPDATE kqm_bellcarddata
SET cardtime =
cardtime
- 12 / 24
- 1 / 1440
- ( v_begintime
- TO_DATE ( TO_CHAR (v_begintime, 'yyyymmdd')
|| '19:50',
'yyyymmdd HH24:mi:ss'
)
),
readtime =
readtime
- 12 / 24
- ( v_begintime
- TO_DATE ( TO_CHAR (v_begintime, 'yyyymmdd')
|| '19:50',
'yyyymmdd HH24:mi:ss'
)
)
WHERE workno = v_workno
AND cardtime <= rec.offdutytime + 1 / 24
AND cardtime >= rec.offdutytime - 1 / 24;
ELSE
UPDATE kqm_bellcarddata
SET cardtime =
cardtime
- 12 / 24
- ( v_begintime
- TO_DATE ( TO_CHAR (v_begintime, 'yyyymmdd')
|| '19:50',
'yyyymmdd HH24:mi:ss'
)
),
readtime =
readtime
- 12 / 24
- ( v_begintime
- TO_DATE ( TO_CHAR (v_begintime, 'yyyymmdd')
|| '19:50',
'yyyymmdd HH24:mi:ss'
)
)
WHERE workno = v_workno
AND cardtime <= rec.offdutytime + 1 / 24
AND cardtime >= rec.offdutytime - 1 / 24;
END IF;
END IF;
END IF;
IF TO_CHAR (rec.kqdate, 'yyyy/MM/dd') <> '2010/01/01'
THEN
UPDATE kqm_kaoqindata
SET ondutytime =
ondutytime
- 12 / 24
- ( v_begintime
- TO_DATE ( TO_CHAR (v_begintime, 'yyyymmdd')
|| '19:50',
'yyyymmdd HH24:mi:ss'
)
),
offdutytime =
offdutytime
- 12 / 24
- ( v_begintime
- TO_DATE ( TO_CHAR (v_begintime, 'yyyymmdd')
|| '19:50',
'yyyymmdd HH24:mi:ss'
)
),
shiftno = 'A611'
WHERE workno = v_workno AND kqdate = rec.kqdate;
END IF;
END LOOP;
FOR rec IN (SELECT ondutytime, otoffdutytime, kqdate
FROM kqm_kaoqindata
WHERE workno = v_workno
AND SUBSTR (shiftno, 0, 1) = 'C'
AND TO_CHAR (kqdate, 'yyyy/MM/dd') >= '2009/08/01'
AND TO_CHAR (kqdate, 'yyyyMMdd') <= v_dt
AND othours > 0)
LOOP
SELECT ondutytime
INTO v_begintime
FROM kqm_kaoqindata
WHERE workno = v_workno AND ondutytime = rec.ondutytime;
SELECT COUNT (*)
INTO v_temp
FROM otm_advanceapply
WHERE workno = v_workno
AND otdate = rec.kqdate
AND SUBSTR (otshiftno, 0, 1) = 'C';
IF rec.ondutytime IS NOT NULL
THEN
SELECT COUNT (*)
INTO v_ondutytime
FROM kqm_bellcarddata
WHERE workno = v_workno
AND cardtime <= rec.ondutytime + 1 / 24
AND cardtime >= rec.ondutytime - 1 / 24;
IF v_ondutytime > 0
THEN
SELECT COUNT (*)
INTO v_a3
FROM kqm_bellcarddata
WHERE workno = v_workno
AND cardtime =
TO_DATE (TO_CHAR (v_begintime, 'yyyymmdd') || '07:50',
'yyyymmdd HH24:mi:ss'
);
IF v_a3 > 0
THEN
UPDATE kqm_bellcarddata
SET cardtime =
cardtime
- 12 / 24
- 1 / 1440
- ( v_begintime
- TO_DATE ( TO_CHAR (v_begintime, 'yyyymmdd')
|| '19:50',
'yyyymmdd HH24:mi:ss'
)
),
readtime =
readtime
- 12 / 24
- ( v_begintime
- TO_DATE ( TO_CHAR (v_begintime, 'yyyymmdd')
|| '19:50',
'yyyymmdd HH24:mi:ss'
)
)
WHERE workno = v_workno
AND cardtime <= rec.ondutytime + 1 / 24
AND cardtime >= rec.ondutytime - 1 / 24;
ELSE
UPDATE kqm_bellcarddata
SET cardtime =
cardtime
- 12 / 24
- ( v_begintime
- TO_DATE ( TO_CHAR (v_begintime, 'yyyymmdd')
|| '19:50',
'yyyymmdd HH24:mi:ss'
)
),
readtime =
readtime
- 12 / 24
- ( v_begintime
- TO_DATE ( TO_CHAR (v_begintime, 'yyyymmdd')
|| '19:50',
'yyyymmdd HH24:mi:ss'
)
)
WHERE workno = v_workno
AND cardtime <= rec.ondutytime + 1 / 24
AND cardtime >= rec.ondutytime - 1 / 24;
END IF;
END IF;
END IF;
IF rec.otoffdutytime IS NOT NULL
THEN
SELECT COUNT (*)
INTO v_otoffdutytime
FROM kqm_bellcarddata
WHERE workno = v_workno
AND cardtime <= rec.otoffdutytime + 1 / 24
AND cardtime >= rec.otoffdutytime - 1 / 24;
IF v_otoffdutytime > 0
AND TO_CHAR (rec.otoffdutytime, 'yyyy/MM/dd') <> '2010/01/01'
THEN
SELECT COUNT (*)
INTO v_a4
FROM kqm_bellcarddata
WHERE workno = v_workno
AND cardtime >= cardtime - 12/ 24
AND cardtime =
TO_DATE (TO_CHAR (cardtime, 'yyyymmdd') || '07:50',
'yyyymmdd HH24:mi:ss'
);
IF v_a4 > 0
THEN
UPDATE kqm_bellcarddata
SET cardtime =
cardtime
- 12 / 24
- 1 / 1440
- ( v_begintime
- TO_DATE ( TO_CHAR (v_begintime, 'yyyymmdd')
|| '19:50',
'yyyymmdd HH24:mi:ss'
)
),
readtime =
readtime
- 12 / 24
- ( v_begintime
- TO_DATE ( TO_CHAR (v_begintime, 'yyyymmdd')
|| '19:50',
'yyyymmdd HH24:mi:ss'
)
)
WHERE workno = v_workno
AND cardtime <= rec.otoffdutytime + 1 / 24
AND cardtime >= rec.otoffdutytime - 1 / 24;
ELSE
UPDATE kqm_bellcarddata
SET cardtime =
cardtime
- 12 / 24
- ( v_begintime
- TO_DATE ( TO_CHAR (v_begintime, 'yyyymmdd')
|| '19:50',
'yyyymmdd HH24:mi:ss'
)
),
readtime =
readtime
- 12 / 24
- ( v_begintime
- TO_DATE ( TO_CHAR (v_begintime, 'yyyymmdd')
|| '19:50',
'yyyymmdd HH24:mi:ss'
)
)
WHERE workno = v_workno
AND cardtime <= rec.otoffdutytime + 1 / 24
AND cardtime >= rec.otoffdutytime - 1 / 24;
END IF;
END IF;
END IF;
IF TO_CHAR (rec.kqdate, 'yyyy/MM/dd') <> '2010/01/01'
THEN
UPDATE kqm_kaoqindata
SET ondutytime =
ondutytime
- 12 / 24
- ( v_begintime
- TO_DATE ( TO_CHAR (v_begintime, 'yyyymmdd')
|| '19:50',
'yyyymmdd HH24:mi:ss'
)
),
offdutytime =
TO_DATE (TO_CHAR (v_begintime, 'yyyymmdd') || '17:40',
'yyyymmdd HH24:mi:ss'
),
otondutytime =
TO_DATE (TO_CHAR (v_begintime, 'yyyymmdd') || '17:40',
'yyyymmdd HH24:mi:ss'
),
otoffdutytime =
otoffdutytime
- 12 / 24
- ( v_begintime
- TO_DATE ( TO_CHAR (v_begintime, 'yyyymmdd')
|| '19:50',
'yyyymmdd HH24:mi:ss'
)
),
shiftno = 'A611'
WHERE workno = v_workno AND kqdate = rec.kqdate;
END IF;
IF v_temp > 0
THEN
UPDATE otm_advanceapply
SET endtime =
endtime
- 12 / 24
- ( v_begintime
- TO_DATE ( TO_CHAR (v_begintime, 'yyyymmdd')
|| '19:50',
'yyyymmdd HH24:mi:ss'
)
),
begintime =
TO_DATE (TO_CHAR (begintime, 'yyyymmdd') || '17:40',
'yyyymmdd HH24:mi:ss'
),
otshiftno = 'A611'
WHERE otdate = rec.kqdate AND workno = v_workno;
UPDATE otm_realapply
SET endtime =
endtime
- 12 / 24
- ( v_begintime
- TO_DATE ( TO_CHAR (v_begintime, 'yyyymmdd')
|| '19:50',
'yyyymmdd HH24:mi:ss'
)
),
begintime =
TO_DATE (TO_CHAR (v_begintime, 'yyyymmdd') || '17:40',
'yyyymmdd HH24:mi:ss'
),
ondutytime =
ondutytime
- 12 / 24
- ( v_begintime
- TO_DATE ( TO_CHAR (v_begintime, 'yyyymmdd')
|| '19:50',
'yyyymmdd HH24:mi:ss'
)
),
offdutytime =
offdutytime
- 12 / 24
- ( v_begintime
- TO_DATE ( TO_CHAR (v_begintime, 'yyyymmdd')
|| '19:50',
'yyyymmdd HH24:mi:ss'
)
),
shiftno = 'A611'
WHERE otdate = rec.kqdate AND workno = v_workno;
END IF;
END LOOP;
END LOOP;
CLOSE find_workno;
COMMIT;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
WHEN OTHERS
THEN
RAISE;
END update_boykq_ding;
/
CREATE OR REPLACE PROCEDURE NEWHR.del_sundaykq_ding (pselectdate IN DATE)
IS
/******************************************************************************
創建人；丁樂進
創建日期：2010/7/9
******************************************************************************/
v_workno VARCHAR (30);
v_projectid VARCHAR (36);
v_otdate DATE;
CURSOR find_workno
IS
SELECT workno, otdate
FROM otm_advanceapply
WHERE isproject = 'Y' AND otdate >= pselectdate;
BEGIN
DELETE FROM otm_realapply
WHERE TO_CHAR (otdate, 'd') = '1' AND otdate >= pselectdate;
DELETE FROM otm_advanceapply
WHERE TO_CHAR (otdate, 'd') = '1' AND otdate >= pselectdate;
DELETE FROM kqm_bellcarddata
WHERE TO_CHAR (cardtime, 'd') = '1' AND cardtime >= pselectdate;
OPEN find_workno;
LOOP
FETCH find_workno
INTO v_workno, v_otdate;
EXIT WHEN find_workno%NOTFOUND;
SELECT ID
INTO v_projectid
FROM otm_advanceapply
WHERE isproject = 'Y' AND otdate = v_otdate AND workno = v_workno;
DELETE FROM otm_advanceapply
WHERE isproject = 'Y' AND otdate = v_otdate AND ID = v_projectid;
DELETE FROM otm_realapply
WHERE advanceid = v_projectid;
END LOOP;
CLOSE find_workno;
UPDATE kqm_kaoqindata
SET ondutytime = '',
offdutytime = '',
exceptiontype = 'F',
othours = '',
workhours = ''
WHERE TO_CHAR (kqdate, 'd') = '1' AND kqdate >= pselectdate;
COMMIT;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
WHEN OTHERS
THEN
-- Consider logging the error and then re-raise
RAISE;
END del_sundaykq_ding;
/
CREATE OR REPLACE PROCEDURE NEWHR.update_boykq_ding
IS
/******************************************************************************
創建人：丁樂進
創建時間：2010/7/9
用途：保密
******************************************************************************/
v_dt VARCHAR (20);
v_workno VARCHAR (20);
v_begintime DATE;
v_workshift NUMBER;
v_temp NUMBER;
v_ondutytime NUMBER;
v_otoffdutytime NUMBER;
v_r1 NUMBER;
v_r2 NUMBER;
v_a1 NUMBER;
v_a2 NUMBER;
v_a3 NUMBER;
v_a4 NUMBER;
CURSOR find_workno
IS
SELECT dt, workno
FROM (SELECT TO_CHAR (TO_NUMBER (TO_CHAR (borndate, 'yyyy')) + 18
)
|| TO_CHAR (borndate, 'mmdd') dt,
borndate, workno
FROM hrm_employees)
WHERE dt >= '20090801';
BEGIN
OPEN find_workno;
LOOP
FETCH find_workno
INTO v_dt, v_workno;
EXIT WHEN find_workno%NOTFOUND;
SELECT COUNT (*)
INTO v_workshift
FROM kqm_employeeshift
WHERE SUBSTR (shiftno, 0, 1) = 'C'
AND workno = v_workno
AND TO_CHAR (startdate, 'yyyy/MM/dd') >= '2009/08/01'
AND TO_CHAR (enddate, 'yyyyMMdd') <= v_dt;
IF v_workshift > 0
THEN
UPDATE kqm_employeeshift
SET shiftno = 'A611'
WHERE workno = v_workno
AND SUBSTR (shiftno, 0, 1) = 'C'
AND TO_CHAR (startdate, 'yyyy/MM/dd') >= '2009/08/01'
AND TO_CHAR (enddate, 'yyyyMMdd') <= v_dt;
END IF;
FOR rec IN (SELECT ondutytime, offdutytime, kqdate
FROM kqm_kaoqindata
WHERE workno = v_workno
AND TO_CHAR (kqdate, 'yyyy/MM/dd') >= '2009/08/01'
AND TO_CHAR (kqdate, 'yyyyMMdd') <= v_dt
AND SUBSTR (shiftno, 0, 1) = 'C'
AND othours = 0
ORDER BY kqdate)
LOOP
SELECT ondutytime
INTO v_begintime
FROM kqm_kaoqindata
WHERE workno = v_workno AND ondutytime = rec.ondutytime;
IF rec.ondutytime IS NOT NULL
THEN
SELECT COUNT (*)
INTO v_r1
FROM kqm_bellcarddata
WHERE workno = v_workno
AND cardtime <= rec.ondutytime + 1 / 24
AND cardtime >= rec.ondutytime - 1 / 24;
IF v_r1 > 0
THEN
SELECT COUNT (*)
INTO v_a1
FROM kqm_bellcarddata
WHERE workno = v_workno
AND cardtime =
TO_DATE (TO_CHAR (v_begintime, 'yyyymmdd') || '07:50',
'yyyymmdd HH24:mi:ss'
);
IF v_a1 > 0
THEN
UPDATE kqm_bellcarddata
SET cardtime =
cardtime
- 12 / 24
- 1 / 1440
- ( v_begintime
- TO_DATE ( TO_CHAR (v_begintime, 'yyyymmdd')
|| '19:50',
'yyyymmdd HH24:mi:ss'
)
),
readtime =
readtime
- 12 / 24
- ( v_begintime
- TO_DATE ( TO_CHAR (v_begintime, 'yyyymmdd')
|| '19:50',
'yyyymmdd HH24:mi:ss'
)
)
WHERE workno = v_workno
AND cardtime <= rec.ondutytime + 1 / 24
AND cardtime >= rec.ondutytime - 1 / 24;
ELSE
UPDATE kqm_bellcarddata
SET cardtime =
cardtime
- 12 / 24
- ( v_begintime
- TO_DATE ( TO_CHAR (v_begintime, 'yyyymmdd')
|| '19:50',
'yyyymmdd HH24:mi:ss'
)
),
readtime =
readtime
- 12 / 24
- ( v_begintime
- TO_DATE ( TO_CHAR (v_begintime, 'yyyymmdd')
|| '19:50',
'yyyymmdd HH24:mi:ss'
)
)
WHERE workno = v_workno
AND cardtime <= rec.ondutytime + 1 / 24
AND cardtime >= rec.ondutytime - 1 / 24;
END IF;
END IF;
END IF;
IF rec.offdutytime IS NOT NULL
THEN
SELECT COUNT (*)
INTO v_r2
FROM kqm_bellcarddata
WHERE workno = v_workno
AND cardtime <= rec.offdutytime + 1 / 24
AND cardtime >= rec.offdutytime - 1 / 24;
IF v_r2 > 0
AND TO_CHAR (rec.offdutytime, 'yyyy/MM/dd') <> '2010/01/01'
THEN
SELECT COUNT (*)
INTO v_a2
FROM kqm_bellcarddata
WHERE workno = v_workno
AND cardtime =
TO_DATE (TO_CHAR (v_begintime, 'yyyymmdd') || '07:50',
'yyyymmdd HH24:mi:ss'
);
IF v_a2 > 0
THEN
UPDATE kqm_bellcarddata
SET cardtime =
cardtime
- 12 / 24
- 1 / 1440
- ( v_begintime
- TO_DATE ( TO_CHAR (v_begintime, 'yyyymmdd')
|| '19:50',
'yyyymmdd HH24:mi:ss'
)
),
readtime =
readtime
- 12 / 24
- ( v_begintime
- TO_DATE ( TO_CHAR (v_begintime, 'yyyymmdd')
|| '19:50',
'yyyymmdd HH24:mi:ss'
)
)
WHERE workno = v_workno
AND cardtime <= rec.offdutytime + 1 / 24
AND cardtime >= rec.offdutytime - 1 / 24;
ELSE
UPDATE kqm_bellcarddata
SET cardtime =
cardtime
- 12 / 24
- ( v_begintime
- TO_DATE ( TO_CHAR (v_begintime, 'yyyymmdd')
|| '19:50',
'yyyymmdd HH24:mi:ss'
)
),
readtime =
readtime
- 12 / 24
- ( v_begintime
- TO_DATE ( TO_CHAR (v_begintime, 'yyyymmdd')
|| '19:50',
'yyyymmdd HH24:mi:ss'
)
)
WHERE workno = v_workno
AND cardtime <= rec.offdutytime + 1 / 24
AND cardtime >= rec.offdutytime - 1 / 24;
END IF;
END IF;
END IF;
IF TO_CHAR (rec.kqdate, 'yyyy/MM/dd') <> '2010/01/01'
THEN
UPDATE kqm_kaoqindata
SET ondutytime =
ondutytime
- 12 / 24
- ( v_begintime
- TO_DATE ( TO_CHAR (v_begintime, 'yyyymmdd')
|| '19:50',
'yyyymmdd HH24:mi:ss'
)
),
offdutytime =
offdutytime
- 12 / 24
- ( v_begintime
- TO_DATE ( TO_CHAR (v_begintime, 'yyyymmdd')
|| '19:50',
'yyyymmdd HH24:mi:ss'
)
),
shiftno = 'A611'
WHERE workno = v_workno AND kqdate = rec.kqdate;
END IF;
END LOOP;
FOR rec IN (SELECT ondutytime, otoffdutytime, kqdate
FROM kqm_kaoqindata
WHERE workno = v_workno
AND SUBSTR (shiftno, 0, 1) = 'C'
AND TO_CHAR (kqdate, 'yyyy/MM/dd') >= '2009/08/01'
AND TO_CHAR (kqdate, 'yyyyMMdd') <= v_dt
AND othours > 0)
LOOP
SELECT ondutytime
INTO v_begintime
FROM kqm_kaoqindata
WHERE workno = v_workno AND ondutytime = rec.ondutytime;
SELECT COUNT (*)
INTO v_temp
FROM otm_advanceapply
WHERE workno = v_workno
AND otdate = rec.kqdate
AND SUBSTR (otshiftno, 0, 1) = 'C';
IF rec.ondutytime IS NOT NULL
THEN
SELECT COUNT (*)
INTO v_ondutytime
FROM kqm_bellcarddata
WHERE workno = v_workno
AND cardtime <= rec.ondutytime + 1 / 24
AND cardtime >= rec.ondutytime - 1 / 24;
IF v_ondutytime > 0
THEN
SELECT COUNT (*)
INTO v_a3
FROM kqm_bellcarddata
WHERE workno = v_workno
AND cardtime =
TO_DATE (TO_CHAR (v_begintime, 'yyyymmdd') || '07:50',
'yyyymmdd HH24:mi:ss'
);
IF v_a3 > 0
THEN
UPDATE kqm_bellcarddata
SET cardtime =
cardtime
- 12 / 24
- 1 / 1440
- ( v_begintime
- TO_DATE ( TO_CHAR (v_begintime, 'yyyymmdd')
|| '19:50',
'yyyymmdd HH24:mi:ss'
)
),
readtime =
readtime
- 12 / 24
- ( v_begintime
- TO_DATE ( TO_CHAR (v_begintime, 'yyyymmdd')
|| '19:50',
'yyyymmdd HH24:mi:ss'
)
)
WHERE workno = v_workno
AND cardtime <= rec.ondutytime + 1 / 24
AND cardtime >= rec.ondutytime - 1 / 24;
ELSE
UPDATE kqm_bellcarddata
SET cardtime =
cardtime
- 12 / 24
- ( v_begintime
- TO_DATE ( TO_CHAR (v_begintime, 'yyyymmdd')
|| '19:50',
'yyyymmdd HH24:mi:ss'
)
),
readtime =
readtime
- 12 / 24
- ( v_begintime
- TO_DATE ( TO_CHAR (v_begintime, 'yyyymmdd')
|| '19:50',
'yyyymmdd HH24:mi:ss'
)
)
WHERE workno = v_workno
AND cardtime <= rec.ondutytime + 1 / 24
AND cardtime >= rec.ondutytime - 1 / 24;
END IF;
END IF;
END IF;
IF rec.otoffdutytime IS NOT NULL
THEN
SELECT COUNT (*)
INTO v_otoffdutytime
FROM kqm_bellcarddata
WHERE workno = v_workno
AND cardtime <= rec.otoffdutytime + 1 / 24
AND cardtime >= rec.otoffdutytime - 1 / 24;
IF v_otoffdutytime > 0
AND TO_CHAR (rec.otoffdutytime, 'yyyy/MM/dd') <> '2010/01/01'
THEN
SELECT COUNT (*)
INTO v_a4
FROM kqm_bellcarddata
WHERE workno = v_workno
AND cardtime >= cardtime - 12/ 24
AND cardtime =
TO_DATE (TO_CHAR (cardtime, 'yyyymmdd') || '07:50',
'yyyymmdd HH24:mi:ss'
);
IF v_a4 > 0
THEN
UPDATE kqm_bellcarddata
SET cardtime =
cardtime
- 12 / 24
- 1 / 1440
- ( v_begintime
- TO_DATE ( TO_CHAR (v_begintime, 'yyyymmdd')
|| '19:50',
'yyyymmdd HH24:mi:ss'
)
),
readtime =
readtime
- 12 / 24
- ( v_begintime
- TO_DATE ( TO_CHAR (v_begintime, 'yyyymmdd')
|| '19:50',
'yyyymmdd HH24:mi:ss'
)
)
WHERE workno = v_workno
AND cardtime <= rec.otoffdutytime + 1 / 24
AND cardtime >= rec.otoffdutytime - 1 / 24;
ELSE
UPDATE kqm_bellcarddata
SET cardtime =
cardtime
- 12 / 24
- ( v_begintime
- TO_DATE ( TO_CHAR (v_begintime, 'yyyymmdd')
|| '19:50',
'yyyymmdd HH24:mi:ss'
)
),
readtime =
readtime
- 12 / 24
- ( v_begintime
- TO_DATE ( TO_CHAR (v_begintime, 'yyyymmdd')
|| '19:50',
'yyyymmdd HH24:mi:ss'
)
)
WHERE workno = v_workno
AND cardtime <= rec.otoffdutytime + 1 / 24
AND cardtime >= rec.otoffdutytime - 1 / 24;
END IF;
END IF;
END IF;
IF TO_CHAR (rec.kqdate, 'yyyy/MM/dd') <> '2010/01/01'
THEN
UPDATE kqm_kaoqindata
SET ondutytime =
ondutytime
- 12 / 24
- ( v_begintime
- TO_DATE ( TO_CHAR (v_begintime, 'yyyymmdd')
|| '19:50',
'yyyymmdd HH24:mi:ss'
)
),
offdutytime =
TO_DATE (TO_CHAR (v_begintime, 'yyyymmdd') || '17:40',
'yyyymmdd HH24:mi:ss'
),
otondutytime =
TO_DATE (TO_CHAR (v_begintime, 'yyyymmdd') || '17:40',
'yyyymmdd HH24:mi:ss'
),
otoffdutytime =
otoffdutytime
- 12 / 24
- ( v_begintime
- TO_DATE ( TO_CHAR (v_begintime, 'yyyymmdd')
|| '19:50',
'yyyymmdd HH24:mi:ss'
)
),
shiftno = 'A611'
WHERE workno = v_workno AND kqdate = rec.kqdate;
END IF;
IF v_temp > 0
THEN
UPDATE otm_advanceapply
SET endtime =
endtime
- 12 / 24
- ( v_begintime
- TO_DATE ( TO_CHAR (v_begintime, 'yyyymmdd')
|| '19:50',
'yyyymmdd HH24:mi:ss'
)
),
begintime =
TO_DATE (TO_CHAR (begintime, 'yyyymmdd') || '17:40',
'yyyymmdd HH24:mi:ss'
),
otshiftno = 'A611'
WHERE otdate = rec.kqdate AND workno = v_workno;
UPDATE otm_realapply
SET endtime =
endtime
- 12 / 24
- ( v_begintime
- TO_DATE ( TO_CHAR (v_begintime, 'yyyymmdd')
|| '19:50',
'yyyymmdd HH24:mi:ss'
)
),
begintime =
TO_DATE (TO_CHAR (v_begintime, 'yyyymmdd') || '17:40',
'yyyymmdd HH24:mi:ss'
),
ondutytime =
ondutytime
- 12 / 24
- ( v_begintime
- TO_DATE ( TO_CHAR (v_begintime, 'yyyymmdd')
|| '19:50',
'yyyymmdd HH24:mi:ss'
)
),
offdutytime =
offdutytime
- 12 / 24
- ( v_begintime
- TO_DATE ( TO_CHAR (v_begintime, 'yyyymmdd')
|| '19:50',
'yyyymmdd HH24:mi:ss'
)
),
shiftno = 'A611'
WHERE otdate = rec.kqdate AND workno = v_workno;
END IF;
END LOOP;
END LOOP;
CLOSE find_workno;
COMMIT;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
WHEN OTHERS
THEN
RAISE;
END update_boykq_ding;
/
CREATE OR REPLACE PROCEDURE NEWHR.del_sundaykq_ding (pselectdate IN DATE)
IS
/******************************************************************************
創建人；丁樂進
創建日期：2010/7/9
******************************************************************************/
v_workno VARCHAR (30);
v_projectid VARCHAR (36);
v_otdate DATE;
CURSOR find_workno
IS
SELECT workno, otdate
FROM otm_advanceapply
WHERE isproject = 'Y' AND otdate >= pselectdate;
BEGIN
DELETE FROM otm_realapply
WHERE TO_CHAR (otdate, 'd') = '1' AND otdate >= pselectdate;
DELETE FROM otm_advanceapply
WHERE TO_CHAR (otdate, 'd') = '1' AND otdate >= pselectdate;
DELETE FROM kqm_bellcarddata
WHERE TO_CHAR (cardtime, 'd') = '1' AND cardtime >= pselectdate;
OPEN find_workno;
LOOP
FETCH find_workno
INTO v_workno, v_otdate;
EXIT WHEN find_workno%NOTFOUND;
SELECT ID
INTO v_projectid
FROM otm_advanceapply
WHERE isproject = 'Y' AND otdate = v_otdate AND workno = v_workno;
DELETE FROM otm_advanceapply
WHERE isproject = 'Y' AND otdate = v_otdate AND ID = v_projectid;
DELETE FROM otm_realapply
WHERE advanceid = v_projectid;
END LOOP;
CLOSE find_workno;
UPDATE kqm_kaoqindata
SET ondutytime = '',
offdutytime = '',
exceptiontype = 'F',
othours = '',
workhours = ''
WHERE TO_CHAR (kqdate, 'd') = '1' AND kqdate >= pselectdate;
COMMIT;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
WHEN OTHERS
THEN
-- Consider logging the error and then re-raise
RAISE;
END del_sundaykq_ding;
/
分享到：
发表评论
-
Sql疑难问题
2011-11-06 11:30 723select ROW_NUMBER() OVER(ORDER ... -
oracle重要的函数
2011-07-31 22:25 889oracle函数（单行） 1. nvl(x1,x2),x1和x ... -
MySql格式化字符串
2011-07-31 20:44 2006DATE_FORMA T(date, format) 根据格式 ... -
splite数据库学习心得
2011-03-19 18:23 1360这篇文章是看网友牛腩的文章后，提炼的精要笔记 1、到http ... -
在Oracle中實現類似自動增加ID的功能
2011-03-19 17:55 733創建序列 create sequence SEQ minv ... -
Sql的高级应用技巧
2011-03-16 21:38 784//獲取table表的列名 ，適用於SQl server ,對 ... -
intersect, minus(转载)
2011-03-16 21:27 594intersect, minus intersect运算:返回 ... -
临时表的概念
2011-03-16 21:26 744//SQL server 在創建表的時候添加了“#”首碼的表 ... -
MSSQL分页
2010-08-09 16:08 958有些控件自身带的 ... -
Oracle分页存储过程
2010-08-09 15:29 1093首先在oracle中创建包，包可以比喻成篮子，存储过程好比是苹 ...
相关推荐
### Oracle存储过程开发基本规范详解 #### 一、概述 Oracle存储过程作为一种强大的数据库编程工具，在企业级应用中被广泛采用。为了确保存储过程的质量、可读性和可维护性，制定一套合理的编码规范至关重要。本文将...
### Oracle存储过程、游标、函数的详解 #### 一、概述 在Oracle数据库中，存储过程、游标和函数是非常重要的组成部分，它们为数据库管理提供了强大的编程能力。通过学习这些概念，我们可以更加灵活地管理和操作...
### Oracle存储过程加密知识点 #### 一、Oracle存储过程与加密背景 在Oracle数据库中，存储过程是一种数据库对象，它可以包含一系列SQL语句和过程化SQL代码，用于执行复杂的业务逻辑。存储过程不仅可以提高应用...
本文档中提供的五个Oracle存储过程练习题，正好能帮助开发者加深对Oracle存储过程应用的理解和实践。 首先，创建用户和分配权限是Oracle数据库管理中的基础工作。通过创建特定的用户并为其分配适当的权限，可以确保...
"使用OLE-DB和ADO调用返回记录集的Oracle存储过程" 摘要：本文介绍了如何使用OLE DB和ADO调用返回记录集的Oracle存储过程。OLE DB是一个开放规范，提供了对不同类型数据的访问和操纵标准。ADO是OLE DB的消费者，...
在这个“VC做的小东西，测试oracle存储过程”项目中，我们可以看到这两个技术的结合，以实现对Oracle数据库存储过程的测试。 首先，让我们深入理解一下Oracle存储过程。存储过程是预编译的SQL语句集合，存储在...
Oracle存储过程是数据库管理系统中一组为了完成特定任务而编写的SQL和PL/SQL语句集合。它们被编译并存储在数据库中，可以被多次调用，提高了代码的复用性和执行效率。以下是对Oracle存储过程语法的详细解析： 1. **...
Oracle存储过程是预编译的SQL语句集合，它可以提高执行效率，减少网络通信，并且能够封装复杂的业务逻辑。 首先，我们需要理解C++与Oracle数据库交互的基本原理。通常，这会通过一个名为ODBC（Open Database ...
综上所述，Oracle存储过程和PL/SQL编程是数据库管理和应用开发中非常重要的技能点。掌握这些技术点，可以帮助开发者编写出更加高效、安全和可维护的数据库应用。在实际应用中，开发者应充分利用存储过程和PL/SQL提供...
Oracle存储过程编码规范的主要目的是为了标准化开发流程，使代码易于理解、调试和维护。通过遵循统一的规范，开发人员可以快速地理解和修改他人编写的代码，降低项目风险，提高软件的可靠性和可扩展性。 2. 名词...
执行Oracle存储过程主要涉及以下几个关键点： 1. 创建CallableStatement对象：使用Connection对象的`prepareCall()`方法创建CallableStatement实例，例如`CallableStatement cs = conn.prepareCall("{call procedure...
在Java中调用Oracle存储过程，我们需要使用JDBC（Java Database Connectivity），这是Java标准库提供的一组接口和类，用于与各种数据库进行通信。首先，我们需要添加Oracle JDBC驱动到项目中，例如ojdbc.jar。 1. *...
水晶报表连接Oracle存储过程实例 本文将详细介绍如何使用水晶报表连接Oracle存储过程实例，从而实现数据报表的自动化生成。我们将从创建 Oracle 存储过程开始，接着指导读者如何在水晶报表中应用该存储过程。 一、...
本文将深入探讨如何使用Java来调用Oracle存储过程，特别是涉及游标的场景。 首先，Oracle存储过程是一种预编译的SQL和PL/SQL代码块，可以在数据库服务器端执行，提供了一种封装业务逻辑的方式。而游标（Cursor）在...
本资源“Oracle学习开发常用的SQL和存储过程学习(内含SQL面试题目和存储过程，函数面试题目).zip”提供了全面的学习材料，旨在帮助初学者和求职者提升在Oracle数据库中的SQL查询和存储过程编写技能。 SQL...
本篇将深入探讨如何使用JDBC来调用Oracle的存储过程并处理返回值。 首先，了解存储过程的概念。存储过程是预编译的SQL语句集合，它们存储在数据库服务器中，可以接受参数，执行一系列操作，并可能返回结果。在...
Oracle 存储过程 exception 异常处理大全及实例经典最终 Oracle 存储过程 exception 异常处理大全及实例经典最终是 Oracle 数据库中存储过程的异常处理机制。异常处理是指在程序执行过程中出现的错误或未预料到的...
标题“Oracle奖学金评定存储过程”与描述“Oracle存储过程奖学金评定存储过程”共同揭示了该存储过程的主要功能：用于评定奖学金。具体来说，这个存储过程设计用于根据学生的成绩和课程学分来计算平均绩点（GPA），...
### Oracle存储过程实现加密 #### 一、引言 随着互联网技术的发展，信息安全问题日益凸显，特别是对于希望转型成为电子商务的企业来说，数据安全至关重要。在众多保护数据安全的方法中，加密技术扮演着不可或缺的...
**JDBC与Oracle存储过程详解** Java Database Connectivity (JDBC) 是Java编程语言中用于连接数据库的标准API，它使得Java开发者能够与各种数据库系统进行交互，包括Oracle数据库。Oracle存储过程是预编译的SQL语句...