按年分区的存储过程

CREATE OR REPLACE PROCEDURE p_add_year_part(p_tablename    IN VARCHAR2,
                                               p_logtablename IN VARCHAR2) AS
  v_sql     VARCHAR2(32767);
  v_logsql  VARCHAR2(4000);
  --v_minpart VARCHAR2(60);
  v_maxpart VARCHAR2(60);
  --v_sqlmin  VARCHAR2(1000);
  v_sqlmax  VARCHAR2(1000);
  v_partfw  VARCHAR2(60);
  v_partna  VARCHAR2(60);
  --v_wap     VARCHAR2(60);
  /*idx_name  user_ind_partitions.index_name%TYPE;
  subpart_name all_ind_subpartitions.partition_name%TYPE;
  TYPE dycurtype IS REF CURSOR;
  idx_name_cursor dycurtype;*/
BEGIN
--select * from user_tab_partitions

    /* 找出分区表最大的分区，把最大值 */
  v_sqlmax := 'select MAX(PARTITION_NAME)
               From user_tab_partitions
                    where table_name=''' || upper(p_tablename) || '''';
  EXECUTE IMMEDIATE v_sqlmax
    INTO v_maxpart; --这里为part_2016
  COMMIT;
  v_maxpart := substr(v_maxpart,6,4);
  v_sql     := 'select ' ||
               to_char(to_number(v_maxpart) + 1) ||
               ' from dual';
  EXECUTE IMMEDIATE v_sql
    INTO v_partna;   --v_partna为2017
  COMMIT;
 v_sql := 'select ' || to_char(to_number(v_maxpart + 2)) ||
                ' from dual';
 EXECUTE IMMEDIATE v_sql
   INTO v_partfw;
COMMIT;

  v_sql    := 'ALTER TABLE ' || p_tablename || ' ADD PARTITION PART_' ||
              v_partna || ' values less than ('|| v_partfw ||')
               compress nologging';
  v_logsql := 'INSERT INTO ' || p_logtablename ||
              '(context,content,sql,time) VALUES (:1,:2,:3,sysdate)';
  EXECUTE IMMEDIATE v_logsql
    USING 1, '添加分区', v_sql;
  COMMIT;
  EXECUTE IMMEDIATE v_sql;
  COMMIT;
  /* v_sql := 'ALTER TABLE ' || p_tablename || ' TRUNCATE PARTITION part_' || to_char(SYSDATE - 90, 'YYYYMMDD');
  v_logsql := 'INSERT INTO '||p_logtablename||'(context,content,sql,time) VALUES (:1,:2,:3,sysdate)';
  EXECUTE IMMEDIATE v_logsql
    USING 4,'清空不需要的数据（分区）',v_sql;
  COMMIT;
  EXECUTE IMMEDIATE v_sql;
  COMMIT;*/

  /*v_logsql := 'INSERT INTO ' || p_logtablename ||
              '(context,content,sql,time) VALUES (:1,:2,:3,sysdate)';
  EXECUTE IMMEDIATE v_logsql
    USING 4, 'END', '';
  COMMIT;*/
END p_add_year_part;