Membuat Trigger,View,Join,Function dan Procedure di Oracle

Asen Hamzah/ June 16, 2017/ Tips dan Trik/ 0 comments


//ini untuk membuat database di oracle
create tablespace jual_cileungsi datafile ‘jual_cileungsi’ size 10M autoextend on;

//untuk membuat user ke database yang telah dibuat
create user asen identified by orcl default tablespace jual_cileungsi; //set user ke
alter user asen default tablespace jual_cileungsi; //set tablespace jika user sudah ada ke

//memberikan akses kepada username tersebut
grant connect to asen;
grant dba to asen;
grant resource to asen;
grant create session to asen;
//buat session

//hapus user
DROP USER xxx CASCADE;

//membuat tabel
create table jenis(
idJenis varchar(3) primary key,
Jenis varchar(30)
);

create table barang(
idBarang varchar(3) primary key,
namabarang varchar(20),
idJenis varchar(3) references jenis(idJenis),
harga number(7),
stok number(1));

Penjelasan Number

oracle tdk menggunakan int, double. jadi menggunakan “number”
number(2) –> number 2 digit –> 45
number(*, 2) –> 2 digit belakang koma –> 123.45
number(2, 2) –> 2 dgt number, 2 digit belakang koma –> 23.45
number(*,-2) –> pembulatan pada koma –> 23

Alter Table

//ubah 1 kolom
alter table barang modify stok number(4);

//ubah banyak kolom
alter table barang modify (stok number(4), nama varchar(30));

//ubah nama kolom
alter table barang rename column nama to namaBarang;
alter table barang modify namabarang varchar(50)

//memasukkan 1 data
insert into jenis (idJenis, Jenis) values (‘J01’, ‘Makanan’);
insert into jenis values (‘J02’, ‘Minuman’);

//memasukan banyak data
insert all
into jenis values (‘J03’, ‘Obat’)
into jenis values (‘J04’, ‘Per. Mandi’)
into jenis values (‘J05’, ‘Per. Dapur’)
into jenis values (‘J06’, ‘Mainan’)
select * from dual;

//menampilkan data
select * from jenis;
select count(*) as jmlJenis from jenis;
select * from jenis where jenis = ‘Makanan’
select * from jenis where jenis in (‘Makanan’, ‘Obat’, ‘Per. Mandi’)
select * from jenis where jenis not in (‘Makanan’, ‘Obat’, ‘Per. Mandi’)

//like case sensitif
select * from jenis where jenis like ‘M%’

//melihat struktur table
desc barang

insert all
into barang values(‘B01’, ‘Oreo Ice Cream’, ‘J01’, 12400, 24)
into barang values(‘B02’, ‘Sharp Blazter Blender SB-TW101P’, ‘J05’, 325000, 35)
select * from dual;

insert all
into barang values(‘B03’, ‘Richeese Cheese’, ‘J01’, 2200, 63)
into barang values(‘B04’, ‘Kacang DK’, ‘J01’, 15700, 41)
into barang values(‘B05’, ‘Walls Buavita Kiwi’, ‘J01’, 6200, 72)
into barang values(‘B06’, ‘Walls Buavita Mangga’, ‘J01’, 6200, 42)
into barang values(‘B07’, ‘Trisonic MX-T2GN Blender 3 in 1’, ‘J05’, 97300, 23)
into barang values(‘B08’, ‘Pilus DK’, ‘J01’, 2800, 52)
into barang values(‘B09’, ‘Ovaltine CC Selai Kaleng 380gr’, ‘J01’, 65000, 120)
into barang values(‘B10’, ‘Elmer Chocomaltine Selai Coklat’, ‘J01’, 74300, 14)
into barang values(‘B11’, ‘Miyako PSG-607 Multi Cooker’, ‘J05’, 137000, 51)
into barang values(‘B12’, ‘Nutella Hazelnut Spread’, ‘J01’, 39981, 18)
into barang values(‘B13’, ‘Choconola Black Chia Seeds 500g’, ‘J01’, 115000, 19)
into barang values(‘B14’, ‘Torabika Moka Bag – 28gr’, ‘J02’, 22785, 16)
into barang values(‘B15’, ‘Teh Pucuk Harum Melati – 350ml’, ‘J02’, 48400, 35)
into barang values(‘B16’, ‘3 Box Susu Kambing Sky Goat’, ‘J02’, 64000, 11)
select * from dual;

insert all
into barang values(‘B17’, ‘Thai mixed coffee number one’, ‘J02’, 79000, 24)
into barang values(‘B18’, ‘Miyako HM-620 Hand Mixer’, ‘J05’, 134500, 24)
into barang values(‘B19’, ‘Rak sudut’, ‘J04’, 39450, 71)
into barang values(‘B20’, ‘Rak Dinding Tempel’, ‘J04’, 94000, 28)
into barang values(‘B21’, ‘Boneka Jari 10pcs’, ‘J06’, 44400, 24)
into barang values(‘B22’, ‘Uno Card’, ‘J06’, 16400, 81)
into barang values(‘B23’, ‘Aqua Doodle Drawing Toys’, ‘J06’, 78600, 19)
into barang values(‘B24’, ‘Bola Mandi’, ‘J06’, 17400, 54)
into barang values(‘B25’, ‘Obat Kulit Anjing Dermakomb’, ‘J03’, 25000, 25)
into barang values(‘B26’, ‘Acnol Acne’, ‘J03’, 58000, 61)
into barang values(‘B27’, ‘Azanis Scarserum’, ‘J03’, 320000, 3)
into barang values(‘B28’, ‘Oreo Ice Cream’, ‘J03’, 12400, 12)
into barang values(‘B29’, ‘Po Wong To Acne’, ‘J03’, 145000, 9)
into barang values(‘B30’, ‘Hendel Forex’, ‘J03’, 95000, 15)
select * from dual;

//———- JOIN ———-//

//join standard
select * from barang
select idbarang, namabarang, harga * stok as nilai_barang from barang;

//menggunakan inner join
select jenis.jenis, barang.namabarang, barang.harga
from jenis inner join barang
on jenis.idjenis = barang.idjenis
order by jenis.idjenis

//tampikan jumlah barang per masing-masing jenis
contoh:
—————–
jenis   jumlah
—————–
makanan   34
minuman   10

Jawaban
select barang.idjenis, jenis.jenis, COUNT(barang.namabarang)
from barang inner join jenis
on jenis.idjenis = barang.idjenis
group by barang.idjenis, jenis.jenis

//——FUNCTION

//1.function tanpa parameter

CREATE OR REPLACE FUNCTION hallo
RETURN VARCHAR2
IS
  vHallo VARCHAR(10);
BEGIN
  SELECT ‘halo’ INTO vHallo
  FROM dual;

  RETURN vHallo;
END hallo;

SELECT hallo FROM dual;

//2.function dengan parameter IN
CREATE OR REPLACE FUNCTION tambah(ang1 IN NUMBER, ang2 IN NUMBER)
RETURN NUMBER
IS
   valTambah VARCHAR(10);
BEGIN
   SELECT ang1 + ang2 INTO valTambah
   FROM dual;
 
   RETURN valTambah;
END tambah;

SELECT tambah(3,4) FROM dual;

//3.Hapus function 
DROP FUNCTION tambah;

Latihan

//Buatlah function untuk menampilkan stok barang berdasarkan parameret idbarang
CREATE OR REPLACE FUNCTION getStok(id IN VARCHAR2)
RETURN varchar2
IS
   valStok varchar2(3);
BEGIN
   select stok into valStok
   from barang
   where idbarang = id;
 
   return valStok;
end getStok;

//cara mengeksekusi function
select getStok(‘B07’) from dual;

==========MEMBUAT VIEW=============

create or replace view vSJenis as
select jenis.jenis, supplier.namasupplier as supplier, barang.idbarang
from barang
inner join jenis on barang.idjenis = jenis.idjenis
inner join supplier on barang.idsupplier = supplier.idsupplier

Leave a Comment

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>
*
*