Transaction locking

Sebuah transaksi adalah unit eksekusi program yang mengakses dan mungkin mengubah beberapa item data. Dalam Konsep transaksi di database harus di penuhi empat sifat database agar integritas database tetap terjaga. Adapun ke-empat sifat tersebut adalah :

• Atomicity: Setiap transaksi harus dijamin untuk dapat sukses dalam melakukan aksinya atau jika gagal, maka tidak berpengaruh apapun terhadap database.
• Consistency: Setiap transaksi adalah sebuah aksi kombinasi secara logikal dari sebuah state database yang konsisten ke state yang lain dengan tetap menjaga kekonsisten-an database tersebut .
• Isolation: Meskipun ada beberapa transaksi yang berlangsung bersamaan, masing-masing transaksi tidak boleh mengetahui transaksi lain yang sedang berlangsung. Hasil transaksi sementara harus disembunyikan dari transaksi lain yang sedang berlangsung . (level transparansi transaksi dapat di set)
• Durability: Setelah sebuah transaksi sukses dilakukan, perubahan-perubahan yang dibuatnya terhadap database bersifat permanen, bahkan jika terjadi kegagalan sistem sekalipun.

Contoh dari penerapan keempat sifat transaksi database didalam menjalankan sebuah transaksi, terdapat beberapa transaction manager pada setiap site yang bertugas untuk menjalankan prosedur -prosedur tertentu untuk menjamin ACID property. 4 Hal pokok yang dilakukan oleh Transaction Manager yaitu :

• Menjadwalkan operasi dari sebuah transaksi
• Menggunakan mekanisme commit dan rollback untuk menjamin atomicity
• Menggunakan mekanisme lock untuk konsistensi dan pelaksanaan transaksi yang concurrent serta isolation untuk menjamin transparansi transaksi pada level user .
• Sebuah loged digunakan untuk menjamin durability ketika sistem mengalami crash / failure .
• Sebuah transaksi dimulai ketika statement SQL di eksekusi pertama kali .

Transaksi ini berakhir ketika terjadi satu dari 4 hal berikut :

• Statement COMMIT atau ROLLBACK di isukan, definisi commit dan rollback Selain menggunkan commit juga dapat digunakan save point
• Eksekusi statement DDL atau DCL
• Ketika user keluar dari iSQL*Plus
• Sistem crash

LOCK CONFLICTS

waktu tunggu terhadap session yang lain ketika session pertama sedang dalam proses transaksi.

Penyebab terjadinya lock conflict :

• Perubahan yang tidak do commit (uncommitted changes)
• Transaksi yang berjalan lama
• Penguncian tingkat tinggi yang tidak diperlukan

Untuk mengatasi lock conflict yang terjadi dapat dilakukan daengan cara sebagai berikut :

• Session yang memegang kendali terhadap lock tersebut harus melepaskannya dengan cara COMMIT atau ROLLBACK
• Pada keadaan emergency, dimungkinkan bagi administrator untuk mengakhiri session yang sedang memegang kendali lock dengan cara menekan tombol kill session 

DEADLOCK

Terjadi bila dua atau lebih session, saling menunggu data yang sedang saling terkunci (lock) oleh masing masing session. Karena saling menunggu satu sama lainnya, maka tidak ada satupun diantara mereka dapat menyelesaikan transaksi mereka untuk mengatasi lock conflict yang terjadi. Oracle server secara otomatis akan mendeteksi kemungkinan deadlock yang akan terjadi dan segera mengatasinya dengan cara melakukan perintah ROLLBACK.

TRIGGER SQL

Trigger adalah blok PL/SQL atau prosedur yang berhubungan dengan table, view, skema atau database yang dijalankan secara implicit pada saat terjadi sebuah event. Trigger merupakan store procedure yang dijalankan secara automatis saat user melakukan modifikasi data pada tabel. Modifikasi data yang dilakukan pada tabel yaitu berupa perintah INSERT, UPDATE, dan DELETE. INSERT , UPDATE dan DELETE bisa digabung jadi satu trigger yang dinamakan Multiple Trigger.

Tipe trigger :

•  Application trigger : diaktifkan pada saat terjadi event yang berhubungan dengan sebuah aplikasi
•   Database trigger : diaktifkan pada saat terjadi event yang berhubungan dengan data (seperti operasi DML) atau event yang berhubungan dengan sistem (semisal logon atau shutdown) yang terjadi pada sebuah skema atau database

Trigger timing adalah waktu kapan trigger diaktifkan. Ada tiga macam trigger timing, yaitu :

• BEFORE : trigger dijalankan sebelum DML event pada table
• AFTER : trigger dijalankan setelah DML event pada table
•  INSTEAD OF : trigger dijalankan pada sebuah view.

Terdapat tiga kejadian (event) umum yang dapat menyebabkan trigger dipicu :

• INSERT, berarti trigger dijalankan ketika terdapat sejumlah data baru yang diinput
• UPDATE, berarti trigger dijalankan ketika terdapat data / record yang diubah
• DELETE, berarti trigger dijalankan ketika terdapat data / record yang dihapus

Manfaat dari penggunaan trigger :

• Trigger merupakan salah satu mekanisme untuk menjalankan aturan bisnis dan menjaga integritas data dalam sebuah basis data. Mekanisme yang lainnya adalah penggunaan CONSTRAINT
• Trigger dapat mengevaluasi status dari sebuah tabel sebelum dan sesudah terjadi perubahan data dan melakukan aksinya sesuai perbedaan yang terjadi
• Trigger dapat melakukan perubahan cascade melalui melalui relasi tabel pada database, akan tetapi perubahan ini akan lebih efisien jika dieksekusi menggunakan referential integrity constraints.
• Trigger dapat melakukan pencegahan akses atas data yang kompleks dibanding dengan menggunakan CHECK CONSTRAINT
• Tidak seperti pada Check Constraint, Trigger dapat mereferensikan kolom / field dalam tabel yang berbeda.

Contoh membuat trigger :

CREATE TRIGGER {Nama_Trigger}
ON {Nama_Table}
FOR {DELETE | INSERT | UPDATE}
AS
{SQL Statement}
Syntax menghapus Trigger :
DROP TRIGGER {Nama_Trigger}
ALTER TRIGGER {Nama_Trigger}
ON [Nama_Table}
FOR {DELETE | INSERT | UPDATE}
AS
{SQL Statement}

FUNCTION PADA PL/SQL

Function adalah suatu blok PL/SQL yang memiliki konsep sama dengan procedure, hanya saja pada function terdapat pengembalian nilai (return value).Karena function dapat mengembalikan sebuah nilai, function dapat diakses seperti layaknya sebuah variabel biasa.

Fungsi SQL Aggregate : fungsi yang menghasilkan nilai tunggal dari perhitungan sebuah nilai dari sebuah kolom, yaitu:

AVG() – memberikan nilai rata-rata dari sebuah kolom numerik

COUNT() – memberikan jumlah baris atau data yang sesuai dengan kriteria yang diberikan

FIRST() – memberikan nilai pertama dari sebuah kolom

LAST() – memberikan nilai terakhir dari sebuah kolom

MAX() – memberikan nilai terbesar dari sebuah kolom

MIN() – memberikan nilai terkecil dari sebuah kolom

SUM() – memberikan jumlah dari sebuah kolom numerik

Fungsi SQL Scalar : fungsi yang menghasilkan nilai tunggal dari nilai yang dimasukkan, yaitu :

UCASE() – merubah menjadi huruf kapital

LCASE() – merubah menjadi huruf kecil

MID() – mengekstrak karakter dari sebuah data teks

LEN() – menghitung jumlah karakter dari sebuah data teks

ROUND() – membulatkan data numerik ke nilai terdekat

NOW() – memberikan data tanggal dan waktu dari sistem

FORMAT() – memberikan format dari sebuah data yang akan ditampilkan

Bentuk Umum :

CREATE OR REPLACE FUNCTION nama_function (parameter_1, …)

RETURN tipe_data AS

variabel_1 tipe_data;

…

BEGIN

statemen_1;

…

RETURN nilai_yang_dikembalikan;

END;

Statemen RETURN tipe_data diatas menunjukkan bahwa function akan mengembalikan nilai dengan tipe data tertentu

Statemen RETURN nilai_yang_dikembalikan berfungsi untuk mengembalikan nilai yang telah diproses dalam function

sumber

CURSOR

CURSOR

Blok PL/SQL tidak memungkinkan untuk menangani query dengan keluaran lebih dari satu baris(record) karena variabel penampungnya harus mempunyai sebuah nilai. Karena itu, diperlukan sebuah cursor untuk menampung semua kondisi dari keluaran sebuah query. Cursor merupakan suatu variabel yang digunakan untuk menampung  hasil query yang terdiri atas lebih dari satu row atau record. Cursor dapat diilustrasikan sebagai penampung sekaligus pointer atas hasil eksekusi query. Pada dasarnya perintah “SELECT ..” pada PL/SQL merupakan sebuah cursor. Terdapat dua macam cursor, yaitu: cursor implisit dan cursor eksplisit

Cursor Implisit

Merupakan perintah SELECT statement dengan klausa INTO yang terdapat di dalam blok PL/SQL dan harus menghasilkan satu baris record. Jika hasil cursor implisit ini lebih dari satu baris atau tidak menghasilkan satu baris pun, maka Oracle akan mengeluarkan pesan kesalahan. Klausa into pada cursor implisit dipakai untuk menugaskan nilai hasil proses select ke dalam variabel-variabel yang dipilih.

Contoh :

DECLARE

CURSOR c_nama IS

SELECT nama_petugas FROM pegawai ORDER BY nama_petugas ASC;

v_nama PETUGAS.nama_petugas%TYPE;

BEGIN

FOR X IN c_nama

LOOP

DBMS_OUTPUT.PUT_LINE (c_nama%ROWCOUNT ||'Daftar Nama Petugas : ' || x.nama_petugas);

END LOOP;

END; 

Cursor Eksplisit

Tidak seperti cursor implisit, cursor eksplisit harus dideklarasikan terlebih dahulu sebelum digunakan. Terdapat empat tahapan penggunaan cursor eksplisit ini yaitu: declare, open, fetch dan terakhir adalah close.

contoh:

DECLARE

v_nama varchar2(20);

v_nim char(9);

CURSOR CUR1 IS SELECT NAMA, NIM

FROM MAHASISWA;

BEGIN

      OPEN CUR1;

      FETCH CUR1 INTO v_nama, v_nim;

      WHILE CUR1%FOUND LOOP

            DBMS_OUTPUT.PUT_LINE(v_nama);

            DBMS_OUTPUT.PUT_LINE(v_nim);

            FETCH CUR1 INTO v_nama, v_nim;

END LOOP;

CLOSE CUR1;

END;

Statement declare:

CURSOR nama_cursor IS query

Merupakan deklarasi nama cursor dan definisi struktur query yang akan dilakukan di dalamnya.

Contoh:

DECLARE

CURSOR CUR1 IS

SELECT nim, nama, alamat

FROM mahasiswa

.. .. ..

Statement open

OPEN nama_cursor;

Sebelum suatu cursor dapat digunakan, cursor harus dibuka terlebih dahulu.

Contoh:

OPEN CUR1;

Statement fetch

FETCH nama_cursor INTO <variable1,variable2,..>;

Fetch merupakan perintah untuk mengambil isi cursor, dimana isi dari cursor tersebut adalah hasil query yang telah dideklarasikan pada statement deklarasi cursor. Dan tiap-tiap field hasil query tersebut kemudian ditampung dalam variable-variable yang tipe datanya sesuai dengan tipe-tipe kolom query pada statemen deklarasi. Fetch ini dilakukan untuk tiap-tiap baris.

Contoh:

FETCH CUR1 INTO v_nim, v_nama, v_alamat;

Statement close

CLOSE nama_cursor;

 Setelah pemrosesan query selesai, cursor harus ditutup dengan satatemen CLOSE ini.

sumber