Praktikum 4
STORED PROGRAM
Tujuan :
1.
Mengetahui dan memahami procedure
2.
Mengetahui dan memahami function
3.
Mampu mengimplementasikan
procedure dan function dalam pemrograman di oracle
Alat dan Bahan :
1.
Buku literatur mengenai PL/SQL
ORACLE, terutama yang menggunakan DBMS ORACLE 10g.
2.
Modul praktikum BASIS DATA LANJUT
3.
Modul praktikum DATA MINING DAN
WAREHOUSING
1.1 Non Nested Procedure
Non nested Procedure atau stored procedure merupakan
sekumpulan blok PL/SQL yang tersimpan
di dalam skema database dan dapat dieksekusi secara berulang kali jika user
memiliki privilege untuk mengeksekusi procedure tersebut. Nama procedure
yang dibuat nantinya akan menjadi objek
dengan tipe procedure. Procedure akan dieksekusi pada saat pemanggilan setelah
sebelumnya dibuat terlebih dahulu.
Sintaks Pendeklarasian:
CREATE [OR REPLACE] PROCEDURE nama_procedure [argum en1
tipe_data, argumen2 tipe_data,...] AS
[deklarasi variabel lokal]
BEGIN badan_prosedur
EXCEPTION
END;
|
Keterangan : nama_procedure : nama dari procedure yang akan
dibuat argumen : parameter-parameter
yang dipakai saat pemanggilan procedure badan_prosedur : tempat blok PL/SQL
yang memuat baris kode.
Contoh :
CREATE OR REPLACE PROCEDURE
tambahMahasiswa
( v_nim mahasiswa.nim%TYPE;
v_nama mahasiswa.nama%TYPE;
v_alamat departemen.lokasi%TYPE;
)
IS
BEGIN
INSERT INTO mahasiswa
VALUES(v_nim,v_nama,v_alamat);
END;
|
Setelah procedure dibuat, dijalankan dengan sintaks
berikut.
EXECUTE
nama_procedure(parameter_1,parameter_2,...);
|
Contoh :
EXECUTE
tambahMahasiswa
(‘30108002’,‘paimen’,‘sukapura’);
|
Untuk menghapus procedure yang telah kita buat, dapat
menggunakan sintaks berikut
DROP
PROCEDURE nama_procedure;
|
Contoh :
DROP
PROCEDURE tambahMahasiswa;
|
1.2 Nested Procedure
Adalah procedure yang
dideklarasikan di dalam Declaration Section suatu blok PL/SQL yang anonim.
Karena dideklarasikan di dalam blok PL/SQL yang anonim maka nested procedure
tidak disimpan dalam basis data dan tidak dapat dijalankan oleh blok lain kecuali dideklarasikan kembali oleh blok
tersebut.
Contoh :
DECLARE
PROCEDURE
CETAK(kata varchar2, n number)
IS BEGIN FOR i IN 1..n LOOP
DBMS_OUTPUT.PUT_LINE(to_char(i)|| ’. ‘||kata);
END LOOP;
END CETAK;
BEGIN
CETAK(’&v_kata’,
&v_n);
END;
|
1.3 Jenis-Jenis Procedure Pada Oracle
1.3.1 Procedure Tanpa Parameter/Argumen
Procedure yang tidak memiliki
parameter/argument biasanya bersifat statis
(outputannya selalu sama) setiap kali dieksekusi.
Contoh:
CREATE OR REPLACE PROCEDURE
lihat_mahasiswa
IS vnama mahasiswa.nama%TYPE;
BEGIN
SELECT nama INTO vnama
FROM mahasiswa WHERE nim='30108002';
DBMS_OUTPUT.PUT_LINE('Nama mahasiswa dengan nim 30108002 adalah
'||vnama);
END;
/
|
Hasil setelah dieksekusi adalah sbb,
SQL> EXECUTE
lihat_mahasiswa;
Nama
mahasiswa dengan nim 30108002 adalah paimen
|
Procedure lihat_mahasiswa di atas bersifat statis,
dieksekusi kapanpun memiliki output yang sama.
1.3.2 Procedure Dengan
Parameter/Argumen
Perbedaan dengan procedure tanpa parameter yaitu procedure
dengan parameter memiliki output yang dinamis sesuai dengan nilai yang diberi
pada parameter procedure tersebut. Default argumen pada Oracle adalah IN.
Jenis-Jenis parameter/argumen dari Procedure
adalah :
a. Parameter Masukan
(Input)
Ditandai dengan atribut IN,
dimana nilai dari parameter ini merupakan inputan untuk sebuah procedure.
Contoh:
CREATE OR REPLACE
PROCEDURE lihat_mahasiswa
(vnim IN mahasiswa.nim%TYPE)
IS
vnama
mahasiswa.nama%type;
BEGIN
SELECT nama
INTO vnama FROM mahasiswa WHERE nim = vnim;
DBMS_OUTPUT.PUT_LINE('Nama
mahasiswa dengan nim '||vnim||'
adalah '||vnama);
END;
/
|
b. Parameter Keluaran
(Output)
Ditandai dengan atribut OUT,
dimana parameter ini merupakan variabel penampung untuk output sebuah
procedure.
Contoh:
Procedure yang menggunakan
parameter masukan dan juga parameter
keluaran adalah sebagai berikut :
CREATE OR REPLACE PROCEDURE lihat_mahasiswa
(vnim IN mahasiswa.nim%TYPE,vnama OUT
mahasiswa.nama%TYPE)
IS
BEGIN
SELECT
nama INTO vnama FROM mahasiswa WHERE nim = vnim; DBMS_OUTPUT.PUT_LINE('Nama mahasiswa
dengan nim '||vnim||'
adalah '||vnama);
END;
/
|
Untuk mengeksekusi procedure
lihat_mahasiswa :
SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
2
vnama
mahasiswa.nama%type;
3
BEGIN
4
lihat_mahasiswa
('30108002',vnama);
5
DBMS_OUTPUT.PUT_LINE('Nama mahasiswa
teladan bulan ini
adalah
'||vnama);
6
END;
7
/
c. Nama
mahasiswa teladan bulan ini adalah paimen Parameter Masukan/Keluaran (Input/Output)
|
Ditandai atribut IN OUT, dimana
parameter tersebut dianggap sebagai masukan kemudian diproses dan ditampilkan
kembali sebagai keluaran.
Contoh:
CREATE OR REPLACE
PROCEDURE kuadrat (x IN OUT numbe
r) IS
BEGIN
x:=x*x;
END;
/
|
Untuk mengeksekusi procedure kuadrat :
SQL> SET VERIFY OFF;
SQL> DECLARE
2
bil number := '&input_angka';
3
n number;
4
BEGIN
5
n := bil;
6
kuadrat(bil);
7
DBMS_OUTPUT.PUT_LINE('kuadrat dari '||n||' adalah '||bil);
8
END;
9
/
Enter value for input_angka : 5 kuadrat
dari 5 adalah 25
|
2. Function
Perbedaan mendasar antara function dan procedure adalah
bahwa function harus mengembalikan nilai tertentu kepada pemanggilnya Nilai ini
dikembalikan dengan menggunakan sintaks RETURN.
2.1 Non Nested Function
Disebut juga stored function yang mirip dengan stored
procedure tetapi harus memberikan output sebuah nilai. Function ini tersimpan
dalam basis data.
Sintaks:
CREATE [OR REPLACE] FUNCTION nama_function
[(argumen [IN|OUT|IN OUT] tipe_data,argumen [IN|OUT|IN
OUT]tipe_data, ...)]
RETURN tipe_data {IS|AS}
[deklarasi variabel lokal]
BEGIN badan fungsi
END;
|
RETURN adalah
nilai yang dikembalikan oleh function. Jika terdapat RETURN dalam badan fungsi,
maka itu berfungsi untuk mengembalikan kontrol kepada pemanggil fungsi bersama
nilai yang dikembalikan fungsi.
Sintaks:
RETURN
ekspresi;
|
Dalam satu fungsi dimungkinkan penggunaan RETURN yang lebih
dari satu tetapi bila di dalam badan fungsi tidak terdapat pernyataan RETURN
maka akan terjadi error.
Contoh:
CREATE OR REPLACE FUNCTION tambah (n1
number, n2 number)
RETURN number IS
BEGIN
RETURN(n1+n2);
END;
|
Cara mengeksekusi :
SQL > SELECT tambah (1,8) FROM
dual;
tambah (1,8)
9
|
Contoh :
CREATE OR REPLACE FUNCTION
cariMahasiswa(vnim mahasiswa.nim%TYPE)
RETURN number IS vnim mahasiswa.nim%TYPE;
BEGIN
SELECT nim FROM mahasiswa WHERE nim =
vnim;
IF SQL%FOUND THEN
RETURN 1;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN 0;
END
cariMahasiswa;
|
Contoh pemanggilan fungsi :
DECLARE
BEGIN
IF(cariMahasiswa('30108002') = 1) THEN
DBMS_OUTPUT.PUT_LINE('mahasiswa
terdaftar');
ELSE
DBMS_OUTPUT.PUT_LINE('mahasiswa tidak
terdaftar');
END IF;
END;
|
Untuk meng-compile ulang sebuah function digunakan sintaks
berikut
ALTER
FUNCTION nama_fungsi COMPILE;
|
Sedangkan untuk menghapus funsi
digunakan sintaks berikut
DROP
FUNCTION nama_fungsi;
|
2.2 Nested Function
Seperti nested procedure, fungsi ini adalah fungsi yang
dideklarasikan dalam suatu blok xPL/SQL, tidak tersimpan dalam basis data dana
hanya bisa dipanggil oleh blok dimana fungsi dideklarasikan.
Contoh:
DECLARE
\ jum number;
FUNCTION tambah(n1 number, n2 number)
RETURN
number IS
BEGIN
RETURN(n1+n2);
END tambah; BEGIN
jum:=tambah(2,3);
DBMS_OUTPUT.PUT_LINE('Hasil
penjumlahan antara 2 dan 3 adalah
'||TO_CHAR(jum));
END;
|
2.3 Jenis-Jenis Function
Secara khusus, jenis-jenis FUNCTION dapat diuraikan sebagai
berikut :
1. Function Tanpa Parameter
Function ini tidak memiliki
parameter/argumen biasanya bersifat statis setiap kali dieksekusi.
Contoh :
DECLARE
FUNCTION jumMahasiswa
RETURN number IS
jum
number;
BEGIN
SELECT COUNT(nim) INTO jum FROM mahasiswa;
RETURN(jum);
END
jumMahasiswa;
BEGIN
DBMS_OUTPUT.PUT_LINE('Jumlah
mahasiswa saat ini adalah
'||TO_CHAR(jumMahasiswa));
END;
|
2. Function
Ber-Prameter
Sama seperti Procedure
berparameter, Function ber-parameter memiliki output yang dinamis sesuai dengan
nilai yang di-assign ke parameter pada function tersebut.
Adapun parameter yang dimaksud disini adalah : a. Parameter masukan Contoh :
CREATE OR REPLACE
FUNCTION luas_persegi_panjang (p number,l number) RETURN number
IS
BEGIN
RETURN (p*l);
END;
|
Salah satu cara untuk mengeksekusinya dengan menggunakan
klausa SELECT seperti di bawah ini :
SQL> SELECT luas_persegi_panjang (8,3) FROM dual;
LUAS_PERSEGI_PANJANG(8,3)
24
b. Parameter keluaran
Contoh :
CREATE OR REPLACE FUNCTION
volume_tabung
(r IN number, t IN number,luas OUT
number)
RETURN number IS pi number:=3.14; vol number;
BEGIN luas:=pi*r*r; return (luas*t);
END;
|
Misal digunakan blok PL/SQL untuk
menjalankan fungsi diatas :
DECLARE
L_alas number;
BEGIN
DBMS_OUTPUT.PUT_LINE('Volume tabung
paijo adalah
'||volume_tabung (2,5,L_alas));
DBMS_OUTPUT.PUT_LINE
('Luas alas lingkaran pada tabung paijo adalah '||L_alas);
END;
|
Output :
Volume tabung paijo adalah 62.8
Luas
alas lingkaran pada tabung paijo adalah 12.56
|
Contoh di atas menunjukan bahwa
function dapat mengembalikan lebih dari satu nilai lewat parameter OUT.
c. Parameter
masukan/keluaran Contoh :
CREATE OR REPLACE FUNCTION volume_tabung_ku (x IN OUT number,
t IN number)
RETURN number IS pi number:=3.14; vol number;
BEGIN x:=pi*x*x;
vol:= x*t;
RETURN (vol);
END;
|
digunakan blok PL/SQL berikut
untuk menjalankan fungsi diatas :
DECLARE y number:='&jari_alas';
BEGIN
DBMS_OUTPUT.PUT_LINE('Volume
tabung paimen adalah
'||volume_tabung2(y,5));
DBMS_OUTPUT.PUT_LINE('Luas
alas lingkaran tabung paimen adalah '||y);
END;
|
Output :
Enter
value for jari_alas: 2
old 2: L number:='&jari_alas';
new 2: L number:='2';
Volume tabung paimen adalah 62.8
Luas
alas lingkaran tabung paimen adalah 12.56
|
Tugas Pendahuluan :
1.
Jalankan dan jelaskan maksud dari
PROCEDURE dibawah ini :
PROCEDURE calc_bonus (emp_id IN
INTEGER, bonus OUT REAL)
IS hire_date DATE;
bonus_missing EXCEPTION;
BEGIN
SELECT sal*0.10,hiredate INTO bonus,hire_date FROM emp WHERE
empno = emp_id;
IF bonus IS NULL THEN RAISE
bonus_missing;
END IF;
IF
MONTHS_BETWEEN(SYSDATE,hire_date)>60
THEN bonus:=bonus + 500;
END IF;
EXCEPTION
WHEN
bonus_missing THEN END calc_bonus;
|
2.
Jalankan, apa outputnya dan
analisa PACKAGE dibawah ini :
CREATE OR REPLACE PACKAGE lihat IS
FUNCTION is_kartu
(id pembayaran.id_pembayaran%TYPE)
RETURN number;
PROCEDURE all_kartu;
END;
CREATE OR REPLACE
PACKAGE BODY lihat IS FUNCTION is_kartu
(id
pembayaran.id_pembayaran%TYPE) RETURN number IS kartu
pembayaran.pembayaran_kartu%TYPE;
tunai pembayaran.pembayaran_tunai%TYPE;
BEGIN
SELECT
pembayaran.pembayaran_kartu, pembayaran.pembayaran_tunai INTO
kartu,tunai
FROM pembayaran WHERE
id=pembayaran.id_pembayaran;
IF kartu>=tunai THEN RETURN (1);
ELSE RETURN (0);
END IF;
END;
PROCEDURE all_kartu IS
CURSOR c_pembayaran IS SELECT * FROM pembayaran WHERE is_kartu (pembayaran.id_pembayaran)=1; nama
pemesan.nama_pemesan%TYPE; id pembayaran.id_pembayaran%TYPE;
BEGIN
DBMS_OUTPUT.PUT_LINE('----------------------');
FOR x IN c_pembayaran
LOOP id:=x.id_reservasi;
SELECT c.nama_pemesan INTO nama FROM reservasi b, pemesan c
WHERE id=b.id_reservasi AND
b.id_pemesan=c.id_pemesan;
DBMS_OUTPUT.PUT_LINE(c_pembayaran%ROWCOUNT||'.'||x.id_
pembayaran||' pada tanggal'||x.tgl_pembayaran||'oleh '||nama);
END LOOP;
END;
END;
EXECUTE
lihat.all_kartu;
|
Percobaan :
1.
Buat sebuah procedure untuk
melakukan enkripsi sebuah string yang di-inputkan oleh user. Jika string yang
dihasilkan sama maka keluarkan perintah gagal enkripsi Contoh hasil tampilan :
SQL> EXECUTE enkripsi('oh
semoga nilaiku bagus');
enkripsi berhasil
string sebelum enkripsi
: semoga selalu bahagia string sesudah
enkripsi : s3m0g4 s3l4lu b4h4g14
PL/SQL procedure successfully
completed.
SQL> EXECUTE enkripsi('kyt
kmnwxz zzbfr');
enkripsi gagal
PL/SQL
procedure successfully completed.
|
2.
Buatlah fungsi untuk menghasilkan
nilai biner dari suatu angka desimal. Tuliskan juga cara mengeksekusinya.
SQL> SELECT biner(10) FROM
dual;
1010
|
0 komentar:
Post a Comment