Oracle找出所有表字段中值包含中文并生成扩充字段的SQL脚本

 

Oracle找出所有表字段中值包含中文并生成扩充字段的SQL脚本

 

背景

后续计划将Oracle的某个库迁移到云上的达梦库，Oracle字符集为ZHS16GBK，达梦库字符集为UTF-8。

我们知道，中文汉字在UTF8中一个汉字占3个字节，而在GBK中则是占2个字节，测试过程发现若字段中存有中文的行，有可能在达梦中会报长度不足而报错。

经过讨论，将含有中文的字段均按照(字段长度/2)*3的规则来扩充。

 

花了4个小时来编写并调试脚本，终于写出了个匿名过程来直接找出含中文的字段的表并生成扩充字段的SQL脚本：（需要用sys执行，根据需求修改第2行的where条件）

PS：含有大表并且表字段没索引又不涉及中文可能会比较慢，可以自行修改脚本忽略特定的大表或者特定的表的某些字段

declare
    cursor colName_cur is select owner,table_name,column_name,data_type,data_length from dba_tab_cols T where owner in (用户列表) and data_type in ('CHAR','NCHAR','VARCHAR2') and table_name not like 'BIN%' and table_name not in (select view_name from dba_views where owner in (用户列表));
    colNameValue colName_cur%rowtype;
    --marks number;
    table_exists number;
    sqlexec varchar2(1000);
begin
    open colName_cur;
    select count(*) into table_exists from dba_tables where table_name='COL_TMP';
    if ( table_exists = 1 ) then 
        sqlexec := 'drop table sys.col_tmp purge';
        execute immediate sqlexec;
    end if;
    sqlexec:='create table sys.col_tmp ( owner varchar2(20),table_name varchar2(35),column_name varchar2(35),data_type varchar2(30),data_length int,record int)';
    execute immediate sqlexec;
    loop
        fetch colName_cur into colNameValue;
        exit when colName_cur%notfound;
        --select count(*) into marks from market.main_combo_list where length(colNameValue.column_name)<>lengthb(colNameValue.column_name) and rownum = 1;
        sqlexec:='insert into sys.col_tmp select '''||colNameValue.owner||''''||','''||colNameValue.table_name||''''||','''||colNameValue.column_name||''','''||colNameValue.data_type||''','''||colNameValue.data_length||''', count(*) from '||colNameValue.owner||'."'||colNameValue.table_name||'" where length('||colNameValue.column_name||')<>lengthb('||colNameValue.column_name||') and rownum=1 having count(*)=1' ;
        execute immediate sqlexec;
        --dbms_output.put_line(sqlexec);
    end loop;
    commit;
    sqlexec:='select ''alter table ''||t.owner||''."''||t.table_name||''" modify ''||t.column_name||'' ''||t.data_type||''(''||ceil(3*t.data_length/2)||'');'' from sys.col_tmp t;';
    dbms_output.put_line(sqlexec);
    --sqlexec := 'drop table sys.col_tmp purge';
    --execute immediate sqlexec;
    close colName_cur;
end;
/

执行结果如下（案例）防爬防偷：

生成一条SQL，同时含中文的表和字段的信息都放在临时表SYS.COL_TMP中了。

然后直接复制该sql执行生成语句（案例）：https://www.cnblogs.com/PiscesCanon/p/17823605.html

之后再执行生成的这些SQL即可。

后续删除临时表SYS.COL_TMP。