Selasa, 12 April 2016
Selasa, 13 Januari 2015
Praktikum - Bagian 5 : Parameter, Procedure & Record
Mula - mula kita buat tabel pekerja2 seperti dibawah.
mysql> create table pekerja2(nama_depan varchar(20),nama_belakang varch
Query OK, 0 rows affected (0.06 sec)
Isi data tabel menggunakan notepad seperti data dibawah
mysql> load data infile 'D://PRAKTIKUM SMBD/KM2_131051056/data2.txt' in
Query OK, 9 rows affected (0.02 sec)
Records: 9 Deleted: 0 Skipped: 0 Warnings: 0
mysql> select*from pekerja2;
+------------+---------------+--------+---------+-------------+
| nama_depan | nama_belakang | alamat | gaji | pekerjaan |
+------------+---------------+--------+---------+-------------+
|Jhon | Doe | Sleman | 500000 | Programmer
|Alison | Mathews | Sleman | 500000 | Programmer
|es | Smith | Bantul | 1000000 | Manager
|ia | Rice | Klaten | 1000000 | Manager
|bert | Black | Klaten | 200000 | Security
|nda | Green | Bantul | 200000 | Security
|David | Larry | Bantul | 500000 | Programmer
|Hercule | Pairot | Klaten | 500000 | Programmer
|Lincoln | Rhyme | Klaten | 500000 | Programmer
+------------+---------------+--------+---------+-------------+
9 rows in set (0.00 sec)
mysql> insert into pekerja2 values('Sherlock','Holmes','Sleman','100000
Query OK, 1 row affected (0.01 sec)
mysql> select*from pekerja2;
+------------+---------------+--------+---------+-------------+
| nama_depan | nama_belakang | alamat | gaji | pekerjaan |
+------------+---------------+--------+---------+-------------+
|Jhon | Doe | Sleman | 500000 | Programmer
|Alison | Mathews | Sleman | 500000 | Programmer
|es | Smith | Bantul | 1000000 | Manager
|ia | Rice | Klaten | 1000000 | Manager
|bert | Black | Klaten | 200000 | Security
|nda | Green | Bantul | 200000 | Security
|David | Larry | Bantul | 500000 | Programmer
|Hercule | Pairot | Klaten | 500000 | Programmer
|Lincoln | Rhyme | Klaten | 500000 | Programmer
| Sherlock | Holmes | Sleman | 1000000 | Manager |
+------------+---------------+--------+---------+-------------+
10 rows in set (0.00 sec)
mysql> delimiter //
mysql> create function nama_lengkap(in_nama_depan varchar(15), in_nama_
-> returns varchar(35)
-> begin
-> return concat(in_nama_depan,' ',in_nama_belakang);
-> end //
Query OK, 0 rows affected (0.05 sec)
mysql> delimiter ;
mysql> select nama_lengkap(nama_depan,nama_belakang) from pekerja2;
+----------------------------------------+
| nama_lengkap(nama_depan,nama_belakang) |
+----------------------------------------+
| Jhon Doe |
| Alison Mathews |
| James Smith |
| Celia Rice |
| Robert Black |
mysql> delimiter //
mysql> create procedure show_pekerja()
-> begin
-> select*from pekerja2;
-> end //
Query OK, 0 rows affected (0.00 sec)
mysql> call show_pekerja();
-> //
+------------+---------------+--------+---------+-------------+
| nama_depan | nama_belakang | alamat | gaji | pekerjaan |
+------------+---------------+--------+---------+-------------+
|Jhon | Doe | Sleman | 500000 | Programmer |
|Alison | Mathews | Sleman | 500000 | Programmer |
|es | Smith | Bantul | 1000000 | Manager |
|ia | Rice | Klaten | 1000000 | Manager |
|bert | Black | Klaten | 200000 | Security |
|nda | Green | Bantul | 200000 | Security |
|David | Larry | Bantul | 500000 | Programmer |
|Hercule | Pairot | Klaten | 500000 | Programmer |
|Lincoln | Rhyme | Klaten | 500000 | Programmer |
| Sherlock | Holmes | Sleman | 1000000 | Manager |
+------------+---------------+--------+---------+-------------+
10 rows in set (0.00 sec)
Parameter IN
mysql> delimiter $$
mysql> create procedure getPekerjaByAlamat2 (in namaAlamat varchar(255))
-> begin
-> select*from pekerja2 where alamat like namaAlamat;
-> end $$
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> call getPekerjaByAlamat2("Sleman");
+------------+---------------+--------+---------+-------------+
| nama_depan | nama_belakang | alamat | gaji | pekerjaan |
+------------+---------------+--------+---------+-------------+
|Jhon | Doe | Sleman | 500000 | Programmer
|Alison | Mathews | Sleman | 500000 | Programmer
| Sherlock | Holmes | Sleman | 1000000 | Manager |
+------------+---------------+--------+---------+-------------+
3 rows in set (0.03 sec)
Query OK, 0 rows affected (0.03 sec)
Parameter OUT
mysql> delimiter :)
mysql> create procedure getNumPekerja2 (out numPekerja int)
-> begin
-> select count(*) into numPekerja from pekerja2;
-> end :)
Query OK, 0 rows affected (0.00 sec)
mysql> select @num;
+------+
| @num |
+------+
| 10 |
+------+
1 row in set (0.00 sec)
Parameter INOUT
mysql> delimiter ^^
mysql> create procedure increase(inout number int)
-> begin
-> set number=number+15;
-> end ^^
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> delimiter ;
mysql> set @num = 100;
Query OK, 0 rows affected (0.02 sec)
mysql> call increase(@num);
Query OK, 0 rows affected (0.00 sec)
mysql> select @num;
+------+
| @num |
+------+
| 115 |
+------+
1 row in set (0.00 sec)
INPUT dengan Procedure
mysql> show procedure status;
+--------------+-----------------------+-----------+----------------+---------------------+---------------------+---------------+---------+
| Db | Name | Type | Definer | Modified | Created | Security_type | Comment |
+--------------+-----------------------+-----------+----------------+---------------------+---------------------+---------------+---------+
| km1 | checkScope | PROCEDURE | root@localhost | 2013-11-21 21:15:36 | 2013-11-21 21:15:36 | DEFINER | |
| km1 | getNumPekerja | PROCEDURE | root@localhost | 2013-11-21 20:14:34 | 2013-11-21 20:14:34 | DEFINER | |
| km1 | getPekerjaByAlamat | PROCEDURE | root@localhost | 2013-11-21 19:56:59 | 2013-11-21 19:56:59 | DEFINER | |
| km1 | hapus_data | PROCEDURE | root@localhost | 2013-11-21 20:33:22 | 2013-11-21 20:33:22 | DEFINER | |
| km1 | increase | PROCEDURE | root@localhost | 2013-11-21 20:22:52 | 2013-11-21 20:22:52 | DEFINER | |
| km1 | isi | PROCEDURE | root@localhost | 2013-11-14 21:41:16 | 2013-11-14 21:41:16 | DEFINER | |
| km1 | isidata | PROCEDURE | root@localhost | 2013-11-14 21:47:59 | 2013-11-14 21:47:59 | DEFINER | |
| km1 | iterateDemo | PROCEDURE | root@localhost | 2013-11-28 20:31:35 | 2013-11-28 20:31:35 | DEFINER | |
| km1 | mod12 | PROCEDURE | root@localhost | 2013-11-28 20:17:35 | 2013-11-28 20:17:35 | DEFINER | |
| km1 | repeatDemo | PROCEDURE | root@localhost | 2013-11-28 20:27:06 | 2013-11-28 20:27:06 | DEFINER | |
| km1 | show_pekerja | PROCEDURE | root@localhost | 2013-11-14 21:35:39 | 2013-11-14 21:35:39 | DEFINER | |
| km2 | getNumPekerja2 | PROCEDURE | root@localhost | 2014-11-20 11:38:01 | 2014-11-20 11:38:01 | DEFINER | |
| km2 | getPekerjaByAlamat2 | PROCEDURE | root@localhost | 2014-11-20 11:33:46 | 2014-11-20 11:33:46 | DEFINER | |
| km2 | increase | PROCEDURE | root@localhost | 2014-11-20 11:49:14 | 2014-11-20 11:49:14 | DEFINER | |
| km2 | show_pekerja | PROCEDURE | root@localhost | 2014-11-20 11:18:08 | 2014-11-20 11:18:08 | DEFINER | |
| pra_sl3 | getnumpekerja | PROCEDURE | root@localhost | 2013-12-04 02:52:50 | 2013-12-04 02:52:50 | DEFINER | |
| rb4 | getnumpekerja | PROCEDURE | root@localhost | 2014-11-19 16:49:31 | 2014-11-19 16:49:31 | DEFINER | |
| rb4 | getpekerjaBYalamat | PROCEDURE | root@localhost | 2014-11-19 16:42:00 | 2014-11-19 16:42:00 | DEFINER | |
| rb4 | isi_data | PROCEDURE | root@localhost | 2014-11-19 17:06:51 | 2014-11-19 17:06:51 | DEFINER | |
| rb4 | show_pekerja | PROCEDURE | root@localhost | 2014-11-19 16:31:22 | 2014-11-19 16:31:22 | DEFINER | |
| responsi_sl3 | tampil | PROCEDURE | root@localhost | 2013-12-11 02:18:41 | 2013-12-11 02:18:41 | DEFINER | |
| responsi_sn2 | alamat | PROCEDURE | root@localhost | 2013-12-09 23:21:31 | 2013-12-09 23:21:31 | DEFINER | |
| sl3 | checkScope | PROCEDURE | root@localhost | 2013-11-27 01:09:28 | 2013-11-27 01:09:28 | DEFINER | |
| sl3 | checkScope2 | PROCEDURE | root@localhost | 2013-11-27 01:21:32 | 2013-11-27 01:21:32 | DEFINER | |
| sl3 | chekscope | PROCEDURE | root@localhost | 2013-11-27 01:14:15 | 2013-11-27 01:14:15 | DEFINER | |
| sl3 | getnumpekerja | PROCEDURE | root@localhost | 2013-11-20 01:44:13 | 2013-11-20 01:44:13 | DEFINER | |
| sl3 | getpekerjabyalamat | PROCEDURE | root@localhost | 2013-11-20 01:34:06 | 2013-11-20 01:34:06 | DEFINER | |
| sl3 | hapus_data | PROCEDURE | root@localhost | 2013-11-20 02:40:48 | 2013-11-20 02:40:48 | DEFINER | |
| sl3 | increase | PROCEDURE | root@localhost | 2013-11-20 01:59:25 | 2013-11-20 01:59:25 | DEFINER | |
| sl3 | isi_data | PROCEDURE | root@localhost | 2013-11-20 02:13:55 | 2013-11-20 02:13:55 | DEFINER | |
| sl3 | iterateDemo | PROCEDURE | root@localhost | 2013-11-27 01:50:51 | 2013-11-27 01:50:51 | DEFINER | |
| sl3 | mod12 | PROCEDURE | root@localhost | 2013-11-27 01:44:58 | 2013-11-27 01:44:58 | DEFINER | |
| sl3 | panggil | PROCEDURE | root@localhost | 2013-11-27 00:46:25 | 2013-11-27 00:46:25 | DEFINER | |
| sl3 | repeatDemo | PROCEDURE | root@localhost | 2013-11-27 01:48:19 | 2013-11-27 01:48:19 | DEFINER | |
| sl3 | show_pekerja2 | PROCEDURE | root@localhost | 2013-11-20 01:22:54 | 2013-11-20 01:22:54 | DEFINER | |
| sl4 | CheckScope | PROCEDURE | root@localhost | 2013-11-27 03:46:56 | 2013-11-27 03:46:56 | DEFINER | |
| sl4 | checkScope2 | PROCEDURE | root@localhost | 2013-11-27 03:57:57 | 2013-11-27 03:57:57 | DEFINER | |
| sl4 | getPekerjaByAlamat | PROCEDURE | root@localhost | 2013-11-13 04:45:07 | 2013-11-13 04:45:07 | DEFINER | |
| sl4 | increase | PROCEDURE | root@localhost | 2013-11-13 05:04:48 | 2013-11-13 05:04:48 | DEFINER | |
| sl4 | show_pekerja | PROCEDURE | root@localhost | 2013-11-13 04:35:10 | 2013-11-13 04:35:10 | DEFINER | |
| sn2 | checkshop | PROCEDURE | root@localhost | 2013-11-25 22:37:02 | 2013-11-25 22:37:02 | DEFINER | |
| sn2 | hapus | PROCEDURE | root@localhost | 2013-11-19 00:10:43 | 2013-11-19 00:10:43 | DEFINER | |
| sn2 | increase | PROCEDURE | root@localhost | 2013-11-18 23:16:40 | 2013-11-18 23:16:40 | DEFINER | |
| sn2 | isi | PROCEDURE | root@localhost | 2013-11-18 23:50:54 | 2013-11-18 23:50:54 | DEFINER | |
| sn2 | iterateDemo | PROCEDURE | root@localhost | 2013-11-25 23:48:06 | 2013-11-25 23:48:06 | DEFINER | |
| sn2 | modl2 | PROCEDURE | root@localhost | 2013-11-25 23:25:28 | 2013-11-25 23:25:28 | DEFINER | |
| sn2 | numpekerja | PROCEDURE | root@localhost | 2013-11-18 23:08:17 | 2013-11-18 23:08:17 | DEFINER | |
| sn2 | show_pekerja | PROCEDURE | root@localhost | 2013-11-18 23:39:52 | 2013-11-18 23:39:52 | DEFINER | |
| sn2 | tampilalamat | PROCEDURE | root@localhost | 2013-11-18 22:57:53 | 2013-11-18 22:57:53 | DEFINER | |
| sn4 | getmhsByalamat | PROCEDURE | root@localhost | 2013-12-10 04:27:41 | 2013-12-10 04:27:41 | DEFINER | |
| sn4 | getnumpekerja | PROCEDURE | root@localhost | 2013-11-12 05:04:07 | 2013-11-12 05:04:07 | DEFINER | |
| sn4 | getpekerjaByAlamat | PROCEDURE | root@localhost | 2013-11-12 04:52:45 | 2013-11-12 04:52:45 | DEFINER | |
| sn4 | getpekerjaBynamadepan | PROCEDURE | root@localhost | 2013-11-12 04:54:55 | 2013-11-12 04:54:55 | DEFINER | |
| sn4 | increase | PROCEDURE | root@localhost | 2013-11-12 05:10:29 | 2013-11-12 05:10:29 | DEFINER | |
| sn4 | show_pekerja | PROCEDURE | root@localhost | 2013-11-12 04:44:35 | 2013-11-12 04:44:35 | DEFINER | |
+--------------+-----------------------+-----------+----------------+---------------------+---------------------+---------------+---------+
55 rows in set (0.00 sec)
mysql> show tables;
+----------------+
| Tables_in_km2 |
+----------------+
| jabatan |
| job |
| pegawai1 |
| pegawai3 |
| pegawai_join |
| pekerja1 |
| pekerja2 |
| t_buku |
| t_car1 |
| t_mhs1 |
| t_parkir1 |
| t_parkir1_copy |
| t_pegawai1 |
| t_stok1 |
| t_user1 |
| v_1 |
| v_2 |
| v_3 |
| v_4 |
| v_5 |
| v_merge |
| v_merge2 |
| v_temptable |
| view_1 |
| view_2 |
| view_4 |
+----------------+
26 rows in set (0.00 sec)
mysql> create table pekerja3(nama varchar(10), alamat varchar(10));
Query OK, 0 rows affected (0.08 sec)
mysql> delimiter #
mysql> create procedure isi_data( in nama varchar(10), alamat varchar(10))
-> begin
-> insert into pekerja3 values(nama,alamat);
-> end #
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql>
HAPUS data dengan Record
mysql> delimiter #
mysql> create procedure hapus_data(in namaDepan varchar(10))
-> begin
-> delete from pekerja3 where nama=namaDepan;
-> end #
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> call hapus_data("Chika");
Query OK, 1 row affected (0.03 sec)
mysql> select*from pekerja3;
+-------+--------+
| nama | alamat |
+-------+--------+
| Rifki | Jogja |
+-------+--------+
1 row in set (0.00 sec)
Langganan:
Postingan (Atom)