把表rename然后重建分区表的一个存储过程(转)

储过程注视很详细了,不多说了

CREATE OR REPLACE PROCEDURE Altertabletopartition_Zxt(Tablename IN VARCHAR2,
Midtablename IN VARCHAR2 DEFAULT '',
Partitioncolomn IN VARCHAR2,
Partitionstartstr IN VARCHAR2 DEFAULT '',
Partitionendstr IN VARCHAR2 DEFAULT '',
Partitioncolomntype IN CHAR DEFAULT '1',
Partitiontype IN CHAR DEFAULT '1',
Parttablespace IN VARCHAR2 DEFAULT NULL,
Varstart IN VARCHAR2 DEFAULT '0',
Varinceraseby IN NUMBER DEFAULT 1,
Varend IN VARCHAR2 DEFAULT '',
Dropmidtabornot IN CHAR DEFAULT '1'--,
--State OUT CHAR
) IS
Mymidtablename VARCHAR2(30); --中间表的名称
Partitiondml VARCHAR2(32767); --分区的DML、最大长度
Mypartitiontype VARCHAR2(10); --分区类型
Partitiontypesql VARCHAR2(20); --分区DDL；
Myvarstart VARCHAR2(30); --起始字段
Myvarend VARCHAR2(30); --终止字段
i INTEGER; --循环
Uu INTEGER; --用以分200字节打印分区DDL；
g INTEGER; --对于日期类型的分区字段，在首次循环，不增加递增模式，Varinceraseby*0=0；
--在下次循环中再按照递增模式增加 Varinceraseby*1=Varinceraseby
BEGIN
i := 0;
g := 0;
/*
--Z.X.T
--20070129
Q:由于数据仓库开发过程中可能可能存在建表的时候没有考虑到分区，在以后的应用中会增加查询的负担和相应时间
A:本过程把一个非分区表转换成分区表，只考虑在原表上的一层分区，不考虑subpartition
分区逻辑：本程序采用先rename 表，然后再按照中间表创建原始表，同时加上分区的属性
对于LIST类型，只提供按照一个类型的分区，也就是说只能PARTITION ** VALUES ('CT')而不能PARTITION ** VALUES ('CT','RI'..)
过程是用于下列情况，1：表数据在修改时间没有变化,对于时时变化的表，建议用oracle的在线重定义来实现！
2：分区字段Varinceraseby有递增模式，如：2004、2005、2006
3：新增对于字段内部有递增方式的，如GLFW01AA、GLFW02AA、在第5位和第6位上有递增模式，
需传partitionstartstr=>'GLFW';partitionendstr=>'AA'
4：分区会删掉所有索引等
5：如果表上有物化试图日志，则应先drop掉，否则汇报错！
Tablename :原tableName
Midtablename :过程中间的表名,如果没有传Midtablename则用Tablename构造Midtablename
Partitioncolomn :原表上分区的字段名称
partitionstartpoint :分区字段的分区起始位置，如列为GLFW01、GLFW02等则可以作为list的分区方式分区
partitionstartpoint为01、02在列中的起始位置
Partitioncolomntype :要分区的字段类型，1--varchar2、2--number、3--char、4--date
parttablespace :分区表空间，为空则为当前用户的表空间
Partitiontype :分区类型1--Range、2--List、3--Hash
Varstart :分区字段（Partitioncolomn）的起始,对于hash则不需要传入,只要传入Varinceraseby(用来表示要分几个区)就行
Varinceraseby :分区字段的增长方式，如：起始为2004年，以1增长则第二个分区就是2004+1=2005,对于hash方式则表示hash分区的个数
Varend :分区字段（Partitioncolomn）的终止,对于hash则不需要
Dropmidtabornot :需不需要drop中间表1--需要、2不需要
--State :返回成功分区标志1--成功、2--不成功
--example
1:
execute altertabletopartition_zxt('f_fix_fixreport','f_fix_fixreport1','REPORTCODE','GLFW','','1','2','shuihu','1','1','17','2');
2:
execute Altertabletopartition_Zxt('f_reg_levyauthinfo','','AUTHDATE','','','4','1','shuihu','20040101',1,'20070101','2');
Tablename IN VARCHAR(30) NOT NULL,
Midtablename IN VARCHAR(30) DEFAULT '',
Partitioncolomn IN VARCHAR2(30) NOT NULL,
partitionstartstr IN varchar2(30) default '',
partitionendstr IN varchar2(30) default '',
Partitioncolomntype IN CHAR(1) DEFAULT '1',
Partitiontype IN CHAR(1) DEFAULT '1',
Parttablespace IN VARCHAR(30) DEFAULT '0',
Varstart IN VARCHAR2(11) DEFAULT '',
Varinceraseby IN NUMBER DEFAULT 1,
Varend IN VARCHAR2(11) DEFAULT '0',
Dropmidtabornot IN CHAR(1) DEFAULT '1',
State OUT CHAR(1)
*/
Dbms_Output.Put_Line('tablename=' || Tablename);
Dbms_Output.Put_Line('Midtablename=' || Midtablename);
Dbms_Output.Put_Line('Partitioncolomn=' || Partitioncolomn);
Dbms_Output.Put_Line('partitionstartstr=' || Partitionstartstr); --partitionendstr
Dbms_Output.Put_Line('partitionendstr=' || Partitionendstr);
Dbms_Output.Put_Line('Partitioncolomntype=' || Partitioncolomntype);
Dbms_Output.Put_Line('Parttablespace=' || Parttablespace);
Dbms_Output.Put_Line('Partitiontype=' || Partitiontype);
Dbms_Output.Put_Line('Varinceraseby=' || Varinceraseby);
Dbms_Output.Put_Line('Varstart=' || Varstart);
Dbms_Output.Put_Line('Varend=' || Varend);
Dbms_Output.Put_Line('Dropmidtabornot=' || Dropmidtabornot);
--对数据数据的校验
--Tablename 、Partitioncolomn不能为空
IF (Tablename IS NULL OR Partitioncolomn IS NULL) THEN
Dbms_Output.Put_Line('表名、分区字段名不能为空！');
GOTO Aa;
END IF;
--Tablename的长度--字段定义不能超过30位
IF (Lengthb(Tablename) > 30 OR Lengthb(Midtablename) > 30 OR Lengthb(Partitioncolomn) > 30 OR Lengthb(Parttablespace) > 30) THEN
Dbms_Output.Put_Line('字段名成太长！');
GOTO Aa;
END IF;
--初始化各数值
IF (Partitiontype = '3') THEN
--对于hash 分区，不能只指定1个分区（Varinceraseby=1）
IF (Varinceraseby = 1) THEN
Dbms_Output.Put_Line('对于hash 分区，不能只指定1个分区！');
GOTO Aa;
ELSE
Myvarstart := 0;
Myvarend := Varinceraseby;
END IF;
ELSE
Myvarstart := Varstart;
Myvarend := Varend;
IF (Myvarstart = Myvarend) THEN
Dbms_Output.Put_Line('指定一个分区没有意义！');
GOTO Aa;
END IF;
END IF;
----初始化各数值end;
--Partitioncolomntype必须为1、2、3、4
IF (Partitioncolomntype > '4' OR Partitioncolomntype < '1') THEN
GOTO Aa;
END IF;
--Partitiontype 必须为1--Range、2--List、3--Hash
IF (Partitiontype > '3' OR Partitiontype < '1') THEN
GOTO Aa;
ELSE
--初始化各字段
IF (Partitiontype = '3') THEN
Mypartitiontype := ' hash ';
Partitiontypesql := '';
ELSIF (Partitiontype = '2') THEN
Mypartitiontype := ' List ';
Partitiontypesql := ' VALUES ';
ELSIF (Partitiontype = '1') THEN
Mypartitiontype := ' Range ';
Partitiontypesql := ' VALUES LESS THAN ';
END IF;
----初始化各字段end;
END IF;
--对数据数据的校验 end;
--得到中间表Midtablename
--如果没有传Midtablename则用Tablename构造Midtablename
IF (Midtablename IS NOT NULL) THEN
Mymidtablename := Midtablename;
ELSE
Mymidtablename := Tablename;
--如果没有传Midtablename并且Midtablename<25则再其后加'_$zxt',如果Midtablename>25,则截取前25位加'_$zxt
IF (Lengthb(Midtablename) <= 25 AND Midtablename IS NOT NULL) THEN
Mymidtablename := Midtablename || '_$zxt';
ELSE
Mymidtablename := Substrb(Tablename, 0, 25) || '_$zxt';
END IF;
END IF;
Dbms_Output.Put_Line('myMidtablename=' || Mymidtablename);
--得到Midtablename end;
--将原标rename为中间表
Dbms_Output.Put_Line('renameDML=' || '
Alter Table ' || Tablename || ' rename To "' || Mymidtablename || '"');
IF (Length(Tablename) = Lengthb(Tablename)) THEN
EXECUTE IMMEDIATE '
Alter Table ' || Tablename || ' rename To ' || Mymidtablename;
ELSE
EXECUTE IMMEDIATE '
Alter Table ' || Tablename || ' rename To "' || Mymidtablename || '"';
END IF;
--将原标rename为中间表 end;
--构造分区语句
Partitiondml := 'PARTITION By' || Mypartitiontype || '(' || Partitioncolomn || ')
( ';
Dbms_Output.Put_Line('Myvarend=' || Myvarend);
IF (Partitioncolomntype = '4') --分区字段为日期或者月份类型字段
THEN
--为to_date('yyyymmdd')做准备
IF (Length(Varstart) = 6) THEN
Myvarstart := Substr(Myvarstart, 0, 6) || '01';
Myvarend := Substr(Myvarend, 0, 6) || '01';
ELSE
Myvarstart := Substr(Myvarstart, 0, 8);
Myvarend := Substr(Myvarend, 0, 8);
END IF;
--为to_date('yyyymmdd')做准备 end;
WHILE (Add_Months(To_Date(Myvarstart, 'yyyymmdd'), Varinceraseby) <= To_Date(Myvarend, 'yyyymmdd'))
LOOP
Myvarstart := To_Char(Add_Months(To_Date(Myvarstart, 'yyyymmdd'), Varinceraseby * g), 'yyyymmdd');
IF (Length(Varstart) = 6) --月份,对于list类型的月份，将PARTITION ** VALUES ('20040701') 变成VALUES ('200407')
THEN
Myvarstart := Substr(Myvarstart, 0, 6);
END IF;
Dbms_Output.Put_Line('Myvarstart=' || i || '=' || Myvarstart);
--While ing
Partitiondml := Partitiondml || ' PARTITION ' || Partitioncolomn || i || Partitiontypesql;
IF (Partitiontype != '3') --不是hash分区
THEN
Partitiondml := Partitiondml || '( ''' || Myvarstart || ''' )';
END IF;
IF (Parttablespace IS NOT NULL) THEN
Partitiondml := Partitiondml || ' TABLESPACE ' || Parttablespace || ' ,';
ELSE
Partitiondml := Partitiondml || ',';
END IF;
i := i + 1;
IF (Length(Varstart) = 6) THEN
Myvarstart := Myvarstart || '01';
--Myvarend := Myvarend || '01';
END IF;
g := 1;
END LOOP;
ELSE
--不是日期型字段，年度、number等
WHILE (Myvarstart + Varinceraseby <= Myvarend)
LOOP
Myvarstart := Myvarstart + Varinceraseby * g;
Dbms_Output.Put_Line('Myvarstart=' || Myvarstart);
IF (Myvarstart < 10) THEN
Myvarstart := '0' || Myvarstart;
END IF;
Dbms_Output.Put_Line('Myvarstart=' || i || '=' || Myvarstart);
--While ing
Partitiondml := Partitiondml || ' PARTITION ' || Partitioncolomn || i || Partitiontypesql;
IF (Partitiontype != '3') --不是hash分区
THEN
Partitiondml := Partitiondml || '( ''' || Partitionstartstr || Myvarstart || Partitionendstr || ''' )';
END IF;
IF (Parttablespace IS NOT NULL) THEN
Partitiondml := Partitiondml || ' TABLESPACE ' || Parttablespace || ' ,';
ELSE
Partitiondml := Partitiondml || ',';
END IF;
i := i + 1;
g := 1;
END LOOP;
END IF;
--对Partitiondml的后续操作
IF (Partitiontype = '1') --1--Range--增加Maxvalue分区
THEN
Partitiondml := Partitiondml || ' PARTITION ' || Partitioncolomn || i || Partitiontypesql || '(Maxvalue)';
IF (Parttablespace IS NOT NULL) THEN
Partitiondml := Partitiondml || ' TABLESPACE ' || Parttablespace || ' )';
ELSE
Partitiondml := Partitiondml || ' ) ';
END IF;
ELSE
Partitiondml := Substr(Partitiondml, 0, Length(Partitiondml) - 1); ---去掉最后的一个逗号
Partitiondml := Partitiondml || ' ) ';
END IF;
----对Partitiondml的后续操作 end;
----构造分区语句 end;
--构造DDL;
IF (Length(Tablename) = Lengthb(Tablename)) THEN
Partitiondml := 'Create Table ' || Tablename || ' ' || Partitiondml || ' as Select * From ' || Mymidtablename;
ELSE
Partitiondml := 'Create Table ' || Tablename || ' ' || Partitiondml || ' as Select * From "' || Mymidtablename || '"';
END IF;
Dbms_Output.Put_Line('Partitiondml length=' || Length(Partitiondml));
Uu := Length(Partitiondml) / 200;
FOR i IN 1 .. Uu
LOOP
Dbms_Output.Put_Line(Substr(Partitiondml, (i - 1) * 200, 200));
END LOOP;
--构造DDL end;
--Execute
EXECUTE IMMEDIATE Partitiondml;
--删除中间表
IF (Dropmidtabornot = '1') THEN
IF (Length(Tablename) = Lengthb(Tablename)) THEN
EXECUTE IMMEDIATE '
drop Table ' || Mymidtablename||' cascade constraints';
ELSE
EXECUTE IMMEDIATE '
drop Table "' || Mymidtablename || '" cascade constraints';
END IF;
END IF;
----删除中间表end;
--State := '2';
GOTO aa;
--出错处理
<<aa>>
--State := '1';
Dbms_Output.Put_Line('State end ');
END Altertabletopartition_Zxt;