laporan praktikum 4_santi dewi putri sutikno_11120037

LAPORAN PRAKTIKUM IV

PROCEDURE dan FUNCTION

BASIS DATA LANJUT

Oleh :

Santi Dewi Putri .S. 11120037

Siti Nuraini Triastuti 11120041

Dewi Rahayuningsih 11120058

Fakultas Teknik

Program Studi Teknik Informatika

Universitas Wijaya Kusuma Surabaya

2014

TUJUAN

1. Mahasiswa mengerti tentang penggunan Procedure dan Function.

2. Mahasiswa memahami pengguanan Procedure dan Function.

3. Mahasiswa mampu menerapkan penggunaan Procedure dan Function.

PENDAHULUAN

STORED PROCEDURES

Stored Procedures merupakan modul dari PL/SQL yang memiliki kemampuan

menerima parameter input, melakukan pengolahan terhadap parameter input

tersebut lalu menampilkannya. Stored Procedures dapat memiliki parameter input

ataupun tidak memiliki parameter. Parameter pada stored procedures dapat berupa

input, output maupun gabungan input dan output. Seperti halnya blok non-

modular PL/SQL, stored procedures mampu menangani pesan kesalahan yaitu

dengan Exception Handling. Stored Procedures dapat berdiri sendiri , digunakan

bersama dengan stored procedures lainnya maupun digunakan dengan Stored

Functions.

Pada exercise workbook ini mahasiswa akan mempelajari dan melakukan

praktikum untuk membuat, menghapus, merubah stored procedures baik dengan

menggunakan parameter maupun tanpa menggunakan parameter. Parameter pada

stored procedure ada 3 yaitu :

- IN, digunakan untuk menerima dan menyimpan nilai inputan dari luar

stored procedures

- OUT, digunakan untuk menyimpan nilai hasil proses dari stored

procedures yang akan ditampilkan

- INOUT, dapat berfungsi sebagai IN dan OUT

Stored Procedures sangat berguna ketika diterapkan pada aplikasi karena

memudahkan pembuat program untuk melakukan perbaikan ketika ditemukan

suatu kesalahan pada program yang dibuatnya.

Aturan Penulisan Stored Procedures

- Kalimat CREATE PROCEDURE bertujuan untuk membuat sebuah stored

procedures pada PL/SQL.

- [OR REPLACE] digunakan jika pembuat program akan melakukan perubahan

pada stored procedure yang telah dibuat

- Procedure_name merupakan nama dari stored procedure yang dibuat

- parameter merupakan nama parameter dari stored procedure

- [mode] merupakan jenis dari parameter apakah IN, OUT atau INOUT

- Datatype merupakan tipe data, dapat berupa tipe data skalar maupun tipe data

reference

- IS | AS merupakan tanda pemisah antara header stored procedure dan isi dari

stored procedure, gunakan salah satu saja

- [local_variable_declarations;] berisikan variabel-variabel lokal dari stored

procedure sebagaimana variable-variabel pada blok non-modular PL/SQL

- BEGIN merupakan tanda awal dari isi proses PL/SQL

- --actions; berisikan baris-baris koding PL/SQL dapat berupa runtunan, pemilihan,

perulangan maupun perintah DML.

- END [procedure_name]; merupakan tanda akhir dari struktur stored procedures

yang di buat, dapat juga ditambahkan pada baris selanjutnya tanda garis miring

”/”.

Fungsi Stored Procedure

Keuntungan menggunakan stored Procedure (menyimpan kode program di

database) dibandingkan dengan menyimpan kode program di aplikasi adalah :

1. Pemrograman menjadi lebih modular

2. Mengurangi lalu lintas jaringan

1. Dapat digunakan untuk mekanisme keamanan basis data

STORED FUNCTIONS

Stored Functions merupakan modul dari PL/SQL yang dapat mempermudah

melakukan pengolahan suatu nilai dan mengembalikan nilai tertentu. Konsep dari

Stored Functions mirip dengan konsep Stored Procedures, tetapi stored functions

harus mengembalikan suatu nilai.

Aturan Penulisan Stored Functions

- Kalimat CREATE FUNCTION bertujuan untuk membuat sebuah stored

function pada PL/SQL.

- [OR REPLACE] digunakan jika pembuat program akan melakukan

perubahan pada stored function yang telah dibuat

- Procedure_name merupakan nama dari stored procedure yang dibuat

- parameter merupakan nama parameter dari stored function

- [mode] merupakan jenis dari parameter apakah IN, OUT atau INOUT

- Datatype merupakan tipe data, dapat berupa tipe data skalar maupun tipe

data reference

- RETURN datatype berfungsi sebagai deklarasi untuk mengembalikan tipe

data tertentu. Tipe data disini harus tanpa ada keterangan panjang

karakter/digit angka.

- IS | AS merupakan tanda pemisah antara header stored function dan isi

dari stored function, gunakan salah satu saja

- [local_variable_declarations;] berisikan variabel-variabel lokal dari stored

function sebagaimana variable-variabel pada blok non-modular PL/SQL

- BEGIN merupakan tanda awal dari isi proses PL/SQL

- --actions; berisikan baris-baris koding PL/SQL dapat berupa runtunan,

pemilihan, perulangan maupun perintah DML.

- RETURN expression mengembalikan nilai suatu variabel tertentu yang

akan ditampilkan

- END [function_name]; merupakan tanda akhir dari struktur stored

function yang di buat, dapat juga ditambahkan pada baris selanjutnya

tanda garis miring ”/”.

PERCOBAAN

Pembuatan Function dan procedure pada Oracle dan SQL

ORACLE

Soal :

1.Buat store procedure untuk mencari nama mahasiswa pada prodi tertentu,

dengan input statis dari kode prodi yang terdapat di dalam procedure.

2.Buat store procedure untuk mencari nama mahasiswa pada prodi tertentu,

dengan input dinamis dari kode prodi yang diinputkan ketika menjalankan

procedure tersebut.

Penyelesaian :

1. Buat table dengan nama prodi dengan query :

Create table prodi(kode_prodi varchar(10) not null constrait kode_prodi primary

key, nama_prodi varchar(20),jurusan varchar(20));

Select*from prodi

Hasil yang ditampilkan :

Selanjutnya buat tabel dengan nama mahasiswa1 dengan query :

create table mahasiswa1 (nim1 int constraint nim1 primary key not null, nama

varchar(20), alamat varchar(25), kode_prodi varchar(10) references

prodi(kode_prodi));

Select*from mahasiswa1


Menampilkan nama dari tabel mahasiswa1 yang memiliki kode_prodi=P01


Buat procedure untuk mendapatkan data mahasiswa pada prodi P01(P01 didalam

bagian code procedure). Dengan query :

Create or replace procedure cari_nama1 (kode_prodi1 in mahasiswa1.kode_prodi% TYPE) is vnama mahasiswa1.nama%type;

BEGIN

Select mahasiswa1.nama into vnama

from mahasiswa1

where mahasiswa1.kode_prodi=kode_prodi1;

dbms_output.put_line(‘kode_prodi=’||kode_prodi1);

dbms_output.put_line(‘nama=’||vnama);

end;

execute cari_nama1(‘P01’);

set serveroutput on;

Hasil yang ditampilkan : nama dari mahasiswa dengan kode prodi P01

2. Buat procedure untuk mendapatkan data Nama dan alamat mahasiswa

pada prodi P01(P01 diinputkan ketika memanggil procedure).dengan

query :

Create or replace procedure cari_alamat

(kode_prodi1 in mahasiswa1.kode_prodi% TYPE) is

Vnama mahasiswa1.alamat%type;

BEGIN

Select mahasiswa1.nama, mahasiswa1.alamat into vnama,valamat

From mahasiswa1

Where mahasiswa1.kode_prodi=kode_prodi1;

Dbms_oputput.put_line(‘kode_prodi=’||kode_prodi1);

Dbms_oputput.put_line(‘nama=’|| vnama);

Dbms_oputput.put_line(‘alamat=’||valamat);

End;

Set serveroutput on;

Execute cari_alamat(‘P01’);

Hasil yang di tampilkan : nama dan alamat dari mahasiswa dengan kode prodi

P01

TUGAS TAMBAHAN

Soal:

1.Buat 4 Function dan 4 Procedure pada SQL dan ORACLE di Project Rental

Mobil

ORACLE

1. Buat Procedure Function untuk menambahkan datacustomer dengan nama

sp_insert_customer.Dengan id=246,nama=eny, alamat=jln jambangan

palm spring, no ktp=6745280099121100, no tlp=081217332599 dan id

jaminan=525. Dengan query :

Create or replace prcedure sp_insert_customer

(vid_customer in datacustomer.id_customer%TYPE,

vnama_customer in datacustomer.nama_customer%TYPE,

valamat_customer in datacustomer.alamat_customer%TYPE,

vno_ktp in datacustomer.no_ktp%TYPE,

vno_tlp in datacustomer.no_tlp%TYPE,

vid_jaminan in datacustomer.id_jaminan%TYPE)

as

begin

insert into datacustomer (id_customer,nama_customer,alamat_customer,

no_ktp,no_tlp,id_jaminan)

values(vid_customer,vnama_customer,valamat_customer,vno_ktp,vno_tlp,

vid_jaminan);

end;

execute sp_insert_customer(246,’eny’,’jln jambangan palm spring’,’6745280099121100’,’081217332599’,’525’);

select* from datacustomer


2. Buat Procedure Function untuk menambahkan datapegawai nama

sp_insert_datapegawai.Dengan id_pegawai=1138, nama_pegawai= riyadi,

alamat_pegawai= jln karah agung no 18, no_ktp=2231664578909700,

no_tlp=085232314333, jenis_kelamin=supir.Dengan query :

Create or replace procedure sp_insert_datapegawai

(vid_pegawai in datapegawai.id_pegawai%TYPE,

vnama_pegawai in datapegawai.nama_pegawai%TYPE,

valamat_pegawai in datapegawai.alamat_pegawai%TYPE,

vno_ktp in datapegawai.no_ktp%TYPE,

vno_tlp in datapegawai.no_tlp%TYPE,

vjenis_pekerjaan in datapegawai.jenis_pekerjaan%TYPE)

as

begin

insert into datapegawai(id_pegawai, nama_pegawai, alamat_pegawai, no_ktp,

no_tlp, jenis_pekerjaan)

values(vid_pegawai, vnama_pegawai, valamat_pegawai, vno_ktp,vno_tlp,

vjenis_pekerjaan);

end;

execute sp_insert_datapegawai (11138,’riyadi’,’jln karah agung no

18’,’2231664578909700’,’085232314333’,’supir’);

select * from datapegawai


3. Buat Procedure Function untuk menghapus datapegawai yang sudah ada di

datapegawai dan data yang akan dihapus dari id_pegawai=1131 .

Dengan query :

Create or replace procedure sp_delete_datapegawai (vid_pegawai in int) as

begin delete from datapegawai where id_pegawai=vid_pegawai;

End;

Execute sp_delete_datapegawai(1131);


pegawai dengan id=1131 telah terhapus dari datapegawai

4. Buat Procedure Function untuk menghapus datacustomer yang sudah ada

di datacustomer dan data yang akan dihapus dari id_customer=221.

Dengan query :

Create or replace procedure sp_delete_datacustomer (vid_customer in

varchar) as begin delete from datacustomer where

id_customer=vid_customer;

end;

execute sp_delete_datacustomer(221);


Customer dengan id=221 telah terhapus dari datacustomer

5. Buat Procedure Function untuk mengupdate datapegawai .

Dengan query :

Create or replace procedure sp_update_datapegawai(p_id_pegawai in int,

p_nama_pegawai in varchar, p_alamat_pegawai in varchar, p_no_ktp in

varchar, p_no_tlp in varchar, p_jenis_pekerjaan in varchar)

as begin

update datapegawai set

id_pegawaih

Select * from datapegawai

Hasil yang di tampilkan :

6. Buat function get_pegawai. Dengan query :

Create or replace function get_pegawai (idp in int)

Return varchar

Is nama varchar(30);

begin

select nama_pegawai into nama from datapegawai where

id_pegawai=idp;

return(nama);

end;

set autoprint on

var get varchar2;

execute :get :=get_pegawai(1112);


7. Mencari customer dengan memasukkan id_customer (223).

Dengan query :

Create or replace function get_customer (idp in int)

Return varchar

Is nama varchar(35);

Begin

Select nama_customer into nama from datacustomer where id_customer=idp;

Return(nama);

End;

Set autoprint on

Var get varchar2;

Execute :get := get_customer(223);


8. Buat function total dengan nama jumlah_harga_sewa. Degan query :

create or replace function jumlah_harga_sewa(id in int)return sys_refcursorisjumlah sys_refcursor;beginopen jumlahfor select sum(harga_sewa*lama_sewa) from dataperoidesewa where id_sewa=id;return jumlah;end;var x refcursor;exec :x := jumlah_harga_sewa(23);print x;


9. Buat function total dengan nama jumlah sewa. Dengan query :

Create or replace function jumlah_sewa

(id in int)

Return sys_refcursor

Is

Jumlah sys_refcursor;

Begin

Open jumlah

For select sum (total*jumlah) from transaksi where id_transaksi=id;

Return jumlah;

End;

Var x refcursor;

Exec :x := jumlah_sewa(405);

Print x;

SQL

1. Buat Function dengan nama get_customer untuk melihat customer dengan

id 223. Dengan query :

create function get_customer

(

@nama varchar(35)

)

as

return

(

select * from datacustomer where id_customer like’%’ + @nama + ‘%’

)

go

select * from get customer (223)


2. Buat function untuk mencari pegawai dari no id_pegawai. Dengan query :

create function get_pegawai

(

@nama varchar (30)

)

returns TABLE

as

return

(

select * from datapegawai where id_pegawai like’%’ + @nama + ‘%’

)

go

select * from get_pegawai (‘1112’)

Hasil yang di tampilkan :

3. Buat function untuk melihat harga dengan nama jumlah_harga_sewa.

Dengan query :

create function jumlah_harga_sewa(@id int)RETURNS @resultTable TABLE(harga_sewa int)asbegininsert @resultTable(harga_sewa)select sum (harga_sewa * lama_sewa)from dataperiodesewa where id_sewa=@idreturnendgoselect * from jumlah_harga_sewa (23)


4. Buat function dengan nama jumlah_sewa . Dengan query :

create function jumlah_sewa

(

@id int

)

returns @resultTable TABLE

(

jumlah int,

total int

)

as

begin

insert @resultTable

(

jumlah ,

total

)

select

count (id_transaksi),

sum (jumlah*total)

from transaksi where id_transaksi=@id

return

end

go

select * from jumlah_sewa (405)


5. Buat procedure untuk menambahkan data customer dengan nama

sp_customer. Dengan query :

Create procedure sp_insert_customer@id_customer int,@nama_customer varchar(30),@alamat_customer varchar(30),@no_ktp varchar(30),@no_tlp varchar(30),@id_jaminan intAsBeginInsert into datacustomer(id customer, nama_customer, alamat_customer, no_ktp, no_tlp, id_jaminan)Values(@id_customer,@nama_customer,@alamat_customer,@no_ktp,@no_tlp,@id_jaminan)End

Execute sp_insert_customer@id_customer=246,@nama_customer=’eny’,@alamat_customer=’jln.jambangan palm spring’,@no_ktp=’674528009912100’,@no_tlp=’081217332599’,@id_jaminan=’525’Select * from datacustomer


6. Buat procedure untuk menambahkan data pegawai dengan nama

sp_insert_datapegawai. Dengan query :

Create procedure sp_insert_datapegawai@id_pegawai int,

@nama_pegawai varchar(20),@alamat_pegawai varchar(30),@no_ktp varchar(30),@no_tlp varchar(30),@jenis_pekerjaan varchar(30)AsBeginInsert into datapegawai(id_pegawai, nama_pegawai, alamat_pegawai, no_ktp, no_tlp, jenis_pekerjaan)Values(@id_pegawai,@nama_pegawai,@alamat_pegawai,@no_ktp,@no_tlp,@jenis_pekerjaan)EndGo

Execute sp_insert_datapegawai@id_pegawai=11138,@nama_pegawai=’riyadi’@alamat_pegawai=’jln.karah agung no 18’,@no_ktp=’2231664578909700’,@no_tlp=’085232314333’,@jenis_pekerjaan=’supir’Select * from datapegawai


7. Buat procedure dengan nama sp_update_datapegawai. Dengan query :Create procedure sp_update_datapegawai@id_pegawai int,@nama_pegawai varchar(20),@alamat_pegawai varchar(20),@no_ktp varchar(20),@no_tlp varchar(20),@jenis_pekerjaan varchar(20)As beginUpdate datapegawai setId_pegawai=@id_pegawai,nama_pegawai=@nama_pegawai, alamat_pegawai=@alamat_pegawai, no_ktp=@no_ktp, no_tlp=@no_tlp, jenis_pekerjaan=@jenis_pekerjaanWhere id_pegawai=@id_pegawai;End

Execute sp_update_datapegawai@id_pegawai=1112,@nama_Pegawai=’chili’,@alamat_pegawai=’jln ahmad yani’,@no_ktp=’2311990044878977’,@no_tlp=’085253465633’,@jenis_pekerjaan=’kasir’Select * from datapegawai


8.Buat procedure untuk mendelete datapegawai dari id_pegawai dengan nama sp_delete_datapegawai. Dengan query :

Create procedure sp_delete_datapegawai(@id_pegawai int)As begin transactionDelete from datapegawai where id_pegawai=@id_pegawaiIf @@ERROR <>0BeginRollback transactionEndElseBegin

Commit transactionEndGo

Execute sp_delete_datapegawai@id_pegawai=11131Select * from datapegawai


KESIMPULAN

Function dan Stored Procedure merupakan perintah-perintah yang memudahkan

kita untuk bisa memberi suatu pesan dan memudahkan kita untuk bisa menyusun

query dengan benar.

LEMBAR MONITORING

laporan praktikum 4_santi dewi putri sutikno_11120037

Documents