purwant's blog : FUNCTION MYSQL, MEMBUAT FUNGSI PADA MYSQL

MYSQL FUNCTION.

FUNCTION MERUPAKAN FUNGSI TERSIMPAN DALAM DATABASE SERVER MYSQL. FUNGSI BERBEDA DENGAN STORED PROCEDURE, FUNGSI MEMILIKI NILAI KEMBALIAN SEDANGKAN STORED PROCEDURE TIDAK.

BUATLAH DATABASE “AKADEMIK”.

DAN BUAT TABEL BESERTA ISINYA SEPERTI BERIKUT.

-- phpMyAdmin SQL Dump

-- version 2.10.2

-- http://www.phpmyadmin.net

-- Host: localhost

-- Generation Time: Aug 29, 2014 at 11:09 PM

-- Server version: 5.0.45

-- PHP Version: 5.2.3

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

-- Database: `akademik`

-- --------------------------------------------------------

-- Table structure for table `dosen`

CREATE TABLE `dosen` (

`KODEDOSEN` char(5) NOT NULL,

`NAMADOSEN` char(50) default NULL,

PRIMARY KEY (`KODEDOSEN`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- Dumping data for table `dosen`

INSERT INTO `dosen` VALUES ('001', 'YOHAKIM');

INSERT INTO `dosen` VALUES ('002', 'SRI REJEKI');

INSERT INTO `dosen` VALUES ('003', 'BADIYANTO');

INSERT INTO `dosen` VALUES ('004', 'CUK SUBIANTORO');

-- --------------------------------------------------------

-- Table structure for table `jurusan`

CREATE TABLE `jurusan` (

`KODEJUR` char(2) NOT NULL,

`NAMAJUR` char(50) default NULL,

`JENJANG` enum('S1','DIII') default NULL,

PRIMARY KEY (`KODEJUR`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- Dumping data for table `jurusan`

INSERT INTO `jurusan` VALUES ('TI', 'TEKNIK INFORMATIKA', 'S1');

INSERT INTO `jurusan` VALUES ('MI', 'MANAJEMEN INFORMATIKA', 'DIII');

INSERT INTO `jurusan` VALUES ('SI', 'SISTEM INFORMASI', 'S1');

INSERT INTO `jurusan` VALUES ('TK', 'TEKNIK KOMPUTER', 'DIII');

INSERT INTO `jurusan` VALUES ('KA', 'KOMPUTERISASI AKUTANSI', 'DIII');

-- --------------------------------------------------------

-- Table structure for table `kuliah`

CREATE TABLE `kuliah` (

`NIM` char(9) default NULL,

`KODEMK` char(5) default NULL,

`KODEDOSEN` char(9) default NULL,

`NILAI` char(1) default NULL

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- Dumping data for table `kuliah`

INSERT INTO `kuliah` VALUES ('095410163', 'PW01', '001', 'A');

INSERT INTO `kuliah` VALUES ('095410163', 'PW02', '002', 'A');

INSERT INTO `kuliah` VALUES ('095410163', 'MD01', '003', 'B');

INSERT INTO `kuliah` VALUES ('095410163', 'SD01', '004', 'B');

INSERT INTO `kuliah` VALUES ('095410163', 'PW11', '001', 'B');

INSERT INTO `kuliah` VALUES ('095410163', 'PW12', '002', 'C');

INSERT INTO `kuliah` VALUES ('095410163', 'MJ01', '003', 'B');

INSERT INTO `kuliah` VALUES ('095410163', 'MK01', '004', 'B');

INSERT INTO `kuliah` VALUES ('095410163', 'SO01', '001', 'B');

INSERT INTO `kuliah` VALUES ('095410163', 'SO11', '002', 'A');

INSERT INTO `kuliah` VALUES ('095410163', 'JK01', '003', 'A');

INSERT INTO `kuliah` VALUES ('095410163', 'BI01', '004', 'B');

INSERT INTO `kuliah` VALUES ('095410166', 'PW01', '001', 'B');

INSERT INTO `kuliah` VALUES ('095410166', 'PW02', '002', 'B');

INSERT INTO `kuliah` VALUES ('095410166', 'MD01', '003', 'B');

INSERT INTO `kuliah` VALUES ('095410166', 'SD01', '004', 'C');

INSERT INTO `kuliah` VALUES ('095410166', 'PW11', '001', 'C');

INSERT INTO `kuliah` VALUES ('095410166', 'PW12', '002', 'B');

INSERT INTO `kuliah` VALUES ('095410166', 'MJ01', '003', 'A');

INSERT INTO `kuliah` VALUES ('095410166', 'MK01', '004', 'A');

INSERT INTO `kuliah` VALUES ('095410166', 'SO01', '001', 'A');

INSERT INTO `kuliah` VALUES ('095410166', 'SO11', '002', 'B');

INSERT INTO `kuliah` VALUES ('095410166', 'JK01', '003', 'B');

INSERT INTO `kuliah` VALUES ('095410166', 'BI01', '004', 'B');

INSERT INTO `kuliah` VALUES ('095410183', 'PW01', '001', 'B');

INSERT INTO `kuliah` VALUES ('095410183', 'PW02', '002', 'A');

INSERT INTO `kuliah` VALUES ('095410183', 'MD01', '003', 'C');

INSERT INTO `kuliah` VALUES ('095410183', 'SD01', '004', 'B');

INSERT INTO `kuliah` VALUES ('095410183', 'PW11', '001', 'A');

INSERT INTO `kuliah` VALUES ('095410183', 'PW12', '002', 'A');

INSERT INTO `kuliah` VALUES ('095410183', 'MJ01', '003', 'B');

INSERT INTO `kuliah` VALUES ('095410183', 'MK01', '004', 'B');

INSERT INTO `kuliah` VALUES ('095410183', 'SO01', '001', 'B');

INSERT INTO `kuliah` VALUES ('095410183', 'SO11', '002', 'A');

INSERT INTO `kuliah` VALUES ('095410183', 'JK01', '003', 'C');

INSERT INTO `kuliah` VALUES ('095410183', 'BI01', '004', 'A');

INSERT INTO `kuliah` VALUES ('095410189', 'PW01', '001', 'A');

INSERT INTO `kuliah` VALUES ('095410189', 'PW02', '002', 'A');

INSERT INTO `kuliah` VALUES ('095410189', 'MD01', '003', 'A');

INSERT INTO `kuliah` VALUES ('095410189', 'SD01', '004', 'B');

INSERT INTO `kuliah` VALUES ('095410189', 'PW11', '001', 'B');

INSERT INTO `kuliah` VALUES ('095410189', 'PW12', '002', 'B');

INSERT INTO `kuliah` VALUES ('095410189', 'MJ01', '003', 'C');

INSERT INTO `kuliah` VALUES ('095410189', 'MK01', '004', 'B');

INSERT INTO `kuliah` VALUES ('095410189', 'SO01', '001', 'C');

INSERT INTO `kuliah` VALUES ('095410189', 'SO11', '002', 'B');

INSERT INTO `kuliah` VALUES ('095410189', 'JK01', '003', 'A');

INSERT INTO `kuliah` VALUES ('095410189', 'BI01', '004', 'A');

INSERT INTO `kuliah` VALUES ('095410162', 'PW01', '001', 'B');

INSERT INTO `kuliah` VALUES ('095410162', 'PW02', '002', 'B');

INSERT INTO `kuliah` VALUES ('095410162', 'MD01', '003', 'B');

INSERT INTO `kuliah` VALUES ('095410162', 'SD01', '004', 'B');

INSERT INTO `kuliah` VALUES ('095410162', 'PW11', '001', 'B');

INSERT INTO `kuliah` VALUES ('095410162', 'PW12', '002', 'C');

INSERT INTO `kuliah` VALUES ('095410162', 'MJ01', '003', 'B');

INSERT INTO `kuliah` VALUES ('095410162', 'MK01', '004', 'A');

INSERT INTO `kuliah` VALUES ('095410162', 'SO01', '001', 'A');

INSERT INTO `kuliah` VALUES ('095410162', 'SO11', '002', 'A');

INSERT INTO `kuliah` VALUES ('095410162', 'JK01', '003', 'A');

INSERT INTO `kuliah` VALUES ('095410162', 'BI01', '004', 'A');

INSERT INTO `kuliah` VALUES ('095410168', 'PW01', '001', 'B');

INSERT INTO `kuliah` VALUES ('095410168', 'PW02', '002', 'B');

INSERT INTO `kuliah` VALUES ('095410168', 'MD01', '003', 'B');

INSERT INTO `kuliah` VALUES ('095410168', 'SD01', '004', 'B');

INSERT INTO `kuliah` VALUES ('095410168', 'PW11', '001', 'B');

INSERT INTO `kuliah` VALUES ('095410168', 'PW12', '002', 'B');

INSERT INTO `kuliah` VALUES ('095410168', 'MJ01', '003', 'B');

INSERT INTO `kuliah` VALUES ('095410168', 'MK01', '004', 'B');

INSERT INTO `kuliah` VALUES ('095410168', 'SO01', '001', 'B');

INSERT INTO `kuliah` VALUES ('095410168', 'SO11', '002', 'A');

INSERT INTO `kuliah` VALUES ('095410168', 'JK01', '003', 'B');

INSERT INTO `kuliah` VALUES ('095410168', 'BI01', '004', 'A');

INSERT INTO `kuliah` VALUES ('095410163', 'PW01', '001', 'B');

INSERT INTO `kuliah` VALUES ('095410163', 'PW02', '002', 'A');

INSERT INTO `kuliah` VALUES ('095410163', 'MD01', '003', 'B');

INSERT INTO `kuliah` VALUES ('095410163', 'SD01', '004', 'B');

INSERT INTO `kuliah` VALUES ('095410163', 'PW11', '001', 'C');

INSERT INTO `kuliah` VALUES ('095410163', 'PW12', '002', 'A');

INSERT INTO `kuliah` VALUES ('095410163', 'MJ01', '003', 'A');

INSERT INTO `kuliah` VALUES ('095410163', 'MK01', '004', 'A');

INSERT INTO `kuliah` VALUES ('095410163', 'SO01', '001', 'A');

INSERT INTO `kuliah` VALUES ('095410163', 'SO11', '002', 'A');

INSERT INTO `kuliah` VALUES ('095410163', 'JK01', '003', 'A');

INSERT INTO `kuliah` VALUES ('095410163', 'BI01', '004', 'C');

INSERT INTO `kuliah` VALUES ('095410166', 'PW01', '001', 'B');

INSERT INTO `kuliah` VALUES ('095410166', 'PW02', '002', 'C');

INSERT INTO `kuliah` VALUES ('095410166', 'MD01', '003', 'B');

INSERT INTO `kuliah` VALUES ('095410166', 'SD01', '004', 'C');

INSERT INTO `kuliah` VALUES ('095410166', 'PW11', '001', 'B');

INSERT INTO `kuliah` VALUES ('095410166', 'PW12', '002', 'B');

INSERT INTO `kuliah` VALUES ('095410166', 'MJ01', '003', 'B');

INSERT INTO `kuliah` VALUES ('095410166', 'MK01', '004', 'B');

INSERT INTO `kuliah` VALUES ('095410166', 'SO01', '001', 'B');

INSERT INTO `kuliah` VALUES ('095410166', 'SO11', '002', 'A');

INSERT INTO `kuliah` VALUES ('095410166', 'JK01', '003', 'A');

INSERT INTO `kuliah` VALUES ('095410166', 'BI01', '004', 'A');

-- --------------------------------------------------------

-- Table structure for table `mahasiswa`

CREATE TABLE `mahasiswa` (

`NIM` char(9) NOT NULL default '',

`NAMAMHS` char(100) NOT NULL,

`TGLLAHIR` date NOT NULL,

`ALAMAT` char(150) NOT NULL,

`KODEJUR` char(2) default NULL,

PRIMARY KEY (`NIM`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- Dumping data for table `mahasiswa`

INSERT INTO `mahasiswa` VALUES ('095410168', 'PURWANTO', '1988-11-11', 'BANTUL,JOGJAKARTA', 'TI');

INSERT INTO `mahasiswa` VALUES ('095410163', 'CHRISTIAN PRAMUDITA', '1988-12-12', 'WESTPROG, JOGJAKARTA', 'TI');

INSERT INTO `mahasiswa` VALUES ('095410166', 'ELLEN A.P.', '1993-09-12', 'BANTUL, JOGJAKARTA', 'TI');

INSERT INTO `mahasiswa` VALUES ('095410183', 'IBNU DAMAR SAPUTRA', '1998-02-12', 'SLEMAN,JOGJAKARTA', 'TI');

INSERT INTO `mahasiswa` VALUES ('095410189', 'EKO SETIAWAN', '1991-02-01', 'BANTUL, JOGJAKARTA', 'TI');

INSERT INTO `mahasiswa` VALUES ('095410162', 'NUR WAHYUDI', '1989-02-16', 'KLATEN, JOGJAKARTA', 'TI');

-- --------------------------------------------------------

-- Table structure for table `matakuliah`

CREATE TABLE `matakuliah` (

`KODEMK` char(5) NOT NULL default '',

`NAMAMK` char(50) NOT NULL,

`SKS` int(11) NOT NULL,

`KODEJUR` char(2) default NULL,

PRIMARY KEY (`KODEMK`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- Dumping data for table `matakuliah`

INSERT INTO `matakuliah` VALUES ('PW01', 'PENGENALAN WEB', 3, 'TI');

INSERT INTO `matakuliah` VALUES ('PW02', 'PEMROGRAMAN WEB', 3, 'TI');

INSERT INTO `matakuliah` VALUES ('MD01', 'MATEMATIKA DISKRIT', 3, 'TI');

INSERT INTO `matakuliah` VALUES ('SD01', 'STATISTIKA DASAR', 3, 'TI');

INSERT INTO `matakuliah` VALUES ('PW11', 'PRAKTEK PENGENALAN WEB', 1, 'TI');

INSERT INTO `matakuliah` VALUES ('PW12', 'PRAKTEK PEMROGRAMAN WEB', 1, 'TI');

INSERT INTO `matakuliah` VALUES ('MJ01', 'MANAJEMEN DAN BISNIS', 2, 'TI');

INSERT INTO `matakuliah` VALUES ('MK01', 'METODE KUANTITATIF UNTUK BISNIS', 2, 'TI');

INSERT INTO `matakuliah` VALUES ('SO01', 'SISTEM OPERASI', 2, 'TI');

INSERT INTO `matakuliah` VALUES ('SO11', 'PRAKTEK SISTEM OPERASI', 1, 'TI');

INSERT INTO `matakuliah` VALUES ('JK01', 'JARINGAN KOMPUTER', 3, 'TI');

INSERT INTO `matakuliah` VALUES ('BI01', 'BAHASA INGGRIS 1', 3, 'TI');

SEKARANG BUAT FUNGSI UNTUK MENAMPILKAN NAMA MAHASISWA DENGAN PARAMETER INPUTAN NIM

DELIMITER $$

DROP FUNCTION IF EXISTS `akademik`.`TAMPILNAMA`$$

CREATE FUNCTION `akademik`.`TAMPILNAMA`(NOMHS CHAR(9))

RETURNS CHAR(30)

/*LANGUAGE SQL

| [NOT] DETERMINISTIC

| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }

| SQL SECURITY { DEFINER | INVOKER }

| COMMENT 'string'*/

BEGIN

DECLARE NM CHAR(30);

SELECT NAMAMHS FROM MAHASISWA WHERE NIM=NOMHS INTO NM;

RETURN NM;

END$$

DELIMITER ;

CARA MEMANGGIL FUNGSI TERSEBUT ADALAH.

mysql> SELECT TAMPILNAMA('095410168');

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

| TAMPILNAMA('095410168') |

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

| PURWANTO |

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

1 row in set (0.00 sec)

mysql> SELECT NIM,TAMPILNAMA(NIM) FROM MAHASISWA;

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

| NIM | TAMPILNAMA(NIM) |

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

| 095410162 | NUR WAHYUDI |

| 095410163 | CHRISTIAN PRAMUDITA |

| 095410166 | ELLEN A.P. |

| 095410168 | PURWANTO |

| 095410183 | IBNU DAMAR SAPUTRA |

| 095410189 | EKO SETIAWAN |

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

6 rows in set (0.05 sec)

BUAT FUNGSI UNTUK MENGHITUNG BOBOT NILAI.

DELIMITER $$

DROP FUNCTION IF EXISTS `akademik`.`HITUNGBOBOT`$$

CREATE DEFINER=`root`@`localhost` FUNCTION `HITUNGBOBOT`(NIL CHAR(1),JSKS INT) RETURNS int(11)

BEGIN

IF NIL='A' THEN

RETURN 4*JSKS;

END IF;

IF NIL='B' THEN

RETURN 3*JSKS;

END IF;

IF NIL='C' THEN

RETURN 2*JSKS;

END IF;

IF NIL='D' THEN

RETURN 1*JSKS;

END IF;

IF NIL='E' THEN

RETURN 0*JSKS;

END IF;

END$$

DELIMITER ;

CARA MEMANGGIL FUNGSI TERSEBUT :

mysql> SELECT HITUNGBOBOT('A',3);

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

| HITUNGBOBOT('A',3) |

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

| 12 |

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

1 row in set (0.05 sec)

mysql> SELECT MAHASISWA.NIM,KULIAH.NILAI,MATAKULIAH.SKS,HITUNGBOBOT(KULIAH.NILAI,MATAKULIAH.SKS)

-> FROM KULIAH,MAHASISWA,MATAKULIAH

-> WHERE MAHASISWA.NIM=KULIAH.NIM AND KULIAH.KODEMK=MATAKULIAH.KODEMK

-> ;

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

| NIM | NILAI | SKS | HITUNGBOBOT(KULIAH.NILAI,MATAKULIAH.SKS) |

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

| 095410168 | B | 3 | 9 |

| 095410168 | B | 1 | 3 |

| 095410168 | B | 2 | 6 |

| 095410168 | A | 1 | 4 |

| 095410168 | B | 3 | 9 |

| 095410168 | A | 3 | 12 |

| 095410163 | A | 3 | 12 |

| 095410163 | B | 3 | 9 |

| 095410163 | B | 1 | 3 |

| 095410163 | C | 1 | 2 |

| 095410163 | B | 2 | 6 |

BUAT FUNGSI UNTUK MENGHITUNG IP MAHASISWA

DELIMITER $$

DROP FUNCTION IF EXISTS `akademik`.`hitungip`$$

CREATE DEFINER=`root`@`localhost` FUNCTION `HITUNgip`(NOMHS CHAR(9)) RETURNS float

BEGIN

DECLARE IP FLOAT;

SELECT SUM(CASE

WHEN NILAI= 'A' THEN 4*MATAKULIAH.SKS

WHEN NILAI= 'B' THEN 3*MATAKULIAH.SKS

WHEN NILAI= 'C' THEN 2*MATAKULIAH.SKS

WHEN NILAI= 'D' THEN 1*MATAKULIAH.SKS

ELSE

0*MATAKULIAH.SKS

END)/SUM(MATAKULIAH.SKS)

AS POINT

FROM KULIAH,MATAKULIAH

WHERE KULIAH.KODEMK=MATAKULIAH.KODEMK

AND KULIAH.NIM=NOMHS

GROUP BY KULIAH.NIM INTO IP;

RETURN IP;

END$$

DELIMITER ;

MEMANGGIL FUNGSI HITUNGIP

mysql> select hitungip('095410168');

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

| hitungip('095410168') |

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

| 3.148148059845 |

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

1 row in set (0.00 sec)

mysql> select nim,namamhs,hitungip(nim) from mahasiswa;

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

| nim | namamhs | hitungip(nim) |

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

| 095410168 | PURWANTO | 3.148148059845 |

| 095410163 | CHRISTIAN PRAMUDITA | 3.351851940155 |

| 095410166 | ELLEN A.P. | 3.0555555820465 |

| 095410183 | IBNU DAMAR SAPUTRA | 3.111111164093 |

| 095410189 | EKO SETIAWAN | 3.4074075222015 |

| 095410162 | NUR WAHYUDI | 3.370370388031 |

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

6 rows in set (0.02 sec)

(NGANTUUUUUKKKKK SAYANG)

MOHON MAAF JIKA ADA KESAMMAN NAMA, BAIK NAMA DOSEN ATAU MAHASISWA.

SERTAKAN LINK JIKA INGIN MENCOPY.

SALAM ALUMNI AKAKOM 2013, BY PURWANT10168

purwant's blog

Friday, 29 August 2014

FUNCTION MYSQL, MEMBUAT FUNGSI PADA MYSQL

4 comments: