下一个文件,如xxx.sql,如下: set serveroutput on declare cursor call_ticket_cur//光标 is select rowid from call_ticket10//表名 where state = '606'; curRec call_ticket_cur%rowtype; record_count number(10); begin record_count := 0; open call_ticket_cur; loop fetch call_ticket_cur into curRec; exit when call_ticket_cur%notfound;
delete from call_ticket10 where rowid = curRec.rowid; record_count := record_count + 1; if mod(record_count,1000) = 0 then commit; end if; end loop; close call_ticket_cur; dbms_output.put_line('Process records: '||record_count); commit; end; /如xxx.sql放在C:,可在sqlplus中用户登陆后执行@c:\xxx.sql 或直接在cmd模式下执行sqlplus UserName/Password@Server @c:\xxx.sql
不需要那么复杂吧! 一个for循环,然后循环内部: delete from table where rownum<=1000 and 自定义条件; commit;就可以搞定了!
如果部分删除的话用delete .
区别:truncate删除整个表的数据,不分配回滚段,删除不可以回滚.
delete删除时可以回滚的.
是不是truncate的作用就是快速删除表中的数据而保留表的结构呢???
用DELETE 很忙啊?
set serveroutput on
declare
cursor call_ticket_cur//光标 is
select rowid from call_ticket10//表名 where state = '606';
curRec call_ticket_cur%rowtype;
record_count number(10);
begin
record_count := 0;
open call_ticket_cur;
loop
fetch call_ticket_cur into curRec;
exit when call_ticket_cur%notfound;
delete from call_ticket10 where rowid = curRec.rowid;
record_count := record_count + 1;
if mod(record_count,1000) = 0 then
commit;
end if;
end loop;
close call_ticket_cur;
dbms_output.put_line('Process records: '||record_count);
commit;
end;
/如xxx.sql放在C:,可在sqlplus中用户登陆后执行@c:\xxx.sql
或直接在cmd模式下执行sqlplus UserName/Password@Server @c:\xxx.sql
一个for循环,然后循环内部:
delete from table where rownum<=1000 and 自定义条件;
commit;就可以搞定了!