DB2 Stored Procedure - controlled large record deletion
Ive written a DB2 Stored Procedure for a housekeeping job that in theory could be deleting large volumes for data from the database.
The requirement is to control the deletion by committing a defined number of records at a time.
Firstly, I would like some feedback on my Stored Procedure to see if there is any improvements I could make.
Secondly, Ive got a question about SQL Errors. If an error occurs during an iteration of the loop, does the Stored Procedure exit immediately ? Ideally I would like to continue the loop trying to delete as many records as I can. Im not sure if my script works in this way or not.
CREATE PROCEDURE leave_loop(IN commit_unit INTEGER, OUT counter INTEGER)
LANGUAGE SQL
BEGIN
DECLARE v_prod_id INTEGER;
DECLARE v_delete_counter INTEGER DEFAULT 0;
DECLARE v_total INTEGER DEFAULT 0;
DECLARE not_found CHAR(1) DEFAULT 'N';
开发者_StackOverflow DECLARE c1 CURSOR WITH HOLD FOR
SELECT prod_id
FROM product
WHERE status_deleted = 1
ORDER BY prod_id;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET not_found = 'Y';
SET counter = 0;
OPEN c1;
delete_loop:
LOOP
-- Fetch the record
FETCH c1 INTO v_prod_id;
-- If not row found then leave the loop
IF not_found = 'Y' THEN
-- If we have not reached the commit unit the commit the outstanding records
IF v_delete_counter > 0 THEN
COMMIT;
END IF;
LEAVE delete_loop;
END IF;
-- Perform the deletion
DELETE FROM product WHERE prod_id = v_prod_id;
SET v_delete_counter = v_delete_counter + 1;
-- Check if the commit unit has been reached
IF MOD(v_delete_counter, commit_unit) = 0 THEN
COMMIT;
SET v_delete_counter = 0;
SET v_total = v_total + 1;
END IF;
END LOOP delete_loop;
CLOSE c1;
SET total = v_total;
END @
I had similar requirements in the past. Please find below the stored procedure which I wrote to serve my needs.
SET SERVEROUTPUT ON@
drop procedure DELETE_WITH_COMMIT_COUNT@
CREATE PROCEDURE DELETE_WITH_COMMIT_COUNT(IN v_TABLE_NAME VARCHAR(24), IN v_COMMIT_COUNT INTEGER, IN v_WHERE_CONDITION VARCHAR(1024))
NOT DETERMINISTIC
LANGUAGE SQL
BEGIN
-- DECLARE Statements
DECLARE SQLCODE INTEGER;
DECLARE v_COUNTER INTEGER DEFAULT 0;
DECLARE v_DELETE_QUERY VARCHAR(1024);
DECLARE v_DELETE_STATEMENT STATEMENT;
SET v_DELETE_QUERY = 'DELETE FROM (SELECT 1 FROM ' || v_TABLE_NAME || ' WHERE ' || v_WHERE_CONDITION
|| ' FETCH FIRST ' || RTRIM(CHAR(v_COMMIT_COUNT)) || ' ROWS ONLY) AS DELETE_TABLE';
PREPARE v_DELETE_STATEMENT FROM v_DELETE_QUERY;
DEL_LOOP:
LOOP
SET v_COUNTER=v_COUNTER + 1;
EXECUTE v_DELETE_STATEMENT;
IF SQLCODE = 100 THEN
LEAVE DEL_LOOP;
END IF;
COMMIT;
END LOOP;
COMMIT;
END@
You can add 'DECLARE CONTINUE HANDLER FOR SQLSTATE XXXX' to your stored procedure which will bypass the erroneous executions and shall avoid abrupt termination of your stored proc.
精彩评论