批量删除脚本

Oracle批量删除数据脚本：

#!/bin/bash
source . /home/oracle/.bash_profile

sqlplus wms_sh6tnur6xpyndu << eof

set timing on
set serveroutput on
alter session set sort_area_size=1000000000;


DECLARE
row_num NUMBER := 0;
BEGIN
FOR c_test IN (select id from INVOICE_NO_HIS t1 ) LOOP
delete from INVOICE_NO t where  c_test.id = t.id;

row_num := row_num + 1;
IF MOD(row_num,5000) =0 THEN
COMMIT;
END IF;
END LOOP;
COMMIT;
END;
  
/

MySQL 批量删除存储过程：

CREATE DEFINER = `dbauser`@`%` PROCEDURE `batchdelete`()
BEGIN 
DECLARE s_id int DEFAULT 8000000;
DECLARE e_id INT DEFAULT 0;

WHILE (e_id<=17946137) DO

SET e_id=s_id+500;
DELETE FROM 3rd_log.3rd_stock_sync_log_tmall WHERE id>=s_id AND id<=e_id;
SET s_id=e_id;
SELECT s_id,e_id;

END WHILE;
COMMIT;

END;

转载于:https://www.cnblogs.com/hllnj2008/p/5051258.html