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;