由语句select * from ALL_TABLES where owner='aa'可以得到aa表空间的所有表名,请问如何写drop语句将这些表删除
execute immediate 'drop table table_name'
declare v_name all_tables.table_name%type; cursor mycur is select table_name from all_tables where table_name ='aa'; begin open mycur; loop fetch mycur into v_name; exit when mycur%NOTFOUND OR mycur%NOTFOUND IS NULL; execute immediate 'drop table '|| v_name; end loop; close mycur; end;
楼上的老大占了楼梯,晚了一步. cursor mycur is select table_name from all_tables where table_name ='aa'; 上面这句话写错了;改成, cursor mycur is select table_name from all_tables where owner='aa';然后直接拷贝到sql/plus里,执行就可以了,
本例使用DBMS_SQL包可实现(已测试通过),ORACLE8版本以上还可以用EXECUTE IMMEDIATE实现类似功能: set serveroutput on size 1000000 DECLARE t_c1_tname user_tables.table_name%TYPE; t_command varchar2(200); t_user_name varchar2(20):='TEMP';--定义用户名为TEMP t_cid integer; stat integer; cursor c1 is select owner||'.'||table_name table_Name from all_tables where owner=t_user_name order by table_name;--查出TEMP用户下所有表的名字 BEGIN open c1; loop fetch c1 into t_c1_tname;--取出一个表名 exit when c1%NOTFOUND;--如果游标记录取完,退出循环 t_command := 'drop table '||t_c1_tname;--定义SQL命令 t_cid := DBMS_SQL.OPEN_CURSOR;--创建一个游标 DBMS_SQL.PARSE(t_cid,t_command,dbms_sql.native);--向服务器发出一个语句并检查这个语句的语法和语义错误 stat := DBMS_SQL.EXECUTE(t_cid); --执行此语句,删除用户下的表 DBMS_OUTPUT.PUT_LINE('now droping table '||rpad(t_c1_tname,30,' ')); DBMS_SQL.CLOSE_CURSOR(t_cid); end loop; close c1; END; / Best wish to you
set termout off set heading off spool drop_table.sql select 'drop '||table_name||' cascade'||';' from dba_tables where tablespace_name='TEST'; spool off @drop_table.sqltry it
v_name all_tables.table_name%type;
cursor mycur is select table_name from all_tables where table_name ='aa';
begin
open mycur;
loop
fetch mycur into v_name;
exit when mycur%NOTFOUND OR mycur%NOTFOUND IS NULL;
execute immediate 'drop table '|| v_name;
end loop;
close mycur;
end;
cursor mycur is select table_name from all_tables where table_name ='aa';
上面这句话写错了;改成,
cursor mycur is select table_name from all_tables where owner='aa';然后直接拷贝到sql/plus里,执行就可以了,
set serveroutput on size 1000000
DECLARE
t_c1_tname user_tables.table_name%TYPE;
t_command varchar2(200);
t_user_name varchar2(20):='TEMP';--定义用户名为TEMP
t_cid integer;
stat integer;
cursor c1 is select owner||'.'||table_name table_Name from all_tables where owner=t_user_name order by table_name;--查出TEMP用户下所有表的名字
BEGIN
open c1;
loop
fetch c1 into t_c1_tname;--取出一个表名
exit when c1%NOTFOUND;--如果游标记录取完,退出循环
t_command := 'drop table '||t_c1_tname;--定义SQL命令
t_cid := DBMS_SQL.OPEN_CURSOR;--创建一个游标
DBMS_SQL.PARSE(t_cid,t_command,dbms_sql.native);--向服务器发出一个语句并检查这个语句的语法和语义错误
stat := DBMS_SQL.EXECUTE(t_cid); --执行此语句,删除用户下的表
DBMS_OUTPUT.PUT_LINE('now droping table '||rpad(t_c1_tname,30,' '));
DBMS_SQL.CLOSE_CURSOR(t_cid);
end loop;
close c1;
END;
/
Best wish to you
set heading off
spool drop_table.sql
select 'drop '||table_name||' cascade'||';' from dba_tables where tablespace_name='TEST';
spool off
@drop_table.sqltry it