Oracle9i Introduction to SQL @Tessy Badriyah, SKom. MT.
BAB 9 : Manipulasi Data Halaman : 9 - 0
Oracle Academic Initiative
Oracle9i Introduction to SQL
Oleh:
Tessy Badriyah, SKom.MT
Politeknik Elektronika Negeri Surabaya
Institut Teknologi Sepuluh Nopember
Surabaya
Oracle9i Introduction to SQL @Tessy Badriyah, SKom. MT.
BAB 9 : Manipulasi Data Halaman : 9 - 1
BAB 9 : Manipulasi Data
9.1. Sasaran
· Memahami Statement DML (Data Manipulation Language)
· Menyisipkan baris ke dalam table
· Merubah baris dalam table
· Menghapus baris dari table
· Mengontrol transaksi
9.2. Data Manipulation Language
Data Manipulation Language (DML) adalah suatu statement yang dijalankan pada saat kita
memerlukan :
· penambahan baris baru pada table
· memodifikasi baris yang ada pada table
· menghapus baris yang ada pada table
DML Statement identik dengan operasi INSERT, MODIFY dan DELETE.
Istilah Transaksi mengandung pengertian kumpulan Statement DML yang membentuk suatu fungsi
tertentu.
9.3. Menambahkan Baris Baru ke dalam Tabel
Menambahkan baris baru ke dalam table menggunakan perintah INSERT.
INSERT INTO table [(column [, column …] ) ]
VALUES (value [, value…] );
Oracle9i Introduction to SQL @Tessy Badriyah, SKom. MT.
BAB 9 : Manipulasi Data Halaman : 9 - 2
9.4. Menambahkan Baris dengan Nilai NULL
Kolom yang tidak disebutkan dalam perintah INSERT INTO secara otomatis akan diisi dengan nilai
NULL.
Atau secara eksplisit memasukkan nilai NULL ke suatu kolom :
9.5. Menambahkan Nilai Khusus
Fungsi SYSDATE menyimpan waktu dan tanggal saat ini.
9.6. Menambahkan Nilai Tanggal Spesifik
Untuk menambahkan data yang memiliki tipe data tanggal dengan format yang spesifik :
9.7. Pembuatan Script
Dapat juga digunakan variabel substitusi dengan & ke dalam INSERT :
Oracle9i Introduction to SQL @Tessy Badriyah, SKom. MT.
BAB 9 : Manipulasi Data Halaman : 9 - 3
9.8. Mengkopi Baris dari Tabel yang lain
Perintah INSERT juga bisa digunakan untuk mengkopi baris data yang berasal dari table yang lain.
Berikut ini membuat tabel sales_reps dari tabel employees :
9.9. Merubah data dalam Tabel
Ilustrasi perubahan data dalam tabel :
9.10. Sintak Statement UPDATE
Untuk memodifikasi baris data yang ada pada table digunakan perintah UPDATE.
Sintak dari perintah UPDATE :
UPDATE table
SET column = value [, column = value, …]
[WHERE condition];
9.11. Mengupdate Baris dalam Tabel
Berikut contoh perintah UPDATE untuk mengubah satu baris saja :
Oracle9i Introduction to SQL @Tessy Badriyah, SKom. MT.
BAB 9 : Manipulasi Data Halaman : 9 - 4
Jika klausa WHERE dihilangkan, maka perintah UPDATE akan mengubah nilai kolom dari semua
data atau record yang ada pada tabel :
9.12. Mengupdate dua kolom dengan subquery
Query berikut ini akan mengupdate job dan salary yang dimiliki oleh employee 114 supaya sama
dengan job dan salary yang dimiliki oleh employee 205
9.13. Mengupdate Berdasarkan Baris pada Tabel yang lain
UPDATE berdasarkan table yang lain artinya perubahan pada sebuah table dimana kondisi
perubahannya ditentukan berdasarkan nilai yang terdapat pada table yang lain.
9.14. Mengupdate Baris : terdapat kesalahan Integrity Constraint
Salah satu kesalahan pada perintah UPDATE, misal jika kita berusaha untuk merubah data sedangkan
data tersebut terikat pada integrity constraint (merupakan suatu key).
Oracle9i Introduction to SQL @Tessy Badriyah, SKom. MT.
BAB 9 : Manipulasi Data Halaman : 9 - 5
9.15. Menghapus Baris dari Tabel
Ilustrasi untuk menghapus baris dari suatu tabel :
9.16. Statement DELETE
Baris data yang ada pada table dapat dihapus dengan menggunakan perintah DELETE.
Sintak penulisannya :
9.17. Menghapus Baris dari Tabel
Jika klausa WHERE disertakan pada perintah DELETE maka baris data tertentu yang akan dihapus
hanya yang memenuhi kriteria pada WHERE :
Tapi jika klausa WHERE dihilangkan, maka semua baris data dalam tabel akan dihapus :
9.18. Menghapus Berdasarkan Baris pada Tabel yang lain
Subquery dapat digunakan dalam statement DELETE untuk menghapus baris pada suatu table
berdasarkan data yang ada di table yang lain.
DELETE FROM employees
WHERE department_id =
(SELECT department_id
FROM departments
WHERE department_name LIKE '%Public%');
1 row deleted.
Oracle9i Introduction to SQL @Tessy Badriyah, SKom. MT.
BAB 9 : Manipulasi Data Halaman : 9 - 6
9.19. Menghapus Baris : terdapat kesalahan Integrity Constraint
Jika baris data yang dihapus pada table berkaitan dengan integrity constraint, maka akan terjadi
kesalahan.
9.20. Menggunakan Subquery dalam Statement INSERT
Subquery dapat digunakan dalam statement INSERT :
9.21. Menggunakan Keyword WITH CHECK OPTION pada DML Statement
Subquery digunakan untuk mengidentifikasi tabel dan kolom dari statement DML. Dengan keyword
WITH CHECK OPTION akan mencegah terjadinya perubahan baris yang tidak berada dalam
subquery.
9.22. Pendahuluan : Explicit Default Feature
Dengan menggunakan keyword DEFAULT secara eksplisit kita bisa menginisialisasi nilai dari suatu
kolom. Perintah tersebut dapat digunakan dalam INSERT dan UPDATE Statement.
DELETE FROM departments
WHERE department_id = 60;
DELETE FROM departments
WHERE department_id = 60;
DELETE FROM departments
*
ERROR at line 1:
ORA-02292: integrity constraint (HR.EMP_DEPT_FK)
violated - child record found
DELETE FROM departments
*
ERROR at line 1:
ORA-02292: integrity constraint (HR.EMP_DEPT_FK)
violated - child record found

 

  
  



 
 
 

INSERT INTO
(SELECT employee_id, last_name,
email, hire_date, job_id,
salary,
department_id
FROM employees
WHERE department_id = 50)
VALUES (99999, 'Taylor', 'DTAYLOR',
TO_DATE('07-JUN-99', 'DD-MON-RR'),
'ST_CLERK', 5000, 50);
1 row created.
INSERT INTO
(SELECT employee_id, last_name,
email, hire_date, job_id,
salary,
department_id
FROM employees
WHERE department_id = 50)
VALUES (99999, 'Taylor', 'DTAYLOR',
TO_DATE('07-JUN-99', 'DD-MON-RR'),
'ST_CLERK', 5000, 50);
1 row created.
INSERT INTO (SELECT employee_id, last_name, email,
hire_date, job_id, salary
FROM employees
WHERE department_id = 50 WITH CHECK OPTION)
VALUES (99998, 'Smith', 'JSMITH',
TO_DATE('07-JUN-99', 'DD-MON-RR'),
'ST_CLERK', 5000);
INSERT INTO
*
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation
INSERT INTO (SELECT employee_id, last_name, email,
hire_date, job_id, salary
FROM employees
WHERE department_id = 50 WITH CHECK OPTION)
VALUES (99998, 'Smith', 'JSMITH',
TO_DATE('07-JUN-99', 'DD-MON-RR'),
'ST_CLERK', 5000);
INSERT INTO
*
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation
Oracle9i Introduction to SQL @Tessy Badriyah, SKom. MT.
BAB 9 : Manipulasi Data Halaman : 9 - 7
9.23. Penggunaan Explicit Default Feature
DEFAULT dengan INSERT :
DEFAULT dengan UPDATE :
9.24. Statement MERGE
Statement MERGE digunakan untuk menggabungkan satu tabel dengan tabel yang lainnya. Perintah
ini pada dasarnya digunakan untuk melakukan sinkronisasi data diantara dua tabel.
Sintak dari Statement MERGE :
Contoh penggunaan Statement MERGE berikut ini untuk INSERT dan UPDATE baris data dalam
tabel COPY_EMP yang sesuai dengan data yang ada pada tabel EMPLOYEES.
9.25. Transaksi Database
Transaksi database berisi salah satu dari hal berikut :
· Statement DML untuk melakukan manipulasi terhadap data yang telah ada
· Statement DDL
· Statement DCL
INSERT INTO departments
(department_id, department_name, manager_id)
VALUES (300, 'Engineering', DEFAULT);
INSERT INTO departments
(department_id, department_name, manager_id)
VALUES (300, 'Engineering', DEFAULT);
UPDATE departments
SET manager_id = DEFAULT WHERE department_id = 10;
UPDATE departments
SET manager_id = DEFAULT WHERE department_id = 10;
MERGE INTO table_name table_alias
USING (table|view|sub_query) alias
ON (join condition)
WHEN MATCHED THEN
UPDATE SET
col1 = col_val1,
col2 = col2_val
WHEN NOT MATCHED THEN
INSERT (column_list)
VALUES (column_values);
MERGE INTO table_name table_alias
USING (table|view|sub_query) alias
ON (join condition)
WHEN MATCHED THEN
UPDATE SET
col1 = col_val1,
col2 = col2_val
WHEN NOT MATCHED THEN
INSERT (column_list)
VALUES (column_values);
MERGE INTO copy_emp c
USING employees e
ON (c.employee_id = e.employee_id)
WHEN MATCHED THEN
UPDATE SET
c.first_name = e.first_name,
c.last_name = e.last_name,
...
c.department_id = e.department_id
WHEN NOT MATCHED THEN
INSERT VALUES(e.employee_id, e.first_name, e.last_name,
e.email, e.phone_number, e.hire_date, e.job_id,
e.salary, e.commission_pct, e.manager_id,
e.department_id);
Oracle9i Introduction to SQL @Tessy Badriyah, SKom. MT.
BAB 9 : Manipulasi Data Halaman : 9 - 8
Pada saat perintah DML selesai dijalankan, diikuti dengan salah satu event berikut ini :
· Diberikan perintah COMMIT atau ROLLBACK
· Menjalankan perintah DDL atau DCL (akan dilakukan COMMIT secara otomatis)
· User keluar dari iSQL*PLUS
· System crash
9.26. Keuntungan dari Statement COMMIT dan ROLLBACK
Ada 2 statement DCL yang penting yaitu COMMIT dan ROLLBACK, selain dari itu ada
SAVEPOINT. Perintah COMMIT menandai perubahan secara permanen pada data. Sedangkan
ROLLBACK mengembalikan keadaan sesuai dengan titik (keadaan) yang ditandai dengan
SAVEPOINT, atau jika ROLLBACK tidak diberi parameter maka keadaan akan dikembalikan pada
titik perubahan yang terakhir.
Dengan menggunakan COMMIT dan ROLLBACK maka :
· Dapat dipastikan konsistensi data
· Dapat ditampilkan perubahan data sebelum membuat perubahan tersebut menjadi permanen.
· Dapat dilakukan pengelompokan secara logika operasi-operasi yang berelasi
9.27. Pengontrolan Transaksi
Berikut ini contoh pengontrolan transaksi dengan perintah COMMIT, ROLLBACK dan SAVEPOINT
9.28. Melakukan ROLLBACK ke SAVEPOINT
Perintah ROLLBACK dapat melakukan rolling back menuju ke suatu titik yang ditandai dengan
SAVEPOINT.
SAVEPOINT B
SAVEPOINT A
DELETE
INSERT
UPDATE
INSERT
Time COMMIT
Transaction
ROLLBACK
to SAVEPOINT B
ROLLBACK
to SAVEPOINT A
ROLLBACK
UPDATE...
SAVEPOINT update_done;
Savepoint created.
INSERT...
ROLLBACK TO update_done;
Rollback complete.
Oracle9i Introduction to SQL @Tessy Badriyah, SKom. MT.
BAB 9 : Manipulasi Data Halaman : 9 - 9
9.29. Pemrosesan Transaksi secara Implisit
Transaksi akan diproses secara implicit atau dilakukan operasi COMMIT secara otomatis, untuk
keadaan berikut :
· Setelah Statement DDL diberikan
· Setelah Statement DCL diberikan
· Proses exit secara normal dari SQL*PLUS.
Sedangkan perintah ROLLBACK secara otomatis akan dijalankan jika terjadi kondisi yang abnormal
atau terjadi system failure.
9.30. Status Data sebelum COMMIT atau ROLLBACK
Status data sebelum COMMIT atau ROLLBACK :
· Current user dapat menampilkan hasil dari operasi DML melalui statement SELECT
· Sedangkan user yang lain tidak dapat menampilkan hasil dari operasi DML
· Baris yang berpengaruh akibat dari perintah DML akan di-locked, sehingga user lain tidak
dapat melakukan perubahan terhadap baris tersebut.
9.31. Status Data setelah COMMIT
Status data setelah diberi COMMIT :
· Data berubah secara permanen
· Keadaan awal sebelum data permanen akan hilang
· Semua user dapat melihat hasilnya
· Penguncian (lock) pada baris data akan dilepas, sehingga baris data tersebut available untuk
semua user
· Semua savepoints dihapus
9.32. Meng-COMMIT-kan Data
Misal dibuat perubahan data sebagai berikut :
Kemudian perubahan dibuat permanen dengan perintah COMMIT :
9.33. Status Data setelah ROLLBACK
Status data setelah ROLLBACK :
· Perubahan data tidak dilakukan
· Status sebelum data diubah akan direstore
· Penguncian terhadap baris data akan dilepas
Contoh perintah ROLLBACK :
DELETE FROM employees
WHERE employee_id = 99999;
1 row deleted.
INSERT INTO departments
VALUES (290, 'Corporate Tax', NULL, 1700);
1 row inserted.
DELETE FROM employees
WHERE employee_WHERE employee_id = 99999;
1 row deleted.
INSERT INTO departments
VALUES (290, 'Corporate Tax', NULL, 1700);
1 row inserted.
COMMIT;
Commit complete.
DELETE FROM copy_emp;
22 rows deleted.
ROLLBACK;
Rollback complete.
Oracle9i Introduction to SQL @Tessy Badriyah, SKom. MT.
BAB 9 : Manipulasi Data Halaman : 9 - 10
9.34. Konsistensi Pembacaan (Read Consistency)
Konsistensi pembacaan menjamin tampilan yang konsisten untuk semua data setiap saat. Perubahan
yang dibuat oleh seorang user tidak akan konflik dengan perubahan yang dibuat oleh user yang lain.
9.35. Implementasi Konsistensi Pembacaan
9.36. Penguncian (Locking)
Pada database Oracle, penguncian atau locking berarti :
· Mencegah terjadinya interaksi yang destruktif diantara transaksi yang bersamaan
· Tidak memerlukan aksi dari user
· Ditangani selama terjadinya transaksi
· Ada dua tipe yaitu : explicit locking dan implicit locking.
9.37. Implicit Locking
Ada dua model lock :
· Exclusive : mengunci sama sekali
· Share : masih mengijinkan user lain untuk mengakses
High level dari data concurrency :
· DML : Table share, row exclusive
· Queries : tidak memerlukan lock
· DDL : melindungi dari definisi object.
Perintah Lock ditangani sampai diberikan perintah commit atau rollback.
SELECT *
FROM userA.employees;
UPDATE employees
SET salary = 7000
WHERE last_name = 'Goyal';
Data
blocks
Rollback
segments
before
change
“old” data
User A
User B
Read
consistent
image
Oracle9i Introduction to SQL @Tessy Badriyah, SKom. MT.
BAB 9 : Manipulasi Data Halaman : 9 - 11
9.38. Latihan
1. Buat table MY_EMPLOYEE yang mempunyai struktur sebagai berikut :
2. Tambahkan baris data berikut ke dalam table MY_EMPLOYEE, sehingga jika ditampilkan
akan tampak listing data table sebagai berikut :
3. Buat penambahan baris data ke dalam table menjadi permanen dengan menggunakan perintah
COMMIT;
Sebelum di-COMMIT, untuk membuktikan bahwa penambahan data belum permanen, buka
SQL*PLUS lagi tanpa menutup SQL*PLUS yang masih dibuka, kemudian dari SQL*PLUS yang
baru beri perintah :
SELECT * FROM MY_EMPLOYEE;
Maka akan terlihat bahwa table masih kosong. Tabel baru berisi jika perintah COMMIT sudah
diberikan atau kita keluar secara normal dari SQL*PLUS tempat baris data ditambahkan.
4. Ubah nama akhir dari pegawai bernomer 3 menjadi ‘Drexler’
5. Ubah gaji menjadi 1000 untuk semua pegawai yang gajinya kurang dari 900
6. Periksa perubahan yang dibuat pada soal no 4 dan 5.
7. Delete pegawai dengan nama ‘Betty Dancs’, kemudian periksa hasilnya :
8. Simpan semua perubahan (DML) dengan memberikan perintah COMMIT;
9. Beri tanda SAVEPOINT sini;
10. Setelah itu hapus semua data dalam table MY_EMPLOYEE
11. Periksa hasilnya dengan me-list semua isi tabel
12. Batalkan penghapusan dengan memberikan perintah ROLLBACK sini;
13. Periksa hasilnya dengan me-list semua isi table. Maka data pada table akan terlihat kembali.