Mengelola Data

Mengelola Data
Oleh: Arif Basofi
Tujuan
• Memanipulasi data menggunakan SQL
• Menggunakan data Pump untuk mengeluarkan
•
9-2
(export) dan mengambil (import) data
Memanggil data dengan SQL Loader
Memanipulasi data menggunakan SQL
9-3
Perintah INSERT
•
•
9-4
Membuat satu baris dalam satu waktu
Menyisipkan banyak baris dari tabel lain
Perintah UPDATE
Digunakan untuk merubah nol atau lebih baris
dari suatu tabel
9-5
Perintah DELETE
Digunakan untuk menghapus nol atau lebih
baris dalam suatu tabel
9-6
Perintah MERGE
• Digunakan untuk menggabungkan data menjadi satu table,
•
yang mencakup perintah INSERT atau UPDATE dalam satu
perintah.
Penggabungan dilakukan dgn syarat:
–
–
9-7
jika data sesuai maka di-UPDATE, dan
jika data tidak sesuai maka di-INSERT.
Perintah COMMIT dan ROLLBACK
Digunakan untuk menyelesaikan transaksi:
• Commit : Membuat perubahan menjadi permanen
• Rollback : Mengembalikan apa yang telah dirubah
9-8
Integrity Constraints dan DML
9-9
Export Data dengan Data Pump
•
Manfaat Oracle Data Pump: memiliki kecepatan tinggi dalam
mentransfer data dari satu database ke database yang lain.
• Misal: mengekspor sebuah tabel yg terhubung dgn index dari satu
database dan impor tabel ke dalam database lain.
Caranya:
• Pada halaman maintenance klik Export to Files untuk menggunakan
Data Pump menuliskan data ke dalam suatu file.
1. Pada halaman pertama, pilih yang ingin di-ekspor, dan masukkan login.
2. Jika memilih skema / tabel untuk tipe ekspor, halaman selanjutnya akan
diminta memilih sekelompok skema atau tabel yang ingin anda ekspor.
3. Halaman Option adalah langkah selanjutnya, jika anda memilih ekspor
database anda akan menuju halaman ini setelah memilih tipe ekspor.
4. Halaman selanjutnya adalah files. Anda gunakan halaman files untuk
merincikan nama direktori dan nama file.
5. Halaman selanjutnya adalah Schedule, untuk mengekspor pekerjaan.
6. Halaman terakhir adalah Review, untuk menampilkan kode PL/SQL dlm
mengenerate perintah ekspor dgn selection yg dibuat dihalaman sblmnya.
•
9-10
Setelah pekerjaan di submit, klik View Job untuk mengawasi proses
pekerjaan
Export data dengan Data Pump
9-11
Import data dengan Data Pump
•
•
Data Pump Import digunakan untuk memanggil data yg akan diekstrak.
Caranya: pada halaman Maintenance klik Import from Files untuk
menggunakan Data Pump Impor.
1. Files : nama file yg akan di impor pada database server. Selanjutnya pilih
satu dari tiga pilihan impor: mengimpor seluruh file, perinci schema, atau
perinci tabel. Anda juga bisa memilih menghasilkan file log lalu memasukkan
credential dalam field host Username dan Host Password.
2. Select Object : untuk memilih objek khusus yg akan di impor, misal. untuk
memilih user yg ingin di impor dari user–user yg ada di list dan
memindahkannya ke user list yang dipilih.
3. Re-Mapping : untuk menunjukkan apakah mengimpor masing-masing data
user ke dalam skema user yg sama atau skema user yg berbeda sesuai
field Source User dan Destination User.
4. Options : untuk mengatur options penggunaan kembali file data, object
creation error, dan index yang tidak digunakan.
5. Schedule : utk menjadwalkan prosedur impor sbg job di dalam job system.
6. Review : untuk menampilkan kode PL/SQL perintah import yg dibuat.
•
9-12
Setelah pekerjaan disubmit, klik View Job untuk mengawasi proses dari
pekerjaan.
Import data dengan Data Pump
9-13
Objek DIRECTORY
9-14
SQL*Loader
• SQL Loader adalah tool Oracle untuk me-load data dari flat file
•
•
•
(text) ke dalam tabel database Oracle.
Program (executable) SQL Loader adalah sqlldr, lokasi ada di
$ORACLE_HOME/bin.
SQL Loader sering digunakan untuk mengatasi kendala jaringan.
Misalkan perusahaan memiliki kantor cabang yg tidak tersambung
scr online dgn pusat; data transaksi biasanya dikirim ke pusat
dalam bentuk text file; kemudian di pusat, data tsb di-load ke
database menggunakan SQL Loader.
SQL Loader juga sering digunakan untuk me-load data dari system
yang berbeda. Misalkan transaksi yg di-handle oleh database non
Oracle, sementara datawarehouse menggunakan Oracle. Nah, data
transaksi dari non Oracle ini kemudian di-export ke dalam text file,
dan kemudian di-import ke Oracle pakai SQL*Loader.
9-15
SQL*Loader
9-16
SQL*Loader
•
•
SQL*Loader: memanggil data dr file eksternal ke dlm tabel database
oracle.
File – file yang digunakan oleh SQL*Loader antara lain :
• Input Data Files : SQL*Loader membaca data dari satu atau lebih file
(atau OS sama dengan files) dispesifikasikan kedalam file kontrol. Dari
SQL*Loader, data dalam data file diatur sbg record. Format record bisa
di spesifikasikan ke dalam file kontrol dengan parameter INFILE. Jika
tidak ada format record yg dispesifikasikan, secara default adalah
stream format record.
• Control File : file teks yang ditulis dlm bahasa yang dimengerti oleh
SQL*Loader. File kontrol memberitahukan kepada SQL*Loader:
dimana menemukan data, bagaimana untuk parse dan interpretasi
data, dimana untuk menyisipkan data, dll.
• Log File : ketika SQL*Loader memulai eksekusi, akan dibuat file log.
Jika tidak bisa membuat file log maka eksekusi akan dibatalkan. File
log terdiri dari detail ringkasan dari load, termasuk deskripsi dari
beberapa eror yang terjadi selama proses load.
9-17
SQL*Loader
•
•
9-18
Bad Files : Bad file terdiri dari record yang ditolak, baik oleh
SQL*Loader atau database oracle. File data yang ditolak oleh
SQL*Loader ketika format input tidak benar. Setelah record file data
diterima untuk proses SQL*Loader, akan dikirim ke database oracle
untuk proses insert kedalam tabel sebagai baris. Jika database oracle
menyatakan bahwa baris tersebut benar,maka baris tsb akan di
masukkan ke dalam tabel. Jika database oracle menyatakan baris tsb
salah, maka record ditolak dan SQL*Loader meletakkan ke bad file.
Discard file : File ini hanya dibuat ketika dibutuhkan, dan hanya jika
anda mempunyai spesifikasi dari discard file yang seharusnya ada.
Discard file terdiri dari record yang difilter diluar load karena mereka
tidak sesuai dengan kriteria record-selection yang dispesifikasikan
didalam file kontrol.
File kontrol untuk SQL*Loader
File kontrol untuk SQL*Loader berisi:
• Dimana menemukan data yang dipanggil
• Format data
• Detail konfigurasi dari :
•
•
•
Pengelolaan memory
Penolakan record
Penolakan Detail penanganan isi
• Bagaimana memanipulasi data
9-19
Sintak file kontrol
• Sintaknya adalah format bebas
• Sintak tidak case sensitif
• Komentar diberikan dari dua hypens (--) yang ditandai
•
9-20
diawal komentar sampai akhir baris
Kata kunci CONSTANT sudah dipesan Æ tidak boleh
digunakan
Input Data dan Data Files
• SQL*Loader membaca data dari satu atau lebih file
•
•
9-21
yang disebutkan di file kontrol
Dari pandangan SQL*Loader, data dalam data file
dikelola sebagai record
Data file berupa salah satu dari tiga format berikut:
- Format Fixed-record
- Format Variable-record
- Format Stream-record
Metode Loading
9-22
Membandingkan Conventional dan Direct
Path Loads
9-23
Conventional Load
Direct Path Load
Menggunakan COMMIT untuk
membuat perubahan jadi
permanent
Menggunakan penyimpanan data
Entry di Redo Log selalu dibuat
Redo dibuat hanya pada kondisi2
tertentu
Semua constraints di jalankan
Hanya menjalankan constraints
PRIMARY KEY, UNIQUE, dan
NOT NULL
Trigger INSERT di-picu
Trigger INSERT tidak dipicu
Dapat digunakan untuk
memasukkan data pada table
yang dicluster
Tidak dapat digunakan untuk
memasukkan data pada table
yang di cluster
User yang lain dapat membuat
perubahan pada table
User lain tidak dapat membuat
perubahan pada table
Memasukkan data dengan SQL*Loader
9-24
Latihan
• Lakukan operasi DML pada table yang anda buat
•
9-25
sebelumnya
Lakukan operasi export-import data
Latihan SQl*Loader
1. Buat file penjualan.dat spt dibawah:
3286;23-DEC-08;SEMARANG
3287;24-DEC-08;SURABAYA
3288;25-DEC-08;MAKASAR
3289;26-DEC-08;MEDAN
3290;26-DEC-08;MAGELANG TENGAH
2. Load file penjualan.dat tsb ke dalam tabel penjualan:
CREATE TABLE penjualan
( no_invoice NUMBER,
tgl_jual
DATE,
area
varchar2(10));
3. Langkah pertama, buat control file yang berisi parameterparameter untuk SQL Loader. Misal. beri nama penjualan.ctl
load data
APPEND
into table PENJUALAN
fields terminated by ";"
TRAILING NULLCOLS
(no_invoice,tgl_jual,area)
9-26
Latihan SQl*Loader…
4. Load data di penjualan.dat ke tabel PENJUALAN, dgn
perintah berikut:
sqlldr USERID=test/test CONTROL=penjualan.ctl,
DATA=penjualan.dat, LOG=penjualan.log, BAD=penjualan.bad
Parameter untuk sqlldr adalah:
• USERID: user dan password ke database Oracle
• CONTROL: control file yang akan digunakan
• DATA: file (data) yang akan di-load
• LOG: file untuk menyimpan log dari proses loader
• BAD: file untuk menyimpan data yang tidak diproses
5. Lihat log-nya di file penjualan.log. Dan ternyata ada error:
Record 5: Rejected - Error on table PENJUALAN, column AREA.
ORA-12899: value too large for column "TEST"."PENJUALAN"."AREA"
(actual: 15, maximum: 10)
9-27
Latihan SQl*Loader...
6. Record (baris) ke-lima tidak diproses karena lebar kolom
AREA hanya 10 karakter, sementara nilai yang dimasukkan
panjangnya 15 karakter (MAGELANG TENGAH). Record yang
tidak diposes ini ditaruh di file penjualan.bad.
Sekarang coba query ke tabel PENJUALAN, data yang masuk
hanya 4 record.
SQL> select * from penjualan;
NO_INVOICE
---------3286
3287
3288
3289
9-28
TGL_JUAL
--------23-DEC-08
24-DEC-08
25-DEC-08
26-DEC-08
AREA
---------SEMARANG
SURABAYA
MAKASAR
MEDAN