Prosedure MySQL pertamaku.

fasilitas ini sebenernya fasilitas kuno yang tersedia di mysql. cuman kali ini aja kayaknya butuh prosedur untuk menginput datanya. so, belajar dulu. dan alhamdulillah berhasil sesuai dengan yang diinginkan. šŸ™‚

 

ini dia prosedure buatan saya:

DELIMITER $$

DROP PROCEDURE IF EXISTS `save_state`$$
CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
PROCEDURE `kang_aep`.`save_state`(id_line VARCHAR(10),
fg_qty INT,
ng_qty INT,
er_qty INT)
/*LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT ‘string’*/
BEGIN
DECLARE last_id INT(11) DEFAULT 0;
DECLARE last_fg INT(11) DEFAULT 0;
DECLARE last_ng INT(11) DEFAULT 0;
DECLARE last_er INT(11) DEFAULT 0;
DECLARE newNG BOOL DEFAULT FALSE;
SELECT MAX(t_counting_id)
INTO last_id
FROM t_counting
WHERE t_counting.`line_id` = id_line;

SELECT t_counting_id,
COALESCE( fg_quantity,0),
COALESCE(ng_quantity,0),
COALESCE(e_quantity,0)
INTO last_id, last_fg, last_ng, last_er
FROM t_counting
WHERE t_counting.`line_id` = id_line
AND t_counting_id = last_id;

WHILE last_fg < fg_qty DO
SET last_fg := last_fg + 1;
INSERT INTO t_counting(line_id,ng_quantity, fg_quantity,e_quantity, TYPE)
VALUE(id_line, last_ng, last_fg, last_er, “FG”);
END WHILE;

WHILE last_ng < ng_qty DO
SET last_ng := last_ng + 1;
INSERT INTO t_counting(line_id,ng_quantity, fg_quantity,e_quantity, TYPE)
VALUE(id_line, last_ng, last_fg, last_er, “NG”);
SET newNG := TRUE;
END WHILE;

WHILE last_er < er_qty DO
SET last_er := last_er + 1;
INSERT INTO t_counting(line_id,ng_quantity, fg_quantity,e_quantity, TYPE)
VALUE(id_line, last_ng, last_fg, last_er, “ER”);
END WHILE;
IF newNG THEN
INSERT INTO `t_message`(`number`, `message`)
(SELECT `telp`, CONCAT(“Telah terdeteksi metal di line (“, `linename` ,”), plan (“, `planname`, “) sejumlah “, `ng_quantity`, “pcs dengan klaim error sejumlah “, `e_quantity`, “pcs.”) message
FROM `v_pre_sms` WHERE `v_pre_sms`.`line_id` = id_line);
INSERT INTO outbox(`DestinationNumber`, `TextDecoded`)
(SELECT `telp`, CONCAT(“Telah terdeteksi metal di line (“, `linename` ,”), plan (“, `planname`, “) sejumlah “, `ng_quantity`, “pcs dengan klaim error sejumlah “, `e_quantity`, “pcs.”) message
FROM `v_pre_sms` WHERE `v_pre_sms`.`line_id` = id_line);
END IF;
END$$

DELIMITER ;

alhamdulillah berjalan dengan baik sekali. šŸ™‚

Advertisements

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