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

关于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.csdn.net/niuch1029291561/article/details/54972233，
https://blog.csdn.net/u013323758/article/details/78769817