INTERACTING WITH THE ORACLE SERVER PL/SQL (Procedural

INTERACTING WITH THE ORACLE SERVER
PL/SQL (Procedural Language/Structured Query Language) merupakan sebuah penggabungan antara
bahasa pemrograman prosedural (PL) dan SQL syntax.
PL/SQL adalah fasilitas yang disediakan Oracle sehingga pengguna dapat memanfaatkan konsep
pemrograman. Dalam PL/SQL dapat digunakan perintah untuk memanipulasi data yang ada dalam
database Oracle. PL/SQL membentuk pemrograman terstruktur dalam memproses data.
PL/SQL Block Structure
Sebuah blok PL/SQL terdiri atas 3 bagian, yaitu:
1. Bagian declarative dimulai dengan keyword DECLARE dan berakhir pada saat bagian executable
dimulai. Bagian ini berisi deklarasi dari semua variabel, constant, cursor, dan user-defined
exception yang direferensi pada bagian executable dan bagian exception handling. Bagian ini
bersifat opsional pada penulisan blok PL/SQL.
2. Bagian executable dimulai dengan keyword BEGIN dan diakhiri dengan keyword END. Bagian
executable dari sebuah blok PL/SQL dapat memuat sejumlah blok PL/SQL lain, perintah SQL
untuk mengambil data dari database, dan perintah PL/SQL untuk memanipulasi data pada block.
3. Bagian exception handling termasuk dalam bagian executable dan dimulai dengan keyword
EXCEPTION. Bagian exception handling menspesifikasikan tindakan yang akan dilakukan ketika
terjadi error dan kondisi abnormal pada bagian eksekusi. Bagian ini juga bersifat opsional pada
penulisan blok PL/SQL.
PL/SQL Variable
Susunan kode untuk mendeklarasikan sebuah variabel PL./SQL adalah sebagai berikut:
Syntax
identifier
CONSTANT
Penjelasan
Nama variabel
Membuat nilai pada variabel tidak dapat dirubah. (nilai pada
variabel harus langsung diinisiasi/diisi pada saat deklarasi)
datatype
NOT NULL
expr syntax
Adalah tipe data dari variabel (bisa tipe data scalar, composite,
reference, atau LOB)
Membuat variabel tidak bisa kosong/ dibuat bernilai NULL.
(nilai pada variabel harus langsung diinisiasi/diisi pada saat
deklarasi)
Adalah ekspresi PL/SQL yang dapat berupa ekspresi literal,
variabel lain, atau ekspresi Penjelasan yang mengandung
operator dan function.
Contoh deklarasi variabel
DECLARE
emp_hiredate DATE;
emp_deptno NUMBER(2) NOT NULL := 10;
location VARCHAR2(13) := ‘Atlanta’;
c_comm CONSTANT NUMBER := 1400;
emp_lname employees.last_name%TYPE;
balance NUMBER(7,2);
min_balance balance%TYPE := 100;
...
Contoh PL/SQL Block (1)
DECLARE
fname VARCHAR2(50);
BEGIN
Fname := ‘Amalia Putri Melati’ ;
DBMS_OUTPUT.PUT_LINE('My name is '||fname);
END;
/
Contoh PL/SQL Block (2)
DECLARE
val1 number(2):= 3;
val2 number(2) := 30;
result number(2);
BEGIN
result := val1 + val2;
DBMS_OUTPUT.PUT_LINE('The result is '||result);
END;
DBMS_OUTPUT.PUT_LINE adalah keyword yang digunakan mencetak/menuliskan nilai dalam
variabel atau mencetak/menuliskan teks.
Selain menggunakan tandase “:=”, variabel juga dapat diisi melalui sebuah query SELECT untuk
mengisi variabel dengan nilai dari sebuah kolom atau tabel. Susunan kode untuk mengisi variabel
melalui query SELECT adalah sebagai berikut:
Sintax :
SELECT select_list
INTO (variable_name[, variable_name] …| record_name}
FROM table
[WHERE condition]
Syntax
select_list
variable_name
record_name
table
condition
Penjelasan
Adalah daftar dari kolom yang akan diambil nilainya dan dapat
meliputi row functions, group functions, atau Ekspresi SQL.
Adalah variabel bertipe scalar yang akan menampung nilai yang
diambil
Adalah obyek PL/SQL RECORD yang akan menampung nilai yang
diambil
Nama dari tabel pada database yang diambil datanya.
Bisa terdiri atas nama kolom, ekspresi, constant, dan operasi
perbandingan yang meliputi variabel atau PL/SQL constant.
Contoh :
DECLARE
full_name varchar2(50);
BEGIN
SELECT first_name||' '||last_name
INTO full_name FROM EMPLOYEES WHERE employee_id = 100;
END;
Conditional Flow
Sintax :
IF condition THEN
statements;
[ELSIF condition THEN
statements;]
[ELSE
statements;]
END IF;
Contoh :
DECLARE
umur number(3);
BEGIN
umur := 18;
IF umur < 18 THEN
DBMS_OUTPUT.PUT_LINE('Umur Anak-anak');
ELSIF umur >= 18 THEN
DBMS_OUTPUT.PUT_LINE('Umur Dewasa');
END IF;
END;
Iteration Flow
Basic Loop  Sintax :
LOOP
Statement1;
...
EXIT [WHEN condition];
END LOOP ;
While Loop  Sintax :
WHILE condition LOOP
Statement1;
Statement2;
...
END LOOP ;
For Loop  Sintax :
FOR counter IN [REVERSE]
lower_bound .. Upper_bound LOOP Statement1;
Statement2;
...
END LOOP ;
CURSOR FOR LOOPS
Cursor yang dideklarasikan dan dikelola dan dimanipulasi melalui bagian executable pada blok
PL/SQL, dan digunakan pada query SQL yang mengembalikan nilai lebih dari 1 baris data.
Sintax :
DECLARE
CURSOR cursor_name IS
select_statement;
BEGIN
FOR record_name IN cusror_name LOOP
statement1;
statement2;
...
END LOOP;
END;
Contoh :
DECLARE
CURSOR deptku IS
SELECT d.department_id, d.department_name,
AVG(e.salary) as avg_sal, COUNT(e.employee_id) as n_emp
FROM employees e, departments d
WHERE d.department_id = e.department_id
GROUP BY d.department_id, d.department_name;
BEGIN
FOR drec IN deptku LOOP
DBMS_OUTPUT.PUT_LINE('ID Department: ' || drec.department_id);
DBMS_OUTPUT.PUT_LINE('Nama Department: ' || drec.department_name);
DBMS_OUTPUT.PUT_LINE('Rata-rata Gaji: ' || drec.avg_sal);
DBMS_OUTPUT.PUT_LINE('Jumlah Karyawan: ' || drec.n_emp);
DBMS_OUTPUT.PUT_LINE(CHR(9));
END LOOP;
END;
PROCEDURE
Procedure (dan function) adalah blok PL/SQL yang memiliki nama, atau dikenal juga sebagai
subprogram. Subprogram di-compile dan disimpan dalam database. Subprogram tidak hanya dapat
dideklarasikan pada level schema saja, tetapi juga di dalam blok PL/SQL lain.
Anonymous block
Blok PL/SQL tanpa nama
di-compile setiap waktu
Tidak tersimpan pada database
Tidak dapat dipanggil oleh aplikasi lain.
Tidak mengembalikan nilai parameter
Subprograms
Blok PL/SQL dengan nama
di-compile satu kali
Tersimpan pada database
Memiliki nama, sehingga dapat dipanggil oleh
aplikasi lain
Subprogram
bisa
mengembalikan
nilai
(Function) paramater
Sebuah procedure dipanggil dengan menggunakan nama procedure pada bagian executable dari blok
PL/SQL lain, atau dengan menggunakan perintah EXEC [nama procedure].
Sintax :
CREATE [OR REPLACE] PROCEDURE procedure_name
[(parameter1 [mode] datatype1, parameter2 [mode] datatype2,...
parameter [mode] datatypeN)] IS|AS
[local_variable_declarations;...]
BEGIN
--actions;
END [procedure_name];
Catatan dalam pembuatan procedure:
 Opsi REPLACE menunjukkan bahwa, jika procedure yang akan dibuat sudah ada (terdapat
procedure dengan nama yang sama persis dengan nama procedure yang akan dibuat), maka
procedure tersebut akan di-drop (dihapus) dan digantikan dengan procedure baru yang dibuat.
 [local_variable_declarations;...] sama dengan bagian declarative pada anonymous block
 Actions sama dengan bagian executable pada anonymous block. Dimulai dengan keyword BEGIN
dan diakhiri dengan keyword END atau END [nama procedure]
 Parameter1 menggambarkan nama dari sebuah parameter.
 [mode] menentukan bagaimana parameter akan digunakan.
 datatype1 menentukan tipe data dari parameter (dituliskan tanpa precision/maximum length).
Contoh 1 :
Membuat procedure dengan nama “Coba”
CREATE OR REPLACE PROCEDURE Coba IS
BEGIN
DBMS_OUTPUT.PUT_LINE (‘Selamat Pagi');
END;
Contoh 2 :
CREATE OR REPLACE PROCEDURE Data_Pasien IS
vnama varchar(100);
valmt varchar(100);
begin
select nm_pas,almt_pas into vnama,valmt
from pasien
where kd_pas = 'P029';
dbms_output.put_line('Namanya : '||vnama);
dbms_output.put_line('Alamatnya : '||valmt);
end;
Sebuah procedure yang sudah tersimpan di dalam database dapat dijalankan dengan dua cara, yaitu:
 Dengan menggunakan sebuah anonymous block
 Dengan menggunakan procedure atau subprogram PL/SQL lain.
Contoh menjalankan sebuah procedure pada anonymous block adalah sebagai berikut:
BEGIN
Data_Pasien ;
END;
Anonymous block di atas menjalankan procedure Data_Pasien (contoh sebelumnya). Contoh
menjalankan sebuah procedure pada subprogram lain, adalah sebagai berikut:
CREATE OR REPLACE PROCEDURE cetak_data_pasien IS
BEGIN
DBMS_OUTPUT.PUT_LINE (‘Berikut Data Pasien);
Data_Pasien;
END;
Ketika sebuah procedure sudah tidak dibutuhkan, maka procedure tersebut dapat dihapus, dengan
perintah
DROP PROCEDURE procedure_name
PROCEDURE DENGAN PARAMETER
Terdapat tiga macam mode yang dapat didefinisikan pada sebuah parameter.
 IN
Parameter dengan mode IN hanya akan memindahkan nilai dari pemanggil subprogram ke dalam
subprogram.
Contoh :
CREATE OR REPLACE PROCEDURE Data_Pasien_In (pkode IN varchar) IS
vnama varchar(100);
valmt varchar(100);
vlhr date;
begin
select nm_pas,almt_pas,lhr_pas into vnama,valmt,vlhr
from pasien
where kd_pas = pkode;
dbms_output.put_line('Namanya : '||vnama);
dbms_output.put_line('Alamatnya : '||valmt);
end;
Eksekusi :
BEGIN
Data_Pasien_In (‘P029’);
END;
 OUT
Parameter dengan mode OUT hanya akan memindahkan nilai dari subprogram ke pemanggil
subprogram.
Contoh :
CREATE OR REPLACE PROCEDURE Data_Pasien_Out (pkode IN varchar, ptampung OUT number) IS
vnama varchar(100);
valmt varchar(100);
vlhr date;
begin
select nm_pas,almt_pas,lhr_pas into vnama,valmt,vlhr
from pasien
where kd_pas = pkode;
ptampung := round((sysdate-vlhr)/365);
dbms_output.put_line('Namanya : '||vnama);
dbms_output.put_line('Alamatnya : '||valmt);
end;
Eksekusi :
DECLARE
vumur NUMBER;
BEGIN
Data_Pasien_Out(‘P029’, vumur);
DBMS_OUTPUT.PUT_LINE('Umur pasien adalah: ' || vumur);
END;
 IN OUT
Parameter dengan mode IN OUT akan memindahkan nilai dari pemanggil subprogram ke dalam
subprogram, dan memungkinkan untuk memindahkan nilai yang berbeda dari subprogram ke
pemanggil subprogram, dengan menggunakan satu parameter yang sama.
Contoh :
CREATE OR REPLACE PROCEDURE format_login (plogin IN OUT VARCHAR2, pkode IN VARCHAR2) IS
BEGIN
SELECT nm_pas INTO plogin FROM pasien WHERE kd_pas = pkode;
plogin := substr(plogin,1,instr(plogin,' ')-1)||pkode;
END;
Eksekusi :
DECLARE
plogin VARCHAR2(50);
BEGIN
format_login(plogin, ‘P029’);
DBMS_OUTPUT.PUT_LINE(plogin);
END;
FUNCTION
Sebuah function adalah sebuah subprogram atau blok PL/SQL yang memiliki nama, yang dapat
menerima parameter, dapat dipanggil, dan HARUS mengembalikan sebuah nilai.
Procedure
Dieksekusi sebagai sebuah kalimat PL/SQL
Tidak memuat klausa RETURN pada bagian
header
Dapat mengembalikan nilai (jika diperlukan)
melalui parameter keluaran
Dapat memuat sebuah kalimat RETURN tanpa
sebuah nilai
Function
Dipanggil sebagai bagian dari sebuah ekspresi
Harus memuat klausa RETURN pada header
Harus mengembalikan sebuah nilai
Harus memuat minimal 1 kalimat RETURN
Sintax :
CREATE (OR REPLACE) FUNCTION function_name [(parameter1 [mode1] datatype1, ...)]
RETURN datatype IS|AS
[local_variable_declarations; ...]
BEGIN
--actions;
RETURN expression;
END [function_name];
Catatan pembuatan stored function:
 Keyword OR REPLACE berfungsi sama dengan pada procedure, yaitu jika terdapat function yang
sama dengan function yang akan dibuat, maka function tersebut akan dihapus dan akan digantikan
oleh function yang dibuat.
 Datatype pada klausa RETURN tidak boleh mencantumkan spesifikasi ukuran
(precision/maximum_length).
 [local_variable_declarations;...] sama dengan bagian declarative pada anonymous block
 Actions sama dengan bagian executable pada anonymous block. Dimulai dengan keyword BEGIN
dan diakhiri dengan keyword END atau END [nama function]
 Harus terdapat minimal 1 kalimat RETURN pada bagian executable dari function.
Contoh :
CREATE OR REPLACE FUNCTION getGrade (sal IN employees.salary%TYPE)
RETURN CHAR IS
grade CHAR(1);
BEGIN
IF sal BETWEEN 5000 AND 10000 THEN
grade := 'C';
ELSIF sal BETWEEN 10100 AND 15000 THEN
grade := 'B';
ELSIF sal >= 15100 THEN
grade := 'A';
ELSE
grade := 'U';
END IF;
RETURN grade;
END;
Eksekusi :
DECLARE
vid employees.employee_id%TYPE;
vsal employees.salary%TYPE;
vgrade CHAR(1);
BEGIN
vid := &empID;
SELECT salary INTO vsal FROM employees
WHERE employee_id = vid;
vgrade := getGrade(vsal);
DBMS_OUTPUT.PUT_LINE('Grade salary dari karyawan dengan ID ' || vid ||' adalah ' ||
vgrade);
END;
Cara lain eksekusi :
SELECT employee_id, getFullName(employee_id) "full_name", salary , getGrade(salary)
from employees where department_id = 50;
Ketika sebuah function sudah tidak dibutuhkan, maka stored function tersebut dapat dihapus, dengan
perintah
DROP FUNCTION function_name;
TRIGGER
Trigger adalah blok PL/SQL atau prosedur yang berhubungan dengan table, view, skema atau
database yang dijalankan secara implicit pada saat terjadi sebuah event.
Sintak penulisan dari database trigger, berisi komponen berikut :
1. Trigger timing :
a. Untuk tabel : BEFORE, AFTER
b. Untuk view : INSTEAD OF
2. Trigger event : INSERT, UPDATE atau DELETE
3. Nama tabel : yaitu nama tabel atau view yang berhubungan dengan trigger
4. Tipe trigger : Baris atau Pernyataan (statement)
5. klausa WHEN : untuk kondisi pembatasan
6. trigger body : bagian prosedur yang dituliskan pada trigger
Sintax :
CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER} triggering_event [referencing_clause]
[WHEN trigger_condition]
[FOR EACH ROW]
Trigger_body;
Dimana trigger_name adalah nama trigger, triggering_event menspesifikasikan event yang firing
(menyalakan) trigger, dan trigger_body adalah kode utama untuk trigger. Referencing_clause
digunakan untuk menunjuk pada data dalam baris yang saat ini sedang dimodifikasi dengan nama
yang berbeda. Jika ada trigger_condition dalam klausa WHEN, pertama akan dievaluasi dan kemudian
trigger_body dieksekusi hanya jika hasil evaluasi bernilai TRUE.
Tabel berikut menunjukkan tipe dari trigger Data Manipulating Language
Contoh :
CREATE TABLE PERSON
(
ID INT,
NAME VARCHAR(30),
DOB DATE,
PRIMARY KEY(ID)
);
CREATE OR REPLACE TRIGGER PERSON_INSERT_BEFORE
BEFORE INSERT ON PERSON
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE(’BEFORE INSERT OF ’ || :NEW.NAME);
END;
INSERT INTO PERSON (ID,NAME,DOB) VALUES (1,’BENNY SETIAWAN’,SYSDATE);
Beberapa event pada trigger bisa dikombinasikan dalam sebuah trigger dengan menggunakan
predikat kondisional INSERTING, UPDATING dan DELETING.
Pada Row Trigger, nilai dari kolom sebelum dan sesudah perubahan data dapat dirujuk dengan
menggunakan OLD dan NEW qualifier. OLD dan NEW hanya digunakan pada Row Trigger. OLD dan
NEW menggunakan prefiks (:) untuk pernyataan dalam perintah SQL. Jika qualifier ini terlibat dalam
pembatasan kondisi pada klausa WHEN, maka tidak digunakan prefiks (:).
Row triggers akan menurunkan unjuk kerja jika banyak dilakukan update pada table yang cukup
besar.
CREATE OR REPLACE
TRIGGER PERSON_BIUD
BEFORE INSERT OR UPDATE OR DELETE ON PERSON
FOR EACH ROW
BEGIN
IF INSERTING THEN
DBMS_OUTPUT.PUT_LINE(’INSERTING PERSON: ’ || :NEW.NAME);
ELSIF UPDATING THEN
DBMS_OUTPUT.PUT_LINE(’UPDATING PERSON: ’ ||
:OLD.NAME || ’ TO ’ || :NEW.NAME);
ELSIF DELETING THEN
DBMS_OUTPUT.PUT_LINE(’DELETING PERSON: ’ || :OLD.NAME);
END IF;
END;
LATIHAN
1.
Tampilkan hasil perhitungan ini dengan ketentuan variabel vsatu = 3000, vdua = 500, vtiga =
4000. Tampilan outputnya adalah :
Jika :
variabel 1 adalah : 3000
variabel 2 adalah : 500
variabel 3 adalah : 4000
Maka :
Hasil vtiga-vsatu+vdua adalah = 1500
2.
Tampilkan output sebagai berikut untuk Dokter dengan kode ‘D006’:
Data Dokter
----------------Nama Dokter : [nama]
Alamat : [alamat]
Spesialis : [spesialis]
Golongan Darah : [golongan darah]
Tanggal Lahir : [tanggal lahir]
3.
Tampilkan output sebagai berikut untuk Pasien dengan kode sesuai inputan !
Ketentuan golongan pasien adalah : Muda jika usianya kurang dari atau sama dengan 30 tahun
dan Tua jika usianya lebih dari 30 tahun.
Pasien dengan nama Sofwan Prahasto Rakhmadi
Berjenis kelamin Pria
Tergolong pasien Muda karena usianya 17 tahun
4.
Tampilkan output satuan obat sesuai jenis satuan yang dinputkan sebagai berikut :
Jumlah stok obat untuk jenis BOTOL adalah 675
5.
Buatlah rekap obat sebagai berikut dengan menggunakan cursor :
KD_OBAT
NAMA
O001
amcillin 500 mg
O002
STOK_OBAT
SATUAN
NICK
2000
BUTIR
BTR
amcillin syr 60 ml
200
BOTOL
BTL
O003
amoxicillin 60 ml
300
BOTOL
BTL
O004
ampicillin 60 ml
45
BOTOL
BTL
O005
baquinor f
1000
BUTIR
BTR
O006
benoson cr 5 gr
TUB
TB
30
6.
Tampilkan data pasien dan dokternya untuk yang sakit ‘Alergi’ seperti contoh berikut :
Hasil Diagnosa : Alergi
Pasien : Rohraga Mofid | Dokter : dr. Dwiana Yuniarti | Tanggal Periksa : 24-OCT-14
Pasien : Yuli Setyowati | Dokter : dr. Krisma Novealianty | Tanggal Periksa : 24-OCT-14
Pasien : Antonius Titis Sukarno | Dokter : dr. Marlensius A.Wijaya | Tanggal Periksa : 10-OCT-14
7.
Tampilkan rekap data dokter yang berusia lebih dari atau sama dengan 17 tahun, urutkan
berdasarkan seperti contoh berikut ini :
KODE DOKTER
D016
NAMA DOKTER
dr. Vika Fransiska Candra
JENIS KELAMIN
Wanita
USIA
34
D011
dr. Delianae, Sp.PD
Wanita
34
D008
dr. Asna Nasiqah, Sp.PD
Wanita
34
D006
dr. Dewi Fatmi Januarini, Sp.PD
Wanita
34
D007
dr. Hananie Taufik, SpOG
Wanita
24
D017
dr. Ronald Ronaldo
Pria
24
D019
dr. Krisma Novealianty
Wanita
21
D020
dr. Dwiana Yuniarti
Wanita
17