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 1 PL/SQL – STMIK CIC

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 2 PL/SQL – STMIK CIC

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 :


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


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


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.



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.


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';


dbms_output.putline (l_start_date); --show date End; / 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;

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, rata-rata 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;

memahami konsep dasar pl/sql

Documents