v number; s varchar2(100); v:=dbms_sql.open_cursor; s:="drop table yourtable"; dbms_sql.parse(v,s,dbms_sql.v7); s:="...";重新赋值 dbms_sql.parse(...); dbms_sql.close_cursor(v);
You should select the table you want delete.cursor delTable is select table_name from all_tables; tableName varchar2(50);begin open delTable; loop fetch delTable into tableName; execute immediate 'drop table'||tableName exit when delTable%notfound; end loop;
declare cursor delTable is select table_name from all_tables where owner='NSS7.1.0' and tablespaces_name='NSS6'; tableName varchar2(50); begin open delTable; loop fetch delTable into tableName; execute immediate 'drop table'||tableName exit when delTable%notfound; end loop; * end > / exit when delTable%notfound; * OR 位于第 10 行: -06550: line 10, column 4: -00103: Encountered the symbol "EXIT" when expecting one of the following: * @ % & = - + ; < / > at in mod not rem return returning exponent (**)> <> or != or ~= >= <= <> and or like ween into using is null is not || is dangling
笨方法,把所有删除语句生成到一个脚本里。 在PL/SQL下 SQL>spool e:\a.sql SQL>select 'drop table ' || table_name || ' cascade constraints;' from user_tables; SQL>spool off 接着修改一下产生的a.sql文件,执行它即可。
s varchar2(100);
v:=dbms_sql.open_cursor;
s:="drop table yourtable";
dbms_sql.parse(v,s,dbms_sql.v7);
s:="...";重新赋值
dbms_sql.parse(...);
dbms_sql.close_cursor(v);
select table_name from all_tables;
tableName varchar2(50);begin
open delTable;
loop
fetch delTable into tableName;
execute immediate 'drop table'||tableName
exit when delTable%notfound;
end loop;
declare
cursor delTable is
select table_name from all_tables where owner='NSS7.1.0' and tablespaces_name='NSS6';
tableName varchar2(50);
begin
open delTable;
loop
fetch delTable into tableName;
execute immediate 'drop table'||tableName
exit when delTable%notfound;
end loop;
* end
> /
exit when delTable%notfound;
*
OR 位于第 10 行:
-06550: line 10, column 4:
-00103: Encountered the symbol "EXIT" when expecting one of the following:
* @ % & = - + ; < / > at in mod not rem return returning
exponent (**)> <> or != or ~= >= <= <> and or like
ween into using is null is not || is dangling
在PL/SQL下
SQL>spool e:\a.sql
SQL>select 'drop table ' || table_name || ' cascade constraints;' from user_tables;
SQL>spool off
接着修改一下产生的a.sql文件,执行它即可。