create or replace package body HCBD_BF_DATA is
procedure HcbdBF( i_temp in varchar2,
i_sjqb in varchar2,
o_result out sys_refcursor) is
l_selectsql varchar2(5000);
l_selectsql1 varchar2(5000);
begin
for x in( delete from (select A.TABLE_NAME where a.TABLESPACE_NAME = 'USERS'
and a.TABLE_NAME like 'HI%'
or a.TABLE_NAME like 'HL%') )loop
commit;
end loop;
用循环delete所有表内的数据时,为什么报错?
procedure HcbdBF( i_temp in varchar2,
i_sjqb in varchar2,
o_result out sys_refcursor) is
l_selectsql varchar2(5000);
l_selectsql1 varchar2(5000);
begin
for x in( delete from (select A.TABLE_NAME where a.TABLESPACE_NAME = 'USERS'
and a.TABLE_NAME like 'HI%'
or a.TABLE_NAME like 'HL%') )loop
commit;
end loop;
用循环delete所有表内的数据时,为什么报错?
end HcbdBF:
end HCBD_BF_DATA;
and a.TABLE_NAME like 'HI%'
or a.TABLE_NAME like 'HL%') )loop
commit;
end loop;
这是要把所有 USERS 表空间里的数据都删除吗?改成下面的试试。
for x in( select A.TABLE_NAME
where a.TABLESPACE_NAME = 'USERS'
and a.TABLE_NAME like 'HI%'
or a.TABLE_NAME like 'HL%')
loop
l_selectsql : = 'delete from ' || x.TABLE_NAME;
execute immediate l_selectsql ;
end loop;