oracle批量修改表字段默认值

需求：将所有表中字段默认值为CO000002的改为CO000001

 

思路：

1.查找出所有表字段默认值为CO000002的字段信息（表名称、字段名称）。

2.写语句更新字段默认值为CO000001。

 

实现：

因为要使用到oracle系统表USER_TAB_COLS，其中默认值字段类型为LONG，不能直接使用，所以需要写一个函数来进行转换，将LONG类型的DATA_DEFAULT转为字符串，方便查询和判断。

函数脚本如下： 

CREATE OR REPLACE FUNCTION LONG_TO_CHAR(uTABLE IN VARCHAR2,uID IN NUMBER)
RETURN VARCHAR2
AS
  uVal VARCHAR2(32767); 
BEGIN
  SELECT DATA_DEFAULT INTO uVal FROM USER_TAB_COLS WHERE TABLE_NAME = UPPER(uTABLE) AND COLUMN_ID = uID;
  uVal := SUBSTR(uVal,1,50); 
  RETURN uVal;  
END LONG_TO_CHAR;

 

函数创建完成后，查询字段默认值为CO000002的字段信息，脚本如下：

--查询字段默认值为CO000001的字段信息
select * from (
    SELECT 
      M.TABLE_NAME
      ,TC.COMMENTS 
      ,M.COLUMN_NAME
      ,CC.COMMENTS COLUMN_COMMENTS
      ,LONG_TO_CHAR(M.TABLE_NAME,M.COLUMN_ID) DEFAULT_VAL
    FROM USER_TAB_COLS M
    LEFT JOIN USER_COL_COMMENTS CC ON M.TABLE_NAME = CC.TABLE_NAME AND M.COLUMN_NAME = CC.column_name
    LEFT JOIN USER_TAB_COMMENTS TC ON M.TABLE_NAME = TC.TABLE_NAME
    ORDER BY M.COLUMN_ID
) 
where DEFAULT_VAL = q'['CO000002']';

 因为CO000002在表结构中前后有单引号，所以这里要写成q'['CO000002']'形式，不然查询不到数据。

 

查询出来要更新的字段信息后，写一个脚本拼接出修改语句，批量更新。脚本如下：

DECLARE str VARCHAR2(4000);
BEGIN 
 
for x in  (
            select * from (
            SELECT 
            M.TABLE_NAME
            ,TC.COMMENTS 
            ,M.COLUMN_NAME
            ,CC.COMMENTS COLUMN_COMMENTS
            ,LONG_TO_CHAR(M.TABLE_NAME,M.COLUMN_ID) DEFAULT_VAL
            FROM USER_TAB_COLS M
            LEFT JOIN USER_COL_COMMENTS CC ON M.TABLE_NAME = CC.TABLE_NAME AND M.COLUMN_NAME = CC.column_name
            LEFT JOIN USER_TAB_COMMENTS TC ON M.TABLE_NAME = TC.TABLE_NAME
            ORDER BY M.COLUMN_ID
            ) 
            where DEFAULT_VAL = q'['CO000002']'
   ) loop 
              
    str:='alter table '||x.TABLE_NAME || ' modify ' || x.COLUMN_NAME ||' default ''CO000001''; ';
    dbms_output.put_line(str);
    
  end loop;

end;

 

在PL/SQL执行上面脚本，然后在输出窗口中可以看到最终需要执行的修改脚本已经被输出出来了。直接把脚本拷贝出来，单独打开一个窗口执行下就可以了。

 

将拷贝出来的脚本执行后，再去看表结构，就发现原来字段默认值为CO000002的变成了CO000001，达到预期目的。

 

参考文章：https://blog.csdn.net/paul50060049/article/details/78811957