ORACLE_关于批量删除视图语句

qq_35860647

已于 2022-09-08 16:57:42 修改

阅读量794

点赞数

分类专栏： oracle数据库记录文章标签： sql 数据库

于 2022-09-08 14:50:22 首次发布

本文链接：https://blog.csdn.net/qq_35860647/article/details/126764438

版权

oracle数据库记录专栏收录该内容

4 篇文章 0 订阅

订阅专栏

NC57再一次还原测试数据的时候发现视图有一百多万条

查询视图语句

select view_name from user_views where view_name like 'TEMQ%' and rownum<=50;

这也就说明正式系统会有那么多数据。所以试图删除这些视图已做影响判断。在做视图删除的时候declae语句执行错误，原因是多了个分号。

第一条语句（分号去掉可以执行）

begin

for cur in (select * from (select view_name,rownum from user_views where view_name like 'TEMQ%') where  rownum<=50 ) loop
dbms_output.put_line( 'drop view ' ||cur.view_name ||';');
execute immediate 'drop view ' ||cur.view_name ||';';//报错原因 是这里加了分号 去掉就对了
commit;
end loop;

end;

begin

for cur in (select * from (select view_name,rownum from user_views where view_name like 'TEMQ%') where  rownum<=50 ) loop
dbms_output.put_line( 'drop view ' ||cur.view_name ||';');
execute immediate 'drop view ' ||cur.view_name;
commit;
end loop;

end;

第二条语句也可以执行

declare

  v_name all_tables.table_name%type;

  cursor mycur is
    select view_name from user_views where view_name like 'TEMQ%' and rownum<=10000;

begin
 for a in 1.. 120 loop
     //循环120次

  open mycur;

  loop
  
    fetch mycur
      into v_name;
  
    exit when mycur%NOTFOUND OR mycur%NOTFOUND IS NULL;
  
    execute immediate 'drop view ' || v_name;
  
  end loop;

  close mycur;
  end loop;
end;

declare

  v_name all_tables.table_name%type;

  cursor mycur is
    select view_name from user_views where view_name like 'TEMQ%' and rownum<=110050;

begin

  open mycur;

  loop
  
    fetch mycur
      into v_name;
  
    exit when mycur%NOTFOUND OR mycur%NOTFOUND IS NULL;
  
    execute immediate 'drop view ' || v_name;
  
  end loop;

  close mycur;

end;