select table_name
from user_tab_columns
where column_name IN ('EMP_NAME');
此查询提供了大约150个表名。我想找到所有列"EMP_NAME"=ABC的表并将其删除。如何获取列"EMP_NAME" = ABC
上有值的所有表?
例如:我将创建emp_temp
表并修改其中一行,使其ENAME
列包含LITTLEFOOT
——这就是我想要删除的内容。
SQL> create table emp_temp as select * from emp;
Table created.
SQL> update emp_temp set ename = 'LITTLEFOOT' where ename = 'KING';
1 row updated.
我们来做吧:
SQL> set serveroutput on
SQL> declare
2 l_str varchar2(500);
3 l_cnt number := 0;
4 begin
5 for cur_r in (select u.table_name, u.column_name
6 from user_tab_columns u, user_tables t
7 where u.table_name = t.table_name
8 and u.column_name = 'ENAME'
9 )
10 loop
11 l_str := 'SELECT COUNT(*) FROM ' || cur_r.table_name ||
12 ' WHERE ' || cur_r.column_name || ' = ''LITTLEFOOT''';
13
14 execute immediate (l_str) into l_cnt;
15
16 if l_cnt > 0 then
17 dbms_output.put_line(l_cnt ||' : ' || cur_r.table_name);
18 execute immediate 'delete from ' || cur_r.table_name ||
19 ' where ' ||cur_r.column_name || ' = ''LITTLEFOOT''';
20 end if;
21 end loop;
22 end;
23 /
1 : EMP_TEMP
PL/SQL procedure successfully completed.
SQL> select * From emp_temp where ename = 'LITTLEFOOT';
no rows selected
SQL>
显然,我已经被删除了。已终止。但我会回来的。
您可以循环查询返回的所有表(使用光标(:
select table_name
from user_tab_columns
where column_name IN ('EMP_NAME');
并使用立即执行执行删除:
execute immediate 'DELETE from ' || table_name || ' where EMP_NAME='''ABC'''
完整示例:
for i in (
select distinct table_name
from user_tab_columns
where column_name IN ('EMP_NAME')
) LOOP
execute immediate 'DELETE from ' || i.table_name || ' where EMP_NAME='''ABC''';
END LOOP;