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

//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

Share this post