标准的Oracle存储过程

omhvuizuhh

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