Perintah Prosedur dan Fungsi

mysql> delimiter //
mysql> create procedure insert_pengarang(id char(3),nama varchar(30))
    -> begin

    -> insert into tb_pengarang(pengarang_id,pengarang_nama) values(id,nama);
    -> end //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> show procedure status;
+------+------------------+-----------+----------------+---------------------+---------------------+---------------+---------
+
| Db   | Name             | Type      | Definer        | Modified            | Created             | Security_type | Comment
|
+------+------------------+-----------+----------------+---------------------+---------------------+---------------+---------
+
| dbin | insert_pengarang | PROCEDURE | root@localhost | 2014-12-04 11:07:33 | 2014-12-04 11:07:33 | DEFINER       |
|
+------+------------------+-----------+----------------+---------------------+---------------------+---------------+---------
+
1 row in set (0.00 sec)

    mengeksekusi prosedure
mysql> call insert_pengarang('pengarang_id','pengarang_nama');
Query OK, 1 row affected, 1 warning (0.04 sec)

mysql> select * from tb_pengarang;
+--------------+--------------------+
| pengarang_id | pengarang_nama     |
+--------------+--------------------+
| ASW          | Andi Setiawan      |
| BRH          | Budi Raharjo       |
| BTO          | Beni Tito          |
| DAR          | Dewi Aulia Raharjo |
| ERD          | Erik Rusdianto     |
| ERK          | E Rosdiana K       |
| FDY          | Fredi Hidayat      |
| GZL          | Abdul Gozali       |
| HND          | Hasanudin          |
| HNF          | Ahmad Hanafi       |
| IGN          | Iwan Gunadi        |
| IHR          | Imam Heriyanto     |
| pen          | pengarang_nama     |
| TTK          | Totok Triwibowo    |
+--------------+--------------------+
14 rows in set (0.01 sec)

    membuat fungsi
select buku_harga into harga from tb_bu' at line 2
mysql> delimiter //
mysql> create function cek_harga(id char(13))
    -> returns decimal(10,0)
    -> begin
    -> declare harga decimal(10,0);
    -> select buku_harga into harga from tb_buku where buku_id=id;
    -> return harga;
    -> end //
Query OK, 0 rows affected (0.02 sec)

mysql> select cek_harga('222-34222-1-0');
+----------------------------+
| cek_harga('222-34222-1-0') |
+----------------------------+
|                      42000 |
+----------------------------+
1 row in set (0.00 sec)

mysql> select cek_harga('222-34222-1-0') as 'HARGA BUKU';
+------------+
| HARGA BUKU |
+------------+
|      42000 |
+------------+
1 row in set (0.00 sec)

mysql> delimiter ;
1.Buatlah sebuah prosedur untuk menyimpan data penerbit.
mysql> delimiter //
mysql> create procedure insert_penerbit(id char(3),nama varchar(30))
    -> begin
    -> insert into tb_penerbit(penerbit_id,penerbit_nama) values(id,nama);
    -> end //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call insert_penerbit('penerbit_id','penerbit_nama');
Query OK, 1 row affected, 1 warning (0.02 sec)

mysql> select * from tb_penerbit;
+-------------+--------------------------+
| penerbit_id | penerbit_nama            |
+-------------+--------------------------+
| PB01        | Angkasa Raya             |
| PB02        | Cahaya Ilmu Persada      |
| PB03        | Sinar Intan Ilmu Perkasa |
| PB04        | Intan                    |
| PB05        | Sinar Jaya               |
| PB06        | Informatika              |
| PB07        | Tiga Sekawan             |
| PB08        | Cipta Ilmu               |
| PB09        | Menara Kudus             |
| PB10        | Muria Kudus              |
| pen         | penerbit_nama            |
+-------------+--------------------------+
11 rows in set (0.00 sec)

2. Buatlah sebuah fungsi untuk menampilkan nama_penerbit ketika kita memasukkan judul_buku.
mysql> delimiter //
mysql> create function cek_penerbit(judul char(50))
    -> returns varchar(50)
    -> begin
    -> declare nama varchar(50);
    -> select penerbit_nama into nama from tb_buku inner join tb_penerbit on tb_buku.penerbit_id=tb_penerbit.penerbit_id
    -> where buku_judul=judul;
    -> return nama;
    -> end //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> select cek_penerbit('menguasai SQL');
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    1
Current database: dbin

+-------------------------------+
| cek_penerbit('menguasai SQL') |
+-------------------------------+
| Informatika                   |
+-------------------------------+
1 row in set (0.03 sec)

3. Tampilkan judul_buku,nama_penerbit yang memiliki jumlahhalaman_buku sama dengan 250 atau penerbitnya 'INTAN'.