oracle 存储过程拼接表名_Oracle 存储过程中执行动态SQL，动态表名 | 学步园

create or replace procedure mw_sys.clearrubbishdatefy2istype table_type is table of mw_app.mwt_ud_yscsjdl.tablename%type;tablenameArray table_type;str varchar(1000);--定义用于存储动态SQL的变量beginselect tablena

weixin_39877182

2000人浏览 · 2020-12-21 09:42:44

weixin_39877182 · 2020-12-21 09:42:44 发布

create or replace procedure mw_sys.clearrubbishdatefy2

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;