标准的Oracle存储过程

为什么我说它标准呢？因为这个存储过程中包括了，游标的使用，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;
/