DECLARE
CURSOR MYCURSOR IS
SELECT ROWID FROM TEST WHERE XXX = XXXX ORDER BY ROWID; --------按ROWID排序的Cursor,删除条件是XXX=XXXX,根据实际情况来定。 TYPE ROWID_TABLE_TYPE IS TABLE OF ROWID INDEX BY PLS_INTEGER;
V_ROWID ROWID_TABLE_TYPE;
BEGIN
OPEN MYCURSOR;
LOOP
FETCH MYCURSOR BULK COLLECT
INTO V_ROWID LIMIT 5000; --------每次处理5000行,也就是每5000行一提交
EXIT WHEN V_ROWID.COUNT = 0;
FORALL I IN V_ROWID.FIRST .. V_ROWID.LAST
DELETE FROM TEST WHERE ROWID = V_ROWID(I);
COMMIT;
END LOOP;
CLOSE MYCURSOR;
END;
CURSOR MYCURSOR IS
SELECT ROWID FROM TEST WHERE XXX = XXXX ORDER BY ROWID; --------按ROWID排序的Cursor,删除条件是XXX=XXXX,根据实际情况来定。 TYPE ROWID_TABLE_TYPE IS TABLE OF ROWID INDEX BY PLS_INTEGER;
V_ROWID ROWID_TABLE_TYPE;
BEGIN
OPEN MYCURSOR;
LOOP
FETCH MYCURSOR BULK COLLECT
INTO V_ROWID LIMIT 5000; --------每次处理5000行,也就是每5000行一提交
EXIT WHEN V_ROWID.COUNT = 0;
FORALL I IN V_ROWID.FIRST .. V_ROWID.LAST
DELETE FROM TEST WHERE ROWID = V_ROWID(I);
COMMIT;
END LOOP;
CLOSE MYCURSOR;
END;
解决方案 »
- 如何判断表是否需要重组?
- PL/SQL中变量的初始值问题
- PL/SQL 过程或函数中如何调用DOS命令?
- 为什么“select 1234 into 变量 from dual”报错?
- 郁闷啊~~~再一次挂彩了(冷备份)
- oracle 游标问题
- 我启动oracle时,怎样改为不加pfile启动呀
- 安装oracle 11.2.0.3.0时出现: 由于以下错误,Enterprise Manager配置失败-将配置数据上载到资料档案库时出错
- 天天晚上可以吃老婆做的饭了(散分)
- 加了 别名 后正确,去掉就报错
- oracle plsql存储过程调试 如何传入date参数
- 带参数的存储过程 发生奇怪的错误 请帮帮忙~
/**
** Usage: run the script to create the proc deleteTab
** in SQL*PLUS, type "exec deleteTab('Foo','ID>=1000000','3000');"
** to delete the records in the table "Foo", commit per 3000 records.
** Condition with default value '1=1' and default Commit batch is 10000.
**/
(
p_TableName in varchar2, -- The TableName which you want to delete from
p_Condition in varchar2 default '1=1', -- Delete condition, such as "id>=100000"
p_Count in varchar2 default '10000' -- Commit after delete How many records
)
as
pragma autonomous_transaction;
n_delete number:=0;
begin
while 1=1 loop
EXECUTE IMMEDIATE
'delete from '||p_TableName||' where '||p_Condition||' and rownum <= :rn'
USING p_Count;
if SQL%NOTFOUND then
exit;
else
n_delete:=n_delete + SQL%ROWCOUNT;
end if;
commit;
end loop;
commit;
DBMS_OUTPUT.PUT_LINE('Finished!');
DBMS_OUTPUT.PUT_LINE('Totally '||to_char(n_delete)||' records deleted!');
end;