Cursor & Stored Procedure (Lanjutan)

1.      Buatlah cursor dari tabel-tabel berikut :

Tb_golongan

Tb_jabatan

Tb_pegawai

1.      Hitunglah Gaji Pokok

DELIMITER $$

CREATE

    PROCEDURE `db_kantor`.`hitung_gaji`()

    BEGIN

      DECLARE id_jabat, id_golong,gajigaji,tunjangan2, totalgaji INT;

      SET id_jabat :=(SELECT id_jabatan FROM tb_pegawai WHERE id_pegawai=id); /*= 1*/

      SET id_golong :=(SELECT id_gol FROM tb_pegawai WHERE id_pegawai=id); /*= 1*/

      SET gajigaji :=(SELECT gaji FROM tb_golongan WHERE id_gol=id_golong); /*= 1000*/

      SET tunjangan2 :=(SELECT tunjangan FROM tb_jabatan WHERE id_jabatan=id_jabat); /*= 100*/

      SET totalgaji=gajigaji+tunjangan2; /* 100+100 =1100*/

    END$$

DELIMITER ;

2.      Cursor Menghitung Gaji Pokok

DELIMITER $$

CREATE

    PROCEDURE `db_kantor`.`cursor_hitung_gaji`()

   

    BEGIN

      DECLARE done INT DEFAULT 0;

      DECLARE id_p INT;

      DECLARE prodrecord

      CURSOR FOR SELECT id_pegawai FROM tb_pegawai;

      DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;

      OPEN prodrecord;

      WHILE NOT done DO

      FETCH prodrecord INTO id_p;

      CALL hitung_gaji(id_p,@a);

      IF @a IS NULL THEN

            SET @a=0;

      END IF;

      IF NOT done THEN

            UPDATE tb_pegawai SET total_gaji=@a WHERE id_pegawai=id_p;

      END IF;

      END WHILE;

      CLOSE prodrecord;

    END$$

DELIMITER ;

3.      Memanggil Cursor

SELECT gaji FROM tb_golongan WHERE id_gol=1

`hitung_gaji`(IN id INT, OUT totalgaji INT)

CALL hitung_gaji(1,@a);

SELECT @a;

CALL `cursor_hitung_gaji`();

Download Filenya : disini