Memahami Konsep Dasar Pl/sql

PL/SQL SEBUAH PENGANTAR (Procedure Language /Structure Query Language) 1. PENDAHULUAN PL/SQL versi 1.0 diperkenalkan pertama kali pada oracle database versi 6.0. PL/SQL memungkinkan penulisan pernyataan kompleks yang tidak dapat dibuat dengan pernyataan SQL. Namun PL/SQL versi 1.0 sangat terbatas kemampuannya dan setelah mengalami sejumlah perkembangan dengan dikeluarkannya Oracle database versi 7.0 dan selanjutnya. PL/SQL adalah fasilitas yang disediakan Oracle sehingga pengguna dapat memanfaatkan konsep pemrograman. PL/SQL adalah bahasa pemrograman yang digabungkan dengan SQL. Dalam PL/SQL dapat digunakan perintah untuk memanipulasi data yang ada dalam database Oracle. PL/SQL membentuk pemrograman terstruktur dalam memproses data. Beberapa kelebihan PL/SQL dalam database Oracle : -

PL/SQL dapat digunakan di server sehingga client hanya dapat mengakses didalam server.

-

Penggunaan PL/SQL mudah dimengerti oleh setiap pengguna.

-

PL/SQL dapat didesain khusus untuk database Oracle dalam menggunakan program aplikasi. Pada PL/QL versi 2 mulai mendukung paket yang tersimpan(stored

packet), prosedur dan fungsi.

© Marsani Asfi – 2007 PL/SQL – STMIK CIC

1

Gambar 1.1. Arsitektur PL/SQL

Arsitektur PL/SQL terlihat pada gambar 1.1 diatas. Database Oracle memanggil program kompilasi ke dalam memori dan kemudian PL/SQL engine dan SQL engine mengeksekusi program yang ada. 2. STRUKTUR PL/SQL Struktur PL/SQL mirip dengan struktur bahasa pascal atau delphi yang menggunakan struktur blok, sehingga akan mempermudah pengertian dalam pemrograman dengan PL/SQL. Struktur Blok berisi perintah SQL dengan kondisi yang berbeda. Perintah PL/SQL dapat menangani kesalahan saat dijalankan. Setiap pengetikan dengan menggunakan PL/SQL dalam SQL*Plus selalu diakhiri dengan tanda /(slash). Sintaks penggunaan PL/SQL adalah sebagai berikut : Declare Begin Exception End © Marsani Asfi – 2007 PL/SQL – STMIK CIC

2

Untuk praktikum selanjutnya dalam penggunaan PL/SQL akan digunakan SQL*Plus sebagai editor penulisan script dan uji cobanya. Ada dua media SQL*Plus yang dapat digunakan dalam praktikum ini, yaitu : Via Web dan Editor SQL*Plus itu sendiri.

Gambar 1.1. SQL*Plus Via Web Sedangkan untuk Oracle PL*SQL Editor tampilannya akan terlihat seperti pada gambar dibawah ini :

© Marsani Asfi – 2007 PL/SQL – STMIK CIC

3

Gambar 1.2. Editor SQL*Plus Dalam praktikum selanjutnya dibebaskan dalam penggunaan media untuk menulis dan menjalankan Perintah PL/SQL. Misalkan diberikan perintah PL/SQL sebagai berikut :

Gambar 1.3. Contoh PL/SQL Baris 1 sampai dengan 4 menjelaskan tentang deklarasi variabel Nim dengan tipe data number dengan panjang maksimum 14 dan deklarasi variabel Nama dengan tipe data varchar dengan panjang maksimum adalah 20, dan variabel Jurusan dengan tipe data varchar dengan panjang © Marsani Asfi – 2007 PL/SQL – STMIK CIC

4

maksmum 20. Baris 6 dan 8 menjelaskan tentang pengisian variabel Nim, Nama serta Jurusan. Baris 9 dan 10 menjelaskan mengenai tentang set untuk variabel yang menghasilkan tampilan Nim, Nama. Untuk menjalankan aplikasi diatas ketikkan perintah sebagai berikut :

Gambar 1.4. Perintah Output untuk Kelayar Selanjutnya untuk melihat hasilnya dapat mengetikkan tanda / (slash). Output akan terlihat sebagai berikut :

Gambar 1.5. Output yang dihasilkan

© Marsani Asfi – 2007 PL/SQL – STMIK CIC

5

STRUKTUR PL/SQL (Procedure Language /Structure Query Language) Pada pertemuan sebelumnya dijelaskan secara sederhana bentuk penulisan struktur dalam PL/SQL, yaitu : Declare Begin Exception End Struktur diatas dapat dijelaskan sebagai berikut : 1. Bagian Judul (Header) Bagian ini hanya digunakan jika PL/SQL diberikan nama, misalnya untuk prosedur atau fungsi. Bagian ini berisi nama blok, daftar parameter, dan pengembalian hasil (return) jika blok adalah fungsi. 2. Bagian Deklarasi (declaration) Bagian ini untuk membuat deklarasi mengenai semua variable dan konstanta yang direferensikan dalam pernyataan PL/SQL. Bagian deklarasi ini dimulai dengan perintah DECLARE. Jika tidak ada variable atau konstanta yang ingin dideklarasikan bagian ini boleh dihilangkan. 3. Bagian Eksekusi (Execution). Bagian ini memuat pernyataan-pernyataan PL/SQL yang akan ditulis. Bagian eksekusi ini harus dimulai dengan perintah BEGIN.

© Marsani Asfi – 2007 PL/SQL – STMIK CIC

6

4. Bagian Perkecualian (Exception) Bagian ini memuat cara menangani kesalahan-kesalahan (error) pada waktu eksekusi program PL/SQL. Jika program tidak memuat cara menangani kesalahan, bagian ini boleh dihilangkan. Setiap pernyataan PL/SQL harus diakhiri dengan tanda titik-koma(;) dan semua program PL/SQL harus diakhiri dengan perintah END. Contoh-1 : DECLARE Ulang NUMBER; Stop EXCEPTION; BEGIN Ulang := 0; LOOP Ulang := Ulang + 1; DBMS_output.put(‘data ke #’); DBMS_output.put_line(to_char(Ulang)); IF Ulang > 100 THEN RAISE Stop; END IF; END LOOP; EXCEPTION WHEN stop THEN DBMS_output.put_line(‘data ulangan selesai’); END; Pada bagian deklarasi ditempatkan variable dan konstanta yang dipakai oleh pernyataan PL/SQL yang dibuat. Perbedaan variabel dengan konstanta adalah : - Konstanta – nilai ditentukan pada saat deklarasi dibuat sehingga nilainya tetap pada saat program dieksekusikan. - Variabel – dapat menerima nilai baru atau sebaliknya diubah pada saat program dieksekusi. © Marsani Asfi – 2007 PL/SQL – STMIK CIC

7

Type data yang dapat dikenali dalam PL/SQL dapat berupa type data SQL dan type data seperti : 1. BOOLEAN–dipakai untuk menyatakan data logika, yaitu TRUE(benar), FALSE(salah), dan NULL(kosong). 2. BINARY_INTEGER–digunakan untuk mendeklarasikan bilangan yang tidak mempunyai angka desimal. Tipe data NATURAL dan POSITIVE merupakan subset dari BINARY_INTEGER. 3. %TYPE-tipe data ini dipakai untuk menandakan bahwa variabel yang deklarasikan sama dengan tipe data dari kolom tabel tertentu. 4. %ROWTYPE–tipe data ini menandakan bahwa sekelompok variabel adalah sama dengan tipe data darirow suatu tabel tertentu. 5. Tabel dan Record – Tipe data komposit untuk pemakaian yang lebih kompleks. Contoh-2 : (deklarasi konstanta) DECLARE Temperatur_asal NUMBER :=0; Nilai_awal NUMBER(2) :=100; Contoh-3 : (deklarasi variabel) DECLARE Temperatur NUMBER(3.3); Nama VARCHAR(30); Alamat %Type; 3 Declare L_start_date date; Begin L_start_date := '29-SEP-2005';

© Marsani Asfi – 2007 PL/SQL – STMIK CIC

8

End; /

dbms_output.putline (l_start_date); --show date

SQL> / 29-SEP-05 PL/SQL procedure successfully completed. L_start_date := to_date('14-JAN-2063', 'DD-MON-YYYY'); dbms_output.put_line (l_start_date); L_start_date := to_date('09-JUN-91:13:01', 'DD-MON-YY:HH24:MI'); dbms_output.put_line (l_start_date);

1 declare 2 v_prod_name varchar2(80); 3 begin 4 select prod_name 5 into v_prod_name 6 from products 7 where rownum = 1; 8 dbms_output.put_line(v_prod_name); 9* end; 10 /

1 declare 2 v_prod_name varchar2(80); 3 cursor get_data is 4 select prod_name 5 from products; 6 begin 7 open get_data; 8 fetch get_data into v_prod_name; 9 dbms_output.put_line(v_prod_name); 10 close get_data; 11* end;

© Marsani Asfi – 2007 PL/SQL – STMIK CIC

9

PERTEMUAN 7 VIEW 1. Tujuan Pembelajaran : Diharapkan mahasiswa mampu, untuk : • Memahami definisi View • Dapat membuat View • Dapat Memanggil data melalui View • Merubah definisi View • Insert, Update, dan Delete data melalui View • Menghapus (drop) view 2. TEORI DAN PERCOBAAN 2.1. Definisi View View adalah salah satu object database, yang secara logika merepresentasikan sub himpunan dari data yang berasal dari satu atau lebih table. Kegunaan dari view adalah : • Membatasi akses database • Membuat query kompleks secara mudah • Mengijinkan independensi data • Untuk menampilkan view (pandangan) data yang berbeda dari data yang sama. Ada 2 (dua) tipe view, yaitu Simple View dan Complex View. Berikut ini perbandingan antara Simple View dan Complex View : Fitur Simple View Complex View Jumlah table Satu Satu atau lebih Berisi Fungsi Tidak Ya Berisi Group Data Tidak Ya DML melalui view Ya Tidak selalu 2.2. Membuat View View dapat dibuat dengan perintah CREATE VIEW. Subquery dapat dicantumkan dalam CREATE VIEW, tapi subquery yang digunakan tidak boleh berisi klausa ORDER BY. Lab 1 : Buat view View01 yang berisi detail dari pegawai yang bekerja pada department 10.

Untuk menampilkan struktur dari view diberikan perintah DESCRIBE namaview;

Pada view juga dapat dibuat kolom alias. Lab 2 : Buat view View02 yang berisi nomer, nama dan gaji pegawai yang bekerja di department 30. Beri nama kolom baru yaitu EMPLOYEE_NUMBER, NAME dan SALARY.

2.3. Memanggil data dari View Untuk memanggil data dari view, digunakan perintah yang sama seperti memanggil data dari table. Lab 3 : Tampilkan semua data yang ada pada view02

2.4. Memodifikasi View Untuk memodifikasi View digunakan klausa CREATE OR REPLACE VIEW. Lab 4 Modifikasi judul kolom dari View01(lab 1) menjadi seperti berikut :

2.5. Membuat Complex View Berikut ini akan dicontohkan pembuatan Complex View yang berisi fungsi group untuk menampilkan nilai yang berasal dari dua table. Lab 5 : Buat Complex View05 yang berisi nama department, minimum gaji, maksimum gaji, ratarata gaji dari seluruh pegawai pada tiap-tiap department

2.6. Aturan untuk membentuk operasi DML pada View Beberapa aturan untuk membentuk operasi DML pada View : • Operasi DML dapat dibentuk pada Simple View • Baris data pada View tidak dapat dihapus, jika berisi : o Fungsi Group o Klausa GROUP BY o Keyword DISTINCT

• Data pada View tidak bisa dimodifikasi jika berisi : o 3 Kondisi yang sudah disebutkan diatas o Kolom yang didefinisikan oleh suatu ekspresi o Kolom ROWNUM • Pada View tidak bisa ditambahkan data, jika : o View berisi 5 kondisi yang sudah disebutkan diatas o Terdapat kolom NOT NULL pada base table (table asal darimana view dibuat) yang tidak dipilih oleh View. 2.7. Menggunakan Klausa WITH CHECK OPTION Jika klausa WITH CHECK OPTION digunakan, maka tidak diperbolehkan terjadi perubahan data pada kolom yang punya relasi ke table yang lain. Misal pada view EMPVU20 kolom deptno punya relasi ke kolom deptno pada table department, maka perubahan data yang dilakukan pada kolom ini tidak diperbolehkan. Lab 6 : Buat view EMPVU20 yang berisi semua data pegawai pada table EMP yang bekerja di department 20, beri klausa WITH CHECK OPTION.

Perubahan yang dilakukan pada view EMPVU20 untuk kolom deptno akan menimbulkan pesan kesalahan.

2.8. Mengabaikan (tidak memperbolehkan) dilakukan Operasi DML Klausa READ ONLY digunakan jika kita ingin mengabaikan atau tidak mengijinkan semua operasi DML yang dilakukan pada data. Lab 7 : Buat view EMPVU10 yang berisi data nomer, nama, dan pekerjaan pegawai untuk pegawai yang bekerja di department 10. Gunakan klausa READ ONLY untuk mengabaikan operasi DML pada view.

Setelah dibuat view EMPVU10 tersebut, sembarang operasi DML yang dilakukan pada view, akan menimbulkan pesan kesalahan, seperti pada contoh berikut :

2.9. Menghapus View View dapat dihapus dengan menggunakan perintah DROP VIEW nama_view; Lab 8 : Hapus view EMPVU10

LATIHAN SOAL 1. Buat view Soal1 yang berisi nomer pegawai, nama pegawai, nomer department yang berasal dari table pegawai. Ubah judul kolom nama pegawai menjadi PEGAWAI. 2. Tampilkan view Soal1

3. Tampilkan nama view dan teks-nya dari data dictionary USER_VIEWS

4. Buat view dengan nama DEPT20 yang berisi nomer, nama dan gaji dari pegawai yang bekerja di department 20. Beri judul kolom EMPLOYEE_ID,EMPLOYEE, dan DEPARTMENT_ID. Jangan perbolehkan pegawai untuk mendaftar kembali (atau mengisi datanya lagi) ke department yang lain melalui view. 5. Tampilkan struktur dari view DEPT20.

6. Buat view Soal6 yang berisi nama pegawai, nama department, gaji dan grade dari gaji untuk semua pegawai. Beri judul PEGAWAI, DEPARTMENT, GAJI, GRADE. Tampilkan data pada SALARY_VU.

PERTEMUAN #8 DECLARE pada PL/SQL 1.Struktur Blok PL/SQL PL/SQL adalah bahasa blok yang terstruktur yang berarti program PL/SQL dibagi dan ditulis dalam blok logika dari kode. Dengan kode PL/SQL, akan muncul proses seperti manipulasi data atau query. Beberapa bagian dari blok PL/SQL adalah : 1. DECLARE berisi definis dari variabel dan objek yang lain seperti konstrain dan cursor 2. PROCEDURE adalah tempat mengontrol blok, berisi perintah dengan kondisi tertentu dan juga perintah SQL. 3. EXCEPTION merupakan bagian dari blok PL/SQL yang menangani error dan berbagai pengecualian yang didefinisikan oleh user. Suatu blok paling tidak terdiri dari satu PROCEDURE sedangkan DECLARE dan EXCEPTION merupakan option dalm blok. Struktur dasar dari blok PL/SQL adalah : BEGIN DECLARE BEGIN EXCEPTION END END Bila ingin memberikan komentar atau keterangan dalam PL/SQL caranya yaitu dengan memberikan : -- komentar untuk satu baris Atau /* komentar untuk Banyak baris */ 2. DECLARE DECLARE pada blok PL/SQL berisi variabel,konstanta, definis cursor dan tipe data yang bersifat khusus. Semua tipe data dapat dideklarasikan pada DECLARE, namun harus menspesifikasikan tipe datanya dan harus mengikuti aturann tipe data dari ORACLE. Begitu juga dengan variabel harus disesuaikan dengan standar penamaan dari ORACLE.

2.1 Variabel Variabel adalah nilai pengganti dari suatu subjek dalam blok PL/SQL. Variabel PL/SQL harus didefinisikan sebagai tipe data yang valid dan bisa diberikan niali awal bila dianggap perlu. Perintah berikut adalah contoh sekumpulan variabel yang terdapat dalam DECLARE. Declare User char(10); Namatabel char(30); Ukuran number(10); Tanggal date; DECLARE sebagai bagian dari blok PL/SQL tidak bisa dijalankan. Bagian ini hanya untuk mendeklarasikan tipe data. Bagian ini dimulai dengan kata DECLARE dan pada baris selanjutnya dituliskan nama variabel atau objek lain yang hendak didefinisikan. Setelah setiap variabel dideklarasikan, pada bagian akhir diberi tanda titik koma. Variabel juga bisa memberikan nilai awal dengan menggunakan tanda := dan jga bisa dideklarasikan sebagai NOT NULL. Contohnya adalah sebagai berikut : Declare User char(10); Tahun_awal number(4) := ’1970’; Tahun_pajak number not null := ‘1997’; Variabel yang diberi nilai awal seperti baris ke-3 dan ke-4 diatas bersifta statis dan tidak berubah sehingga disebut konstanta. 2.2. CURSOR Cursor adalah tipe variable yang lain dalam PL/SQL. Cursor merupakan variable yang menunjuk baris data dari hasil sebuah query. Jika hasil query berisi banyak baris, setiap record diteliti untuk menganalisa data. Ketika blok PL/SQL melihat hasil query, cursor digunakan untuk menunjuk setiap baris yang dihasilkan. Contoh mendeklarasikan cursor adalah sebagai berikut : Declare Cursor pegawai is Select * from pegawai Cursor hamper sama dengan view dalam SQL, dan dengan menggunakan loop dalam PROCEDURE, dapat digunakan untukk menggulung(Scroll) sebuah cursor.

2.3. Atribut %TYPE %TYPE adalah variable dengan nilai sama dengan nilai suatu kolom. %TYPE digunakan untuk memelihara konsistensi data kode blok. Contoh dari %TYPE adalah sebagai berikut : Declare Cursor c_pegawai is Select no_peg,nama from pegawai; Nomor pegawai.no_peg%type; Namapeg pegawai.nama%type; Variabel nomor dalam contoh diatas, dideklarasikan sama datanya dengan kolom no_peg dari tabel pegawai. Sedangkan variabe namapeg dideklarasikan sama agar sama datanya dengan kolom nama dari tabel pegawai. 2.4. Atribut %ROWTYPE Suatu variabel dapat mempunyai banyak nilai. Jika dapat mendeklarasikan sebuah variabel yang berhubungan dengan cursor. Gunakan atribut %ROWTYPE untuk mendeklarasikan type data tersebut agar datanya sama dengan setiap kolom dalam satu baris dari cursor. Perhatikan contoh berikut ini : Declare Cursor c_pegawai is Select nopeg, nama from pegawai; Datapegawai c_pegawai%ROWTYPE; Contoh diatas mendeklarasikan variable bernama datapegawai. Dengan atribut %TYPE maka variable tersebut mempunyai tipe data yang sama dengan seluruh baris dari cursor c_pegawai. Variabel yang menggunakan %ROWTYPE disebut juga dengan variable agregat. 2.5 Atribut %ROWCOUNT Atribut %ROWCOUNT menyimpan jumlah baris yang diakses cursor dengan perintah SQL dalam satu spesifik blok. Declare Cursor c_pegawai is Select nopeg,nama from pegwai Norecord := c_pegawai%ROWCOUNT; Variable norecord mewakili nomor baris yang sedang diakses oleh cursor c_pegawai.

PERTEMUAN #9 PROCEDURE pada PL/SQL PROCEDURE adalah bagian utama dari sebuah blok PL/SQL dan dapat berdiri sendiri. Pada bagian ini, PL/SQL akan memanggil variabel dan memakai cursor untuk memanipulasi data dalam database. PROCEDURE yang merupakan bagian utama suatu blok, terdiri dari pernyataan kondisi dan perintah SQL. 1. BEGIN ......END Dalam suatu blok, pernyataan BEGIN....END berarti memulai suatu PROCEDURE, sedangkan pernyataan END mengakhiri suatu PROCEDURE. Berikut ini adalah struktur dasar PROCEDURE. BEGIN open cursor; Kondisi1; Pernyataan1; Kondisi2; Pernyataan2; Kondisi3; Pernyataan3; .... .... ... Tutup cursor. END 2. OPEN Perintah OPEN dilakukan setelah mendeklarasikan cursor. Dengan pernyataan OPEN dapat digunakan untuk membuka cursor. Dengan kata lain cursor tidak akan dapat dipakai bila tidak melakukan OPEN. Contoh : DECLARE Cursor cursorpegawai is Select nopeg, nama from pegawai; Datapegawai cursorpegawai%ROWTYPE; BEGIN Open cursorpegawai; Pada contoh diatas, baris no 6 adalah pernyataan untuk membuka cursorpegawai.

3. FETCH FETCH berfungsi untuk mengambil variabel dengan nilai yan ada dari cursor. FETCH dapat dipakai untuk mengambil variabel agregat dan juga variabel biasa. Contoh : DECLARE Cursor cursorpegawai is Select nopeg, nama from pegawai; Datapegawai cursorpegawai %ROWTYPE BEGIN Open cursorpegawai; Loop Fetch cursor pegawai into datapegawai; DBMS.Output.Put_Line(mydata); End loop; Close cursorpegawai; End; Pada contoh diatas, dengan perintah FETCH akan diambil baris sekarang dari cursor untuk dimasukkan kedalam variabel datapegawai. Loop digunakan untuk menggulung cursor. Contoh berikutnya adalah mengambil baris dari cursor ke dalam variabel nomor dan nama yang telah didefinisikan dalam DECLARE. DECLARE Cursor cursorpegawai is Select nopeg,nama from pegawai; Nomor pegawai.nopeg%TYPE; Namapegawai pegawai.nama%TYPE; BEGIN Open cursorpegawai; Loop FETCH CURSORPEGAWAI INTO NOMOR,NAMAPEGAWAI; END LOOP; Close cursorpegawai; END; 4. CLOSE Setelah selesai memakai cursor dalam blok, cursor ditutup dengan perintah CLOSE. Sintaksnya adalah sebagai berikut : BEGIN Open cursor; pernyataan1; pernyataan2; ......

........ Close cursor; END Ketika cursor ditutup, hasil query akan hilang dan apabila hendak mengambil hasil query setelah ditutup, jalankan OPEN kembali. IF....THEN IF...THEN adalah pernyataan kondisi yang memberikan kontrol bagaimana perintah SQL diproses. Penggunaannya hampir sama dengan bahasa generasi ketiga yang memakai bahasa prosedural. Sintaks untuk satu kondisi : IF kondisi1 THEN Pernyataan1 END IF; Sintaks untuk dua kondisi : IF kondisi1 THEN Pernyataan1; ELSE Pernyataan2; END IF Sintaks untuk dua kondisi atau lebih : IF kondisi1 THEN Pernyataan1; ELSIF kondisi2 THEN Pernyataan2; ELSE Pernyataan3; END IF; Pada contoh terakhir, jika kondisi1 terpenuhi maka pernyataan1 dikerjakan, jika kondis2 terpenuhi maka pernyataan2 dikerjakan, selain dari kondisi tersebut maka pernyataan3 yang dijalankan. 5. LOOP Sebagaimana dalam bahasa pemrograman yang lain, loop adalah suatu keadaan dimana terjadi proses terus menerus selama suatu kondisi terpenuhi. Begitu pula dengan loop dalam blok PL/SQL yang akan menjalankan suatu pernyataan secara non stop selama kondisi yang diberikan terpenuhi.

Loop adalah perulangan yang tidak akan berhenti, biasanya digunakan untuk menggulung cursor. Untuk menghentikan jenis loop ini, harus dispesifikasikan kapan harus berhenti. Misalnya ketika menggulung layar proses loop akan berhenti setelah baris terakhir dari cursor diproses. Sintaksnya adalah sebagai berikut : BEGIN Open cursor; Loop FETCH cursor into variabel; EXIT WHEN cursor%NOTFOUND; Pernyataan1; ..... ..... END LOOP; Close cursor; END;