简单实验-A/B两用户下表结构比较（存储过程游标）

题目：从A/B库分别导出表结构，然后比较两者之前的区别（Oracle数据库）

思路：先对表名进行匹配，有无比较，如果都有再进行字段比较；字段也是先匹配有无，然后匹配类型长度等。

问题：①、

          遇到该问题未仔细看错误提示。到时去翻看存储过程。实际问题

         

exit when cur%notfound;   --特么这边应该是 cur%notfound    sql%notfound 这个是隐式游标里的

   ②、遍历问题  对表进行遍历 显示游标，分别处理

             对字段进行遍历(内部再循环)，用的是内部for rec循环

 

for cur_b in (
        select (case when a.table_name is null then b.table_name else a.table_name end) tbname,
               (case when  a.COLUMN_NAME is null then b.COLUMN_NAME else a.COLUMN_NAME end)clname,
               a.data_type type1,
               b.data_type type2,
               a.data_length len1,
               b.data_length len2
          from (select table_name,COLUMN_NAME, data_type, data_length
                  from all_tab_cols
                 where owner = upper(cur_a.owner1)
                   and table_name = upper(cur_a.name1)) a
          full join (select table_name,COLUMN_NAME, data_type, data_length
                       from all_tab_cols
                      where owner = upper(cur_a.owner2)
                        and table_name = upper(cur_a.name2)) b --内部怎么再循环？
            on (a.COLUMN_NAME = b.COLUMN_NAME) --外关联<>有问题
) loop
 --如果不相等
   if (cur_b.type1 <> cur_b.type2 or cur_b.len1<> cur_b.len2 )then 
           insert into scott.test_ab
        (tname, cname, a_bj, b_bj, a_type, b_type, a_len, b_len)
        values
        (cur_b.tbname,cur_b.clname,1,1,cur_b.type1,cur_b.type2,cur_b.len1,cur_b.len2);
   end if;
end loop;
    end if;

解决：可以在遍历的时候对数据进行判断，也可以先存入数据，在通过特定条件删除

create or replace procedure p_test_ab2(USER_A in VARCHAR2,
                                       USER_B in VARCHAR2)
/*
  查看A用户下表结构是否一致
  createuser：dong
  cteratedate:2015.09.14 16:55
  */
 is
  --获取传入参数
  var_a varchar2(20) := user_a;
  var_b varchar2(20) := user_b;
  --定义游标  先全关联表数据
  cursor cur is
    select b.owner      owner1,
           b.table_name name1,
           c.owner      owner2,
           c.table_name name2
      from (select * from all_tables a where a.owner = upper(var_a)) b
      full join (select * from all_tables a where a.owner = upper(var_b)) c
        on (b.table_name = c.table_name);
  --定义游标变量
  cur_a     cur%rowtype;
  TAB_NAMEA varchar(30); --遍历的表名
  num_1     number; --判断条件
begin
  --创建表存放比较差错数据


  select count(1)
    into num_1
    from all_tables a
   where a.owner = 'SCOTT'
     and a.table_name = 'TEST_AB';
  if num_1 > 0 then
    execute immediate 'truncate table scott.test_ab';


  else
    execute immediate '
   create table scott.test_ab (
   tname varchar2(20) ,  --表名
   cname varchar2(30), --列明
   a_bj  char(1),  --是否a中存在
   b_bj  char(2),  --是否b中存在
   a_type varchar2(30), --字段类型a
   b_type varchar2(30), --字段类型b
   a_len   number ,    --字段长度a
   b_len   number     --字段长度b
   )
   ';


  end if;
  --比较列信息
  --按表遍历


  open cur;


  loop
    fetch cur
      into cur_a;
    exit when cur%notfound; --特么这边是 cur%notfound    sql%notfound 这个是隐式游标里的
    /*    dbms_output.put_line(cur_a.table_name);*/


    --如果A中不存在
    if cur_a.name1 is null then
      insert into scott.test_ab
        (tname, cname, a_bj, b_bj, a_type, b_type, a_len, b_len)
        select a.owner,
               a.table_name,
               0,
               1,
               null,
               a.data_type,
               null,
               a.data_length
          from all_tab_cols a
         where a.owner = upper(cur_a.owner2)
           and a.table_name = upper(cur_a.name2);


      --如果B中不存在
    elsif cur_a.name2 is null then
      insert into scott.test_ab
        (tname, cname, a_bj, b_bj, a_type, b_type, a_len, b_len)
        select a.owner,
               a.table_name,
               1,
               0,
               a.data_type,
               null,
               a.data_length,
               null
          from all_tab_cols a
         where a.owner = upper(cur_a.owner1)
           and a.table_name = upper(cur_a.name1);


      --如果都存在
    else 
      --内部游标遍历
  for cur_b in (
        select (case when a.table_name is null then b.table_name else a.table_name end) tbname,
               (case when  a.COLUMN_NAME is null then b.COLUMN_NAME else a.COLUMN_NAME end)clname,
               a.data_type type1,
               b.data_type type2,
               a.data_length len1,
               b.data_length len2
          from (select table_name,COLUMN_NAME, data_type, data_length
                  from all_tab_cols
                 where owner = upper(cur_a.owner1)
                   and table_name = upper(cur_a.name1)) a
          full join (select table_name,COLUMN_NAME, data_type, data_length
                       from all_tab_cols
                      where owner = upper(cur_a.owner2)
                        and table_name = upper(cur_a.name2)) b --内部怎么再循环？
            on (a.COLUMN_NAME = b.COLUMN_NAME) --外关联<>有问题
) loop
 --如果不相等
   if (cur_b.type1 <> cur_b.type2 or cur_b.len1<> cur_b.len2 )then 
           insert into scott.test_ab
        (tname, cname, a_bj, b_bj, a_type, b_type, a_len, b_len)
        values
        (cur_b.tbname,cur_b.clname,1,1,cur_b.type1,cur_b.type2,cur_b.len1,cur_b.len2);
   end if;
end loop;
    end if;


    --先插入再删除


  end loop;
commit;
  close cur;
exception
  when others then
    dbms_output.put_line(Sqlcode || '    ' || substr(sqlerrm, 1, 200));
end;

--调用
declare 
 p_test_ab2('app','apps');
end;