关于ORACLE 批量替换表字段类型

最新推荐文章于 2025-03-31 09:45:34 发布

花洒了一片地

最新推荐文章于 2025-03-31 09:45:34 发布

阅读量455

点赞数

CC 4.0 BY-SA版权

分类专栏： Oracle 文章标签： oracle 批量替换字段类型

本文链接：https://blog.youkuaiyun.com/weixin_43925869/article/details/88792336

Oracle 专栏收录该内容

4 篇文章

订阅专栏

本文提供了一种在Oracle数据库中批量修改表字段类型的PL/SQL脚本示例，特别针对需要将带有小数位的NUMBER类型字段从小数位4调整到5的情况。通过创建临时字段进行数据迁移，确保了数据完整性和修改过程的顺利进行。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

关于ORACLE 批量替换表字段类型

-- Created on 2019/3/25 by HSLYPD 
declare
  -- Local variables here
  STR1 VARCHAR2(500);
  STR2 VARCHAR2(500);
  STR3 VARCHAR2(500);
  STR4 VARCHAR2(500);
  STR5 VARCHAR2(500);
  STR6 VARCHAR2(500);
  STR7 VARCHAR2(500);
begin
  -- Test statements here
  FOR S IN (SELECT OWNER, TABLE_NAME, COLUMN_NAME, DATA_LENGTH, NULLABLE
              FROM all_TAB_COLUMNS
             WHERE lower(DATA_TYPE) = 'number'
               and owner = 'HKCONFIG'
               and data_scale = 4
            /*and table_name = 'T_HK_CRD_COMPACT'*/
            ) LOOP
    if s.nullable = 'N' then
      STR6 := 'ALTER TABLE ' || S.OWNER || '.' || S.TABLE_NAME ||
              ' MODIFY ' || S.COLUMN_NAME || ' null';
      --DBMS_OUTPUT.PUT_LINE(STR1);
      EXECUTE IMMEDIATE STR6;
    end if;
    STR1 := 'ALTER TABLE ' || S.OWNER || '.' || S.TABLE_NAME || ' add(' ||
            S.COLUMN_NAME || '_tmp number(23,4))';
    --DBMS_OUTPUT.PUT_LINE(STR1);
    EXECUTE IMMEDIATE STR1;
    STR2 := 'update ' || S.OWNER || '.' || S.TABLE_NAME || ' set ' ||
            S.COLUMN_NAME || '_tmp = ' || S.COLUMN_NAME || ',' ||
            S.COLUMN_NAME || '=null';
    --DBMS_OUTPUT.PUT_LINE(STR2);
    EXECUTE IMMEDIATE STR2;
    commit;
    STR3 := 'ALTER TABLE ' || S.OWNER || '.' || S.TABLE_NAME || ' MODIFY(' ||
            S.COLUMN_NAME || ' number(23,5))';
    --DBMS_OUTPUT.PUT_LINE(STR3);
    EXECUTE IMMEDIATE STR3;
    STR4 := 'update ' || S.OWNER || '.' || S.TABLE_NAME || ' set ' ||
            S.COLUMN_NAME || ' = ' || S.COLUMN_NAME || '_tmp,' ||
            S.COLUMN_NAME || '_tmp=null';
    --DBMS_OUTPUT.PUT_LINE(STR4);
    EXECUTE IMMEDIATE STR4;
    commit;
    STR5 := 'ALTER TABLE ' || S.OWNER || '.' || S.TABLE_NAME ||
            ' drop column ' || S.COLUMN_NAME || '_tmp';
    --DBMS_OUTPUT.PUT_LINE(STR5);
    EXECUTE IMMEDIATE STR5;
    if s.nullable = 'N' then
      STR6 := 'ALTER TABLE ' || S.OWNER || '.' || S.TABLE_NAME ||
              ' MODIFY ' || S.COLUMN_NAME || ' not null';
      --DBMS_OUTPUT.PUT_LINE(STR1);
      EXECUTE IMMEDIATE STR6;
    end if;
  END LOOP;
end;

代码参考于：
https://blog.youkuaiyun.com/niuch1029291561/article/details/54972233，
https://blog.youkuaiyun.com/u013323758/article/details/78769817