masyanto

Query CRUD dalam 1 store procs

Oct 25th, 2020
805
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. DELIMITER $$
  2.  
  3. USE `dbmadrasah`$$
  4.  
  5. DROP PROCEDURE IF EXISTS `sp_CRUD_tblsurat`$$
  6.  
  7. CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_CRUD_tblsurat`(
  8.  IN pIdsurat INT(11),
  9.  IN pidguru INT(11),
  10.  IN ptgl DATE,
  11.  IN pjenis ENUM('masuk','keluar'),
  12.  IN pno_surat VARCHAR(50),
  13.  IN pket_surat VARCHAR(255)
  14.         )
  15. BEGIN
  16.     IF @perintah='simpan' THEN
  17.     INSERT INTO tblsurat (idsurat,idguru,tgl_surat,jenis_surat,no_surat,ket_surat)
  18.     VALUES (pidsurat,pidguru,ptgl,pjenis,pno_surat,pket_surat);
  19.     END IF;
  20.    
  21.     IF @perintah='ubah' THEN
  22.    
  23.     UPDATE tblsurat
  24.     SET
  25.     idguru=pidguru,tgl_surat=ptgl,jenis_surat=pjenis,no_surat=pno_surat,ket_surat=pket_surat
  26.     WHERE
  27.     idsurat=pidsurat;
  28.      END IF;
  29.  
  30.      IF @perintah='hapus'THEN    
  31.     DELETE FROM tblsurat
  32.         WHERE idsurat=pidsurat;
  33.      END IF;
  34.      IF @perintah='tampil' THEN
  35.    
  36.     SELECT * FROM tblsurat;
  37.      END IF;
  38.  
  39.  END$$
  40.  
  41. DELIMITER ;
RAW Paste Data