1 · web viewmodul perkuliahan teknologi basis data (oracle) mulyadi, s.kom, m.s.i teknik...
TRANSCRIPT
MODUL PERKULIAHANTEKNOLOGI BASIS DATA (ORACLE)
MULYADI, S.KOM, M.S.I
TEKNIK INFORMATIKA
STIKOM DINAMIKA BANGSA JAMBI
2016
Teknologi Basis Data (Oracle)
BASIC INSTUCTION OFSQL DATA RETRIEVAL
Perintah dasar SQL yang digunakan untuk menampilkan data (Data Retrieval) adalah
SELECT dengan bentuk umum penulisan adalah sebagai berikut :
SELECT digunakan mengidentifikasi kolom/field
FROM digunakan mengidentifikasi tabel
Ada beberapa cara dalam menampilkan seluruh data dalam suatu tabel, yaitu :
1. Menampilkan seluruh isi tabel (seluruh field)
Contoh :
SELECT * FROM employees;
2. Menampilkan sebagian saja dari isi tabel (sebagian field)
Contoh :
SELECT last_name, job_id, salary FROM employees;
3. Menggunakan Operator Arithmetik :
2
SELECT * |{[DISTINCT] fields | expression [alias],….}
FROM table
SELECT * FROM table
SELECT field1, field2, …., fieldn FROM table
+ = Tambah - = Kurang * = Kali / = Bagi
Perioritas : * / + -
Teknologi Basis Data (Oracle)
Contoh :
SELECT last_name, salary, salary + 300 FROM employees;
SELECT last_name, salary, 12*(salary+100) FROM employees;
4. Menggunakan Kolom Alias
Contoh :
SELECT Last_name Nama, Commission_pct Komisi FROM employees
5. Menggunakan Concatenation Operator ( || )
Operator Concatenation digunakan untuk menggabungkan field dengan field dan field
dengan karakter/string.
Contoh Penggabungan field dengan field:
SELECT last_name || department_id “Employees” FROM employees
Contoh penggabungan field dengan karakter/string :
SELECT last_name || ‘ memperoleh gaji Rp. ‘ || job_id “Data Karyawan” FROM
employees
6. Duplikasi Baris
Contoh :
SELECT department_id FROM employees
7. Menghindari Duplikasi Baris
Contoh :
SELECT DISTINCT department_id FROM employees
8. Menampilkan Struktur dari Tabel :
Contoh :
3
DESC nama_tabel
SELECT field1 nama_kolom_alias1, field1 nama_kolom_alias1, …………………………….. , fieldn nama_kolom_aliasn
FROM table
Teknologi Basis Data (Oracle)
DESC departments
SELECTION & SORT
Pada bab ini membahas tentang penyeleksian data dan pengurutan data yang akan
ditampilkan. Berikut ini penjelasan tentang metode penyeleksian dan pengurutan data :
1. Mengunakan klausa WHERE untuk menyeleksi data / record.
Bentuk umum penulisan perintah SQL menggunakan klausa WHERE adalah sebagai
berikut :
Penulisan klausa WHERE diletakkan setelah klausa FROM
Penggunaan :
Where : mengakses record tertentu berdasarkan kondisi
Kondisi : terdiri dari kolom / field, ekspresi, suatu operator / kondisi
pembanding dan konstanta.
Operator Pembanding :
Operator Arti
= Sama dengan
> Lebih dari
>= Lebih dari atau sama dengan
< Kurang dari
<= Kurang dari atau sama dengan
<> Tidak sama dengan
4
SELECT * |{DISTINCT] field / expression [alias],…}FROM tabelWHERE kondisi;
Teknologi Basis Data (Oracle)
Kondisi perbandingan :
Operator Arti
BETWEEN ….. AND ……. Antara dua nilai
IN (set) Nilai yang sama dengan nilai pada tanda kurung
LIKE Menyerupai karakter tertentu
IS NULL Nilai null (kosong)
Kondisi Logika :
Operator Meaning
AND Bernilai benar jika kedua kondisi bernilai benar
OR Bernilai benar jika salah satu kondisi bernilai benar
NOT Bernilai benar jika tidak sama dengan kondisi
Prioritas :
Order Evaluated
Operator
1 Operator aritmatika ( * / + - )
2 Operator Concatenation ( || )
3 Operator Pembanding ( = > < >= <= <> )
4 IS [NOT] NULL, LIKE, [NOT] IN
5 [NOT] BETWEEN
6 Not Logical condition
7 And Logical condition
8 Or Logical Condition
Berikut beberapa contoh pemakaian operator / kondisi pembanding :
a. Contoh Pengunaan “=” untuk Numeric
5
SELECT Last_name, Department_id
FROM employees
WHERE department_id = 50
Teknologi Basis Data (Oracle)
b. Contoh Pengunaan “=” untuk Character
c. Contoh Pengunaan BETWEEN
d. Contoh Pengunaan IN
e. Contoh Pengunaan LIKE
f. Contoh Pengunaan IS NULL
g. Contoh Pengunaan AND Operator
6
SELECT Last_name, Department_id, salary
FROM employees
WHERE salary >=1400 AND last_name LIKE ‘E%’
SELECT Last_name, Department_id
from employees
WHERE last_name = ‘OConnell’
SELECT Last_name, salary
FROM employees
WHERE salary BETWEEN 1500 AND 3500
SELECT last_name, salary, manager_id
FROM employees
WHERE manager_id IN (101, 108)
SELECT first_name
FROM employees
WHERE first_name LIKE ‘A%’
SELECT last_name, Manager_id
FROM employees
WHERE manager_id IS NULL
Teknologi Basis Data (Oracle)
h. Contoh Pengunaan OR Operator
i. Contoh Pengunaan NOT Operator
j. Contoh Pengunaan Rules of Precedence 1
k. Contoh Pengunaan Rules of Precedence 2
2. Mengunakan klausa ORDER BY untuk mengurutkan data
Mengurutkan record dengan menggunakan klausa ORDER BY
- ASC : Secara Ascending, default
- DESC : Secara Descending
7
SELECT last_name, salary, manager_id
FROM employees
WHERE manager_id NOT IN (101, 102, 108)
SELECT Last_name, Department_id, salary
FROM employees
WHERE department_id= 40
OR department_id= 50 AND salary > 1400
SELECT Last_name, Department_id, salary
FROM employees
WHERE ( department_id = 40 OR department_id =
50 )
SELECT Last_name, Department_id, salary
FROM employees
WHERE salary >=1400 OR Last_name LIKE ‘E%’
Teknologi Basis Data (Oracle)
Penggunaan kalusa ORDER BY diletakkan di akhir pernyataan SELECT
a. Pengunaan ORDER BY dengan ASC
b. Pengunaan ORDER BY dengan DESC
8
SELECT last_name, department_id, manager_id,
salary
FROM employees
ORDER BY department_id
SELECT last_name, department_id, manager_id,
salary
FROM employees
ORDER BY department_id DESC
Teknologi Basis Data (Oracle)
FUNCTION
Pada bab ini mempelajari penggunaan fungsi dalam perintah SQL yang berguna untuk
memanipulasi record-record yang ditampilkan sesuai dengan kebutuhan informasinya. Bentuk
umum pemakaian fungsi adalah sebagai berikut :
Beberapa fungsi yang akan dibahas antara lain :
1. Character Functions terbagi 2, yaitu :
a. Case-manipulation functions
Fungsi ini digunakan untuk mengkonversi tampilan format huruf (Besar/Kecil), antara
lain :
LOWER digunakan untuk mengkonversi ke huruf kecil semua
UPPER digunakan untuk mengkonversi ke huruf besar semua
INITCAP digunakan untuk mengkonversi huruf awal setiap kata ke huruf besar dan
huruf lainnya ke huruf kecil.
Contoh 1:
Fungsi Hasil
LOWER(‘SQL Course’) sql course
UPPER(‘SQL Course’) SQL COURSE
INITCAP(‘SQL Course’) Sql Course
9
Nama_Fungsi [(ekspresi1, ekspresi2, …..)]
Teknologi Basis Data (Oracle)
Contoh 2 :
b. Character-manipulation functions
Fungsi ini digunakan untuk memanipulasi karakter, antara lain :
CONCAT digunakan untuk menggabungkan nilai dari suatu ekspresi dengan nilai
dari ekspresi yang lain. Hampir sama dengan concatenation operator (||)
SUBSTR digunakan untuk mengambil karakter tertentu dari suatu ekspresi
LENGTH digunakan untuk menghitung jumlah karakter dari suatu ekspresi
INSTR digunakan untuk mengetahui letak dari suatu karakter tertentu dari suatu
ekspresi
LPAD digunakan untuk menempatkan karakter tertentu disisi kiri suatu nilai
sebanyak n dari seluruh total jumlah karakter.
RPAD digunakan untuk menempatkan karakter tertentu disisi kanan suatu nilai
sebanyak n dari seluruh total jumlah karakter.
TRIM digunakan untuk menghilangkan karakter awal atau akhir dari suatu ekspresi.
REPLACE digunakan untuk mencari karakter tertentu dari suatu ekspresi, bila
ditemukan akan digantikan dengan karakter yang lain.
Contoh 1 :
FUNGSI HASIL
CONCAT(‘Hello’, ‘World’) HelloWorld
SUBSTR(‘HelloWorld’,1,5) Hello
LENGTH(‘HelloWorld’) 10
INSTR(‘HelloWorld’, ‘W’) 6
LPAD(salary,10,’*’) *****24000
RPAD(salary, 10, ‘*’) 24000*****
TRIM(‘H’ FROM ‘HelloWorld’) elloWorld
10
SELECT ‘Jabatan untuk ’ || UPPER(last_name) || ‘ adalah ‘ ||
LOWER(job_id) “Data Karyawan”
FROM employees
Teknologi Basis Data (Oracle)
Contoh 2 :
2. Number Function
Fungsi ini digunakan untuk memformat tampilan angka. Berikut ini beberapa fungsi dari
Number Function
ROUND : digunakan untuk pembulatan angka hingga mengikuti format angka
desimal tertentu
ROUND (45.926, 2) = 45.93
TRUNC : digunakan untuk memformat angka hingga mengikuti format angka
desimal tertentu
TRUNC (45.926,2) = 45.92
MOD : digunakan untuk menentukan sisa hasil pembagian.
MOD(1600,300) =100
Contoh :
3. Working with Dates
Sebelum membahas tentang fungsi yang berhubungan dengan penggunaan data tanggal,
berikut ini ada penggolongan dari suatu format data waktu, misalnya untuk tanggal 12
Juni 2007 Jam 19:05:43 dijabarkan sebagai berikut :
Berikut ini adalah beberapa fungsi yang berhubungan dengan penggunaan data tanggal
a. SYSDATE adalah fungsi yang digunakan untuk menampilkan tanggal dan waktu dari
sistem.
11
SELECT department_id, CONCAT(first_name, last_name) “Nama”,
LENGTH(last_name) “Jumlah Karakter pada Nama”,
INSTR(last_name, ‘a’) “posisi huruf ‘a’ pada Nama” FROM employees
SELECT Last_name “Nama”, Salary “Gaji perbulan” ,
ROUND(Salary/30,0) “Kebutuhan perhari” , MOD(Salary , 30) “Sisa
Gaji”
FROM Employees ;
Century Year Month Day Hour Minute Second
20 11 12 20 19 05 43
Teknologi Basis Data (Oracle)
Contoh :
SELECT SYSDATE FROM DUAL;
b. Operasi Aritmatika pada data tanggal
Operasi aritmatika pada data tanggal mengikuti aturan sebagai berikut :
Operasi Hasil Penjelasan
Date + number(n) Date Menambah n hari pada data tanggal
Date – number(n) Date Mengurangi n hari dari data tanggal
Date-date Number Menghasilkan selisih hari diantara 2 tanggal
Date + number(n)/24 Date Menambah n jam dari data tanggal
Contoh :
c. Date Functions
FUNGSI Keterangan
MONTHS_BETWEEN Menampilkan Jumlah Bulan antara 2 tanggal
ADD_MONTHS Menambah Jumlah Bulan pada suatu tanggal
NEXT_DAY Menampilkan tanggal dari nama hari tertentu
LAST_DAY Menampilkan tanggal akhir bulan
ROUND Pembulatan data tanggal
TRUNC Truncate data tanggal
Contoh :
* MONTHS_BETWEEN (’01-SEP-95’,’11-JAN-94’) = 19.6774194
* ADD_MONTHS (’11-JAN-94,6) = ’11-JUL-94’
12
SELECT SYSDATE “Tgl. Sekarang” , SYSDATE + 7 “Tgl. Minggu
Depan”,
SYSDATE – 30 “Tgl. Bulan Lalu”, SYSDATE + 48/24 “Tgl. Lusa”
FROM DUAL ;
Teknologi Basis Data (Oracle)
* NEXT_DAY (’01-SEP-95’,’FRIDAY’) = ’08-SEP-95’
* LAST_DAY(’01-FEB-95’) = ’28-FEB-95’
Mengunakan DATE Functions
MISALKAN SYSDATE = ’12-JUN-08’ :
* ROUND (SYSDATE,’MONTH’) 01-JUN-08
* ROUND (SYSDATE,’YEAR’) 01-JAN-08
* TRUNC (SYSDATE,’MONTH’) 01-JUN-08
* TRUNC (SYSDATE,’YEAR’) 01-JAN-08
Contoh :
Menggunakan TO_CHAR Function With Date
Menggunakan TO_CHAR Function With Number
13
SELECT department_id, Hire_date, ROUND(Hire_date,'MONTH'),
TRUNC(Hire_date,'MONTH') FROM employees
WHERE Hire_date LIKE '%90'
SELECT Last_name, TO_CHAR(Hire_date,'DD Month YYYY')
“Tanggal Mulai Bekerja”
FROM employees
9 = mewakili digit angka
0 = menampilkan angka 0
$ = menampilkan simbol $
. = menampilkan simbol desimal
, = menampilkan simbol pemisah
ribuan
Teknologi Basis Data (Oracle)
Contoh :
Menggunakan TO_NUMBER DAN TO_DATE Function
Syntax :
Contoh :
Current Year Specified Date RR Format YY Format
1995 27-oct-95 1995 1995
1995 27-oct-17 2017 1917
2001 27-oct-17 2017 2017
2001 27-0ct-95 1995 2095
Keterangan :
2 digits year of Spesified Date (Format RR)
0 - 49 50 - 99
2 digits year of Current
Year
0 – 49Abad / century sama
dengan abad pd Current Year
Abad / century dibawah dari abad pada Current Year
50 - 99Abad / century di atas
dari abad pada Current Year
Abad / century sama dengan abad pd Current Year
Contoh :
14
SELECT Last_Name, TO_CHAR(salary, '$99,999.00') “Gaji”
FROM employees
WHERE last_name ='Whalen'
TO_NUMBER(char, [’format_model’])
TO_DATE(char, [’format_model’])
SELECT Last_name, TO_CHAR(hire_date, 'DD-Mon-
YYYY')
FROM employees
WHERE hire_date > TO_DATE('01-Jan-90','DD-Mon-RR')
Teknologi Basis Data (Oracle)
3. General Function
Fungsi-fungsi berikut digunakan sehubungan dengan type data NULL
NVL : mengkonversi data Null ke nilai tertentu
Contoh :
SELECT last_name, salary “Gaji”, NVL(commission_pct,0) “Komisi”,
(salary*12) + (salary*12*NVL(commission_pct,0)) “Penghasilan pertahun”
FROM employees ;
NVL2 : Mengkonversi data dengan 2 kondisi
Contoh :
SELECT last_name, salary “Gaji”, commission_pct “Komisi”,
NVL2(commission_pct, 'Gaji + Komisi', 'Gaji') “Penghasilan”
FROM employees WHERE department_id in (40,80)
NULLIF : Membandingkan dua ekspresi, jika keduanya bernilai sama maka akan
ditampilkan nilai NULL, namun jika berbeda yang ditampilkan
adalah nilai pada ekspresi pertama.
15
NVL (eksp1, eksp2)Keterangan
Eksp1 : Ekspresi yang mengandung nilai NULLEksp2 : Nilai yang akan menggantikan nilai NULL
Pada Eksp1
NVL (eksp1, eksp2, eksp3)
Keterangan Jika eksp1 mengandung nilai NULL, maka akan digantikan dengan nilai pada eksp3, namun jika tidak mengandung nilai NULL maka akan digantikan dengan nilai pada eksp2
NULLIF (eksp1, eksp2)
Teknologi Basis Data (Oracle)
Contoh :
SELECT first_name, LENGTH(first_name) "Ekspresi 1",
last_name, LENGTH(last_name) "Ekspresi 2",
NULLIF(LENGTH(First_name), LENGTH(last_name)) “Hasil”
FROM employees
COALESCE : Menampilkan nilai NOT NULL dari urutan ekspresi yang dituliskan.
Contoh :
SELECT last_name, COALESCE(commission_pct, salary, 10) “Komisi” FROM
employees ORDER BY commission_pct
4. Conditional Expressions :
Fungsi ini sama dengan logika IF-THEN-ELSE, yang kita tuliskan sebagai pernyataan
SQL. Ada 2 fungsi, yaitu CASE dan DECODE
CASE Function :
Contoh :
Select last_name, department_id, Salary,
CASE department_id WHEN 40 THEN 1.10*salary
WHEN 50 THEN 1.15*salary
WHEN 70 THEN 1.20*salary
ELSE salary
END "Kenaikan Gaji"
FROM Employees
16
COALESCE(eksp1, eksp2,…..ekspn)
CASE Ekpresi WHEN nilai1 THEN Pernyataan 1 WHEN nilai2 THEN Pernyataan 2 ……………………………………………..
WHEN nilain THEN Pernyataan n ELSE Pernyataan_alternatif
END
Teknologi Basis Data (Oracle)
DECODE Function :
Contoh :
SELECT last_name, department_id, Salary,
DECODE (department_id, 40, 1.10*salary, 50, 1.15*salary,
70, 1.20*salary, salary)
“Kenaikan Gaji”
FROM Employees
17
DECODE (Kolom / ekspresi, Nilai1, Pernyataan1,
Nilai2, Pernyataan2,
……………………,
Nilain,Pernyataann,
Pernyataan_alternatif )
Teknologi Basis Data (Oracle)
DISPLAYING DATA FROM MULTIPLE TABLES
Salah satu fitur SQL yang paling berguna adalah kemampuan untuk mengabungkan
tabel – tabel dan mendapatkan data dari tabel tabel tersebut. Namun untuk mempelajari ini
terlebih dahulu anda harus memahami tabel relasional dan rancangan database relasional.
Berikut sebuah contoh pengabungan tabel-tabel tanpa where caluse yang
menyebabkan terjadinya Cartesian Product.
Cartesian Product adalah : Hasil yang dikembalikan oleh sebuah tabel relasional dimana
Jumlah baris yang didapatkan kembali akan menjadi jumlah baris dalam tabel pertama
dikalikan dengan jumlah baris pada tabel kedua.
Contoh :
SELECT last_name, department_name
FROM employees, departments
Jika dilihat dari hasil diatas terdapat 300 rows padahal didalam contoh diatas datanya
hanya sebanyak 25 rows, hal diatas terjadi karena tidak adanya Filter sehingga dalam
prosesnya komputer mengkombinasikan kedua tabel diatas. Untuk itu diperlukan cara yang
benar dalam mengakses data yang bersumber lebih dari satu tabel.
1. Penggunaan klausa WHERE
Contoh :
SELECT Employees.department_id, Employees.last_name,
Departments.department_name
FROM Employees, Departments
18
SELECT table1.column, table2.columnFROM table1, table2WHERE table1.column1 = table2.column2
Teknologi Basis Data (Oracle)
WHERE Employees.Department_id=Departments.Department_id
Didalam pencarian datanya juga bisa menggunakan kondisi.
Contoh :
SELECT Employees.department_id, Employees.last_name,
Departments.department_name
FROM Employees, Departments
WHERE Employees.Department_id=Departments.Department_id AND
Last_name = ‘Fay’ ;
Serta juga bisa menggunakan Tabel alias.
Contoh :
SELECT E.department_id, E.last_name, D.department_name
FROM Employees E, Departments D
WHERE E.Department_id=D.Department_id
(Jelas bagi kita ternyata hasilnya sama dengan yang tidak menggunakan kolom alias)
2. Penggunaan JOIN ON
Fungsi ini sama halnya dengan penggunaan klausa WHERE, namun pernyataan relational
databasenya di tuliskan setelah perintah ON
Contoh :
SELECT E.department_id, E.last_name, D.department_name
FROM Employees E JOIN Departments D
ON E.Department_id=D.Department_id
3. Penggunaan NATURAL JOIN :
Kapanpun tabel digabungkan, sedikitnya ada satu kolom yang akan muncul pada lebih
dari satu tabel (kolom akan digabungkan). Penggabungan standar mengembalikan semua
data, bahkan banyak kejadian pada kolom yang sama. Natural Join hanya mengurangi
kejadian tersebut sehingga hanya satu pada setiap kolom yang dikembalikan.
Contoh :
SELECT last_name, department_name
FROM employees
NATURAL JOIN departments
19
Teknologi Basis Data (Oracle)
4. Penggunaan OUTER JOIN
Kebanyakan join menghubungkan baris-baris dalam satu tabel dengan baris-baris tabel
lainnya. Tetapi kadang-kadang Anda perlu memasukkan baris yang tidak mempunyai
baris-baris yang berhubungan.
Contoh :
SELECT E.department_id, E.last_name, D. department_id,
D.department_name
FROM Employees E, Departments D
WHERE E.Department_id(+)=D.Department_id
5. Joining a Table to Itself
Penggabungan tabel juga bisa dilakukan dengan tabel itu sendiri
Contoh :
SELECT Karyawan.last_name || ' bawahan dari ' || Manager.last_name
FROM Employees Karyawan, Employees Manager
WHERE Karyawan.manager_id= Manager.employee_id
6. Creating Cross Joins
Klausa CROSS JOIN menghasilkan hasil persilangan dari dua tabel, sama halnya dengan
Cartesian Product pada dua table.
Contoh :
SELECT last_name, department_name
FROM employees
CROSS JOIN departments
20
Teknologi Basis Data (Oracle)
GROUP FUNCTION
Pada kasus seleksi data seringkali diminta untuk menampilkan atau memilih
sekumpulan data berdasarkan kelompok data tertentu. Untuk menyelesaikan masalah tersebut,
SQL menyediakan perintah atau sintax Group By. Pada pengelompokan data biasanya
disertakan bersama Aggregate Function. Dalam hal ini implementasinya, Aggregate Function
harus diikuti group by bila terdapat field lain yang dijadikan sebagai kriteria
pengelompokkan.
Bentuk Umum
Kriteria HAVING adalah : kriteria pemilihan atau seleksi data dengan menggunakan
kata HAVING, Kata HAVING ini bisa berupa HAVING Count(nama_field), having
SUM(nama_field), dan lain-lain.
Berikut ini adalah beberapa group functions yang digunakan :
1. Penggunaan AVG
Fungsi ini digunakan untuk mencari harga rata-rata dari sekumpulan data yang ada.
Tampilkan rata-rata dari gaji berdasarkan kode Departement :
Input :
SELECT Department_id, AVG(salary) from employees
GROUP BY Department_id
21
SELECT column, group_function(column)
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BY column];
Teknologi Basis Data (Oracle)
2. Penggunaan COUNT( )
Fungsi ini digunakan untuk mencari cacah atau banyaknya data.
Contoh :
SELECT COUNT(DISTINCT department_id)
FROM employees
3. Penggunaan MIN( ) dan MAX( ), SUM( )
Fungsi MIN( ) digunakan untuk mencari harga minimum dan MAX( ) mencari harga
maximum serta SUM( ) mencari total dari sekumpulan data yang ada.
Contoh :
SELECT MIN(salary), MAX(salary), SUM(salary)
FROM employees
Contoh lain :
SELECT MIN(salary), MAX(salary), SUM(salary)
FROM employees
WHERE Last_name LIKE 'E%'
Bisa juga menggunakan klausa WHERE dan klausa GROUP BY serta HAVING, seperti contoh-contoh berikut :
Contoh :
SELECT AVG(salary) FROM employees
WHERE department_id=30
Contoh :
SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id
Bandingkan hasil jika menggunakan HAVING :
Contoh :
SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary) >15000
22
Teknologi Basis Data (Oracle)
Bandingkan hasil jika menggunakan Order By Descending:
Contoh :
SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary) >15000
ORDER BY department_id DESC
23
Teknologi Basis Data (Oracle)
SUBQUERIES
Subquery adalah Query didalam query. Artinya seleksi data berdasarkan hasil seleksi
data yang telah ada. Sintax SQL nya sama syntax SQL pada umumnya, tetapi kondisi setelah
where diikuti dengan query baru atau subquery. Syntaxnya :
Contoh :
SELECT Last_name, salary, department_id, manager_id
FROM employees
WHERE salary =
(SELECT salary
FROM employees
WHERE department_id=40)
Contoh Menggunakan AND Single-ROW Subqueries:
SELECT Last_name, salary, department_id, Manager_id
FROM employees
WHERE salary =
(SELECT salary
FROM employees
WHERE department_id=40)
AND manager_id =
(SELECT manager_id
FROM employees
WHERE department_id=40)
24
SELECT nama_field-1,…..,nama_field-n
FROM nama_tabel
WHERE kriteria (SELECT nama_field-1, ……., nama_field-n
FROM nama_tabel
WHERE kriteria)
Teknologi Basis Data (Oracle)
Contoh Menggunakan OR Single-ROW Subqueries:
SELECT Last_name, salary, department_id, Manager_id
FROM employees
WHERE salary =
(SELECT salary
FROM employees
WHERE department_id=40)
OR manager_id =
(SELECT manager_id
FROM employees
WHERE department_id=40)
Contoh Menggunakan Function dalam Subqueries:
SELECT last_name, Department_id, salary
FROM employees
WHERE salary = ( SELECT MIN(salary)
FROM employees)
Contoh Menggunakan HAVING dan Subqueries:
SELECT department_id, MIN(salary)
FROM employees
GROUP BY Department_id
HAVING MIN(salary) >
(SELECT MIN(salary)
FROM employees
WHERE Department_id=40)
25
Teknologi Basis Data (Oracle)
DATA CONTROL LANGUAGE (DCL)
1. Create User
Untuk membuat atau menambah user baru, hanya dapat dilakukan oleh seorang
administrator database. Dalam oracle, agar dapat berperan sebagai administrator, maka
pada saat Login, masukkan user dengan username “System” dan password “oracle”.
Perintah menambah user adalah sebagai berikut :
Contoh :
CREATE USER Gunawan
IDENTIFIED BY gugun
Output :
User created
Untuk mengubah password juga dilakukan oleh administrator database dengan perintah
sebagai berikut :
Contoh :
ALTER USER Gunawan
IDENTIFIED BY wawan
Output :
User Altered
26
CREATE USER nama_user
IDENTIFIED BY password
ALTER USER nama_user
IDENTIFIED BY password_baru
Teknologi Basis Data (Oracle)
2. GRANT
Perintah Grant digunakan untuk membuat otoritas atau hak akses (priviledges) terhadap
seorang user. Dalam hal ini berarti seorang user hanya diperbolehkan melakukan transaksi
selama transaksi tersebut merupakan hak dia.
Ada 2 jenis Priviledges, yaitu System Priviledges dan Object Priviledges.
a. System Priviledges
System priviledger merupakan hak akses yang diberikan kepada user untuk dapat
melakukan koneksi ke database dan mengelola systemnya. Adapun jenis hak aksesnya
adalah sebagai berikut :
- CREATE SESSION : Koneksi ke database
- CREATE TABLE : Membuat tabel
- CREATE SEQUENCE : Membuat Sequence
- CREATE VIEW : Membuat View
- CREATE PROCEDURE : Membuat prosedur atau fungsi
- UNLIMITED TABLESPACE : Space pada skema user yg tidak terbatas
- dll.
Syntax
Contoh :
GRANT CREATE SESSION, CREATE TABLE,
CREATE PROCEDURE, UNLIMITED TABLESPACE
TO Gunawan;
Output :
Grant Succeeded
b. Object Priviledges
Object priviledges merupakan hak-hak yang dapat diberikan oleh seorang user kepada
user yang lainnya sehubungan dengan pengaksesan objek-objek dalam skema user
tersebut. Hak tersebut meliputi :
27
GRANT system_priviledges
TO nama_user
Teknologi Basis Data (Oracle)
- All Privileges : Pemberian semua hak
- Select : User hanya diperboleh melakukan select
- Insert : User hanya diperbolehkan melakukan insert
- Delete : User Hanya boleh mendelete
- Update : User hanya dibolehkan mengupdate
Syntax :
Contoh :
GRANT Select, Insert, Update
ON Employees
TO Gunawan;
3. REVOKE
Berguna untuk menghapus hak akses dari seorang user.
Syntax :
Contoh :
REVOKE Select, insert
ON Employees
FROM Gunawan ;
Keterangan :
Jadi hak select dan insert user gunawan terhadap tabel Employees sudah dihapus.
28
GRANT object_priviledgesON Nama_TabelTO nama_user
REVOKE PriveledgesON Nama_TabelFROM Nama_User ;
Teknologi Basis Data (Oracle)
MEMBUAT DAN MENGELOLATABEL
Sebelum membahas cara membuat tabel Anda harus mengenal terlebih dahulu tipe data
yang ada dalam Oracle. Type-type data dalam Oracle sebagai berikut :
1. CHAR(n)
Mendefenisikan string sepanjang n karakter, Bila N tidak disertakan, panjang karakter
adalah 1.
2. VARCHAR(n)
Mendefenisikan string yang panjangnya bisa berubah-ubah sesuai dengan kebutuhan,
namun string tersebut dibatasi sebanyak n karakter. Oracle merekomendasikan
varchar2.
3. VARCHAR2(n)
Mendefenisikan string yang panjangnya bisa berubah-ubah sesuai dengan kebutuhan,
namun string tersebut dibatasi sebanyak n karakter. Maksimum karakter pada varchar2
adalah 2000 karakter.
4. LONG
Mendefenisikan tipe data binary, maksimum 2 Gygabyte, disimpan dalam format
internal Oracle.
5. LONG RAW
Sama dengan long yaitu mendefenisikan tipe data binary, maksimum 2 Gigabyte, tidak
dikonversi oleh Oracle (data mentah apa adanya
6. DATE
Mendefenisikan tanggal, menyimpan tahun, bulan, hari, jam, menit dan detik.
7. NUMBER(n,p)
Mendefenisikan angka pecahan, fixed decimal atau floating point, Nilai n adalah
jumlah bytes total dan p adalah presisi angka di belakang koma.
29
Teknologi Basis Data (Oracle)
a. Membuat Tabel
Bentuk umum :
didalam pembuatan tabel juga bisa menggunakan Constrains. Constraint melindungi
terhapusnya data pada suatu tabel bila tabel tersebut memiliki keterkaitan dengan tabel
lainnya. Berikut ini beberapa type Constraint :
- NOT NULL : kolom tidak boleh dikosongkan
- UNIQUE : Nilai harus memiliki keunikan tersendiri
- PRIMARY KEY : Primary key pada suatu Tabel
- FOREIGN KEY : Foreign Key, digunakan untuk merelasikan tabel dengan tabel
lainnya.
- CHECK : Melakukan pengecekan terhadap kondisi tertentu.
Contoh penerapan constraints :
- Untuk membuat tabel acuan (reference) dengan Primary Key, sebelum tanda ‘)’
tambahkan sintax :
Constraint nama_constraint primary key (nama_field_primarykey) ;
- Untuk membuat tabel yang mengacu pada tabel lain (tabel relasi) sebelum tanda ‘)’
tambahkan sintak :
Constraint nama_constraint primary key(
Nama_field_primarykey-1, ………
Nama_field_primarykey-n),
Constraint nama_constraint foreign key(
Nama_field_foreignkey-1)references
Nama_tabel_acuan-1(nama_field_primarykey-1),
….
30
CREATE TABLE nama_tabel (
Nama field ke-1 tipe data (lebar_field),
……….
……….
Nama field ke-n tipe_data (lebar_field),
);
Teknologi Basis Data (Oracle)
Constraint nama_constraint foreign key(
Nama_field_foreignkey-n)references
Nama_tabel_acuan-n(nama_field_primarykey-n)
);
Soal :
Buatlah tabel barang dengan field Kode_barang char(6), nama_barang varchar2(25),
satuan_barang varchar(20) dan stok_barang number(4), primary key adalah
kode_barang
Syntax :
Create table barang (
kode_barang char(6) ,
nama_barang varchar2(25),
satuan_barang varchar2(20),
stok_barang number(4),
constraint pk_barang primary key(kode_barang)
);
Soal :
Buatlah table suplier dengan filed kode_suplier char(5), nama_suplier varchar2(30),
alamat_suplier varchar2(30), kota_suplier varchar(15), telepon_suplier varchar2(15)
dimana nomor telepon unique, primary key (PK) adalah field kode_suplier.
Syntax :
Create table suplier (
kode_suplier char(5),
nama_suplier varchar2(30),
alamat_suplier varchar2(30),
kota_suplier varchar2(15),
telepon_suplier varchar2(15) unique,
constraint pk_suplier primary key(kode_suplier) );
31
Teknologi Basis Data (Oracle)
Soal :
Buatlah table customer dengan field kode_customer char(6), nama_customer
varchar2(30), alamat_customer varchar2(30), kota_customer varchar2(15),
telepon_customer varchar2(15) unique dan primary key (PK) adalah field
kode_customer.
Syntax :
Create table customer (
kode_customer char(6),
nama_customer varchar2(30),
alamat_customer varchar2(30),
kota_customer varchar2(15),
telepon_customer varchar2(15) unique,
constraint pk_customer primary key(kode_customer) );
Soal :
Buatlah table pasok dengan field kode_pasok char(10), kode_barang char(6),
kode_suplier char(5), tanggal_pasok date, jumlah_pasok number(4). Primary Key
(PK) adalah field kode_pasok, kode_barang dan kode_suplier. Foreign Key (FK)
adalah field kode_barang dan kode_suplier.
Syntax :
Create table pasok (
kode_pasok char(10),
kode_barang char(6),
kode_suplier char(5),
tanggal_pasok date,
jumlah_pasok number(4),
constraint pk_pasok primary key(kode_pasok,
kode_barang, kode_suplier),
constraint fk_pasok_barang foreign key(kode_barang)
references barang(kode_barang),
constraint fk_pasok_suplier foreign key(kode_suplier)
32
Teknologi Basis Data (Oracle)
references suplier(kode_suplier) );
Soal :
Buatlah tabel pembelian dengan field kode_pembelian char(10), kode_barang
char(6), kode customer char(6), tanggal_pembelian date, jumlah_pembelian
number(4). Primay Key adalah field kode_pembelian, kode_barang dan
kode_customer. Foreign key (FK) adalah field kode_barang dan kode_customer.
Syntax :
Create table pembelian (
kode_pembelian char(10),
kode_barang char(6),
kode_customer char(6),
tanggal_pembelian date,
jumlah_pembelian number(4),
constraint pk_pembelian primary key(kode_pembelian,
kode_barang, kode_customer),
constraint fk_pembelian_barang foreign key(kode_barang)
references barang(kode_barang),
constraint fk_pembelian_customer foreign key(kode_customer)
references customer(kode_customer) );
Keterangan :
- fk_pasok_barang : foreign key tabel pasok yang mengacu pada tabel barang
- Fk_pasok_suplier : foreign key table pasok yang mengacu pada tabel suplier
- Fk_pembelian_barang : foreign key tabel pembelian yang mengacu pada tabel barang
- Fk_pembelian_customer : foreign key tabel pembelian yang mengacu pada tabel
customer.
33
Teknologi Basis Data (Oracle)
Untuk menampilkan struktur tabel gunakan perintah describe/desc [nama tabel], seperti
berikut ini :
1. Desc barang;
Name Null? Type KODE_BARANG NOT NULL CHAR(6) NAMA_BARANG VARCHAR2(25) SATUAN_BARANG VARCHAR2(20) STOK_BARANG NUMBER(4)
2. Desc suplier;
Name Null? Type KODE_SUPLIER NOT NULL CHAR(5) NAMA_SUPLIER VARCHAR2(30) ALAMAT_SUPLIER VARCHAR2(30) KOTA_SUPLIER VARCHAR2(15) TELEPON_SUPLIER VARCHAR2(15)
3. Desc customer;
Name Null? Type KODE_CUSTOMER NOT NULL CHAR(6) NAMA_CUSTOMER VARCHAR2(30) ALAMAT_CUSTOMER VARCHAR2(30) KOTA_CUSTOMER VARCHAR2(15) TELEPON_CUSTOMER VARCHAR2(15)
4. Desc Pasok;
Name Null? Type KODE_PASOK NOT NULL CHAR(10) KODE_BARANG NOT NULL CHAR(6) KODE_SUPLIER NOT NULL CHAR(5) TANGGAL_PASOK DATE JUMLAH_PASOK NUMBER(4)
5. Desc pembelian;
Name Null? Type KODE_PEMBELIAN NOT NULL CHAR(10) KODE_BARANG NOT NULL CHAR(6) KODE_CUSTOMER NOT NULL CHAR(6) TANGGAL_PEMBELIAN DATE
34
Teknologi Basis Data (Oracle)
JUMLAH_PEMBELIAN NUMBER(4)
Keterangan :
- Ada 5 tabel masing-masing adalah tabel barang, tabel suplier, tabel customer, tabel
pasok, dan tabel pembelian. Tabel barang dan tabel suplier dijadikan tabel acuan oleh
tabel pasok. Artinya didalam tabel pasok ada field yang berasal dari tabel barang
(yaitu kode_barang) dan ada field yang berasal dari tabel suplier (yaitu kode_suplier).
Field kode_barang (PK tabel barang) dan kode_suplier (pk tabel suplier) menjadi
foreign key/FK pada tabel pasok yang diberi nama masing-masing fk_pasok_barang
dan fk_pasok_suplier.
- Relasi yang lain adalah antara tabel barang dan tabel customer yang dihubungkan oleh
tabel relasi pembelian. Jadi tabel pembelian di sini mengacu pada tabel barang dan
tabel customer. Oleh karena itu maka kode_barang (PK Tabel barang) dan
kode_customer (PK tabel customer) menjadi foreign key/FK pada tabel pembelian
yang masing-masing diberi nama fk_pembelian_barang dan fk_pembelian_customer.
- Untuk tabel pasok memiliki 3 PK, yaitu kode_pasok, kode_barang, dan kode_suplier.
Sedangkan tabel pembelian memiliki 3 PK yaitu : kode_pembelian, kode_barang dan
kode_customer.
b. Mengubah Struktur Tabel
Mengubah tabel berarti mengubah struktur dari tabel tersebut. Ada beberapa kemungkinan
dalam melakukan perubahan yaitu mengubah dalam arti menghapus salah satu atau
beberapa field pada tabel tersebut atau dalam arti menambah satu atau beberapa field pada
tabel tersebut.
Syntax adalah :
35
Menambah Field : ALTER TABLE Nama_TabelADD Nama_Field Type_Data(Size);
Mengubah Field : ALTER TABLE Nama_TabelMODIFY Nama_Field Type_Data(Size);
Menghapus Field : ALTER TABLE Nama_TabelDROP COLUMN Nama_Field
Mengubah Nama Field :ALTER TABLE Nama_TabelRENAME COLUMN Field_Lama TO Field_Baru ;
Teknologi Basis Data (Oracle)
Contoh 1:
Pada tabel barang terdapat field dan tipe data untuk masing-masing field sebagai berikut :
Contoh :
Desc barang
Output :
Name Null? Type KODE_BARANG NOT NULL CHAR(6) NAMA_BARANG VARCHAR2(25) SATUAN_BARANG VARCHAR2(20) STOK_BARANG NUMBER(4)
Sekarang Anda diminta untuk merubah field satuan_barang dari varchar2(20) menjadi
char(5).
Caranya :
Alter table barang
Modify SATUAN_BARANG char(5) ;
Desc Barang
Output :
Name Null? Type KODE_BARANG NOT NULL CHAR(6) NAMA_BARANG VARCHAR2(25) SATUAN_BARANG CHAR(5) STOK_BARANG NUMBER(4)
Terlihat satuan_barang telah berubah menjadi char(5).
Contoh 2 :
Ubahlah tipe data untuk field stok barang dari number(4) menjadi number(2).
Input :
Alter table barang
Modify STOK_BARANG number(2) ;
Desc Barang
36
Teknologi Basis Data (Oracle)
Output :
Name Null? Type KODE_BARANG NOT NULL CHAR(6) NAMA_BARANG VARCHAR2(25) SATUAN_BARANG CHAR(5) STOK_BARANG NUMBER(2)
Contoh 3 :
Tambahkan satu field pada tabel barang dengan ketentuan :
- nama field : keterangan
- tipe data : varchar2
- lebar data : 15
Input :
Alter table barang
Add keterangan varchar(15) ;
Desc Barang
Output :
Name Null? Type KODE_BARANG NOT NULL CHAR(6) NAMA_BARANG VARCHAR2(25) SATUAN_BARANG CHAR(5) STOK_BARANG NUMBER(2) KETERANGAN VARCHAR2(15)
Contoh 4 :
Hapus Field stok_barang pada tabel barang:
Input :
Alter Table barang
Drop column stok_barang ;
Desc Barang;
37
Teknologi Basis Data (Oracle)
Output :
Name Null? Type KODE_BARANG NOT NULL CHAR(6) NAMA_BARANG VARCHAR2(25) SATUAN_BARANG CHAR(5) KETERANGAN VARCHAR2(15)
Contoh 4 :
Ganti nama field SATUAN_BARANG menjadi SATUAN pada tabel barang
Input :
Alter Table Barang
Rename Column SATUAN_BARANG to SATUAN;
Desc Barang ;
Output :
Name Null? Type KODE_BARANG NOT NULL CHAR(6) NAMA_BARANG VARCHAR2(25) SATUAN CHAR(5) KETERANGAN VARCHAR2(15)
c. Menghapus Tabel
Syntax :
Drop table akan berhasil jika tabel yang dihapus adalah tabel yang tidak ada relasinya
(tabel yang berdiri sendiri). Jika anda menghapus table yang punya relasi maka perintah
Drop ini tidak akan berhasil.
Sebagai contoh :
Input :
38
DROP TABLE nama_table
Teknologi Basis Data (Oracle)
DROP TABLE barang
Output :
DROP TABLE barang * ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys
Jelas tidak bisa karena adanya reference dengan table yang lain.
Jika Anda ingin menghapus tabel tersebut maka relasinya hapus dulu, contoh disini
hapuslah table pasok dan pembelian baru setelah itu anda bisa menghapus tabel barang,
customer dan suplier yang merupakan tabel acuan atau reference.
Input :
Drop Table Pasok
Output :
Table dropped.
Input :
Drop Table Pembelian
Output :
Table dropped
Sehingga jika lihat tabel dengan perintah Select * from tab maka yang tinggal adalah :
Tabel barang, customer, suplier. Sekarang anda diminta untuk mencoba menghapus table
barang, customer, suplier.
Input :
Drop Table Barang
Output :
Table dropped
Input :
Drop Table customer
Output :
39
Teknologi Basis Data (Oracle)
Table dropped
Input:
Drop table suplier
Output :
Table dropped
Sekarang Anda diminta untuk membuat kesemua tabel diatas kembali yaitu tabel barang,
suplier, customer, pembelian, pasok.
d. Merubah Nama Table
Contoh :
Input :
RENAME barang to brg
Output :
Table renamed.
e. Menghapus seluruh baris dalam tabel
Syarat : - tidak ada tabel yang reference
- Tidak bisa Rollback
Input :
Truncate Table brg
Output :
Table truncated.
40
RENAME Nama_Tabel_Lama TO Nama_Tabel_Baru
TRUNCATE TABLE Nama_Tabel;
Teknologi Basis Data (Oracle)
DATA MANIPULATIONS
Pada Manipulasi data ini akan dibahas beberapa hal yaitu :
1. INSERT
2. UPDATE
3. DELETE
4. MERGE
1. INSERT (Memasukkan Data)
Pada prinsipnya insert bertujuan untuk mengisikan data/record ke dalam suatu tabel.
Pengisian data ini bisa satu record penuh atau hanya sebagian saja.
Syntax :
Keterangan :
- (nama field ke-1, ………, nama field ke-n) adalah nama field yang ada pada tabel dan
sifatnya opsional.
- (nilai_field ke-1, ……..,nilai_field ke-n) adalah isi dari field pada tabel dan harus
diisi.
Contoh : isikan data berikut kedalam table barang.
Kode_barang : BRG-1
Nama_barang : Televisi
Satuan Barang : Unit
Stok_barang : 10
41
INSERT INTO nama_tabel (nama field ke-1, ………,nama field ke-n)
VALUES (nilai_field ke-1, ……..,nilai_field ke-n)
Teknologi Basis Data (Oracle)
Input :
Insert Into barang(kode_barang,nama_barang,Satuan_barang,stok_barang)
Values(‘BRG-1’,’Televisi’,’Unit’,10)
Selanjutnya jalankan perintah select * from barang maka akan menghasilkan :
Output :
KODE_B NAMA_BARANG SATUAN_BARANG STOK_BARANG BRG-1 Televisi Unit 10
Selanjutnya Anda diminta untuk mengisi / menambahkan data sebagai berikut dan diisi
sekaligus.
Kode_barang : BRG-2 BRG-3 BRG-4
Nama_barang : Kulkas Tape Recorder VCD
Satuan Barang : Unit Unit Unit
Stok_barang : 10 20 20
Input :
Insert Into Barang (Kode_barang,Nama_barang,Satuan_barang,Stok_barang)
Values (‘BRG-2’,’Kulkas’,’Unit’,10);
Insert Into Barang (Kode_barang,Nama_barang,Satuan_barang,Stok_barang)
Values (‘BRG-3’,’Tape Recorder’,’Unit’,20);
Insert Into Barang (Kode_barang,Nama_barang,Satuan_barang,Stok_barang)
Values (‘BRG-4’,’VCD’,’Unit’,20);
Membuat Script :
Anda juga bisa membuat Script sehingga bisa membuat semacam form nya, seperti contoh
berikut :
Input :
Insert Into barang
(Kode_barang,Nama_barang,Satuan_barang,Stok_barang)
Values('&Kode_barang','&Nama_barang','&Satuan_barang',&Stok_barang)
42
Teknologi Basis Data (Oracle)
Hasil setelah dijalankan query berikut :
Select * from Barang;
KODE_B NAMA_BARANG SATUAN_BARANG STOK_BARANG BRG-1 Televisi Unit 10 BRG-2 Kulkas Unit 10 BRG-3 Tape Recorder Unit 20 BRG-4 VCD Unit 20
2. UPDATE (Memperbaharui Data)
Update adalah perubahan data lama menjadi data terkini.
Syntax :
Contoh :
Disini kita input dulu sebuah data kedalam tabel barang tetapi disengajakan untuk
ditinggalkan dan kemudian diisi ulang dengan cara UPDATE.
(Diasumsikan data sudah di input) dengan hasil sebagai berikut :
Input :
Select * from barang
Output :
KODE_B NAMA_BARANG SATUAN_BARANG STOK_BARANG BRG-1 Televisi Unit 10 BRG-2 Kulkas Unit 10 BRG-3 Tape Recorder Unit 20 BRG-4 VCD Unit 20 BRG-5 Komputer Unit 10 BRG-6 Kipas Angin 10
43
UPDATE nama_table SET nama_field = data_baru WHERE nama_field_kunci = Kode_kunci
Teknologi Basis Data (Oracle)
Cara pengisian bagian record yang tertinggal adalah sebagai berikut : Input :
Update BarangSet Satuan_barang=’Unit’Where Kode_barang=’BRG-6’
Ouput :
KODE_B NAMA_BARANG SATUAN_BARANG STOK_BARANG BRG-1 Televisi Unit 10 BRG-2 Kulkas Unit 10 BRG-3 Tape Recorder Unit 20 BRG-4 VCD Unit 20 BRG-5 Komputer Unit 10 BRG-6 Kipas Angin Unit 10
6 rows selected.
Disini diasumsikan bahwa nama_barang untuk kode_barang BRG-6 terjadi kesalahan input, dimana terinput adalah Kipas Angin tapi sebenarnya adalah AC National. Kasus diatas bisa dilakukan perbaikan dengan Update, seperti contoh berikut :
Input :
Update barangset nama_barang='AC National'Where kode_barang='BRG-6';
Output :
KODE_B NAMA_BARANG SATUAN_BARANG STOK_BARANG BRG-1 Televisi Unit 10 BRG-2 Kulkas Unit 10 BRG-3 Tape Recorder Unit 20 BRG-4 VCD Unit 20 BRG-5 Komputer Unit 10 BRG-6 AC National Unit 10
6 rows selected.
3. DELETE (Menghapus Data)
Penghapusan data bisa dilakukan secara keseluruhan dan bisa juga dilakukan sebagian.
Syntax :
44
DELETE FROM nama_table
[WHERE Kondisi]
Teknologi Basis Data (Oracle)
Anda tidak bisa langsung begitu saja menghapus isi sebuah tabel reference sebelum Anda
menghapus isi dari tabel yang mengacu pada tabel tersebut. Jika Anda ingin disetiap
menghapus isi tabel tidak terganggu dengan adanya reference maka bisa menambahkan
On Delete Cascade pada saat create tabel seperti contoh berikut ini : (Tabel Pasok dan
Tabel Pembelian)
Create table pasok (
kode_pasok char(10),
kode_barang char(6),
kode_suplier char(5),
tanggal_pasok date,
jumlah_pasok number(4),
constraint pk_pasok primary key(kode_pasok,
kode_barang, kode_suplier),
constraint fk_pasok_barang foreign key(kode_barang)
references barang(kode_barang) on delete cascade,
constraint fk_pasok_suplier foreign key(kode_suplier)
references suplier(kode_suplier) on delete cascade
);
Create table pembelian (
kode_pembelian char(10),
kode_barang char(6),
kode_customer char(6),
tanggal_pembelian date,
jumlah_pembelian number(4),
constraint pk_pembelian primary key(kode_pembelian,
kode_barang, kode_customer),
constraint fk_pembelian_barang foreign key(kode_barang)
references barang(kode_barang) on delete cascade,
constraint fk_pembelian_customer foreign key(kode_customer)
references customer(kode_customer) on delete cascade
);
45
Teknologi Basis Data (Oracle)
Contoh cara menghapus isi data :
Input :
Delete from barang
Where kode_barang='BRG-6';
Select * from barang;
Output :
1 row deleted
KODE_B NAMA_BARANG SATUAN_BARANG STOK_BARANG BRG-1 Televisi Unit 10 BRG-2 Kulkas Unit 10 BRG-3 Tape Recorder Unit 20 BRG-4 VCD Unit 20 BRG-5 Komputer Unit 10
Dengan menggunakan perintah Delete data masih bisa di Rollback.
4. MERGE (Pengabungan)
Jika Anda ingin membuat sebuah tabel baru tetapi sama dengan salah satu tabel yang
lainnya, asal nama dibedakan.
Syntax :
46
Merge Into table_name AS table_alias
Using (table/view/sub_query) AS alias
ON (join condition)
WHEN MATCHED THEN
UPDATE SET
Col1 = col_vall,
Col2 = col2_val
WHEN NOT MATCHED THEN
Insert (column_list)
VALUES (column_values);
Teknologi Basis Data (Oracle)
Terlebih dahulu create sebuah file dengan nama copy_emp (contoh)
Input :
Create table copy_emp (
First_name varchar2(25),
Last_name varchar2(25),
Department_id number(7)
);
Output :
Table Created
Input :
Merge Into copy_emp c
Using employeese
ON (c.department_id=e.department_id)
WHEN MATCHED THEN
UPDATE SET
c.first_name = e.first_name,
c.last_name = e.Last_name
WHEN NOT MATCHED THEN
Insert Values(e.first_name,e.Last_name,e.department_id)
Output :
FIRST_NAME LAST_NAME DEPARTMENT_IDEddie Chang 44 Antoinette Catchpole 44 George Smith 41
Alexander Markarian 43 Chad Newman 43 Ben Biri 43 Radha Patel 34 Mai Nguyen 34 Andre Dumas 35
25 rows selected.
47
Teknologi Basis Data (Oracle)
5. SEQUENCE
DEFINISI DAN PEMBUATAN SEQUENCE
Apa itu sequence ?
Secara otomatis mengenerate bilangan secara unik
Object yang bisa dipakai bersama
Biasanya digunakan untuk keperluan penyediaan PRIMARY KEY
Sequence dibuat dengan perintah CREATE SEQUENCE.
Berikut ini sintaks umum pembuatan sequence.
Contoh
NEXTVAL, CURRVAL DAN PENGGUNAAN SEQUENCE
Next sequence ditempatkan pada NEXTVAL, yang menampung nilai sequence berikutnya yang
akan tampil..
48
Teknologi Basis Data (Oracle)
MODIFIKASI DAN PENGHAPUSAN SEQUENCE
Perintah ALTER SEQUENCE nama_sequence bisa digunakan untuk memodifikasi sequence,
misal merubah increment value, maximum value, pilihan cycle, atau cache.
Untuk menghapus sequence digunakan perintah :
Sekali dihapus, sequence tidak bisa direferensi lagi.
49
DROP SEQUENCE nama_sequence
Teknologi Basis Data (Oracle)
TRANSACTION CONTROL
1. COMMIT
Perintah Commit digunakan untuk menyimpan secara permanen transaksi yang sudah dilakukan didalam database.
Syntax :
Jika Anda melakukan Insert atau Update tetapi tidak di Commit maka penambahan data dan perubahan data yang telah dilakukan tidak akan disimpan.
2. ROLLBACK
Berbeda dengan perintah commit yang digunakan untuk menyimpan transaksi, perintah rollback justru digunakan untuk membatalkan transaksi yang terjadi didalam database tetapi dengan syarat belum dilakukan commit.
Syntax:
3. SAVEPOINT
- Beguna untuk penandaan transaksi yang baru terjadi.
- Sehingga jika ada transaksi berikutnya dan sebelum di commit terjadi rollback
maka transaksi yang sudah di save point tidak ikut ter Rollback.
50
COMMIT
ROLLBACK
Teknologi Basis Data (Oracle)
Lakukan perintah dibawah ini dan lihat hasilnya :
Update barang
set nama_barang='AC LG'
Where kode_barang='BRG-6';
Select * from barang; (lihat hasil update)
Savepoint barang ; (tandai hasil perbaikan)
Insert Into Barang
(Kode_barang,Nama_barang,Satuan_barang,Stok_barang)
Values ('BRG-7','Kipas Angin','Unit',10); (nambah data baru)
Select * from barang; (lihat hasil insert)
Rollback to barang; (lakukan rollback)
Select * from barang
(lihat hasilnya , yang dirollback hanya transaksi terakhir sedangkan
yang sudah di savepoint tidak ter rollback)
Selanjutnya jika anda ingin pembuktian, ulangi perintah diatas dan jangan lakukan savepoint
dan selanjutnya lakukan rollback dan lihat hasilnya, maka anda akan melihat bahwa yang
diupdate pun di rollback.
51