Wednesday 11 March 2015

Modul Praktikum Data Mining "STORED PROGRAM"

08:07:00    No comments


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