SQL> select table_name from user_tables where table_name like 'T_';TABLE_NAME
------------------------------
TB
T5
T4
T2
T1declare
s varchar2(100);
begin
for x in (select table_name from user_tables where table_name like 'T_') loop
s := 'drop table :1';
dbms_output.put_line(s);
execute immediate s using x.table_name;
end loop;
end;
错误如下:请问这是怎么回事啊?drop table :1
declare
*
ERROR at line 1:
ORA-00903: invalid table name
ORA-06512: at line 7
s varchar2(100);
begin
for x in (select table_name from user_tables where table_name like 'T_') loop
s := 'drop table ' || x.table_name;
dbms_output.put_line(s);
execute immediate s;
end loop;
end;
dbms_output.put_line(s);
execute immediate s using x.table_name;
因为你这段话等效于
drop table 'T1';
2 s varchar2(100);
3 begin
4 for x in (select table_name from user_tables where table_name like 'T_') loop
5 s := 'drop table :1';
6 dbms_output.put_line(x.table_name);
7 --execute immediate s using x.table_name;
8 end loop;
9* end;
10 /
T1PL/SQL procedure successfully completed.SQL> 不会呀,测了一把x.table_name应该还是T1而不是'T1',麻烦再指点一下,谢谢。
以上使用 PL/SQL的 "绑定变量" 机制。上面的又不是DML命令!
但是你在DML里面用变量替代表明,自然看做drop table '表名'处理了。