SQL DAN PL/SQL
PL/SQL adalah bahasa pemrograman yang merupakan perluasan dari Struktured Query Language. PL merupakan singkatan dari Prosedural Language. PL/SQL merupakan sebuah bahasa pemrograman 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.
Perbedaan SQL dan PL/SQL:
- SQL adalah bahasa deklaratif untuk database yg sudah distandarkan secara internasional (ANSI dan ISO), jadi seharusnya semua database yg menggunakan SQL standar akan memiliki statement/syntax yg sama, baik di Oracle, MySQL atau database lainnya. Tetapi beberapa database memang memiliki statement/syntax proprietary yg hanya dikenali di database itu sendiri dan ini tentu saja bukan standar SQL.
- Sementara PL/SQL adalah ekstensi dari SQL dengan menambahkan fungsi bahasa prosedural. Berbeda dengan SQL yg hanya mementingkan bagaimana mendapatkan hasil, dengan PL/SQL juga akan diketahui bagaimana proses untuk mendapatkan hasil tsb. Karena PL/SQL merupakan bahasa prosedural, maka statement/syntax yg digunakan sangat tergantung dari jenis bahasa yg diadopsi.
MySQL sendiri menggunakan standar ANSI untuk bahasa proseduralnya (SQL/PSM), dan kabarnya mengadopsi berbagai bahasa seperti Perl, Phyton, dan C. Apalagi sekarang MySQL sudah diakuisisi oleh SUN, mungkin bahasa Java juga sudah dimasukkan ke situ.
Istilah PL/SQL khusus untuk oracle saja. PL/SQL yang merupakan singkatan dari Procedural Language/Structured Query Language merupakan sebuah penggabungan antara bahasa pemrograman prosedural (PL) dan SQL syntax. Jadi dengan PL/SQL kita tidak perlu menggunakan sebuah bahasa pemrograman sendiri. fungsi-fungsi standard di bahasa pemrograman sudah ada di sini dan bisa langsung digabung dengan perintah SQL untuk memanipulasi database. Tapi PL/SQL cuma ada di database oracle saja.
Memahami Fitur-fitur Utama PL/SQL
Cara terbaik untuk mempelajari PL/SQL adalah dengan melihat sebuah contoh program.Program berikut ini memproses pemesanan raket tenis. Pertama, program mendeklarasikan variable bertipe NUMBER untuk menyimpan jumlah raket tenis yang tersedia. Kemudian, menampilkan jumlah raket tersedia dari table database yang bernama INVENTORY. Jika jumlah lebih besar daripada nol, program akan meng-update table dan menambahkan data pembelian ke dalam table lain yang bernama PURCHASE_RECORD. Sebaliknya, jika jumlah lebih kecil atau sama dengan nol, program akan menambahkan informasi bahwa stok tidak mencukupi ke dalam table PURCHASE_RECORD.
Dengan PL/SQL, kita dapat menggunakan perintah-perintah SQL untuk memanipulasi data pada database Oracle serta perintah-perintah flow-of-control untuk memproses data. Kita juga dapat mendeklarasikan konstanta dan variable, mendefinisikan procedure dan function, dan menangkap pesan kesalahan.Dengan demikian, PL/SQL mengkombinasikan kekuatan manipulasi data dari SQL dengan kekuatan pemrosesan data dari bahasa prosedural (procedural language).
PL/SQL package
Package adalah sebuah schema object yang mengelompokkan PL/SQL types, items, dan subprograms, yang terkait secara logikal. Packages biasanya memiliki dua bagian, sebuah specification dan sebuah body, meskipun kadangkala body tersebut tidak diperlukan. Specification (atau spec) merupakan antarmuka terhadap aplikasi-aplikasi kita; ia mendeklarasikan types, variables, constants, exceptions, cursors, dan subprograms yang tersedia untuk digunakan. Body secara penuh mendefinisikan cursors dan subprograms, dan juga mengimplementasikan spec.
Seperti ditunjukkan oleh Gambar 9-1, kita dapat memikirkan sebuah spec sebagai sebuah antarmuka operasional dan body sebagai sebuah “kotak hitam (black box)”. Kita dapat men-debug, meningkatkan, atau menimpa sebuah package body tanpa mengubah antarmuka (package spec) ke package tersebut.
Seperti ditunjukkan oleh Gambar 9-1, kita dapat memikirkan sebuah spec sebagai sebuah antarmuka operasional dan body sebagai sebuah “kotak hitam (black box)”. Kita dapat men-debug, meningkatkan, atau menimpa sebuah package body tanpa mengubah antarmuka (package spec) ke package tersebut.
Contoh Sebuah PL/SQL Package
Dalam contoh di bawah ini, kita mem-package sebuah record type, sebuah cursor, dan dua procedure kepegawaian. Perlu diingat bahwa procedure hire_employee menggunakan database sequence empno_seq dan function SYSDATE untuk menambahkan sebuah employee number baru dan hire date.
CREATE OR REPLACE PACKAGE emp_actions AS -- spec
TYPE EmpRecTyp IS RECORD (emp_id INT, salary REAL);
CURSOR desc_salary RETURN EmpRecTyp;
PROCEDURE hire_employee (
ename VARCHAR2,
job VARCHAR2,
mgr NUMBER,
sal NUMBER,
comm NUMBER,
deptno NUMBER);
PROCEDURE fire_employee (emp_id NUMBER);
END emp_actions;
Dalam contoh di bawah ini, kita mem-package sebuah record type, sebuah cursor, dan dua procedure kepegawaian. Perlu diingat bahwa procedure hire_employee menggunakan database sequence empno_seq dan function SYSDATE untuk menambahkan sebuah employee number baru dan hire date.
CREATE OR REPLACE PACKAGE emp_actions AS -- spec
TYPE EmpRecTyp IS RECORD (emp_id INT, salary REAL);
CURSOR desc_salary RETURN EmpRecTyp;
PROCEDURE hire_employee (
ename VARCHAR2,
job VARCHAR2,
mgr NUMBER,
sal NUMBER,
comm NUMBER,
deptno NUMBER);
PROCEDURE fire_employee (emp_id NUMBER);
END emp_actions;
CREATE OR REPLACE PACKAGE BODY emp_actions AS -- body
CURSOR desc_salary RETURN EmpRecTyp IS
SELECT empno, sal FROM emp ORDER BY sal DESC;
PROCEDURE hire_employee (
ename VARCHAR2,
job VARCHAR2,
mgr NUMBER,
sal NUMBER,
comm NUMBER,
deptno NUMBER) IS
BEGIN
INSERT INTO emp VALUES (empno_seq.NEXTVAL, ename, job, mgr,
SYSDATE, sal, comm, deptno);
END hire_employee;
CURSOR desc_salary RETURN EmpRecTyp IS
SELECT empno, sal FROM emp ORDER BY sal DESC;
PROCEDURE hire_employee (
ename VARCHAR2,
job VARCHAR2,
mgr NUMBER,
sal NUMBER,
comm NUMBER,
deptno NUMBER) IS
BEGIN
INSERT INTO emp VALUES (empno_seq.NEXTVAL, ename, job, mgr,
SYSDATE, sal, comm, deptno);
END hire_employee;
PROCEDURE fire_employee (emp_id NUMBER) IS
BEGIN
DELETE FROM emp WHERE empno = emp_id;
END fire_employee;
END emp_actions;
Hanya deklarasi di dalam package yang terlihat dan dapat dikses terhadap aplikasi. Detil implementasi di dalam package body disembunyikan dan tidak dapat diakses. Jadi, kita dapat mengubah body (implementation) tanpa harus meng-compile ulang program-program yang memanggilnya.
Variable dan Konstanta
PL/SQL mengijinkan kita untuk mendeklarasikan konstanta dan variable, lalu menggunakannya di dalam SQL dan perintah-perintah prosedura
BEGIN
DELETE FROM emp WHERE empno = emp_id;
END fire_employee;
END emp_actions;
Hanya deklarasi di dalam package yang terlihat dan dapat dikses terhadap aplikasi. Detil implementasi di dalam package body disembunyikan dan tidak dapat diakses. Jadi, kita dapat mengubah body (implementation) tanpa harus meng-compile ulang program-program yang memanggilnya.
Variable dan Konstanta
PL/SQL mengijinkan kita untuk mendeklarasikan konstanta dan variable, lalu menggunakannya di dalam SQL dan perintah-perintah prosedura
l dimanapun ekspresi dapat digunakan. Namun perlu diingat, forward reference tidak diperbolehkan. Maksudnya, kita harus mendeklarasikan konstanta dan variable sebelum mereferensinya (menggunakannya) di dalam perintah-perintah yang lain, termasuk pada perintah-perintah declarative lainnya.
[DECLARE
[DECLARE
- - - declarations]
BEGIN
- - - statemens
[EXCEPTION
- - - handlers]
END;
BEGIN
- - - statemens
[EXCEPTION
- - - handlers]
END;
Mendeklarasikan Variable
kiri operator dan ekspresi (dapat pula mengandung pemanggilan sebuah function) pada sisi kanan. Sedikit contoh-contohnya adalah sebagai berikut:
tax := price * tax_rate;
valid_id := FALSE;
bonus := current_salary * 0.10;
wages := gross_pay(emp_id, st_hrs, ot_hrs) - deductions;
Cara kedua untuk memberikan nilai ke variable adalah dengan melakukan select (atau fetch) terhadap nilai-nilai yang ada di dalam database ke variable tersebut. Pada contoh di bawah ini, kita mengharuskan Oracle untuk menghitung bonus sebesar 10% ketika kita menampilkan gaji karyawan. Sekarang, kita dapat menggunakan variable bonus di dalam proses perhitungan lainnya atau menambahkan nilainya ke table di dalam database.
SELECT sal * 0.10 INTO bonus FROM emp WHERE empno = emp_id;
Sedangkan cara ketiga untuk memberikan nilai ke variable adalah dengan melewatkannya sebagai parameter OUT atau IN OUT kepada sebuah subprogram. Seperti pada contoh di bawahini, parameter IN OUT mengijinkan kita untuk melewatkan nilai awal ke subprogram yang sedang dipanggil dan kemudian mengembalikan nilai perubahannya kepada pemanggilnya:
DECLARE
my_sal REAL(7,2);
PROCEDURE adjust_salary (emp_id INT, salary IN OUT REAL) IS ...
BEGIN
SELECT AVG(sal) INTO my_sal FROM emp;
adjust_salary(7788, my_sal); -- assigns a new value to my_sa
tax := price * tax_rate;
valid_id := FALSE;
bonus := current_salary * 0.10;
wages := gross_pay(emp_id, st_hrs, ot_hrs) - deductions;
Cara kedua untuk memberikan nilai ke variable adalah dengan melakukan select (atau fetch) terhadap nilai-nilai yang ada di dalam database ke variable tersebut. Pada contoh di bawah ini, kita mengharuskan Oracle untuk menghitung bonus sebesar 10% ketika kita menampilkan gaji karyawan. Sekarang, kita dapat menggunakan variable bonus di dalam proses perhitungan lainnya atau menambahkan nilainya ke table di dalam database.
SELECT sal * 0.10 INTO bonus FROM emp WHERE empno = emp_id;
Sedangkan cara ketiga untuk memberikan nilai ke variable adalah dengan melewatkannya sebagai parameter OUT atau IN OUT kepada sebuah subprogram. Seperti pada contoh di bawahini, parameter IN OUT mengijinkan kita untuk melewatkan nilai awal ke subprogram yang sedang dipanggil dan kemudian mengembalikan nilai perubahannya kepada pemanggilnya:
DECLARE
my_sal REAL(7,2);
PROCEDURE adjust_salary (emp_id INT, salary IN OUT REAL) IS ...
BEGIN
SELECT AVG(sal) INTO my_sal FROM emp;
adjust_salary(7788, my_sal); -- assigns a new value to my_sa
Mendeklarasikan Konstanta
Mendeklarasikan konstanta sama halnya dengan mendeklarasikan, kecuali bahwa kita harus menambahkan keyword CONSTANT dan segera memberikan nilai ke konstanta tersebut. Setelah itu, tidak diperbolehkan adanya pemberian nilai kembali kepada konstanta tersebut. Pada contoh beirkut ini, kita mendeklarasikan konstanta dengan nama credit_limit:
credit_limit CONSTANT REAL:= 5000.00;
Mendeklarasikan konstanta sama halnya dengan mendeklarasikan, kecuali bahwa kita harus menambahkan keyword CONSTANT dan segera memberikan nilai ke konstanta tersebut. Setelah itu, tidak diperbolehkan adanya pemberian nilai kembali kepada konstanta tersebut. Pada contoh beirkut ini, kita mendeklarasikan konstanta dengan nama credit_limit:
credit_limit CONSTANT REAL:= 5000.00;
