oracel 2个数据库对象比较，并且创建不同的对象

--创建dblink
--host 对应数据库的ip地址
--service_name  = ORACLE SID
create    database link cq  
connect to xzcq identified by "123"  
using '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS =(PROTOCOL = TCP)(HOST = 10.52.52.153)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = orcl)))';
----------------------------------------------------------存储过程 函数----------------------------------------------------
create or replace procedure pr_riseobj(ctype varchar2) AUTHID CURRENT_USER is
str clob;
cursor c_cur is 
--新建
select   name from ( select * from user_source@cq  where type = ctype ) highversion  where name not in ( select name from  user_source  where type =ctype )
 union
--重建
select   name from (
select highversion.* from ( select * from user_source@cq  where type = ctype ) highversion 
inner   join (select * from user_source  where type = ctype ) lowversion   on highversion.name = lowversion.name and highversion.line = lowversion.line 
and replace(　replace( replace (replace(highversion.text,chr(10),''),' ','' ),chr(9),'' ),'　','' ) != replace( replace(replace( replace(lowversion.text,chr(10),''),' ',''),chr(9),''),'　','')
)
group by name;
begin
  
  for  c in c_cur loop
      str := 'create or replace ';
      for f in ( select  text from user_source@cq  where type = ctype and name = c.name  order  by  line ) loop
        str := str||f.text; 
      end loop;
      execute immediate str;
  end loop;
end pr_riseobj;

------------------------------------------------------------序列-----------------------------------------------------------------
create or replace procedure pr_risesequences AUTHID CURRENT_USER is
str varchar2(500);
cursor c_cur is
select  * from ( select  * from user_sequences@cq    ) highversion  where sequence_name not in ( select sequence_name from  user_sequences    );
begin
  for  c in c_cur loop
      str := 'create sequence '||c.sequence_name||' minvalue '||c.min_value||' maxvalue '||c.max_value||' start with 1 increment by '||c.increment_by ;
      execute immediate str;
  end loop;
end pr_risesequences;
------------------------------------------------------建表--------------------------------------------------------------
create or replace procedure pr_riseTable AUTHID CURRENT_USER is
str varchar2(1000); --创建表语句
pk varchar2(100);   --创建主键
un varchar2(100);  --创建唯一约束
cursor c_cur is

select   tname from ( select  table_name tname  from user_tab_columns@cq where table_name not like '%$%' group by table_name) highversion
where tname not in ( select  table_name tname  from user_tab_columns where table_name not like '%$%' group by table_name )

group by tname;
begin

  for  c in c_cur loop
      --创建表
      str := 'create table  '||c.tname||'( ';
      for f in ( select  column_name cn ,data_type dt ,data_length dl,nullable from user_tab_columns@cq where table_name = c.tname ) loop
        if f.dt = 'CLOB' or f.dt = 'BLOB'  or f.dt = 'NUMBER' or f.dt = 'DATE' or f.dt = 'FLOAT' or f.dt = 'INTEGER' then
          --这些字段不需要加长度
          str := ' '||str||lower(f.cn)||' '||f.dt||'';
        else
          str := ' '||str||lower(f.cn)||' '||f.dt||'('||f.dl||')';
        end if;  
        --判断当前字段属性 是否为空
        if f.nullable = 'N' then
          str := str||' not null,';
        else
          str := str||',';
        end if;   
      end loop;
      str := substr(str,0,length(str)-1);
      str := str||' ) ';
      execute immediate str;
      --创建主键
      select ' alter table '||c.tname||' add constraint '||cn||' primary key ( ' into pk  from (
             select  cu.constraint_name cn from user_cons_columns@cq cu, user_constraints@cq au  where cu.constraint_name = au.constraint_name and au.constraint_type = 'P' and au.table_name = c.tname
      ) where rownum = 1;
      
      for p in (
        select cu.column_name cn from user_cons_columns@cq cu, user_constraints@cq au where cu.constraint_name = au.constraint_name and au.constraint_type = 'P' and au.table_name =c.tname 
      ) loop
        pk := pk||p.cn||',';
     end loop;
     pk := substr(pk,0,length(pk)-1);
     pk := pk||' ) ';
     execute immediate pk;
     --创建唯一约束
     for u in (
       select au.constraint_name cn from user_cons_columns@cq cu, user_constraints@cq au 
       where cu.constraint_name = au.constraint_name and au.constraint_type = 'U'  and au.table_name =c.tname
       group by au.constraint_name
     ) loop
       un:=  ' ALTER TABLE '||c.tname||'  ADD CONSTRAINT '||u.cn||'  UNIQUE (' ;
       for uc in (
          select cu.column_name cname from user_cons_columns@cq cu, user_constraints@cq au 
          where cu.constraint_name = au.constraint_name and au.constraint_type = 'U'  
          and au.table_name =c.tname
          and au.constraint_name = u.cn    
       ) loop
         un := un||uc.cname||',';
       
       end loop;
       un := substr(un,0,length(un)-1);
       un := un||' ) ';
       execute immediate un;
     end loop;
  end loop;
end pr_riseTable;
------------------------------------------------------------新增字段----------------------------------------------------------------------
create or replace procedure pr_riseFiled  AUTHID CURRENT_USER is
str clob;
un varchar2(100);  --创建唯一约束
ucount int;
cursor c_cur is
select  * from ( select  table_name tname,column_name cname,data_type dt,data_length dl from user_tab_columns@cq where table_name not like '%$%'  ) highversion
where tname||cname not in ( select  table_name||column_name  from user_tab_columns  where table_name not like '%$%');
begin

  for  c in c_cur loop
    --新增字段
    if c.dt = 'CLOB' or c.dt = 'BLOB'  or c.dt = 'NUMBER' or c.dt = 'DATE' or c.dt = 'FLOAT' or c.dt = 'INTEGER'  then
       str := 'alter table '||c.tname||' add '||lower(c.cname)||' '||c.dt;
    else
       str := 'alter table '||c.tname||' add  '||lower(c.cname)||' '||c.dt||'('||c.dl||')';
    end if;
    execute immediate str;

     --创建唯一约束
     for u in (
       select au.constraint_name cn from user_cons_columns@cq cu, user_constraints@cq au
       where cu.constraint_name = au.constraint_name and au.constraint_type = 'U'  and au.table_name =c.tname 
       and  au.constraint_name =  (
            select  i.Index_name from user_ind_columns@cq t,user_indexes@cq i 
            where t.index_name = i.index_name and t.table_name = i.table_name and t.table_name = c.tname  and column_name = c.cname
       )
       group by au.constraint_name  
     ) loop
       un:=  ' ALTER TABLE '||c.tname||'  ADD CONSTRAINT '||u.cn||'  UNIQUE (' ;
       ----------------------
       for uc in (
          select cu.column_name cname from user_cons_columns@cq cu, user_constraints@cq au
          where cu.constraint_name = au.constraint_name and au.constraint_type = 'U'
          and au.table_name =c.tname
          and au.constraint_name = u.cn
       ) loop
         un := un||uc.cname||',';

       end loop;
       --------------------------------
       --删除已建唯一索引
       select count(constraint_name) into  ucount  from user_constraints   where  table_name = c.tname and constraint_name =  u.cn;
       if ucount > 0 then
        execute immediate 'drop index '||u.cn;
       end if;

       un := substr(un,0,length(un)-1);
       un := un||' ) ';
       execute immediate un;

    end loop;
  end loop;
end pr_riseFiled;
----------------------------------------------------视图-------------------------------------------------
create or replace procedure pr_riseuserview AUTHID CURRENT_USER is
str clob;
v_isexist number(3,0);
i int;
begin
  v_isexist := 0;
  select count(1) into v_isexist from sys.all_objects where object_name='T_USER_VIEWS_ROW' and object_type = 'TYPE';
  if v_isexist>0 then
     execute immediate 'drop type t_user_views_table';
     execute immediate 'drop type t_user_views_row';
  end if;
  execute immediate 'create type t_user_views_row as object
                        (view_name varchar2(60),text_length number,text clob)';
  execute immediate 'create type t_user_views_table is table of t_user_views_row';
  execute immediate 'create or replace function fu_get_user_views_high
                    return t_user_views_table
                    as
                       rs t_user_views_table:= t_user_views_table();
                    begin
                           for cur in (select view_name,text_length,text from user_views@cq) loop
                               rs.extend;
                               rs(rs.count) := t_user_views_row(cur.view_name,cur.text_length,cur.text);
                           end loop;
                       return rs;
                    end fu_get_user_views_high;';
  execute immediate 'create or replace function fu_get_user_views_low
                    return t_user_views_table
                    as
                       rs t_user_views_table:= t_user_views_table();
                    begin
                           for cur in (select view_name,text_length,text from user_views) loop
                               rs.extend;
                               rs(rs.count) := t_user_views_row(cur.view_name,cur.text_length,cur.text);
                           end loop;
                       return rs;
                    end fu_get_user_views_low;';
  for c in (    select  view_name vn,text  from  table( fu_get_user_views_high())  ) loop
    for c1 in (select   view_name vn,text   from  table( fu_get_user_views_low()) ) loop
      if (c1.vn = c.vn and replace( replace( replace (replace(c.text,chr(10),''),' ','' ),chr(9),'' ),'　','' ) !=replace(  replace( replace (replace(c1.text,chr(10),''),' ','' ),chr(9),'' ),'　','' )) then
        --dbms_output.put_line('reate or replace view '||c.vn||' as '||c.text);
        execute immediate 'create or replace view '||c.vn||' as '||c.text;
      end if;
      
    end loop;
    
  end loop;
end pr_riseuserview;
----------------------------------------------------------------自定义类型-------------------------------------------------------------------------------
create or replace procedure pr_riseusertype  AUTHID CURRENT_USER is
childStr clob;
ustr varchar2(1000);
begin
  --step 1 新建
  for c in (
    select  name,rn from ( select name,referenced_name rn from user_dependencies@cq where type='TYPE'  and  Referenced_owner != 'SYS') highversion
    where name not in (select name from user_dependencies where type='TYPE'  and  Referenced_owner != 'SYS')
  ) loop
    childStr := 'create or replace ';
    --创建行类型
    for childs in (    select text t from user_source@cq where type = 'TYPE' and name = c.rn order by line) loop
      childStr := childStr||childs.t;
    end loop;
    execute immediate childStr;
    --创建表类型
    execute immediate  'create or replace type '||c.name||' is table of '||c.rn;
  end loop;
  childStr := '';
  --step 2 更新
  for cu in (
    select name,Referenced_name rn from user_dependencies  where type='TYPE'  and  Referenced_owner != 'SYS' and Referenced_name in (
      select lowversion.name from (
        select * from user_source@cq where type = 'TYPE' and name in ( select referenced_name from user_dependencies@cq where type='TYPE'  and  Referenced_owner != 'SYS')
      )highversion
      inner join (
        select * from user_source  where type = 'TYPE' and name in ( select referenced_name from user_dependencies  where type='TYPE'  and  Referenced_owner != 'SYS')
      )lowversion
      on highversion.name = lowversion.name and highversion.line= lowversion.line
      and replace(　replace( replace (replace(highversion.text,chr(10),''),' ','' ),chr(9),'' ),'　','' )  != replace(　replace( replace (replace(lowversion.text,chr(10),''),' ','' ),chr(9),'' ),'　','' )
      group by lowversion.name
    )
  ) loop
    --删除表类型
    execute immediate  'drop type '||cu.name;
    --删除行类型
    execute immediate  'drop type '||cu.rn;
    --根据高版本的类型创建
    --创建行类型
    childStr := 'create or replace ';
    for r in (  select * from user_source@cq where type = 'TYPE' and name = cu.name ) loop
      childStr := childStr||r.text;
    end loop;
    execute immediate childStr;
    --创建表类型
    execute immediate  'create or replace type '||cu.name||' is table of '||cu.rn;

  end loop;

end pr_riseusertype;

---------------------------- 查询视图 Long 转clob-----------------------------
create or replace type t_user_views_row as object (view_name varchar2(60),text_length number,text clob)
create or replace type t_user_views_table is table of t_user_views_row

declare     
    v_isexist number(3,0):=0;  
begin
   select count(1) into v_isexist from sys.all_objects where object_name='T_USER_VIEWS_ROW' and object_type = 'TYPE';
   if v_isexist>0 then
       execute immediate 'drop type t_user_views_table';
       execute immediate 'drop type t_user_views_row';
       execute immediate 'create type t_user_views_row as object
                          (view_name varchar2(60),text_length number,text clob)';
       execute immediate 'create type t_user_views_table is table of t_user_views_row';
       execute immediate 'create or replace function fu_get_user_views
                          return t_user_views_table
                          as
                             rs t_user_views_table:= t_user_views_table();
                          begin
                                 for cur in (select view_name,text_length,text from user_views@cq) loop
                                     rs.extend;
                                     rs(rs.count) := t_user_views_row(cur.view_name,cur.text_length,cur.text);
                                 end loop;
                             return rs;
                          end fu_get_user_views;';
   end if;
end;
 

转载于:https://my.oschina.net/wxh1989/blog/882906