PL/SQL >>Pemrograman Generasi 4<<

Pendahuluan

* PL/SQL adalah peningkatan perintah SQL dalam bentuk pemrograman prosedural.
* DML dan SELECT dari perintah SQL berada dalam PL/SQL.
* PL/SQL Engine akan memecah perintah blok PL/SQL menjadi PL/SQL (dikerjakan oleh Procedural Statement Executor dari PL/SQL Engine) dan SQL (dikerjakan oleh SQL Statement Executor dari Oracle Server).

Keuntungan Penggunaan PL/SQL

Integrasi
Memungkinkan Aplikasi dan Oracle Server menggunakan Library yang dapat digunakan bersama-sama dalam bentuk stored procedure.


Peningkatan Kinerja
Penggunaan PL/SQL, memungkinkan pengiriman perintah secara blok dalam satu perintah yang secara drastis dapat menurunkan trafik jaringan.

Modular
* Bentuk modular memungkinkan banyak kemudahan yang dapat diperoleh.

Portable
* PL/SQL adalah sarana yang asli dimiliki oleh Oracle Server, artinya, program PL/SQL dapat dengan mudah dipindahkan dari satu Oracle Server ke Oracle Server lainnya, meskipun dengan OS atau platform yang berbeda.

Dapat mendeklarasikan Variable
* Dapat berupa Variable, Constant, Cursor dan Exception.
* Penggunaan variable/identifier sangat memudahkan dalam membuat suatu perintah. Misalkan untuk membaca hasil suatu perintah SELECT untuk diproses dan hasilnya digunakan untuk melakukan proses lainnya.


Dapat menggunakan Struktur Kontrol
* LOOP, FOR, WHILE


Dapat menangani Error
* Error bisa dihasilkan/dibangkitkan oleh Oracle Server atau bisa dibangkitkan sendiri dengan sengaja.
* Untuk mengantisipasi berbagai hal yang barangkali sulit untuk ditangani secara langsung menggunakan kontrol program.


Pemanggilan PL/SQL
* Langsung dari SQL-Plus
* Dari Aplikasi
* Dari PL/SQL lainnya


Deklarasi Variabel

Struktur PL/SQL
* [DECLARE]
- [Variable, cursor, user-defined exception]
* BEGIN
- SQL
- PL/SQL
* [EXCEPTION]
- [Action]
* END

Format Deklarasi Variabel
* pengenal [CONSTANT] tipe [NOT NULL] [:= | DEFAULT nilai];
* tipe dapat menggunakan atribut %TYPE

Aturan Penulisan Perintah PL/SQL
* Dapat ditulis dalam beberapa baris (tidak harus dalam satu baris perintah)
* Dapat berupa nested blok
* Karakter dan literal diapit oleh tanda kutip
* Setiap perintah/blok diakhiri dengan titik-koma (;)
* Komentar diawali dengan tanda min dua-kali (--) atau diapit dengan tanda /* … */
* Pemberian nilai menggunakan :=
* Dapat menggunakan Bind Variable
* Tanda garis miring (/), berarti ‘RUN’

Contoh program PL/SQL

VARIABLE CC NUMBER
DECLARE
A NUMBER;
B NUMBER;
BEGIN
A := 100;
B := 20.25;
:CC := A + B;
END;
/
PRINT CC

SET SERVEROUTPUT ON
DECLARE
A NUMBER;
B NUMBER;
C NUMBER;
BEGIN
A := 100;
B := 20.25;
C := A + B;
DBMS_OUTPUT.PUT_LINE(’A + B = ’||C);
END;
/

Berhubungan dengan Oracle Server

* Mengambil Data, menggunakan SELECT
- Harus ditambahkan klausa INTO
- SELECT kolom INTO variable FROM table [WHERE kondisi];
- Harus menghasilkan 1 baris data (bisa beberapa kolom)
- Dapat menggunakan SQL Cursor
. Cursor adalah cara untuk menunjuk suatu baris data pada suatu table
. Ada dua jenis cursor, Inplisit dan Eksplisit. Oracle server menggunakan jenis Implisit (perintah SQL)

* Manipulasi Data
- Semua perintah DML dan Transaksi dapat digunakan
. INSERT, UPDATE, DELETE, . . .
. COMMIT, ROLLBACK

Contoh SELECT dalam PL/SQL
SET SERVEROUTPUT ON
DECLARE
JUMLAH NUMBER;
BEGIN
SELECT COUNT(*) INTO JUMLAH FROM TAB;
DBMS_OUTPUT.PUT_LINE(’JUMLAH TABEL = ’||JUMLAH);
END;
/



SET SERVEROUTPUT ON
DECLARE
GAJI NUMBER;
BEGIN
SELECT SAL INTO GAJI FROM EMP WHERE ENAME=’ADAMS’;
DBMS_OUTPUT.PUT_LINE(’GAJI ADAMS = ’||GAJI);
END;
/


Contoh DML dalam PL/SQL

DECLARE
GAJI NUMBER;
BEGIN
SELECT SAL INTO GAJI FROM EMP WHERE ENAME=’ADAMS’;
UPDATE EMP SET SAL=GAJI WHERE ENAME=’SCOTT’;
END;
/

Penulisan Struktur Kontrol

* Pernyataan IF
- IF ... THEN ... END IF;
- IF ... THEN ... ELSE ... END IF;
- IF ... THEN ... ELSIF ... END IF;

* Pernyataan CASE
- CASE selector
^ WHEN ekspresi THEN hasil
^ ...
^ ELSE hasil
- END;

Contoh IF dalam PL/SQL

SET SETVEROUTPUT ON
DECLARE
J NUMBER;
JUM VARCHAR(20);
BEGIN
SELECT COUNT(*) INTO J FROM TAB;
IF J=1 THEN
JUM := ’SATU’;
ELSIF J=2 THEN
JUM := ’DUA’;
ELSIF J=3 THEN
JUM := ’TIGA’;
ELSIF J=4 THEN
JUM := ’EMPAT’;
ELSIF J=5 THEN
JUM := ’LIMA’;
END IF;
DBMS_OUTPUT.PUT_LINE(‘JUMLAH TABEL = ’||J||’ (’||JUM||’)’);
END;
/


Contoh Case dalam PL/SQL

SET SETVEROUTPUT ON
DECLARE
J NUMBER;
JUM VARCHAR(20);
BEGIN
SELECT COUNT(*) INTO J FROM TAB;
JUM := CASE J
WHEN 1 THEN ’SATU’
WHEN 2 THEN ’DUA’
WHEN 3 THEN ’TIGA’
WHEN 4 THEN ’EMPAT’
WHEN 5 THEN ’LIMA’
END;
DBMS_OUTPUT.PUT_LINE(’JUMLAH TABEL = ’||J||’ (’||JUM||’)’);
END;
/

Kontrol - LOOP

* LOOP Sederhana
- LOOP
^ pernyataan;
^ EXIT [WHEN kondisi];
- END LOOP;
* FOR LOOP
- FOR var IN [REVERSE] awal..akhir LOOP
^ pernyataan;
^ END LOOP;
* WHILE LOOP
- WHILE kondisi LOOP
^ pernyataan;
- END LOOP;

Contoh For … Loop

SET SERVEROUTPUT ON
DECLARE
N NUMBER;
BEGIN
FOR N IN 1..10 LOOP
DBMS_OUTPUT.PUT_LINE(N);
END LOOP;
END;
/

Tipe Data RECORD

RECORD
* TYPE nama_record IS RECORD (
^ deklarasi variable);
- variable nama_record
- Catatan : bisa menggunakan atribut table.kolom%TYPE atau table%ROWTYPE

Contoh:
* type rpegawai is record (
- nama varchar(50),
- NIP varchar(15)
- GAJI EMP.SAL%TYPE);
* pegawai rpegawai;
* PEGAWAI EMP%ROWTYPE;

Contoh Tipe Data Record

SET SERVEROUTPUT ON
DECLARE
E EMP%ROWTYPE;
BEGIN
SELECT * INTO E FROM EMP WHERE ENAME=’ADAMS’;
DBMS_OUTPUT.PUT_LINE(’NOMOR = ’||E.EMPNO);
DBMS_OUTPUT.PUT_LINE(’NAMA = ’||E.ENAME);
DBMS_OUTPUT.PUT_LINE(’JOB = ’||E.JOB);
DBMS_OUTPUT.PUT_LINE(’GAJI = $’||E.SAL);
END;
/


SQL Cursor

* Suatu area memori yang dibuka setiap Oracle Server menjalankan perintah SQL
* Terdapat dua jenis Cursor
- Implisit
Digunakan setiap Oracle Server menjalankan perintah SQL
- Eksplisit
Digunakan secara langsung dalam pemrograman

Penulisan Cursor secara Eksplisit

* Setiap operasi SQL yang dijalankan oleh SERVER memiliki cursor masing-masing
* Ada 2 cursor:
1. implisit - selalu digunakan oleh oracle saat menjalankan perintah DML dan SELECT
2. eksplisit - dibuat oleh programmer

* Penggunaan:
DECLARE - OPEN - FETCH - repeat fetch while not EMPTY - CLOSE
* Deklarasi : CURSOR nama IS perintah_select;
* Membuka : OPEN nama
* Membaca : FETCH nama INTO list_variable | nama_record;
* Menutup : CLOSE nama
* Atribut Cursor :%ISOPEN, %NOTFOUND, %FOUND, %ROWCOUNT


Contoh Cursor

SET SERVEROUTPUT ON
DECLARE
NOMOR NUMBER;
NAMA VARCHAR(50);
LOKASI VARCHAR(50);
N NUMBER(4);
CURSOR D IS SELECT * FROM DEPT;
BEGIN
OPEN D;
N :=1;
LOOP
FETCH D INTO NOMOR,NAMA,LOKASI;
EXIT WHEN D%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(N||’ ’||NOMOR||’ ’||NAMA||’ ’||LOKASI);
N := N + 1;
END LOOP;
CLOSE D;
END;
/

Menangani Kesalahan (Eksepsi)

* Eksepsi akan muncul jika ada kesalahan
- Oleh Oracle
- Disengaja oleh User : RAISE_APPLICATION_ERROR
* Jika Eksepsi muncul
- Jika tidak ditangani, program akan dihentikan
- Jika ditangani, alur program diarahkan ke eksepsi handler
* Sintak :
- EXCEPTION
^ WHEN eksepsi or … THEN
pernyataan
^ WHEN OTHERS THEN
pernyataan
* Contoh Eksepsi : CURSOR_ALREADY_OPEN, PROGRAM_ERROR, INVALID_CURSOR, INVALID_NUMBER


Contoh Penanganan Kesalahan

DECLARE
GAJI EMP.SAL%TYPE;
BEGIN
SELECT SAL INTO GAJI FROM EMP WHERE ENAME=’SCOTT’;
IF GAJI=0 THEN
RAISE_APPLICATION_ERROR(-20000,’GAJI SCOTT MASIH $0’);
ELSE
DBMS_OUTPUT.PUT_LINE(’GAJI DINAIKKAN $’||(GAJI*0.1));
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(’TIDAK ADA DATA’);
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(’TERJADI SUATU KESALAHAN’);
END;
/


Sub Program
* Nama dari blok PL/SQL yang dapat menerima parameter dan dapat dipanggil
* terdapat dua jenis:
1. Sebuah prosedur yang digunakan untuk melakukan suatu kegiatan tertentu
2. Sebuah fungsi yang digunakan untuk menghitung suatu nilai
* Menggunakan struktur blok PL/SQL standart
* Modular, dapat dipergunakan berulang-ulang, dapat ditingkatkan/dikembangkan, mudah dalam perawatan


Membuat Prosedur

CREATE OR REPLACE PROCEDURE nama (list_parameter)
IS/AS
BEGIN
program PL/SQL
EXCEPTION
program PL/SQL
END;

Contoh Prosedur

CREATE OR REPLACE PROCEDURE JUMLAH AS
J NUMBER;
JUM VARCHAR(20);
BEGIN
SELECT COUNT(*) INTO J FROM TAB;
JUM := CASE J
WHEN 1 THEN ’SATU’
WHEN 2 THEN ’DUA’
WHEN 3 THEN ’TIGA’
WHEN 4 THEN ’EMPAT’
WHEN 5 THEN ’LIMA’
END;
DBMS_OUTPUT.PUT_LINE(’JUMLAH TABEL = ’||J||’ (’||JUM||’)’);
END;
/
BEGIN JUMLAH(); END;
/

Membuat Fungsi

CREATE OR REPLACE FUNCTION nama (list_parameter)
RETURN tipe
IS/AS
BEGIN
program PL/SQL
RETURN hasil
END;


Contoh Fungsi

CREATE OR REPLACE FUNCTION BAGI(A NUMBER,B NUMBER) RETURN NUMBER IS
BEGIN
IF B=0 THEN
RETURN NULL;
ELSE
RETURN A/B;
END IF;
END;
/
SELECT 10 A,3 B,BAGI(10,3) A_BAGI_B FROM DUAL;