select * from sys.col a left join sys.tab b on a.tname = b.tname where a.cname = 'L_FUND_ID' and a.tname like'T%' 该语句可查出所有包含'L_FUND_ID' 字段的表的信息。 现在我想删除所有表中L_FUND_ID=‘1’的信息。 请达人提供存储过程。
DECLARE TYPE t_cursor IS ref CURSOR; v_cursor t_cursor; tabname varchar2(20); BEGIN OPEN v_cursor FOR select b.tname from sys.col a left join sys.tab b on a.tname = b.tname where a.cname = 'L_FUND_ID' and a.tname like'T%'; loop fetch v_cursor into tabname; EXIT WHEN v_cursor%NOTFOUND; delete from tabname t where t.L_FUND_ID='1'; end loop; CLOSE v_cursor; commit;EXCEPTION WHEN OTHERS THEN ROLLBACK; END ;
v_sql := 'delete from '||tabname||' t where t.L_FUND_ID=''1'''; execute immediate v_sql;
delete from tabname t where t.L_FUND_ID='1'; tabname從哪里來的?
DECLARE TYPE t_cursor IS ref CURSOR; v_cursor t_cursor; tabname varchar2(20); BEGIN OPEN v_cursor FOR select b.tname from sys.col a left join sys.tab b on a.tname = b.tname where a.cname = 'L_FUND_ID' and a.tname like'T%'; loop fetch v_cursor into tabname; EXIT WHEN v_cursor%NOTFOUND; delete from tabname t where t.L_FUND_ID='1'; end loop; CLOSE v_cursor; commit;EXCEPTION WHEN OTHERS THEN ROLLBACK; END ;
DECLARE
TYPE t_cursor IS ref CURSOR;
v_cursor t_cursor;
tabname varchar2(20);
BEGIN
OPEN v_cursor FOR select b.tname from sys.col a left join sys.tab b on a.tname = b.tname where a.cname = 'L_FUND_ID' and a.tname like'T%';
loop
fetch v_cursor into tabname;
EXIT WHEN v_cursor%NOTFOUND;
delete from tabname t where t.L_FUND_ID='1';
end loop;
CLOSE v_cursor;
commit;EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END ;
execute immediate v_sql;
tabname從哪里來的?
TYPE t_cursor IS ref CURSOR;
v_cursor t_cursor;
tabname varchar2(20);
BEGIN
OPEN v_cursor FOR select b.tname from sys.col a left join sys.tab b on a.tname = b.tname where a.cname = 'L_FUND_ID' and a.tname like'T%';
loop
fetch v_cursor into tabname;
EXIT WHEN v_cursor%NOTFOUND;
delete from tabname t where t.L_FUND_ID='1';
end loop;
CLOSE v_cursor;
commit;EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END ;