INSERT, UPDATE, DELETE MYSQL STORED PROCEDURE

MYSQL STORED PROCEDURE OPERASI CRUD

BUATLAH DATABASE CRUDSTOREPROCEDURE.

CREATE TABLE `mahasiswa` (

  `NIM` char(3) default NULL,

  `NAMA` char(60) default NULL,

  `JENISKELAMIN` enum('L','P') default NULL

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

BUAT PROCEDURE UNTUK INSERT,UPDATE DAN DELETE DATA

DELIMITER $$

DROP PROCEDURE IF EXISTS `crudstoreprocedure`.`INSERTDATA`$$

CREATE PROCEDURE `crudstoreprocedure`.`INSERTDATA`(NIM CHAR(3),NAMA CHAR(60),JK ENUM('L','P'))

      BEGIN

     INSERT INTO MAHASISWA (NIM,NAMA,JENISKELAMIN)

     VALUES

     (NIM,NAMA,JK);

    END$$

DELIMITER ;

DELIMITER $$

DROP PROCEDURE IF EXISTS `crudstoreprocedure`.`EDITDATA`$$

CREATE PROCEDURE `crudstoreprocedure`.`EDITDATA`(NIMLAMA CHAR(3),NAMA CHAR(60),JK ENUM('L','P'))

    BEGIN

     UPDATE MAHASISWA SET NAMA=NAMA,JENISKELAMIN=JK

     WHERE NIM=NIMLAMA;

    END$$

DELIMITER ;

DELIMITER $$

DROP PROCEDURE IF EXISTS `crudstoreprocedure`.`DELETEDATA`$$

CREATE PROCEDURE `crudstoreprocedure`.`DELETEDATA`(NIMLAMA CHAR(3))

    BEGIN

     DELETE FROM MAHASISWA WHERE NIM=NIMLAMA;

    END$$

DELIMITER ;

MENAMBAHKAN DATA

mysql> CALL INSERTDATA('002','ELLENA','P');

Query OK, 1 row affected (0.08 sec)

mysql> CALL INSERTDATA('001','PURWANTO,S.KOM','L');

Query OK, 1 row affected (0.00 sec)

mysql> CALL INSERTDATA('003','PAIJO','L');

Query OK, 1 row affected (0.09 sec)

mysql> SELECT*FROM MAHASISWA;

+------+----------------+--------------+

| NIM  | NAMA           | JENISKELAMIN |

+------+----------------+--------------+

| 001  | PURWANTO,S.KOM | L            |

| 002  | ELLEN          | P            |

| 003  | PAIJO          | L            |

+------+----------------+--------------+

3 rows in set (0.03 sec)

MENGUPDATE DATA

mysql> CALL EDITDATA('002','ELLENA,S.H','P');

Query OK, 1 row affected (0.01 sec)

mysql> SELECT*FROM MAHASISWA;

+------+----------------+--------------+

| NIM  | NAMA           | JENISKELAMIN |

+------+----------------+--------------+

| 002  | ELLENA,S.H     | P            |

| 003  | PAIJO          | L            |

| 001  | PURWANTO,S.KOM | L            |

+------+----------------+--------------+

3 rows in set (0.00 sec)

MENGHAPUS DATA

mysql> CALL DELETEDATA('003');

Query OK, 1 row affected (0.00 sec)

mysql> SELECT*FROM MAHASISWA;

+------+----------------+--------------+

| NIM  | NAMA           | JENISKELAMIN |

+------+----------------+--------------+

| 002  | ELLENA,S.H     | P            |

| 001  | PURWANTO,S.KOM | L            |

+------+----------------+--------------+

2 rows in set (0.00 sec)

NOTE: NAMA PARAMETER HARUS BEDA DENGAN NAMA FIEL PADA TABEL.

SERTAKAN LINK JIKA INGIN MENCOPY..

SALAM ALUMNI AKAKOM 2013