oracle修改有数据的字段类型

/*
table_name 表名
old_column 字段名
column_type 要修改的字段类型 eg number 修改成 varchar2(20)
*/
create or replace procedure update_column_type(table_name in varchar2,
                                               old_column in varchar2 ,
                                               column_type in varchar2) as
  sqltext varchar2(1000);
begin
  sqltext := Q'[alter table ]' || table_name || Q'[ add (cp_]' || old_column ||' '||
             column_type || Q'[)]';
  execute immediate sqltext;
  dbms_output.put_line('备份列创建成功');
  sqltext := 'update ' || table_name || ' set cp_' || old_column || ' = ' ||
             old_column;
  execute immediate sqltext;
  dbms_output.put_line('备份列数据复制成功');
  sqltext := 'alter table ' || table_name || ' drop column ' || old_column;
  execute immediate sqltext;
  dbms_output.put_line('备份列删除成功');
  sqltext := 'alter table ' || table_name|| ' rename column cp_' || old_column || ' to ' ||
             old_column;
  execute immediate sqltext;
  dbms_output.put_line(old_column || '列数据类型修改成功');
end;

在数据库中执行存储过程，调用存储过程修改即可！