定時清空MessageData表數據的存儲過程（15）-CSDN博客

create or replace
PROCEDURE             MESSAGEDATA_CLEAR_PROC
AS
    v_err_num NUMBER; --ORA错误号
    v_err_msg VARCHAR2(100); --错误描述
    v_daty_num NUMBER;---星期数
    v_day      VARCHAR2(20);
    v_SqlExec VARCHAR2(2000);
    v_time   VARCHAR2(20);

    ---为禁用或启用约束而定义的游标
    cursor cursor_const_part is
    select b.constraint_name
      from user_constraints a, user_cons_columns b
      where a.table_name=b.table_name
        and   a.constraint_name=b.constraint_name
        and   a.owner='TICKETS'
        and   a.table_name=upper('MessageData');

    ---为索引失效而重建索引定义游标
    cursor cursor_idx is
    select index_name
      from user_indexes
     where table_name = UPPER('MessageData')
       and status = 'UNUSABLE';

record_cursor_const_part cursor_const_part%rowtype;
record_cursor_idx cursor_idx%rowtype;

BEGIN

---暂时禁用约束
    open cursor_const_part;
    loop
      fetch cursor_const_part into record_cursor_const_part;
      exit when cursor_const_part%notfound;

      execute immediate 'alter table MessageData disable constraint '||record_cursor_const_part.constraint_name||' cascade';

    end loop;
    close cursor_const_part;

   select   to_char(sysdate,'yyyymmdd HH24:MI:SS') into v_day from dual;
   select   TO_NUMBER(TO_CHAR(TO_DATE(v_day,'yyyymmdd HH24:MI:SS'),'d')) into v_daty_num from dual;

   IF v_daty_num = 1 THEN
       v_time := 'MON';
   ELSIF v_daty_num = 2 THEN
        v_time := 'TUE';
   ELSIF v_daty_num = 3 THEN
        v_time := 'WED';
   ELSIF v_daty_num = 4 THEN
        v_time := 'THU';
ELSIF   v_daty_num = 5 THEN
        v_time := 'FRI';
ELSIF   v_daty_num = 6 THEN
         v_time := 'SAT';
   ELSIF v_daty_num = 7 THEN
         v_time := 'SUN';
   END IF;

v_SqlExec := 'ALTER TABLE MESSAGEDATA TRUNCATE PARTITION '|| v_time ;

   EXECUTE immediate v_SqlExec;


---启用约束
       open cursor_const_part;
         loop
          fetch cursor_const_part into record_cursor_const_part;
          exit when cursor_const_part%notfound;

           execute immediate 'alter table MESSAGEDATA enable novalidate constraint '||record_cursor_const_part.constraint_name;

         end loop;
         close cursor_const_part;

--重建失效的索引
        open cursor_idx;
        loop
          fetch cursor_idx into record_cursor_idx;
          exit when cursor_idx%notfound;

          execute immediate 'alter index ' ||record_cursor_idx.index_name||' rebuild';

         end loop;
        close cursor_idx;

EXCEPTION
WHEN OTHERS THEN
v_err_num := SQLCODE;
v_err_msg := SUBSTR(SQLERRM, 1, 100);
dbms_output.put_line('MESSAGEDATA_CLEAR_PROC执行出现异常,错误码='|| v_err_num || '错误描述=' || v_err_msg);
END MESSAGEDATA_CLEAR_PROC;

-- ALTER TABLE AlexMonitor.MESSAGEDATA TRUNCATE PARTITION MON;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26851211/viewspace-753068/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26851211/viewspace-753068/