Simple stored procedure example on Mysql

Posted: January 20, 2011 in IT and Programming Stuff
Tags: , , ,

Berikut ini sebuah stored procedure sederhana di Mysql yang di dalamnya terdapat contoh penggunaan cursor dan loop :

DELIMITER $$

USE `lendor_data`$$

DROP PROCEDURE IF EXISTS `sp_cleanup_reversal_pengembalian`$$

CREATE DEFINER=`root`@`%` PROCEDURE `sp_cleanup_reversal_pengembalian`(IN vNoKredit VARCHAR(20))
BEGIN
DECLARE v_id_bayar BIGINT(20);
DECLARE v_id_sumber_pengembalian BIGINT(20);
DECLARE done INT DEFAULT 0;

DECLARE cursorList CURSOR FOR
	SELECT sumber.ID_SUMBER_PENGEMBALIAN, ang.id_bayar
	FROM t_jmn_sumber_pengembalian_asuransi sumber
		JOIN t_jmn_klaim klm
			ON klm.id_klaim = sumber.ID_KLAIM
		JOIN t_krd_pembayaran_angsuran ang
			ON ang.ID_SUMBER_PENGEMBALIAN = sumber.ID_SUMBER_PENGEMBALIAN
	WHERE 1=1
		AND klm.no_kredit = vNoKredit
		AND ang.FLAG_REVERSAL = 'Y';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cursorList;
myLoop: LOOP
        FETCH cursorList INTO v_id_sumber_pengembalian, v_id_bayar;

        IF done THEN
            CLOSE cursorList;
            LEAVE myLoop;
        END IF;

        -- set field id_sumber_pengembalian dengan null agar data detilnya bisa dihapus
	UPDATE t_krd_pembayaran_angsuran ang
	SET ang.id_sumber_pengembalian = NULL
	WHERE 1=1
	AND ang.id_bayar = v_id_bayar;
	-- hapus data yang belum berhasil dihapus ketika proses reversal angsuran stlh pembebanan
	DELETE FROM t_jmn_sumber_pengembalian_asuransi
	WHERE 1=1
	AND ID_SUMBER_PENGEMBALIAN = v_id_sumber_pengembalian;

END LOOP myLoop;

    END$$

DELIMITER ;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s