Oracle SQL & PL/SQL - 13. Menciptakan Sequence

Posted by admin

October 8, 2008 170 views

Sequence adalah objek basis data yang dapat digunakan untuk secara otomatis membangkitkan nilai urut. Penggunaan sequence terutama untuk nilai primary key, dimana nilai tersebut harus unik. Sequence dibangkitkan dan ditambah (atau dikurangi) oleh rutin internal Oracle. Dengan menggunakan sequence kita dapat menghemat waktu dibandingkan harus menciptakan rutin tersendiri. Sequence disimpan dan dibangkitkan secara independen terhadap tabel. Sehingga sebuah sequence dapat digunakan untuk beberapa tabel.

13.1. Menciptakan Sequence

Kita dapat menciptakan sequence untuk membangkitkan nilai urut secara otomatis dengan menggunakan perintah CREATE SEQUENCE. Kita harus memiliki privilege CREATE SEQUENCE untuk dapat menciptakan sequence dan area penyimpanan dimana objek tersebut diciptakan. Kita akan membahas masalah privilege pada pembahasan Mengontrol Akses User.

Sintaks

CREATE SEQUENCE sequence
[INCREMENT BY n]
[START WITH n]
[{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE n | NOCACHE}]

dimana:

sequence
- nama sequence yang akan diciptakan.
INCREMENT BY n
- menentukan interval nilai sequence. Bila tidak dituliskan maka dianggap sequence memiliki interval 1.
START WITH n
- menentukan nilai awal sequence. Bila tidak dituliskan maka dianggap sequence diawali dengan nilai 1.
MAXVALUE n
- menentukan nilai maksimum sequence.
NOMAXVALUE
- menentukan nilai maksimum sequence 1027. Ini nilai default.
MINVALUE n
- menentukan nilai minimum sequence.
NOMINVALUE
- menentukan nilai minimum sequence 1.
CYCLE | NOCYCLE
- menentukan bahwa setelah sequence mencapai nilai maksimumnya maka akan berputar kembali ke nilai awalnya.
CACHE n | NOCACHE
- menentukan berapa jumlah nilai yang dialokasikan sebelumnya dan diletakkan dalam memori. Defaultnya adalah 20 nilai.

Contoh:

Menciptakan sequence DEPARTMENT_ID untuk kolom DEPT_ID pada tabel DEPARTMENT. Sequence diawali dengan nilai 51. Tidak mengijinkan caching dan cycle.

CREATE SEQUENCE department_id
INCREMENT BY 1
START WITH 51
MAXVALUE 9999999
NOCACHE
NOCYCLE;

13.2. Mengkonfirmasi Sequence

Setelah sequence diciptakan maka informasi sequence tersebut akan tersimpan dalam data dictionary (kamus data) Oracle, sama seperti objek-objek lainnya. Kita dapat melihatnya menggunakan tabel USER_OBJECTS atau USER_SEQUENCES.

Contoh:

Menampilkan informasi dari seluruh sequence telah kita ciptakan.

SELECT sequence_name, min_value, max_value, increment_by, last_number
FROM user_sequences;

13.3. Menggunakan Sequence

Setelah sequence diciptakan, kita dapat menggunakannya untuk menambahkan data pada suatu tabel. Kolom-kolom sequence yang digunakan adalah NEXTVAL dan CURRVAL.

Contoh:

Menambahkan data baru pada tabel DEPARTMENT.

INSERT INTO department (id, name, region_id)
VALUES (department_id.NEXTVAL, ‘Finance’, 2);
Menampilkan nilai terakhir dari sequence DEPARTMENT_ID.

SELECT department_id.CURRVAL
FROM SYS.dual;

13.3.1. NEXTVAL dan CURRVAL

NEXTVAL digunakan untuk mengambil nilai sequence berikutnya. Kita harus menyebutkan nama sequencenya sequence.NEXTVAL. Ketika kita memanggil NEXTVAL, maka nilai sequence yang baru akan dibangkitkan, dan nilai sequence saat ini akan diletakkan pada CURRVAL.
CURRVAL digunakan untuk mengetahui nilai sequence yang baru saja dibangkitkan. CURRVAL menunjukkan nilai terakhir yang telah dicapai oleh sequence.

13.3.1.1. Aturan Penggunaan NEXTVAL dan CURRVAL

Kita dapat menggunakan NEXTVAL dan CURRVAL dalam

Perintah SELECT yang bukan merupakan bagian dari subquery.
Perintah SELECT yang merupakan subquery dalam perintah INSERT.
Klausa VALUES dari perintah INSERT.
Klausa SET dari perintah UPDATE.

Kita tidak dapat menggunakan NEXTVAL dan CURRVAL dalam

Perintah SELECT dalam view.
Perintah SELECT dengan kata kunci DISTINCT.
Perintah SELECT dengan klausa GROUP BY, HAVING, atau ORDER BY.
Subquery dalam perintah SELECT, DELETE, dan UPDATE.
Ekspresi DEFAULT dalam perintah CREATE TABLE atau ALTER TABLE.

Tips

Kita dapat melihat nilai sequence berikutnya tanpa menyebabkan nilai sequence bertambah dengan melakukan query terhadap tabel USER_SEQUENCE. Hal ini berlaku hanya jika sequence diciptakan dengan opsi NOCACHE.

13.3.2. Menyiapkan Nilai Sequence

Menyiapkan nilai sequence dalam memori (caching) meningkatkan kecepatan akses terhadap nilai sequence tersebut. Cache ditempati pada saat nilai pertama sequence digunakan.

Setiap permintaan untuk nilai sequence berikutnya ditampilkan dari sequence yang di-cache. Setelah nilai sequence terakhir digunakan, permintaan nilai berikutnya memasukkan cache lainnya ke dalam memori .

Berhati-hatilah terhadap Celah dalam Sequence

Meskipun sequence menghasilkan nilai urut tanpa celah, proses ini tidak bergantung terhadap commit atau rollback. Sehingga, jika kita membatalkan transaksi yang didalamnya terdapat sequence, nilai tersebut akan hilang.

Kejadian lain yang dapat menyebabkan celah dalam sequence adalah kegagalan sistem. Jika sequence meng-cache nilai-nilai dalam memori, maka nilai-nilai tersebut akan hilang jika sistem gagal.

Karena sequence tidak terikat langsung dengan tabel, sequence yang sama dapat digunakan untuk beberapa tabel. Jika hal ini terjadi, maka setiap tabel dapat memiliki celah dalam nilai-nilai urutnya.

13.4. Memodifikasi Sequence

Ketika nilai maksimum sequence telah tercapai, tidak ada lagi nilai tambahan yang dialokasikan dari sequence tersebut dan kita akan mendapati pesan kesalahan yang menyebutkan bahwa sequence telah melampaui batas maksimumnya. Agar dapat terus menggunakan sequence tersebut, kita dapat memodifikasinya dengan menggunakan perintah ALTER SEQUENCE.

Sintaks

ALTER SEQUENCE sequence
[INCREMENT BY n]
[{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE n | NOCACHE}]

Aturan

Perintah ini hanya dapat dilakukan oleh pemilik sequence atau user yang memiliki privilege ALTER ANY SEQUENCE.
Hanya nilai berikutnya yang terpengaruh.
Beberapa validasi dilakukan.
Untuk memulai kembali sequence dengan nilai berbeda maka sequence harus dihapus dan diciptakan ulang.

13.5. Menghapus Sequence

Gunakan perintah DROP SEQUENCE untuk menghapus sequence. Perintah ini hanya dapat dilakukan oleh pemilik sequence atau user yang memiliki system privilege DROP ANY SEQUENCE.

Sintaks

DROP SEQUENCE sequence

13.6. Latihan

1. Buatlah sebuah sequence bernama S_DEPT_ID untuk digunakan pada column DEPT_ID pada table DEPARTMENT, dengan catatan bahwa sequence tersebut dimulai dengan angka 51 dengan nilai maksimum 9999999 tanpa cache dan cycle.

CREATE SEQUENCE s_dept_id
INCREMENT BY 1
START WITH 51
MAXVALUE 9999999
NOCACHE
NOCYCLE;

2. Tampilkan sequence yang telah Anda buat tersebut dengan perintah :

SELECT sequence_name, min_value, max_value, increment_by, last_number
FROM user_sequence;

3. Inputkan satu data dengan nama departemen Finance dan kode daerah 2 dengan menggunakan sequence S_DEPT_ID tersebut.

INSERT INTO department(id, name, region_id)
VALUES(s_dept_id.NEXTVAL,’Finance’,2);

4. Tampilkan nilai dari sequence S_DEPT_ID.

SELECT s_dept_id.CURRVAL
FROM SYS.dual;

5. Modifikasilah sequence S_DEPT_ID sehingga memiliki nilai maksimum 99999.

ALTER SEQUENCE s_dept_id MAXVALUE 99999;

6. Tampilkan data dari sequence S_DEPT_ID setelah Anda modifikasi.

SELECT sequence_name, min_value, max_value, increment_by, last_number
FROM user_sequence;

7. Hapuslah sequence S_DEPT_ID.

DROP SEQUENCE s_dept_id;

Silahkan melanjutkan membaca pembahasan Menciptakan View

Catatan:
Untuk mempraktekkan artikel-artikel mengenai Oracle SQL & PL/SQL Anda dapat mendownload script table disini, lalu jalankan sesuai live demo (browser Anda harus mendukung Flash Player).

Oracle SQL & PL/SQL

Anda dapat membantu situs ini dengan memberikan donasi kepada kami. Berapapun jumlahnya akan sangat berharga bagi kami. Salurkan donasi anda melalui halaman donasi. Terima kasih.