Oracle自定义实用函数（一）：快速复制表注释&字段注释

日常工作中，有没有因为表（或视图，以下不特别强调视图，对表和视图都适用）缺少字段注释而烦恼？也许你对SQL语句很熟练，可以很轻松的写出comment on column...语句，但表字段非常多的时候，有时复制粘贴的工作，都会显得力不从心。由于我在工作中经常会遇到这方面的需求，所以还是抽空，自定义函数，一键解决复制表时，顺便复制表注释&字段注释。以Oracle数据库举例，MySQL或者其它数据库，方法如出一辙，感兴趣的小伙伴可以自行修改与发挥。

第1步：建表

create table owner1.tab1 as select * from owner2.v1 where 1=2;

第2步：调用函数，复制表注释&字段注释

select f_copy_comments('table','owner1','tab1','view','owner2','v1') from dual;

没错，就是这么简单，owner1.tab1会拥有owner2.v1的全部注释，哈哈！

简单操作一下：

1、准备测试视图v1，并建好注释。

2、创建新表，此时肯定没有注释。

3、调用函数，直接复制视图的注释&字段注释。

 

代码如下：

create or replace function f_copy_comments(new_type varchar2,new_owner varchar2,new_tab varchar2,
                                           old_type varchar2,old_owner varchar2,old_tab varchar2) 
return varchar2 authid current_user is
  /*
    Author:caihuadong
    Date:2023/08/11
    How To Use:
      1.select f_copy_comments(new_type,new_owner,new_tab,old_type,old_owner,old_tab) from dual，能轻松实现复制old_tab表（视图）的注释。
            
      例如：create table owner1.tab1 as select * from owner2.v1 where 1=2;
                  
            select f_copy_comments('table','owner1','tab1','view','owner2','v1') from dual;
            
      2.new_type与old_type只能是'table'或'view'（大小写随意）
      
      3.new_owner与old_owner指定用户名（大小写随意）
      
      4.new_tab指定新表（视图），old_tab指定旧表（视图），（大小写随意）
            
      5.建议 新表（视图）、旧表（视图） 的顺序不要写颠倒了。
  */
  v_new_type varchar2(100);
  v_new_owner varchar2(100);
  v_new_tab varchar2(100);
  v_old_type varchar2(100);
  v_old_owner varchar2(100);
  v_old_tab varchar2(100);
  v_cnt integer;
  v_comment varchar2(1000);
  pragma autonomous_transaction;
begin
  --1.将入参 '大写'
  v_new_type:=upper(regexp_replace(new_type,'\s'));
  v_new_owner:=upper(regexp_replace(new_owner,'\s'));
  v_new_tab:=upper(regexp_replace(new_tab,'\s'));
  v_old_type:=upper(regexp_replace(old_type,'\s'));
  v_old_owner:=upper(regexp_replace(old_owner,'\s'));
  v_old_tab:=upper(regexp_replace(old_tab,'\s'));
  
  --2.检查入参是否为空
  if v_new_type is null or v_new_owner is null or v_new_tab is null
    or v_old_type is null or v_old_owner is null or v_old_tab is null then
    return '入参不可为空';
  end if;
  
  --3.检查入参是否合法
  --检查类型
  if v_new_type not in('TABLE','VIEW') then
    return v_new_type||'类型错误，请指定TABLE或VIEW类型';
  end if;
  
  if v_old_type not in('TABLE','VIEW') then
    return v_old_type||'类型错误，请指定TABLE或VIEW类型';
  end if;
  
  --检查用户
  select count(1) into v_cnt from all_users where username=v_new_owner;
  
  if v_cnt=0 then
    return v_new_owner||'用户不存在';
  end if;
  
  select count(1) into v_cnt from all_users where username=v_old_owner;
  
  if v_cnt=0 then
    return v_old_owner||'用户不存在';
  end if;
  
  --检查表（视图）
  if v_new_type='TABLE' then
    select count(1) into v_cnt from all_tables where owner=v_new_owner and table_name=v_new_tab;
    
    if v_cnt=0 then
      return v_new_owner||'.'||v_new_tab||'表不存在';
    end if;
  end if;
  
  if v_new_type='VIEW' then
    select count(1) into v_cnt from all_views where owner=v_new_owner and view_name=v_new_tab;
    
    if v_cnt=0 then
      return v_new_owner||'.'||v_new_tab||'视图不存在';
    end if;
  end if;
  
  if v_old_type='TABLE' then
    select count(1) into v_cnt from all_tables where owner=v_old_owner and table_name=v_old_tab;
    
    if v_cnt=0 then
      return v_old_owner||'.'||v_old_tab||'表不存在';
    end if;
  end if;
  
  if v_old_type='VIEW' then
    select count(1) into v_cnt from all_views where owner=v_old_owner and view_name=v_old_tab;
    
    if v_cnt=0 then
      return v_old_owner||'.'||v_old_tab||'视图不存在';
    end if;
  end if;
  
  --4.复制表（视图）的字段注释
  --当旧表（视图）本身无注释时，则返回提醒
  select max(comments) into v_comment from all_col_comments where owner=v_old_owner and table_name=v_old_tab;
  
  if v_comment is null then
    return v_old_owner||'.'||v_old_tab||'无注释供复制';
  end if;
  
  /*
    1.开始复制字段
    2.这里存在选择，选择不同则效果不同。当新表字段本身就有注释时，这里选择了不复制旧表里的注释。
  */
  for x in(select column_name
             from all_col_comments
            where owner=v_new_owner
              and table_name=v_new_tab
              and comments is null) loop
    select max(comments)
      into v_comment
      from all_col_comments 
     where owner=v_old_owner 
       and table_name=v_old_tab 
       and column_name=x.column_name;
      
    if v_comment is not null then
      execute immediate 'comment on column '||v_new_owner||'.'||v_new_tab||'.'||x.column_name||' is '''||regexp_replace(v_comment,'''','''''')||'''';
    end if;
  end loop;
  
  --5.复制表（视图）名的注释
  select max(comments) into v_comment from all_tab_comments where table_type=v_new_type and owner=v_new_owner and table_name=v_new_tab;
  
  if v_comment is null then
    select max(comments) into v_comment from all_tab_comments where table_type=v_old_type and owner=v_old_owner and table_name=v_old_tab;
    
    if v_comment is not null then
      execute immediate 'comment on table '||v_new_owner||'.'||v_new_tab||' is '''||regexp_replace(v_comment,'''','''''')||'''';
    end if;
  end if;
  
  return '执行成功';
  
end f_copy_comments;