delete from tn where time<to_date('20081010','yyyymmdd'); 非要写存储过程 create or replace pro_delete as begin delete from tn where time<to_date('20081010','yyyymmdd');---删除语句 commit;--提交 end;
为什么要写存储过程? 直接用SQL就好了嘛 delete from tn where time<to_date('20081010','yyyymmdd');
delete from tn where time<to_date('20081010','yyyymmdd') and exists select(1 from tn,TNa,TNb whre TNa.nid=tn.id and tn.id=tnb.id)
delete from tna where nid=' '; delete from tnb where nid=' '; commit;或是写个触发器删除TNA的时候同时删除TnbCreate or repalce trigger tri_tna after delete on tna for each row begin if deleting then delete from tnb where nid=:old.nid; end if; EXCEPTION WHEN others THEN null; end;
delete from tn where exists ( select 1 from tna, tnb where tna.nid = tn.id and tnb.nid = tn.id )
慎用!conn 用户名/密码@连接串; set heading off set feedback off select 'drop trigger '||' '||object_name||' ;' from user_objects ; set heading on set feedback on commit;
非要写存储过程
create or replace pro_delete
as
begin
delete from tn where time<to_date('20081010','yyyymmdd');---删除语句
commit;--提交
end;
还有两张表TNa(id,nid,...)、TNb(id,nid...)与TN关联。TNa.nid、TNb.nid都与TN.id是对应的。
直接用SQL就好了嘛
delete from tn where time<to_date('20081010','yyyymmdd');
TNa.nid=tn.id and tn.id=tnb.id)
delete from tnb where nid=' ';
commit;或是写个触发器删除TNA的时候同时删除TnbCreate or repalce trigger tri_tna
after delete
on tna
for each row
begin
if deleting then
delete from tnb where nid=:old.nid;
end if;
EXCEPTION
WHEN others THEN
null;
end;
from tn
where exists
(
select 1
from tna, tnb
where tna.nid = tn.id
and tnb.nid = tn.id
)
set heading off
set feedback off
select 'drop trigger '||' '||object_name||' ;' from user_objects ;
set heading on
set feedback on
commit;