Pengenalan PL/SQL

PL/SQL adalah kependekan dari “Procedural Language extension to SQL”. Pada pembahasan kali ini kita akan meninjau fitur-fitur utama PL/SQL dan melihat keuntungan-keuntungan yang ditawarkannya. Kita juga akan mempelajari konsep-konsep dasar dibalik PL/SQL dan gambaran umum program-program PL/SQL. Kita akan melihat bagaimana PL/SQL menjembatani celah antara teknologi database dan bahasa pemrograman prosedural.

Pada pembahasan kali ini kita akan mempelajari topik-topik berikut:

• Memahami fitur-fitur utama PL/SQL
• Arsitektur PL/SQL
• Keuntungan PL/SQL

1.1. 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.

BEGIN
  SELECT quantity INTO qty_on_hand FROM inventory
  WHERE product = 'TENNIS RACKET'
  FOR UPDATE OF quantity;
  IF qty_on_hand > 0 THEN -- check quantity
    UPDATE inventory SET quantity = quantity - 1
    WHERE product = 'TENNIS RACKET';
    INSERT INTO purchase_record
    VALUES ('Tennis racket purchased', SYSDATE);
  ELSE
  INSERT INTO purchase_record
  VALUES ('Out of tennis rackets', SYSDATE);
  END IF;
  COMMIT;
END;

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).

1.1.1. Struktur Blok

PL/SQL merupakan bahasa block-structured (berstruktur blok). Yaitu bahwa unit-unit dasar (procedure, function, dan blok tanpa nama (anonymous block))  yang membentuk program PL/SQL merupakan blok logikal, yang mana dapat berisi beberapa sub-sub blok bersarang (nested-blocks). Biasanya, setiap blok-blok logikal berhubungan dengan problem dan subproblem yang akan dipecahkan. Dengan demikian, PL/SQL mendukung pendekatan divide-and-conquer (membagi dan mengatasi) untuk penyelesaian masalah yang disebut dengan stepwise refinement.

Blok (atau sub-blok) mengijinkan kita untuk mengelompokkan secara logikal deklarasi-deklarasi dan perintah-perintah yang terkait. Dengan demikian, kita dapat meletakkan deklarasi-deklarasi tersebut dekat dengan tempat dimana mereka digunakan.

Seperti pada Gambar 1-1, blok PL/SQL memiliki tiga bagian: bagian declarative, bagian executable, dan bagian exception-handling. (Dalam PL/SQL, peringatan dan kondisi error disebut dengan exception). Bagian executable merupakan bagian yang mutlak harus ada pada suatu blok PL/SQL.

Urutan dari bagian-bagian tersebut adalah logikal. Pertama dimulai dari bagian declarative, dimana item-item dapat dideklarasikan. Sekali dideklarasikan, item-item dapat dimanipulasi di dalam bagian executable. Exception yang muncul selama proses eksekusi dapat ditangani di dalam bagian exception-handling.

Gambar 1-1. Struktur Blok

Kita dapat menyarangkan atau meletakkan sub-sub blok di dalam bagian executable dan exception-handling dari sebuah blok PL/SQL, namun kita tidak dapat melakukannya di dalam bagian declarative. Kita juga dapat mendefinisikan subprogram lokal di dalam bagian declarative pada suatu blok. Namun, kita hanya dapat memanggil subprogram-subprogram lokal tersebut dari blok dimana mereka dedefinisikan.

1.1.2. Variable dan Konstanta

PL/SQL mengijinkan kita untuk mendeklarasikan konstanta dan variable, lalu menggunakannya di dalam SQL dan perintah-perintah prosedural 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.

1.1.2.1. Mendeklarasikan Variable

Variable dapat memiliki tipe data SQL, seperti CHAR, DATE, atau NUMBER, atau tipe data PL/SQL, seperti BOOLEAN atau BINARY_INTEGER. Sebagai contoh, asumsikan bahwa kita ingin mendeklarasikan variable dengan nama part_no untuk menyimpan 4 digit angka dan variable dengan nama in_stock untuk menyimpan nilai Boolean TRUE atau FALSE. Kita mendeklarasikan variable-variable tersebut dengan cara sebagai berikut:

part_no  NUMBER(4);
in_stock BOOLEAN;

Kita dapat pula mendeklarasikan nested tables, variable-size arrays (varrays), serta record dengan menggunakan tipe-tipe data komposit TABLE, VARRAY, dan RECORD.

1.1.2.2. Memberikan Nilai ke Variable

Kita dapat memberikan nilai ke variable dengan tiga cara. Pertama dengan menggunakan assignment operator (:=), yaitu titik dua diikuti dengan sama dengan. Kita meletakka variable pada sisi 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 bawah ini, 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_sal

1.1.2.3. 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;

1.1.3. Cursor

Oracle menggunakan area-area kerja (work area) untuk mengeksekusi perintah-perintah PL/SQL dan menyimpan informasi yang sedang diproses. Konstruksi PL/SQL yang disebut cursor mengijinkan kita memberi nama sebuah area kerja dan mengakses informasi yang ada di dalamnya. Terdapat dua macam cursor: implisit dan explisit. PL/SQL secara implisit mendeklarasikan cursor untuk seluruh perintah-perintah manipulasi data SQL, termasuk query-query yang hanya menghasilkan satu baris data. Untuk query-query yang menghasilkan lebih dari satu baris data, kita dapat secara eksplisit mendeklarasikan cursor untuk memproses baris-baris data secara individual.

DECLARE
  CURSOR c1 IS
    SELECT empno, ename, job FROM emp WHERE deptno = 20;

Kumpulan baris-baris data yang dihasilkan oleh query yang menghasilkan banyak baris data disebut dengan result set. Besarnya adalah jumlah baris data sesuai dengan kriteria pencarian. Seperti yang ditunjukkan oleh Gambar 1-2, explicit cursor “menunjuk” kepada current row (baris terkini) di dalam result set.

Gambar 1–2 Query Processing

Query yang menghasilkan banyak baris data mirip dengan pemrosesan file. Sebagai contoh, program COBOL membuka file, memproses records, dan kemudian menutup file. Demikian pula, program PL/SQL membuka cursor, memproses baris-baris data yang dihasilkan oleh query, kemudian menutup cursor tersebut. Seperti halnya file pointer yang menandai posisi terkini di dalam file yang sedang terbuka, cursor menandai posisi terkini dari result set.

Kita menggunakan perintah-perintah OPEN, FETCH dan CLOSE untuk mengontrol cursor. Perintah OPEN mengeksekusi query yang terkait dengan cursor, mengidentifikasi result set, dan memposisikan cursor sebelum baris pertama (first row). Perintah FETCH menampilkan baris data terkini (current row) dan memajukan cursor ke baris berikutnya (next row). Ketika baris terakhir (last row) selesai diproses, perintah CLOSE men-disable cursor.

1.1.4. Cursor FOR Loops

Pada banyak situasi yang membutuhkan explicit cursor, secara sederhana kita dapat melakukan coding dengan menggunakan cursor FOR loop dibanding menggunakan  perintah OPEN, FETCH, dan CLOSE. Cursor FOR loop secara implisit mendeklarasikan loop index-nya sebagai record yang merepresentasikan baris data yang didapat database. Kemudian, ia membuka cursor, secara berulangkali mengambil nilai-nilai baris data dari result set ke field-field pada record, kemudian menutup cursor ketika seluruh baris data telah selesai diproses. Pada contoh berikut ini, cursor FOR loop secara implisit mendeklarasikan emp_rec sebagai sebuah record:

DECLARE
  CURSOR c1 IS
    SELECT ename, sal, hiredate, deptno FROM emp;

  ...

BEGIN

  FOR emp_rec IN c1 LOOP
  ...
  salary_total := salary_total + emp_rec.sal;

END LOOP;

Untuk mereferensi field-field tunggal di dalam record, kita menggunakan dot notation, dimana dot (.) berlaku sebagai penyeleksi komponen.

1.1.5. Cursor Variable

Seperti halnya cursor, cursor variable menunjuk kepada baris terkini (current row) di dalam result set dari multi-row query. Tetapi, tidak seperti cursor, cursor variable dapat dibuka untuk type-compatible query. Ia tidak terikat dengan kepada query tertentu. Cursor variable benar-benar merupakan variable PL/SQL, diaman kita dapat memberikan nilai baru dan melewatkannya ke subprogram-subprogram di dalam database Oracle. Hal ini memberikan kita fleksibilitas lebih dan cara yang tepat untuk memusatkan proses menampilkan data.

Biasanya, kita membuka cursor variable dengan melewatkannya ke stored procedure yang mendeklarasikan cursor variable sebagai satu dari parameter-parameter formalnya. Procedure berikut ini membuka cursor variable yang bernama generic_cv untuk query yang dipilih:

PROCEDURE open_cv (generic_cv IN OUT GenericCurTyp,choice NUMBER) IS
BEGIN
  IF choice = 1 THEN
    OPEN generic_cv FOR SELECT * FROM emp;
  ELSIF choice = 2 THEN
    OPEN generic_cv FOR SELECT * FROM dept;
  ELSIF choice = 3 THEN
    OPEN generic_cv FOR SELECT * FROM salgrade;
  END IF;
  ...
END;

1.1.6. Attributes

Variable dan cursor PL/SQL memiliki attributes, yang merupakan property yang mengijinkan kita mereferensi tipe data dan struktur dari item tanpa mengulangi definisinya. Column-column dan table-table database memiliki attribute yang serupa, dimana kita dapat menggunakannya untuk kemudahan pemeliharaan. Tanda persen (%) bertindak sebagai indikator attribute.

1.1.6.1. %TYPE

Attribute %TYPE menyediakan tipe data untuk variable atau kolom database. Hal ini khususnya berguna ketika mendeklarasikan variable-variable yang akan menyimpan nilai-nilai database. Sebagai contoh, asumsikan bahwa ada kolom dengan nama title di dalam table books. Untuk mendeklarasikan variable bernama my_title yang memiliki tipe data sama dengan kolom title, gunakan dot notation (.) dan attribute %TYPE, seperti berikut ini:

my_title books.title%TYPE;

Mendeklarasikan my_title dengan %TYPE memiliki dua keuntungan. Pertama, kita tidak perlu tahu tipe data sebenarnya dari kolom title. Kedua, jika kita mengubah definisi database untuk title (misalnya memanjangkan nilai string-nya), tipe data my_title berubah secara otomatis mengikuti perubahan tipe data kolom title.

1.1.6.2. %ROWTYPE

Di dalam PL/SQL, record digunakan untuk mengelompokkan data. Record terdiri dari sejumlah field-field terkait dimana nilai-nilai data disimpan. Attribute %ROWTYPE menyediakan tipe record yang merepresentasikan baris data pada table. Record dapat menyimpan seluruh baris data yang di-select dari table atau dihasilkan dari cursor atau cursor variable.

Kolom di dalam baris data dan field-field yang terkait dengannya di dalam record memiliki nama dan tipe data yang sama. Di dalam contoh di bawah ini, kita mendeklarasikan record bernama dept_rec. Field-field-nya memiliki nama-nama dan tipe-tipe data yang sama dengan kolom-kolom pada table dept.

DECLARE

dept_rec dept%ROWTYPE; -- declare record variable

Gunakan tanda titik (.) untuk mereferensi field-field, seperti ditunjukkan oleh contoh berikut ini:

my_deptno := dept_rec.deptno;

Jika kita mendeklarasikan cursor yang menampilkan last name, salary, hire date dan job title dari employee, kita dapat menggunakan %ROWTYPE untuk mendeklarasikan record yang menyimpan informasi yang sama, seperti berikut ini:

DECLARE
  CURSOR c1 IS
    SELECT ename, sal, hiredate, job FROM emp;
  emp_rec c1%ROWTYPE; -- declare record variable that represents
  -- a row fetched from the emp table

Ketika kita mengeksekusi perintah:

FETCH c1 INTO emp_rec;

nilai di dalam kolom ename dari table emp diberikan ke field ename dari emp_rec, nilai dari kolom sal diberikan ke field sal, dan begitu seterusnya.

Gambar 1-3 Record %ROWTYPE

1.1.7. Control Structures

Control structures merupakan perluasan paling penting PL/SQL untuk SQL. Tidak hanya PL/SQL mengijinkan kita untuk memanipulasi data Oracle, ia juga mengijinkan kita untuk memproses data menggunakan perintah-perintah conditional, iterative, dan sequential flow-of-control seperti IF-THEN-ELSE, CASE, FOR-LOOP, WHILE-LOOP, EXIT-WHEN, dan GOTO. Secara bersama-sama, perintah-perintah ini dapat menangani berbagai situasi permasalahan.

1.1.7.1. Conditional Control

Seringkali, penting untuk melakukan aksi alternatif bergantung kepada kondisi permasalahannya. Perintah IF-THEN-ELSE mengijinkan kita untuk memproses perintah secara kondisional. Klausa IF mengecek kondisi; klausa THEN mendefinisikan apa yang akan dilakukan jika kondisi benar (TRUE); klausa ELSE mendefinisikan apa yang akan dilakukan jika kondisi salah (FALSE) atau null.

Pertimbangkan program berikut ini, yang memproses transaksi bank. Sebelum mengijinkan kita untuk menarik dana $500 dari rekening 3, program akan memastikan apakah rekening tersebut memiliki dana yang cukup untuk ditarik. Jika dana cukup, program akan men-debit rekening. Sebaliknya, jika tidak, program akan mencatatnya ke dalam audit table.

DECLARE
acct_balance NUMBER(11,2);
acct CONSTANT NUMBER(4) := 3;
debit_amt CONSTANT NUMBER(5,2) := 500.00;
BEGIN
  SELECT bal INTO acct_balance
  FROM accounts
  WHERE account_id = acct
  FOR UPDATE OF bal;

  IF acct_balance >= debit_amt THEN
    UPDATE accounts
    SET bal = bal - debit_amt
    WHERE account_id = acct;
  ELSE
    INSERT INTO temp
    VALUES(acct, acct_balance, 'Insufficient funds');
    -- insert account, current balance, and message
  END IF;
  COMMIT;
END;

Untuk memilih diantara beberapa nilai atau rangkaian aksi, kita dapat menggunakan konstruksi CASE. Ekspresi CASE mengevaluasi kondisi dan menghasilkan nilai untuk setiap kasus (case). Perintah case mengevaluasi kondisi dan melakukan aksi (mungkin dapat berupa seluruh blok PL/SQL) untuk setiap kasus.

-- This CASE statement performs different actions based
-- on a set of conditional tests.
CASE
  WHEN shape = 'square' THEN area := side * side;
  WHEN shape = 'circle' THEN
  BEGIN
    area := pi * (radius * radius);
    DBMS_OUTPUT.PUT_LINE('Value is not exact because pi is irrational.');
  END;
  WHEN shape = 'rectangle' THEN
    area := length * width;
  ELSE
  BEGIN
    DBMS_OUTPUT.PUT_LINE('No formula to calculate area of a' ||shape);
    RAISE PROGRAM_ERROR;
  END;
END CASE;

Rangkaian perintah yang menggunakan hasil-hasil query untuk memilih alternatif aksi adalah umum di dalam aplikasi-aplikasi database. Rangkaian umum lainnya menambahkan atau menghapus baris data hanya jika entry terkait ditemukan di dalam table lain. Kita dapat mengikat rangkaian-rangkaian umum ini ke dalam blok PL/SQL dengan menggunakan logika kondisional.

Oracle Magazine contains technology strategy articles, sample code, tips, Oracle and partner news, how to articles for developers and DBAs, and more. Oracle (NASDAQ: ORCL) is the world’s largest enterprise software company.

For more information about Oracle, please visit their Web site at http://www.oracle.com. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.

Get Free Oracle Magazine

1.1.7.2. Iterative Control

Perintah-perintah LOOP mengijinkan kita untuk mengeksekusi perintah-perintah hingga beberapa kali. Kita meletakkan keyword LOOP sebelum perintah pertama di dalam rangkaian perintah dan keyword END LOOP setelah perintah terakhir dalam rangkaian perintah tersebut. Contoh berikut ini menunjukkan kepada kita bentuk loop sederhana, dimana mengulang rangkaian perintah-perintah secara terus kontinyu:

LOOP
  -- sequence of statements
END LOOP;

Perintah-perintah FOR-LOOP mengijinkan kita untuk menentukan jangkauan integer, lalu mengeksekusi rangkaian perintah-perintah sekali untuk setiap integer di dalam jangkauan tersebut. Sebagai contoh, loop berikut ini menambahkan 500 angka beserta nilai kuadratnya ke dalam database table:

FOR num IN 1..500 LOOP
  INSERT INTO roots VALUES (num, SQRT(num));
END LOOP;

Perintah WHILE-LOOP menghubungkan kondisi dengan rangkaian perintah-perintah. Sebelum setiap perulangan di dalam loop, kondisi dievaluasi. Jika kondisi benar (true), rangkaian perintah-perintah akan dieksekusi, lalu kontrol akan mulai lagi dari puncak loop. Jika kondisi salah (false) atau null, loop dilewati dan kontrol menuju kepada perintah selanjutnya.

DECLARE
salary emp.sal%TYPE := 0;
mgr_num emp.mgr%TYPE;
last_name emp.ename%TYPE;
starting_empno emp.empno%TYPE := 7499;
BEGIN
  SELECT mgr INTO mgr_num
  FROM emp
  WHERE empno = starting_empno;
  WHILE salary <= 2500 LOOP
    SELECT sal, mgr, ename INTO salary, mgr_num, last_name
    FROM emp
    WHERE empno = mgr_num;
  END LOOP;
  INSERT INTO temp VALUES (NULL, salary, last_name);
  COMMIT;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      INSERT INTO temp VALUES (NULL, NULL, 'Not found');
  COMMIT;
END;

Perintah EXIT-WHEN mengijinkan kita untuk menyelesaikan loop jika perintah selanjutnya tidak mungkin atau tidak diinginkan. Ketika perintah EXIT ditemui, kondisi di dalam klausa WHEN dievaluasi. Jika kondisi benar (true), loop diselesaikan dan kontrol menuju kepada perintah selanjutnya. Di dalam contoh berikut ini, loop diselesaikan ketika nilai dari total melampaui 25000.

LOOP
  ...
  total := total + salary;
  EXIT WHEN total > 25000; -- exit loop if condition is true
END LOOP;
-- control resumes here

1.1.7.3. Sequential Control

Perintah GOTO mengijinkan kita untuk mencabangkan label secara tidak terkondisi. Label, identifier yang tidak terdeklarasikan, ditutup dengan tanda kurung persegi ganda, harus mendahului perintah executable atau blok PL/SQL. Ketika dieksekusi, perintah GOTO mentransfer kontrol kepada perintah atau blok berlabel, seperti yang ditunjukkan oleh contoh berikut ini:

IF rating > 90 THEN
  GOTO calc_raise; -- branch to label
END IF;

...

<>

IF job_title = 'SALESMAN' THEN -- control resumes here
  amount := commission * 0.25;
ELSE
  amount := salary * 0.10;
END IF;

1.1.8. Modularity

Modularity mengijinkan kita untuk memecah aplikasi menjadi modul-modul yang mudah di-manage, dan didefinisikan dengan baik. Selama proses penyaringan, kita dapat mengurangi problem yang kompleks menjadi kumpulan problem-problem sederhana yang memiliki solusi-solusi mudah untuk diimplementasikan. PL/SQL menjawab kebutuhan ini dengan program units, yang mengandung blok-blok, subprogram-subprogram, dan package-package.

1.1.8.1. Subprogram

PL/SQL memiliki dua tipe subprogram yang dinamakan procedure dan function, yang mana mengambil parameter dan dipanggil. Seperti contoh berikut ini, subprogram seperti program kecil (miniature program), yang diawali dengan header dan diikuti dengan bagian opsional declarative, bagian executable, dan bagian opsional exception-handling.

PROCEDURE award_bonus (emp_id NUMBER) IS
bonus REAL;
comm_missing EXCEPTION;
BEGIN -- executable part starts here
  SELECT comm * 0.15 INTO bonus FROM emp WHERE empno = emp_id;
  IF bonus IS NULL THEN
    RAISE comm_missing;
  ELSE
    UPDATE payroll SET pay = pay + bonus WHERE empno = emp_id;
  END IF;
  EXCEPTION -- exception-handling part starts here
  WHEN comm_missing THEN
  ...
END award_bonus;

Ketika dipanggil, procedure ini menerima kode karyawan. Kode tersebut digunakan untuk mendapatkan komisi karyawan dari database table, dan, pada saat yang sama, menghitung bonus 15%. Lalu, procedure akan mengecek total bonus. Jika bonus adalah null, exception akan tampil; sebaliknya, data penggajian karyawan akan di-update.

1.1.8.2. Packages

PL/SQL mengijinkan kita untuk mem-bundle secara logikal terhadap tipe-tipe, variable-variable, cursor-cursor, dan subprogram-subprogram yang saling terkait ke dalam sebuah package.

Package biasanya memiliki dua bagian: yaitu spesification dan body. Specification merupakan antarmuka untuk aplikasi-aplikasi kita; ia mendeklarasikan tipe, konstanta, variable, exception, cursor, dan subprogram yang tersedia untuk digunakan. Body mendefinisikan cursors dan subprogram dan kemudian mengimplementasikan spesification.

CREATE PACKAGE emp_actions AS -- package specification
PROCEDURE hire_employee (empno NUMBER, ename CHAR, ...);
PROCEDURE fire_employee (emp_id NUMBER);
END emp_actions;

CREATE PACKAGE BODY emp_actions AS -- package body
PROCEDURE hire_employee (empno NUMBER, ename CHAR, ...) IS
BEGIN
  INSERT INTO emp VALUES (empno, ename, ...);
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 diakses oleh aplikasi. Detail implementasi di dalam package body tersembunyi dan tidak dapat diakses.

Package dapat di-compile dan disimpan di dalam database Oracle, dimana isinya dapat digunakan secara bersama-sama oleh banyak aplikasi. Ketika kita memanggil subprogram yang ter-packaged untuk pertama kalinya, seluruh package akan di-load ke memory. Selanjutnya, pemanggilan berikutnya terhadap subprogram-subprogram yang terdapat di dalam package tersebut tidak lagi memerlukan disk I/O. Dengan demikian, package dapat meningkatkan produktifitas dan meningkatkan performa.

1.1.9. Data Abstraction

Data abstraction mengijinkan kita untuk mengekstrak property-property penting dari data sambil mengabaikan detil-detil yang tidak perlu. Sekali kita mendesain struktur data, kita dapat melupakan detilnya dan fokus terhadap algoritma desain yang memanipulasi struktur data tersebut.

1.1.9.1. Collections

Tipe-tipe collection yaitu TABLE dan VARRAY membolehkan kita untuk mendeklarasikan index berdasarkan table (index-by tables), table bersarang (nested tables), dan array berukuran fleksibel (variable-size arrays). Collection merupakan kelompok elemen-elemen terurut, yang seluruhnya memiliki tipe sama. Setiap elemen memiliki subscript unik yang menentukan posisinya di dalam collection.

Untuk mereferensi elemen, gunakan sintaks subscripting standar. Sebagai contoh, pemanggilan berikut mereferensi elemen kelima di dalam table bersarang / nested table (dengan tipe Staff) yang dihasilkan oleh function new_hires:

DECLARE
TYPE Staff IS TABLE OF Employee;
staffer Employee;
FUNCTION new_hires (hiredate DATE) RETURN Staff IS
BEGIN ... END;
BEGIN
  staffer := new_hires('10-NOV-98')(5);
  ...

END;

Collections bekerja seperti array yang dapat kita temui pada bahasa pemrograman generasi ke 4. Juga, collections dapat dilewatkan sebagai parameter. Sehingga, kita dapat menggunakannya untuk memindahkan kolom-kolom dari data ke dalam dan keluar dari table-table database atau diantara aplikasi-aplikasi client-side dan stored subprograms.

1.1.9.2. Records

Kita dapat menggunakan attribute %ROWTYPE untuk mendeklarasikan record yang merepresentasikan baris data di dalam table atau baris data yang dihasilkan dari sebuah cursor. Tetapi, dengan record yang didefinisikan oleh user (user-defined record), kita dapat mendeklarasikan field-field milik kita.

Records mengandung field-field bernama yang unik, yang mana dapat memiliki tipe-tipe data yang berbeda. Bayangkan kita memiliki berbagai macam data employee, seperti name, salary, dan hire date. Item-item ini tidak sama tetapi secara logikal saling terkait. Record yang mengandung field untuk setiap item mengijinkan kita untuk memperlakukan data sebagai unit logikal.

Perhatikan contoh berikut ini:

DECLARE
TYPE TimeRec IS RECORD (hours SMALLINT, minutes SMALLINT);
TYPE MeetingTyp IS RECORD (
date_held DATE,
duration TimeRec, -- nested record
location VARCHAR2(20),
purpose VARCHAR2(50));

Perhatikan bahwa kita dapat menyarangkan records. Sehingga, sebuah record dapat menjadi komponen dari record yang lain.

1.1.9.3. Object Types

Di dalam PL/SQL, object-oriented programming adalah berbasiskan pada object types. Object type meng-enkapsulasi struktur data yang berhubungan dengan functions dan procedures yang diperlukan untuk memanipulasi data tersebut. Variable-variable yang membentuk struktur data disebut attributes. Functions dan procedures yang mengkaraktersisasi kelakuan (behavior) dari object type dinamakan methods.

Object type menekan kompleksitas dengan memecah sistem yang besar ke dalam entity-entity logikal. Hal ini mengijinkan kita untuk menciptakan komponen-komponen software yang modular, maintainable, dan reusable.

Ketika kita mendefinisikan object type menggunakan perintah CREATE TYPE (di dalam SQL*Plus sebagai contoh), kita menciptakan template yang abstrak untuk sebuah objek di dunia nyata (real-word object). Seperti pada contoh tentang rekening bank berikut ini, template menentukan hanya attributes dan behaviors yang akan dibutuhkan oleh objek di dalam lingkungan aplikasi.

CREATE TYPE Bank_Account AS OBJECT (
acct_number INTEGER(5),
balance REAL,
status VARCHAR2(10),
MEMBER PROCEDURE open (amount IN REAL),
MEMBER PROCEDURE verify_acct (num IN INTEGER),
MEMBER PROCEDURE close (num IN INTEGER, amount OUT REAL),
MEMBER PROCEDURE deposit (num IN INTEGER, amount IN REAL),
MEMBER PROCEDURE withdraw (num IN INTEGER, amount IN REAL),
MEMBER FUNCTION curr_bal (num IN INTEGER) RETURN REAL
);

Pada saat runtime, ketika struktur data diisi dengan nilai, kita harus menciptakan instance dari rekening bank yang abstrak. Kita dapat menciptakan instances (disebut objects) sebanyak yang kita butuhkan. Setiap object memiliki nomor, saldo dan status dari rekening bank sesungguhnya.

1.1.10. Information Hiding

Dengan information hiding, kita hanya melihat detil yang relevan pada suatu level desain algoritma dan struktur data. Information hiding menjaga keputusan desain high-level tetap terpisah dengan detil desain low-level, yang mana lebih sering untuk diubah.

1.1.10.1. Algorithms

Kita menerapkan information hiding untuk algoritma melalui top-down design. Sekali kita mendefinisikan spesifikasi tujuan dan antarmuka dari low-level procedure, kita dapat mengabaikan detil-detil implementasi. Mereka disembunyikan pada level-level yang lebih tinggi. Sebagai contoh, implementasi dari procedure dengan nama raise_salary disembunyikan. Seluruh yang perlu kita ketahui adalah procedure akan menambah gaji karyawan dengan nilai tertentu. Perubahan-perubahan terhadap definisi raise_salary adalah jelas untuk aplikasi-aplikasi yang memanggilnya.

1.1.10.2. Data Structures

Kita menerapkan information hiding untuk data structures melalui data encapsulation. Dengan membagun kumpulan dari subprogram-subprogram utility untuk struktur data, kita mengisolasinya dari user-user dan developer-developer yang lain. Dengan cara itu, para developer mengetahui bagaimana menggunakan subprogram-subprogram yang mengoperasikan data structure namun tidak mengetahui bagaimana struktur tersebut direpresentasikan.

Dengan package-package PL/SQL, kita dapat menentukan apakah subprogram-subprogram bersifat public atau private. Sehingga, package menjalankan data encapsulation dengan membiarkan kita meletakkan definisi subprogram di dalam kotak hitam. Definisi private adalah tersembunyi dan tidak dapat diakses. Hanya package, bukannya aplikasi kita, yang terpengaruh jika definisi tersebut berubah. Hal ini menyederhanakan pemeliharaan dan pengembangannya.

1.1.11. Error Handling

PL/SQL membuatnya mudah untuk mendeteksi dan memproses kondisi-kondisi yang telah didefinisikan terlebih dahulu atau yang didefinisikan oleh user, yang dinamakan dengan exceptions. Ketika error muncul, exception akan ditampilkan. Sehingga, eksekusi normal akan menghentikan dan mengontrol transfer ke bagian exception-handling (penanganan kesalahan) dari blok atau subprogram PL/SQL kita. Untuk menangani exception yang muncul, kita menulis rutin terpisah yang dinamakan dengan exception handlers.

Eksepsi-eksepsi yang telah didefinisikan sebelumnya tampil secara implisit bersamaan dengan berjalannya sistem. Sebagai contoh, jika kita berusaha membagi sebuah angka dengan nol, PL/SQL akan memunculkan predefined exception ZERO_DEVIDE secara otomatis. Kita harus menampilkan eksepsi yang kita definisikan (user-defined exceptions) secara eksplisit dengan perintah RAISE.

Kita dapat mendefinisikan exceptions kita sendiri pada bagian declarative dari setiap blok  PL/SQL atau subprogram. Di dalam bagian executable, kita mengecek kondisi yang membutuhkan perhatian khusus. Jika kita menemukan bahwa kondisi tersebut ada, kita mengeksekusi perintah RAISE. Pada contoh berikut ini, kita menghitung bonus yang diperoleh oleh tenaga penjualan. Bonus tersebut berdasarkan gaji dan komisi. Sehingga, jika komisi tersebut adlaah null, kita menampilkan eksepsi comm_missing.

DECLARE
  ...
  comm_missing EXCEPTION; -- mendeklarasikan exception
BEGIN
  ...
  IF commission IS NULL THEN
    RAISE comm_missing; -- memunculkan exception
  END IF;
  bonus := (salary * 0.10) + (commission * 0.15);
  EXCEPTION
    WHEN comm_missing THEN ... -- memproses the exception

1.2. PL/SQL Architecture

Kompilasi dan sistem runtime dari PL/SQL adalah teknologi, bukan produk yang berdiri sendiri. Pikirkan teknologi ini sebagai engine yang meng-compile dan mengeksekusi blok-blok PL/SQL dan subprogram. Engine dapat diinstall di dalam Oracle server atau di dalam aplication development tool seperti Oracle Forms atau Oracle Reports. Sehingga, PL/SQL dapat berada di dua lingkungan:

• Oracle database server
• Oracle tools

Dua lingkungan ini adalah independen. PL/SQL di-bundle dengan Oracle server tetapi mungkin tidak tersedia di beberapa tool. Pada salah satu lingkungan tersebut, engine PL/SQL menerima sebagai input untuk setiap blok PL/SQL dan subprogram yang valid. Gambar 1-4 menunjukkan engine PL/SQL sedang memproses blok tanpa nama (anonymous block). Engine mengeksekusi perintah-perintah procedural tetapi mengirimkan perintah-perintah SQL ke SQL Statement Executor di dalam Oracle server.

Gambar 1-4 PL/SQL Engine

1.2.1. Oracle Database Server

Application development tool yang tidak memiliki engine PL/SQL lokal harus mempercayakan kepada Oracle untuk memproses blok-blok dan subprogram-subprogram PL/SQL. Karena ia memiliki engine PL/SQL, Oracle server dapat memproses blok-blok dan subprogram-subprogram PL/SQL seperti halnya perintah-perintah SQL.

1.2.1.1. Anonymous Blocks

Anonymous PL/SQL blocks dapat dilekatkan di dalam Oracle Precompiler atau program OCI (Oracle Call Interface). Saat runtime, program tersebut, tanpa engine PL/SQL lokal, mengirimkan blok-blok ini ke Oracle server, dimana mereka di-compile dan dieksekusi. Demikian juga, tool-tool interaktif seperti SQL*Plus dan Enterprise Manager, yang tidak memiliki engine PL/SQL lokal, harus mengirimkan blok-blok tanpa nama tersebut ke Oracle server.

1.2.1.2. Stored Subprograms

Subprogram-subprogram dapat di-compile secara terpisah dan disimpan secara permanen di dalam database Oracle, dan siap untuk dieksekusi. Subprogram secara eksplisit di-CREATE dengan menggunakan tool yang disebut dengan stored subprogram. Sekali di-compile dan disimpan di dalam data dictionary, ia merupakan schema object, yang mana dapat direferensi atau digunakan oleh aplikasi-aplikasi yang terhubung dengan database.

Stored subprogram (subprogram yang tersimpan di database) yang didefinisikan di dalam package disebut packaged subprograms. Jika subprogram didefinisikan secara bebas atau berdiri sendiri, maka dinamakan standalone subprograms. Dan jika subprogram didefinisikan di dalam subprogram yang lain atau di dalam blok PL/SQL, maka dinamakan local subprograms, yang mana tidak dapat digunakan oleh aplikasi-aplikasi lain serta ia ada hanya untuk melengkapi blok yang menyertainya.

Stored subprograms menawarkan produktifitas lebih, performa yang lebih baik, penghematan memori, keutuhan aplikasi, dan keamanan yang lebih ketat. Sebagai contoh, dengan mendesain aplikasi-aplikasi diantara banyaknya stored procedures dan functions, kita dapat meniadakan kejadian dimana kita menulis kode program yang sama berkali-kali, karena dengan subprogram kita hanya perlu menulis sekali dan meletakkannya di database, sehingga seluruh aplikasi dapat saling berbagi menggunakannya, dan pada akhirnya hal ini akan meningkatkan produktifitas kita.

Kita dapat memanggil stored subprograms dari database trigger, stored program lainnya, aplikasi Oracle Precompiler, aplikasi OCI, atau secara interaktif melalui SQL*Plus atau Enterprise Manager. Sebagai contoh, kita dapat memanggil procedure create_dept dari SQL*Plus dengan cara:

SQL> CALL create_dept('FINANCE', 'NEW YORK');

Subprograms disimpan dalam keadaan terkompilasi. Sehingga, ketika dipanggil, mereka akan di-load dan segera dilewatkan ke engine PL/SQL. Juga, mereka memiliki keuntungan dalam hal pembagian memory. Sehingga, hanya satu copy dari subprogram saja yang perlu di-load ke memori untuk dieksekusi oleh banyak user secara bersamaan.

1.2.1.3. Database Triggers

Database trigger adalah stored subprogram yang terkait dengan database table, view, atau sebuah event (kejadian). Kita dapat memerintahkan Oracle untuk mengeksekusi trigger secara otomatis sebelum atau sesudah perintah INSERT, UPDATE, atau DELETE berpengaruh terhadap table. Satu dari banyak penggunaan trigger adalah untuk meng-audit modifikasi data. Sebagai contoh, trigger pada level table berikut ini dieksekusi kapanpun gaji pada table emp di-update.

CREATE TRIGGER audit_sal
  AFTER UPDATE OF sal ON emp
  FOR EACH ROW
BEGIN
  INSERT INTO emp_audit VALUES ...
END;
/

Bagian executable dari trigger dapat mengandung perintah-perintah prosedural dan juga perintah-perintah Data Manipulation Language dari SQL.  Disamping trigger-trigger pada level table, terdapat pula trigger-trigger instead-of untuk view dan trigger-trigger system-event untuk schema-schema.

1.2.2. Oracle Tools

Jika memiliki engine PL/SQL, maka application development tool dapat memproses blok-blok dan subprogram-subprogram PL/SQL. Tool tersebut akan melewatkan blok-blok ke engine PL/SQL lokalnya. Engine mengeksekusi seluruh perintah-perintah prosedural pada sisi aplikasi dan hanya mengirimkan perintah-perintah SQL ke Oracle. Dengan demikian, kebanyakan pekerjaan dilakukan pada sisi aplikasi, tidak pada sisi server.

Selanjutnya, jika blok tidak mengandung perintah-perintah SQL, engine mengeksekusi seluruh blok pada sisi aplikasi. Hal ini berguna jika aplikasi kita dapat diuntungkan dari kontrol conditional dan iterative.

Seringkali, aplikasi-aplikasi Oracle Forms menggunakan perintah-perintah SQL hanya untuk mengetes nilai dari masukan-masukan field atau untuk melakukan perhitungan-perhitungan sederhana. Dengan menggunakan PL/SQL, kita dapat mengabaikan panggilan-panggilan ke Oracle Server. Lebih baik lagi, kita dapat menggunakan function-function PL/SQL untuk memanipulasi masukan-masukan terhadap sebuah field.

1.3. Keuntungan PL/SQL

PL/SQL merupakan bahasa untuk memproses transaksi berperforma tinggi yang menawarkan keuntungan-keuntungan sebagai berikut:

• Mendukung SQL
• Mendukung object-oriented programming
• Performa lebih baik
• Produktifitas lebih tinggi
• Portabilitas penuh
• Integrasi dengan Oracle
• Keamanan yang baik

1.3.1. Mendukung SQL

SQL telah menjadi bahasa basis data standar karena ia fleksibel, powerful, dan mudah dipelajari. Beberapa perintah-perintah seperti bahasa Inggris seperti SELECT, INSERT, UPDATE, dan DELETE membuatnya mudah untuk memanipulasi data yang tersimpan di dalam basis data relasional (relational database).

SQL adalah non-prosedural, yang berarti bahwa kita dapat menetapkan apa yang ingin kita lakukan tanpa menentukan bagaimana melakukannya. Oracle yang akan menentukan cara terbaik untuk melayani permintaan kita. Tidak ada koneksi yang tidak perlu antara perintah-perintah yang berurutan karena Oracle mengeksekusi perintah SQL sekali pada satu waktu.

PL/SQL mengijinkan kita untuk menggunakan seluruh perintah-perintah data manipulation, cursor control, dan transaction control SQL begitu pula dengan functions, operators, dan pseudocolumns. Sehingga, kita dapat memanipulasi data secara lebih fleksibel dan aman. Juga, PL/SQL mendukung penuh tipe-tipe data SQL. Hal ini mengurangi perlunya mengkonversi data yang dilewatkan diantara aplikasi-aplikasi kita dan database.

PL/SQL juga mendukung SQL dinamis (dynamic SQL), sebuah teknik pemrograman tingkat lanjut yang membuat aplikasi-aplikasi kita lebih fleksibel dan serbaguna. Program-program kita dapat memproses perintah-perintah data definition, data control, dan session control  SQL secara “on the fly” saat runtime.

1.3.2. Mendukung Object-Oriented Programming

Object types merupakan tool pemodelan berorientasi objek yang ideal, dimana kita dapat menggunakannya untuk mengurangi biaya dan waktu yang dibutuhkan untuk membangun aplikasi-aplikasi kompleks. Disamping juga membolehkan kita untuk menciptakan komponen-komponen software yang modular, maintainable, dan reusable, object types membolehkan juga tim-tim programmer yang berbeda untuk membangun komponen-komponen software secara berkelanjutan.

Dengan mengenkapsulasi operasi-operasi dengan data, object types mengijinkan kita untuk memindahkan kode-kode pemeliharaan data keluar dari script-script SQL dan blok-blok PL/SQL menuju kepada methods. Object types juga menyembunyikan detil-detil implementasi, sehingga kita dapat mengubah detil-detil tanpa mempengaruhi program-program pada sisi klien.

Sebagai tambahan, object types memperbolehkan pemodelan data yang realistis. Entitas-entitas kompleks pada dunia nyata beserta relasi-relasinya dipetakan secara langsung ke dalam object types. Hal ini membantu program-program kita secara lebih baik merefleksikan dunia yang berusaha disimulasikannya.

Gambar 1-5 PL/SQL Meningkatkan Performa

PL/SQL juga meningkatkan performa dengan menambahkan kekuatan pemrosesan prosedural kepada tool-tool Oracle. Dengan menggunakan PL/SQL, tool tersebut dapat melakukan suatu perhitungan secara lebih cepat dan efisien tanpa memanggil Oracle server.

1.3.3. Produktifitas Lebih Tinggi

PL/SQL menambahkan fungsionalitas untuk tool-tool non-prosedural seperti Oracle Forms dan Oracle Reports. Dengan PL/SQL di dalam tool-tool ini, kita dapat menggunakan konstruksi prosedural yang sudah lazim untuk membangun aplikasi.

Sebagai contoh, kita dapat menggunakan seluruh blok PL/SQL di dalam trigger pada Oracle Forms. Kita tidak perlu lagi menggunakan langkah-langkah trigger yang banyak, macros, atau user exits. Sehingga, PL/SQL meningkatkan produktifitas dengan meletakkan tool-tool yang lebih baik di tangan kita.

PL/SQL memiliki bentuk yang sama pada seluruh lingkungan. Segera setelah kita memahami PL/SQL dengan suatu tool Oracle, kita dapat menggunakan pengetahuan itu pada tool-tool yang lain, sehingga menggandakan keuntungan-keuntungan produktifitas. Sebagai contoh, script-script yang ditulis dengan satu tool dapat digunakan oleh tool-tool yang lain.

1.3.4. Portabilitas Penuh

Aplikasi-aplikasi yang ditulis dengan PL/SQL portabel untuk setiap sistem operasi dan platform dimana Oracle berjalan. Dengan kata lain, program-program PL/SQL dapat berjalan dimanapun Oracle dapat berjalan; kita tidak perlu memodifikasinya lagi untuk setiap lingkungan yang baru. Hal ini berarti kita dapat menulis library-library program yang portabel, yang mana dapat digunakan di dalam lingkungan-lingkungan yang berbeda.

1.3.5. Integritas dengan SQL

Bahasa-bahasa PL/SQL dan SQL terintegrasi penuh satu sama lain. PL/SQL mendukung seluruh tipe-tipe data SQL dan nilai NULL. Hal ini memperbolehkan kita memanipulasi data Oracle secara lebih mudah dan efisien. Sehingga juga membantu kita untuk menulis kode program yang memiliki performa tinggi.

Attribute-attribute %TYPE dan %ROWTYPE selanjutnya mengintegrasikan PL/SQL dengan SQL. Sebagai contoh, kita dapat menggunakan attribute %TYPE untuk mendeklarasikan variable-variable, berdasarkan deklarasi-deklarasi pada definisi-definisi  kolom-kolom database. Jika definisi berubah, deklarasi variable akan menyesuakan dengan perubahan tersebut. Definisi baru akan mengalami perubahan tanpa campur tangan kita. Hal ini menyediakan data independence, mengurangi biaya-biaya maintenance, dan mengijinkan program-program untuk beradaptasi dengan perubahan database untuk menyesuaikan dengan kebutuhan-kebutuhan bisnis.

1.3.6. Keamanan yang Baik

Stored procedures PL/SQL memungkinkan kita untuk menyekat logika aplikasi diantara client dan server. Dengan cara itu, kita dapat mencegah aplikasi-aplikasi client agar tidak memanipulasi data yang sensitif. Database triggers yang ditulis dengan PL/SQL dapat men-disable proses update oleh aplikasi secara selektif dan melakukan audit berdasarkan konten terhadap proses insert data yang dilakukan oleh user.

Selanjutnya, kita dapat membatasi akses ke data Oracle dengan mengijinkan user untuk memanipulasi data hanyak melalui stored procedures yang dieksekusi dengan hak pendefinisinya. Sebagai contoh, kita dapat memberikan akses kepada user untuk procedure yang meng-update table, tetapi tidak mengijinkan mereka untuk mengakses table tersebut secara langsung.

Silahkan melanjutkan membaca pembahasan Dasar-dasar PL/SQL