1. Pengenalan PL/SQL
SQL (Structured Query Language) adalah sebuah bahasa yang
dipergunakan untuk mengakses data dalam basis data relasional. Bahasa ini
secara de facto merupakan bahasa standar yang digunakan dalam
manajemen basis data relasional. Saat ini hampir semua server basis data
yang ada mendukung bahasa ini untuk melakukan manajemen datanya.
“SQL adalah bahasa komputer standar yang digunakan untuk
komunikasi dengan sistem database relasional. Standar SQL didefinisikan
oleh American National Standard Institute (ANSI) dan International Standard
Organization (ISO). Nama resmi bahasa ini adalah International Standard
Database Language SQL (1992). Versi terakhir dari standar ini umumnya
disebut sebagai SQL/92 atau SQL2.
SQL bukan merupakan bahasa pemrograman yang lengkap, melainkan
sebuah data sublanguage yang hanya terdiri dari statement bahasa untuk
mendefinisikan dan memproses suatu database. Untuk mendapatkan suatu
bahasa pemrograman yang menyeluruh, statement SQL harus ditanamkan
(embedded) pada bahasa script seperti VBScript atau dalam bahasa
pemrograman seperti Java atau C++. statement SQL juga dapat digunakan
pada prosedur tersimpan dan trigger, serta dapat diajukan secara interaktif
dengan menggunakan suatu prompt command atau perintah atau instruksi
yang diberikan oleh DBMS
PL/SQL versi 1.0 diperkenalkan pertama kali pada Oracle database
versi 6.0. PL/SQL ini memungkinkan penulisan pernyataan kompleks yang
tidak dapat dibuat dengan pernyataan SQL. Setelah mengalami sejumlah
perkembangan dengan dikeluarkannya Oracle versi 7.0 dan penerusnya.
PL/SQL menjadi suatu bahasa yang sangat bermanfaat. PL/SQL versi 2.0 mulai mendukung paket yang tersimpan (stored packet), prosedur dan fungsi.
ORACLE Corporation, membentuk Relatioanal software Inc, memproduksi
implementasi komersial pertama untuk bahasa ini pada tahun 1979.
Walaupun banyak vendor database relational mendukung SQL/92, namun
kecocokannya terhadap standar ini tidak 100%. Saat ini terdapat beberapa
macam SQL di pasaran dan setiap vendor RDBMS mencoba memperluas
standarnya untuk meningkatkan daya tarik komersial dari produk ini. Kita
akan mengilustarsikan implementasi dari fitur-fitur ini menggunakan
personal Oracle 8, yaitu sistem manajemen database relational Oracle versi
PC.
2. Tipe Data dan Operator yang Digunakan Didalam Blok PL/SQL
Secara garis besar struktur dalam dunia PL / SQL terbagi menjadi 4 bagian
yaitu declare, begin, exception, dan end. Empat bagian struktur tersebut
dapat dijelaskan sebagai berikut :
a. 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.
b. 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.
c. Bagian Eksekusi (Execution)
Bagian ini memuat pernyataan-pernyataan PL/SQL yang akan
ditulis. Bagian eksekusi ini harus dimulai dengan perintah BEGIN.
d. 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. Pada bagian
deklarasi ditempatkan variable dan konstanta yang dipakai oleh pernyataan
PL/SQL yang dibuat.
Perbedaan variabel dengan konstanta adalah:
a. Konstanta
Nilai ditentukan pada saat deklarasi dibuat sehingga nilainya tetap
pada saat program dieksekusikan.
b. 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 :
a. BOOLEAN
Dipakai untuk menyatakan data logika, yaitu TRUE(benar),
FALSE(salah), dan NULL(kosong).
b. BINARY_INTEGER
Digunakan untuk mendeklarasikan bilangan yang tidak mempunyai
angka desimal. Tipe data NATURAL dan POSITIVE merupakan subset
dari BINARY_INTEGER.
c. %TYPE
Tipe data ini dipakai untuk menandakan bahwa variabel yang
deklarasikan sama dengan tipe data dari kolom tabel tertentu.
d. %ROWTYPE
Tipe data ini menandakan bahwa sekelompok variabel adalah sama
dengan tipe data darirow suatu tabel tertentu.
e. Tabel and RecordTipe data komposit untuk pemakaian yang lebih kompleks.
Contoh deklarasi konstanta :
DECLARE
Temperatur_asal NUMBER := 0;
Nilai_mula NUMBER (2) := 100;
Contoh deklarasi variable :
DECLARE
Temperatur NUMBER (3.2);
Nama VARCHAR2 (30);
Alamat %TYPE;
Pada saat deklarasi, panjang tipe data dapat ditentukan (bounded) atau
tidak (unbounded). Jika ditentukan, maka jumlah memori yang dialokasikan
untuk tipe data tersebut juga ditentukan sesuai dengan deklarasi. Misalnya
jika panjang tipe data ditentukan NUMBER(2), yang berarti panjang bilangan
adalah dua angka, maka memori yang dialokasikan adalah untuk dua angka.
Sedangkan jika panjang tipe data tidak ditentukan, maka memori yang
dialokasikan adalah panjang maksimum yang ditentukan untuk tipe data
NUMBER adalah 38 angka.
Cara untuk mendeklarasikan variabel :
a. Nama variable tidak boleh menggunakan kata kunci (key word) yang
digunakan oleh PL/SQL seperti : IF, ELSE, LOOP, WHILE, FOR, EXIT,
NULL, dan END.
b. Pilih nama variable yang ada hubungannya dengan program yang
akan dibuat.
c. Tentukan aturan pemberian nama (naming convention) yang
sehingga mudah diingat.
d. Pergunakan konstanta untuk menggantikan bilangan tetap.
e. Hapus variable-variabel yang tidak dipakai lagi.f. Jika variable mewakili (represent) kolom-kolom suatu table,
pergunakan tipe data %TYPE waktu deklarasi.
3. Penggunaan Blok PL/SQL Dalam:
a. Percabangan
Perintah IF terdiri atas tiga bentuk, yaitu IF THEN, IF THEN ELSE, serta IF THEN ELSEIF. Struktur dari ketiganya ditampilkan dalam satu rumusan umum sebagai berikut:
IF kondisi 1 THEN
Baris perintah...
[ELSIF kondisi 2 THEN
Baris perintah...]
...
[ELSE
baris perintah..]
END IF;
Baris pada baris perintah dapat berupa perintah IF sehingga akan membentuk blok IF bersarang. Bagian ELSIF bisa muncul beberapa kali sesuai dengan kebutuhan sedangkan bagian ELSE biasanya dipakai untuk menangani kondisi jika semua kondisi pada kalang IF... THEN atau ELSIF... THEN tidak terpenuhi. Namun bagian ELSE ini bisa saja tidak digunakan.
contoh:
declare
penuh exception;
n1 number;
n2 number;
begin
if b1>n2 then
raise penuh;
else
dbms_ouput.put_line(‘bisa’);
end if;
end;
b. Pengulangan
struktur ini digunakan untuk melakukan pengulangan statemen dalam PL/SQL dimana saat akan masuk ke badan pengulangan tidak diperlukan adanya kondisi awal yang harus diperiksa terlebih dahulu. Pengecekan kondisi akan dilakukan pada saat program selesai mengeksekusi statemen- statemen yang didefinisikan dalam badan pengulangan sehingga pada struktur pengulangan ini statemen- statemen minimal akan dikerjakan minimal 1 kali.
Sintak umum:
LOOP
statemen_1;
…
EXIT WHEN kondisi;
END LOOP;
contoh: menampilkan teks Saya Belajar PL/SQL.
DECLARE
J integer;
BEGIN
J := 0;
LOOP
J := J+1;
dbms_output.put_line (‘Saya Belajar PL/SQL’);
EXIT WHEN J = 10;
END LOOP;
END;
keterangan:
nilai variabel J pada awalnya bernilai 0. Lalu program memasuki badan pengulangan dan saat memasuki proses ini nilai J bertambah menjadi 1(lihat baris ke-6). Lalu statemen pada baris ke-7 dieksekusi. Saat proses memasuki baris ke-8, nilai J akan terus bertambah karena kondisi menunjukkan jika J bernilai 10 proses pengulangan akan berhenti. Akan tetapi nilai J sekarang masih 1, maka proses pengulangan terus dilakukan sampai kondisi J>10 terpenuhi atau bernilai True. Jika kondisi tersebut telah terpenuhi maka statemen EXIT akan dieksekusi dan program akan meninggalkan proses pengulangan.
contoh: menghitung hasil penjumlahan 2+4+8+…+18.
DECLARE
K integer;
JUMLAH integer := 0;
BEGIN
K := 2;
LOOP
JUMLAH := JUMLAH + K;
K := K+2;
EXIT WHEN K > 18;
END LOOP;
dbms_output.put_line (‘Jumlah = ‘|| JUMLAH);
END;
SQL (Structured Query Language) adalah sebuah bahasa yang
dipergunakan untuk mengakses data dalam basis data relasional. Bahasa ini
secara de facto merupakan bahasa standar yang digunakan dalam
manajemen basis data relasional. Saat ini hampir semua server basis data
yang ada mendukung bahasa ini untuk melakukan manajemen datanya.
“SQL adalah bahasa komputer standar yang digunakan untuk
komunikasi dengan sistem database relasional. Standar SQL didefinisikan
oleh American National Standard Institute (ANSI) dan International Standard
Organization (ISO). Nama resmi bahasa ini adalah International Standard
Database Language SQL (1992). Versi terakhir dari standar ini umumnya
disebut sebagai SQL/92 atau SQL2.
SQL bukan merupakan bahasa pemrograman yang lengkap, melainkan
sebuah data sublanguage yang hanya terdiri dari statement bahasa untuk
mendefinisikan dan memproses suatu database. Untuk mendapatkan suatu
bahasa pemrograman yang menyeluruh, statement SQL harus ditanamkan
(embedded) pada bahasa script seperti VBScript atau dalam bahasa
pemrograman seperti Java atau C++. statement SQL juga dapat digunakan
pada prosedur tersimpan dan trigger, serta dapat diajukan secara interaktif
dengan menggunakan suatu prompt command atau perintah atau instruksi
yang diberikan oleh DBMS
PL/SQL versi 1.0 diperkenalkan pertama kali pada Oracle database
versi 6.0. PL/SQL ini memungkinkan penulisan pernyataan kompleks yang
tidak dapat dibuat dengan pernyataan SQL. Setelah mengalami sejumlah
perkembangan dengan dikeluarkannya Oracle versi 7.0 dan penerusnya.
PL/SQL menjadi suatu bahasa yang sangat bermanfaat. PL/SQL versi 2.0 mulai mendukung paket yang tersimpan (stored packet), prosedur dan fungsi.
ORACLE Corporation, membentuk Relatioanal software Inc, memproduksi
implementasi komersial pertama untuk bahasa ini pada tahun 1979.
Walaupun banyak vendor database relational mendukung SQL/92, namun
kecocokannya terhadap standar ini tidak 100%. Saat ini terdapat beberapa
macam SQL di pasaran dan setiap vendor RDBMS mencoba memperluas
standarnya untuk meningkatkan daya tarik komersial dari produk ini. Kita
akan mengilustarsikan implementasi dari fitur-fitur ini menggunakan
personal Oracle 8, yaitu sistem manajemen database relational Oracle versi
PC.
2. Tipe Data dan Operator yang Digunakan Didalam Blok PL/SQL
Secara garis besar struktur dalam dunia PL / SQL terbagi menjadi 4 bagian
yaitu declare, begin, exception, dan end. Empat bagian struktur tersebut
dapat dijelaskan sebagai berikut :
a. 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.
b. 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.
c. Bagian Eksekusi (Execution)
Bagian ini memuat pernyataan-pernyataan PL/SQL yang akan
ditulis. Bagian eksekusi ini harus dimulai dengan perintah BEGIN.
d. 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. Pada bagian
deklarasi ditempatkan variable dan konstanta yang dipakai oleh pernyataan
PL/SQL yang dibuat.
Perbedaan variabel dengan konstanta adalah:
a. Konstanta
Nilai ditentukan pada saat deklarasi dibuat sehingga nilainya tetap
pada saat program dieksekusikan.
b. 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 :
a. BOOLEAN
Dipakai untuk menyatakan data logika, yaitu TRUE(benar),
FALSE(salah), dan NULL(kosong).
b. BINARY_INTEGER
Digunakan untuk mendeklarasikan bilangan yang tidak mempunyai
angka desimal. Tipe data NATURAL dan POSITIVE merupakan subset
dari BINARY_INTEGER.
c. %TYPE
Tipe data ini dipakai untuk menandakan bahwa variabel yang
deklarasikan sama dengan tipe data dari kolom tabel tertentu.
d. %ROWTYPE
Tipe data ini menandakan bahwa sekelompok variabel adalah sama
dengan tipe data darirow suatu tabel tertentu.
e. Tabel and RecordTipe data komposit untuk pemakaian yang lebih kompleks.
Contoh deklarasi konstanta :
DECLARE
Temperatur_asal NUMBER := 0;
Nilai_mula NUMBER (2) := 100;
Contoh deklarasi variable :
DECLARE
Temperatur NUMBER (3.2);
Nama VARCHAR2 (30);
Alamat %TYPE;
Pada saat deklarasi, panjang tipe data dapat ditentukan (bounded) atau
tidak (unbounded). Jika ditentukan, maka jumlah memori yang dialokasikan
untuk tipe data tersebut juga ditentukan sesuai dengan deklarasi. Misalnya
jika panjang tipe data ditentukan NUMBER(2), yang berarti panjang bilangan
adalah dua angka, maka memori yang dialokasikan adalah untuk dua angka.
Sedangkan jika panjang tipe data tidak ditentukan, maka memori yang
dialokasikan adalah panjang maksimum yang ditentukan untuk tipe data
NUMBER adalah 38 angka.
Cara untuk mendeklarasikan variabel :
a. Nama variable tidak boleh menggunakan kata kunci (key word) yang
digunakan oleh PL/SQL seperti : IF, ELSE, LOOP, WHILE, FOR, EXIT,
NULL, dan END.
b. Pilih nama variable yang ada hubungannya dengan program yang
akan dibuat.
c. Tentukan aturan pemberian nama (naming convention) yang
sehingga mudah diingat.
d. Pergunakan konstanta untuk menggantikan bilangan tetap.
e. Hapus variable-variabel yang tidak dipakai lagi.f. Jika variable mewakili (represent) kolom-kolom suatu table,
pergunakan tipe data %TYPE waktu deklarasi.
3. Penggunaan Blok PL/SQL Dalam:
a. Percabangan
Perintah IF terdiri atas tiga bentuk, yaitu IF THEN, IF THEN ELSE, serta IF THEN ELSEIF. Struktur dari ketiganya ditampilkan dalam satu rumusan umum sebagai berikut:
IF kondisi 1 THEN
Baris perintah...
[ELSIF kondisi 2 THEN
Baris perintah...]
...
[ELSE
baris perintah..]
END IF;
Baris pada baris perintah dapat berupa perintah IF sehingga akan membentuk blok IF bersarang. Bagian ELSIF bisa muncul beberapa kali sesuai dengan kebutuhan sedangkan bagian ELSE biasanya dipakai untuk menangani kondisi jika semua kondisi pada kalang IF... THEN atau ELSIF... THEN tidak terpenuhi. Namun bagian ELSE ini bisa saja tidak digunakan.
contoh:
declare
penuh exception;
n1 number;
n2 number;
begin
if b1>n2 then
raise penuh;
else
dbms_ouput.put_line(‘bisa’);
end if;
end;
b. Pengulangan
struktur ini digunakan untuk melakukan pengulangan statemen dalam PL/SQL dimana saat akan masuk ke badan pengulangan tidak diperlukan adanya kondisi awal yang harus diperiksa terlebih dahulu. Pengecekan kondisi akan dilakukan pada saat program selesai mengeksekusi statemen- statemen yang didefinisikan dalam badan pengulangan sehingga pada struktur pengulangan ini statemen- statemen minimal akan dikerjakan minimal 1 kali.
Sintak umum:
LOOP
statemen_1;
…
EXIT WHEN kondisi;
END LOOP;
contoh: menampilkan teks Saya Belajar PL/SQL.
DECLARE
J integer;
BEGIN
J := 0;
LOOP
J := J+1;
dbms_output.put_line (‘Saya Belajar PL/SQL’);
EXIT WHEN J = 10;
END LOOP;
END;
keterangan:
nilai variabel J pada awalnya bernilai 0. Lalu program memasuki badan pengulangan dan saat memasuki proses ini nilai J bertambah menjadi 1(lihat baris ke-6). Lalu statemen pada baris ke-7 dieksekusi. Saat proses memasuki baris ke-8, nilai J akan terus bertambah karena kondisi menunjukkan jika J bernilai 10 proses pengulangan akan berhenti. Akan tetapi nilai J sekarang masih 1, maka proses pengulangan terus dilakukan sampai kondisi J>10 terpenuhi atau bernilai True. Jika kondisi tersebut telah terpenuhi maka statemen EXIT akan dieksekusi dan program akan meninggalkan proses pengulangan.
contoh: menghitung hasil penjumlahan 2+4+8+…+18.
DECLARE
K integer;
JUMLAH integer := 0;
BEGIN
K := 2;
LOOP
JUMLAH := JUMLAH + K;
K := K+2;
EXIT WHEN K > 18;
END LOOP;
dbms_output.put_line (‘Jumlah = ‘|| JUMLAH);
END;
c. Prosedur dan Fungsi
Sub program adalah PL/SQL block yang dapat di panggil dengan sekumpulan parameter. PL/SQL memiliki dua jenis subprogram, yaitu : Procedure dan Function. Secara umum, Prosedur digunakan untuk melaksanakan aksi dan Fungsi digunakan untuk mengkomputasi suatu nilai.
Sub program adalah PL/SQL block yang dapat di panggil dengan sekumpulan parameter. PL/SQL memiliki dua jenis subprogram, yaitu : Procedure dan Function. Secara umum, Prosedur digunakan untuk melaksanakan aksi dan Fungsi digunakan untuk mengkomputasi suatu nilai.
Untuk melihat Seluruh Subprogram pada Database Oracle, anda dapat menuliskan perintah berikut :
Melihat semua Subprogram
SELECT * FROM USER_PROCEDURES;
Melihat Subprogram tertentu
SELECT * FROM USER_PROCUDERES WHERE OBJECT_NAME = ;
Melihat seluruh Subprogram yang dimiliki DBA
SELECT * FROM DBA_PROCEDURES;
Hal ini tidak di sarankan karena dari perintah
SELECT COUNT (*) FROM DBA_PROCEDURES;
akan menghasilkan row sebanyak 12.906
a) Procedure
Membuat Stored Procedure
Gambar 6 : Create Procedure
Badan Procedure (Secara Umum)
CREATE OR REPLACE PROCEDURE AS
/* Deklarasi variables, types, dan local subprograms. */
BEGIN
/* procedural and SQL masuk disini*/
/* blok ini yang wajib*/
EXCEPTION
/* penanganan eksepsi (ERROR) */
END ;
Contoh Stored Procedure 1 :
Berikut adalah salah satu contoh Stored Procedure yang mengisi field nilai pada tabel deret1 dengan nilai deret 0, 2, 4, … , 100.
CREATE OR REPLACE PROCEDURE prc_isi_deret1 AS
v_counter INTEGER;
BEGIN
v_counter := 0;
loop
DBMS_OUTPUT.PUT_LINE(‘Nilai Counter Saat ini = ‘||v_counter);
INSERT INTO deret1(nilai) VALUES (v_counter);
v_counter := v_counter + 2;
IF (v_counter > 100) THEN
EXIT;
END IF;
END LOOP;
END prc_isi_deret1;
/
Eksekusi Stored Procedure diatas :
EXEC rpc_isi_deret1;
Catatan :
untuk memperlihatkan output dari DBMS_OUTPUT.PUT_LINE anda harus terlebih dahulu mengeset variable system SERVEROUTPUT menjadi ON dengan menuliskan perintah :
SET SERVEROUTPUT ON
Cek isi tabel deret1 yang sudah di modifikasi :
SELECT * FROM deret1;
Menghapus Stored Procedure
DROP PROCEDURE ;
Contoh :ngosongin table: truncate table deret1;
DROP PROCEDURE prc_isi_deret1;
b) Function
Membuat Stored Function
Badan Function (Secara Umum)
CREATE OR REPLACE FUNCTION RETURN
AS
/* Deklarasi variables, types, dan local subprograms. */
BEGIN
/* procedural and SQL masuk disini*/
/* blok ini yang wajib*/
EXCEPTION
/* penanganan eksepsi (ERROR) */
RETURN();
/* kalusa return ini wajib di deklarasikan */
END;
Contoh Stored Function 1:
Berikut adalah salah satu contoh Stored Function yang mencari nilai maksimum pada tabel deret1.
CREATE OR REPLACE FUNCTION func_max_deret1 RETURN NUMBER
AS
v_maksimum INTEGER;
BEGIN
SELECT MAX(nilai) INTO v_maksimum FROM deret1;
RETURN (v_maksimum);
END func_max_deret1;
/
Eksekusi Stored Function diatas dengan cara:
SELECT func_max_deret1 FROM dual;
Catatan :
table dual pada oracle adalah “table semu” yang dapat digunakan untuk berbagai operasi yang membutuhkan table acuan semu seperti contoh diatas.
Fungsi dapat dipanggil melalui sintax SQL, contoh :
SELECT nilai, func_max_deret1 from deret1;
Menghapus Stored Function
DROP FUNCTION ;
Contoh :
DROP FUNCTION func_max_deret1;
0 comments:
Post a Comment