Penggunaan Cursor MYSQL

2009 April 22 at 10:14 am Leave a comment

SEKENARIO:
1. buat table test1
CREATE TABLE  `test1` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`data` int(10) unsigned NOT NULL,
`keterangan` varchar(45) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;
2. masukkan data ke table test1, field keterangan sengaja jangan diisi
mysql>insert into test1(data) values (2), (0),(1000),(0),(2);
3. buat procedure untuk mengupdate field keterangan dengan membandingkan id dan data lebih besar atau lebih kecil
DELIMITER $$
DROP PROCEDURE IF EXISTS test_cursor $$
CREATE PROCEDURE test_cursor()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE a, b INT;
DECLARE cur1 CURSOR FOR SELECT id,data FROM test1;
#agar tidak keluar error “No data – zero rows fetched, selected, or processe”
DECLARE CONTINUE HANDLER FOR SQLSTATE ‘02000’ SET done = 1;
OPEN cur1;
REPEAT
FETCH cur1 INTO a, b;
IF NOT done THEN
IF a > b THEN
update test1 set keterangan=concat(a,’ > ‘,b)WHere id=a ;
ELSE
update test1 set keterangan=concat(a,’ < ‘,b)WHere id=a;
END IF;
END IF;
UNTIL done END REPEAT;
CLOSE cur1;
END $$
DELIMITER ;
4.Jalanakan procedure test_cursor()
mysql>call test_cursor();
5. Lihat table test1 setelah diupdate dengan cursor, maka field keterangan akan terisi
mysql>select * from test1;

Entry filed under: MYSQL. Tags: .

Pelenyap Kesusahan dan Kesempitan. Rasulullah saw dan Hutang

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

Trackback this post  |  Subscribe to the comments via RSS Feed


Categories


%d bloggers like this: