Oracle 查询表主键创建情况并创建

小鱼儿 2022-05-31 03:41 186阅读 0赞

declare
      cursor cur_check is 
      select 
         case when b.COLUMN_NAME is not null then 'select '||b.COLUMN_NAME||', count(1) from '|| a.OWNER||'.'||a.TABLE_NAME ||' group by '||b.COLUMN_NAME||' having count(1)>1' end sql1
       , case when b.COLUMN_NAME is not null then 'alter table '|| a.OWNER||'.'||a.TABLE_NAME||' add constraint '||replace(a.TABLE_NAME,'T_','PK__')||' primary key ('||b.COLUMN_NAME||')' end sql2
       , 'select a.* from ' || a.OWNER||'.'||a.TABLE_NAME ||'' sql3
      from all_tables a 
      left join all_tab_columns b on b.TABLE_NAME=a.TABLE_NAME and b.OWNER=a.OWNER and b.COLUMN_NAME in ('PKID', 'ID')
      where a.owner = 'OWNER' 
      and not exists  (
       select 1 from all_cons_columns c,all_constraints d where c.constraint_name=d.constraint_name and d.constraint_type='P'and c.table_name=a.table_name  and c.owner=a.owner
      );
      
      row_check cur_check%rowtype;
       
      cur_sql1 SYS_REFCURSOR;
      
      PKID NUMBER;
      PKCOUNT NUMBER;
      
    begin
      open cur_check;
      loop 
        fetch cur_check into row_check;
        exit when cur_check%notfound;
        if row_check.sql1 is not null then
          open cur_sql1 for row_check.sql1;
          loop
            fetch cur_sql1 into PKID,PKCOUNT;
            if cur_sql1%notfound then 
              if(cur_sql1%rowcount <> 0) then 
                dbms_output.put_line('待设主键列值重复数：'|| cur_sql1%rowcount||'组');
              else
                -- dbms_output.put_line(row_check.sql2);
                execute immediate row_check.sql2;
              end if;
              exit;
            end if;
            if(cur_sql1%rowcount=1) then 
              dbms_output.put_line('待设主键列有重复值：'||row_check.sql1);
            end if;
            dbms_output.put_line(PKID||','||PKCOUNT);
          end loop;
          close cur_sql1;
        else 
          dbms_output.put_line('待设主键列无法确认：' || row_check.sql3);
        end if;
      end loop;
      close cur_check;
    end;