delete from a where exists(select 1 from b where id=a.id and time=a.time)
亏了,被老beckham给枪先了!555555555555555~~~:)delete from a where exists(select 1 from b where id=a.id and time=a.time)向这样的条件删除语句,由于用到了全表扫描,速度会比较慢,当对一个不大的表,还可以运行运行,但当这个表很大的时候,建议最好还是写procedure.比如说 先定义一个游标 declear cursor vdeal_result is select a.id from a,b where a.id=a.id and b.time=a.time 然后在进行循环的delete操作 delete from a where id = vdeal_result 虽然这样麻烦一些,但可以提高一些时间效率
create function is_int(p_time in varchar2) return number as num number; n number:=0; begin num:=length(p_time); loop n:=n+1; for i in 0..9 loop if instr(substr(p_time,1,n),i)=0 then return 0; end if; end loop; exit when n=num; end loop; return 1; end; /delete from a where exists(select 1 from b where id=a.id and time=a.time) and is_int(time)=0
create function is_int(p_time in varchar2) return number as num number; n number:=0; begin loop n:=n+1; for i in 0..9 loop mum:=i; if substr(p_time,1,n)=to_char(i) then exit; end if; end loop; if num=9 then return 0; end if; exit when n=length(p_time); end loop; return 1; end; /
先定义一个游标
declear
cursor vdeal_result is
select a.id from a,b where a.id=a.id and b.time=a.time
然后在进行循环的delete操作
delete from a where id = vdeal_result
虽然这样麻烦一些,但可以提高一些时间效率
return number
as
num number;
n number:=0;
begin
num:=length(p_time);
loop
n:=n+1;
for i in 0..9 loop
if instr(substr(p_time,1,n),i)=0 then
return 0;
end if;
end loop;
exit when n=num;
end loop;
return 1;
end;
/delete from a where exists(select 1 from b where id=a.id and time=a.time) and is_int(time)=0
return number
as
num number;
n number:=0;
begin
loop
n:=n+1;
for i in 0..9 loop
mum:=i;
if substr(p_time,1,n)=to_char(i) then
exit;
end if;
end loop;
if num=9 then
return 0;
end if;
exit when n=length(p_time);
end loop;
return 1;
end;
/