Memanipulasi Data
Data Manipulation Language (DML) merupakan bagian inti dari SQL. DML digunakan untuk menambah, mengubah, atau menghapus data dalam sistem basis data. Perubahan data yang belum tersimpan secara permanen dalam sistem basis data disebut transaksi, atau kumpulan pekerjaan logika.
11.1. Menambahkan Data
Menambahkan data pada tabel dapat dilakukan dengan mengeksekusi perintah INSERT. Pada saat mengisikan data kita harus memperhatikan urutan kolom dari table apabila kita tidak menyebutkan urutan kolomnya pada perintah INSERT.
Sintaks
INSERT INTO table [(column [, column…])]
VALUES (value [, value…);
- table
- nama tabel yang akan ditambah datanya.
- column
- nama kolom yang akan diisi data
- value
- nilai yang akan diinputkan
Contoh:
Menampilkan struktur table DEPARTMENT.
DESCRIBE department
Menambahkan data pada table DEPARTMENT.
INSERT INTO department
VALUES (11,’Finance’,2);
11.1.1. Menambahkan Nilai Null
- Implisit
- Abaikan kolom dari daftar kolom
- Eksplisit
- Sebutkan kata kunci NULL dalam VALUES
- Sebutkan string kosong (”) dalam VALUES;
- Hanya berlaku untuk string dan tanggal.
Contoh:
Menambahkan data pada table DEPARTMENT dengan mengabaikan nilai untuk kolom REGION_ID.
INSERT INTO department (id, name)
VALUES (12,’Information Technology’);
Apabila penambahan tersebut dilakukan secara eksplisit, perintahnya menjadi seperti berikut.
INSERT INTO department
VALUES (12,’Information Technology’,NULL);
11.1.2. Menambahkan Nilai Khusus
Kita dapat menambahkan data ke dalam suatu table dengan mengambil nilai-nilai yang telah disediakan oleh sistem basis data Oracle seperti USERID (user yang sedang aktif) dan SYSDATE (tanggal sistem).
Contoh:
Menambahkan data pada table EMPLOYEE menggunakan USERID dan SYSDATE.
INSERT INTO employee
(id, first_name, last_name, userid, salary, start_date)
VALUES (26,’Donna’,'Smith’,USER, NULL, SYSDATE);
Menambahkan data yang mengandung tanda petik tunggal (’).
CREATE TABLE TABLE1 (KOLOM1 VARCHAR2(50));
INSERT INTO TABLE1
VALUES(’Indra’’s books’);
SELECT *
FROM TABLE1;
DROP TABLE TABLE1;
11.1.3. Menambahkan Tanggal dan Waktu
Ketika kita menambahkan nilai tanggal, biasanya format DD-MM-YY yang digunakan. Dengan format ini, nilai abad di-default ke abad saat ini. Karena tanggal juga mengandung informasi waktu, nilai default waktu adalah tengah malam (00:00:00).
Jika kita perlu menambahkan data dengan nilai abad yang berbeda dan tanggal tertentu, kita dapat menggunakan function TO_DATE.
Contoh:
Menambahkan data pada table EMPLOYEE menggunakan USERID dan SYSDATE. Kolom START_DATE diset dengan nilai spesifik 1 Januari 1999, 08:00 A.M.
INSERT INTO employee(id, first_name, last_name, userid, salary, start_date)
VALUES (26,’Donna’,'Smith’,USER, NULL,TO_DATE(’01-JAN-96′));
11.1.4. Menambahkan Data Mengggunakan Substitution Variables
Kita dapat membentuk perintah INSERT yang mengijinkan user untuk menambahkan nilai-nilai secara interaktif dengan menggunakan substitution variables dari SQL*Plus.
Contoh:
Menambahkan data department. Menanyakan kepada user untuk memasukkan department number, department name, dan region number.
INSERT INTO department(id, name, region_id)
VALUES (&department_id, ‘&department_name’, ®ion_id);
Catatan:
Untuk nilai-nilai tanggal dan karakter, tanda ampersand (&) dan nama variable diapit oleh tanda petik tunggal.
11.1.5. Menciptakan Script untuk Memanipulasi Data
Kita dapat menyimpan perintah dengan substitution variables ke dalam sebuah file dan mengeksekusinya kemudian. Setiap kali kita mengeksekusi perintah tersebut, ia akan menanyakan kepada kita untuk memasukkan sebuah nilai baru. Kita dapat mengatur bentuk prompt (pertanyaan) tersebut dengan menggunakan perintah ACCEPT.
Contoh:
Menambahkan data department. Menanyakan kepada user untuk memasukkan department number, department name, dan region number. Atur pesan yang ditampilkan pada prompt.
ACCEPT department_id PROMPT ‘Please enter the department number:’
ACCEPT department_name PROMPT ‘Please enter the department name:’
ACCEPT region_id PROMPT ‘Please enter the region number:’
INSERT INTO department(id, name, region_id)
VALUES (&department_id, ‘&department_name’, ®ion_id);
Catatan:
Jangan mengawali substitution parameter SQL*Plus dengan tanda ampersand (&) ketika mereferensikannya di dalam perintah ACCEPT. Gunakan tanda pemisah (-) untuk melanjutkan perintah SQL*Plus pada baris berikutnya.
11.1.6. Mengkopi Data dari Table Lain
Kita dapat menggunakan perintah INSERT untuk menambahkan data kedalam table dimana nilai-nilainya berasal dari table-table lain. Pada bagian klausa VALUES, kita gunakan subquery.
Sintaks
INSERT INTO table [column (, column)]
subquery;
dimana:
- table: nama table
- column: nama kolom pada table yang akan diisi data
- subquery: subquery yang menghasilkan nilai ke dalam table
Kita dapat melihat kembali BAB 6 – SUBQUERY untuk mendapat gambaran yang lebih jelas mengenai topik subquery.
Contoh:
Mengkopi data dari table EMPLOYEE ke dalam table HISTORY.
INSERT INTO HISTORY (id, last_name, salary, title, start_date)
SELECT id, last_name, salary, title, start_date
FROM employee
WHERE start_date < ‘01-JAN-94′;
Catatan:
Jumlah kolom pada klausa INSERT harus sama dengan jumlah kolom yang dihasilkan oleh subquery di bawahnya.
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.
11.2. Mengubah Data
Kita dapat memodifikasi data yang telah ada dengan menggunakan perintah UPDATE.
Sintaks
UPDATE table
SET column = value [, column = value . . .]
[WHERE condition];
dimana:
- table: nama table
- column: nama kolom yang akan di-update
- value: nilai atau subquery yang sesuai untuk kolom tersebut
- condition: mengidentifikasi baris-baris data yang akan di-update dan dibangun dari nama-nama kolom, ekspresi, konstanta, subquery, dan operator pembanding
Contoh:
Memindah employee number 2 ke department 10. Memindah employee number 1 ke department 32 serta mengubah salary menjadi 2550.
UPDATE employee
SET dept_id = 10
WHERE id = 2;
UPDATE employee
SET dept_id = 32, salary = 2550
WHERE id = 1;
Mengkonfirmasi perubahan data yang telah dilakukan.
SELECT id, last_name, salary, dept_id
FROM employee
WHERE id IN (1,2);
Jika kita tidak menyertakan klausa WHERE pada perintah UPDATE, maka seluruh baris data pada table akan ter-update. Oleh karena itu kita harus berhati-hati dalam melakukan perubahan data, agar data yang ter-update sesuai dengan yang kita kehendaki.
Contoh:
Memberikan komisi 10 persen kepada setiap employee (karyawan). Setelah itu konfirmasikan perubahan yang telah dilakukan.
UPDATE employee
SET commission_pct = 10;
SELECT id, commission_pct
FROM employee;
11.2.1 Integrity Constraint Error
Jika kita mencoba mengubah data dimana nilainya terkait dengan integrity constraint, kita akan menemui pesan kesalahan (error).
Contoh:
Jika nilai yang kita tentukan tidak terdapat pada table induk, maka kita akan menemui “parent key” violation ORA-02291.
UPDATE employee
SET dept_id = 60
WHERE dept_id = 10;
11.3. Menghapus Data
Kita dapat menghapus data yang telah ada dengan menggunakan perintah DELETE.
Sintaks
UPDATE [FROM] table
[WHERE condition];
dimana:
- table: nama table
- condition: mengidentifikasi baris-baris data yang akan di-update dan dibangun dari nama-nama kolom, ekspresi, konstanta, subquery, dan operator pembanding
Setelah kita melakukan perintah penghapusan data, konfirmasikan perubahannya dengan menggunakan perintah SELECT.
Catatan:
Jika klausa WHERE tidak disertakan, seluruh data pada table akan dihapus. Maka dari itu berhati-hatilah dalam melakukan penghapusan data.
Contoh:
Hapus seluruh informasi employee yang mulai bekerja setelah 1 Januari 1996.
DELETE FROM employee
WHERE start_date > TO_DATE(’01-JAN-96′);
Jika kita tidak menyertakan klausa WHERE didalam perintah DELETE, seluruh data pada table akan dihapus.
Contoh:
Menghapus seluruh data pada table TEST.
DELETE FROM test;
Mengkonfirmasi penghapusan.
SELECT *
FROM test;
11.3.1. Integrity Constraint Error
Jika kita mencoba menghapus data dimana nilainya terkait dengan integrity constraint, kita akan menemui pesan kesalahan (error).
Contoh:
Berusaha menghapus data department yang berada pada region number 1.
DELETE department
WHERE region_id = 1;
11.4. Pemrosesan Transaksi
Oracle menjamin konsistensi data berdasarkan transaksi yang dilakukan. Transaksi memberikan fleksibilitas dan kontrol lebih kepada kita ketika melakukan perubahan terhadap data, dan menjamin konsistensi data ketika terjadi kegagalan user atau system.
Transaksi terdiri dari perintah-perintah DML (Data Manipulation Language) yang menyediakan perubahan yang konsisten terhadap data. Sebagai contoh, proses transfer dana antar dua rekening bank, seharusnya terdiri dari proses debit terhadap satu rekening dan kredit terhadap rekening lainnya dengan jumlah uang yang sama. Kedua proses tersebut seharusnya berhasil atau gagal secara bersamaan. Proses kredit seharusnya tidak dilakukan tanpa adanya proses debit. Agar lebih jelas kita dapat memperhatikan ilustrasi berikut ini:
Banking Transaction
Transaction Begins…
UPDATE saving_accounts
SET balance = balance - 500
WHERE account = 3209;
— Decrement Saving Account
UPDATE checking_accounts
SET balance = balance + 500
WHERE account = 3208;
— Increment Checking Account
INSERT INTO journal
VALUES (journal_seq.NEXTVAL, ‘1B’, 3209, 3208, 500);
— Record in Transaction Journal
Transaction Ends…
11.4.1. Tipe-tipe Transaksi
- Data Manipulation Language
- Terdiri dari beberapa statement DML dimana Oracle memperlakukannya sebagai entitas tunggal atau satu unit logik pekerjaan.
- Data Definition Language
- Terdiri dari satu statement DDL.
- Data Control Language
- Terdiri dari satu statement DCL.
11.4.2. Kapan Sebuah Transaksi Dimulai dan Diakhiri?
Transaksi dimulai ketika perintah SQL pertama dijalankan dan diakhiri ketika salah satu dari hal-hal berikut ini terjadi:
- Perintah COMMIT atau ROLLBACK.
- Perintah DDL, seperti CREATE, atau perintah DCL.
- Error tertentu terdeteksi, seperti deadlocks.
- User keluar dari SQL*Plus.
- Terdapat kegagalan mesin (komputer) atau system crashes
Setelah sebuah transaksi diakhiri, statement executable SQL lainnya secara otomatis akan dimulai pada transaksi berikutnya.
Catatan:
Perintah DDL atau DCL secara otomatis melakukan commit dan oleh sebab itu secara implisit akan mengakhiri sebuah transaksi.
11.4.3. Statement Pengontrol Transaksi Secara Eksplisit
Kita dapat mengontrol jalannya transaksi dengan menggunakan statement COMMIT, SAVEPOINT, dan ROLLBACK.
- COMMIT
- Mengakhiri transaksi serta membuat seluruh perubahan data menjadi permanen
- SAVEPOINT name
- Manandai savepoint didalam sebuah transaksi
- ROLLBACK [TO SAVEPOINT name]
- Mengakhiri transaksi dan membatalkan seluruh perubahan data
11.4.4. Statement Pengontrol Transaksi Secara Implisit
- Automatic commit
- Adanya perintah DDL atau DCL.
- Keluar dari SQL*Plus secara normal, tanpa secara eksplisit menjalankan perintah COMMIT atau ROLLBACK.
- Automatic rollback
- Keluar dari SQL*Plus secara tidak normal, atau terjadi kegagalan sistem.
11.4.5. Committing Changes
Setiap perubahan data yang terjadi selama transaksi adalah sementara, sampai transaksi tersebut di-commit.
Keadaan Data Sebelum COMMIT atau ROLLBACK
- Operasi manipulasi data terutama mempengaruhi database buffer, oleh sebab itu keadaan data sebelumnya dapat diperbaiki.
- User yang sedang connect (current user) dapat me-review hasil dari operasi manipulasi data dengan melakukan query terhadap table
- User lain tidak dapat melihat hasil dari operasi manipulasi data dari current user. Oracle memberlakukan read consistency untuk menjamin bahwa setiap user melihat data seperti yang ada pada saat commit terakhir.
- Baris-baris data yang sedang diubah akan dikunci (locked), user lain tidak dapat mengubah data yang sedang diubah tersebut.
Kita menggunakan COMMIT untuk membuat seluruh perubahan data menjadi permanen. Hal-hal yang mengikuti COMMIT:
- Seluruh perubahan data ditulis ke database
- Keadaan data sebelumnya secara permanen hilang
- Seluruh user dapat melihat hasil dari transaksi
- Penguncian terhadap baris-baris data yang diubah akan dibebaskan, baris-baris data tersebut sekaran tersedia kembali bagi user lain untuk melakukan perubahan data berikutnya.
- Seluruh savepoints dihapus.
Contoh:
Ciptakan department baru dengan nama Education dengan paling tidak satu employee. Buatlah perubahan data menjadi permanen.
INSERT INTO department (id, name, region_id)
VALUES (54, ‘Education’, 1);
UPDATE employee
SET dept_id = 54
WHERE id = 2;
COMMIT;
11.4.6. Rolling Back Changes
Kita dapat membatalkan seluruh perubahan data yang masih menggantung dengan menggunakan statement ROLLBACK. Hal-hal yang mengikuti ROLLBACK:
- Seluruh perubahan data dibatalkan.
- Keadaan data sebelumnya dikembalikan seperti semula.
- Penguncian terhadap baris-baris data yang diubah akan dibebaskan, baris-baris data tersebut sekaran tersedia kembali bagi user lain untuk melakukan perubahan data berikutnya.
Contoh:
Ketika berusaha menghapus satu record pada table TEST, secara tidak sengaja menghapus seluruh table. Betulkan kesalahan tersebut, lalu jalankan perintah yang sesuai, dan buatlah seluruh perubahan data menjadi permanen.
DELETE FROM test;
ROLLBACK;
DELETE FROM test
WHERE id = 100;
SELECT *
FROM test
WHERE id = 100;
COMMIT;
11.4.6.1. Rolling Back Changes to a Savepoint
Kita dapat menciptakan tanda didalam sebuah transaksi dengan menggunakan perintah SAVEPOINT. Sehingga sebuah transaksi dapat dipecah menjadi bagian-bagian yang lebih kecil. Kemudian kita dapat membatalkan perubahan data kembali kepada keadaan saat tanda tersebut diciptakan, dengan menggunakan statement ROLLBACK TO SAVEPOINT.
Contoh:
Menaikkan salary untuk seluruh Stock Clerk sebanyak 10 persen. Menciptakan savepoint dengan nama UPDATE_DONE.
UPDATE employee
SET salary = salary * 1.1
WHERE title = ‘Stock Clerk’;
SAVEPOINT TO update_done;
INSERT INTO region (id, name)
VALUES (8, ‘Central’);
SELECT *
FROM region
WHERE id = 8;
ROLLBACK TO update_done;
SELECT *
FROM region
WHERE id = 8;
Catatan:
Jika kita menciptakan savepoint yang kedua dengan nama yang sama dengan savepoint sebelumnya, maka savepoint sebelumnya akan dihapus.
11.4.6.2. Statement Level Rollback
Bagian tertentu transaksi mungkin dibatalkan dengan menggunakan rollback secara implisit jika kesalahan (error) saat eksekusi statement terdeteksi. Jika statement DML tunggal gagal selama proses eksekusi transaksi, efeknya dibatalkan oleh statement level rollback, akan tetapi perubahan-perubahan yang diciptakan oleh statement-statement DML lain sebelumnya dalam transaksi tersebut tidak akan dibatalkan. Mereka dapat di-commit atau di-rollback secara eksplisit oleh user.
Oracle menjalankan COMMIT secara implisit sebelum dan sesudah setiap statement Data Definition Language. Sehingga, bahkan ketika statement DML kita tidak tereksekusi secara sukses, kita tidak dapat melakukan rollback ke statement sebelumnya karena server telah melakukan commit.
Silahkan melanjutkan membaca pembahasan Mengubah Table dan Constraint
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).
 |
Buku ini membahas berbagai macam tip dan trik yang sangat berguna bagi Anda pembaca pengguna database Oracle. Pemula, mahasiswa, programmer, maupun database administrator yang mengharapkan solusi cepat dalam menangani permasalahan-permasalahan dalam penggunaan database Oracle dapat membaca buku ini. Anda tidak harus membaca buku ini secara urut karena buku ini di susun berdasarkan topik permasalahan, dan Anda dapat membaca topik-topik yang sesuai dengan permasalahan yang Anda hadapi. Dapat di beli di toko buku Gramedia, Gunung Agung, Uranus, Karisma dsb. Harga Rp. 24.800,- (157 halaman) |
[…] 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 […]