create or replace procedure mw_sys.clearrubbishdatefy2
is
type table_type is table of mw_app.mwt_ud_yscsjdl.tablename%type;
tablenameArray table_type;
str varchar(1000);--定义用于存储动态SQL的变量
begin
select tablename bulk collect into tablenameArray from mw_app.mwt_ud_yscsjdl group by tablename;
for j in 1..tablenameArray.count loop---循环遍历查询表得到的结果
str :=' delete from '||'mw_app.'||tablenameArray(j)||' where obj_id in (select obj_id from mw_app.mwt_ud_yscsjdl where tablename = '''||tablenameArray(j)||''')' ;--拼接查询SQL,单引号的转义是通过双引号进行的
execute immediate str;---立即执行拼接的动态SQL
end loop;
commit;
--dbms_output.put('success');
dbms_output.put_line('success');
exception
when others then
--异常处理
rollback;
-- dbms_output.put('error');
dbms_output.put_line('error');
end;
Oracle 存储过程中执行动态SQL,动态表名
最新推荐文章于 2024-09-13 08:02:47 发布