Studio Press

Just another Blogger Blog

Using Cursor In MySQL PLSQL : Trigger

A Cursor can be imagine as A Table in MySQL PLSQL. We can use a cursor as temp table that we process in the PLSQL.

Below i have a simple MySQLTrigger to show how cursor can be use. I use MySQL version 5.1.33.

Please Just See A Red Row of the Program, That i will Explain.


DELIMITER $$

DROP TRIGGER /*!50032 IF EXISTS */ `db_pengabdian`.`update_jum_pengabdi`$$

CREATE
/*!50017 DEFINER = 'root'@'localhost' */
TRIGGER `update_jum_pengabdi` AFTER UPDATE ON `tb_mketua`
FOR EACH ROW BEGIN
DECLARE done INT DEFAULT 0;

DECLARE nip CHAR(30);
DECLARE yr YEAR;
DECLARE jum INT;

DECLARE cur1 CURSOR FOR select distinct(nip_anggota), tahun from tb_manggota union all select distinct(nip_ketua), tahun from tb_mketua;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur1;
set jum = 0;
REPEAT
FETCH cur1 INTO nip, yr;
IF yr = old.tahun THEN
set jum=jum+1;
END IF;
UNTIL done END REPEAT;
CLOSE cur1;
set jum=jum-1;
update tb_dwh_pengabdian set pengabdian_jum_dosen_mengabdi = jum where pengabdian_tahun=old.tahun;


END;
$$

DELIMITER ;



The Red Row of the syntax is a row definiion, then a fetch (something we said to cut per row piece). And at the Green Line, you can find that if the table change on field year has a similar value with a cursor fetch, do something.

That is a simple example of using cursor in In MySQL PLSQL, Using Cursor in a Trigger.

2 comments:

Anonymous said...

saya hanya bisa menggut-manggut sob, hehehee

Anonymous said...

nice info, thanx friend

Labels

Message For Me

Alexa Rank

SEO Stats

Followers

FEEDJIT Live Traffic Feed