create or replace procedure ZFJC_DROP_TEMPTABLE
is
num number;
tempTbl varchar2(100);
begin
select count(*) into num
from tabs
where substr(table_name,1,2) = upper('tp');
Loop
select table_name into tempTbl
from tabs
where substr(table_name,1,2) = upper('tp')
and rwonum=1;
execute immediate 'drop table '||tempTbl||'';
num := num -1;
exit when num=0;
end loop;
end;
----------------------
第一tabs中的不一定全是表
第二,动态语句删除表需要delete(any)table系统特权
is
num number;
tempTbl varchar2(100);
begin
select count(*) into num
from tabs
where substr(table_name,1,2) = upper('tp');
Loop
select table_name into tempTbl
from tabs
where substr(table_name,1,2) = upper('tp')
and rwonum=1;
execute immediate 'drop table '||tempTbl||'';
num := num -1;
exit when num=0;
end loop;
end;
----------------------
第一tabs中的不一定全是表
第二,动态语句删除表需要delete(any)table系统特权
问题二:在过程中动态执行,要有相关权限
create or replace procedure ZFJC_DROP_TEMPTABLE
is
num number;
tempTbl varchar2(100);
begin
select count(*) into num from tabs where substr(table_name,1,2) = upper('tp'); Loop
exit when num=0;
select table_name into tempTbl from tabs where substr(table_name,1,2) = upper('tp') and rwonum=1;
execute immediate 'drop table '||tempTbl;
num := num -1;
end loop;
end;
/