修改oracle有数据字段长度

最新推荐文章于 2024-08-29 16:12:10 发布

yanleigis

最新推荐文章于 2024-08-29 16:12:10 发布

阅读量5.7k

点赞数

分类专栏： oracle 文章标签： oracle null

本文链接：https://blog.csdn.net/yanleigis/article/details/2473482

版权

oracle 专栏收录该内容

64 篇文章 0 订阅

订阅专栏

修改oracle有数据字段长度
create or replace function pc_modicolumnSize(ptableName in varchar2,
                                             pfieldName in varchar2,
                                             ptype      in varchar2)
  return integer is

  -- Author  : YANLEI
  -- Created : 2007-12-30 11:49:11
  -- Purpose : 修改字段长度

  sqlstr        varchar2(1024);
  tempFieldname varchar2(32);
begin
  tempFieldname := 'wwwXXX';
  begin
    sqlstr := 'alter table ' || ptableName || ' add ' || tempFieldname || ' ' ||
              ptype;
    execute immediate sqlstr;
  exception
    when others then
      dbms_output.put_line(SQLstr || '字段已存在');
  end;

  sqlstr := 'update ' || ptableName || ' set ' || tempFieldname || '=' ||
            pfieldName;
  execute immediate sqlstr;
  sqlstr := 'update ' || ptableName || ' set ' || pFieldname || '=null';
  execute immediate sqlstr;

  sqlstr := 'ALTER table  ' || ptableName || ' modify ' || pFieldname || '
            ' || ptype;
  execute immediate sqlstr;
  sqlstr := ' update ' || ptableName || ' set ' || pFieldname || ' = ' ||
            tempfieldName;
  execute immediate sqlstr;

  sqlstr := ' alter table ' || ptableName || ' drop column ' ||
            tempfieldName;
  execute immediate sqlstr;
  commit;
  return 1;
  execute immediate sqlstr;
exception
  when others then
    dbms_output.put_line(SQLERRm);
    dbms_output.put_line(SQLcode);
    dbms_output.put_line(SQLstr);

    rollback;
    return - 1;
end;