CODE EJHA

DELIMITER $$
CREATE PROCEDURE `process_VerKendaliHarga` (IN in_kode varchar(15))
BEGIN

	DECLARE v_idkatalog varchar(15);
	DECLARE v_reffbatch varchar(40);

	DECLARE v_endloopkatalog BOOLEAN DEFAULT FALSE;
	DECLARE v_endloopbatch BOOLEAN DEFAULT FALSE;

	-- declare cursor for id_katalog && NOT FOUND handler
	DECLARE c_idkatalog CURSOR FOR SELECT A.id_katalog FROM tdetailf_konsinyasi A WHERE A.kode_reff = in_kode;
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_endloopkatalog = TRUE;
	OPEN c_idkatalog;
    for_each_katalog: LOOP

        IF v_endloopkatalog = TRUE THEN
			LEAVE for_each_katalog;
		END IF;
		FETCH c_idkatalog INTO v_idkatalog;

        DECLARE c_reffbatch CURSOR FOR SELECT A.kode_reffbatch FROM tdetailf_konsinyasirinc A WHERE A.kode_reff=in_kode AND A.id_katalog=v_idkatalog;
		DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_endloopbatch = TRUE;
        OPEN c_reffbatch;
			for_each_reffbatch: LOOP

				IF v_endloopbatch = TRUE THEN
						LEAVE for_each_reffbatch;
					END IF;
				FETCH c_reffbatch INTO v_reffbatch;

			END LOOP for_each_reffbatch;
		CLOSE c_reffbatch;

    END LOOP for_each_katalog;
    CLOSE c_idkatalog;

END $$

DELIMITER ;