SEQUENCE DAN INDEX
Sequence pada Oracle merupakan nomor unik yang di-generate secara otomatis. Sequence adalah sebuah objek yang dapat di-share kepada banyak user. Kegunaan utama dari sequence adalah sebagai primary key, yang harus bersifat unik untuk setiap baris. Penggunaan sequence dapat mempermudah dibandingkan menulis kode baru untuk mengimplementasikan nomor yang berurutan. Sequence disimpan dan digenerate secara terpisah dari tabel yang ada. Sequence yang sama dapat digunakan pada tabel yang berbeda. Sintaks untuk menciptakan sequence adalah sebagai berikut.
CREATE SEQUENCE nama_sequence [INCREMENT BY n] [START WITH n] [(MAXVALUE n | NOMAXVALUE)] [(MINVALUE n | NOMINVALUE)] [CYCLE | NOCYCLE] [(CACHE n | NOCACHE)]; Contoh: CREATE SEQUENCE dept_deptid_seq INCREMENT BY 10 START WITH 120 MAXVALUE 9999 NOCACHE NOCYCLE;
Untuk melihat daftar dari sequence yang ada, dapat menggunakan cara berikut. SELECT sequence_name, min_value, max_value, increment_by, last_number FROM user_sequences;
Query di atas memanggil sequence yang ditulis di dalam tabel user_sequences yang dibuat secara otomatis ketika Oracle diinstal.
Perlu diingat bahwa opsi last_number berfungsi untuk menampilkan nomor sequence yang tersedia jika NOCACHE disebutkan pada saat sequence dibuat.
Sequence pada Oracle mengenal dua perintah dasar, yakni NEXTVAL yang berfungsi untuk mengembalikan nilai berikut dari sebuah sequence, dan CURRVAL yang berfungsi untuk mengambil
32 Danny Kriestanto, S.Kom., M.Eng
nilai sequence yang ada saat ini. Perlu diingat bahwa NEXTVAL harus dipanggil terlebih dahulu sebelum memanggil CURRVAL. Contoh:
INSERT INTO departments(department_id, department_name, location_id) VALUES (dept_deptid_seq.NEXTVAL, ‘Support’, 2500);
SELECT dept_deptid_seq.CURRVAL FROM dual;
Sebuah nilai pada sequence dapat ‘melompat’ apabila: -
Perintah ROLLBACK dipanggil
-
Terjadi crash pada sistem.
-
Sequence dipakai pada tabel lain.
Sama seperti tabel, sebuah sequence juga dapat dimodifikasi dan juga dapat dihapus. Contoh:
ALTER SEQUENCE dept_deptid_seq INCREMENT BY 20 MAXVALUE 999999 NOCACHE NOCYCLE;
DROP SEQUENCE dept_deptid_seq;
Index pada Oracle adalah sebuah objek pada skema dan digunakan oleh server Oracle untuk mempercepat pengambilan data dengan menggunakan pointer. Index dapat mengurangi proses I/O dengan menggunakan metode jalur akses cepat untuk mengalokasikan data secara cepat. Secara fisik maupun logika, sebuah index bersifat independen dari tabel yang diindex. Artinya, index dapat dibuat dan dihapus kapanpun tanpa mempengaruhi isi tabel ataupun index yang lain.
Sebuah index tercipta ketika sebuah primary key didefenisikan. Secara manual, index dapat diciptakan oleh pengguna untuk mempercepat akses pada tabel. Perintah untuk menciptakan index adalah sebagai berikut.
CREATE INDEX nama_indeks ON nama_tabel (kolom[, kolom]…);
Contoh: 33 Danny Kriestanto, S.Kom., M.Eng
CREATE INDEX emp_lastname_idx ON employees(last_name);
Index dibutuhkan pada saat: -
Terdapat sebuah kolom yang memiliki nilai dengan range yang besar
-
Terdapat sebuah kolom yang memiliki banyak sekali nilai null.
-
Ada satu kolom atau lebih yang sering digunakan pada klausa WHERE atau kondisi join.
-
Tabel berukuran besar dan banyak dari query diharapkan untuk mengambil kurang dari 2-4% baris.
Index tidak dibutuhkan ketika: -
Tabel berukuran kecil.
-
Kolom jarang digunakan sebagai kondisi pada query.
-
Sebagian besar query diharapkan mengambil lebih dari 2-4% dari total baris pada tabel.
-
Tabel sering diupdate.
-
Kolom yang diindex direferensi sebagai bagian dari sebuah ekspresi.
Perintah untuk melihat index ada pada contoh berikut. SELECT ic.index_name, ic.column_name, ic.column_position col_pos, ix.uniqueness FROM user_indexes ix, user_ind_columns ic WHERE ic.index_name = ix.index_name AND ic.table_name = ‘EMPLOYEES’; Catatan: kata EMPLOYEES harus berupa huruf besar semua karena merupakan tipe data string. Contoh pembuatan index: CREATE INDEX upper_dept_name_idx ON departments(UPPER(department_name));
Contoh penghapusan index: DROP INDEX upper_dept_name_idx;
34 Danny Kriestanto, S.Kom., M.Eng
PL/SQL : STRUKTUR BLOK
PL/SQL merupakan sebuah teknologi dari Oracle yang memungkinkan user untuk menciptakan sebuah blok program sama seperti bahasa pemrograman procedural seperti C, Pascal, Delphi, dan lain sebagainya. PL/SQL memudahkan eksekusi pernyataan-pernyataan SQL. Penggunaan PL/SQL akan meningkatkan performa Oracle dalam mengeksekusi perintah SQL karena satu perintah PL/SQL dapat dipanggil berkali-kali tanpa harus mengetikkan SQL yang sama berulang-ulang.
PL/SQL merupakan bahasa berstruktur blok, artinya program dapat dibagi ke dalam blok-blok logika. Setiap blok PL/SQL terdiri atas 3 bagian: 1. Deklarasi (opsional), berisi semua variabel, konstanta, cursor, dan penanganan error yang dibuat sendiri oleh user. 2. Eksekusi (harus ada), berisi pernyataan SQL untuk memanipulasi data di dalam database dan pernyataan PL/SQL untuk memanipulasi data di dalam blok. 3. Penanganan error (opsional). Mengspesifikasikan aksi yang akan dilakukan apabila error muncul di bagian eksekusi.
Bentuk dari blok PL/SQL dapat diperlihatkan sebagai berikut. DECLARE ... BEGIN ... EXCEPTION ... END;
Penggunaan titik-koma (;) di akhir pernyataan wajib ada. Dan setiap blok PL/SQL harus diakhiri dengan tanda garis miring (/) sebagai penanda bahwa blok PL/SQL telah selesai. Ketika pernyataan PL/SQL berhasil dieksekusi dengan benar, maka pernyataan yang akan muncul adalah PL/SQL procedure successfully completed.
Tipe dari blok PL/SQL sendiri terdiri atas 3 macam, yaitu: anonymous, procedure, dan function. Anonymous berarti blok yang tidak diberi nama. Prosedur dan function akan dibahas pada pertemuan lain.
35 Danny Kriestanto, S.Kom., M.Eng
Variabel pada PL/SQL dapat mendukung berbagai tipe data pada SQL dari Oracle. Namun, selain tipe-tipe tersebut, PL/SQL juga mengenal dua macam variabel lain, yaitu: %TYPE dan %ROWTYPE. %TYPE berarti PL/SQL akan secara otomatis menyesuaikan tipe data yang terdapat pada kolom tabel tertentu. Sedangkan %ROWTYPE mendukung semua tipe data yang terdapat pada kolom.
Berdasarkan kategorinya, tipe data pada PL/SQL dapat dibagi menjadi 4 bagian, yaitu: -
Skalar, yang hanya mendukung satu tipe data. Yang termasuk di dalamnya adalah: CHAR, VARCHAR2, LONG, NUMBER, BINARY_INTEGER, PLS_INTEGER, dan BOOLEAN (TRUE, FALSE, dan NULL), DATE, TiMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, INTERVAL YEAR TO MONTH, INTERVAL DAY TO SECOND.
-
Komposit, yang mendukung tipe data seperti record, yang mendukung banyak tipe data
-
Reference, tipe data pointer.
-
LOB (Large Objects), menyimpan nilai yang disebut sebagai locator, yang mengspesifikasikan lokasi dari objek berukuran besar, seperti citra.
Penerapan penulisan sintaks yang digunakan untuk mendeklarasikan variabel dapat ditulis sebagai berikut.
indentifier [CONSTANT] tipe_data [NOT NULL] [:= | DEFAULT ekspr]
Agar lebih jelas, perhatikan penggalan PL/SQL berikut.
DECLARE v_tglmasuk v_nodept v_lokasi c_komisi
DATE; NUMBER(2) NOT NULL := 10; VARCHAR2(13) := ‘Atlanta’; CONSTANT NUMBER := 1400;
Pernyataan NOT NULL HARUS memiliki nilai awal, dalam hal ini adalah 10. Demikian halnya dengan CONSTANT yang berisi nilai konstanta. Semua variabel di atas merupakan variabel skalar karena hanya masing-masing hanya menampung 1 nilai.
36 Danny Kriestanto, S.Kom., M.Eng
Salah satu aturan dari penggunaan variabel adalah: nama variabel tidak boleh sama dengan nama kolom pada tabel. Selain itu, untuk mengikat variable pada PL/SQL harus digunakan tanda titik ganda di depan nama variabel tersebut. Contoh: DECLARE v_id_pegawai NUMBER(6); BEGIN SELECT employee_id INTO v_id_pegawai FROM employees WHERE last_name = ‘Kochhar’; END; /
Ada alternatif lain untuk menampilkan hasil PL/SQL selain menggunakan perintah PRINT, yaitu dengan perintah DBMS_OUTPUT.PUT_LINE. Tapi untuk menampilkan hasilnya, terlebih dahulu harus menyebutkan SET SERVEROUTPUT ON.
PL/SQL memiliki lexical unit yang dapat diklasifikasi atas: -
Delimiter, merupakan simbol yang mengandung arti khusus pada PL/SQL. Simbol
-
Arti
+
Operator penjumlahan
-
Operator pengurangan
*
Operator perkalian
/
Operator pembagian
=
Operator relasional
@
Indicator akses remote
;
Terminator perintah
<>
Operator relasional
!=
Operator relasional
||
Operator penggabungan
--
Indikator komentar satu baris
/*
Indikator awal komentar
*/
Indikator akhir komentar
:=
Operator penugasan
Identifier, digunakan untuk menamakan unit dan item PL/SQL, termasuk konstanta, variable, eksepsi, kursor, subprogram, dan paket. 37
Danny Kriestanto, S.Kom., M.Eng
-
Literal, yakni penggunaan tanda petik tunggal untuk mengapit karakter dan tanggal.
-
Komentar, yakni penggunaan tanda (--) ataupun /* dan */.
PL/SQL juga mengenal prioritas terhadap operator yang dapat dilihat pada tabel berikut. Operator
Operasi
**
Pemangkatan
*, /
Perkalian, pembagian
+, -, || =, <, >, <=, >=, <>, !=,
Penambahan, pengurangan, penggabungan Perbandingan
~=, ^=, IS NULL, LIKE, BETWEEN, IN NOT
Negasi logika
AND
Konjungsi
OR
Inklusi
KERJAKAN, AMATI, DAN ANALISIS HASILNYA 1. SET SERVEROUTPUT ON 2. VARIABLE g_gaji NUMBER; BEGIN SELECT salary INTO :g_gaji FROM employees WHERE employee_id = 178; END; / PRINT g_gaji
3. DEFINE p_gaji_thnan = 60000; DECLARE v_gaji NUMBER(9,2) := &p_gaji_thnan; BEGIN V_gaji := v_gaji/12; DBMS_OUTPUT.PUT_LINE(‘Besar gaji TO_CHAR(v_gaji)); END; /
bulanan
adalah
’
||
4. VARIABLE g_pesan VARCHAR2(30); BEGIN :g_pesan := ‘Contoh blok PL/SQL’; END; /
38 Danny Kriestanto, S.Kom., M.Eng
PRINT g_pesan;
5. VARIABLE g_char CHAR(30); VARIABLE g_num NUMBER; DECLARE v_char CHAR(30); v_num NUMBER; BEGIN v_char := ‘Jawabannya 42’; v_num := to_number(substr(v_char,1,2)); :g_char := v_char; :g_num := v_num; END; / PRINT g_char; PRINT g_num;
6. DECLARE v_ts TIMESTAMP(6):=systimestamp; v_tx VARCHAR2(2000); BEGIN v_tx:=TO_CHAR(v_ts,'HH24:MI:SS.FF6'); DBMS_OUTPUT.PUT_LINE(v_tx); END; /
7. DECLARE nama_pegawai_c CHAR(32); nama_pegawai_v VARCHAR2(32); BEGIN nama_pegawai_c := 'James Gennick'; nama_pegawai_v := 'James Gennick'; IF nama_pegawai_c = nama_pegawai_v THEN DBMS_OUTPUT.PUT_LINE('Kedua nama sama'); ELSE DBMS_OUTPUT.PUT_LINE('Kedua nama TIDAK sama'); END IF; END; /
8. DECLARE empty_varchar2 VARCHAR2(10) := ''; empty_char CHAR(10) := ''; BEGIN IF empty_varchar2 IS NULL THEN
39 Danny Kriestanto, S.Kom., M.Eng
DBMS_OUTPUT.PUT_LINE('empty_varchar2 is NULL'); END IF; IF '' IS NULL THEN DBMS_OUTPUT.PUT_LINE(''''' is NULL'); END IF; IF empty_char IS NULL THEN DBMS_OUTPUT.PUT_LINE('empty_char is NULL'); END IF; END; /
40 Danny Kriestanto, S.Kom., M.Eng
