Diklat Praktikum Basis Data II 2008-2009 PENDAHULUAN INSTALASI ORACLE 9i DAN KONFIGURASI NETWORK A. Tujuan Praktikum Mahasiswa dapat memahami dan mempraktekkan cara penginstalan program database, bukan hanya menggunakannya saja. B. Penginstalan Oracle 9i Oracle database adalah salah satu produk Oracle Corp. Database Oracle adalah salah satu database yang powerfull, secure untuk tingkat kompleksitas yang tinggi. Instalasi Oracle 9i yang digunakan pada praktikum Basisdata II ini menggunakan operating system (OS) Windows XP dan ruang hardisk sekitar 2.7 GB. Instalasi Oracle 9i terdiri atas 3 CD, pada instalasi Oracle 9i terdapat beberapa langkah yang harus dilakukan agar database dapat digunakan yaitu menginstal Oracle 9i, membuat database, dan setting konfigurasi network (TNS). Berikut langkah-langkah instalasinya : Langkah 1 : Welcome Jalankan setup dari CD pertama atau autoplay dan pilih Install Product. Langkah 2 : File Location Pada langkah ini kita harus memilih path file source yang akan diinstall serta path direktori di mana kita akan menyimpan hasil instalasi. Source berisi path source instalasi. Destination berisi nama HOME (istilah yang digunakan oleh product Oracle untuk menandai lokasi direktori instalasi) dan direktori asosiasinya. Kemudian klik Next. Laboratorium Komputer III ISTA Kotabaru 1 Langkah 3 : Avaible Product Memilih product yang akan diinstall. Karena kkita akan menginstall database Oracle, maka pilih pilihan paling atas (Oracle Database 9.0.1.1.1). Klik NEXT. Langkah 4 : Installation Types Kita harus memilih tipe instalasi yang kita inginkan. Sesuaikan dengan kebutuhan, sebaiknya pilih tipe Enterprise Edition karena kita dapat mencoba semua fitur yang terdapat did ala Oracle 9i. Kemudian klik NEXT. Langkah 5 : Database Configuration General Purpose yaitu database yang dibuat untuk keperluan general. Transaction Processing yaitu database yang bersifat spesifik untuk transaksi. Data Warehouse yaitu database yang bersifat spesifik untuk data warehouse. Customized yaitu kita dapat mengkonfigurasi sendiri tipe database yang dibuat. Software Only yaitu setelah instalasi tidak membuat database. Pada langkah ini kita memilih database General Purpose. Klik NEXT. Langkah 6 : Database Identifications Pada langkah ini kita harus memberi nama database pada Global Database Name dan Oracle system Identifier (SID). Biasanya Global database dan SID sama. Klik NEXT. Langkah 7 : Database File Location Pada langkah ini kita harus menentukan lokasi direktori file database yang akan disimpan. Klik NEXT. Langkah 8 : Database Character Set Pilih Use the default character set. Klik NEXT. Langkah 9 : Summary Ini adalah rangkuman product yang telah dipilih dan akan diinstall. Klik Install. Langkah 10 : Install Pada langkah ini, product yang telah dipilih diinstall ke directori Orahome. Selama proses berlangsung kita akan diminta untuk memasukkan CD kedua dan ketiga. Ikuti instruksi-instruksi tersebut sampai instalasi selesai. Langkah 11 : Configuration Tools Pada tahap ini product-product yang baru saja terinstall akan dikonfigurasi. Langkah 12 : End of Installation Ini merupakan langkah terakhir instalasi Oracle 9i. klik Exit. C. Konfigurasi Network Dari Start menu, jalankan Net Configuration Assistant. Konfigurasi network ini akan menghasilkan file TNSNAMES.ORA yang disimpan di folder oracle\ora81\network\admin. Dengan konfigurasi inilah Oracle Developer dan atau produk Oracle yang lainnya akan mengenali database yang kita buat. Langkah 1 : Welcome Pilih Local Net Service Name Configuration. Klik NEXT. Langkah 2 : Net Service Name Configuration Pilih Add kemudian klik NEXT. Langkah 3 : Net Service Name Configuration, Database Version Pilih Oracle8i or later database or service. Langkah 4 : Net Service Name Configuration, Service Name Ketikkan sesuai dengan Langkah 5 : Net Service Name Configuration, Select Protocols Pilih TCP, lalu klik NEXT. Langkah 6 : Net Service Name Configuration, TCP/IP Protocol Ketikkan hostname anda. Pada contoh hostname diisi localhost karena database terinstall dalam computer yang sama. Langkah 7 : Net Service Name Configuration, Test Pilih Yes, perform a test, kemudian klik NEXT. Langkah 8 : Net Service Name Configuration, Connecting Connection Successful…. Dengan ini anda telah berhasil mengkonfigurasikan network (TNS). Langkah 9 : Net Service Name Configuration, Net Service Name Net service name adalah alias yang akan anda gunakan pada saat mengakses database. Net service name dapat diisi dengan nama bebas (missal nama anda). Pada contoh ini kita memberi nama pada Net service name sama dengan SID database. Klik NEXT. Langkah 10 : Net Service Name Configuration, Another Net Service Pilih No, kemudian klik NEXT. Langkah 11 : Net Service Name Configuration Done Selamat !! anda berhasil mengkonfigurasi network untuk mengakses database. D. Test Database Kita akan masuk ke database yang telah kita buat untuk memastikan bahwa database berjalan dengan baik. Anda dapat menggunakan SQL Plus. Jalankan SQL Plus dari start menu, login dengan : User Name : scott Password : tiger Host String : oradb Scott dan tiger adalah pasangan standar yang otomatis disediakan Oracle, anda dapat menggunakan user scott ini untuk berlatih bahasa SQL di Oracle. BAB I PENGENALAN PL/SQL A. Tujuan Praktikum Mahasiswa memahami dan mengerti mengenai PL/SQL. B. Tool SQL*Plus PL/SQL (Procedural Language/Structure Query Language) adalah suatu blok yang berisi skrip-skrip bahasa procedural. Penulisan SQL menggunakan tool SQL*Plus. Tool dalam bentuk Command Line tersebut adalah standar dari Oracle untuk melakukan interaksi dengan database. Anda dapat menggunakan SQL*Plus dari start menu, login dengan : User Name : scott Password : tiger Host String : oradb Scott dan tiger adalah pasangan standar yang otomatis disediakan Oracle, anda dapat menggunakan user scott ini untuk berlatih bahasa SQL di Oracle. Demi alasan keamanan, sebaiknya segera mengubah password user tersebut, dengan cara sebagai berikut : 1. jalankan SQL*Plus dari Start menu. 2. connect dengan menggunakan user name : scott, password : tiger, host string : oradb. 3. setelah berhasil, untuk mengawali penggantian password, ketikkan perintah berikut : Selanjutnya anda akan diminta untuk memasukkan password yang lama yaitu tiger, kemudian password baru, misalnya lab3. Ulangi sekali lagi password tersebut. Maka password telah berubah menjadi lab3. C. Dasar-Dasar SQL Menurut fungsinya SQL dibagi menjadi dua kategori, yaitu DDL (Data Definition Language) dan DML (Data Manipulation Language). DDL (Data Definition Language) untuk mendefinisikan data dan object database, yang termasuk kategori ini adalah : Perintah Keterangan CREATE TABLE Membuat tabel CREATE INDEX Membuat indek ALTER TABLE Mengubah atau menyisipkan kolom kedalam tabel DROP TABLE Menghapus tabel dari database DROP INDEX Menghapus indek GRANT Memberikan izin akses pada user DML (Data Manipulation Language) untuk menampilkan dan memanipulasi data. Beberapa perintah yang sering digunakan adalah : Perintah Keterangan SELECT Memilih atau menampilkan data INSERT Menyisipkan baris data ke tabel DELETE Menghapus baris data dari tabel UPDATE Mengubah isi dari kolom pada tabel Catatan : Dalam membuat tabel, anda dapat menamakan hingga 30 karakter. Sebaiknya penamaan dalam pembuatan tabel dibuat singkat namun memberikan gambaran tentang isi tabel, misalnya nm_kr akan sulit dibayangkan isinya, dibandingkan dengan nama_karyawan. NULL atau NOT NULL mengidentifikasi apakah kolom pada tabel tersebut harus diisi atau boleh tidak diisi. 1. Membuat Tabel Untuk membuat tabel, kita dapat menggunakan perintah CREATE TABLE. Perintah dasarnya : CREATE TABLE nama_table (nama_kolom tipe data [NULL| NOT NULL] [IDENTITY (NILAI AWAL, NILAI AKHIR) ] ); Untuk mempermudah belajar dan mengikuti bab-bab selanjutnya, sebaiknya anda langsung mempraktekkan. Untuk keperluan bab selanjutnya, anda perlu membuat 4 tabel, yaitu tabel pegawai, departemen, jabatan, lokasi, tunjangan. Latihan : Untuk membuat tabel pegawai, ketikkan perintah berikut di SQL*Plus : Perintah tersebut artinya kita membuat tabel dengan nama pegawai. Tabel tersebut mempunyai 5 kolom, yaitu id_pegawai, nama, jenis_kelamin, alamat, telp. Kolom yang memiliki tipe data NUMBER berarti hanya dapat diisi angka, sedangkan VARCHAR2 artinya dapat diisi data karakter. Angka dalam kurung setelah tipe data adalah jumlah maksimal karakter yang dapat dimasukkan. Selanjutnya silahkan anda membuat tabel-tabel jabatan, departemen, lokasi, tunjangan. SQL> create table jabatan ( 2 id_jabatan varchar2 (25) not null, 3 jabatan varchar2 (30), 4 gaji_pokok number); SQL> create table departemen ( 2 id_dep 3 nama_dept varchar2 (30) ); SQL> varchar2 (5) not null, create table lokasi ( 2 id_lokasi number (5) not null, 3 lokasi varchar2 (30) ); SQL> create table tunjangan ( 2 id_tunjangan number (2) not null, 3 keterangan varchar2 (30), 4 jumlah number ); 2. Menghapus Tabel Perintah untuk menghapus tabel adalah DROP TABLE. Sebaiknya anda berhatihati dalam menggunakan perintah ini, karena perintah ini tidak hanya menghapus tabel dari database tetapi juga menghapus struktur dan datanya. Latihan : Sebelum mencoba, sebaiknya silahkan anda membuat tabel contohnya terlebih dahulu, misalnya : SQL> create table coba_coba ( 2 id_coba number not null, 3 coba_aja varchar2 (10) ); Untuk menghapus tabel coba_coba : 3. Mengubah dan Menambah Kolom Perintah untuk mengubah atau menambah kolom pada suatu tabel ALTER TABLE. Menambah Latihan : menambah kolom propinsi pada tabel departemen. SQL> 2 alter table departemen add ( propinsi varchar2 (30) ); Mengubah Untuk mengubah kolom pada suatu tabel adalah seperti berikut : SQL> 2 alter table departemen modify ( propinsi varchar2 (50) ); Pada latihan diatas, kolom id_lokasi pada kolom departemen diubah dari tipe data number(7) menjadi number(5). Jika anda ingin menghapus suatu kolom pada tabel, anda dapat menggunakan perintah drop, misalnya kita tidak memerlukan kolom propinsi pada tabel departemen. SQL> alter table departemen drop (propinsi); 4. Memasukkan Data Perintah untuk memasukkan data ke dalam tabel adalah INSERT. Latihan : SQL> insert into pegawai 2 (id_pegawai, nama, jenis_kelamin, alamat, telp) 3 values 4 (‘1’,’Anggara Siswoutomo’,’1’,’Jalan Kapten Tendean 3’, 5 ‘(0274) 589154’); Perintah diatas untuk memasukkan satu baris data ke dalam tabel. Kolom jenis kelamin hanya diisi angka 1 mewakili kata laki-laki sedangkan o mewakili kata perempuan. Trik ini sebenarnya untuk penghematan bytes dan mempercepat query. Anda dapat menggunakan alternative perintah berikut : SQL> insert into pegawai 2 values 3 (‘1’,’Anggara Siswoutomo’,’1’,’Jalan Kapten Tendean 3’, 4 ‘(0274) 589154’); Silahkan anda mengisi tabel-tabel yang anda buat sambil mengasah ketrampilan SQL anda. 5. Menampilkan Data Perintah utnuk menampilkan data adalah SELECT. Perintah dasar dari perintah tersebut adalah : SELECT nama_tabel.nama_kolom, nama_tabel.nama_kolom FROM nama_tabel; Latihan : SQL> select pegawai.id_pegawai, 2 pegawai.nama, 3 pegawai.telp 4 from pegawai; Perintah diatas berarti bahwa mengambil data id_pegawai, nama dan telp dari tabel pegawai. Perintah tersebut dapat disingkat menjadi : SQL> select id_pegawai, nama, telp from pegawai; Jika anda ingin menampilkan semua kolom pada satu tabel dapat menggunakan perintah : SQL> select*from pegawai; 6. Klausa ORDER BY dan WHERE Klausa ORDER BY Klausa ORDER BY digunakan untuk mengurutkan baris-baris yang dihasilkan dengan perintah SELECT berdasarkan kolom tertentu. Perintah ini diikuti dengan jenis urutan yang kita inginkan, yaitu ASC (Ascending) atau DESC (Descending). ASC adalah jenis urutan dari kecil ke besar atau a ke z, sedangkan DESC adalah jenis urutan dari besar ke kecil atau z ke a. Latihan : ASC SQL> select pegawai.id_pegawai, 2 pegawai.nama, 3 pegawai.telp 4 from pegawai 5 order by nama ASC; Latihan : DESC SQL> select pegawai.id_pegawai, 6 pegawai.nama, 7 pegawai.telp 8 from pegawai 9 order by nama DESC; Klausa WHERE Klausa WHERE digunakan untuk menampilkan data sesuai dengan persyaratan tertentu. Persyaratan tersebut menggunakan bentuk perbandingan. Latihan : SQL> select nama from pegawai where jenis_kelamin=1; Perintah diatas menampilkan nama dari tabel pegawai berjenis kelamin laki-laki. Anda dapat menggunakan alternative perintah berikut : SQL> select*from pegawai where alamat=’Jalan Kapten Tendean 3’; Perintah diatas menampilkan semua data pegawai yang beralamat di Jalan Kapten Tendean 3. 7. Update Data Perintah untuk mengubah data pada tabel adalah UPDATE. Latihan : SQL> 2 update pegawai set telp=’(0274) 777777’ where id_pegawai=1; Perintah diatas berarti mengubah data di kolom telp dari tabel pegawai menjadi (0274) 777777 pada baris yang mempunyai id_pegawai=1. Latihan : SQL> update pegawai set telp=’(0274) 333333’; Perintah diatas berarti mengubah semua nilai pada kolom tertentu dalam satu data. 8. Menghapus Data Jika ingin menghapus data, gunakan perintah DELETE. Latihan : SQL> delete from pegawai where id_pegawai=3; Artinya menghapus data pegawai yang mempunyai id_pegawai=3. Anda dapat menghapus seluruh data pada tabel dengan cara : SQL> delete from lokasi; 9. Menampilkan Data dari Beberapa Tabel Latihan : SQL> select nama, id_pegawai from pegawai; SQL> select id_jabatan, jabatan from jabatan; SQL> select pegawai.nama, jabatan.jabatan 2 from pegawai, jabatan 3 where jabatan.id_jabatan=pegawai.id_jabatan; Perintah terakhir adalah gabungan dari 2 tabel, yaitu pegawai dan jabatan. Antara tabel pegawai dan jabatan dihubungkan dengan id_jabatan (disebut JOIN CONDITION). Pada contoh diatas setiap pegawai harus mempunyai nilai id_jabatan, jika anda tidak memasukkan nilai id_jabatan maka nama pegawai tersebut tidak akan muncul karena tidak mempunyai id_jabatan. Latihan : SQL> select pegawai.nama, jabatan.jabatan 2 from pegawai, jabatan 3 where jabatan.id_jabatan=pegawai.id_pegawai (+) ; Perintah diatas menampilkan semua data walaupun pegawai tersebut tidak mempunyai id_jabatan. Pada contoh diatas menggunakan OUTER JOIN. OUTER JOIN dilakukan dengan menyertakan tanda (+) pada tabel yang boleh memiliki nilai NULL. 10. Sub Query Anda dapat menggunakan SELECT dalam SELECT, artinya di dalam perintah SELECT anda masih dapat menggunakan perintah SELECT lagi. Ini yang disebut dengan Sub Query. Latihan : SQL> select nama from pegawai 2 where id_dept= 3 (select id_dept from departemen 4 where departemen=’IT’); 11. Fungsi Anda dapat melakukan suatu operasi pada sekelompok data. Latihan : menghitung nama pegawai SQL> select sum (jenis_kelamin) from pegawai; Perintah diatas berarti pada kolom jenis_kelamin yang ada hanya 2 nilai, yaitu 0 (mewakili kata perempuan) dan 1 (mewakili kata laki_laki). Berikut adalah daftar Group Function : Fungsi Keterangan DISTINCT Menampilkan data hanya pada baris yang nilainya berbeda-beda. AVG Menghitung rata-rata dari semua nilai. COUNT Menghitung jumlah baris di kolom yang tidak mengandung null MAX Menampilkan nilai minimum MIN Menampilkan nilai minimum STDEV Menghitung jumlah semua nilai. SUM Menghitung jumlah semua nilai. VARIANCE Menghitung varian dari semua nilai. KLAUSA GROUP BY Anda dapat menggunakan klausa GROUP BY untuk membagi baris-baris dalam tabel ke dalam group-group. Latihan : SQL> select jabatan, AVG (gaji_pokok) 2 from pegawai, jabatan 3 where jabatan.id_jabatan=pegawai.id_pegawai 4 group by jabatan; Artinya menampilkan jabatan dan rata-rata gaji_pokok dari pegawai yang dikelompokkan berdasarkan jabatan. KLAUSA HAVING Anda dapat menggunakan klausa HAVING untuk lebih menspesifikasikan group-group mana yang akan ditampilkan. Latihan : SQL> select jabatan, AVG (gaji_pokok) 2 from pegawai, jabatan 3 group by jabatan 4 having avg(gaji_pokok) > 5000000; Artinya menampilkan jabatan dengan gaji_pokok rata-rata di atas 5000000. Tugas 1 : Carilah 3 judul artikel tentang DDL (Data Definition Language) dan DML (Data Manipulation Language). BAB II BLOK DAN OPERATOR PL/SQL A. Tujuan Praktikum Mahasiswa memahami dan mengerti mengenai Blok dan Operator PL/SQL. B. Blok PL/SQL Blok PL/SQL merupakan blok yang berisi skrip-skrip bahasa procedural. Sebelum melangkah lebih lanjut tentang blok PL/SQL terlebih dahulu kita harus mengetahui tentang tipe data, variable dan konstanta. 1. Tipe Data Pada Oracle tipe data PL/SQL secara umum dibedakan menjadi lima yaitu numeric, karakter, date, boolean, dan rowed. Dibawah ini adalah daftar tipe data yang sering digunakan. Nama Tipe Keterangan NUMBER Numerik Untuk semua tipe numeric BINARY_INTEGER Numerik Di dalamnya meliputi tipe unsigned integer DEC Numerik Untuk bilangan decimal DOUBLE PRECISION Numerik Untuk bilangan riil dengan presisi yang tinggi INTEGER Numerik Untuk bilangan bulat INT Numerik Untuk bilangan bulat NUMERIC Numerik Sama dengan NUMERIC REAL Numerik Sama dengan NUMBER SMALLINT Numerik Untuk bilangan bulat degan rentang yang kecil Karakter Untuk string dengan panjang yang dinamis sesuai dengan panjang maksimal yang ditentukan CHAR Karakter Untuk string dengan panjang yang sudah pasti LONG Karakter Untuk string dengan lebar diatas 32.767 byte DATE Date Untuk tipe tanggal BOOLEAN Boolean Bernilai TRUE (benar) dan FALSE (salah) VARCHAR2 ROWID Rowid Untuk tipe rowed atau nilai id dari suatu tabel 2. Variabel Variable adalah sebuah peubah yang digunakan untuk menampung sebuah nilai di memori computer. Nilai yang disimpan didalam variable tersebut dapat diubah setiap saat jika diperlukan. Tipe data dari variable tersebut harus sesuai dengan nilai yang akan ditampung. Misalnya jika dalam membuat blok PL/SQL anda ingin menyimpan nilai dengan tipe data Integer, maka anda harus mendeklarasikan variable tersebut dengan tipe data Integer, jika tidak maka oracle akan menampilkan pesan eror. Contoh : DECLARE {memberitahu oracle untuk mendeklarasikan beberapa variable} X integer; {mendeklarasikan variable X bertipe integer} Alamat varchar2 (40); {mendeklarasikan variable Alamat bertipe varchar2} No_induk char(8); {mendeklarasikan variable No_induk bertipe char} Contoh penyimpana nilai ke dalam variable-variabel tersebut : BEGIN X Alamat No_induk := 12; := `Gelatik Dalam 391, Bandung`; := `D0G96549`; 3. Konstanta Konstanta berguna untuk menyimpan sebuah nilai di memori computer. Nilai yang disimpan bersifat tetap (konstan). Cara mendeklarasikan konstanta pada blok PL/SQL adalah dengan menambahkan kata CONSTANT. Contoh : DECLARE pi CONSTANT real := 3.14; lebar CONSTANT integer := 100; C. Komentar dan Struktur Blok PL/SQL Komentar pada Blok PL/SQL Oracle telah menyediakan fitur yang mengizinkan anda untuk menuliskan komentar-komentar di dalamnya. Adapun jenis komentar yang tersedia dalam Oracle adalah sebagai berikut : 1. /*…*/ komentar ini adalah komentar gaya bahasa C yang dapat digunakan untuk beberapa baris. Contoh : 2. -- /* Ini adalah komentar Oracle*/ komentar ini hanya berlaku untuk satu baris. Contoh : -- Ini juga komentar Oracle Struktur Blok PL/SQL Secara umum struktur blok PL/SQL Oracle terdiri dari tiga bagian, yaitu : 1. Bagian pendeklarasian tipe data Pendeklarasian tipe data ini bersifat opsional yang berarti tidak mutlak untuk dituliskan, misalnya anda ingin membuat sebuah blok PL/SQL yang tidak memerlukan satu pun variable atau konstanta, tentu anda tidak perlu menuliskan bagian pendeklarasian pada blok PL/SQL yang akan dibuat. 2. Bagian penulisan perintah-perintah Bagian ini harus ada pada setiap blok PL/SQL yang anda buat karena secara logika apalah artinya sebuah blok PL/SQL jika di dalamnya tidak terdapat perintah sama sekali. 3. Bagian Eksepsi Bagian ini bersifat opsional, tetapi penggunaannya sangatlah dianjurkan karena berguna sekali untuk menjaga terjadinya kesalahan pada blok PL/SQL. Anda tidak perlu menuliskan bagian eksepsi ini jika benar-benar yakin bahwa pada blok PL/SQL yang dibuat tidak mungkin terjadi error. Berikut adalah sintak umum untuk membuat sebuah blok PL/SQL di Oracle : DECLARE variable tipe_data; -- Mendeklarasikan variable konstanta constant tipe_data := nilai; -Mendeklarasikan konstanta … BEGIN Statemen_1; Statemen_2; … EXCEPTION WHEN nama_eksepsi THEN Statemen_untuk_mengatasi_error; … END; Catatan : Di Oracle ini kita menggunakan package DBMS_OUTPUT untuk menampilkan teks ke layar SQL*PLUS dan procedure PUT_LINE yang ada dalam package tersebut. Package adalah sebuah paket atau kumpulan procedure dan function yang berguna untuk menyelesaikan masalah-masalah dalam blok PL/SQL. Sekarang coba anda tuliskan sintak berikut ini pada SQL*Plus. Latihan : SQL> 2 BEGIN DBMS_OUTPUT.PUT_LINE (‘Belajar Oracle…’); 3 END; 4 / kemudian tuliskan : SQL> SET SERVEROUTPUT ON SQL> / SET SERVEROUTPUT ON berfungsi untuk melihat hasil dari blok PL/SQL yang dibuat. Latihan : SQL> SET SERVEROUTPUT ON SQL> DECLARE 2 teks varchar2 (20); 3 BEGIN 4 teks := ‘Belajar Oracle’; 5 DBMS_OUTPUT.PUT_LINE (teks); 6 END; 7 / Pada latihan diatas, pertama kali anda mengeset variable system SERVEROUTPUT ke keadaan ON, lalu memasukkan deklarasi dan di sana dideklarasikan sebuah variable dengan nama teks yang bertipe VARCHAR2. pada bagian utama blok PL/SQL terdapat proses penyimpanan nilai ‘Belajar Oracle’ ke dalam variable teks, sehingga variable tersebut dapat kita panggil oleh procedure PUT_LINE. Latihan : Eksepsi SQL> SET SERVEROUTPUT ON SQL> DECLARE 2 X integer; 3 BEGIN 4 X := ‘Belajar Oracle’; 5 DBMS_OUTPUT.PUT_LINE (TO_CHAR(X) ); 6 EXCEPTION 7 WHEN VALUE_ERROR THEN 8 DBMS_OUTPUT.PUT_LINE (‘Kesalahan pada Pengisian nilai’); 9 END; 10 / Kesalahan dari sintaks di atas yaitu pada penyimpanan nilai ke dalam variable karena tipe data dari nilai yang dimasukkan tidak sama dengan tipe data variable yang terdapat pada bagian deklarasi. Untuk mencegah terjadinya pesan error dari Oracle, kita mendefinisikan sendiri sebuah pesan (message). D. Operator Oracle menyediakan beberapa jenis operator untuk menyelesaikan persoalanpersoalan pada database yang diolah. Sebelum mempelajari lebih dalam tentang operator, anda harus akrab dengan istilah-istilah yang berkaitan dengan program. Misalnya didalam blok PL/SQL terdapat sebuah persamaan matematika sebagai berikut : Z := 3 + 6; Maka : Z := 3 dan 6 3+6 + Z := 3 + 6 disebut variable disebut operator assignment disebut operand disebut ekspresi disebut operator aritmatika disebut statement aritmatika 1. Operator Logika Dalam Oracle terdapat tiga operator logika, yaitu NOT, AND, dan OR. Operator NOT Operator NOT tidak lain adalah negasi sari suatu nilai. Jika kita mempunyai sebuah variable X yang bertipe Boolean, hasil operasi yang menggunakan operator NOT dapat ditampilkan seperti berikut : X NOT X TRUE FALSE FALSE TRUE Operator AND Operator AND digunakan untuk menghasilkan sebuah nilai yang berasal dari dua buah nilai atau lebih yang masing-masing bernilai Boolean. Jika anda menmpunyai dua variable X dan Y, hasil penggunaan operator AND dapat dilihat sebagai berikut : X Y X AND Y TRUE TRUE TRUE TRUE FALSE FALSE FALSE TRUE FALSE FALSE FALSE FALSE Operator OR Operator OR melibatkan dua buah nilai atau lebih, penggunaan operator OR akan menghasilkan nilai TRUE jika minimal satu dari operand-nya bernilai TRUE. Untuk lebih jelasnya lihat tabel berikut : X Y X AND Y TRUE TRUE TRUE TRUE FALSE TRUE FALSE TRUE TRUE FALSE FALSE FALSE 2. Operator Aritmatika Operator aritmatika yang termasuk dalam Oracle ditunjukkan pada tabel berikut : Operator Keterangan Contoh + Penjumlahan 2+3=5 - Pengurangan 5–3=2 * Perkalian 5 * 2 = 10 / Pembagian 10 / 3 = 3.3333 Sisa bagi 10 MOD 3 = 1 MOD a. Contoh penggunaan Operator + Latihan : SQL> SET SERVEROUTPUT ON SQL> DECLARE 2 X INTEGER; 3 Y NUMBER; 4 BEGIN 5 X := 2 + 3; 6 Y := 2.45 + 3.14; 7 DBMS_OUTPUT.PUT_LINE (TO_CHAR(X) ); 8 DBMS_OUTPUT.PUT_LINE (TO_CHAR(Y) ); 9 END; 10 / b. Contoh penggunaan Operator – Latihan : SQL> SET SERVEROUTPUT ON SQL> DECLARE 2 X INTEGER; 3 Y NUMBER; 4 BEGIN 5 X := 5 - 3; -- pengurangan bilangan bulat 6 Y := 6.88 - 2.73; -- pengurangan bilangan desimal 7 DBMS_OUTPUT.PUT_LINE (TO_CHAR(X)); 8 DBMS_OUTPUT.PUT_LINE (TO_CHAR(Y)); 9 END; 10 / c. Contoh penggunaan Operator * Latihan : SQL> SET SERVEROUTPUT ON SQL> DECLARE 2 X INTEGER; 3 Y NUMBER; 4 BEGIN 5 X := 5 * 2; 6 Y := 6.13 * 2; 7 DBMS_OUTPUT.PUT_LINE (TO_CHAR(X)); 8 DBMS_OUTPUT.PUT_LINE (TO_CHAR(Y)); 9 END; 10 / d. Contoh penggunaan Operator / Latihan : SQL> SET SERVEROUTPUT ON SQL> DECLARE 2 3 X NUMBER; BEGIN 4 X := 10 / 3; 5 DBMS_OUTPUT.PUT_LINE (TO_CHAR(X)); 6 END; 7 / e. Contoh penggunaan Operator MOD Latihan : SQL> SET SERVEROUTPUT ON SQL> DECLARE 2 3 X INTEGER; BEGIN 4 X := 10 MOD 3; 5 DBMS_OUTPUT.PUT_LINE (TO_CHAR(X)); 6 END; 7 / 3. Operator Relasional Operator relasional adalah operator yang digunakan untuk menyatakan hubungan antara dua nilai atau ekspresi dan akan menghasilkan nilai Boolean (TRUE atau FALSE). Adapaun yang termasuk dalam operator relasional adalah sebagai berikut : Operator Keterangan < Lebih kecil <= Lebih kecil atau sama dengan > Lebih besar >= Lebih besar atau sama dengan Contoh : 2 < 5 nilai yang dihasilkan TRUE 5 <= 5 nilai yang dihasilkan TRUE 2 > 5 nilai yang dihasilkan FALSE 2 >= 10 nilai yang dihasilkan FALSE 4. Operator Persamaan Operator persamaan digunakan untuk melakukan pengecekan terhadap dua nilai atau ekspresi yang terdapat pada dua ruas yaitu ruas kanan dan ruas kiri. Berikut ini yang termasuk dalam operator persamaan : Operator = <> Keterangan Sama dengan Tidak sama dengan Latihan : SQL> SET SERVEROUTPUT ON SQL> DECLARE 2 3 X INTEGER; BEGIN 4 X := 10; 5 IF (X = 10) THEN 6 DBMS_OUTPUT.PUT_LINE ('Sepuluh'); 7 ELSE 8 DBMS_OUTPUT.PUT_LINE ('Bukan Sepuluh'); 9 END IF; 10 END; 11 / 5. Operator Penggabungan Oracle tersedia sebuah operator untuk melakukan operasi penggabungan (concatenation) dua atau lebih data yang bertipe teks (CHAR atau VARCHAR2), yaitu : operator || Contoh : X := ‘Budi’ || ‘Raharjo’; sama dengan X := ‘BudiRaharjo’; X := ‘Budi’ || ‘ ‘ || ‘Raharjo’; sama dengan X := ‘Budi Raharjo’; Latihan : SQL> SET SERVEROUTPUT ON SQL> DECLARE 2 X NUMBER; 3 S VARCHAR2 (25); 4 BEGIN 5 X := 15.2 + 4.63; 6 S := 'Nilai X sama dengan’; 7 DBMS_OUTPUT.PUT_LINE (S || TO_CHAR (X) ); 8 END; 9 / Tugas 2 : 1. Tulislah sebuah blok PL/SQL sederhana sehingga pada layar SQL*Plus akan menampilkan teks BELAJAR ORACLE DI LABORATORIUM KOMPUTER III 2. Jelaskan mengapa blok PL/SQL berikut mengalami error : DECLARE X CONSTANT INTEGER := 100 BEGIN X := 50; DBMS_OUTPUT.PUT_LINE (TO_CHAR(X) ); END; / 3. Jika terdapat tiga variable yaitu X (diisi dengan nilai 13), Y (diisi dengan nilai 12), Z sebagai penampung hasil operasi-operasi aritmatika antara X dan Y, buatlah sebuah blok PL/SQL yang hasilnya sebagai berikut : Hasil penjumlahan antara X dan Y = 25 Hasil pengurangan antara X dan Y = 1 Hasil perkalian antara X dan Y = 156 BAB III EKSEPSI DAN PERCABANGAN A. Tujuan Praktikum Mahasiswa memahami dan mengerti mengenai Eksepsi dan Percabangan. B. Eksepsi Tujuan utama dibuat blok Eksepsi adalah untuk menangani error-error yang telah didefinisikan menjadi sebuah eksepsi, sehingga meskipun dalam blok PL/SQL terdapat error, error tersebut tidak akan ditampilkan melainkan dilempar ke bagian eksepsi. Hal ini menyebabkan blok PL/SQL akan terhenti normal dengan penampilan pesan-pesan error yang kita definisikan sendiri seakan-akan tidak terjadi error. Eksepsi terbagi menjadi dua jenis, yaitu : 1 Pre-defined Exception Pre-defined Exception adalah sebutan bagi eksepsi-eksepsi yang telah didefinisikan atau sudah tersedia dalam Oracle, sehingga dapat langsung digunakan tanpa harus membuatnya terlebih dahulu. Adapun daftar nama eksepsi yang sudah tersedia dalam Oracle (pre-defined exception) seperti berikut ini : Nama Eksepsi Kode Error Keterangan CURSOR_ALREADY_OPEN ORA-6511 Cursor masih dalam keadaan terbuka DUP_VALUE_ON_INDEX ORA-0001 Duplikasi constraint INVALID_CURSOR ORA-1001 Membuka cursor yang salah ORA-1722 Melakukan operasi numeric pada tipe INVALID_NUMBER non-numerik LOGIN_DENIED ORA-1017 Username / password salah NO_DATA_FOUND ORA-1403 Tidak terdapat data pada query NO_LOGGED_ON ORA-1012 Melakukan operasi database ketika tidak terhubung (connect) PROGRAM_ERROR ORA-6501 Kesalahan internal ROWTYPE_MISMATCH ORA-6504 Host variable dan cursor mempunyai tipe yang tidak sama STORAGE_ERROR ORA-6500 Kesalahan internal TYMEOUT_ON_RESOURCE ORA-0051 Terjadi timeout TOO_MANY_ROWS ORA-1422 Statemen SELECT INTO yang mengembalikan lebih dari satu baris TRANSACTION_BACK_ED_OUT ORA-006 Rollback transaksi untuk didealokasikan VALUE_ERROR ORA-6502 Nama Eksepsi ZERO_DIVIDE Kode Error ORA-1476 Konversi atau truncation salah Keterangan Pembagian dengan nol Latihan : Tanpa Blok Eksepsi : SQL> SET SERVEROUTPUT ON SQL> DECLARE 2 X 3 Y NUMBER; 4 INTEGER; BEGIN 5 X := 0; 6 Y := 1 / X; 7 DBMS_OUTPUT.PUT_LINE ('Nilai Y ='|| TO_CHAR(Y) ); 8 END; 9 / DECLARE * ERROR at line 1: ORA-01476: divisor is equal to zero ORA-06512: at line 6 Dari hasil yang diberikan diatas jelas terlihat bahwa blok PL/SQL sama sekali tidak dapat dijalankan karena terdapat error pada baris ke-6, yaitu error dengan kode ORA-01476 (‘divisor is equal to zero’). Dengan kata lain blok PL/SQL dihentikan (terminated) secara tidak normal. Menggunakan Blok Eksepsi : SQL> SET SERVEROUTPUT ON SQL> DECLARE 2 X INTEGER; 3 4 Y NUMBER; BEGIN 5 X := 0; 6 Y := 1 / X; 7 DBMS_OUTPUT.PUT_LINE ('Nilai Y =' || TO_CHAR (Y) ); 8 EXCEPTION 9 WHEN ZERO_DIVIDE THEN 10 DBMS_OUTPUT.PUT_LINE ('Terjadi kesalahan karena terdapat' ||``|| 11 'pembagian dengan 0 (NOL)'); 12 END; 13 / Terjadi kesalahan karena terdapat pembagian dengan 0 (NOL) PL/SQL procedure successfully completed. Pada blok PL/SQL tersebut mula-mula dieksekusi statemen yang terdapat pada baris ke-1 sampai ke-5. Namun dengan adanya blok eksepsi, setelah menemukan error pada baris ke-6 program akan meloncat ke blok eksepsi yaitu dengan mengeksekusi statemen yang didefinisikan untuk nama eksepsi yang sesuai (baris ke-10 dan ke-11). Jadi, statemen baris ke-7 tidak akan pernah dieksekusi. Selain itu blok PL/SQL dapat berjalan dengan baik atau normal seakan-akan tidak terdapat error didalamnya. Berikut ini akan diterapkan tentang eksepsi VALUE_ERROR dengan mengambil masalah proses memasukkan teks ke variable yang bertipe numeric (NUMBER). Latihan : Tanpa Blok Eksepsi : SQL> SET SERVEROUTPUT ON SQL> DECLARE 2 3 X NUMBER; BEGIN 4 X:='Laboratorium Komputer III'; 5 DBMS_OUTPUT.PUT_LINE ('Nilai X='|| TO_CHAR(X)); 6 END; 7 / DECLARE * ERROR at line 1: ORA-06502: PL/SQL: numeric or value error: character to number conversion error ORA-06512: at line 4 Error yang terjadi diatas disebabkan oleh pengisian nilai terhadap variable X (baris ke-4), yaitu yang seharusnya bertipe numeric (NUMBER) tetapi diisi dengan nilai yang bertipe karakter (CHAR atau VARCHAR2). Menggunakan Blok Eksepsi SQL> SET SERVEROUTPUT ON SQL> DECLARE 2 3 X NUMBER; BEGIN 4 X := 'Laboratorium Komputer III'; 5 DBMS_OUTPUT.PUT_LINE ('Nilai X='|| TO_CHAR(X)); 6 EXCEPTION 7 WHEN VALUE_ERROR THEN 8 DBMS_OUTPUT.PUT_LINE ('Pengisian nilai tidak sesuai dengan'|| 9 'tipe variabel'); 10 END; 11 / Pengisian nilai tidak sesuai dengantipe variabel PL/SQL procedure successfully completed. 2. User-defined Exception Anda dapat membuat eksepsi sendiri sesuai keperluan atau kasus yang anda hadapi, eksepsi semacam ini disebut user-defined exception. Untuk membuat sebuah eksepsi, anda harus mendeklarasikan sebuah variable yang bertipe EXCEPTION sebagai nama eksepsi dan dihubungkan dengan kode error dari Oracle dengan cara menggunakan statemen PRAGMA EXCEPTION_INIT pada bagian deklarasi dari sebuah blok PL/SQL. Adapun sintak umum untuk pendeklarasiannya sebagai berikut : DECLARE Nama_eksepsi EXCEPTION; PRAGMA EXCEPTION_INIT (nama_eksepsi, kode_error); Perlu diperhatikan bahwa kode_error harus negative atau menambahkan tanda minus (-) di depan kode_error tersebut. Latihan : SQL> SET SERVEROUTPUT ON SQL> DECLARE 2 eksepsiku EXCEPTION; 3 PRAGMA EXCEPTION_INIT (eksepsiku,-01445); 4 X ROWID; 5 6 7 8 BEGIN SELECT ROWID INTO X FROM ALL_VIEWS; EXCEPTION WHEN eksepsiku THEN 9 DBMS_OUTPUT.PUT_LINE ('KESALAHAN : Tidak dapat' || 10 'Menampilkan ROWID dari beberapa tabel atau view'); 11 END; 12 / KESALAHAN : Tidak dapatMenampilkan ROWID dari beberapa tabel atau view PL/SQL procedure successfully completed. C. Percabangan Percabangan adalah sebuah pemilihan yang didasarkan atas kondisi tertentu. Sebuah atau beberapa statemen yang terdapat pada badan percabangan hanya akan dieksekusi jika kondisi yang didefinisikan bernilai TRUE. Sebaliknya jika kondisi tersebut bernilai FALSE, badan percabangan tidak akan pernah dieksekusi karena semua kondisi yang terdapat pada badan percabangan selalu diperiksa pada saat awal. Oracle sebuah badan percabangan secara umum dibedakan menjadi 3 struktur, yaitu : 1. Struktur Satu Kondisi Dalam struktur percabangan yang hanya mempunyai satu kondisi ini jika kondisi tidak terpenuhi atau bernilai FALSE, badan percabangan tidak akan pernah dimasuki. Sintak umum untuk pembuatan sebuah struktur percabangan dengan satu kondisi adalah : IF kondisi THEN Statemen_1; … END IF; Latihan : Buatlah sebuah blok PL/SQL yang dapat menentukan sebuah bilangan bulat apakah termasuk ke dalam bilangan genap atau tidak. Jika ya, tampilkanlah bilangan tersebut dan diikuti dengan teks ADALAH BILANGAN GENAP. Jawaban : SQL> SET SERVEROUTPUT ON SQL> DECLARE 2 3 BIL INTEGER := 80; -- 80 adalah contoh bilangan yang dicek BEGIN 4 IF MOD(BIL, 2) = 0 THEN 5 DBMS_OUTPUT.PUT_LINE (TO_CHAR(BIL) || 6 'ADALAH BILANGAN GENAP'); 7 END IF; 8 END; 9 / 80ADALAH BILANGAN GENAP PL/SQL procedure successfully completed. Pada sintak diatas terdapat adanya function MOD yang mengembalikan nilai sisa bagi dari proses pembagian bilangan BIL bagi 2. Jika sisa baginya sama dengan 0, artinya BIL habis dibagi 2 yang berarti pula bahwa bilangan tersebut merupakan bilangan genap. Karena 80 habis dibagi 2, statemen-statemen yang terdapat pada badan percabangan akan dieksekusi. Sebaliknya jika bilangan 80 diganti dengan 39 yang mempunyai sisa 1 jika dibagi 2, maka program tidak dapat memasuki badan percabangan dan statemenstatemen yang ada sudah tentu tidak akan dieksekusi. Latihan : SQL> SET SERVEROUTPUT ON SQL> DECLARE 2 3 BIL INTEGER := 39; BEGIN 4 IF MOD (BIL,2) = 0 THEN 5 DBMS_OUTPUT.PUT_LINE (TO_CHAR(BIL) || 6 'ADALAH BILANGAN GENAP'); 7 END IF; 8 END; 9 / PL/SQL procedure successfully completed. 2. Struktur Dua Kondisi Pada struktur dua kondisi ini, statemen-statemen yang akan dieksekusi adalah statemen yang didasarkan pada kondisi yang nilainya TRUE. Artinya, tidak mungkin terdapat dua kondisi yang keduanya bernilai TRUE. Sintak umum badan percabangan yang mempunyai dua kondisi adalah sebagai berikut : IF kondisi THEN Statemen_1; … ELSE Statemen_2; … END IF; Sintak diatas berarti bahwa program akan mengeksekusi Statemen_1 jika kondisi terpenuhi, tetapi jika tidak terpenuhi maka yang akan dieksekusi adalah statemen_2. Latihan : Buatlah sebuah blok PL/SQL yang dapat menentukan bilangan bulat apakah termasuk bilangan genap atau bilangan ganjil. Kemudian tampilkan bilangan tersebut dengan diikuti teks ADALAH BILANGAN GENAP (jika bilangan tersebut habis dibagi 2) dan jika (tidak habis dibagi 2 ) maka teks yang ditampilkan ADALAH BILANGAN GANJIL. Jawab : SQL> SET SERVEROUTPUT ON SQL> DECLARE 2 3 BIL INTEGER := 3; BEGIN 4 IF MOD (BIL,2) = 0 THEN 5 DBMS_OUTPUT.PUT_LINE (TO_CHAR(BIL)|| 6 'ADALAH BULANGAN GENAP'); 7 ELSE 8 DBMS_OUTPUT.PUT_LINE (TO_CHAR(BIL)|| 9 'ADALAH BILANGAN GANJIL'); 10 END IF; 11 END; 12 / 3ADALAH BILANGAN GANJIL PL/SQL procedure successfully completed. 3. Struktur Tiga Kondisi atau Lebih Sintak umum untuk pembuatan badan percabangan yang mempunyai tiga kondisi atau lebih dapat anda lihat seperti berikut : IF kondisi_1 THEN Statemen_1; … ELSIF kondisi_2 THEN Statemen_2; … ELSE Statemen_3; … END IF; Sintak diatas menunjukkan bahwa mula-mula dilakukan pengecekan nilai terhadap kondisi_1. Jika kondisi_1 bernilai TRUE, statemen_1 yang akan dieksekusi. Tetapi jika tidak, akan dilakukan pengecekan terhadap kondisi_2. Jika kondisi_2 bernilai TRUE, statemen_2 yang akan dieksekusi, tetapi jika kondisi_2 juga masih bernilai FALSE, berarti yang akan dieksekusi adalah statemen_3. Latihan : SQL> SET SERVEROUTPUT ON SQL> DECLARE 2 3 X BEGIN 4 5 INTEGER; X := -25; IF X >0 THEN 6 DBMS_OUTPUT.PUT_LINE (TO_CHAR(X) || 'ADALAH BILANGAN POSITIF'); 7 ELSIF X = 0 THEN 8 9 DBMS_OUTPUT.PUT_LINE ('BILANGAN NOL'); ELSE 10 DBMS_OUTPUT.PUT_LINE (TO_CHAR(X) || 'ADALAH BILANGAN NEGATIF'); 11 END IF; 12 END; 13 / -25 ADALAH BILANGAN NEGATIF PL/SQL procedure successfully completed. Pada sintak diatas, nilai X yang didefinisikan adalah -25. Mula-mula akan diperiksa kondisi X > 0. Karena -25 tidak lebih besar dari 0, maka dilakukan pengecekan terhadap kondisi berikutnya yaitu X = o. Oleh karena -25 juga tidak sama dengan 0, statemen yang dieksekusi adalah statemen yang ‘tidak’ didasarkan atas dua kondisi tersebut. Latihan : Buatlah sebuah blok PL/SQL untuk menentukan nama hari dari nomor/indeks hari yang diberikan. Jawab : SQL> SET SERVEROUTPUT ON SQL> DECLARE 2 K INTEGER := 2; 3 HARI VARCHAR2 (6); 4 5 6 7 8 9 10 11 12 BEGIN IF K = 1 THEN HARI := 'MINGGU'; ELSIF K = 2 THEN HARI := 'SENIN'; ELSIF K = 3 THEN HARI := 'SELASA'; ELSIF K = 4 THEN HARI := 'RABU'; 13 ELSIF K = 5 THEN 14 15 HARI := 'KAMIS'; ELSIF K = 6 THEN 16 17 HARI := 'JUMAT'; ELSE 18 19 HARI := 'SABTU'; END IF; 20 DBMS_OUTPUT.PUT_LINE ('Hari ke-' || TO_CHAR (K) || 21 ' Adalah ' || INITCAP (HARI)); 22 END; 23 / Hari ke-2 Adalah Senin Tugas 3 : 1. Buatlah eksepsi dengan kode_error ORA-3434. 2. Buatlah sebuah blok PL/SQL yang dapat memberikan indeks/nomor urut bulan jika yang dimasukkan adalah nama bulannya. Misalnya, kita masukkan nama bulan MARET, yang akan dihasilkan dari blok PL/SQL tersebut adalah teks ‘Bulan Maret adalah bulan ke-3’. BAB IV PENGULANGAN DAN ARRAY A. Tujuan Praktikum Mahasiswa memahami dan mengerti mengenai Pengulangan dan Array. B. Pengulangan Dalam Oracle terdapat tiga macam struktur pengulangan, yaitu : 1. Struktur Simple Loop Simple Loop ini digunakan untuk melakukan pengulangan statemen dalam blok PL/SQL dimana pada saat akan masuk ke badan pengulangan tidak diperlukan adanya kondisi awal yang harus diperiksa terlebih dahulu. Pengecekan kondisi baru akan dilakukan pada saat program selesai mengeksekusi statemen-statemen yang didefinisikan dalam badan pengulangan sehingga pada pengulangan jenis ini statemen-statemen yang terdapat pada badan pengulangan minimal akan dikerjakan 1 kali. Selama kondisi yang didefinisikan masih bernilai FALSE atau belum terpenuhi, proses pengulangan akan terus dilakukan. Kondisi ini diperlukan untuk menjalankan statemen EXIT yang berfungsi untuk mengeluarkan atau menghentikan program dari proses pengulangan. Tanpa adanya statemen EXIT dalam badan pengulangan jenis ini, maka proses pengulangan tidak pernah akan berhenti sehingga jelas akan terjadi kesalahan pada program atau blok PL/SQL yang anda buat. Berikut ini sintak umum untuk badan pengulangan : LOOP Statemen_1; … EXIT WHEN kondisi; END LOOP; Latihan : Buatlah sebuah blok PL/SQL yang dapat menampilkan teks ‘Saya belajar PL/SQL’ sebanyak 10 kali. Jawaban : SQL> SET SERVEROUTPUT ON SQL> DECLARE 2 3 4 5 J INTEGER; BEGIN J := 0; LOOP 6 J := J + 1; 7 DBMS_OUTPUT.PUT_LINE ('Saya belajar PL/SQL'); 8 EXIT WHEN J = 10; 9 END LOOP; 10 END; 11 / Latihan : Buatlah blok PL/SQL untuk menghitung hasil dari penjumlahan bilanganbilangan yang terdapat pada deret berikut : 2 + 4 + 6 + … + 18 Jawab : SQL> SET SERVEROUTPUT ON SQL> DECLARE 2 K INTEGER; 3 JUMLAH INTEGER := 0; 4 BEGIN 5 6 K := 2; LOOP 7 JUMLAH := JUMLAH + K; 8 K := K + 2; 9 EXIT WHEN K > 18; 10 END LOOP; 11 DBMS_OUTPUT.PUT_LINE ('Jumlah = ' || JUMLAH); 12 END; 13 / 2. Struktur WHILE-LOOP Pada struktur WHILE-LOOP ini proses pengulangan dapat memasuki badan pengulangan hanya bernilai benar. Jika ternyata kondisi tidak terpenuhi (FALSE), badan pengulangan tidak pernah dimasuki. Sintak umum dari badan pengulangan jenis WHILE-LOOP ini ditunjukkan sebagai berikut : WHILE kondisi LOOP Statemen_1; … END LOOP; Latihan : Buatlah sebuah blok PL/SQL yang dapat menampilkan teks ‘Saya belajar PL/SQL’ sebanyak 10 kali, tetapi gunakan struktur pengulangan WHILE-LOOP. Jawaban : SQL> SET SERVEROUTPUT ON SQL> DECLARE 2 3 J INTEGER; BEGIN 4 5 J := 1; WHILE (J <= 10) LOOP 6 DBMS_OUTPUT.PUT_LINE ('Saya belajar PL/SQL'); 7 J := J + 1; 8 END LOOP; 9 END; 10 / Latihan : Buatlah sebuah blok PL/SQL yang dapat menghitung nilai perpangkatan dari 26. Jawaban : SQL> SET SERVEROUTPUT ON SQL> DECLARE 2 BIL INTEGER := 2; 3 HASIL INTEGER := 1; 4 K INTEGER; 5 BEGIN 6 7 K := 0; WHILE K < 6 LOOP 8 HASIL := HASIL * BIL; 9 K := K + 1; 10 END LOOP; 11 DBMS_OUTPUT.PUT_LINE ('Hasil dari 2^6 = ' || HASIL); 12 END; 13 / 3. Struktur FOR-LOOP Struktur pengulangan FOR-LOOP adalah struktur pengulangan yang tidak mengandung kondisi yang harus diperiksa di dalamnya sehingga badan pengulangan pasti akan dimasuki.pada struktur FOR-LOOP ini batas-batas nilai yang akan digunakan untuk menentukan banyaknya proses pengulangan harus didefinisikan terlebih dahulu sehingga struktur pengulangan jenis ini lebih sering digunakan untuk melakukan proses pengulangan yang sudah dipastikan banyaknya. Sintak umum pembuatan struktur pengulangan FOR-LOOP adalah seperti berikut : FOR variabel IN batas_minimal..batas_maksimal LOOP Statemen_1; … END LOOP; Latihan : Buatlah sebuah blok PL/SQL yang dapat menampilkan bilangan dari 1 sampai 10. Jawaban : SQL> SET SERVEROUTPUT ON SQL> DECLARE 2 3 I INTEGER; BEGIN 4 FOR I IN 1..10 LOOP 5 DBMS_OUTPUT.PUT_LINE (TO_CHAR(I)); 6 END LOOP; 7 END; 8 / Latihan : Buatlah blok PL/SQL untuk menentukan perhitungan factorial dari sebuah bilangan 5! = 120. Jawab : SQL> SET SERVEROUTPUT ON SQL> DECLARE 2 BIL INTEGER := 5; 3 I INTEGER; 4 HASIL INTEGER := 1; 5 BEGIN 6 FOR I IN REVERSE 1..BIL LOOP 7 HASIL := HASIL * I; 8 END LOOP; 9 DBMS_OUTPUT.PUT_LINE ('Hasil dari 5! = ' || HASIL); 10 END; 11 / C. ARRAY Array adalah tipe data yang menyimpan nilai-nilai sejenis. Dengan adanya array secara otomatis akan menghemat terjadinya pemesanan memori computer untuk penempatan variabel-variabel yang tidak perlu. Sintak umum untuk pembuatan Array adalah sebagai berikut : TYPE nama_tipe IS TABLE OF tipe_data INDEX BY BINARY_INTEGER; Latihan : 1. Tanpa Array SQL> SET SERVEROUTPUT ON SQL> DECLARE 2 X1 NUMBER; 3 X2 NUMBER; 4 X3 NUMBER; 5 X4 NUMBER; 6 X5 NUMBER; 7 BEGIN 8 X1 := 2; 9 X2 := 4; 10 X3 := 6; 11 X4 := 8; 12 X5 := 10; 13 DBMS_OUTPUT.PUT_LINE (TO_CHAR(X1)); 14 DBMS_OUTPUT.PUT_LINE (TO_CHAR(X2)); 15 DBMS_OUTPUT.PUT_LINE (TO_CHAR(X3)); 16 DBMS_OUTPUT.PUT_LINE (TO_CHAR(X4)); 17 DBMS_OUTPUT.PUT_LINE (TO_CHAR(X5)); 18 END; 19 / 2. Menggunakan Array SQL> SET SERVEROUTPUT ON SQL> DECLARE 2 TYPE LARIK IS 3 TABLE OF NUMBER 4 INDEX BY BINARY_INTEGER; 5 X LARIK; 6 I INTEGER; 7 BEGIN 8 FOR I IN 1..5 LOOP 9 X (I) := I * 2; 10 END LOOP; 11 FOR I IN 1..5 LOOP 12 DBMS_OUTPUT.PUT_LINE (TO_CHAR (X (I) ) ); 13 END LOOP; 14 END; 15 / Tugas 4 : 1. Buatlah sebuah blok PL/SQL yang dapat mengecek sebuah bilangan bulat apakah termasuk bilangan prima atau bukan dengan menerapkan konsep pengulangan. 2. Buatlah sebuah blok PL/SQL tipe data array dengan nama MAHASISWA yang berupa kumpulan dari tipe VARCHAR2. (minimal 5 nama mahasiswa) BAB V PROCEDURE DAN FUNCTION A. Tujuan Praktikum Mahasiswa memahami dan mengerti mengenai Procedure dan Function. B. Procedure Procedure adalah sebuah blok PL/SQL yang dapat berdiri sendiri serta dikompilasi untuk selanjutnya masuk ke dalam skema database. Nama procedure yang dibuat kemudian menjadi objek dengan tipe procedure. Procedure akan dieksekusi pada saat pemanggilan setelah sebelumnya dibuat terlebih dahulu. Sintak umum procedure adalah seperti berikut ini : CREATOR OR REPLACE PROCEDURE nama_procedure (parameter_1 tipe_data,…) AS variabel_1 tipe_data; … BEGIN Statemen_1; … END; Adapun sintak umum untuk mengeksekusi sebuah procedure adalah seperti berikut : EXECUTE nama_procedure(parameter_1,…); Statemen CREATE digunakan untuk membuat procedure baru, sedangkan REPLACE digunakan untuk mengganti isi procedure yang telah dibuat sebelumnya. Parameter dan variabel-variabel atau konstanta hanya bersifat opsional, artinya tidak harus dituliskan pada setiap pembuatan procedure. 1. Procedure dengan Parameter Procedure dengan parameter mempunyai sifat dinamis karena pada saat pemanggilan/pengeksekusian procedure tersebut nilai parameternya dapat berubah-ubah, sehingga hasil yang diberikan dengan sendirinya akan berubah. Adapun jenis-jenis parameter sebagai berikut : a. Parameter Masukan Parameter masukan berarti parameter yang dideklarasikan bertindak sebagai masukan (input) di dalam badan procedure. Parameter masukan ditandai dengan kata IN. Latihan : Buatlah blok PL/SQL menggunakan procedure yang dapat menambahkan sebuah bilangan bulat dengan nilai 2. Jawab : SQL> SET SERVEROUTPUT ON SQL> CREATE OR REPLACE PROCEDURE tambah_dua (a IN INTEGER) AS 2 3 hasil INTEGER(5); BEGIN 4 hasil := a+2; 5 DBMS_OUTPUT.PUT_LINE ('Hasil akhir = ' || hasil); 6 c 7 / kemudian untuk mengeksekusi silahkan anda tuliskan : SQL> EXECUTE tambah_dua(4); Latihan : Buatlah procedure yang dapat menentukan hasil perhitungan factorial dari sebuah bilangan bulat positif dimana bilangan tersebut dijadikan parameter masukan. Jawab: SQL> SET SERVEROUTPUT ON SQL> CREATE OR REPLACE PROCEDURE hitung_faktorial (X INTEGER) AS 2 hasil INTEGER(5); 3 J 4 BEGIN INTEGER(3); 5 hasil := 1; 6 J 7 IF X = 0 THEN 8 hasil := 1; 9 := 1; ELSE 10 WHILE J <= X LOOP 11 hasil := hasil * J; 12 J 13 END LOOP; 14 END IF; 15 := J + 1; DBMS_OUTPUT.PUT_LINE (X || '! = ' || TO_CHAR(hasil)); 16 END; 17 / Procedure created. SQL> EXECUTE hitung_faktorial(5); 5! = 120 IN b. Parameter Keluaran Parameter keluaran berfungsi menampung hasil pelaksanaan suatu procedure. Parameter actual dari parameter yang bertipe keluaran ini akan berbentuk variabel, bukan berbentuk nilai. Parameter yang berjenis keluaran ini akan diikuti oleh kata OUT. Latihan : Hitunglah luas segitiga dengan menggunakan procedure dimana alas dan tinggi segitiga tersebut dijadikan sebagai parameter masukan serta luasnya dijadikan sebagai parameter keluaran. Jawab: SQL> CREATE OR REPLACE PROCEDURE hitung_luas_segitiga ( 2 a IN NUMBER, 3 t NUMBER, 4 L OUT NUMBER) AS 5 IN BEGIN 6 L := (a * t) / 2; 7 END; 8 / kemudian tuliskan lagi sintak berikut : SQL> SET SERVEROUTPUT ON SQL> DECLARE 2 3 LUAS NUMBER; BEGIN 4 hitung_luas_segitiga (6, 8, LUAS); 5 DBMS_OUTPUT.PUT_LINE ('Luas segitiga = ' || TO_CHAR (LUAS) ); 6 END; 7 / Luas segitiga = 24 Latihan : Buatlha procedure yang dapat menentukan hasil dari perpangkatan bilangan bulat dimana bilangan yang akan dipangkatkan dan bilangan pemangkat dimasukkan sebagai parameter masukan sedangkan hasilnya sebagai parameter keluaran. Jawab: SQL> CREATE OR REPLACE PROCEDURE pangkat ( 2 bil IN INTEGER, 3 pemangkat IN INTEGER, 4 hasil OUT INTEGER) AS 5 J INTEGER; 6 pkt INTEGER; 7 BEGIN 8 pkt := 1; 9 FOR J IN 1..pemangkat LOOP 10 pkt := pkt * bil; 11 END LOOP; 12 hasil := pkt; 13 END; 14 / kemudian tuliskan lagi sintak berikut : SQL> SET SERVEROUTPUT ON SQL> DECLARE 2 3 H INTEGER; BEGIN 4 pangkat (2, 5, H); 5 DBMS_OUTPUT.PUT_LINE ('Hasil perpangkatan = ' || TO_CHAR(H)); 6 END; 7 / Hasil perpangkatan = 32 c. Parameter Masukan/Keluaran Parameter masukan/keluaran adalah gabungan dari tipe parameter masukan dan keluaran. Artinya selain berfungsi sebagai masukan (input), parameter masukan/keluaran ini juga digunakan sebagai keluaran (output). Parameter yang berjenis masukan/keluaran ini ditandai dengan kata IN OUT. Latihan : Buatlah procedure yang dapat menjumlahan sebuah bilangan bulat dengan nilai 10 dimana didalamnya menggunakan parameter masukan/keluaran. Jawab: SQL> CREATE OR REPLACE PROCEDURE tambah_10 (X IN OUT INTEGER) AS 2 BEGIN 3 X := X + 10; 4 END; 5 / kemudian tuliskan kembali sintak berikut : SQL> SET SERVEROUTPUT ON SQL> DECLARE 2 3 Y INTEGER; BEGIN 4 Y := 15; 5 tambah_10(Y); 6 DBMS_OUTPUT.PUT_LINE TO_CHAR(Y) ); ('Hasil penambahan = ' || 7 END; 8 / Hasil penambahan = 25 Latihan : Buatlah sebuah procedure yang dapat menentukan keliling lingkaran dengan menggunakan parameter masukan/keluaran dimana jari-jari lingkaran diperlakukan sebagai masukan dan keluarannya adalah keliling lingkaran. (Rumus keliling lingkaran : K = 2 πr, dimana π = 3.14) Jawab : SQL> CREATE OR REPLACE PROCEDURE keliling_lingkaran (R IN OUT INTEGER) 2 BEGIN 3 R := 2 * 3.14 * R; 4 END; 5 / kemudian tuliskan sintak berikutnya : SQL> SET SERVEROUTPUT ON SQL> DECLARE 2 3 BIL INTEGER; BEGIN 4 BIL := 4; 5 DBMS_OUTPUT.PUT_LINE ('Jari-jari lingkaran : ' || TO_CHAR (BIL) ); 6 keliling_lingkaran(BIL); 7 DBMS_OUTPUT.PUT_LINE ('Keliling Lingkaran : ' || TO_CHAR (BIL) ); 8 END; 9 / Jari-jari lingkaran : 4 Keliling Lingkaran : 25 2. PROCEDURE didalam PROCEDURE Tanpa disadari kita serng menggunakan procedure di dalam procedure. Coba anda teliti pada procedure yang telah dibuat. Disitu anda sering menggunakan procedure PUT_LINE, hanya saja procedure PUT_LINE tersebut bukan anda yang membuat melainkan procedure yang telah disediakan Oracle dan ditempatkan pada package DBMS_OUTPUT. Latihan : Buatlah dua procedure, yaitu procedure cetak_angka dan panggil-proc. Procedure cetak_angka akan mencetak n buah bilangan positif pertama dengan n adalah nilai parameter yang dimasukkan, sedangkan procedure panggil_proc adalah procedure yang menggunakan procedure cetak_angka di dalamnya. Jawab : SQL> CREATE OR REPLACE PROCEDURE cetak_angka (X INTEGER) AS 2 3 J INTEGER; BEGIN 4 FOR J IN 1..X LOOP 5 DBMS_OUTPUT.PUT_LINE (TO_CHAR (J) ); 6 END LOOP; 7 END; 8 / kemudian tuliskan sintak berikut : SQL> 2 CREATE OR REPLACE PROCEDURE panggil_proc AS BEGIN 3 cetak_angka(10); -- memanggil procedure cetak_angka dengan parameter 10 4 END; 5 / kemudian tuliskan sintak berikut : SQL> SET SERVEROUTPUT ON SQL> EXECUTE panggil_proc; C. Function Function adalah suatu blok PL/SQL yang memiliki konsep sama dengan procedure, hanya saja function terdapat pengembalian nilai (return value), oleh karena itu function dapat diakses layaknya sebuah variabel biasa. Sintak umum untuk function dalam Oracle adalah sebagai berikut : CREATE OR REPLACE FUNCTION nama_function (parameter_1, …) RETURN tipe_data AS variabel_1 tipe_data; … BEGIN statemen_1; … RETURN nilai_yang_dikembalikan; END; Statemen RETURN tipe_data diatas menunjukkan bahwa function akan mengembalikan nilai dengan tipe data tertentu, sedangkan statemen RETURN nilai_yang_dikembalikan berfungsi untuk mengembalikan nilai yang telah di proses dalam function. 1. Function tanpa Parameter Function yang tidak memiliki parameter akan memiliki sifat statis (tetap) karena nilai yang diproses didalam function tersebut bersifat tetap. Latihan : Buatlah blok PL/SQL yang akan membuat function yang dapat mengembalikan teks HALLO SEMUA. Jawab : SQL> CREATE OR REPLACE FUNCTION tulis_teks RETURN VARCHAR2 AS 2 BEGIN 3 RETURN 'HALLO SEMUA'; 4 END; 5 / kemudian tulis sintak dibawah ini SQL> CREATE OR REPLACE FUNCTION tulis_teks RETURN VARCHAR2 AS 2 3 S VARCHAR2(20); BEGIN 4 S := 'HALLO SEMUA'; 5 RETURN S; 6 END; 7 / kemudian tulis sintak dibawah ini SQL> SET SERVEROUTPUT ON SQL> SELECT tulis_teks FROM DUAL; kemudian tulis sintak dibawah ini SQL> SET SERVEROUTPUT ON SQL> DECLARE 2 3 X VARCHAR2 (20); BEGIN 4 X := tulis_teks; 5 DBMS_OUTPUT.PUT_LINE(X); 6 END; 7 / Latihan : Buatlah sebuah function yang dapat menghitung jumlah dari 10 buah bilangan bulat positif pertama. Jawab : SQL> CREATE OR REPLACE FUNCTION jumlah_10_BilBul RETURN INTEGER AS 2 JML INTEGER; 3 I INTEGER; 4 BEGIN 5 JML := 0; 6 FOR I IN 1..10 LOOP 7 JML := JML + I; 8 END LOOP; 9 RETURN JML; 10 END; 11 / kemudian tulis sintak dibawah ini SQL> SET SERVEROUTPUT ON SQL> DECLARE 2 3 X INTEGER; BEGIN 4 X := jumlah_10_BilBul; 5 DBMS_OUTPUT.PUT_LINE ('Jumlah = ' || TO_CHAR(X) ); 6 END; 7 / 2. Function dengan Parameter Nilai yang dihasilkan pada function dengan parameter ini bersifat dinamis. Latihan : Buatlah sebuah function yang dapat mengembalikan nilai dari perpangkat bilangan bulat positif (seperti function POWER yang telah disediakan Oracle). Jawab : SQL> CREATE OR REPLACE FUNCTION pangkat_bilangan(bil INTEGER, n INTEGER) 2 RETURN INTEGER AS 3 HASIL INTEGER(10); 4 I INTEGER; 5 BEGIN 6 HASIL := 1; 7 FOR I IN 1..n LOOP 8 HASIL := HASIL * bil; 9 END LOOP; 10 RETURN HASIL; 11 END; 12 / kemudian tulis sintak dibawah ini : SQL> SET SERVEROUTPUT ON SQL> DECLARE 2 3 4 H INTEGER; BEGIN H := pangkat_bilangan(2,3); 5 DBMS_OUTPUT.PUT_LINE ('Hasil = ' || TO_CHAR(H) ); 6 END; 7 / 3. Function Didalam Function Function juga dapat digunakan dalam pembuatan function lainnya. Namun, perlu diperhatikan bahwa function yang akan digunakan untuk membuat function baru sebelumnya sudah dibuat dalam skema database anda. Latihan : Buatlah function yang dapat mengembalikan nilai determinan dari persamaan kuadrat : y = ax2 + bx + c, dimana nilai determinan dutentukan dengan menggunakan rumus D = b2 – 4ac. Jawab : SQL> CREATE OR REPLACE FUNCTION kuadrat(X NUMBER) 2 RETURN NUMBER AS 3 4 HASIL NUMBER(10); BEGIN 5 HASIL := X * X; 6 RETURN HASIL; 7 END; 8 / kemudian tulis sintak dibawah ini: SQL> CREATE OR REPLACE FUNCTION determinan 2 3 (a NUMBER, b NUMBER, c NUMBER) RETURN NUMBER AS 4 5 D NUMBER(10); BEGIN 6 D := kuadrat(b) - (4 * a * c); 7 RETURN D; 8 END; 9 / kemudian tulis sintak dibawah ini: SQL> SET SERVEROUTPUT ON SQL> DECLARE 2 3 D NUMBER(10); BEGIN 4 D := determinan(1, 1, -6); 5 DBMS_OUTPUT.PUT_LINE ('Nilai determinan = ' || TO_CHAR(D) ); 6 END; 7 / 4. Built-In Function Built-In Function adalah function ‘siap pakai’ yang telah disediakan Oracle untuk keperluan-keperluan yang bersifat umum. Berikut ini diberikan daftar function yang sering digunakan dalam menyelesaikan permasalahan PL/SQL di Oracle. a. Function Matematika Daftar function yang termasuk ke dalam kategori Function Matematika adalah sebagai berikut : Nama Function Kegunaan Contoh ABS Menentukan harga mutlak ABS(-12) = 12 CEIL Pembulatan ke atas CEIL(7.22) = 8 FLOOR Pembulatan ke bawah FLOOR(7.98) = 7 MOD Menentukan sisa bagi 10 MOD 3 = 1 POWER Menentukan sisa bagi POWER(2,4) = 16 ROUND (nilai, presisi) Pembulatan nilai ROUND(3.49, 1) = 3.5 Menentukan bilangan positif dan SIGN(12) negative, = 1 SIGN(-12) = -1 SIGN Menghasilkan 1 jika positif Menghasilkan -1 jika negative SQRT Menentukan Akar SQRT(9) = 3 TRUNC Pemotongan TRUNC(2.37, 1) = 2.3 b. Function Karakter/String Daftar function yang termasuk ke dalam kategori Function Karakter/String adalah sebagai berikut : Nama Kegunaan Contoh Function SUBSTR SUBSTR(‘Budi’, 2, 3) = ‘udi’ Mengambil bagian dari teks SUBSTR(‘Budi’, 1, 2) = ‘Bu’ INSTR LPAD RPAD LTRIM Mengembalikan indeks/posisi teks INSTR(‘Belajar’, ‘aj’) = 4 dari teks lainnya INSTR(‘Belajar’, ‘ela’) = 2 Menambahkan teks/karakter di LPAD(‘Budi’, 6, ‘*’) = ’*’Budi’ bagian kiri teks sebanyak panjang LPAD(‘Budi’, string yang ditentukan ‘####Budi’ Menambahkan teks/karakter dibagian RPAD(‘Budi’, 6, ‘*’) = ‘Budi**’ kanan teks sebanyak panjang string RPAD(‘Budi’, yang ditentukan ‘Budi####’ Melakukan penghapusan teks/karakter disebelah kiri teks yang dimaksud 8, 8, LTRIM(‘xxxBudi’, ‘Budi’ ‘#’) = ‘#’) = ‘X’) = Nama Kegunaan Contoh Function RTRIM Melakukan penghapusan RTRIM(‘Budi**’, ‘*’) = ‘Budi’ teks/karakter disebelah kanan teks yang dimaksud INITCAP Mengubah huruf pertama setiap kata INITCAP(‘Satu Dua’) = ‘Satu pada suatu teks menjadi hururf besar Dua’ INITCAP(‘Itu Ita’) = ‘Itu Ita’ UPPER UPPER(‘bandung’) Mengubah teks menjadi huruf besar = ‘BANDUNG’ UPPER(‘Bandung’) = ‘BANDUNG’ UPPET(‘banDUng’) = ‘BANDUNG’ LOWER LOWER(‘IMAM’) = ‘imam’ Mengubah teks menjadi huruf kecil LOWER(‘Imam’) = ‘imam’ LOWER(‘iMAm’) = ‘imam’ LENGTH LENGTH(‘Budi’) = 4 Mengembalikan panjang string/teks LENGTH(‘Budi’) = 5 CONCAT CHR Menyambung teks pertama dengan CONCAT(‘ab’, ’cd’) = ’abcd’ teks kedua CONCAT(‘bu’, ’ku’) = ‘buku’ Mengubah nilai decimal ke bentuk CHR(65) = ‘A’ kode ASCII CHR(66) = ‘B’ c. Function Konversi Daftar Function yang termasuk ke dalam kategori Function Konversi adalah sebagai berikut : Nama Function TO_CHAR Kegunaan Contoh Mengubah nilai numeric/bentuk TO_CHAR(2) = ‘2’ tanggal TO_CHAR(15) = ‘15’ ke dalam bentuk teks/karakter TO_NUMBER Mengubah teks/karakter ke dalam bentuk numeric TO_DATE Mengubah tanggal teks ke TO_NUMBER(‘16’) = 16 TO_NUMBER(‘9’) = 9 dalam TO_DATE(‘1-may-08’) 1/05/08 = d. Function Tanggal Daftar Function yang termasuk ke dalam kategori Function Tanggal adalah sebagai berikut : Nama Function SYSDATE Kegunaan Mengambil tanggal Contoh system - yang ada pada computer ADD_MONTHS Menambah/mengurangi ADD_MONTHS (SYSDATE - tanggal dengan unit bulan 2) = /* dua bulan sebelum sekarang */ MONTHS_BETWEEN Mengembalikan selisih bulan MONTHS_BETWEEN (‘1-jul- antara 08’,’1-may-08’) = 2 dua tanggal yang diberikan LAST_DAY Menentukan tanggal terakhir LAST_DAY(‘1-feb-08’) = 28- dari bulan yang terdapat pada FEB-08 tanggal yang diberikan LAST_DAY(2-jun-08) = 30JUN-08 Tugas 5 : 1. Tentukanlah volume sebuah kubus dengan menggunakan procedure yang mempunyai parameter berjenis keluaran. Adapun masukan yang diperlukan adalah panjang sisi dari kubus tersebut. 2. Buatlah function yang dapat menghitung luas [ermukaan sebuah kubus dengan panjang sisi 5 cm. (luas permukaan kubus = 6 x (sisi x sisi) ) BAB VI PACKAGE DAN TRIGGER A. Tujuan Praktikum Mahasiswa memahami dan mengerti mengenai Package dan Trigger. B. Package Package adalah sebuah paket atau kumpulan procedure dan function yang berguna untuk menyelesaikan masalah-masalah dalam blok PL/SQL. Package terdiri dari dua bagian yang terpisah tetapi keduanya saling berhubungan, yaitu Package Specification dan Package Body. 1. Package Specification Package specification sering disebut dengan istilah Package Header atau kepala package, dimana didalamnya terdapat pendeklarasian variabel-variabel, procedure-procedure atau function-function yang akan dibuat. Adapun sintak umum dari pembuatan sebuah package specification adalah sebagai berikut : CREATE OR REPLACE PACKAGE nama_package AS nama_variabel; … PROCEDURE nama_procedure; … FUNCTION nama_function RETURN tipe_data; END nama_package; Latihan : Buatlah sebuah package dengan nama RUMUS yang didalamnya terdapat tiga buah variabel dengan nama massa, percepatan, dan gaya yang semuanya bertipe NUMBER, function yang mengembalikan nilai bertipe NUMBER dengan nama hitung_gaya dan procedure dengan nama cetak_gaya. Jawab : SQL> CREATE OR REPLACE PACKAGE RUMUS AS 2 massa NUMBER(10); 3 percepatan NUMBER(10); 4 gaya NUMBER; 5 FUNCTION hitung_gaya( n NUMBER, a NUMBER) RETURN NUMBER; 6 PROCEDURE cetak_gaya(F NUMBER); 7 END RUMUS; 8 / sampai disini anda telah mempunyai sebuah package specification, tetapi anda belumdapat menggunakan package tersebut karena procedure dan function yang terdapat didalamnya belum mempunyai implementasi atau belum dideskripsikan. kemudian tulislah sintak dibawah ini : SQL> SET SERVEROUTPUT ON SQL> DECLARE 2 3 F NUMBER; BEGIN 4 RUMUS.massa := 2; 5 RUMUS.percepatan := 3; 6 F := RUMUS.hitung_gaya(RUMUS.massa, RUMUS.percepatan); 7 RUMUS.cetak_gaya(F); 8 END; 9 / hasil : DECLARE * ERROR at line 1: ORA-04068: existing state of packages has been discarded ORA-04067: not executed, package body "SCOTT.RUMUS" does not exist ORA-06508: PL/SQL: could not find program unit being called ORA-06512: at line 6 Error diatas disebabkan tidak adanya package body dengan nama RUMUS. Oleh karena itu perlu dibuat implementasi dari procedure dan function yang terdapat pada package sebelum package tersebut digunakan dalam blok PL/SQL. Untuk membuat package body akan dijelaskan pada subbab selanjutnya. 2. Package Body Package body adalah bagian yang digunakan untuk mengimplementasikan atau mendeskripsikan procedure dan function yang telah dideklarasikan pada package specification. Adapun sintak umum untuk package body adalah sebagai berikut : CREATE OR REPLACE PACKAGE BODY nama_package AS PROCEDURE nama_procedur (parameter; …) AS BEGIN statemen_1; … END; FUNCTION nama_function (parameter, …) RETURN tipe_data AS BEGIN statemen_1; … END; Latihan : Lanjutan pada soal latihan sebelumnya mengenai package specification. Jawab : SQL> CREATE OR REPLACE PACKAGE BODY RUMUS AS 2 FUNCTION hitung_gaya(n NUMBER, a NUMBER) RETURN NUMBER AS 3 BEGIN 4 gaya := n * a; 5 RETURN gaya; 6 END; 7 8 PROCEDURE cetak_gaya(F NUMBER) AS BEGIN 9 10 DBMS_OUTPUT.PUT_LINE('Gaya = ' || TO_CHAR(F) ); END; 11 12 END RUMUS; / kemudian tuliskan sintak dibawah ini : SQL> SET SERVEROUTPUT ON SQL> DECLARE 2 3 F NUMBER; BEGIN 4 RUMUS.massa := 2; 5 RUMUS.percepatan := 3; 6 F := RUMUS.hitung_gaya(RUMUS.massa, RUMUS.percepatan); 7 RUMUS.cetak_gaya(F); 8 END; 9 / Gaya = 6 C. Trigger Trigger adalah sebuah stored procedure yang dieksekusi pada saat terjadi modifikasi data pada tabel-tabel dalam database. Contoh kegunaan trigger adalah untuk menjaga validasi data yang akan dimasukkan ke tabel dan lain-lain. Sintak umum trigger adalah sebagai berikut : CREATE OR REPLACE TRIGGER nama_trigger (BEFORE|AFTER) (INSERT|UPDATE|UPDATE OF nama_kolom|DELETE) ON nama_tabel FOR EACH ROW WHEN (kondisi) DECLARE variabel_1; … BEGIN statemen_1; … END; Latihan : Sebelum anda membuat trigger, buatlah terlebih dahulu sebuah tabel STOK yang terdiri dari tiga buah field yaitu : Kode, Nama_barang, dan Jumlah_Stok. SQL> CREATE TABLE STOK ( 2 KODE CHAR(4) NOT NULL PRIMARY KEY, 3 NAMA_BARANG VARCHAR2(25), 4 JUMLAH_STOK NUMBER(10) 5 ); kemudian masukan data-data dummy dengan sintak sebagai berikut : SQL> INSERT INTO STOK VALUES ('0001','Televisi',25); 1 row created. SQL> INSERT INTO STOK VALUES ('002','Radio',50); 1 row created. SQL> INSERT INTO STOK VALUES ('0003','Kipas Angin',30); 1 row created. SQL> INSERT INTO STOK VALUES ('0004','Tape',35); 1 row created. SQL> INSERT INTO STOK VALUES ('0005','AC',15); 1 row created. Dari latihan diatas anda dapat mebuat trigger untuk event BEFORE INSERT pada tabel Stok. SQL> CREATE OR REPLACE TRIGGER TR_BI_STOK 2 BEFORE INSERT ON STOK 3 4 FOR EACH ROW BEGIN 5 DBMS_OUTPUT.PUT_LINE ('Trigger BEFORE INSERT dilaksanakan'); 6 END; 7 / Trigger created. Kemudian anda dapat mebuat trigger untuk event BEFORE INSERT pada tabel Stok. SQL> CREATE OR REPLACE TRIGGER TR_AU_STOK 2 AFTER UPDATE ON STOK 3 FOR EACH ROW 4 BEGIN 5 DBMS_OUTPUT.PUT_LINE ('Trigger AFTER UPDATE dilaksanakan'); 6 END; 7 / Trigger created. Kemudian anda dapat mebuat trigger untuk event AFTER DELETE pada tabel Stok. SQL> CREATE OR REPLACE TRIGGER TR_AD_STOK 2 AFTER DELETE ON STOK 3 FOR EACH ROW 4 BEGIN 5 DBMS_OUTPUT.PUT_LINE ('Trigger AFTER DELETE dilaksanakan'); 6 END; 7 / Trigger created. Untuk mengecek masing-masing trigger dengan sintak sebagai berikut : Mengecek TR_BI_INSERT SQL> INSERT INTO STOK VALUES('0006','Lemari Es',20); Trigger BEFORE INSERT dilaksanakan 1 row created. Mengecek TR_AU_STOK SQL> UPDATE STOK 2 SET JUMLAH_STOK = 10 3 WHERE KODE = '0006'; Trigger AFTER UPDATE dilaksanakan 1 row updated. Mengecek TR_AD_STOK SQL> DELETE FROM STOK WHERE KODE = '0006'; Trigger AFTER DELETE dilaksanakan 1 row deleted. Latihan : Buatlah dua buah tabel Pembelian dan Penjualan : Jawab : SQL> CREATE TABLE PEMBELIAN ( 2 KD_PEMBELIAN CHAR(6) NOT NULL, 3 KODE_BARANG CHAR(4), 4 JUMLAH_PEMBELIAN NUMBER(5), 5 CONSTRAINT PK_PEMBELIAN PRIMARY KEY (KD_PEMBELIAN), 6 CONSTRAINT FK_PEMBELIAN_STOK FOREIGN KEY (KODE_BARANG) REFERENCES STOK(KODE)); SQL> CREATE TABLE PENJUALAN ( 2 KD_PENJUALAN CHAR(6) NOT NULL, 3 KODE_BARANG CHAR(4), 4 JUMLAH_PENJUALAN NUMBER(5), 5 CONSTRAINT PK_PENJUALAN PRIMARY KEY (KD_PENJUALAN), 6 CONSTRAINT FK_PENJUALAN_STOK FOREIGN KEY (KODE_BARANG) REFERENCES STOK(KODE)); kemudian menambah JUMLAH_STOK pada tabel STOK dengan melakukan proses INSERT pada tabel PEMBELIAN. SQL> CREATE OR REPLACE TRIGGER TR_AI_PEMBELIAN 2 AFTER INSERT ON PEMBELIAN 3 FOR EACH ROW 4 DECLARE 5 X NUMBER; 6 BEGIN 7 X := :NEW.JUMLAH_PEMBELIAN; 8 UPDATE STOK 9 SET JUMLAH_STOK = JUMLAH_STOK + X; 10 END; 11 / Trigger created. untuk mengecek kebernaran trigger : SQL> SELECT * FROM STOK; 1. Melakukan CASCADING DELETE dengan Trigger Cascading delete adalah menghapus baris dari dua atau lebih tabel yang saling berhubungan. Misalnya : buatlah 2 tabel, yaitu tabel Pelanggan dan Orders yang dihubungkan melalui Constraint. SQL> CREATE TABLE PELANGGAN ( 2 NO_PELANGGAN NUMBER(10), 3 NAMA VARCHAR2(25), 4 ALAMAT VARCHAR2(15)); SQL> CREATE TABLE ORDERS ( 2 NO_ORDER NUMBER(10), 3 NO_PELANGGAN NUMBER(10), 4 JUMLAH_ORDER NUMBER(5)); SQL> INSERT INTO PELANGGAN VALUES ('1','BUDIMAN','SEMARANG'); 1 row created. SQL> INSERT INTO PELANGGAN VALUES ('2','DEWI','SOLO'); 1 row created. SQL> INSERT INTO ORDERS VALUES ('1001','2','50'); 1 row created. Jika kita ingin menghapus baris dari tabel PELANGGAN yang mempunyai NO_PELANGGAN 2, sedangkan kita masih memerlukan baris pada tabel ORDERS tersebut untuk memperoleh informasi detail tentang NO_PELANGGAN, maka setiap kita akan menghapus baris pada tabel PELANGGAN kita harus menghapus terlebih dahulu baris-baris pada tabel ORDERS yang mengacu pada tabel PELANGGAN. Untuk itu kita harus membuat trigger pada tabel PELANGGAN dengan event BEFORE DELETE. SQL> SELECT*FROM ORDERS; SQL> CREATE OR REPLACE TRIGGER TR_BD_PELANGGAN 2 BEFORE DELETE ON PELANGGAN 3 FOR EACH ROW 4 BEGIN 5 DELETE FROM ORDERS 6 WHERE NO_PELANGGAN =:OLD.NO_PELANGGAN; 7 END; 8 / sekarang kita akan melakukan penghapusan pada tabel PELANGGAN. SQL> DELETE FROM PELANGGAN 2 WHERE NO_PELANGGAN = 2; 2. Mengaktifkan dan Menon-aktifkan Trigger Dalam sebuah database, trigger tidak selalu dijalankan, hal ini tergantung pada pergeseran (setting) trigger itu sendiri. Artinya, trigger akan dieksekusi jika trigger diaktifkan(enable) dan diabaikan keberadaannya jika trigger tersebut dinonaktifkan (disable). Dalam hal ini Oracle menyediakan ALTER TRIGGER untuk mengaktifkan (bukan menjalankan) dan menon-aktifkan trigger yang terdapat dalam database. Klausa ENABLE untuk mengaktifkan trigger dan klause DISABLE untuk menon-aktifkan. Sintak umumnya sebagai berikut : ALTER TRIGGER Nama_trigger ENABLE|DISABLE; Latihan : Buatlah dua buah trigger pada tabel PELANGGAN masing-masing untuk event AFTER INSERT dan AFTER UPDATE. Jawab: Untuk event AFTER INSERT : SQL> CREATE OR REPLACE TRIGGER TR_AI_PELANGGAN 2 AFTER INSERT ON PELANGGAN 3 FOR EACH ROW 4 BEGIN 5 DBMS_OUTPUT.PUT_LINE ('Trigger AFTER INSERT dilaksanakan'); 6 END; 7 / Untuk event AFTER UPDATE : SQL> CREATE OR REPLACE TRIGGER TR_AU_PELANGGAN 2 AFTER UPDATE ON PELANGGAN 3 FOR EACH ROW 4 BEGIN 5 DBMS_OUTPUT.PUT_LINE ('Trigger AFTER UPDATE dilaksanakan'); 6 END; 7 / Sekarang kita akan menon-aktifkan trigger TR_AI_PELANGGAN, sehingga pada saat kita masukkan data pada tabel PELANGGAN, trigger tersebut tidak akan dieksekusi. SQL> ALTER TRIGGER 2 3 TR_AI_PELANGGAN DISABLE; Melakukan pengecekan terhadap aktif atau tidaknya trigger diatas dengan memasukkan sebuah baris baru ke tabel PELANGGAN, SQL> INSERT INTO PELANGGAN VALUES('3','LESTARI','JATENG'); Dari hasil diatas, trigger TR_AI_PELANGGAN tidak dieksekusi. Sekarang coba aktifkan kembali trigger tersebut. SQL> ALTER TRIGGER 2 3 TR_AI_PELANGGAN ENABLE; Lakukan pengecekan aktif atau tidaknya trigger diatas dengan memasukkan sebuah baris baru ke tabel PELANGGAN. SQL> INSERT INTO PELANGGAN VALUES ('3','SRI','BANDUNG'); SQL> INSERT INTO PELANGGAN VALUES ('4','HARTANTI','MAGELANG'); Untuk mengaktifkan dan menonaktifkan ‘semua’ trigger yang terdapat pada sebuah tabel, anda dapat menggunakan statemen ALTER TABLE. Misal, kita ingin menonaktifkan semua trigger yang ada pada tabel PELANGGAN sehingga saat proses INSERT dan UPDATE pada tabel tersebut, trigger TR_AI_PELANGGAN TR_AU_PELANGGAN tidak akan dieksekusi. SQL> ALTER TABLE 2 PELANGGAN 3 DISABLE ALL TRIGGERS; Sekarang kita coba lakukan proses UPDATE pada tabel PELANGGAN : SQL> UPDATE PELANGGAN 2 SET NAMA = 'Asfira' 3 WHERE NO_PELANGGAN = 3; dan Untuk pengaktifan ulang semua trigger yang terdapat pada tabel PELANGGAN dengan melakukan perintah sebagai berikut : SQL> ALTER TABLE 2 PELANGGAN 3 ENABLE ALL TRIGGERS; sekarang coba lakukan proses UPDATE pada tabel PELANGGAN SQL> UPDATE PELANGGAN 2 SET NAMA = 'Junaedi' 3 WHERE NO_PELANGGAN = 3; DAFTAR PUSTAKA Heryanto, Imam, Pemrograman PL/SQL Oracle, Informatika Bandung, Bandung 2003. Tim X-Oerang Technology, Pemrograman Menggunakan Developer, Andi Offset, Yogyakarta 2003. Oracle