--****************用动态语句,写过程*********************---
/*参数:返回看是否执行成功*/
create or replace function test2 return varchar2 is
isumrow number:=0;
v_cursor number;
v_string varchar2(300);
v_row number;
t_table_name varchar2(100);
cursor c is select a.table_name from all_tables a where a.table_name like 'SY_%';
begin
open c;
loop
fetch c into t_table_name;
exit when c%notfound;
v_string:='drop table '||t_table_name||' ';
v_cursor:=dbms_sql.open_cursor;
dbms_sql.parse(v_cursor,v_string,dbms_sql.native);
v_row:=dbms_sql.execute(v_cursor);
dbms_sql.close_cursor(v_cursor);
isumrow:=isumrow+v_row;
end loop;
close c;
return ('成功执行'||iSumrow||'张表!');
exception
when others then
return ('执行出错,成功执行'||isumrow||'张表!');
raise;
end;
--以上oracle 8.1.7 编译通过,但是没敢执行,楼主自己去试试吧!
/*参数:返回看是否执行成功*/
create or replace function test2 return varchar2 is
isumrow number:=0;
v_cursor number;
v_string varchar2(300);
v_row number;
t_table_name varchar2(100);
cursor c is select a.table_name from all_tables a where a.table_name like 'SY_%';
begin
open c;
loop
fetch c into t_table_name;
exit when c%notfound;
v_string:='drop table '||t_table_name||' ';
v_cursor:=dbms_sql.open_cursor;
dbms_sql.parse(v_cursor,v_string,dbms_sql.native);
v_row:=dbms_sql.execute(v_cursor);
dbms_sql.close_cursor(v_cursor);
isumrow:=isumrow+v_row;
end loop;
close c;
return ('成功执行'||iSumrow||'张表!');
exception
when others then
return ('执行出错,成功执行'||isumrow||'张表!');
raise;
end;
--以上oracle 8.1.7 编译通过,但是没敢执行,楼主自己去试试吧!
select * from all_tables a where a.table_name like 'SY_%'
然后一条一条去drop.
sql> set pagesize 0
sql> set head off
sql>spool d:\del.sql
sql>select 'drop table ' || table_name ||'; ' from all_tables a where a.table_name like 'SY_%'
sql>spool off
sql>@ d:\del.sql
select 'drop table '|| table_name||';' from user_tables where table_name like 'SY%'
spool offstart c:\aa.txt