Basis Data Part 8: View & Store Procedure

View

Pengertian View secara umum adalah:

• View adalah virtual tabel atau tabel semu..
• View berperilaku seperti tabel pada umunya namun sebatas pada perintah SELECT.
• View tidak dapat dikenai perintah INSERT, UPDATE atau DELETE.

Tujuan View adalah untuk memudahkan pengembang dalam melakukan query, dimana query yang kompleks dapat disederhanakan menjadi View. Sebuah View dapat dibentuk dari rangkaian query tabel-tabel, query view-view atau kombinasi dari query tabel dan query view.

Pembentukan View sebagai berikut:

CREATE VIEW nama_view
AS
SELECT * FROM nama_tabel

Sebagai contoh kita akan membuat view bernama barang_vw yang merupakan gabungan dari tabel barang_tb dan jenis_barang_tb, sebagai berikut:

-- sesuaikan dengan nama database kalian
use penjualan_db;
create view barang_vw
as
SELECT barang_tb.barang_id, barang_tb.nama_barang, jenis_barang_tb.nama_jenis_barang, barang_tb.harga
FROM jenis_barang_tb INNER JOIN barang_tb ON jenis_barang_tb.jenis_barang_id = barang_tb.jenis_barang_id
ORDER BY jenis_barang_tb.nama_jenis_barang;

Eksekusi script di atas dengan perintah ALT+X

Untuk melihat hasil eksekusi menjaddi file barang_vw lakukan refresh dengan menekan tombol F5.

Perhatikan bahwa struktur view barnag_vw serupa dengan tabel.

Untuk mengoperasikan barang_vw serupa dengan query pada tabel.

use penjualan_db;
select * from barang_vw
where nama_jenis_barang like 'alat%'
order by barang_id;

Perhatikan bahwa select * yang berarti semua field/kolom akan tampil hanya sejumlah empat field sesuai yang telah ditetapkan pada barang_vw.

Sekarang kita akan coba kombinasikan antara view barang_vw dan tabel penjualan_detil_tb menjadi view bernama barang_penjualan_detil_vw. Seperti berikut ini:

use penjualan_db;
create view barang_penjualan_detil_vw
as
SELECT penjualan_detil_tb.penjualan_id, barang_vw.nama_barang, barang_vw.nama_jenis_barang, penjualan_detil_tb.jumlah, penjualan_detil_tb.harga
FROM penjualan_detil_tb INNER JOIN barang_vw ON penjualan_detil_tb.barang_id = barang_vw.barang_id
ORDER BY barang_vw.nama_jenis_barang;

Sekarang kita lakukan query yang melibatkan dua view, yaitu view barang_penjualan_detil_vw dan view barang_vw yang direlasikan berdasarkan field nama_jenis_barang, sebagai berikut:

use penjualan_db;
SELECT barang_vw.barang_id, barang_vw.nama_barang, barang_vw.nama_jenis_barang, barang_penjualan_detil_vw.jumlah, barang_penjualan_detil_vw.harga
FROM barang_penjualan_detil_vw INNER JOIN barang_vw ON barang_penjualan_detil_vw.nama_jenis_barang = barang_vw.nama_jenis_barang;

Store Procedure / Function

Jika kalian telah mengenal bahasa pemrograman komputer maka konsep store procedure/function pada SQL serupa dengan procedure atau function dalam bahasa pemrograman komputer lainnya.

Store procedure/function merupakan potongan kode program yang umumnya digunakan berkali-kali, sehingga kita cukup memanggil nama procedure beserta parameter-parameternya (bila ada). Keuntungannya adalah kita tidak perlu membuat progam berulang-ulang untuk tujuan yang sama.

Ditinjau dari sisi database, keuntungan lainnya adalah eksekusi store procedure/function berada di database (back-end) bukan di front-end. Sedangkan tugas front-end sekedar memanggil procedure, kemudian database yang mengeksekusinya. Oleh karenanya proses eksekusi menjadi lebih cepat.

Perbedaan Store procedure dan store function adalah procedure tidak menghasilkan nilai balik, sedangkan store function menghasilkan nilai balik.

Ok saya yakin kalian masih bingung. Kita akan membuatnya lebih jelas melalui praktik program. Siap?

Store procedure yang mengoperasikan SELECT, UPDATE dan DELETE dalam satu program store procedure

Pertama kita akan membuat store procedure bernama sud_barang_sp. Tugas procedure ini adalah melakukan eksekusi SELECT atau UPDATE atau DELETE dari tabel barang_tb.

Untuk membedakan eksekusi mana yang akan dilaksanakan, program harus melihat variabel parameter dipilih dalam hal ini variabel jenis_eksekusi.

use penjualan_db;
drop procedure if exists coba_sp;
delimiter $$
create procedure coba_sp (
 in jenis_ekesekusi varchar(15),
 in vbarang_id char(10),
 in vnama_barang varchar(50),
 in vjenis_barang_id char(10),
 in vharga int unsigned
)
begin
 if jenis_ekesekusi = 'insert' then
  insert into barang_tb (barang_id, nama_barang, jenis_barang_id, harga)
  values (vbarang_id, vnama_barang, vjenis_barang_id, vharga);
 
 elseif jenis_ekesekusi = 'update' then
  update barang_tb set nama_barang = vnama_barang, jenis_barang_id = vjenis_barang_id, harga=vharga
  where barang_id=vbarang_id;
 
 elseif jenis_ekesekusi = 'delete' then
  delete from barang_tb
  where barang_id = vbarang_id; 
 
 end if;
 select * from barang_vw;
 
end$$
delimiter ;

alternatif lain

use penjualan_db;
-- hapus procedure sud_barang_sp jika sudah pernah ada
drop procedure if exists sud_barang_sp;
-- penanda delimiter (wajib diisi)
DELIMITER $$
-- menciptakkan procedure sud_barang_sp 
-- beserta deklarasi variabel parameternya
create procedure sud_barang_sp (
 in jenis_eksekusi varchar(15),
 in vbarang_id char(10),
 in vnama_barang varchar(50),
 in vjenis_barang_id char(10),
 in vharga int
)
-- badan program procedure
begin  
 if jenis_eksekusi = 'insert' then      
  insert into barang_tb(barang_id, nama_barang, jenis_barang_id, harga) 
  values(vbarang_id, vnama_barang, vjenis_barang_id, vharga);
elseif jenis_eksekusi = 'update' then
  update barang_tb set nama_barang = vnama_barang, 
  jenis_barang_id = vjenis_barang_id, harga = vharga
  where barang_id = vbarang_id;
 
 elseif jenis_eksekusi = 'delete' then
  delete from barang_tb
  where barang_id = vbarang_id;
 
 elseif jenis_eksekusi = 'select' then
  select * from barang_tb
  order by nama_barang;
 
 end if; 
end$$
DELIMITER ;

Kemudian untuk mengeksekusi procedure sud_barang_sp gunakan perintah call seperti berikut:

call sud_barang_sp ('select', '', '', '', 0);

dan hasilnya sebagai berikut:

call sud_barang_sp ('insert', 'xxx', 'pisang goreng', 'J6', 1000);
select * from barang_tb;

call sud_barang_sp ('update', 'xxx', 'celana jeans bolong tengah', 'J14', 500000);
select * from barang_tb;

call sud_barang_sp ('delete', 'xxx', '', '', 0);
select * from barang_tb
where barang_id = 'xxx';

Tugas

Buatlah store procedure yang serupa dengan store procedure di atas, tetapi untuk tabel yang lain.

• Basis Data Part 1: Persiapan Lingkungan Pengembangan Database
• Basis Data Part 2: Pengantar RDBMS (Relational Database Management System)
• Basis Data Part 3: Keutuhan Data (Data Integrity) Dengan Relasi Tabel
• Basis Data Part 4: CRUD (Create, Retrieve, Update, Delete) pada Tabel dan Pengujian ER Diagram
• Basis Data Part 5: Bahasa SQL (Structured Query Language)
• Basis Data Part 6: Bahasa SQL (Structured Query Language): Like, In, Between
• Basis Data Part 7: Query Melibatkan Join Table
• Basis Data Part 8: View & Store Procedure
• Basis Data Part 9: Trigger MySQL