DATA MANIPULATION LANGUAGE
(DML) PADA ORACLE
DML adalah bahasa basis data yang berguna untuk melakukan modifikasi
dan pengambilan data pada suatu basis data.
Modifikasi data terdiri dari:
1. INSERT - insert data into a table
2. UPDATE - updates existing data within a table
3. DELETE - deletes all records from a table, the space for the records
remain
4. SELECT - retrieve data from the a database
5. MERGE - UPSERT operation (insert or update)
UPSERT (UPDATE & INSERT)
6. CALL - call a PL/SQL or Java subprogram
7. EXPLAIN PLAN - explain access path to data
8. LOCK TABLE - control concurrency
You must specify COMMIT or ROLLBACK to complete or undo a DML
transaction
PENAMBAHAN DATA (INSERT) PADA
SEBUAH TABEL
INSERT INTO <namaTabel> [(field1, field2, … fieldn)]
VALUES <ListValues>
<ListValues> bisa berbentuk nilai tunggal atau berbentuk
SQL query.
Syarat dari <ListValues>, jumlah, urutan dan jenis datanya
harus sama.
Contoh :
insert into Mahasiswa (nrp, nama, alamat)
values (‘5105100234’, ‘Rayna’, ‘Surabaya’);
PEMBARUAN DATA (UPDATE)
PADA SEBUAH TABEL
UPDATE <NamaTabel> SET
<field1>=<nilai1> [,<field2>=<nilai2>, …
<fieldn>=<nilain>]
Contoh :
Update Mahasiswa set nama = ‘Rama’
where nrp = ‘5105100234’ ;
PENGHAPUSAN DATA (DELETE)
PADA SEBUAH TABEL
DELETE FROM <NamaTabel> [WHERE <kondisi>]
[Where <kondisi>]
sama seperti where yang dipelajari pada SQL.
Contoh :
Delete from Mahasiswa where nrp = ‘5105100234’;
PENGAMBILAN DATA (SELECT)
PADA SEBUAH TABEL
SELECT * FROM departments;
1 SELECT department_id, department_name
2 FROM departments;
PENGAMBILAN DATA (SELECT)
PADA SEBUAH TABEL
Perintah SELECT digunakan untuk
menampilkan data yang berasal dari
database.
Tanda * digunakan untuk menampilkan
seluruh isi kolom yang ada dalam suatu
table.
FROM digunakan untuk memilih table.
WHAT ARE THE DIFFERENCE
BETWEEN DDL, DML, DCL AND TCL
COMMANDS?
DDLData Definition Language (DDL) statements are
used to define the database structure or schema.
Some examples:
1. CREATE - to create objects in the database
2. ALTER - alters the structure of the database
3. DROP - delete objects from the database
4. RENAME - rename an object
[Link] - remove all records from a table, including all
spaces allocated for the records are removed
6. COMMENT - add comments to the data dictionary
WHAT ARE THE DIFFERENCE
BETWEEN DDL, DML, DCL AND TCL
COMMANDS?
DMLData Manipulation Language (DML) statements
are used for managing data within schema objects.
Some examples:
1. SELECT - retrieve data from the a database
2. INSERT - insert data into a table
3. UPDATE - updates existing data within a table
4. DELETE - deletes all records from a table, the space for the
records remain
5. MERGE - UPSERT operation (insert or update)
6. CALL - call a PL/SQL or Java subprogram
7. EXPLAIN PLAN - explain access path to data
8. LOCK TABLE - control concurrency
WHAT ARE THE DIFFERENCE
BETWEEN DDL, DML, DCL AND TCL
COMMANDS?
DCLData Control Language (DCL)
statements. Give or remove
access rights to the database and the
structures within it.
Some examples:
1. GRANT - gives user's access privileges to database
2. REVOKE - withdraw (menarik hak ) access
privileges given with the GRANT command
WHAT ARE THE DIFFERENCE
BETWEEN DDL, DML, DCL AND TCL
COMMANDS?
TCL Transaction Control (TCL)
statements are used to manage the
changes made by DML statements.
It allows statements to be grouped together into
logical transactions.
1. COMMIT - save work done, This command is used
to make all changes permanent in database and also marks
the end of transaction.
2. SAVEPOINT - identify a point in a transaction to
which you can later roll back
3. ROLLBACK - restore database to original since
the last COMMIT