oracle procedure

记录的目的在于方便查询

CREATE OR REPLACE PROCEDURE xx.p_deal_ABC_INFO
IS
   v_begin_as       DATE;
   v_begin_report   DATE;
   i_sqlcode        VARCHAR2 (4000);
   v_sqlerrm        VARCHAR2 (4000);

   CURSOR v_cursor_com
   IS
      SELECT   COM_ID
        FROM   xx.COM_INFO a
       WHERE   a.SEARCH_FLAG IN ('1', '2');
BEGIN
   BEGIN
      FOR c_temp IN v_cursor_com
      LOOP
         v_begin_as := NULL;
         v_begin_report := NULL;

         SELECT   EGIN_DATE
           INTO   v_begin_report
           FROM   xx.BOOK_INFO
          WHERE   COM_ID = c_temp.com_id;

         SELECT   BEGIN_DATE
           INTO   v_begin_as
           FROM   xx.LIBRARY
          WHERE   COM_ID = c_temp.com_id;

         IF (v_begin_report > v_begin_as)
         THEN
            UPDATE   xx.USER
               SET   FLAG = '0'
             WHERE   MEMBER_ID =100;  
         ELSE
            UPDATE   xx.USER
               SET   REPORT_xx = '0'
             WHERE   MEMBER_ID =1000;
                        
         END IF;

         COMMIT;
      END LOOP;

      -----更新xx.yy
INSERT INTO xx.CHAIR_CHAIR (REC_ID,
                                        ID,
                                        URL,
                                        UPDATER_NO,
                                        UPDATER_IP,
                                        UPDATED_TIME)
(SELECT   xx.s_CHAIR_CHAIR.NEXTVAL,
          a.com_id,
          '0',
          '00000000',
          '127.0.0.1',
          SYSDATE
   FROM   xx.abc

      COMMIT;
   END;
EXCEPTION
   WHEN OTHERS
   THEN
      ROLLBACK;
      i_sqlcode := SQLCODE;
      v_sqlerrm := SUBSTR (SQLERRM, 1, 512);

      INSERT INTO xx.error_log
        VALUES   (i_sqlcode,
                  v_sqlerrm,
                  'P_DEAL_ABC_INFO',
                  USER,
                  SYSDATE);

      COMMIT;
END P_DEAL_ABC_INFO;
/