MySQL Stored Procedure

coba bikin table afei
lalu bikin SP test

• Server type: MariaDB
• Server version: 10.1.22-MariaDB - mariadb.org binary distribution
• Protocol version: 10 
• Apache/2.4.25 (Win32) OpenSSL/1.0.2j PHP/7.1.4 

isinya:


DELIMITER $$
--
-- Procedures
--
CREATE DEFINER=`root`@`localhost` PROCEDURE `test` (IN `idx` INT, IN `pesan2` VARCHAR(50))  NO SQL
BEGINSP:
BEGIN
  -- declare var disini
  DECLARE i INT DEFAULT 0;
  DECLARE keterangan VARCHAR(200) DEFAULT '';
  DECLARE tidakKetemu INT;

  -- declare cursor utk lock disini
  DECLARE cLock
   CURSOR FOR
    SELECT afei_id
      FROM afei
     WHERE afei_id=idx
    -- AND last_updated_date=xlast_updated_date
       FOR UPDATE;
      
  -- declare cursor utk looping disini
  DECLARE cData
   CURSOR FOR
   SELECT afei_id, pesan
     FROM afei
    WHERE afei_id>100;

  -- BEGIN: declare ini di akhir
  DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING
  BEGIN
    ROLLBACK;
    GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
     SET @full_error = CONCAT("Ada ERROR ", @errno, " (", @sqlstate, "): ", @text);
     SELECT @full_error;
  END;
  -- CONTINUE krn mungkin ada cursor yg lain
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET tidakKetemu=1;
  START TRANSACTION;
  -- END: declare ini di akhir
 
  SET tidakKetemu=0;
  OPEN cLock;
  FETCH cLock INTO i;
  IF tidakKetemu=1 THEN
    ROLLBACK;
     SET @full_error = "Data berubah! Mohon refresh kembali.";
     SELECT @full_error;
     LEAVE BEGINSP;
  END IF;
 
  UPDATE AFEI set pesan=pesan2 WHERE afei_id=idx;

  /* ini diremark = bentuk umum
  COMMIT;            -- setelah komit
  START TRANSACTION; -- harus start lagi
  */
  
  -- utk trace:
  -- select 1;
 
  INSERT INTO AFEI(afei_id,tgl,pesan)
  VALUES(1233,now(),'test');

  /* ini diremark = bentuk umum
  COMMIT;            -- setelah komit
  START TRANSACTION; -- harus start lagi
  */

  SET tidakKetemu=0;
  OPEN cData;
  cDataLoop: LOOP
    FETCH cData INTO i,keterangan;
    IF tidakKetemu=1 THEN
       CLOSE cData;
       LEAVE cDataLoop;
    END IF;
    -- lakukan sesuatu:
    UPDATE afei SET pesan=concat(keterangan,pesan2) WHERE afei_id=i;
  END LOOP cDataLoop;

  COMMIT;
END$$

DELIMITER ;